图解MySQL大全

MySQL 大多数的核心功能模块都在这实现，主要包括连接器，查询缓存、解析器、预处理器、优化器、执行器等。

另外，所有的内置函数（如日期、时间、数学和加密函数等）和所有跨存储引擎的功能（如存储过程、触发器、视图等）都在 Server 层实现。

支持 InnoDB、MyISAM、Memory 等多个存储引擎，不同的存储引擎共用一个 Server 层。

现在最常用的存储引擎是 InnoDB，从 MySQL 5.5 版本开始， InnoDB 成为了 MySQL 的默认存储引擎。

我们常说的索引数据结构，就是由存储引擎层实现的，不同的存储引擎支持的索引类型也不相同，比如 InnoDB 
支持索引类型是 B+树 ，且是默认使用，也就是说在数据表中创建的主键索引和二级索引默认使用的是 B+ 树索引。

如果你在 Linux 操作系统里要使用 MySQL，那你第一步肯定是要先连接 MySQL 服务，
然后才能执行 SQL 语句，普遍我们都是使用下面这条命令进行连接：

连接的过程需要 先经过 TCP 三次握手，因为 MySQL 是基于 TCP 协议进行传输的，如果 MySQL 服务并没有启动，则会收到如下的报错：

如果 MySQL 服务正常运行，完成 TCP 连接的建立后，连接器就要开始验证你的用户名和密码，
如果用户名或密码不对，就收到一个 "Access denied for user" 的错误，然后客户端程序结束执行。

如果用户密码都没有问题，连接器就会获取该用户的权限，然后保存起来，后续该
用户在此连接里的任何操作，都会 基于连接开始时读到的权限进行权限逻辑的判断。

所以，如果一个用户已经建立了连接，即使管理员中途修改了该用户的权限，也不会
影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。

可以执行 show processlist 命令进行查看：

比如上图的显示结果，共有两个用户名为 root 的用户连接了 MySQL 服务，其中 id 为 6 的用户的 Command 列的状态为 Sleep ，
这意味着该用户连接完 MySQL 服务就没有再执行过任何命令，也就是说这是一个空闲的连接，并且空闲的时长是 736 秒（ Time 列）。

当然不是了，MySQL 定义了 空闲连接的最大空闲时长，由 wait_timeout 参数控制的，
默认值是 8 小时（28880秒），如果空闲连接超过了这个时间，连接器就会 自动将它断开。

当然，我们自己也可以 手动断开空闲的连接，使用的是 kill connection + id 的命令。

一个处于空闲状态的连接被服务端主动断开后，这个 客户端并不会马上知道，等到客户端在 发起下一个请求的时候，
才会收到这样的报错“ERROR 2013 (HY000): Lost connection to MySQL server during query”。

MySQL 服务支持的最大连接数由 max_connections 参数控制，比如我的 MySQL 服务默认是 151 个，
超过这个值，系统就会拒绝接下来的连接请求，并报错提示“Too many connections”。

MySQL 的连接也跟 HTTP 一样，有 短连接和长连接 的概念，它们的区别如下：

可以看到，使用长连接的好处就是可以减少建立连接和断开连接的过程，所以一般是推荐使用长连接。

但是，使用长连接后可能会占用内存增多，因为 MySQL 在执行 查询过程中临时使用内存来管理连接对象，这些连接对象资源只有在连接断开时才会释放。
如果长连接累计很多，将导致 MySQL 服务 占用内存太大，有可能会被系统强制杀掉，这样会发生 MySQL 服务异常重启的现象。

第一种：定期断开长连接。既然断开连接后就会释放连接占用的内存资源，那么我们可以定期断开长连接。

第二种，客户端主动重置连接。MySQL 5.7 版本实现了 mysql_reset_connection() 函数的接口，注意这是接口函数
不是命令，那么 当客户端执行了一个很大的操作后，在代码里调用 mysql_reset_connection() 函数来重置连接，达到
 释放内存 的效果。这个过程不需要重连和重新做权限验证，但是 会将连接恢复到刚刚创建完时的状态。

至此，连接器的工作做完了，简单总结一下：

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

我们先来说说预处理阶段做了什么事情：

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

我下面这条查询语句，test 这张表是不存在的，这时 MySQL 就会在执行 SQL 查询语句的 prepare 阶段中报错。

这里贴个 MySQL 8.0 源码来证明表或字段是否存在的判断，不是在解析器里做的，而是在 prepare 阶段。

上面的中间部分是 MySQL 报错表不存在时的函数调用栈，可以看到表不存在的
错误是在 get_table_share() 函数里报错的，而这个函数是在 prepare 阶段调用的。

不过，对于 MySQL 5.7 判断表或字段是否存在的工作，是在解析器之后，prepare 阶段之前做的。

正因为 MySQL 5.7 代码结构不好，所以 MySQL 8.0 代码结构变化很大，后来判断表或字段是否存在的工作就被放入到 prepare 阶段做了。

经过预处理阶段后，还需要为 SQL 查询语句先制定一个 执行计划，这个工作交由「优化器」来完成的。

优化器主要负责将 SQL 查询语句的执行方案确定下来，比如在表里面有多个
索引的时候，优化器会基于查询成本的考虑，来决定选择使用哪个索引。

要想知道优化器选择了哪个索引，我们可以在查询语句最前面加个 explain 命令，这样就会输出这条 SQL 语句的执行
计划，然后执行计划中的 key 就表示执行过程中使用了哪个索引，比如下图的 key 为 PRIMARY 就是使用了主键索引。

如果查询语句的执行计划里的 key 为 null 说明没有使用索引，那就会全表扫描（type = ALL），这种查询扫描的方式是效率最低档次的，如下图：

这张 product 表只有一个索引就是主键，现在我在表中将 name 设置为普通索引（二级索引）。

这时 product 表就有主键索引（id）和普通索引（name）。假设执行了这条查询语句：

这条查询语句的结果既可以使用主键索引，也可以使用普通索引，但是执行的效率会不同。这时，就需要 优化器来决定使用哪个索引了。

很显然这条查询语句是 覆盖索引，直接在二级索引就能查找到结果（因为二级索引中 B+ 树的叶子节点的数据存储的是主键值），
刚好要查询的就是主键 id，所以就没必要在主键索引查找了，因为查询主键索引的 B+ 树的成本会比查询二级索引的 B+ 的成本大，
优化器基于查询成本的考虑，会选择查询代价小的普通索引。

在下图中执行计划，我们可以看到，执行过程中使用了普通索引（name），Extra 为 Using index，这就是表明使用了覆盖索引优化。

经历完优化器后，就确定了执行方案，接下来 MySQL 就 真正开始执行语句 了，这个工作是由「执行器」完成的。
在执行的过程中，执行器就会和存储引擎交互了，交互是以记录为单位的。

接下来，用三种方式执行过程，跟大家说一下执行器和存储引擎的交互过程：

• 主键索引查询
• 全表扫描
• 索引下推

主键索引查询

全表扫描

索引下推

MySQL 存储的行为是由存储引擎实现的，MySQL 支持多种存储引擎，不同的存储引擎保存的文件自然也不同。

InnoDB 是我们常用的存储引擎，也是 MySQL 默认的存储引擎。所以，本文主要以 InnoDB 存储引擎展开讨论。

先来看看 MySQL 数据库的文件存放在哪个目录？

我们每创建一个 database（数据库） 都会在 /var/lib/mysql/ 目录里面创建一个以 database 为名的目录，
然后保存表结构和表数据的文件都会存放在这个目录里。

比如，我这里有一个名为 my_test 的 database，该 database 里有一张名为 t_order 数据库表。

然后，我们进入 /var/lib/mysql/my_test 目录，看看里面有什么文件？

可以看到，共有三个文件，这三个文件分别代表着：

• db.opt，用来存储当前数据库的默认字符集和字符校验规则。

• t_order.frm ，t_order 的表结构会保存在这个文件。在 MySQL 中建立一张表都会生成一个.frm 文件，
  该文件是用来保存每个表的元数据信息的，主要包含 表结构 定义。

• t_order.ibd，t_order 的表数据会保存在这个文件。表数据既可以存在共享表空间文件（文件名：ibdata1）里，
  也可以存放在独占表空间文件（文件名：表名字.ibd）。这个行为是由参数 innodb_file_per_table 控制的，
  若设置了参数 innodb_file_per_table 为 1，则会将存储的数据、索引等信息单独存储在一个独占表空间，
  从 MySQL 5.6.6 版本开始，它的默认值就是 1 了，因此从这个版本之后， MySQL 中每一张 表的数据 都存放在一个独立的 .idb 文件。

好了，现在我们知道了一张数据库表的数据是保存在「 表名字.idb 」的文件里的，这个文件也称为独占表空间文件。

表空间由 段（segment）、区（extent）、页（page）、行（row）组成，InnoDB 的逻辑存储结构大致如下图：

1、行（row）

2、页（page）

3、区（extent）

4、段（segment）

先跟 Compact 行格式混个脸熟，它长这样：

可以看到，一条完整的记录分为「记录的额外信息」和「记录的真实数据」两个部分。

记录的额外信息包含 3 个部分：变长字段长度列表、NULL 值列表、记录头信息。

变长字段长度列表

NULL 值列表

记录头信息

记录真实数据部分除了我们定义的字段，还有 三个隐藏字段，分别为：row_id、trx_id、roll_pointer。

row_id：如果我们建表的时候 指定了主键或者唯一非空约束列，那么就没有 row_id 隐藏字段了。如果既没有指定主键，
又没有唯一约束，那么 InnoDB 就会为记录添加 row_id 隐藏字段。row_id 不是必需的，占用 6 个字节。

trx_id：事务id，表示这个数据是 由哪个事务生成的。 trx_id 是必需的，占用 6 个字节。

roll_pointer：这条记录 上一个版本的指针。roll_pointer 是必需的，占用 7 个字节。
如果你熟悉 MVCC 机制，你应该就清楚 trx_id 和 roll_pointer 的作用了

我们要清楚一点，MySQL 规定 除了 TEXT、BLOBs 这种大对象类型之外，其他所有
的列（不包括隐藏列和记录头信息）占用的字节长度 加起来不能超过 65535 个字节。

也就是说，一行记录除了 TEXT、BLOBs 类型的列，限制最大为 65535 字节，注意是一行的总长度，不是一列。

知道了这个前提之后，我们再来看看这个问题：「varchar(n) 中 n 最大取值为多少？」

varchar(n) 字段类型的 n 代表的是最多存储的字符数量，并 不是字节大小。

要算 varchar(n) 最大能允许存储的字节数，还要看数据库表的 字符集，因为字符集代表着，1个字符要占用多少
字节，比如 ascii 字符集， 1 个字符占用 1 字节，那么 varchar(100) 意味着最大能允许存储 100 字节的数据。

前面我们知道了，一行记录最大只能存储 65535 字节的数据。

那假设数据库表只有一个 varchar(n) 类型的列且字符集是 ascii，在这种情况下， varchar(n) 中 n 最大取值是 65535 吗？

不着急说结论，我们先来做个实验验证一下。

我们定义一个 varchar(65535) 类型的字段，字符集为 ascii 的数据库表。

可以看到，创建失败了：

从报错信息就可以知道 一行数据的最大字节数是 65535（不包含 TEXT、BLOBs 这种大对象类型），其中包含了 storage overhead。

问题来了，这个 storage overhead 是什么呢？其实就是「变长字段长度列表」和 「NULL 值列表」，也就是说一行数据的最大字节数 65535，其实是包含「变长字段长度列表」和 「NULL 值列表」所占用的字节数的。所以， 我们在算 varchar(n) 中 n 最大值时，需要减去 storage overhead 占用的字节数。

这是因为我们存储字段类型为 varchar(n) 的数据时，其实分成了三个部分来存储：

• 真实数据

• 真实数据占用的字节数

• NULL 标识，如果不允许为NULL，这部分不需要

前面我创建表的时候，字段是允许为 NULL 的，所以会用 1 字节来表示「NULL 值列表」。

「变长字段长度列表」所占用的字节数 = 所有「变长字段长度」占用的字节数之和。

所以，我们要先知道每个变长字段的「变长字段长度」需要用多少字节表示？具体情况分为：

• 条件一：如果变长字段允许存储的最大字节数 小于等于 255 字节，就会用 1 字节表示「变长字段长度」；

• 条件二：如果变长字段允许存储的最大字节数 大于 255 字节，就会用 2 字节表示「变长字段长度」；

我们这里字段类型是 varchar(65535) ，字符集是 ascii，所以代表着变长字段允许存储的
最大字节数是 65535，符合条件二，所以会用 2 字节来表示「变长字段长度」。

因为我们这个案例是只有 1 个变长字段，所以「变长字段长度列表」= 1 个「变长字段长度」占用的字节数，也就是 2 字节。

因为我们在算 varchar(n) 中 n 最大值时，需要减去 「变长字段长度列表」和 「NULL 值列表」所占用的字节数的。
所以，在数据库表只有一个 varchar(n) 字段且字符集是 ascii 的情况下，varchar(n) 中 n 最大值 = 65535 - 2 - 1 = 65532。

我们先来测试看看 varchar(65533) 是否可行？

可以看到，还是不行，接下来看看 varchar(65532) 是否可行？

可以看到，创建成功了。说明我们的推论是正确的，在算 varchar(n) 中 n 最大值时，
需要减去 「变长字段长度列表」和 「NULL 值列表」所占用的字节数的。

当然，我上面这个例子是针对字符集为 ascii 情况，如果采用的是 UTF-8，varchar(n) 最多能存储的数据计算方式就不一样了：

• 在 UTF-8 字符集下，一个字符串最多需要三个字节，varchar(n) 的 n 最大取值就是 65532/3 = 21844。

如果有多个字段的话，要保证所有字段的长度 + 变长字段字节数列表所占用的字节数 + NULL 值列表所占用的字节数 <= 65535。

这里举个多字段的情况的例子

从数据结构的角度来看，MySQL 常见索引有 B+Tree 索引、HASH 索引、Full-Text 索引。

每一种存储引擎支持的索引类型不一定相同，我在表中总结了 MySQL 常见的
存储引擎 InnoDB、MyISAM 和 Memory 分别支持的索引类型。

B+ Tree 简介

通过主键查询商品数据的过程

通过二级索引查询商品数据的过程

为什么 InnoDB 选择 B+tree 作为索引的数据结构？

从物理存储的角度来看，索引分为聚簇索引（主键索引/无主键的唯一非空列）、二级索引（辅助索引）。

这两个区别在前面也提到了：

• 主键索引的 B+Tree 的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的 B+Tree 的叶子节点里；
• 二级索引的 B+Tree 的叶子节点存放的是主键值，而不是实际数据。

所以，在查询时使用了二级索引，如果查询的数据能在二级索引里查询的到，那么就不需要回表，这个过程就是覆盖索引。如果查询的数据不在二级索引里，就会先检索二级索引，找到对应的叶子节点，获取到主键值后，然后再检索主键索引，就能查询到数据了，这个过程就是回表。

从字段特性的角度来看，索引分为主键索引、唯一索引、普通索引、前缀索引。

主键索引

唯一索引

普通索引

前缀索引

从字段个数的角度来看，索引分为单列索引、联合索引（复合索引）。

• 建立在单列上的索引称为单列索引，比如主键索引；
• 建立在多列上的索引称为联合索引；

联合索引

字段有唯一性限制的，比如商品编码；

经常用于 WHERE 查询条件的字段，这样能够提高整个表的查询速度，如果查询条件不是一个字段，可以建立联合索引。

经常用于 GROUP BY 和 ORDER BY 的字段，这样在查询的时候就不需要再去做一次排序了，
因为我们都已经知道了建立索引之后在 B+Tree 中的记录都是排序好的。

WHERE 条件，GROUP BY，ORDER BY 里用不到的字段，索引的价值是快速定位，
如果起不到定位的字段通常是不需要创建索引的，因为索引是会占用物理空间的。

字段中存在大量重复数据，不需要创建索引，比如性别字段，只有男女，如果数据库表中，男女的记录分布均匀，
那么无论搜索哪个值都可能得到一半的数据。在这些情况下，还不如不要索引，因为 MySQL 还有一个查询优化器，
查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。

表数据太少 的时候，不需要创建索引；

经常更新的字段不用创建索引，比如不要对电商项目的用户余额建立索引，因为索引字段频繁修改，
由于要维护 B+Tree的有序性，那么就需要频繁的重建索引，这个过程是会影响数据库性能的。

B+ 树就是对 B 树做了一个升级，MySQL 中索引的数据结构就是采用了 B+ 树，B+ 树结构如下图：

B+ 树与 B 树差异的点，主要是以下这几点：

• 叶子节点（最底部的节点）才会存放实际数据（索引+记录），非叶子节点只会存放索引；
• 所有索引都会在叶子节点出现，叶子节点之间构成一个有序链表；
• 非叶子节点的索引也会同时存在在子节点中，并且是在子节点中所有索引的最大（或最小）。
• 非叶子节点中有多少个子节点，就有多少个索引；

下面通过三个方面，比较下 B+ 和 B 树的性能区别。

B 树进行单个索引查询时，最快可以在 O(1) 的时间代价内就查到，而从平均时间代价来看，会比 B+ 树稍快一些。

但是 B 树的查询波动会比较大，因为每个节点即存索引又存记录，所以有时候
访问到了非叶子节点就可以找到索引，而有时需要访问到叶子节点才能找到索引。

B+ 树的非叶子节点不存放实际的记录数据，仅存放索引，因此数据量相同的情况下，相比既存索引又存记录的 B 树，
B+ 树的非叶子节点可以存放更多的索引，因此 B+ 树可以比 B 树更「矮胖」，查询底层节点的磁盘 I/O次数会更少。

B+ 树有大量的冗余节点，这样使得删除一个节点的时候，可以直接从叶子节点中删除，甚至可以不动非叶子节点，这样删除非常快，

比如下面这个动图是删除 B+ 树 0004 节点的过程，因为非叶子节点有 0004 的冗余节点，所以在删除的时候，树形结构变化很小：

下面这个动图是删除 B 树 0008 节点的过程，可能会导致树的复杂变化：

B+ 树的插入也是一样，有冗余节点，插入可能存在节点的分裂（如果节点饱和），但是最多只涉及
树的一条路径。而且 B+ 树会自动平衡，不需要像更多复杂的算法，类似红黑树的旋转操作等。

因此，B+ 树的插入和删除效率更高。

B 树和 B+ 树等值查询原理基本一致，先从根节点查找，然后对比目标数据的范围，最后递归的进入子节点查找。

因为 B+ 树所有叶子节点间还有一个链表进行连接，这种设计对范围查找非常有帮助。

比如说我们想知道 12 月 1 日和 12 月 12 日之间的订单，这个时候可以先查找到 12 月 1 日所在的叶子节点，然后
利用链表向右遍历，直到找到 12 月12 日的节点，这样就不需要从根节点查询了，进一步节省查询需要的时间。

而 B 树没有将所有叶子节点用链表串联起来的结构，因此只能通过树的中序遍历来完成范围查询，
这会涉及多个节点的磁盘 I/O 操作，范围查询效率不如 B+ 树。

因此，存在大量范围检索的场景，适合使用 B+树，比如数据库。而对于大量的单个索引查询的场景，可以考虑 B 树，比如 nosql 的 MongoDB。

下图就是 Innodb 里的 B+ 树：

但是 Innodb 使用的 B+ 树有一些特别的点，比如：

• B+ 树的 叶子节点之间 是用「双向链表」进行连接，这样的好处是既能向右遍历，也能向左遍历。
• B+ 树点 节点内容是数据页，数据页里存放了用户的记录以及各种信息，每个数据页默认大小是 16 KB。

在这些情况里，all 是最坏的情况，因为采用了 全表扫描 的方式。index 和 all 差不多，只不过 index 对索引表进行
全扫描，这样做的好处是 不再需要对数据进行排序，但是开销依然很大。所以，要尽量避免全表扫描和全索引扫描。

range 表示采用了 索引范围扫描，一般在 where 子句中使用 < 、>、in、between 等关键词，只检索给定范围的行，属于范围查找。

从这一级别开始，索引的作用会越来越明显，因此我们需要尽量让 SQL 查询可以使用到 range 这一级别及以上的 type 访问方式。

ref 类型表示 二级索引与常数进行等值比较。形成的扫描区间虽为单点区间，但是二级索引记录可能有重复值。
它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。

eq_ref 类型是 使用主键或唯一索引时产生的访问方式，通常使用在 多表联查 中。比如，对两张表进行联查，关联条件是
两张表的 user_id 相等，且 user_id 是唯一索引，那么使用 EXPLAIN 进行执行计划查看的时候，type 就会显示 eq_ref。

const 类型表示使用了 主键或者唯一索引与常量值进行比较，比如 select name from product where id=1。

需要说明的是 const 类型和 eq_ref 都使用了主键或唯一索引，不过这两个类型有所区别，
const 是与常量进行比较，查询效率会更快，而 eq_ref 通常用于多表联查中。

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

假设 count() 函数的参数是字段名，如下：

这条语句是统计「 t_order 表中，name 字段不为 NULL 的记录」有多少个。
也就是说，如果某一条记录中的 name 字段的值为 NULL，则就不会被统计进去。

再来假设 count() 函数的参数是数字 1 这个表达式，如下：

这条语句是统计「 t_order 表中，1 这个表达式不为 NULL 的记录」有多少个。

1 这个表达式就是单纯数字，它永远都不是 NULL，所以上面这条语句，其实是在统计 t_order 表中有多少个记录。

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

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

InnoDB 是通过 B+ 树来保存记录的，根据索引的类型又分为聚簇索引和二级索引，它们区别在于，
聚簇索引的叶子节点存放的是实际数据，而二级索引的叶子节点存放的是主键值，而不是实际数据。

用下面这条语句作为例子：

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

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

这是因为相同数量的 二级索引记录可以比聚簇索引记录占用更少的存储空间，所以二级索引树比聚簇索引树小，
这样遍历二级索引的 I/O 成本比遍历聚簇索引的 I/O 成本小，因此「优化器」优先选择的是二级索引。

用下面这条语句作为例子：

如果表里 只有主键索引，没有二级索引 时：

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

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

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

看到 * 这个字符的时候，是不是大家觉得是读取记录中的所有字段值？

对于 selete * 这条语句来说是这个意思，但是 在 count(*) 中并不是这个意思。

count(*) 其实等于 count(0)，也就是说，当你使用 count(*) 时，MySQL 会将 * 参数转化为参数 0 来处理。

所以，count(*) 执行过程跟 count(1) 执行过程基本一样的，性能没有什么差异，因为 0 也是非 NULL。

在 MySQL 5.7 的官方手册中有这么一句话：

InnoDB handles SELECT COUNT(\*) and SELECT COUNT(1) operations in the same way. There is no performance difference.

翻译：InnoDB以相同的方式处理SELECT COUNT（\*）和SELECT COUNT（1）操作，没有性能差异。

而且 MySQL 会对 count(*) 和 count(1) 有个优化，如果 有多个二级索引的时候，优化器会使用 
key_len 最小的二级索引进行扫描。只有当没有二级索引的时候，才会采用主键索引来进行统计。

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

用下面这条语句作为例子：

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

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

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

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

如果你的业务对于统计个数不需要很精确，比如搜索引擎在搜索关键词的时候，给出的搜索结果条数是一个大概值。

这时，我们就可以 使用 show table status 或者 explain 命令来表进行估算。

执行 explain 命令效率是很高的，因为它并不会真正的去查询所有记录，下图中的 rows 字段值就是 explain 命令对表 t_order 记录的估算值。

explain 命令不会真正的去查所有的记录，而是直接根据第一个记录所在的 page 和最后一个记录所在的 page，再选择左侧 page 向右连续查找
8 个 page，总共 10 个 page，获取这 10 个 page 的总记录数，再除以 10 取平均值，最后乘总的 page 数目，即可得到一个不精确的总记录数。

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

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

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

假设有 A 和 B 这两个事务同时在处理，事务 A 先开始从数据库中读取小林的余额数据，然后再执行更新操作，如果此时事务 A 还没有提交事务，
而此时正好事务 B 也从数据库中读取小林的余额数据，那么事务 B 读取到的余额数据是刚才事务 A 更新后的数据，即使没有提交事务。

因为事务 A 是还没提交事务的，也就是它随时可能发生回滚操作，如果在上面这种情况事务 A 发生了回滚，
那么事务 B 刚才得到的数据就是过期的数据，这种现象就被称为脏读。

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

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

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

假设有 A 和 B 这两个事务同时在处理，事务 A 先开始从数据库查询账户余额大于 100 万的记录，
发现共有 5 条，然后事务 B 也按相同的搜索条件也是查询出了 5 条记录。

接下来，事务 A 插入了一条余额超过 100 万的账号，并提交了事务，此时数据库超过 100 万余额的账号个数就变为 6。

然后事务 B 再次查询账户余额大于 100 万的记录，此时查询到的记录数量有 6 条，发现
和前一次读到的记录数量不一样了，就感觉发生了幻觉一样，这种现象就被称为幻读。

脏写就是一个事务先修改了数据，另一个事务也修改同一个数据后提交了，这时 第一个事务发生了回滚，就会把
数据回滚到原始值，对于第二个事务来说，明明已经提交了事务，但是最后的数据还是原来的，这就发生了脏写。

还是以这张表作为例子：

事务 A 执行查询 id = 5 的记录，此时表中是没有该记录的，所以查询不出来。

然后事务 B 插入一条 id = 5 的记录，并且提交了事务。

此时，事务 A 更新 id = 5 这条记录，对没错，事务 A 看不到 id = 5 这条记录，但是他去更新了这条记录，这场景
确实 很违和，然后 再次查询 id = 5 的记录，事务 A 就能看到事务 B 插入的纪录了，幻读就是发生在这种违和的场景。

整个发生幻读的时序图如下：

在可重复读隔离级别下，事务 A 第一次执行普通的 select 语句时生成了一个 ReadView，之后事务 B 向表中新插入了一条 
id = 5 的记录并提交。接着，事务 A 对 id = 5 这条记录进行了更新操作，在这个时刻，这条新记录的 trx_id 隐藏列的值就
变成了事务 A 的事务 id，之后事务 A 再使用普通 select 语句去查询这条记录时就可以看到这条记录了，于是就发生了幻读。

因为这种特殊现象的存在，所以我们认为 MySQL Innodb 中的 MVCC 并不能完全避免幻读现象。

除了上面这一种场景会发生幻读现象之外，还有下面这个场景也会发生幻读现象：

• T1 时刻：事务 A 先执行「快照读语句」：select * from t_test where id > 100 得到了 3 条记录。
• T2 时刻：事务 B 往 插入 一个 id= 200 的记录并提交；
• T3 时刻：事务 A 再执行「当前读语句」 select * from t_test where id > 100 for update 就会
  得到 4 条记录，发生了 幻读。因为 当前读会获取最新的记录信息，然后进行加锁。

要避免这类特殊场景下发生幻读的现象的话，就是 尽量在开启事务之后，马上执行 select ... for update 
这类当前读的语句，因为它会对记录加 next-key lock，从而避免其他事务插入一条新记录。

要使用全局锁，则要执行这条命：

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

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

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

当然，当会话断开了，全局锁会被自动释放。

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

在全库逻辑备份期间，假设不加全局锁的场景，看看会出现什么意外的情况。

如果在全库逻辑备份期间，有用户购买了一件商品，一般购买商品的业务逻辑是会涉及到多张数据库表的更新，
比如在用户表更新该用户的余额，然后在商品表更新被购买的商品的库存。

那么，有可能出现这样的顺序：

• 先备份了用户表的数据；
• 然后有用户发起了购买商品的操作；
• 接着再备份商品表的数据。

也就是在备份用户表和商品表之间，有用户购买了商品。

这种情况下，备份的结果是用户表中该用户的余额并没有扣除，反而商品表中该商品的库存被减少了，
如果后面用这个备份文件恢复数据库数据的话，用户钱没少，而库存少了，等于用户白嫖了一件商品。

所以，在全库逻辑备份期间，加上全局锁，就不会出现上面这种情况了。

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

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

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

因为在可重复读的隔离级别下，即使其他事务更新了表的数据，也不会影响备份数据库时的 Read View，
这就是事务四大特性中的隔离性，这样备份期间备份的数据一直是在开启事务时的数据。

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

InnoDB 存储引擎默认的事务隔离级别正是可重复读，因此可以采用这种方式来备份数据库。

但是，对于 MyISAM 这种不支持事务的引擎，在备份数据库时就要使用全局锁的方法。

如果我们想对学生表（t_student）加表锁，可以使用下面的命令：

需要注意的是，表锁除了会限制别的线程的读写外，也会限制本线程接下来的读写操作。

也就是说如果本线程对学生表加了「共享表锁」，那么本线程接下来如果要对学生表执行写操作的语句，
是会被阻塞的，当然其他线程对学生表进行写操作时也会被阻塞，直到锁被释放。

要释放表锁，可以使用下面这条命令，会释放当前会话的所有表锁：

另外，当会话退出后，也会释放所有表锁。

不过尽量避免在使用 InnoDB 引擎的表使用表锁，因为表锁的颗粒度太大，
会影响并发性能，InnoDB 牛逼的地方在于实现了颗粒度更细的行级锁。

MDL 主要是用于对表执行 CRUD（DML） 和 改变表结构（DDL） 时进行 互斥 的作用。

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

• 对一张表进行 CRUD 操作时，加的是 MDL 读锁；
• 对一张表做 结构变更 操作的时候，加的是 MDL 写锁；

MDL 是为了保证当用户对表执行 CRUD 操作时，防止其他线程对这个表结构做了变更。

当有线程在执行 select 语句（ 加 MDL 读锁）的期间，如果有其他线程要更改该表的
结构（ 申请 MDL 写锁），那么将会被阻塞，直到执行完 select 语句（ 释放 MDL 读锁）。

反之，当有线程对表结构进行变更（ 加 MDL 写锁）的期间，如果有其他线程执行了 CRUD 
操作（ 申请 MDL 读锁），那么就会被阻塞，直到表结构变更完成（ 释放 MDL 写锁）。

注意：加了 MDL 写锁后，就算是 select 也是会阻塞的，因为此时表结构可能会发生改变而导致数据不一致。

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

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

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

• 首先，线程 A 先启用了事务（但是一直不提交），然后执行一条 select 语句，此时就先对该表加上 MDL 读锁；
• 然后，线程 B 也执行了同样的 select 语句，此时并不会阻塞，因为「读读」并不冲突；
• 接着，线程 C 修改了表字段，此时由于线程 A 的事务并没有提交，也就是 MDL 读锁还在占用着，
  这时线程 C 就无法申请到 MDL 写锁，就会被阻塞，

那么在线程 C 阻塞后，后续有对该表的 select 语句，就都会被阻塞，如果此时有大量该表的 
select 语句的请求到来，就会有大量的线程被阻塞住，这时数据库的线程很快就会爆满了。

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

这是因为申请 MDL 锁的操作会形成一个队列，队列中 写锁获取优先级高于读锁，
一旦出现 MDL 写锁等待，会阻塞后续该表的所有 CRUD 操作。

所以为了能安全的对表结构进行变更，在对表结构变更前，先要看看数据库中的长事务，是否
有事务已经对表加上了 MDL 读锁，如果可以考虑 kill 掉这个长事务，然后再做表结构的变更。

• 在使用 InnoDB 引擎的表里对某些记录加上「共享锁」之前，需要先在表级别加上一个「意向共享锁」；
• 在使用 InnoDB 引擎的表里对某些纪录加上「独占锁」之前，需要先在表级别加上一个「意向独占锁」；

也就是，当执行插入、更新、删除操作，需要先对表加上「意向独占锁」，然后对该记录加独占锁。

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

不过，select 也是可以对记录加共享锁和独占锁的，具体方式如下：

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

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

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

那么有了「意向锁」，由于在对记录加独占锁前，先会加上表级别的意向独占锁，那么在加「独占表锁」时，
直接查该表是否有意向独占锁，如果有就意味着表里已经有记录被加了独占锁，这样就不用去遍历表里的记录。

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

表里的主键通常都会设置成自增的，这是通过对主键字段声明 AUTO_INCREMENT 属性实现的。

之后可以在插入数据时，可以不指定主键的值，数据库会 自动给主键赋值递增的值，这主要是通过 AUTO-INC 锁 实现的。

AUTO-INC 锁是特殊的表锁机制，锁 不是再一个事务提交后才释放，而是 执行完插入语句后就会立即释放。

在插入数据时，会加一个表级别的 AUTO-INC 锁，然后为被 AUTO_INCREMENT 
修饰的字段赋值递增的值，等插入语句执行完成后，才会把 AUTO-INC 锁释放掉。

那么，一个事务在持有 AUTO-INC 锁的过程中，其他事务的如果要向该表插入语句都会被阻塞，
从而保证插入数据时，被 AUTO_INCREMENT 修饰的字段的值是连续递增的。

但是， AUTO-INC 锁再对大量数据进行插入的时候，会影响插入性能，因为另一个事务中的插入会被阻塞。

因此， 在 MySQL 5.1.22 版本开始，InnoDB 存储引擎提供了一种 轻量级的锁 来实现自增。

一样也是在插入数据的时候，会为被 AUTO_INCREMENT 修饰的字段加上轻量级锁，然后给该
字段赋一个自增的值，就把这个轻量级锁释放了，而不需要等待整个插入语句执行完后才释放锁。

InnoDB 存储引擎提供了个 innodb_autoinc_lock_mode 的系统变量，是用来控制选择用 AUTO-INC 锁，还是轻量级的锁：

• 当 innodb_autoinc_lock_mode = 0，就采用 AUTO-INC 锁，语句执行结束后才释放锁；
• 当 innodb_autoinc_lock_mode = 2，就采用轻量级锁，申请自增主键后就释放锁，并不需要等语句执行后才释放。
• 当 innodb_autoinc_lock_mode = 1：

       （1）普通 insert 语句，自增锁在申请之后就马上释放；
       （2）类似 insert … select 这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放；

当 innodb_autoinc_lock_mode = 2 是性能最高的方式，但是当搭配 binlog 的日志格式
是 statement 一起使用的时候，在「主从复制的场景」中会发生 数据不一致的问题。

举个例子，考虑下面场景：

session A 往表 t 中插入了 4 行数据，然后创建了一个相同结构的表 t2，然后 两个 session 同时执行向表 t2 中插入数据。

如果 innodb_autoinc_lock_mode = 2，意味着「申请自增主键后就释放锁，不必等插入语句执行完」。
那么在向表 t2 插入数据时，就可能出现这样的情况：

• session B 先插入了两个记录，(1,1,1)、(2,2,2)；
• 然后，session A 来申请自增 id 得到 id=3，插入了（3,5,5)；
• 之后，session B 继续执行，插入两条记录 (4,3,3)、 (5,4,4)。

可以看到，session B 的 insert 语句，生成的 id 不连续。

当「主库」发生了这种情况，binlog 面对 t2 表的更新只会记录这两个 session 的 insert 语句，如果 binlog_format=statement，
记录的语句就是原始语句。记录的顺序要么先记 session A 的 insert 语句，要么先记 session B 的 insert 语句。

但不论是哪一种，这个 binlog 拿去「从库」执行，这时 从库是按「顺序」执行语句的，只有当执行完一条 SQL 语句后，
才会执行下一条 SQL。因此，在从库上「不会」发生像主库那样两个 session 「同时」执行向表 t2 中插入数据的场景。
所以，在备库上执行了 session B 的 insert 语句，生成的结果里面，id 都是连续的。这时，主从库就发生了数据不一致。

要解决这问题，binlog 日志格式要设置为 row，这样在 binlog 里面记录的是主库分配的自增值，
到备库执行的时候，主库的自增值是什么，从库的自增值就是什么。

所以，当 innodb_autoinc_lock_mode = 2 时，并且 binlog_format = row，既能提升并发性，又不会出现数据一致性问题。

InnoDB 引擎是支持行级锁的，而 MyISAM 引擎并不支持行级锁。

前面也提到，普通的 select 语句是不会对记录加锁的，因为它属于快照读。如果要在
查询时对记录加行锁，可以使用下面这两个方式，这种查询会加锁的语句称为 锁定读。

上面这两条语句必须在一个事务中，因为当事务提交了，锁就会被释放，所以在使用
这两条语句的时候，要加上 begin、start transaction 或者 set autocommit = 0。

共享锁（S锁）满足读读共享，读写互斥。独占锁（X锁）满足写写互斥、读写互斥。

行级锁的类型主要有三类：

• Record Lock，记录锁，也就是仅仅把一条记录锁上；
• Gap Lock，间隙锁，锁定一个范围，但是不包含记录本身；
• Next-Key Lock：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。

Record Lock 称为 记录锁，锁住的是一条记录。而且记录锁是有 S 锁和 X 锁之分的：

• 当一个事务对一条记录加了 S 型记录锁后，其他事务也可以继续对该记录加 S 型记录锁（S 型与 S 锁兼容），
  但是不可以对该记录加 X 型记录锁（S 型与 X 锁不兼容）;
• 当一个事务对一条记录加了 X 型记录锁后，其他事务既不可以对该记录加 S 型记录锁（S 型与 X 锁不兼容），
  也不可以对该记录加 X 型记录锁（X 型与 X 锁不兼容）。

举个例子，当一个事务执行了下面这条语句：

就是对 t_test 表中主键 id 为 1 的这条记录加上 X 型的记录锁，这样其他事务就无法对这条记录进行修改了。

当事务执行 commit 后，事务过程中生成的锁都会被释放。

Gap Lock 称为 间隙锁，只存在于可重复读隔离级别，它的提出只是 为了解决可重复读隔离级别下幻读的现象。

假设，表中有一个范围 id 为（3，5）间隙锁，那么其他事务就无法插入 id = 4 这条记录了，这样就有效的防止幻读现象的发生。

间隙锁虽然存在 X 型间隙锁和 S 型间隙锁，但是并没有什么区别，间隙锁之间是兼容的，
即两个事务可以同时持有包含共同间隙范围的间隙锁，并不存在互斥关系，
因为 间隙锁的目的只是防止插入幻影记录而提出的，只在于阻止区间被插入。

但是有一点要注意，next-key lock 是包含间隙锁+记录锁的，如果一个事务获取了 X 型的 next-key lock，
因为记录锁的存在，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，是会被阻塞的。

比如，一个事务持有了范围为 (1, 10] 的 X 型的 next-key lock，那么
另外一个事务在获取相同范围的 X 型的 next-key lock 时，就会被阻塞。

但是还要注意！对于这种范围为 (xxx, +∞] 的 next-key lock，两个事务是可以同时持有的，
不会冲突。因为 +∞ 并不是一个真实的记录，自然就不需要考虑 X 型与 S 型关系。

Next-Key Lock 称为临键锁，是 Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。

假设，表中有一个范围 id 为（3，5] 的 next-key lock，那么其他事务即不能插入 id = 4 记录，也不能修改 id = 5 这条记录。

所以，next-key lock 即能保护该记录，又能阻止其他事务将新纪录插入到被保护记录前面的间隙中。

next-key lock 是包含间隙锁+记录锁的，如果一个事务获取了 X 型的 next-key lock，
那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，是会被阻塞的。

虽然 相同范围的间隙锁是多个事务相互兼容的，但对于 记录锁，我们是 要考虑 X 型与 S 型关系，
X 型的记录锁与 X 型的记录锁是冲突的。

一个事务在插入一条记录的时候，需要判断插入位置是否已被其他事务加了间隙锁（next-key lock 也包含间隙锁）。

如果有的话，插入操作就会发生阻塞，直到拥有间隙锁的那个事务提交为止（释放间隙锁的时刻），
在此期间会生成一个 插入意向锁，表明有事务想在某个区间插入新记录，但是现在处于等待状态。

举个例子，假设事务 A 已经对表加了一个范围 id 为（3，5）间隙锁。

当事务 A 还没提交的时候，事务 B 向该表插入一条 id = 4 的新记录，这时会判断到插入的位置已经被事务 A 加了间隙锁，于是事物 B 会生成一个
插入意向锁，然后将锁的状态设置为等待状态（PS：MySQL 加锁时，是先生成锁结构，然后设置锁的状态，如果锁状态是等待状态，并不是意味着
事务成功获取到了锁，只有当锁状态为正常状态时，才代表事务成功获取到了锁），此时事务 B 就会发生阻塞，直到事务 A 提交了事务。

插入意向锁名字虽然有意向锁，但是它并 不是意向锁，它是一种特殊的间隙锁，属于行级别锁。

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

如果说间隙锁锁住的是一个区间，那么「插入意向锁」锁住的就是一个点。
因而从这个角度来说，插入意向锁确实是一种特殊的间隙锁。

插入意向锁之间也并不会阻塞，两个事务可以同时获取到插入意向锁，进行各自的插入操作。

而且 插入意向锁并不会阻止别的事务继续获取该记录上任何类型的锁（插入意向锁就是这么“鸡肋”）。

但是如果某个区间的记录已经加了 排他锁，那么插入意向锁与排他锁 互斥。

主要的作用就是 在并发插入时，在同一个范围插入记录，只要插入的位置不冲突，就不会阻塞。

行级锁加锁规则比较复杂，不同的场景，加锁的形式是不同的。

加锁的对象是索引，加锁的基本单位是 next-key lock，它是由记录锁和间隙锁
组合而成的，next-key lock 是前开后闭区间，而间隙锁是前开后开区间。

但是，next-key lock 在一些场景下会退化成记录锁或间隙锁。

那到底是什么场景呢？

这次会以下面这个表结构来进行实验说明：

其中，id 是主键索引（唯一索引），age 是普通索引（非唯一索引），name 是普通的列。

表中的有这些行记录：

这次实验环境的 MySQL 版本是 8.0.26，隔离级别是「可重复读」。

当我们用唯一索引进行等值查询的时候，查询的记录存不存在，加锁的规则也会不同：

• 当查询的 记录是「存在」的，在索引树上定位到这一条记录后，
  将该记录的索引中的 next-key lock 会 退化成「记录锁」。
  
  
• 当查询的 记录是「不存在」的，则会在索引树找到 第一条大于该查询记录的记录，
  然后将该记录的索引中的 next-key lock 会 退化成「间隙锁」。

1、记录存在的情况

2、记录不存在的情况

范围查询和等值查询的加锁规则是不同的。

当唯一索引进行范围查询时，会对每一个扫描到的索引加 next-key 锁，然后如果遇到下面这些情况，会退化成记录锁或者间隙锁：

• 情况一：针对「大于等于」的范围查询，因为存在等值查询的条件，那么 如果等值
  查询的记录是存在于表中，那么该记录的索引中的 next-key 锁会 退化成记录锁。
  其他 大于条件的记录 自然不能退化，加的都是 next-key 锁。
  
  
• 情况二：针对「小于或者小于等于」的范围查询，要看条件值的记录是否存在于表中：
  
  

        （1）当条件值的记录不在表中，那么 不管是「小于」还是「小于等于」条件的范围查询，扫描到 终止范围查询的记录时，
                 该记录的索引的 next-key 锁 都会退化成间隙锁，其他扫描到的记录，都是 在这些记录的索引上 加 next-key 锁。

        （2）当条件值的记录在表中，如果是「小于」条件的范围查询，扫描到 终止范围查询的记录时，该记录的索引的 next-key 
                 锁会 退化成间隙锁，其他扫描到的记录，都是 在这些记录的索引上 加 next-key 锁；
                 如果「小于等于」条件的范围查询，扫描到 终止范围查询的记录时，该记录的索引 next-key 锁 不会退化成间隙锁。
                 其他扫描到的记录，都是 在这些记录的索引上 加 next-key 锁。

接下来，通过几个实验，才验证我上面说的结论。

1、针对「大于或者大于等于」的范围查询

2、针对「小于或者小于等于」的范围查询

当我们用非唯一索引进行等值查询的时候，因为存在两个索引，一个是主键索引，一个是非唯一索引（二级索引），所以在加锁时，
同时会对这两个索引都加锁，但是对主键索引加锁的时候，只有满足查询条件的记录才会对它们的主键索引加锁。

针对非唯一索引等值查询时，查询的记录存不存在，加锁的规则也会不同：

• 当查询的记录「存在」时，由于 不是唯一索引，所以肯定 存在索引值相同的记录，于是 非唯一索引等值查询的过程是一个扫描的过程，
  直到扫描到第一个不符合条件的二级索引记录就停止扫描，然后在扫描的过程中，对扫描到的二级索引记录加的是 next-key 锁，而对于
  第一个不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。同时，在符合查询条件的记录的主键索引上加记录锁。
  
  
• 当查询的记录「不存在」时，扫描到第一条不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。
  因为不存在满足查询条件的记录，所以不会对主键索引加锁。

1、记录存在的情况

2、记录不存在的情况

当有一个事务持有间隙锁 (22, 39) 时，到底是什么情况下，可以让其他事务的插入 
age = 39 记录的语句成功？又是什么情况下，插入 age = 39 记录时的语句会被阻塞？

非唯一索引和主键索引的范围查询的加锁也有所不同，不同之处在于 非唯一索引范围查询，索引的 next-key lock 
不会有退化为间隙锁和记录锁的情况，也就是非唯一索引进行范围查询时，对二级索引记录加锁都是加 next-key 锁。

就带大家简单分析一下，事务 A 的这条范围查询语句：

事务 A 的加锁变化：

1. 最开始要找的第一行是 age = 22，虽然范围查询语句包含等值查询，但是这里不是唯一索引范围查询，所以是不会发生退化锁的现象，因此对该
   二级索引记录加 next-key 锁，范围是 (21, 22]。同时，对 age = 10 这条记录的主键索引加记录锁，即对 id = 10 这一行记录的主键索引加记录锁。
2. 由于是范围查询，接着继续扫描已经存在的二级索引记录。扫面的第二行是 age = 39 的二级索引记录，于是对该二级索引记录加 next-key 锁，
   范围是 (22, 39]，同时，对 age = 39 这条记录的主键索引加记录锁，即对 id = 20 这一行记录的主键索引加记录锁。
3. 虽然我们看见表中最后一条二级索引记录是 age = 39 的记录，但是实际在 Innodb 存储引擎中，会用一个特殊的记录来标识最后一条记录，
   该特殊的记录的名字叫 supremum pseudo-record ，所以扫描第二行的时候，也就扫描到了这个特殊记录的时候，会对该二级索引记录加
   的是范围为 (39, +∞] 的 next-key 锁。
4. 停止查询

可以看到，事务 A 对主键索引和二级索引都加了 X 型的锁：

主键索引（id 列）：

• 在 id = 10 这条记录的主键索引上，加了记录锁，意味着其他事务无法更新或者删除 id = 10 的这一行记录。
• 在 id = 20 这条记录的主键索引上，加了记录锁，意味着其他事务无法更新或者删除 id = 20 的这一行记录。

二级索引（age 列）：

• 在 age = 22 这条记录的二级索引上，加了范围为 (21, 22] 的 next-key 锁，意味着其他事务无法更新或者删除 age = 22 的这一些新记录，
  不过对于插入 age = 22 记录的语句，在一些情况是可以成功插入的，而一些情况则无法成功插入，具体哪些情况，我们前面也讲了。
• 在 age = 39 这条记录的二级索引上，加了范围为 (22, 39] 的 next-key 锁，意味着其他事务无法更新或者删除 age = 39 的这一些记录，
  也无法插入 age 值为 23、24、25、...、38 的这一些新记录。不过对于插入 age = 39 记录的语句是，在一些情况是可以成功插入的，
  而一些情况则无法成功插入，具体哪些情况，我们前面也讲了。
• 在特殊的记录（supremum pseudo-record）的二级索引上，加了范围为 (39, +∞] 的 next-key 锁，意味着其他事务无法插入 age 值
  大于 39 的这些新记录。

若上面的查询条件是 age > 21，加锁情况和上面一致，因为会扫描到第一条符合条件的记录 age = 22，然后才开始加锁。

前面的案例，我们的查询语句都有使用索引查询，也就是查询记录的时候，
是通过索引扫描的方式查询的，然后对扫描出来的记录进行加锁。

如果锁定读查询语句，没有使用索引列作为查询条件，或者查询语句没有走索引查询，导致扫描是全表扫描。那么，每一条记录
的索引上都会加 next-key 锁，这样就相当于锁住的全表，这时如果其他事务对该表进行增、删、改操作的时候，都会被阻塞。

不只是锁定读查询语句不加索引才会导致这种情况，update 和 delete 语句如果查询条件不加索引，那么
由于扫描的方式是全表扫描，于是就会对每一条记录的索引上都会加 next-key 锁，这样就相当于锁住的全表。

因此，在线上在执行 update、delete、select ... for update 等具有加锁性质的语句，一定要检查语句是否走
了索引，如果是全表扫描的话，会对每一个索引加 next-key 锁，相当于把整个表锁住了，这是挺严重的问题。

问题来了，A 事务在执行 select ... for update 语句时，具体加了什么锁呢？

我们可以通过 select * from performance_schema.data_locks\G; 这条语句，查看事务执行 SQL 过程中加了什么锁。

输出的内容很多，共有 11 行信息，我删减了一些不重要的信息：

从上面输出的信息可以看到，共加了两种不同粒度的锁，分别是：

• 表锁（LOCK_TYPE: TABLE）：X 类型的意向锁；
• 行锁（LOCK_TYPE: RECORD）：X 类型的 next-key 锁；

这里我们重点关注「行锁」，图中 LOCK_TYPE 中的 RECORD 表示行级锁，而不是记录锁的意思：

• 如果 LOCK_MODE 为 X，说明是 next-key 锁；
• 如果 LOCK_MODE 为 X, REC_NOT_GAP，说明是记录锁；
• 如果 LOCK_MODE 为 X, GAP，说明是间隙锁；

然后通过 LOCK_DATA 信息，可以确认 next-key 锁的范围，具体怎么确定呢？

• 根据我的经验，如果 LOCK_MODE 是 next-key 锁或者间隙锁，那么 LOCK_DATA 
  就表示锁的范围最右值，而锁范围的最左值为 LOCK_DATA 的上一条记录的值。

因此，此时事务 A 在主键索引（INDEX_NAME : PRIMARY）上加了 10 个 next-key 锁，如下：

• X 型的 next-key 锁，范围：(-∞, 1]
• X 型的 next-key 锁，范围：(1, 2]
• X 型的 next-key 锁，范围：(2, 3]
• X 型的 next-key 锁，范围：(3, 4]
• X 型的 next-key 锁，范围：(4, 5]
• X 型的 next-key 锁，范围：(5, 6]
• X 型的 next-key 锁，范围：(6, 7]
• X 型的 next-key 锁，范围：(7, 8]
• X 型的 next-key 锁，范围：(8, 9]
• X 型的 next-key 锁，范围：(9, +∞]

这相当于把整个表给锁住了，其他事务在对该表进行增、删、改操作的时候都会被阻塞。

只有在事务 A 提交了事务，事务 A 执行过程中产生的锁才会被释放。

为什么只是查询年龄 20 岁以上行记录，而把整个表给锁住了呢？

如果对 age 建立索引，事务 A 这条查询会加什么锁呢？

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

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

如果说间隙锁锁住的是一个区间，那么「插入意向锁」锁住的就是一个点。
因而从这个角度来说，插入意向锁确实是一种特殊的间隙锁。

插入意向锁的生成时机：

• 每插入一条新记录，都需要看一下待插入记录的下一条记录上是否已经被加了间隙锁，如果已加间隙锁，
  此时会生成一个插入意向锁，然后锁的状态设置为 等待状态（PS：MySQL 加锁时，是先生成锁结构，
  然后设置锁的状态，如果锁状态是等待状态，并不是意味着事务成功获取到了锁，只有当锁状态为正常
  状态时，才代表事务成功获取到了锁），现象就是 Insert 语句会被阻塞。

当事务需要加锁的时，如果这个锁不可能发生冲突，InnoDB 会 跳过加锁环节，这种机制称为 隐式锁。

隐式锁是 InnoDB 实现的一种延迟加锁机制，其特点是只有在可能发生冲突时才加锁，从而减少了锁的数量，提高了系统整体性能。

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

• 如果记录之间加有间隙锁，为了避免幻读，此时是不能插入记录的；
• 如果 Insert 的记录和已有记录存在唯一键冲突，此时也不能插入记录；

每插入一条新记录，都需要看一下待插入记录的下一条记录上是否已经被加了间隙锁，如果已加间隙锁，
此时会生成一个插入意向锁，然后锁的状态设置为 等待状态（PS：MySQL 加锁时，是先生成锁结构，
然后设置锁的状态，如果锁状态是等待状态，并不是意味着事务成功获取到了锁，只有当锁状态为正常
状态时，才代表事务成功获取到了锁），现象就是 Insert 语句会被阻塞。

举个例子，现在 t_order 表中，只有这些数据，order_no 是二级索引。

现在，事务 A 执行了下面这条语句：

接着，我们执行 select * from performance_schema.data_locks\G; 
确定事务 A 加了什么类型的锁，这里只关注在记录上加锁的类型。

本次的例子加的是 next-key 锁（记录锁+间隙锁），锁范围是（1005, +∞]。

然后，有个事务 B 在这个间隙锁中，插入了一个记录，那么此时该事务 B 就会被阻塞：

接着，我们执行 select * from performance_schema.data_locks\G;
确定事务 B 加了什么类型的锁，这里只关注在记录上加锁的类型。

可以看到，事务 B 的状态为等待状态（LOCK_STATUS: WAITING），因为向事务 A 生成的 next-key 锁（记录锁+间隙锁）
范围（1005, +∞] 中插入了一条记录，所以事务 B 的插入操作生成了一个插入意向锁（LOCK_MODE: X,INSERT_INTENTION），
锁的状态是等待状态，意味着事务 B 并没有成功获取到插入意向锁，因此事务 B 发生阻塞。

如果在插入新记录时，插入了一个与「已有的记录的主键或者唯一二级索引列值相同」的记录（不过可以有多条记录的
唯一二级索引列的值同时为 NULL，这里不考虑这种情况），此时插入就会失败，然后对于这条记录加上了 S 型的锁。

至于是行级锁的类型是记录锁，还是 next-key 锁，跟是「主键冲突」还是「唯一二级索引冲突」有关系。

如果 主键索引重复：

• 当隔离级别为 读已提交 时，插入新记录的事务会给已存在的主键值重复的聚簇索引记录添加 S 型记录锁。
• 当隔离级别是 可重复读（默认隔离级别），插入新记录的事务会给已存在的主键值重复的聚簇索引记录添加 S 型记录锁。

如果 唯一二级索引列重复：

• 不论是哪个隔离级别，插入新记录的事务都会给已存在的二级索引列值重复的二级索引记录添加 S 型 next-key 锁。对的，没错，即使是读已提交

     隔离级别也是加 next-key 锁，这是读已提交隔离级别中为数不多的给记录添加间隙锁的场景。至于为什么要加 next-key 锁，我也没找到合理的解释。

主键索引冲突

唯一二级索引冲突

Time 1 阶段，事务 A 执行以下语句：

然后执行 select * from performance_schema.data_locks\G; 查看事务 A 此时加了什么锁。

从上图可以看到，共加了两个锁，分别是：

• 表锁：X 类型的意向锁；
• 行锁：X 类型的间隙锁；

这里我们重点关注行锁，图中 LOCK_TYPE 中的 RECORD 表示行级锁，而不是记录锁的意思，
通过 LOCK_MODE 可以确认是 next-key 锁，还是间隙锁，还是记录锁：

• 如果 LOCK_MODE 为 X，说明是 next-key 锁；
• 如果 LOCK_MODE 为 X, REC_NOT_GAP，说明是记录锁；
• 如果 LOCK_MODE 为 X, GAP，说明是间隙锁；

因此，此时事务 A 在主键索引（INDEX_NAME : PRIMARY）上加的是间隙锁，锁范围是(20, 30)。

间隙锁的范围(20, 30) ，是怎么确定的？

根据我的经验，如果 LOCK_MODE 是 next-key 锁或者间隙锁，那么 LOCK_DATA 
就表示锁的范围最右值，此次的事务 A 的 LOCK_DATA 是 30。

然后锁范围的最左值是 t_student 表中 id 为 30 的上一条记录的 id 值，即 20。

因此，间隙锁的范围(20, 30)。

Time 2 阶段，事务 B 执行以下语句：

然后执行 select * from performance_schema.data_locks\G; 查看事务 B 此时加了什么锁。

从上图可以看到，行锁是 X 类型的间隙锁，间隙锁的范围是(20, 30)。

两个事务的间隙锁之间是相互兼容的，不会产生冲突。

间隙锁的意义只在于阻止区间被插入，因此是可以共存的。一个事务获取的间隙锁不会阻止另一个事务获取
同一个间隙范围的间隙锁，共享（S型）和排他（X型）的间隙锁是没有区别的，他们相互不冲突，且功能相同。

Time 3，事务 A 插入了一条记录：

此时，事务 A 就陷入了等待状态。

然后执行 select * from performance_schema.data_locks\G; 查看事务 A 在获取什么锁而导致被阻塞。

可以看到，事务 A 的状态为等待状态（LOCK_STATUS: WAITING），因为 向事务 B 生成的间隙锁（范围 (20, 30)）
中插入了一条记录，所以 事务 A 的插入操作生成了一个插入意向锁（LOCK_MODE:INSERT_INTENTION）。

插入意向锁是什么？

Time 4，事务 B 插入了一条记录：

此时，事务 B 就陷入了等待状态。

然后执行 select * from performance_schema.data_locks\G; 查看事务 B 在获取什么锁而导致被阻塞。

可以看到，事务 B 在生成插入意向锁时而导致被阻塞，这是因为事务 B 向事务 A 生成的范围为 (20, 30) 的
间隙锁插入了一条记录，而插入意向锁和间隙锁是冲突的，所以事务 B 在获取插入意向锁时就陷入了等待状态。

MySQL 的数据都是存在磁盘中的，那么我们要更新一条记录的时候，得先要从磁盘读取该记录，
然后在内存中修改这条记录。那修改完这条记录是选择直接写回到磁盘，还是选择缓存起来呢？

当然是缓存起来好，这样下次有查询语句命中了这条记录，直接读取缓存中的记录，就不需要从磁盘获取数据了。
而且万一同时修改很多记录，将这么多记录直接写回磁盘，效率是很低的，磁盘 IO 非常消耗性能。

为此，Innodb 存储引擎设计了一个 缓冲池（Buffer Pool），来 提高数据库的读写性能。

有了 Buffer Poo 后：

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

InnoDB 会把存储的数据划分为若干个「页」，以页作为磁盘和内存交互的基本单位，
一个页的默认大小为 16KB。因此，Buffer Pool 同样需要按「页」来划分。

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

所以，MySQL 刚启动的时候，你会观察到使用的虚拟内存空间很大，而使用到的物理内存空间却很小，这是因为
只有这些虚拟内存被访问后，操作系统才会触发缺页中断，申请物理内存，接着将虚拟地址和物理地址建立映射关系。

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

Undo 页是记录什么？

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

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

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

Buffer Pool 是提高了读写效率没错，但是问题来了，Buffer Pool 是基于内存的，
而内存总是不可靠，万一断电重启，还没来得及落盘的脏页数据就会丢失。

为了防止断电导致数据丢失的问题，当有一条记录需要更新的时候，InnoDB 引擎就会 先更新内存（同时标记为脏页），
然后将本次对这个页的修改以 redo log 的形式记录下来，这个时候更新就算完成了。

后续，InnoDB 引擎会在适当的时候，由 后台线程将缓存在 Buffer Pool 的脏页刷新到磁盘里，这就是 WAL （Write-Ahead Logging）技术。

WAL 技术指的是： 写操作并不是立刻将数据写到磁盘上，而是先写日志，然后在合适的时间再写到磁盘上。

过程如下图：

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

在事务提交时，只要先将 redo log 持久化到磁盘即可，可以不需要等到将缓存在 Buffer Pool 里的脏页数据持久化到磁盘。

当系统崩溃时，虽然脏页数据没有持久化，但是 redo log 已经持久化，接着 MySQL 重启后，
可以根据 redo log 的内容，将所有数据恢复到最新的状态。

需要的。

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

不过，在内存修改该 Undo 页面后，需要记录对应的 redo log。

这两种日志是属于 InnoDB 存储引擎的日志，它们的区别在于：

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

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

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

写入 redo log 的方式使用了追加操作， 所以磁盘操作是 顺序写，而
写入数据需要先找到写入位置，然后才写到磁盘，所以磁盘操作是 随机写。

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

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

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

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

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

不是的。

实际上， 执行一个事务的过程中，产生的 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 buffer 里的 redo log 还是在内存中，它什么时候刷新到磁盘？

主要有下面几个时机：

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

innodb_flush_log_at_trx_commit 参数控制的是什么？

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

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

默认情况下， InnoDB 存储引擎有 1 个重做日志文件组( redo log Group），「重做日志文件组」
由 2 个 redo log 文件组成，这两个 redo 日志的文件名叫 ：ib_logfile0 和 ib_logfile1 。

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

重做日志文件组是以 循环写 的方式工作的，从头开始写，写到末尾就又回到开头，相当于一个环形。

所以 InnoDB 存储引擎会先写 ib_logfile0 文件，当 ib_logfile0 文件被写满的时候，
会切换至 ib_logfile1 文件，当 ib_logfile1 文件也被写满时，会切换回 ib_logfile0 文件。

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

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

图中的：

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

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

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

这个问题跟 MySQL 的时间线有关系。

最开始 MySQL 里并没有 InnoDB 引擎，MySQL 自带的引擎是 MyISAM，
但是 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。

而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，
所以 InnoDB 使用 redo log 来实现 crash-safe 能力。

crash-safe 是靠 redo log 来保障的，并不靠 binlog。只不过为了奔溃恢复后的数据与 binlog 一致，
某些有 commit 失败的 redo log 在恢复时要根据 binlog 是否写入来决定是否恢复！

还有就是，binlog 只是记录对数据库修改的记录，而不会记录事务对数据页的修改。
所以当数据库宕机重启时，最多只能将数据记录恢复，而不能恢复成事务提交后的样子。

1、适用对象不同：

• binlog 是 MySQL 的 Server 层实现的日志，所有存储引擎都可以使用；
• redo log 是 Innodb 存储引擎实现的日志；

2、文件格式不同：

• binlog 有 3 种格式类型，分别是 STATEMENT（默认格式）、ROW、 MIXED，区别如下：

       （1）STATEMENT：每一条 修改数据的 SQL 都会被记录到 binlog 中（相当于记录了逻辑操作，所以针对这种格式， binlog 可以称为逻辑日志），
                主从复制中 slave 端再根据 SQL 语句重现。但 STATEMENT 有动态函数的问题，比如你 用了 uuid 或者 now 这些函数，你在主库上执行的
                结果并 不是你在从库执行的结果，这种 随时在变的函数会导致复制的数据不一致；
       （2）ROW：记录行数据最终被修改成什么样了（这种格式的日志，就不能称为逻辑日志了），不会出现 STATEMENT 下动态函数的问题。但 ROW 
                的 缺点是每行数据的变化结果都会被记录，比如执行批量 update 语句，更新多少行数据就会产生多少条记录，使 binlog 文件过大，而在 
                STATEMENT 格式下只会记录一个 update 语句而已；
       （3）MIXED：包含了 STATEMENT 和 ROW 模式，它会 根据不同的情况自动使用 ROW 模式和 STATEMENT 模式；

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

3、写入方式不同：

• binlog 是 追加写，写满一个文件，就创建一个新的文件继续写，不会覆盖以前的日志，保存的是 全量的日志。
• redo log 是 循环写，日志空间大小是固定，全部写满就从头开始，保存未被刷入磁盘的脏页日志。

4、用途不同：

• binlog 用于备份恢复、主从复制；
• redo log 用于断电等故障恢复。

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

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

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

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

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

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

• 写入 Binlog：主库写 binlog 日志，提交事务，并更新本地存储数据。
• 同步 Binlog：把 binlog 复制到所有从库上，每个从库把 binlog 写到暂存日志中。
• 回放 Binlog：回放 binlog，并更新存储引擎中的数据。

具体详细过程 如下：

• MySQL 主库在收到客户端提交事务的请求之后，会先写入 binlog，再提交事务，
  更新存储引擎中的数据，事务提交完成后，返回给客户端“操作成功”的响应。
• 从库会创建一个专门的 I/O 线程，连接主库的 log dump 线程，来接收主库的 binlog 日志，
  再把 binlog 信息写入 relay log 的中继日志里，再返回给主库“复制成功”的响应。
• 从库会创建一个用于回放 binlog 的线程，去读 relay log 中继日志，然后回放 binlog 更新
  存储引擎中的数据，最终实现主从的数据一致性。

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

从库是不是越多越好？

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

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

一个事务的 binlog 是不能被拆开的，因此无论这个事务有多大（比如有很多条语句），也要保证一次性写入。这是因为有一个线程
只能同时有一个事务在执行的设定，所以每当执行一个 begin/start transaction 的时候，就会默认提交上一个事务，这样如果一个
事务的 binlog 被拆开的时候，在备库执行就会被当做多个事务分段自行，这样破坏了原子性，是有问题的。

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

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

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

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

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

• prepare 阶段：将 XID（内部 XA 事务的 ID） 写入到 redo log，同时将 redo log 对应的事务状态设置为 prepare，
  然后将 redo log 持久化到磁盘（innodb_flush_log_at_trx_commit = 1 的作用）；

• commit 阶段：把 XID 写入到 binlog，然后将 binlog 持久化到磁盘（sync_binlog = 1 的作用），接着调用引擎的提交事务接口，
  将 redo log 状态设置为 commit，此时该状态并不需要持久化到磁盘，只需要 write 到文件系统的 page cache 中就够了，因为
  只要 binlog 写磁盘成功，就算 redo log 的状态还是 prepare 也没有关系，一样会被认为事务已经执行成功；

我们来看看在两阶段提交的不同时刻，MySQL 异常重启会出现什么现象？下图中有时刻 A 和时刻 B 都有可能发生崩溃：

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

在 MySQL 重启后会按顺序扫描 redo log 文件，碰到处于 prepare 状态的 redo log，就拿着 redo log 中的 XID 去 binlog 查看是否存在此 XID：
（XID 是 redo log 和 binlog 的一个共同的数据字段）

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

所以，当在时刻 A 崩溃时，binlog 中 没有 XID，binlog 没有刷盘，则回滚事务，让主库也不要这些数据，才能保持主从数据的一致性。
当在时刻 B 崩溃时，binlog 中有 XID，redo log 和 binlog 都已经刷盘，那要提交事务，让主库也有这些数据，才能保持主从数据的一致性。

可以看到，对于处于 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 会被持久化到磁盘吗？

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

• 当 sync_binlog = 1 的时候，表示每次提交事务都会将 binlog cache 里的 binlog 直接持久到磁盘；
• 当 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 阶段拆分为三个过程：

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

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

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

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

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

flush 阶段

sync 阶段

commit 阶段

undo log 是在更新记录前记录，保存的是数据修改 之前的记录，用于事务的回滚，当操作失败或者异常时，将数据回滚到原来的的状态。

保证了事务的 原子性。

undo log 保存在 Buffer Pool 中的 undo 页面，对 undo 页面的修改也会记录到 redo log，所以 redo log 刷盘后 undo log 也就持久化到磁盘了。

undo log 还有一个重要的作用就是，是 MVCC 中版本链的重要组成部分。

MySQL 并不是进行一次写操作就把数据同步到磁盘上的，这样太消耗性能了，
而是先将写操作的数据放在 Buffer Pool 的脏页面中，再定时刷入磁盘。

但是这样存在断电后丢失数据的风险，因此 InnoDB 使用 redo log 来保证数据的 持久性。

采用 WAL（Write-Ahead-Log）技术，当执行写操作时，先将数据写入 redo log，再由后台线程定时将脏页面的数据刷盘。

所以，redo log 是在更新内存后记录，保存的是数据修改 之后的记录，这样才能保证 崩溃恢复 时，能恢复到最新的状态。这就是 crash-safe 能力。

但是，为了保证崩溃恢复后的数据与 binlog 保持一致，通常要根据 binlog 是否已刷盘来决定是否恢复（两阶段提交）。

redo log 虽然也是先保存在 redo log buffer 中，但是它刷盘频率可以控制得频繁些，因为它刷盘是 顺序 IO，
不用管真实数据在哪，直接顺着写入磁盘即可。顺序 IO 的效率比随机 IO 高很多的。

如果是从脏页面中刷盘，每次还要找到修改的数据在磁盘中的哪个位置，找到位置后才能写入，
所以 真实数据 的写入要跳来跳去寻找数据真实的位置，属于 随机 IO，效率非常低。

redo log 是 循环写，因为当脏页面的数据刷入磁盘后，redo log 就没用了，所以可以覆盖之前没用的数据。

所以恢复所有数据只能靠 binlog，不能靠 redo log。

redo log 有特定情况进行刷盘，例如有后台线程每隔 1 秒就将 redo log 进行刷盘、MySQL 正常关闭时，事务提交时根据刷盘策略刷盘等。

binlog 保存的是数据记录每次修改的情况，在 Server 层，用于 备份、主从复制。

binlog 是 追加写，要保存数据库完整的数据修改记录，当写完一个文件后，会生成新的文件继续写。

binlog 每次只会在 事务提交时 根据刷盘策略进行刷盘。

虽然说 MySQL 的数据是存储在磁盘里的，但是也不能每次都从磁盘里面读取数据，这样性能是极差的。

要想提升查询性能，加个缓存就行了嘛。所以，当数据从磁盘中取出后，
缓存内存中，下次查询同样的数据的时候，直接从内存中读取。

为此，Innodb 存储引擎设计了一个 缓冲池（Buffer Pool），来提高数据库的读写性能。

有了缓冲池后：

• 当读取数据时，如果数据存在于 Buffer Pool 中，客户端就会 直接读取 Buffer Pool 中的数据，否则再去磁盘中读取。
• 当修改数据时，首先是 修改 Buffer Pool 中数据所在的页，然后将其页设置为脏页，最后由后台线程将脏页写入到磁盘。

Buffer Pool 是在 MySQL 启动的时候，向操作系统申请的一片连续的内存空间，默认配置下 Buffer Pool 只有 128MB 。

可以通过调整 innodb_buffer_pool_size 参数来设置 Buffer Pool 的大小，一般建议设置成可用物理内存的 60%~80%。

InnoDB 会把存储的数据划分为若干个「页」，以页作为磁盘和内存交互的基本单位，
一个页的默认大小为 16KB。因此，Buffer Pool 同样需要按「页」来划分。

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

所以，MySQL 刚启动的时候，你会观察到使用的虚拟内存空间很大，而使用到的物理内存空间却很小，
这是因为只有这些虚拟内存被访问后，操作系统才会触发缺页中断，接着将虚拟地址和物理地址建立映射关系。

Buffer Pool 除了缓存「索引页」和「数据页」，还包括了 undo 页、插入缓存、自适应哈希索引、锁信息 等等。
(注意：redo log 有自己的 redo log buffer，默认 16MB，不在 Buffer Pool 中)

为了更好的管理这些在 Buffer Pool 中的缓存页，InnoDB 为每一个缓存页都创建了一个控制块，
控制块信息包括「缓存页的表空间、页号、缓存页地址、链表节点」等等。

控制块也是占有内存空间的，它是放在 Buffer Pool 的最前面，接着才是缓存页，如下图：

上图中控制块和缓存页之间灰色部分称为 碎片空间。

为什么会有碎片空间呢？

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

在 MySQL 5.5 之前，只针对 insert 做了优化，叫插入缓冲（insert buffer），
后面进行了优化，对 delete 和 update 操作也有效，叫做写缓冲（change buffer）。

change buffer 在 Buffer Pool 中，空间大小默认占 Buffer Poll 总内存的 25%。

在对数据做增删改时，都可以看成是写操作，因为它们都需要从磁盘中把这条待操作的记录读出来。

但是在更新一条记录（增删改都叫更新），如果该记录的数据页没有在内存中，那么就需要 从磁盘读取该数据页到内存，然后在更新数据页。

可以看到这种场景下，会发生磁盘 IO，如果更新的记录很多，然后这些记录的数据页都不在内存中的话，
就可能需要发生很多次磁盘 IO，这样还是挺影响更新操作的性能的。

那么为了提高更新操作的性能，InnoDB 存储引擎引入了 change buffer。

当更新一条记录，如果该记录的数据页没有在内存中，就 将更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。

在下次查询需要访问这个数据页的时候，将数据页读入内存，然后将 change buffer 缓存的操作应用到这个数据页，
这个过程就是 merge（合并），通过这种方式就能保证这个数据逻辑的正确性。

注意：merge 操作并不会将新纪录刷到磁盘，而是：

• 从磁盘读入数据页到内存（老版本的数据页）；
• 从 change buffer 里找出这个数据页的 change buffer 记录 (可能有多个），依次应用，得到新版数据页；
• 写 redo log。这个 redo log 包含了数据的变更和 change buffer 的变更。

到此，merge 操作就完成了。这时数据页和内存中 change buffer 对应的磁盘位置都还没有修改，属于脏页，之后各自刷回磁盘即可。

当然，为了防止一直都不会访问 change buffer 中的记录，所以 change buffer 有自己的 merge 时机：

• 读取 change buffer 中记录的数据页时，会将 change buffer 合并到 Buffer Pool 中，然后被刷新到磁盘。
• 当系统空闲或者 slow shutdown 时，后台 master 线程发起 merge。
• change buffer 的内存空间用完了，后台 master 线程会发起 merge。
• redo log 写满了，但是一般不会发生。

因为 在 change buffer 中的更新操作也会写入 redo log，所以不用担心宕机后，change buffer 还没 merge，而出现脏数据的情况。

注意，change buffer 只对非唯一普通索引有效，原因如下：

• 如果是 主键索引或唯一索引：更新记录都 会先判断当前操作是否违反了唯一性约束，而这个判断 需要将索引页读取到内存中。
  此时既然已经读取到内存中了，那就可以直接在内存中更新了，没有必要使用 change buffer 了。

• 如果是 非唯一普通索引：更新记录 不需要判断操作是否违反了唯一性约束，也就不需要将数据页读取到内存中，可以直接使用 change buffer 更新。

Buffer Pool 是一片连续的内存空间，当 MySQL 运行一段时间后，这片连续的内存空间中的缓存页既有空闲的，也有被使用的。

那当我们从磁盘读取数据的时候，总不能通过遍历这一片连续的内存空间来找到空闲的缓存页吧，这样效率太低了。

所以，为了能够快速找到空闲的缓存页，可以使用链表结构，将空闲缓存页的「控制块」作为链表的节点，这个链表称为 Free 链表（空闲链表）。

Free 链表上除了有控制块，还有一个头节点，该头节点包含链表的头节点地址，尾节点地址，以及当前链表中节点的数量等信息。

Free 链表节点是一个一个的控制块，而每个控制块包含着对应缓存页的地址，所以相当于 Free 链表节点都对应一个空闲的缓存页。

有了 Free 链表后，每当需要从磁盘中加载一个页到 Buffer Pool 中时，就从 Free链表中取一个空闲的缓存页，
并且把该缓存页对应的控制块的信息填上，然后把该缓存页对应的控制块从 Free 链表中移除。

设计 Buffer Pool 除了能提高读性能，还能提高写性能，也就是更新数据的时候，不需要每次都要写入磁盘，
而是将 Buffer Pool 对应的缓存页标记为 脏页，然后再 由后台线程将脏页写入到磁盘。

那为了能快速知道哪些缓存页是脏的，于是就设计出 Flush 链表，它跟 Free 
链表类似的，链表的节点也是控制块，区别在于 Flush 链表的元素都是脏页。

有了 Flush 链表后，后台线程就可以遍历 Flush 链表，将脏页写入到磁盘。

Buffer Pool 的大小是有限的，对于一些频繁访问的数据我们希望可以一直留在 Buffer Pool 中，而一些很少访问的数据希望可以在
某些时机可以淘汰掉，从而保证 Buffer Pool 不会因为满了而导致无法再缓存新的数据，同时还能保证常用数据留在 Buffer Pool 中。

要实现这个，最容易想到的就是 LRU（Least recently used）算法。

该算法的思路是，链表头部的节点是最近使用的，而链表末尾的节点是最久没被使用的。
那么，当空间不够了，就淘汰最久没被使用的节点，从而腾出空间。

简单的 LRU 算法的实现思路是这样的：

• 当访问的页在 Buffer Pool 里，就直接把该页对应的 LRU 链表节点移动到链表的头部。
• 当访问的页不在 Buffer Pool 里，除了要把页放入到 LRU 链表的头部，还要淘汰 LRU 链表末尾的节点。

比如下图，假设 LRU 链表长度为 5，LRU 链表从左到右有 1，2，3，4，5 的页。

如果访问了 3 号的页，因为 3 号页在 Buffer Pool 里，所以把 3 号页移动到头部即可。

而如果接下来，访问了 8 号页，因为 8 号页不在 Buffer Pool 里，所以需要先淘汰末尾的 5 号页，然后再将 8 号页加入到头部。

到这里我们可以知道，Buffer Pool 里有三种页和链表来管理数据：

• Free Page（空闲页），表示此页未被使用，位于 Free 链表；
• Clean Page（干净页），表示此页已被使用，但是页面未发生修改，位于LRU 链表。
• Dirty Page（脏页），表示此页「已被使用」且「已经被修改」，其数据和磁盘上的数据已经不一致。当脏页上的
  数据写入磁盘后，内存数据和磁盘数据一致，那么该页就变成了干净页。脏页同时存在于 LRU 链表和 Flush 链表。

简单的 LRU 算法并没有被 MySQL 使用，因为简单的 LRU 算法无法避免下面这两个问题：

• 预读失效；
• Buffer Pool 污染；

什么是预读失效？

怎么解决预读失效而导致缓存命中率降低的问题？

什么是 Buffer Pool 污染？

怎么解决出现 Buffer Pool 污染而导致缓存命中率下降的问题？

引入了 Buffer Pool 后，当修改数据时，首先是修改 Buffer Pool 中数据
所在的页，然后将其页设置为脏页，但是磁盘中还是原数据。

因此，脏页需要被刷入磁盘，保证缓存和磁盘数据一致，但是若每次修改
数据都刷入磁盘，则性能会很差，因此一般都会 在一定时机进行批量刷盘。

可能大家担心，如果在脏页还没有来得及刷入到磁盘时，MySQL 宕机了，不就丢失数据了吗？

这个不用担心，InnoDB 的更新操作采用的是 WAL 技术（Write Ahead Log），
即先写日志，再写入磁盘，通过 redo log 日志让 MySQL 拥有了崩溃恢复能力。

下面几种情况会触发脏页的刷新：

• 当 redo log 日志满了 的情况下，会主动触发脏页刷新到磁盘；
• Buffer Pool 空间不足时，需要将一部分数据页淘汰掉，如果 淘汰的是脏页，需要先将脏页同步到磁盘；
• MySQL 认为 空闲 时，后台线程回定期将适量的脏页刷入到磁盘；
• MySQL 正常关闭之前，会把所有的脏页刷入到磁盘；

在我们开启了慢 SQL 监控后，如果你发现「偶尔」会出现一些用时稍长的 SQL，这可能
是因为 脏页在刷新到磁盘 时可能会给数据库带来性能开销，导致 数据库操作抖动。

如果间断出现这种现象，就需要 调大 Buffer Pool 空间或 redo log 日志的大小。