SQL 事务

廖雪峰数据库教学资料读书笔记.

MySQL可以分为Server层和存储引擎两部分。

• Server层包括连接器，查询缓存、分析器、优化器、执行器等。涵盖了MySQL的大多数核心服务功能，以及所有的内置函数（日期、时间、数学和加密函数等），所有跨引擎存储的功能都是在这里是显得，比如存储过程、触发器、视图等。

• 存储引擎层负责数据的存储和提取，它的结构是插件式的、支持InnoDB、MyISAM、Memory等多个存储引擎。现在最常用的存储引擎是InnoDB，也是MySQL的默认存储引擎。

---

1. 连接器：登录数据库的连接验证，完成TCP握手之后，连接器就要开始认证身份mysql -h$ip -P$port -u$user -p。
2. 查询缓存：连接建立完成之后，会到查询缓存中查询缓存。查询缓存的存储默认Key为查询语句，而Value为查询结果。查询缓存弊大于利，因为只要有表更新，这个表的相关缓存就会被清除。

只要在表的层面上更新、关于该表的所有缓存都会被清除。

3. 分析器：对语法进行分析、判断语法是否合规。
4. 优化器：对语法的执行流程进行优化、决定使用那个索引。select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;

其既可以先从表t1中获取到t1.c=10记录的ID值，再根据ID值关联到表t2，再判断t2.d=20。
也可以先从表t2中获取到t2.d=20，再根据ID值关联到t1，在判断t1的c值是否等于10.
上述这两种方法的执行结果是一样的，但是执行效率会不同，而优化器负责决定是否效率高的方案。

5. 执行器：负责具体语句的执行，首先判断是否有权限select * from T where Id = 10。

假设其中ID字段没有索引，那么执行器的执行流程如下:

1. 调用InnoDB引擎接口获取第一行判断ID值是不是10，如果不是则跳过，如果是则存储到结果集中。
2. 调用引擎接口读取下一行、重复相同的逻辑判断，知道取到最后一行。
3. 执行器将上述表里结果过程中，所有满足条件的行的记录作为结果集返回客户端。

---

索引分类

• 主要分为：唯一索引、聚集（主键）索引、非聚集索引（普通索引）。

主键索引和普通索引的区别

1. 一个表中只能有一个主键索引、但是可以有多个普通索引
2. 主键索引存储记录是物理上连续存在、而普通索引是逻辑上的连续、物理存储并不连续。
3. 查询区别：主要在于若执行的查询中需要较多的信息、普通索引会执行回表操作。

• 如果语句是select * from T where ID=500，即主键的查询方式，则只需要搜索ID这棵B+树
• 如果语句是select * from T where k=5，即普通索引查询方式，则需要先搜索k索引树，得到ID的值为500、然后根据ID索引树再次搜索。这个过程就称为回表操作。

上图右侧，根据k=5先找到ID的值为500，然后根据ID=500，在左侧找到R4数据。

可以发现，ID占用的空间越小、 普通索引树占据的空间也越小，所以此处最好使用主键ID自增操作。

唯一索引和普通索引

1. 查询性能差不多
2. 主要区别在于，更新的记录目标也不在内存中。普通索引更新会使用change buffer。唯一索引因为需要验证索引的唯一性，因此每次更新都需要将磁盘数据加载到内存。

在不影响数据一致性的前提下，InnoDB会将这些更新操作缓存在change buffer中，在下次查询需要该数据页的时候，将数据页读入内存，进行查询。然后执行change buffer与这一页有关的操作。一次满足最终一致性。

增删操作和索引

对于删除操作，InnoDB会将该条记录标记为删除，但是并不会在物理上做出删除，这类似于Redis的惰性释放策略，也类似于MySQL的Change buffer都是提高运行速度的一种策略。

为什么采用B+树

为什么不采用Redis中ZSet的那种跳跃链表、B树或者是红黑树呢？

又为什么HashMap不采取后缀B+树呢？

1. 和B树相比，其非叶子节点不保存关键字记录的指针，只进行数据索引，这样使得每个非叶子节点能保存的关键字大大增加。
2. 数据保存在叶子节点每次查询的效率一致。和其他数据结构相比
3. B+树叶子节点的关键字从小到大排列，左边结尾处会保存右边节点开始数据的指针，可以使用最左匹配原则。

事务

在执行SQL语句的时候,因为某些业务需求,一些列操作必须执行完毕, 而不能仅仅 执行一部分. 例如一个转账操作. id为1 的用户 给id为2的用户转账100块 . 这种操作是需要全部执行的. 可能出现的情况:

1. 第一条执行完毕之后 , 第二条执行失败.
2. 第二条执行成功 , 但是第一条执行失败.

这种操作要么就全部执行 , 要么就全部不执行 .

UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;

此类将多条语句 , 作为一个整体进行操作的功能被称为数据库的 事务 , 数据库事务可以确保该事务范围内的所有操作都可以 全部成功 或 全部失败 , 如果执行失败 , 效果就像没有执行过这些事务一样 , 不会对数据库造成任何改动 .

可见数据库事务具有 ACID 这四个特性 .

• A: Atomic , 原子性,将所有SQL作为原子工作单元执行, 要么全部执行, 要么全部不执行;
• C: Consistent , 一致性, 事务完成之后, 所有数据的状态都是一致的 , 即 A 账户 减去100 , 那么B账户一定增加了 100 ;
• I: Isolation , 隔离性, 如果有多个事务并发执行, 每个事物做出的修改必须和其他事务隔离;
• D: Duration , 持久性,即 事务完成之后, 对数据库的修改被持久化存储.

对于单条SQL语句来说 , 数据库系统自动将其作为一个事务执行 ,这种事务被称为 隐藏事务 .

需要手动将多条SQL语句作为一个事务执行, 使用 BEGIN 开启一个事务 , 使用COMMIT提交一个事务, 这种事务被称为显示事务.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

很显然多条语句需要作为一个事务执行的时候, 就必须使用显示事务 . COMMIT 是提交事务, 即试图将事务内的所有SQL 提交执行并永久保存 , 如果 COMMIT 失败的话 , 整个事务都会失败 且 不会对数据库产生任何影响. 有些时候我们希望事务可以主动失败 ,这个时候可以用ROLLBACK 回滚事务, 整个事务都会失败:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
ROLLBACK;

---

---

---

止于数据库的时候实现, 这个是由数据库系统保证的 , 我们只需要根据业务逻辑使用即可.

对于两个并发执行的事务, 如果牵扯到操作同一条记录的时候 , 可能会发生问题 , 因为并发操作会带来数据的不一致性 , 包括脏读 , 不可重复读, 幻读 等 . 数据库系统提供了隔离级别来让我们有针对性的选择事务的隔离级别, 避免数据的不一致性问题.

SQL提供了四种级别 分别对应可能出现的数据不一致的情况 .

Isolation	脏读(Dirty Read)	不可重复读(Non Repeatable Read)	幻读(Phantom Read)
Read Uncommitted	YES	YES	YEs
Read Committed	-	YES	YES
Repeatable Read	-	-	YES
Serializable	-	-	-

Read Uncommitted

Read Uncommitted 是隔离级别最低的一种事务级别, 在这种隔离级别下, 一个事物会读到另一个事务更新后但未提交的数据, 如果另一个事物回滚, 那么当前事务读到的数据就是脏数据, 这就是脏读(Dirty Read).

模拟脏读 , 首先准备好students的数据, 该表仅一条记录 .

id	name
1	Alice

开启两个 MySQL 查询 . 按照下列顺序执行.

时刻	事务A	事务B
1	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED	SET TRANSACTION LEVEL READ UNCOMMITTED
2	BEGIN;	BEGIN;
3	UPDATE students SET name="Bob" WHERE id=1;	-
4	-	SELECT * FROM students WHERE id=1;
5	ROLLBACK;	-
6	-	SELECT * FROM students WHERE id=1;
7	-	COMMIT;

1. 首先设置Read Umcommitted的数据隔离级别.
2. 开始事务操作.
3. 在事务A 中更新id为1 的记录name字段为 Bob.
4. 在事务B 中读取students的所有记录, 因为 数据隔离级别为Read Uncommitted, 所以可以读到 事务A中尚未提交的数据,
5. 事务A回滚, 这个时候 第4步 事务B读到的数据就成为脏数据.
6. 略.
7. 略.

Read Committed

Read Committed 在隔离级别下, 一个事务可能会遇到不可重复读(Non Repeatable Read)的问题. 不可重复读是指, 在一个事务内, 多次读统一数据, 在这个事务还没有结束时, 如果另一个事务恰好修改这个数据, 那么在第一个事务中, 两次读取的数据就可能不一致.

模拟不可重复读 , 首先准备好students的数据, 该表仅一条记录 .

id	name
1	Alice

开启两个 MySQL 查询 . 按照下列顺序执行.

时刻	事务A	事务B
1	SET TRANSACTION ISOLATION LEVEL READ COMMITTED;	SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
2	BEGIN;	BEGIN;
3	-	SELECT * FROM students WHERE id=1;
4	UPDATE students SET name="Bob" WHERE id=1	-
5	COMMIT;	-
6	-	SELECT * FROM students WHERE id=1;
7	-	COMMIT;

1. 首先设置Read Committed的数据隔离级别.
2. 开始事务操作.
3. 在事务B 中查询id=1的所有信息.
4. 在事务A 中更新id=1的记录, 因为 数据隔离级别为Read Uncommitted,因为在事务B读取结束之后,我们在事务A修改了原本的信息, 这个时候就遇到了 不可重复读(Non Repeatable Read)的问题.
5. 事务A回滚, 这个时候 第4步 事务B读到的数据就成为脏数据.
6. 略.
7. 略.

Repeatable Read

在Repeatable Read的隔离级别下, 一个事务可能会遇到幻读(Phantom Read)的问题.

幻读: 在一个事务中, 第一次查询某条记录, 发现没有,但是当试图更新这条不存在的记录的时候,竟然可以成功, 并且再次读取同一条记录, 它就神奇的出现了.

id	name
1	Alice

开启两个 MySQL 查询 . 按照下列顺序执行.

时刻	事务A	事务B
1	SET TRANSACTION ISOLATION LEVEL REPEATABLE READ	SET TRANSACTION LEVEL REPEATABLE READ
2	BEGIN;	BEGIN;
3	-	SELECT * FROM students WHERE id=99
4	INSERT INTO students (id,name) VALUES (99,'Bob')	-
5	COMMIT;	-
6	-	SELECT * FROM students WHERE id=99;
7	-	UPDATE students SET name = 'Alice' WHERE id=99;
8	-	SELECT * FROM students WHERE id = 99;
9	-	COMMIT;

1. 首先设置Repeatable Read的数据隔离级别.
2. 开始事务操作.
3. 在事务B 中查询id=99的所有信息 , 显示查询不到.
4. 在事务A 中插入一条记录 id=99,name='Bob'.
5. 事务A提交信息
6. 事务B 发现可以查询到 id=99的信息.
7. 事务B, 更新id=99成功.
8. 略.
9. 略.

Serializable

Serializable是最严格的隔离级别, 在Serializable的隔离级别下, 所有事务按照次序执行, 因此脏读,不可重复读,幻读都不会出现.

---

在MySQL的 InnoDB,中 默认的隔离级别是Repeatable Read.

小结

Read Uncommitted按照字面意思理解就可以了 , 第一个 是可以读到 没有COMMIT的 信息的 . 因为这个原因 可能会造成脏读 的问题. 同样不可重复读和 幻读 也可以出现 .

Read Committed 按照字面意思 , 只读取已经提交的信息 , 这样可以避免回滚操作 产生的脏数据. 但是 因为 改变某条记录的时候 其他事务无法重复读取 该记录.

Repeatable Read 很明显 意思是解决了 无可重复度 这个问题 .

Serializable 序列化, 顺序执行, 不会再有问题了 ,但是 效率也是低的可怕.

廖雪峰数据库教学资料读书笔记.