标签：回滚进阶事务查询索引提交 mysql

事务

要么都成功，要么都失败

ACID

原子，一致，持久，隔离

原子性，一致性，隔离性，持久性

原子性：要么都成功，要么都失败回滚

一致性: 事务前后的数据完整性要保证一致

持久性：事务一但提交则不可逆，被持久化到数据库

隔离性: 多个用户并发访问数据库时，数据库为每个用户开启一个事务，不能被其他事务的操作所干扰，事务要相互隔离

# 开启事务
START TRANSACTION;
# A 的余额 - 200
UPDATE money SET balance = balance - 200.00 WHERE name = 'A';
# B 的余额 + 200
UPDATE money SET balance = balance + 200.00 WHERE name = 'B';
#提交事务
COMMIT;

原子性：要么完全提交（A余额减少200，B 的余额增加200），要么完全回滚（两个人的余额都不发生变化）
一致性：这个例子的一致性体现在 200元不会因为数据库系统运行到第3行之后，第4行之前时崩溃而不翼而飞，因为事务还没有提交。
隔离性：允许在一个事务中的操作语句会与其他事务的语句隔离开，比如事务A运行到第3行之后，第4行之前，此时事务B去查询A余额时，它不能看到在事务A中被减去的200元（账户钱不变），因为事务A和B是彼此隔离的。在事务A提交之前，事务B观察不到数据的改变。
持久性：事务一旦提交，不能更改

隔离失败产生的问题

脏读，幻读,不可重复读，更新丢失

脏读：读取到了其他事务未提交的数据，导致产生了脏数据

不可重复读：在一个事务中，多次查询的数据结果不同

幻读:在一个事务中，根据同一个条件查询得到的数据个数不同

更新丢失
丢失更新就是两个不同的事务（或者Java程序线程）在某一时刻对同一数据进行读取后，先后进行修改。导致第一次操作数据丢失。

第一类丢失更新：A，B 事务同时操作同一数据，A先对改数据进行了更改，B再次更改时失败然后回滚，把A更新的数据也回滚了。（事务撤销造成的撤销丢失）

第二类丢失更新：A，B 事务同时操作同一数据，A先对改数据进行了更改，B再次更改并且提交，把A提交的数据给覆盖了。（事务提交造成的覆盖丢失）

不可重复读和幻读的区别

不可重复读的重点是修改，幻读的重点在于新增或者删除。

怎么解决

解决方法：加锁（不建议使用，锁的粒度大），隔离级别，MVCC

加锁

1.脏读：在修改的时候添加排他锁，知道事务提交才释放，读取的时候共享锁，读完释放锁

2.不可重复读：读数据时加共享锁，写数据时加排他锁

3.幻读:加范围锁

隔离级别

有五种隔离级别：NONE（不使用事务），读未提交，读已提交，可重复读，串行化

读未提交（Read Uncommitted）：最低的隔离级别，允许读取尚未提交的数据变更，允许脏读、幻读或不可重复读。

读已提交（Read Committed）：一个事务只能看到已经提交的事务所造成的改变，防止脏读，允许幻读和不可重复读

可重复读（Repeatable Read）: 同一个事务的多个实例在并发读取数据时，会看到同样的数据行，mysql默认，防止脏读，不可重复读，允许幻读

串行化 (SERIALIZABLE): 最高的隔离级别，不允许读-写，写-读的并发操作 (读-读可以)，防止脏读，幻读，不可重复读

隔离级别越高，安全性越高，但是事务的并发性能越低。不建议在数据库中添加大量事务，将事务交给应用程序来控制

添加事务

MySQL中事务隐式开启的,也就是说,一个sql语句就是一个事务,当sql语句执行完毕,事务就提交了。

C:\Users> mysql -uroot -p   #进入数据库

Enter password: ****** #输入密码
# 查看是否开启了自动提交     autocommit为ON 表示开启了自动提交。
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.05 sec)
mysql>

显示开启

开启事务	START TRANSACTION 或者 begin
提交	commit；
回滚	ROLLBACK；
设计标记点	SAVEPOINT point_name;
回滚到标记点	ROLLBACK TO point_name；

# mysql中是默认开启事务自动提交的
SET autocommit = 0；#关闭

SET autocommit = 1；#开启（默认）

#手动处理事务

-- 1.关闭自动提交 
SET autocommit = 0；

-- 开启一个事务  START TRANSACTION   或者 begin
START TRANSACTION  

　　#sql操作
　　update count set money = money -100 where name ='A';   -- A-100
　　
　　SAVEPOINT p;  -- 回滚标记

　　update count set money = money +100 where name ='B'; -- B+100

commit； --提交事务
ROLLBACK ； -- 回滚

ROLLBACK TO p； -- 回滚到标记点

SET autocommit = 1； -- 恢复默认值，否则后面的sql都无法提交成功

转载自：SQL事务（非常详细） (biancheng.net)

例如，有包含如下记录的 CUSTOMERS 表：

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
+----+----------+-----+-----------+----------+

打开一个 MySQL 命令行窗口（我们称它为 A 窗口），使用事务向表中插入两条数据：

mysql> BEGIN;
mysql> INSERT INTO CUSTOMERS (NAME, AGE, ADDRESS, SALARY) VALUES ('Chaitali', 25, 'Mumbai', 6500.00 );
mysql> INSERT INTO CUSTOMERS (NAME, AGE, ADDRESS, SALARY) VALUES ('Hardik', 27, 'Bhopal', 8500.00 );

再打开另外一个 MYSQL 命令行窗口（我们称它为 B 窗口），使用 SELECT 命令查看 CUSTOMERS 表的内容：

mysql>  SELECT * FROM CUSTOMERS;
+----+---------+-----+-----------+--------+
| id | name    | age | address   | salary |
+----+---------+-----+-----------+--------+
|  1 | Ramesh  |  32 | Ahmedabad |   2000 |
|  2 | Khilan  |  25 | Delhi     |   1500 |
|  3 | Kaushik |  23 | Kota      |   2000 |
+----+---------+-----+-----------+--------+

您看，A 窗口对表的修改并没有影响到 B 窗口，B 窗口只能查看到修改之前的内容，这说明 A 窗口的修改动作并没有立即更新到物理数据库，所以在其它会话窗口中无法看到。

在 A 窗口中提交事务：

mysql> COMMIT;

在 B 窗口中再次查看 CUSTOMERS 表的内容：

mysql>  SELECT * FROM CUSTOMERS;
+----+----------+-----+-----------+--------+
| id | name     | age | address   | salary |
+----+----------+-----+-----------+--------+
|  1 | Ramesh   |  32 | Ahmedabad |   2000 |
|  2 | Khilan   |  25 | Delhi     |   1500 |
|  3 | Kaushik  |  23 | Kota      |   2000 |
|  4 | Chaitali |  25 | Mumbai    |   6500 |
|  5 | Hardik   |  27 | Bhopal    |   8500 |
+----+----------+-----+-----------+--------+

您看，在 A 窗口提交事务以后，对表所做的修改才真正更新到物理数据库中，所以才能在其它会话窗口中查看到 A 窗口中插入的数据。

回滚事务

回滚意味着撤销尚未保存到物理数据库中的操作，具体语法格式如下：

ROLLBACK;

事务执行过程中如果发生了某种故障，事务不能继续执行，就可以撤销事务，此时对数据库的修改并不会保存到物理数据库中。撤销意味着事务结束，并且执行失败。

例如，有包含如下记录的 CUSTOMERS 表：

+----+----------+-----+-----------+--------+
| id | name     | age | address   | salary |
+----+----------+-----+-----------+--------+
|  1 | Ramesh   |  32 | Ahmedabad |   2000 |
|  2 | Khilan   |  25 | Delhi     |   1500 |
|  3 | Kaushik  |  23 | Kota      |   2000 |
|  4 | Chaitali |  25 | Mumbai    |   6500 |
|  5 | Hardik   |  27 | Bhopal    |   8500 |
+----+----------+-----+-----------+--------+

使用事务删除最后两个用户，并回滚：

mysql> BEGIN;
mysql> DELETE FROM CUSTOMERS WHERE ID=4;
mysql> DELETE FROM CUSTOMERS WHERE ID=5;
mysql> ROLLBACK;

mysql> SELECT * FROM CUSTOMERS;
+----+----------+-----+-----------+--------+
| id | name     | age | address   | salary |
+----+----------+-----+-----------+--------+
|  1 | Ramesh   |  32 | Ahmedabad |   2000 |
|  2 | Khilan   |  25 | Delhi     |   1500 |
|  3 | Kaushik  |  23 | Kota      |   2000 |
|  4 | Chaitali |  25 | Mumbai    |   6500 |
|  5 | Hardik   |  27 | Bhopal    |   8500 |
+----+----------+-----+-----------+--------+

您看，回滚事务以后，物理数据库中的数据并没有发生改变，表中的内容和事务执行之前的内容一致。

回滚标记点

ROLLBACK 命令默认回滚整个事务，也即事务中的所有修改操作都无效。但是 SQL 允许回滚事务的一部分，此时需要在事务中设置一个标记点，在该标记点之后的 SQL 语句将被回滚，之前的 SQL 语句将被成功执行。

设置标记点使用 SAVEPOINT 命令，具体语法如下：

SAVEPOINT point_name;    #point_name 为标记点名字。

回滚到标记点使用 ROLLBACK TO 命令，具体语法如下：

ROLLBACK TO point_name;

例如，有包含如下记录的 CUSTOMERS 表：

+----+----------+-----+-----------+--------+
| id | name     | age | address   | salary |
+----+----------+-----+-----------+--------+
|  1 | Ramesh   |  32 | Ahmedabad |   2000 |
|  2 | Khilan   |  25 | Delhi     |   1500 |
|  3 | Kaushik  |  23 | Kota      |   2000 |
|  4 | Chaitali |  25 | Mumbai    |   6500 |
|  5 | Hardik   |  27 | Bhopal    |   8500 |
+----+----------+-----+-----------+--------+

使用事务删除最后两个用户，并回滚到标记点：

mysql> BEGIN;
mysql> DELETE FROM CUSTOMERS WHERE ID=4;
mysql> SAVEPOINT sp;
mysql> DELETE FROM CUSTOMERS WHERE ID=5;
mysql> ROLLBACK TO sp;

mysql> SELECT * FROM CUSTOMERS;
+----+---------+-----+-----------+--------+
| id | name    | age | address   | salary |
+----+---------+-----+-----------+--------+
|  1 | Ramesh  |  32 | Ahmedabad |   2000 |
|  2 | Khilan  |  25 | Delhi     |   1500 |
|  3 | Kaushik |  23 | Kota      |   2000 |
|  5 | Hardik  |  27 | Bhopal    |   8500 |
+----+---------+-----+-----------+--------+

您看，我们回滚到标记点 sp，只有 ID 为 4 的用户被删除，ID 为 5 的用户依然留在数据库中。

索引

主键索引（PRIMARY KEY）：唯一的标识，主键不可重复，数值不能为null，只能有一个列作为主键

唯一索引（UNIQUE KEY）：避免重复的列数据出现，唯一索引可以有多个，数值可以为null

全文索引（FullText）：通过倒序排序，快速定位元素

常规索引（INDEX,KEY）：默认的

原则

索引并不是越多越好
不要对进程变动的数据加索引
小数据量的表不需要加索引
索引一般加在用来查询的字段上

EXPLAIN

执行计划就是sql的执行查询的顺序，以及如何使用索引查询，返回的结果集的行数。

explain sql；查看索引

select 查询的序列号，标识执行的顺序

id 相同，执行顺序由上至下
id 不同，如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行

select_type

查询的类型，主要是用于区分普通查询、联合查询、子查询等。

SIMPLE：简单的 select 查询，查询中不包含子查询或者 union
PRIMARY：查询中包含子部分，最外层查询则被标记为 primary
SUBQUERY/MATERIALIZED：SUBQUERY 表示在 select 或 where 列表中包含了子查询，MATERIALIZED：表示 where 后面 in 条件的子查询
UNION：表示 union 中的第二个或后面的 select 语句
UNION RESULT：union 的结果

table

查询涉及到的表。

直接显示表名或者表的别名
<unionM,N> 由 ID 为 M，N 查询 union 产生的结果
<subqueryN> 由 ID 为 N 查询产生的结果

type

访问类型，SQL 查询优化中一个很重要的指标，结果值从好到坏依次是：system > const > eq_ref > ref > range > index > ALL。

system：系统表，少量数据，往往不需要进行磁盘IO
const：常量连接
eq_ref：主键索引（primary key）或者非空唯一索引（unique not null）等值扫描
ref：非主键非唯一索引等值扫描
range：范围扫描
index：索引树扫描
ALL：全表扫描（full table scan）

possible_keys

查询过程中有可能用到的索引。

key

实际使用的索引，如果为 NULL ，则没有使用索引。

执行计划 rows

根据表统计信息或者索引选用情况，大致估算出找到所需的记录所需要读取的行数。

filtered

表示返回结果的行数占需读取行数的百分比， filtered 的值越大越好。

Extra

十分重要的额外信息。

Using filesort：MySQL 对数据使用一个外部的文件内容进行了排序，而不是按照表内的索引进行排序读取。
Using temporary：使用临时表保存中间结果，也就是说 MySQL 在对查询结果排序时使用了临时表，常见于order by 或 group by。
Using index：表示 SQL 操作中使用了覆盖索引（Covering Index），避免了访问表的数据行，效率高。
Using index condition：表示 SQL 操作命中了索引，但不是所有的列数据都在索引树上，还需要访问实际的行记录。
Using where：表示 SQL 操作使用了 where 过滤条件。
Select tables optimized away：基于索引优化 MIN/MAX 操作或者 MyISAM 存储引擎优化 COUNT(*) 操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即可完成优化。
Using join buffer (Block Nested Loop)：表示 SQL 操作使用了关联查询或者子查询，且需要进行嵌套循环计算。

索引结构

CodingLabs - MySQL索引背后的数据结构及算法原理

数据库备份

保证重要数据不丢失，数据转移

https://blog.csdn.net/weixin_51486343/article/details/113702736

-- 导出
# mysqldump -h 主机 -u用户名 -p 密码 数据库 表名 >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p1111 school student >D:/a.sql

# mysqldump -h 主机 -u用户名 -p 密码 数据库 表1 表2  >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p1111 school student teacher >D:/b.sql

# mysqldump -h 主机 -u用户名 -p 密码 数据库  >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p1111 school  >D:/c.sql
-- 导入
#登入之后，切换到指定数据库  source 备份文件
source d:/a.sql

标签：回滚,进阶,事务,查询,索引,提交,mysql
From： https://www.cnblogs.com/zhuyilong/p/17052687.html

mysql进阶

事务