MySQL八股

1.SQL 查询语句的执行流程

连接器

• 与客户端进行 TCP 三次握手建立连接
• 校验客户端的用户名和密码，如果用户名或密码不对，则会报错
• 如果用户名和密码都对了，则会读取该用户的权限，然后后面的权限判断逻辑都基于此时读到的权限

查询缓存

• 查询缓存是以 key-value 形式保存在内存中的，key 为 SQL 查询语句，value 为 SQL 语句的查询结果。
• 对于更新比较频繁的表，查询缓存的命中率是很低的，因为只要一个表有更新操作，那么这个表的查询缓存就会被清空。如果刚缓存了一个查询结果很大的数据，还没被使用的时候，刚好这个表有更新操作，查询缓存就会被清空了，相当于缓存了个寂寞。
• 注意：在 MySQL 8.0 中删除了查询缓存这个功能！！！

解析 SQL

解析器

1. 词法分析

MySQL 会根据你输入的字符串识别出关键字，构建出 SQL 语法树，方便后面模块获取 SQL 类型、表名、字段名、 where 条件等等。

2. 语法分析

根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。

执行 SQL

预处理器

• 检查 SQL 查询语句中的表或字段是否存在
• 将 select * 中的 * 符号，扩展为表上的所有列

优化器

• 主要负责将 SQL 查询语句的执行方案确定下来，比如在表里面有多个索引的时候，优化器会基于查询成本的考虑，来决定使用哪个索引。
• 通过在查询语句的最前面加一个 explain 命令，就可以知道优化器选择了哪个索引

执行器

• 通过执行器，透彻理解什么是"索引下推"

索引下推能够减少二级索引在查询时的回表操作，提高查询效率，因为它将 Server 层负责的事情，交给存储引擎层去处理了。

举一个例子：这里有一张用户表如下，我对 age 和 reword 字段建立了联合索引（age，reword）：

现在有下面这条查询语句：

select * from t_user where age > 20 and reward = 100000;
1

联合索引当遇到范围查询（>、<、between、like）时就会停止匹配，也就是 age 字段能用到联合索引，但是 reward 字段则无法利用到索引。

那么，不使用索引下推（MySQL 5.6 之前的版本）时，执行器与存储引擎的执行流程是这样的：

• Server 层首先调用存储引擎的接口定位到满足查询条件的第一条二级索引记录，也就是定位到 age > 20 的第一条记录；
• 存储引擎根据二级索引的 B+ 树快速定位到这条记录后，获取主键值，然后进行回表操作，将完整的记录返回给 Server 层；
• Server 层再判断该记录的 reward 是否等于 100000，如果成立则将其发送给客户端；否则跳过该记录；
• 接着，继续向存储引擎索要下一条记录，存储引擎在二级索引定位到记录后，获取主键值，然后进行回表操作，将完整的记录返回给 Server 层；
• 如此往复，直到存储引擎把表中的所有记录读完。
• 可以看到，没有索引下推的时候，每查询到一条二级索引记录，都要进行回表操作，然后将记录返回给 Server 层，接着 Server 层再判断该记录的 reward 是否等于 100000。

而使用索引下推后，判断记录的 reward 是否等于 100000 的工作交给了存储引擎层，过程如下：

• Server 层首先调用存储引擎的接口定位到满足查询条件的第一条二级索引记录，也就是定位到 age > 20 的第一条记录；
• 存储引擎定位到二级索引后，先不执行回表操作，而是先判断一下该索引中包含的列（reward列）的条件（reward 是否等于 100000）是否成立。如果条件不成立，则直接跳过该二级索引。如果成立，则执行回表操作，将完整记录返回给 Server 层。
• Server 层再判断其他查询条件（本次查询没有其他条件）是否成立，如果成立则将其发送给客户端；否则跳过该记录，然后继续向存储引擎索要下一条记录。
• 如此往复，直到存储引擎把表中的所有记录读完。
• 可以看到，使用了索引下推后，虽然 reward 列无法使用到联合索引，但是因为它包含在联合索引（age，reward）里面，所以直接在存储引擎过滤出满足 reward = 100000 的记录后，才去执行回表操作获取整行记录。相比于没有使用索引下推，节省了很多回表操作。

存储引擎

常见的存储引擎有 InnoDB、MyISAM、Memory 等等

从 MySQL 5.5 版本开始，InnoDB 成为了 MySQL 的默认存储引擎

InnoDB 默认索引类型是 B+树

2.联合索引

• 联合索引的最左匹配原则，在遇到范围查询（>、<、between、like 包括like '林%'这种）的时候，就会停止匹配，也就是范围列可以用到联合索引，但是范围列后面的列无法用到联合索引。
• 实际开发工作中建立联合索引时，要把区分度高的字段排在前面，这样区分度高的字段越有可能被更多的 SQL 使用到。

针对下面这条 SQL，如何通过索引来提高查询效率：select * from order where status = 1 order by create_time asc

• 给 status 和 create_time 列建立一个联合索引，这样可以避免 MySQL 数据库发生文件排序。
• 如果只建立 status 的索引，但是这条语句还要对 create_time 排序，这时就会用到文件排序 filesort
• 而在 status 列和 create_time 列建立联合索引，这样根据 status 筛选后的数据就是按照 create_time 排好序的，避免发生文件排序，提高了查询效率。

3.索引的缺点

• 需要占用物理空间，数量越多，占用空间越大
• 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增大
• 会降低表的增删改的效率，因为每次增删改索引时，B+ 树为了维护索引的有序性，需要进行动态维护

4.什么时候适用索引？

• 字段有唯一性限制的，比如商品编码
• 经常用于 WHERE 查询条件的字段
• 经常用于 GROUP BY 和 ORDER BY 的字段

5.什么时候不需要创建索引？

• WHERE 条件，GROUP BY，ORDER BY 里用不到的字段，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的，因为索引是会占用物理空间的。
• 字段中存在大量重复数据，不需要创建索引，比如性别字段，只有男女，如果在数据库表中，男女的记录分布均匀，那么无论搜索哪个值都可能得到一半的数据。查询优化器如果发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。
• 表数据太少的时候，不需要创建索引
• 经常更新的字段不用创建索引，比如不要对电商项目的用户余额建立索引，因为索引字段频繁修改，由于要维护 B+Tree 的有序性，那么就需要频繁的重建索引，这个过程是会影响数据库性能的。

6.有什么优化索引的方法？

• 前缀索引优化

• 覆盖索引优化

• 主键索引最好是自增的

  • 如果使用非自增主键，就可能会造成页分裂，性能会受到影响，并且页空间的利用率下降，造成存储空间的浪费
  • 

• 索引最好设置为 NOT NULL

  • 如果索引列存在 NULL 会导致优化器在做索引选择时更加复杂

• 防止索引失效

7.聚簇索引和二级索引

聚簇索引的叶子节点存放的是实际数据

• 因为表的数据都是存放在聚簇索引的叶子节点里，所以 InnoDB 存储引擎一定会为表创建一个聚簇索引，且由于数据在物理上只会保存一份，所以聚簇索引只能有一个。
• InnoDB 在创建聚簇索引时，会根据不同的场景选择不同的列作为索引：
  • 如果有主键，默认会使用主键作为聚簇索引的索引键
  • 如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键
  • 在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键

二级索引的叶子节点存放的是主键值

8.为什么 MySQL 采用 B+ 树作为索引？

要设计一个适合 MySQL 索引的数据结构，至少要满足以下要求：

• 要在尽可能少的磁盘 I/O 操作中完成查询工作

• 既能高效地查询某一个记录，也能高效地进行范围查找

• 由于树是存储在磁盘中的，访问每一个节点，都对应一次磁盘 I/O 操作，也就是说树的高度就等于每次查询数据时磁盘 IO 操作的次数，所以树的高度越高，就会越影响查询性能。

为什么不能是二分查找树？

• 二分查找树虽然是一个天然的二分结构，能很好的利用二分查找快速定位数据，但是它存在一种极端的情况，每当插入的元素都是树内最大的元素时，就会导致二分查找树退化成一个链表，此时查询复杂度就会从 O(logn) 变成 O(n)。
• 而且会随着插入的元素越多，树的高度也变高，意味着需要磁盘 IO 操作的次数就越多，这样会导致查询性能严重下降，再加上不能范围查询，所以不适合作为数据库的索引结构。

为什么不能是自平衡二叉树（AVL树、红黑树）？

• 自平衡二叉树虽然能保持查询操作的时间复杂度在 O(logn)，但是因为它本质上还是一棵二叉树，每个节点只能有 2 个子节点，那么当节点个数越多的时候，树的高度也会相应地变高，这样就会增加磁盘的 I/O 次数，从而影响数据查询的效率。

为什么不能是 B 树，为什么要选择 B+ 树？

• MySQL 默认的存储引擎 InnoDB 采用的就是 B+ 树作为索引的数据结构
• B+ 树的非叶子节点不存放实际的记录数据，仅存放索引，因此数据量相同的情况下，相比存储即存索引又存记录的 B 树，B+树的非叶子节点可以存放更多的索引，因此 B+ 树可以比 B 树更「矮胖」，查询底层节点的磁盘 I/O 次数会更少。
• B+ 树有大量的冗余节点（所有非叶子节点都是冗余索引），这些冗余索引让 B+ 树在插入、删除的效率会更高，比如删除根节点时，不会像 B 树那样会发生复杂的树形变化。
• B+ 树叶子节点之间采用双向链表进行连接，有利于范围查询，而 B 树要想实现范围查询，只能通过树的遍历来完成范围查询，这会涉及到多个节点的磁盘 I/O 操作，所以范围查询效率不如 B+ 树。

9.哪些场景会让索引失效？

对索引使用左或者左右模糊查询

当我们使用左或者左右模糊匹配时，也就是 like %XX 或者 like %XX% 这两种方式都会造成索引失效。而如果是 like 林% 这种方式是可以走索引扫描的。

为什么 like 关键字左（%XX）或者左右模糊（%XX%）匹配无法走索引呢？

因为索引 B+ 树是按照「索引值」进行有序排列存储的，只能根据前缀进行比较。
如果使用 name like '%林' 的方式来查询，因为查询的结果可能是「陈林、张林、周林」等之类的，所以不知道从哪个索引值开始比较，于是就只能通过全表扫描的方式来查询。

对索引使用函数

如果在查询条件中对索引字段使用了函数，就会导致索引失效。

为什么对索引使用函数，就无法走索引了呢？

因为索引保存的是索引字段的原始值，而不是经过函数计算后的值，自然就没办法走索引了。

从 MySQL 8.0 开始，索引特性增加了函数索引，即可以针对函数计算后的值建立一个索引，也就是说该索引的值是函数计算后的值，这样就可以通过扫描索引来查询数据了。

举个例子：对 length(name) 的计算结果建立一个名为 idx_name_length 的索引：

alter table t_user add key idx_name_length ( length(name) );
1

对索引进行表达式计算

例如：select * from t_user where id + 1 = 10; 这个查询语句是会导致索引失效的。而如果将查询语句改写成：select * from t_user where id = 10 - 1; 就可以走索引扫描了。

为什么对索引进行表达式计算，就无法走索引了呢？

因为索引保存的是索引字段的原始值，而不是 id + 1 表达式计算后的值，所以无法走索引，只能通过把索引字段的值都取出来，然后依次进行表达式的计算来进行条件判断，因此采用的就是全表扫描的方式。

对索引进行隐式类型转换

如果索引字段是字符串类型，但是在条件查询中，输入的参数是整型的话，是会走全表扫描的。

如果索引字段是整型类型，查询条件中的输入参数是字符串的话，是不会导致索引失效的，还是可以走索引扫描的。

MySQL 在遇到字符串和数字作比较时，会自动将字符串转为数字，然后再进行比较。

如果字符串是索引列，而条件语句中的输入参数是数字的话，那么索引列会发生隐式类型转换，由于隐式类型转换是通过 CAST 函数实现的，等同于对索引列使用了函数，所以就会导致索引失效。

联合索引非最左匹配（包含索引下推知识点）

多个普通字段组合在一起创建的索引叫做联合索引，也叫组合索引。

联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配。

如果创建了一个 (a, b, c) 联合索引，查询条件是以下这几种，就可以匹配上联合索引：

where a=1;
where a=1 and b=2 and c=3;
where a=1 and b=2;
# 因为有查询优化器的存在，所以 a 字段在 where 子句中的顺序并不重要。
1
2
3
4

如果查询条件是以下这几种，因为不符合最左匹配原则，所以就无法匹配上联合索引，联合索引就会失效：

where b=2;
where c=3;
where b=2 and c=3;
1
2
3

有一个比较特殊的查询条件：where a = 1 and c = 3

MySQL 5.5 的话，前面 a 会走索引，在联合索引找到主键值后，开始回表，到主键索引读取数据行，然后再比对 c 字段的值。

从 MySQL 5.6 之后，有一个索引下推的功能，可以在索引遍历过程中，对联合索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

大概原理是：截断的字段会被下推到存储引擎层进行条件判断（因为 c 字段的值是在 (a, b, c) 联合索引中的），然后过滤出符合条件的数据后再返回给 Server 层。由于在存储引擎层就过滤掉大量的数据，无需再回表读取数据来进行条件判断，减少回表次数，从而提升了性能。

WHERE 子句中的 OR

在 WHERE 子句中，如果在 OR 前面的条件列是索引列，而在 OR 后面的条件列不是索引列，那么索引就会失效。

这是因为 OR 的含义就是两个只要满足其中一个即可，因此只有一个条件列是索引列是没有意义的，只要有条件列不是索引列，就会进行全表扫描。

10.count(*) 和 count(1) 有什么区别？哪个性能最好？

按照性能排序：count(*) = count(1) > count(主键字段) > count(普通字段)

count( ) 是一个聚合函数，函数的参数不仅可以是字段名，也可以是其他任意表达式，该函数的作用是统计符合查询条件的记录中，函数指定的参数不为 NULL 的记录有多少个。

count(主键字段) 执行过程是怎样的？

在通过 count 函数统计有多少个记录时，MySQL 的 server 层会维护一个名字叫做 count 的变量。

server 层会循环向 InnoDB 读取一条记录，如果 count 函数指定的参数不为 NULL，那么就将变量 count 的值加 1，直到符合查询条件的全部记录被读取完，就退出循环。最后将 count 变量的值发送给客户端。

接下来看一个例子：

select count(id) from t_order; # id 为主键值
1

如果表里只有主键索引，没有二级索引时，那么InnoDB 会循环遍历聚簇索引，将读取到的记录返回给 server 层，然后读取记录中的 id 值，判断 id 值是否为 NULL，如果不为 NULL，就将 count 变量的值加 1。

但是，如果表里有二级索引时，InnoDB 循环遍历的对象就不是聚簇索引，而是二级索引。

因为遍历二级索引的 I/O 成本比遍历聚簇索引的 I/O 成本小。

count(1) 执行过程是怎样的？

接下来看一个例子：

select count(1) from t_order;
1

如果表里只有主键索引，没有二级索引时，那么InnoDB 循环遍历聚簇索引，将读取到的记录返回给 server 层，但不会读取记录中的任何字段的值，因为 count 函数的参数是 1，不是字段，所以不需要读取记录中的字段值。参数为 1 很明显并不是 NULL，因此 server 层每从 InnoDB 读取到一条记录，就将 count 变量的值加 1。

可以看到，count(1) 相比 count(主键字段) 少一个步骤，就是不需要读取记录中的字段值，所以 count(1) 的执行效率会比 count(主键字段) 高一些。

但是，如果表里有二级索引时，InnoDB 循环遍历的对象就是二级索引了。

count(*) 执行过程是怎样的？

count(*) 其实等于 count(0)，也就是说，当你使用 count(*) 时，MySQL 会将 * 参数转化为参数 0 来处理。所以，count(*) 执行过程跟 count(1) 执行过程基本一样，性能没有什么差异。

而且 MySQL 会对 count(*) 和 count(1) 有个优化，如果有多个二级索引时，优化器会选择 key_len 最小的二级索引进行扫描。

只有当没有二级索引时，才会使用主键索引来进行统计。

count(普通字段) 执行过程是怎样的？

count(普通字段) 的执行效率相比前面的 count(1)、 count(*)、 count(主键字段) 是最差的。

接下来看一个例子：

select count(name) from t_order; # name不是索引，而是普通字段
1

对于这个查询来说，会采用全表扫描的方式进行计数，所以它的执行效率是比较差的。

总结

count(1)、 count(*)、 count(主键字段)在执行时，如果表里存在二级索引，优化器就会选择二级索引进行扫描。

所以，如果要执行 count(1)、 count(*)、 count(主键字段) 时，尽量在数据表上建立二级索引，这样优化器会自动选择 key_len 最小的二级索引进行扫描，相比于扫描主键索引效率会高一些。

再者，就是不要使用 count(普通字段) 来统计记录个数，因为它的效率是最差的，会采用全表扫描的方式进行统计。如果你非要统计表中该字段不为 NULL 的记录个数，建议给该字段建立一个二级索引。

11.如何优化 count(*) ？

第一种：近似值

可以使用 explain 命令进行表估算

第二种：额外表保存计数值

如果想精确地获取表的记录总数，可以将这个计数值保存到单独的一张计数表中。

当我们往数据表中插入一条记录的同时，将计数表中的计数字段 + 1。也就是说，在新增和删除操作时，需要额外维护这个计数表。

12.事务有哪些特性？

实现事务必须要遵守 4 个特性：

1. 原子性

一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节，而且事务在执行过程中如果发生错误，会被回滚到事务开始前的状态。

2. 一致性

指事务操作前和操作后，数据满足完整性约束，数据库保持一致性状态。

3. 隔离性

数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致，因为多个事务同时使用相同的数据时，不会相互干扰，每个事务都有一个完整的数据空间，对其他并发事务是隔离的。

4. 持久性

事务执行结束后，对数据的修改就是永久的，即便系统发生故障也不会丢失。

13.InnoDB 引擎通过什么技术来保证事务的这四个特性呢？

• 持久性是通过 redo log （重做日志）来保证的
• 原子性是通过 undo log（回滚日志） 来保证的
• 隔离性是通过 MVCC（多版本并发控制） 或 锁机制 来保证的
• 一致性则是通过 持久性 + 原子性 + 隔离性 来保证的

14.并发事务会引发什么问题？

MySQL 在同时处理多个事务时，就可能会出现脏读、不可重复读、幻读等问题。

脏读

如果一个事务「读到」了另一个「未提交的事务修改过的数据」，就意味着发生了「脏读」现象。

不可重复读

在一个事务内多次读取同一个数据，如果出现前后两次读到的数据不一致的情况，就意味着发生了「不可重复读」现象。

假设有 A 和 B 这两个事务同时在处理，事务 A 先开始从数据库中读取小林的余额数据，然后继续执行代码逻辑处理，在这过程中如果事务 B 更新了这条数据，并提交了事务，那么当事务 A 再次读取该数据时，就会发现前后两次读到的数据是不一致的，这种现象就被称为「不可重复读」。

幻读

在一个事务内多次查询某个符合查询条件的「记录数量」，如果出现前后两次查询到的记录数量不一样的情况，就意味着发生了「幻读」现象。

假设有 A 和 B 这两个事务同时在处理，事务 A 先开始从数据库查询账户余额大于 100 万的记录，发现共有 5 条，然后事务 B 也按相同的搜索条件也是查询出了 5 条记录。接下来，事务 A 插入一条余额超过 100 万的账号，并提交了事务，此时数据库超过 100 万余额的账号个数就变为 6。然后事务 B 再次查询账户余额大于 100 万的记录，此时查询到的记录数量有 6 条，发现和前一次读到的记录数量不一样了，就好像出现了幻觉一样，这种现象就被称为「幻读」。

15.事务的隔离级别有哪些？

当多个事务并发执行时，可能会遇到

1. 脏读：读到其他事务未提交的数据
2. 不可重复读：前后读取的数据不一致
3. 幻读：前后读取的记录数量不一致

这三个现象的严重性排序为：脏读 > 不可重复读 > 幻读

SQL 标准提出了四种隔离级别来规避这些现象，隔离级别越高，性能效率就越低，这四个隔离级别为：

1. 读未提交（read uncommitted）：指一个事务还没提交时，它做的变更就能被其他事务看到

在「读未提交」隔离级别下，可能发生脏读、不可重复读和幻读现象

2. 读提交（read committed）：指一个事务提交之后，它做的变更才能被其他事务看到

在「读提交」隔离级别下，可能发生不可重复读和幻读现象，但是不可能发生脏读现象

3. 可重复读（repeatable read）：指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的

InnoDB 默认的隔离级别

在「可重复读」隔离级别下，可能发生幻读现象，但是不可能发生脏读和不可重复读现象

可以通过 next-key lock 锁（行锁和间隙锁的组合）来锁住记录之间的 “间隙” 和记录本身，防止其他事务在这个记录之间插入新的记录，这样就避免了幻读现象。

4. 串行化（serializable ）：会对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突，后访问的事务必须等到前一个事务执行完成后才能继续执行

在「串行化」隔离级别下，脏读、不可重复读和幻读现象都不可能发生

按隔离水平高低排序：串行化 > 可重复读 > 读提交 > 读未提交

16.这四种隔离级别具体是如何实现的呢？

1. 对于「读未提交」隔离级别的事务来说，因为可以读到未提交事务修改的数据，所以直接读取最新的数据就好了。
2. 对于「串行化」隔离级别的事务来说，通过加读写锁的方式来避免并行访问。

对于「读提交」和「可重复读」隔离级别的事务来说：

它们是通过 Read View 来实现的，它们的区别在于创建 Read View 的时机不同，可以把 Read View 理解成一个数据快照，就像手机拍照那样，定格某一时刻的风景。

「读提交」隔离级别是在「每个语句执行前」都会重新生成一个 Read View。

「可重复读」隔离级别是在「启动事务时」生成一个 Read View，然后整个事务期间都在用这个 Read View。

17.两种开启事务的命令

1. 执行了 begin / start transaction 命令后，并不代表事务启动了。只有在执行这个命令后，执行了增删改查操作的 SQL 语句，才是事务真正启动的时机。
2. 执行了 start transaction with consistent snapshot 命令，就会马上启动事务。

18.Read View 在 MVCC 里是如何工作的？

Read View 是什么东西呢？

Read View 有四个重要的字段：

1. m_ids ：指的是在创建 Read View 时，当前数据库中「活跃事务」的事务 id 列表，“活跃事务”指的就是：启动了但还没提交的事务。
2. min_trx_id ：指的是在创建 Read View 时，当前数据库中「活跃事务」中事务 id 最小的事务，也就是 m_ids 的最小值。
3. max_trx_id ：这个并不是 m_ids 的最大值，而是创建 Read View 时当前数据库中应该分配给下一个事务的 id 值，也就是全局事务中最大的事务 id 值 + 1。
4. creator_trx_id ：指的是创建该 Read View 的事务 id。

聚簇索引记录中包含两个隐藏列

1. trx_id：当一个事务对某条聚簇索引记录进行改动时，就会把该事务的事务 id 记录在 trx_id 隐藏列里。
2. roll_pointer：每次对某条聚簇索引记录进行改动时，都会把旧版本的记录写入到 undo 日志中，然后这个隐藏列是个指针，指向每一个旧版本记录，于是就可以通过它找到修改前的记录。

举个例子：假设事务A（id=50）在账户余额表插入一条小林余额为 100 万的记录，该记录的整个示意图如下：

在创建 Read View 后，我们可以将记录中的 trx_id 划分成这三种情况：

一个事务去访问记录的时候，除了自己的更新记录总是可见之外，还有这几种情况：

1. 如果记录的 trx_id 值小于 Read View 中的 min_trx_id 值，表示这个版本的记录是在创建 Read View 前已经提交的事务生成的，所以该版本的记录对当前事务可见。
2. 如果记录的 trx_id 值大于等于 Read View 中的 max_trx_id 值，表示这个版本的记录是在创建 Read View 后才启动的事务生成的，所以该版本的记录对当前事务不可见。
3. 如果记录的 trx_id 值在 Read View 的 min_trx_id 和 max_trx_id 之间，需要判断 trx_id 是否在 m_ids 列表中：

如果记录的 trx_id 在 m_ids 列表中，表示生成该版本的记录的活跃事务依然活跃着（还没提交事务），所以该版本的记录对当前事务不可见。

如果记录的 trx_id 不在 m_ids 列表中，表示生成该版本的记录的活跃事务已经被提交，所以该版本的记录对当前事务可见。

这种通过「版本链」来控制并发事务访问同一个记录时的行为叫做 MVCC（多版本并发控制）。

19.可重复读是如何工作的？

可重复读隔离级别是启动事务时生成一个 Read View，然后整个事务期间都在用这个 Read View。

假设事务 A （事务 id 为51）启动后，紧接着事务 B （事务 id 为52）也启动了，那这两个事务创建的 Read View 如下：

事务 A 和 事务 B 的 Read View 具体内容如下：

• 在事务 A 的 Read View 中，它的事务 id 是 51，由于它是第一个启动的事务，所以此时活跃事务的事务 id 列表就只有 51，活跃事务的事务 id 列表中最小的事务 id 是事务 A 本身，下一个事务 id 则是 52。
• 在事务 B 的 Read View 中，它的事务 id 是 52，由于事务 A 是活跃的，所以此时活跃事务的事务 id 列表有 51 和 52，活跃的事务 id 中最小的事务 id 是事务 A，下一个事务 id 应该是 53。

接着，在「可重复读」隔离级别下，事务 A 和事务 B 按顺序执行以下操作：

• 事务 B 读取小林的账户余额记录，读到的余额是 100 万；
• 事务 A 将小林的账户余额记录修改成 200 万，并没有提交事务；
• 事务 B 读取小林的账户余额记录，读到的余额还是 100 万；
• 事务 A 提交事务；
• 事务 B 读取小林的账户余额记录，读到的余额依然还是 100 万；

事务 B 第一次读小林的账户余额记录，在找到记录后，它会先看这条记录的 trx_id，此时发现 trx_id 为 50，比事务 B 的 Read View 中的 min_trx_id 值（51）还小，这意味着修改这条记录的事务早在事务 B 启动前就提交过了，所以该版本的记录对事务 B 是可见的，也就是事务 B 可以获取到这条记录。

接着，事务 A 通过 update 语句将这条记录修改了（还未提交事务），将小林的余额改成 200 万，这时 MySQL 会记录相应的 undo log，并以链表的方式串起来，形成版本链，如下图：

然后事务 B 第二次去读取该记录，发现这条记录的 trx_id 值为 51，在事务 B 的 Read View 的 min_trx_id 和 max_trx_id 之间，则需要判断 trx_id 值是否在 m_ids 范围内，判断的结果是在的，那么说明这条记录是被还未提交的事务修改的，这时事务 B 并不会读取这个版本的记录。而是沿着 undo log 版本链往下找旧版本的记录，直到找到 trx_id 「小于」事务 B 的 Read View 中的 min_trx_id 值的第一条记录，所以事务 B 能读取到的是 trx_id 为 50 的记录，也就是小林余额是 100 万的这条记录。

最后，当事物 A 提交事务后，由于此时的隔离级别是「可重复读」，所以事务 B 再次读取记录时，还是基于启动事务时创建的 Read View 来判断当前版本的记录是否可见。所以，即使事物 A 将小林余额修改为 200 万并提交了事务， 事务 B 第三次读取记录时，读到的记录都还是小林余额是 100 万的这条记录。

就是通过这样的方式实现了「可重复读」隔离级别下在事务期间读到的记录都是事务启动前的记录。

20.读提交是如何工作的？

「读提交」隔离级别是在每次读取数据时，都会生成一个新的 Read View。

也意味着，事务期间的多次读取同一条数据，前后两次读的数据可能会出现不一致，因为可能这期间另一个事务修改了该记录，并提交了事务。

那「读提交」隔离级别是怎么工作呢？

假设事务 A （事务 id 为51）启动后，紧接着事务 B （事务 id 为52）也启动了，接着按顺序执行了以下操作：

• 事务 B 读取数据（创建 Read View），小林的账户余额为 100 万；
• 事务 A 修改数据（还没提交事务），将小林的账户余额从 100 万修改成了 200 万；
• 事务 B 读取数据（创建 Read View），小林的账户余额为 100 万；
• 事务 A 提交事务；
• 事务 B 读取数据（创建 Read View），小林的账户余额为 200 万；

那具体怎么做到的呢？我们重点看事务 B 每次读取数据时创建的 Read View。前两次 事务 B 读取数据时创建的 Read View 如下图：

21.幻读是怎么被解决的？

1. 普通的查询是快照读，是不会看到别的事务插入的数据的（由 MVCC 多版本并发控制实现）。
2. MySQL 里除了普通查询是快照读，其他都是当前读，比如 update、insert、delete，这些语句执行前都会查询最新版本的数据，然后再做进一步的操作。

22.在 MySQL 里，根据加锁的范围，可以分为全局锁、表级锁和行锁三类

全局锁

# 执行命令：
flush tables with read lock
1
2

执行后，整个数据库就处于只读状态了，这时其他线程执行以下操作，都会被阻塞：

• 对数据进行增删改操作，比如 insert、delete、update 等语句
• 对表结构进行更改操作，比如 alter table、drop table 等语句

如果想要释放全局锁，则要执行这条命令：unlock tables;

或者当会话断开了，全局锁也会被自动释放

应用场景

全局锁主要应用于做全库逻辑备份，这样在备份数据库期间，不会因为数据或表结构的更新，而出现备份文件的数据和预期不一致的问题。

缺点

加上全局锁，意味着整个数据库都是只读状态。

如果数据库中有很多数据，备份就会花费很长时间，关键是备份期间，业务只能读数据，而不能更新数据，这样会造成业务停滞。

备份数据库数据的时候，使用全局锁会影响业务，那有什么其他方式可以避免吗？

如果数据库的引擎支持的事务支持「可重复读」的隔离级别，那么在备份数据库之前先开启事务，会先创建 Read View，然后整个事务执行期间都在用这个 Read View，而且由于 MVCC 的支持，备份期间业务依然可以对数据进行更新操作。

备份数据库的工具是 mysqldump，在使用 mysqldump 时加上 –single-transaction 参数，就会在备份数据库之前先开启事务。这种方式只适用于支持「可重复读」隔离级别的事务的存储引擎。

表级锁

表锁

# 表级别的共享锁，也就是读锁；
lock tables xxx read; # 如果本线程对该表加了「共享表锁」，那么本线程如果要对该表进行写操作是会被阻塞的，当然其他线程对该表进行写操作也会被阻塞，直到锁被释放。

# 表级别的独占锁，也就是写锁；
lock tables xxx write;

- 主动释放当前会话的所有表锁：unlock tables
- 当会话退出后，会自动释放所有的表锁
1
2
3
4
5
6
7
8

元数据锁（MDL）

1. 我们不需要显式的使用 MDL，因为当我们对数据库表进行操作时，会自动给这个表加上 MDL：

对一张表进行 CRUD 操作时，加的是 MDL 读锁

对一张表做结构变更操作时，加的是 MDL 写锁

2. MDL 是为了保证当用户对表进行 CRUD 操作时，防止其他线程对这个表结构做了变更。
3. 读读共享，读写互斥，写写互斥

MDL 不需要显示调用，那它是在什么时候释放的?

1. MDL 是在事务提交后才会释放，这意味着事务执行期间，MDL 是一直持有的。

2. 如果数据库有一个长事务（所谓的长事务，就是开启了事务，但一直没有提交），那在对表结构做变更操作的时候，可能会发生意想不到的事情，比如下面这个顺序的场景：

• 首先，线程 A 先开启了事务（但是一直不提交），然后执行一条 select 语句，此时就先对该表加上 MDL 读锁
• 然后，线程 B 也执行了同样的 select 语句，此时并不会阻塞，因为「读读」并不冲突
• 接着，线程 C 修改了表字段，此时由于线程 A 的事务还没有提交，也就是 MDL 读锁还在占用着，这时线程 C 就无法申请到 MDL 写锁，就会被阻塞
• 那么在线程 C 阻塞后，后续有对该表的 select 语句，就都会被阻塞，如果此时有大量该表的 select 语句的请求到来，就会有大量的线程被阻塞住，这时数据库的线程很快就会爆满了。

3. 为什么线程 C 会因为申请不到 MDL 写锁，而导致后续申请读锁的查询操作都被阻塞呢？

• 这是因为申请 MDL 锁的操作会形成一个队列，队列中写锁获取优先级高于读锁，一旦出现 MDL 写锁等待，就会阻塞后续该表的所有 CRUD 操作。
• 所以为了能安全的对表结构进行变更，在对表结构进行变更前，先要看看数据库中的长事务，是否已经有事务对该表加上了 MDL 读锁，如果可以需要考虑 kill 掉这个长事务，然后再做表结构的变更。

意向锁

1. 意向共享锁（IS Lock）：当事务想要获得一张表中某几行的共享锁（行级锁）时，InnoDB 存储引擎会自动地先获取该表的意向共享锁（表级锁）
2. 意向排他锁（IX Lock）：当事务想要获得一张表中某几行的排他锁（行级锁）时，InnoDB 存储引擎会自动地先获取该表的意向排他锁（表级锁）

注意：普通的 select 是不会加行级锁的，普通的 select 语句是利用 MVCC 实现一致性读，是无锁的。

但是 select 也可以显式对记录加共享锁和排他锁

# 先在表上加意向共享锁，然后对读取的记录加共享锁
select ... lock in share mode;

# 先在表上加意向排他锁，然后对读取的记录加排他锁
select ... for update;
1
2
3
4
5

3. 意向共享锁和意向排他锁是表级锁，不会和行级的共享锁和排他锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁（lock tables … read）和独占表锁（lock tables … write）发生冲突。

意向锁之间是相互兼容的：

但是意向锁与表级读写锁之间大部分都是不兼容的：

注意：意向锁不会与行级的读写锁互斥。

表锁和行锁是满足读读共享、读写互斥、写写互斥的。

4. 如果没有「意向锁」，那么在加「独占表锁」时，就需要遍历表里的所有记录，查看是否有记录存在排他锁（行级锁），这样效率会很慢。

那么有了「意向锁」，由于在对记录加排他锁（行级锁）前，会先自动加上表级别的意向排他锁，那么在加「独占表锁」时，会先查看该表是否有意向排他锁，如果有就说明表里已经有记录被加了排他锁（行级锁），这样就不用再去遍历表里的记录了。

所以，意向锁的目的是为了快速判断表里是否有记录被加锁（行级锁）。

AUTO-INC 锁

行级锁

1. InnoDB 引擎是支持行级锁的，而 MyISAM 引擎并不支持行级锁
2. Record Lock：记录锁，也就是仅仅把一条记录锁上
3. Gap Lock：间隙锁，锁定一个范围，但是不包含记录本身
4. Next-Key Lock：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身

MySQL 行锁的加锁规则（重点）

前提说明

对记录加锁时，加锁的基本单位是：next-key lock 锁，它是由 记录锁 和 间隙锁 组合而成的

next-key lock锁的区间范围是左开右闭 (]

间隙锁的区间范围是左开右开 ()

下面的实验都是基于这个表：

唯一索引等值查询

1. 当查询的记录是存在的，next-key lock 会退化成「记录锁」

会话1加锁变化过程如下：

• 加锁的基本单位是 next-key lock，因此会话1的加锁范围是 (8, 16]
• 但由于是用唯一索引进行等值查询，且查询的记录是存在的，所以 next-key lock 会退化成记录锁，因此最终加锁范围是 id = 16 这一行

2. 当查询的记录是不存在的，next-key lock 会退化成「间隙锁」

会话1加锁变化过程如下：

• 加锁的基本单位是 next-key lock，因此主键索引 id 的加锁范围是 (8, 16]
• 但由于查询的记录是不存在的，next-key lock 会退化成间隙锁，因此最终加锁范围是 (8,16)

唯一索引范围查询

会话 1 加锁变化过程如下：

• 最开始要找的第一行是 id = 8，因此 next-key lock(4,8]，但由于 id 是唯一索引，且该记录是存在的，因此会退化成记录锁，也就是只会对 id = 8 这一行加锁；
• 由于是范围查找，就会继续往后找存在的记录，也就是会找到 id = 16 这一行停下来，然后加 next-key lock (8, 16]，但由于 id = 16 不满足 id < 9，所以会退化成间隙锁，加锁范围变成 (8, 16)。
• 所以，会话 1 这时候主键索引的锁是记录锁 id=8 和间隙锁 (8, 16)。

非唯一索引等值查询

1. 当查询的记录存在时，除了会加 next-key lock锁外，还会额外加间隙锁（规则是向下遍历到第一个不符合条件的值才能停止），也就是会加两把锁

会话 1 加锁变化过程如下：

• 先会对普通索引 b 加上 next-key lock，范围是 (4,8]
• 然后因为是非唯一索引，且查询的记录是存在的，所以还会加上间隙锁，规则是向下遍历到第一个不符合条件的值才能停止，因此间隙锁的范围是 (8,16)
• 所以，会话1的普通索引 b 上共有两把锁，分别是 next-key lock (4,8] 和 间隙锁 (8,16)

2. 当查询的记录不存在时，只会加 next-key lock锁，然后会退化成间隙锁，也就是只会加一把锁

会话 1 加锁变化过程如下：

• 先会对普通索引 b 加上 next-key lock，范围是 (8,16]
• 但由于查询的记录是不存在的，所以不会再额外加间隙锁，但是 next-key lock 会退化成间隙锁，最终加锁范围是 (8,16)

非唯一索引范围查询

非唯一索引和唯一索引的范围查询的加锁规则也有所不同，不同之处在于非唯一索引范围查询，next-key lock 不会退化成间隙锁和记录锁。

会话 1 加锁变化过程如下：

• 最开始要找的第一行是 b = 8，因此 next-key lock (4,8]，但由于 b 不是唯一索引，并不会退化成记录锁。
• 但由于是范围查找，就会继续往后找存在的记录，也就是会找到 b = 16 这一行停下来，然后加 next-key lock (8, 16]，因为是非唯一索引范围查询，所以并不会退化成间隙锁。
• 所以，会话 1 的普通索引 b 有两个 next-key lock，分别是 (4,8] 和 (8, 16]。

MySQL 死锁相关问题

死锁的发生

在 t_order 表里已经有了 6 条记录

假设这时有两个事务，一个事务要插入订单 1007 ，另外一个事务要插入订单 1008，因为需要对订单做幂等性校验，所以这两个事务先要查询该订单是否存在，不存在才插入记录，过程如下：

可以看到，两个事务都陷入了等待状态（前提没有打开死锁检测），也就是发生了死锁，因为都在相互等待对方释放锁。

为什么会产生 MySQL 死锁？

事务A在执行下面这条语句的时候：

select id from t_order where order_no = 1007 for update;
1

因为 order_no 不是唯一索引，所以行锁的类型是间隙锁，于是间隙锁的范围是（1006, +∞）。那么，当事务 B 往间隙锁里插入 id = 1008 的记录就会被锁住。这是为什么呢？接着看下面的讲解！！！

因为当我们执行以下插入语句时，会在插入间隙上再次获取插入意向锁。

Insert into t_order (order_no, create_date) values (1008, now());
1

插入意向锁与间隙锁是冲突的，所以当其它事务持有该间隙的间隙锁时，需要等待其它事务释放间隙锁之后，才能获取到插入意向锁。而间隙锁与间隙锁之间是兼容的，所以两个事务中 select … for update 语句并不会相互影响。

案例中的事务 A 和事务 B 在执行完 select … for update 语句后都持有范围为 (1006, +∞) 的间隙锁，而接下来的插入操作为了获取到插入意向锁，都在等待对方事务的间隙锁释放，于是就造成了循环等待，导致死锁。

为什么间隙锁与间隙锁之间是兼容的？

间隙锁在本质上是不区分共享间隙锁或互斥间隙锁的，而且间隙锁是不互斥的，即两个事务可以同时持有包含共同间隙的间隙锁。

间隙锁本质上是用于阻止其他事务在该间隙内插入新记录，而自身事务是允许在该间隙内插入数据的。也就是说间隙锁的应用场景包括并发读取、并发更新、并发删除和并发插入。

插入意向锁是什么？

注意！插入意向锁名字虽然有意向锁，但它并不是意向锁，而是一种特殊的间隙锁。

插入意向锁是一种特殊的间隙锁，但不同于间隙锁的是，该锁只用于并发插入操作。

插入意向锁与间隙锁的另一个非常重要的差别是：尽管「插入意向锁」也属于间隙锁，但两个事务却不能在同一时间内，一个拥有间隙锁，另一个拥有该间隙区间内的插入意向锁（当然，插入意向锁如果不在间隙锁区间内则是可以的）。

插入意向锁的生成时机：

每插入一条新记录，都需要看一下待插入记录的下一条记录上是否已经被加上了间隙锁，如果已加间隙锁，那 Insert 语句应该被阻塞，并生成一个插入意向锁 。

Insert 语句是怎么加行级锁的？

Insert 语句在正常执行时是不会生成锁结构的，它是靠聚簇索引记录自带的 trx_id 隐藏列来作为隐式锁来保护记录的。

什么是隐式锁？

当事务需要加锁时，如果这个锁不可能发生冲突，InnoDB 会跳过加锁环节，这种机制称为隐式锁。隐式锁是 InnoDB 实现的一种延迟加锁机制，其特点是只有在可能发生冲突时才加锁，从而减少了锁的数量，提高了系统整体性能。

隐式锁就是在 Insert 过程中不加锁，只有在特殊情况下，才会将隐式锁转换为显式锁，这里我们列举两个场景。

1. 如果记录之间加有间隙锁，为了避免幻读，此时是不能插入记录的

每插入一条新记录，都需要看一下待插入记录的下一条记录上是否已经被加了间隙锁，如果已加间隙锁，那 Insert 语句应该被阻塞，并生成一个插入意向锁。

2. 如果 Insert 的记录和已有记录存在唯一键冲突，此时也不能插入记录

如果是主键值重复，插入新记录的事务会给已存在的主键值重复的聚簇索引记录添加 S 型记录锁。

如果是唯一二级索引列重复，插入新记录的事务都会给已存在的二级索引列值重复的二级索引记录添加 S 型 next-key 锁。

如何避免死锁？

1. 设置事务等待锁的超时时间

当一个事务的等待时间超过该值后，就对这个事务进行回滚，于是锁就被释放了，另一个事务就可以继续执行了。在 InnoDB 中，参数 innodb_lock_wait_timeout 是用来设置超时时间的，默认值是 50 秒。

当发生超时后，就出现下面这个提示：

2. 开启主动死锁检测

主动死锁检测在发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑，默认就是开启的。

当检测到死锁后，就会出现下面这个提示：

23.更新语句的流程会涉及到 undo log（回滚日志）、redo log（重做日志） 、binlog （归档日志）这三种日志：

1. undo log（回滚日志）：是 InnoDB 存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和 MVCC（多版本并发控制）。
2. redo log（重做日志）：是 InnoDB 存储引擎层生成的日志，实现了事务中的持久性，主要用于掉电、宕机等故障恢复。
3. binlog（归档日志）：是 Server 层生成的日志，主要用于数据备份和主从复制。

24.为什么需要 undo log？

undo log（回滚日志），它保证了事务 ACID 特性中的原子性。

undo log 是一种用于撤销回退的日志。在事务还没提交之前，MySQL 会先记录更新前的数据到 undo log 日志文件里面，当事务回滚时，可以利用 undo log 来进行回滚。如图：

一条记录的每一次更新操作产生的 undo log 格式都有一个 roll_pointer 指针和一个 trx_id 事务id：

通过 trx_id 可以知道该记录是被哪个事务修改的

通过 roll_pointer 指针就可以将这些 undo log 串成一个链表，这个链表被称为版本链

通过 ReadView + undo log 可以实现 MVCC（多版本并发控制）

1. 对于「读提交」和「可重复读」隔离级别的事务来说，它们的快照读（普通 select 语句）是通过 Read View + undo log 来实现的，它们的区别在于创建 Read View 的时机不同：

「读提交」隔离级别是在每个 select 语句都会生成一个新的 Read View，也就意味着，事务期间的多次读取同一条数据，前后两次读取的数据可能会出现不一致，因为可能这期间另外一个事务修改了该记录，并提交了事务。

「可重复读」隔离级别是在开启事务时生成一个 Read View，然后整个事务期间都在用这个 Read View，这样就保证了在事务期间读到的数据都是事务启动前的记录。

2. 这两个隔离级别的实现是通过「事务的 Read View 里的字段」和「记录中的两个隐藏列（trx_id 和 roll_pointer）」的比对，如果不满足可见性，就会顺着 undo log 版本链找到满足其可见性的记录，从而控制并发事务访问同一个记录时的行为，这就叫 MVCC（多版本并发控制）。

undo log 两大作用

1. 实现事务回滚，保障事务的原子性。事务执行过程中，如果出现了错误或者用户执行了 ROLLBACK 语句，MySQL 可以利用 undo log 中的历史数据将数据恢复到事务开始前的状态。
2. 实现 MVCC（多版本并发控制）关键因素之一。MVCC 是通过 ReadView + undo log 实现的。undo log 为每条记录保存多份历史数据，MySQL 在执行快照读（普通 select 语句）的时候，会根据事务的 Read View 里的信息，顺着 undo log 的版本链找到满足其可见性的记录。

25.Buffer Pool 缓存什么？

有了 Buffer Poo 后：

• 当读取数据时，如果数据存在于 Buffer Pool 中，客户端就会直接读取 Buffer Pool 中的数据，否则再去磁盘中读取。
• 当修改数据时，如果数据存在于 Buffer Pool 中，那直接修改 Buffer Pool 中数据所在的页，然后将其页设置为脏页（该页的内存数据和磁盘上的数据已经不一致），为了减少磁盘 I/O，不会立即将脏页写入磁盘，而是由后台线程选择一个合适的时机将脏页写入到磁盘。

在 MySQL 启动时，InnoDB 会为 Buffer Pool 申请一块连续的内存空间，然后按照默认的 16KB 的大小划分出一个个的页， Buffer Pool 中的页叫做缓存页。此时这些缓存页都是空闲的，之后随着程序的运行，才会有磁盘上的页被缓存到 Buffer Pool 中。

Buffer Pool 除了缓存「索引页」和「数据页」，还包括 Undo 页，插入缓存、自适应哈希索引、锁信息等等。

Undo 页记录什么？

开启事务后，InnoDB 层更新记录前，首先要记录相应的 undo log，如果是更新操作，需要把被更新的列的旧值记下来，也就是要生成一条 undo log，undo log 会写入到 Buffer Pool 中的 Undo 页面。

查询一条记录，就只需要缓存一条记录吗？

不是的。

当我们查询一条记录时，InnoDB 是会把该记录所在的整个页的数据加载到 Buffer Pool 中，将页加载到 Buffer Pool 后，再通过页里的「页目录」去定位到某条具体的记录。

26.为什么需要 redo log？

什么是 redo log？

redo log 是物理日志，记录了某个数据页做了什么修改，对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了 AAA 更新，每执行一个事务就会产生一条或者多条物理日志。

在事务提交时，只要先将 redo log 持久化到磁盘，不需要等到缓存在 Buffer Pool 中的脏页数据持久化到磁盘。此时系统发生崩溃，虽然脏页数据没有持久化，但是 redo log 已经持久化了，接着 MySQL 重启后，可以根据 redo log 的内容，将所有数据恢复到最新状态。

Buffer Pool 是基于内存的，而内存总是不可靠的，万一断电重启，那么还没来得及落盘的脏页数据就会丢失。

为了防止断电导致数据丢失的问题，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log 里面，并更新内存，这时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，由后台线程将缓存在 Buffer Pool 中的脏页数据刷新到磁盘里，这就是 WAL （Write-Ahead Logging）技术，指的是 MySQL 的写操作并不是立刻更新到磁盘上，而是先记录在日志上，然后在合适的时间再更新到磁盘上。

被修改 Undo 页面，需要记录对应的 redo log 吗？

需要的。

开启事务后，InnoDB 层更新记录前，首先要记录相应的 undo log，如果是更新操作，需要把被更新的列的旧值记下来，也就是要生成一条 undo log，undo log 会写入 Buffer Pool 中的 Undo 页面。

不过，在修改该 Undo 页面前需要先记录对应的 redo log，所以先记录修改 Undo 页面的 redo log ，然后再真正的修改 Undo 页面。

redo log 和 undo log 有什么区别？

这两种日志都属于 InnoDB 存储引擎的日志。

redo log 记录了此次事务「完成后」的数据状态，记录的是更新之「后」的值

undo log 记录了此次事务「开始前」的数据状态，记录的是更新之「前」的值

事务提交之前发生了崩溃，重启后会通过 undo log 回滚事务，事务提交之后发生了崩溃，重启后会通过 redo log 恢复事务，如图：

所以有了 redo log，再通过 WAL 技术，InnoDB 就可以保证即使数据库发生异常重启，之前已经提交的记录都不会丢失，这个能力称为 crash-safe（崩溃恢复）。可以看出， redo log 保证了事务四大特性中的持久性。

redo log 要写到磁盘，脏页数据也要写到磁盘，为什么要多此一举呢？

服务器在启动时就已经给 redo log 日志文件分配好了一块物理上连续的磁盘空间，每次写 redo log 日志都是往文件中追加写，并没有寻址的过程，所以磁盘操作是顺序写。

而修改过的数据页要写到磁盘，需要先在磁盘找到写入位置，然后才写到磁盘，所以磁盘操作是随机写。

磁盘的「顺序写 」比「随机写」 高效的多，因此 redo log 写入磁盘的开销更小。

针对「顺序写」为什么比「随机写」更快这个问题，可以比喻为你有一个本子，按照顺序一页一页写肯定比写一个字都要找到对应页写快得多。

可以说这是 WAL 技术的另外一个优点：MySQL 的写操作从磁盘的「随机写」变成了「顺序写」，提升语句的执行性能。这是因为 MySQL 的写操作并不是立刻更新到磁盘上，而是先记录在日志上，然后在合适的时间再更新到磁盘上 。

针对为什么需要 redo log 这个问题我们有两个答案：

1. 实现事务的持久性，让 MySQL 有 crash-safe 的能力，能够保证 MySQL 在任何时间段突然崩溃，重启后之前已提交的记录都不会丢失。
2. 将写操作从「随机写」变成「顺序写」，提升 MySQL 写入磁盘的性能

产生的 redo log 是直接写入磁盘的吗？

不是的。

实际上， 每执行一个事务，产生的 redo log 也不是直接写入磁盘的，因为这样会产生大量的 I/O 操作，而且磁盘的运行速度远慢于内存。

所以，redo log 也有自己的缓存 —— redo log buffer，每当产生一条 redo log 时，会先写入到 redo log buffer，后续再持久化到磁盘，如图：

redo log buffer 默认大小为 16 MB，可以通过 innodb_log_Buffer_size 参数动态调整大小，增加它的大小可以让 MySQL 处理「大事务」时不必写入磁盘，进而提升写 IO 性能。

缓存在 redo log buffe 里的 redo log 还是在内存中，它什么时候刷新到磁盘？

主要有以下几个时机：

1. MySQL 正常关闭时
2. 当 redo log buffer 中记录的写入量大于 redo log buffer 内存空间的一半时，会触发落盘
3. InnoDB 的后台线程每隔 1 秒，会将 redo log buffer 持久化到磁盘
4. 每次事务提交时都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘（这个策略可由 innodb_flush_log_at_trx_commit 参数控制，下面会说）

innodb_flush_log_at_trx_commit 参数控制的是什么？

默认行为：单独执行一个更新语句的时候，InnoDB 引擎会自己启动一个事务，在执行更新语句的过程中，生成的 redo log 先写入到 redo log buffer 中，然后等事务提交的时候，再将缓存到 redo log buffer 中的 redo log 按组的方式「顺序写」到磁盘。

除此之外，InnoDB 还提供了另外两种策略，由参数 innodb_flush_log_at_trx_commit 参数控制，可取的值有：0、1、2，默认值为 1，这三个值分别代表的策略为：

1. 当设置该参数为 0 时，表示每次事务提交时 ，还是将 redo log 留在 redo log buffer 中 ，该模式下在事务提交时不会主动触发写入磁盘的操作。

2. 当设置该参数为 1 时，表示每次事务提交时，都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘，这样可以保证 MySQL 异常重启后数据不会丢失。

3. 当设置该参数为 2 时，表示每次事务提交时，都只是将缓存在 redo log buffer 里的 redo log 写入到 redo log 文件。

注意写入到「 redo log 文件」并不意味着写到了磁盘，因为在操作系统的文件系统中还有个 Page Cache，Page Cache 是专门用来缓存文件数据的，所以写入到「 redo log文件」意味着写入到了操作系统的文件缓存中。

redo log 和 redo log 文件 有什么区别？

redo log 表示一条修改记录的日志，这些日志都要保存到 redo log 文件中

innodb_flush_log_at_trx_commit 为 0 或 2 时，什么时候才将 redo log 写入磁盘？

针对参数 0 ：InnoDB 的后台线程每隔 1 秒，会把缓存在 redo log buffer 里的 redo log，通过调用 write() 写到操作系统的 Page Cache 中，然后调用 fsync() 持久化到磁盘。所以参数为 0 的策略，如果 MySQL 崩溃了会导致上一秒钟所有事务数据的丢失。

针对参数 2 ：InnoDB 的后台线程每隔 1 秒，会调用 fsync()，将缓存在操作系统的 Page Cache 里的 redo log 持久化到磁盘。所以参数为 2 的策略，相比于参数为 0 的情况下更安全，如果 MySQL 崩溃了也不会丢失数据，只有在操作系统崩溃或者系统断电的情况下，上一秒钟的所有事务数据才可能丢失。

加入后台线程后，innodb_flush_log_at_trx_commit 的刷盘时机如图：

innodb_flush_log_at_trx_commit 这三个参数的应用场景是什么？

1. 数据安全性：参数 1 > 参数 2 > 参数 0
2. 写入性能：参数 0 > 参数 2 > 参数 1
3. 在一些对数据安全性要求比较高的场景中，显然 innodb_flush_log_at_trx_commit 参数需要设置为 1。
4. 在一些可以容忍数据库崩溃时丢失 1s 数据的场景中，我们可以将该值设置为 0，这样可以明显地减少日志同步到磁盘的 I/O 操作。
5. 安全性和性能折中的方案就是参数 2，虽然参数 2 没有参数 0 的性能高，但是数据安全性方面比参数 0 强，因为参数 2 只要操作系统不宕机，即使数据库崩溃了，也不会丢失数据，同时性能方面比参数 1 高。

redo log 文件如果写满了怎么办？

默认情况下， InnoDB 存储引擎有 1 个重做日志文件组（redo log Group），「重做日志文件组」由 4 个 redo log 文件组成，这四个 redo 日志的文件名叫 ：ib_logfile0、ib_logfile1、ib_logfile2、ib_logfile3

在重做日志文件组中，每个 redo log File 的大小是固定且一致的，假设每个 redo log File 设置的上限是 1 GB，那么总共就可以记录 4GB 的操作。

重做日志文件组是以循环写的方式工作的，从头开始写，写到末尾就又回到开头，相当于一个环形。所以 InnoDB 存储引擎会先写 ib_logfile0 文件，当 ib_logfile0 文件被写满时，会切换到 ib_logfile1 文件，当 ib_logfile1 文件也被写满时，会切换到 ib_logfile2 文件，依次循环反复。

redo log 是为了防止 Buffer Pool 中的脏页丢失而设计的，随着系统的运行，Buffer Pool 中的脏页刷新到了磁盘中，那么 redo log 对应的记录也就没用了，这时候我们要擦除这些旧记录，以腾出空间记录新的更新操作。

redo log 是循环写的方式，相当于一个环形，InnoDB 用 write pos 表示当前记录写到的位置，用 check point 表示当前要擦除的位置，如图：

• write pos 和 check point 的移动方向都是顺时针的
• write pos ～ checkpoint 之间的部分（图中绿色部分）：用来记录新的更新操作
• check point ～ write pos 之间的部分（图中浅色部分）：待落盘的脏页数据

如果 write pos 追上了 check point，就意味着 redo log 文件满了，这时候 MySQL 不能再执行新的更新操作，也就是说 MySQL 会被阻塞（因此针对并发量大的系统，适当调整 redo log 的文件大小非常重要），此时会停下来将 Buffer Pool 中的脏页数据刷新到磁盘中，然后标记 redo log 哪些记录可以被擦除，接着对旧的 redo log 记录进行擦除，等擦除完旧记录腾出了空间，check point 就会往后移动（图中顺时针），然后 MySQL 恢复正常运行，继续执行新的更新操作。

所以，一次 check point 的过程就是将脏页刷新到磁盘中变成干净页，然后标记 redo log 哪些记录可以被覆盖的过程。

27.为什么需要 binlog？

MySQL 在完成一条更新操作后，server 层还会生成一条 binlog，等之后事务提交时，会将该事务执行过程中产生的所有 binlog 统一写入到 binlog 文件。

binlog 文件是记录所有数据库表结构变更和表数据修改的日志，不会记录查询类的操作，比如 SELECT 和 SHOW 操作。

为什么有了 binlog， 还要有 redo log？

1. 最开始 MySQL 里并没有 InnoDB 引擎，MySQL 自带的引擎是 MyISAM，但是 MyISAM 并没有 crash-safe 的能力，binlog 日志只能用于归档。
2. 而 InnoDB 是另一家公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB 使用 redo log 来实现 crash-safe 能力。

redo log 和 binlog 有什么区别？

1. 适用对象不同

binlog 是 MySQL 的 server 层实现的日志，所有存储引擎都可以使用

redo log 是 InnoDB 引擎特有的

2. 文件格式不同

binlog 有 3 种格式类型，分别是：

statement
- 每一条修改数据的 SQL 都会被记录到 binlog 中（相当于记录了逻辑操作，所以针对这种格式， binlog 可以称为逻辑日志），主从复制中 slave 端再根据 SQL 语句重现。
- 但 statement 格式有动态函数的问题，比如你用了 uuid 或者 now 这些函数，你在主库上执行的结果并不是你在从库上执行的结果，这种随时在变的函数会导致复制的数据不一致。

row
- 记录行数据最终被修改成什么样（这种格式的日志，就不能称为逻辑日志了），不会出现 statement 格式下动态函数的问题。
- 但 row 格式的缺点是每行数据的变化结果都会被记录，比如执行批量 update 语句，更新多少行数据就会产生多少条记录，使得 binlog 文件过大，而在 statement 格式下只会记录一个 update 语句即可。

mixed
- 包含了 statement 和 row，它会根据不同的情况自动选择 row 格式或 statement 格式。
1
2
3
4
5
6
7
8
9
10
11
12

redo log 是物理日志，记录的是在某个数据页做了什么修改，比如对 XXX 表空间中的 YYY 数据页 ZZZ 偏移量的地方做了 AAA 更新。
1

3. 写入方式不同

binlog 是追加写，写满一个文件，就会创建一个新的文件继续写，不会覆盖以前的日志，保存的是全量的日志。

redo log 是循环写，日志空间大小是固定的，全部写满就从头开始，保存未被刷入磁盘的脏页日志。

4. 用途不同

binlog 用于备份恢复、主从复制

redo log 用于掉电、宕机等故障恢复

如果不小心把整个数据库的数据都删除了，能使用 redo log 文件恢复数据吗？

不能使用 redo log 文件恢复，只能使用 binlog 文件恢复。

因为 redo log 文件是循环写，是会边写边擦除日志的，只会记录未被刷入磁盘的脏页数据的物理日志，已经刷入磁盘的数据都会从 redo log 文件中擦除。

binlog 文件保存的是全量的日志，也就是保存了所有数据变更的情况，理论上只要记录在 binlog 上的数据都可以恢复。所以如果不小心把整个数据库的数据都删除了，得用 binlog 文件恢复数据。

binlog 什么时候刷盘？

事务执行过程中，先把日志写到 binlog cache（server 层的 cache），事务提交的时候，再把 binlog cache 写到 binlog 文件中。

MySQL 给 binlog cache 分配了一块内存，每个线程一个，参数 binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小。如果超过这个参数规定的大小，就要暂存到磁盘。

什么时候 binlog cache 会写到 binlog 文件？

在事务提交的时候，执行器会把 binlog cache 里的完整事务写入到 binlog 文件中，并清空 binlog cache。如图所示：

虽然每个线程都有自己的 binlog cache，但最终都要写到同一个 binlog 文件

图中的 write，指的就是把日志写入到 binlog 文件，但是并没有将数据持久化到磁盘，因为数据还缓存在文件系统的 Page Cache 里，write 的写入速度还是比较快的，因为不涉及磁盘的 I/O 操作。

图中的 fsync，才是将数据持久化到磁盘的操作，这里就会涉及到磁盘的 I/O 操作，所以频繁的 fsync 会导致磁盘的 I/O 升高。

MySQL提供了一个 sync_binlog 参数用来控制数据库的 binlog 刷新到磁盘的频率：

1. sync_binlog = 0 时，表示每次提交事务都只 write，不 fsync，后续交由操作系统决定何时将数据持久化到磁盘
2. sync_binlog = 1 时，表示每次提交事务都会 write，然后马上执行 fsync
3. sync_binlog = N（N > 1）时，表示每次提交事务都 write，但累积 N 个事务后才执行 fsync

在 MySQL 中系统默认的设置是 sync_binlog = 0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但也是风险最大的。因为一旦操作系统发生异常重启，还没持久化到磁盘的数据就会丢失。

而当 sync_binlog 设置为 1 时，是最安全但性能损耗是最大的。因为当设置为 1 时，即使操作系统发生异常重启，最多丢失一个事务的 binlog，而已经持久化到磁盘的数据不会受到影响，不过就是对写入性能影响太大了。

如果能允许少量事务的 binlog 日志丢失的风险，为了提高写入的性能，一般会将 sync_binlog 设置为 100~1000 中的某个值。

28.为什么需要两阶段提交？

事务提交后，redo log 和 binlog 都要持久化到磁盘。但这两个是独立的逻辑，可能出现半成功的状态，这样就会造成两份日志之间的逻辑不一致。

举个例子，假设 id = 1 这行数据的字段 name 的值原本是 ‘jay’，然后执行 UPDATE t_user SET name = 'xiaolin' WHERE id = 1; 如果在持久化 redo log 和 binlog 这两个日志的过程中，出现了半成功状态，那么就有两种情况：

1. 如果在将 redo log 刷新到磁盘后， MySQL 突然宕机了，而 binlog 还没来得及写入。MySQL 重启后，通过 redo log 能够将 Buffer Pool 中 id = 1 这行数据的 name 字段恢复到新值 xiaolin，但是 binlog 里面并没有记录这条更新语句。在主从架构中，binlog 会被复制到从库，由于 binlog 丢失了这条更新语句，从库的这一行 name 字段依然是旧值 jay，与主库的值不一致。
2. 如果在将 binlog 刷新到磁盘后， MySQL 突然宕机了，而 redo log 还没来得及写入。由于 redo log 还没写，崩溃恢复以后这个事务无效，所以 id = 1 这行数据的 name 字段依然还是旧值 jay，而 binlog 里面记录了这条更新语句。在主从架构中，binlog 会被复制到从库，从库执行了这条更新语句，那么这一行数据的 name 字段是新值 xiaolin，与主库的值不一致。

可以看到，在持久化 redo log 和 binlog 这两份日志的时候，如果出现半成功状态，就会造成主从库环境的数据不一致性。这是因为 redo log 影响主库的数据，binlog 影响从库的数据，所以 redo log 和 binlog 必须保持一致才能保证主从库数据的一致性。

MySQL 为了避免出现两份日志之间的逻辑不一致问题，使用了「两阶段提交」来解决，两阶段提交其实是分布式事务的一致性协议，它可以保证多个逻辑操作要么全部成功，要么全部失败，不会出现半成功的状态。

两阶段提交就是把单个事务的提交拆分成了 2 个阶段，分别是「准备（Prepare）阶段」和「提交（Commit）阶段」。

注意，不要把提交（Commit）阶段和 commit 语句混淆了，commit 语句执行的时候，会包含提交（Commit）阶段。

29.两阶段提交的过程是怎样的？

在 MySQL 的 InnoDB 存储引擎中，开启 binlog 的情况下，MySQL 会同时维护 binlog 和 redo log，为了保证这两个日志的一致性，MySQL 使用了内部 XA 事务，内部 XA 事务由 binlog 作为协调者，存储引擎是参与者。

当客户端执行 commit 语句或者在自动提交的情况下，MySQL 内部会开启一个 XA 事务，分两阶段来完成 XA 事务的提交，如图：

从图中可以看出，事务的提交过程有两个阶段，就是将 redo log 的写入拆成了两个步骤：prepare 和 commit，中间再穿插写入binlog，具体如下：

1. prepare 阶段：将 XID（内部 XA 事务的 ID） 写入到 redo log，同时将 redo log 对应的事务状态设置为 prepare，然后将 redo log 刷新到磁盘
2. commit 阶段：把 XID 写入到 binlog，然后将 binlog 刷新到磁盘，接着调用存储引擎的提交事务接口，将 redo log 状态设置为 commit（只是修改成 commit 状态，在 prepare 阶段 redo log 就已经刷盘了）

异常重启会出现什么现象？

不管是时刻 A（已经写入 redo log，还没写入 binlog），还是时刻 B （已经写入 redo log 和 binlog，还没写入 commit 标识）崩溃，此时的 redo log 都处于 prepare 状态。

在 MySQL 重启后会按顺序扫描 redo log 文件，碰到处于 prepare 状态的 redo log，就拿着 redo log 中的 XID 去 binlog 中查看是否存在此 XID：

1. 如果 binlog 中没有当前内部 XA 事务的 XID，说明 redo log 已经刷盘，但是 binlog 还没有刷盘，则回滚事务。（对应时刻 A 崩溃恢复的情况）
2. 如果 binlog 中有当前内部 XA 事务的 XID，说明 redo log 和 binlog 都已经完成了刷盘，则提交事务。（对应时刻 B 崩溃恢复的情况）

可以看到，对于处在 prepare 阶段的 redo log，即可以提交事务，也可以回滚事务，这取决于是否能在 binlog 中找到与 redo log 相同的 XID，如果有就提交事务，如果没有就回滚事务。这样就可以保证 redo log 和 binlog 这两份日志的一致性了。

所以说，两阶段提交是以 binlog 写成功为事务提交成功的标识，因为 binlog 写成功了，就意味着能在 binlog 中找到与 redo log 相同的 XID。

处于 prepare 阶段的 redo log 加上完整的 binlog，重启就提交事务，MySQL 为什么要这样设计呢?

因为 binlog 已经写入了，之后就会被从库（或者用这个 binlog 恢复出来的库）使用。

所以，在主库上也要提交这个事务。采用这个策略，主库和备库的数据就保证了一致性。

事务还没提交的时候，redo log 会被持久化到磁盘吗？

会的。

事务执行中间过程的 redo log 也是直接写在 redo log buffer 中的，这些缓存在 redo log buffer 里的 redo log 也会被「后台线程」每隔一秒一起持久化到磁盘。

也就是说，事务还没提交时，redo log 也是有可能被持久化到磁盘的。

如果 mysql 崩溃了，还没提交事务的 redo log 已经被持久化到磁盘了，mysql 重启后，数据不就不一致了吗？

这种情况 mysql 重启后会进行回滚操作，因为在事务没提交时，binlog 是还没有持久化到磁盘的。

所以，redo log 可以在事务没提交之前持久化到磁盘，但 binlog 必须在事务提交之后，才能持久化到磁盘。

两阶段提交有什么问题？

两阶段提交虽然保证了两个日志文件的数据一致性，但是性能很差，主要有两方面的影响：

1. 磁盘 I/O 次数高：对于 “双1” 配置，每个事务提交都会进行两次 fsync（刷盘），一次是 redo log 刷盘，另一次是 binlog 刷盘。
2. 锁竞争激烈：两阶段提交虽然能保证「单事务」两个日志的内容一致，但在「多事务」情况下，却不能保证两者的提交顺序一致。因此，在两阶段提交的流程基础上，还需要加一把锁来保证提交的原子性，从而保证在「多事务」情况下，两个日志的提交顺序是一致的。

为什么两阶段提交的磁盘 I/O 次数会很高？

binlog 和 redo log 在内存中都对应着缓存空间，binlog 会缓存在 binlog cache，redo log 会缓存在 redo log buffer。它们持久化到磁盘的时机分别由下面这两个参数控制。一般我们为了避免日志丢失的风险，会将这两个参数设置为 1：

1. 当 sync_binlog = 1 时，表示每次事务提交都会将 binlog cache 里的 binlog 直接持久化到磁盘
2. 当 innodb_flush_log_at_trx_commit = 1 时，表示每次事务提交都会将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘

可以看到，如果 sync_binlog 和 innodb_flush_log_at_trx_commit 都设置为 1，那么在每次事务提交过程中， 都会至少调用 2 次刷盘操作，一次是 redo log 刷盘，一次是 binlog 落盘，所以这会成为性能瓶颈。

为什么锁竞争激烈？

在早期的 MySQL 版本中，通过使用 prepare_commit_mutex 锁来保证事务提交的顺序，在一个事务获得锁时才能进入 prepare 阶段，一直到 commit 阶段结束才能释放锁，下一个事务才能继续进行 prepare 操作。

通过加锁虽然能完美地解决顺序一致性的问题，但在并发量较大时，就会导致对锁的争用，性能不佳。

组提交

MySQL 引入了 binlog 组提交（group commit）机制，当有多个事务提交时，会将多个 binlog 刷盘操作合并成一个，从而减少磁盘 I/O 的次数，如果说 10 个事务依次排队刷盘的时间成本是 10，那么将这 10 个事务一次性一起刷盘的时间成本则近似于 1。

引入了组提交机制后，prepare 阶段不变，只针对 commit 阶段，将 commit 阶段拆分成三个过程：

1. flush 阶段：多个事务按进入的顺序将 binlog 从 binlog cache 写入文件（不刷盘）
2. sync 阶段：对 binlog 文件做 fsync 操作（多个事务的 binlog 合并一次刷盘）
3. commit 阶段：各个事务按顺序做 InnoDB commit 操作

上面的每个阶段都有一个队列，每个阶段都有锁进行保护，因此保证了事务写入的顺序，第一个进入队列的事务会成为 leader，leader 领导所在队列的所有事务，全权负责整队的操作，完成后通知队内其他事务操作结束。

对每个阶段引入队列后，锁就只针对每个队列进行保护，不再锁住提交事务的整个过程。可以看出来，锁粒度变小了，这样就使得多个阶段可以并发执行，从而提高效率。

有 binlog 组提交，那有 redo log 组提交吗？

这个要看 MySQL 版本，MySQL 5.6 没有 redo log 组提交，MySQL 5.7 有 redo log 组提交。

在 MySQL 5.6 的组提交逻辑中，每个事务各自执行 prepare 阶段，也就是各自将 redo log 刷盘，这样就没办法对 redo log 进行组提交。

所以在 MySQL 5.7 版本中，做了个改进，在 prepare 阶段不再让事务各自执行 redo log 刷盘操作，而是推迟到组提交的 flush 阶段，也就是将 prepare 阶段融合在了 flush 阶段。

这个优化是将 redo log 的刷盘延迟到了 flush 阶段之中，sync 阶段之前。通过延迟写 redo log 的方式，为 redo log 做了一次组写入，这样 binlog 和 redo log 就都进行了优化。

介绍每个阶段的过程，注意下面的过程针对的是 “双 1” 配置（sync_binlog 和 innodb_flush_log_at_trx_commit 都配置为 1）

flush 阶段

第一个事务会成为 flush 阶段的 Leader，此时后面到来的事务都是 Follower ：

接着，获取队列中的事务组，由绿色事务组的 Leader 对 rodo log 做一次 write + fsync，即一次性将同组事务的 redo log 刷盘：

完成了 prepare 阶段后，将绿色这一组事务执行过程中产生的 binlog 写入 binlog 文件（调用 write，不会调用 fsync，所以不会刷盘，binlog 缓存在操作系统的文件系统中）。

从上面这个过程，可以知道 flush 阶段队列的作用是用于支持 redo log 的组提交。

如果在这一步完成后数据库崩溃，由于 binlog 中没有该组事务的记录，所以 MySQL 会在重启后回滚该组事务。

sync 阶段

绿色这一组事务的 binlog 写入到 binlog 文件后，并不会马上执行刷盘操作，而是会等待一段时间，这个等待的时长由 Binlog_group_commit_sync_delay 参数控制，目的是为了组合更多事务的 binlog，然后再一起刷盘，如图：

不过，在等待的过程中，如果事务的数量提前达到了 Binlog_group_commit_sync_no_delay_count 参数设置的值，就不用继续等待了，马上将 binlog 刷盘，如图：

从上面的过程，可以知道 sync 阶段队列的作用是用于支持 binlog 的组提交。

如果想提升 binlog 组提交的效果，可以通过设置这两个参数来实现：

1. binlog_group_commit_sync_delay = N，表示在等待 N 微秒后，直接调用 fsync，将处于文件系统 Page Cache 中的 binlog 刷盘，也就是将「 binlog 文件」持久化到磁盘。

2. binlog_group_commit_sync_no_delay_count = N，表示如果队列中的事务数量达到 N 个，就忽视 binlog_group_commit_sync_delay 的设置，直接调用 fsync，将处于文件系统 Page Cache 中的 「binlog文件」 刷盘。

如果在这一步完成后数据库崩溃，由于 binlog 中已经有了事务记录，MySQL会在重启后通过 redo log 刷盘的数据继续进行事务提交。

commit 阶段

最后进入 commit 阶段，调用存储引擎的提交事务接口，将 redo log 状态设置为 commit。

commit 阶段队列的作用是承接 sync 阶段的事务，完成最后的引擎提交，使得 sync 可以尽早的处理下一组事务，最大化组提交的效率。

30.MySQL 磁盘 I/O 很高，有什么优化的方法？

事务在提交的时候，需要将 binlog 和 redo log 持久化到磁盘，那么如果出现 MySQL 磁盘 I/O 很高的现象，我们可以通过控制以下参数，来 “延迟” binlog 和 redo log 刷盘的时机，从而降低磁盘 I/O 的频率：

设置组提交的两个参数： binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 参数，延迟 binlog 刷盘的时机，从而减少 binlog 的刷盘次数。

这个方法是基于 “额外的故意等待” 来实现的，因此可能会增加语句的响应时间，但即使 MySQL 进程中途挂了，也没有丢失数据的风险，因为 binlog 早就被写入到 Page Cache 了，只要操作系统没有宕机，缓存在 Page Cache 里的 binlog 就会被持久化到磁盘。

将 sync_binlog 设置为大于 1 的值（比较常见是 100~1000），表示每次提交事务都 write，但累积 N 个事务后才 fsync，相当于延迟了 binlog 刷盘的时机。但是这样做的风险是，主机掉电时会丢失 N 个事务的 binlog 日志。

将 innodb_flush_log_at_trx_commit 设置为 2。表示每次事务提交时，都只是将缓存在 redo log buffer 里的 redo log 写到 redo log 文件。

注意写入到「 redo log 文件」并不意味着写入到了磁盘，因为操作系统的文件系统中有个 Page Cache，专门是用来缓存文件数据的，所以写入「 redo log 文件」意味着写入到了操作系统的文件缓存中，然后交由操作系统控制持久化到磁盘的时机。但是这样做的风险是，主机掉电的时候会丢失数据。

31.update 语句的执行过程

具体更新一条记录 UPDATE t_user SET name = 'xiaolin' WHERE id = 1; 的流程如下：

1. 执行器负责具体执行，会调用存储引擎的接口，通过主键索引树搜索获取 id = 1 这一行记录

如果 id=1 这一行记录所在的数据页本来就在 Buffer Pool 中，就直接返回给执行器更新

如果记录不在 Buffer Pool 中，就需要将对应的数据页从磁盘读入到 Buffer Pool，然后返回记录给执行器

2. 执行器得到聚簇索引记录后，会看一下更新前的记录和更新后的记录是否一样

如果一样就不进行后续更新流程

如果不一样就把更新前的记录和更新后的记录都当作参数传给 InnoDB 层，让 InnoDB 真正执行更新记录的操作

3. 开启事务， InnoDB 层更新记录前，首先要记录相应的 undo log，因为这是更新操作，需要把被更新的列的旧值记下来，也就是要生成一条 undo log 日志，undo log 会写入到 Buffer Pool 中的 Undo 页面，不过在修改该 Undo 页面之前需要先记录对应的 redo log，所以先记录修改 Undo 页面对应的 redo log ，然后再真正的修改 Undo 页面。
4. InnoDB 层开始更新记录，根据 WAL 技术，先记录修改数据页面的 redo log ，然后再真正的修改数据页面。修改数据页面的过程是修改 Buffer Pool 中数据所在的页，然后将其页设置为脏页，为了减少磁盘 I/O，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘。
5. 至此，一条记录更新完了。
6. 在一条更新语句执行完成后，然后开始记录该语句对应的 binlog，此时记录的 binlog 会被保存到 binlog cache 中，并没有刷新到磁盘上的 binlog 文件，当事务提交时才会统一将该事务运行过程中的所有 binlog 刷新到磁盘。
7. 事务提交（为了方便说明，这里不说组提交的过程，只说两阶段提交）：

prepare 阶段：将 redo log 对应的事务状态设置为 prepare，然后将 redo log 刷新到磁盘

commit 阶段：将 binlog 刷新到磁盘，接着调用存储引擎的提交事务接口，将 redo log 状态设置为 commit

8. 至此，一条更新语句执行完成。

32.MySQL 的主从复制是怎么实现的？

MySQL 的主从复制依赖于 binlog ，也就是记录 MySQL 上的所有变化并以二进制形式保存在磁盘上。复制的过程就是将 binlog 中的数据从主库传输到从库上。

这个过程一般是异步的，也就是主库上执行事务操作的线程不会等待复制 binlog 的线程同步完成。

MySQL 集群的主从复制过程梳理成 3 个阶段：

1. 写入 Binlog：主库写 binlog 日志，提交事务，并更新本地存储数据。

2. 同步 Binlog：把 binlog 复制到所有从库上，每个从库把 binlog 写到暂存日志中。

3. 回放 Binlog：每个从库回放 binlog，并更新存储引擎中的数据。

具体详细过程：

MySQL 主库在收到客户端提交事务的请求后，会先写入 binlog，再提交事务，更新存储引擎中的数据，事务提交完成后，返回给客户端 “操作成功” 的响应。

从库会创建一个专门的 I/O 线程，连接主库的 log dump 线程，来接收主库的 binlog 日志，再把 binlog 信息写入到 relay log 的中继日志里，再返回给主库 “复制成功” 的响应。

从库会创建一个用于回放 binlog 的线程，进行读取 relay log 中继日志，然后回放 binlog 并更新存储引擎中的数据，最终实现主从数据的一致性。

在完成主从复制后，你就可以在写数据时只写主库，读数据时只读从库，这样即使写请求会锁表或锁记录，也不会影响读请求的执行。

33.从库是不是越多越好？

不是的。

因为从库数量增加，从库连接上来的 I/O 线程也比较多，主库也要创建同样多的 log dump 线程来处理复制的请求，对主库的资源消耗比较高，同时还受限于主库的网络带宽。

所以在实际使用中，一个主库一般跟 2～3 个从库（1 套数据库：1 主 2 从 1 备主），这就是一主多从的 MySQL 集群结构。

34.MySQL 主从复制还有哪些模型？

同步复制

MySQL 主库提交事务的线程要等待所有从库的复制成功响应，才返回给客户端结果。

这种方式在实际项目中基本上没法用，原因有两个：一是性能很差，因为要复制到所有节点才返回响应；二是可用性也很差，主库和所有从库任何一个数据库出问题，都会影响业务。

异步复制（默认模型）

MySQL 主库提交事务的线程并不会等待 binlog 同步到各个从库，就返回给客户端结果。这种模式一旦主库宕机，数据就会发生丢失。

半同步复制

MySQL 5.7 版本之后增加的一种复制方式，介于两者之间，主库提交事务的线程不用等待所有的从库复制成功响应，只要一部分复制成功响应回来就行，比如一主二从的集群，只要 binlog 成功复制到任意一个从库上，主库的事务线程就可以返回给客户端。

这种半同步复制的方式，兼顾了异步复制和同步复制的优点，即使出现主库宕机，至少还有一个从库有最新的数据，不存在数据丢失的风险。