基础知识

一条 SELECT 的执行流程

MySQL_1774513017966

Server 层负责建立连接、分析和执行 SQL
存储引擎层负责数据的存储和提取

MySQL_1774513053935

连接器

怎么解决长连接占用内存的问题：

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

查询缓存

查询缓存是以 key-value 形式保存在内存中的，key 为 SQL 查询语句，value 为 SQL 语句查询的结果

解析器

词法分析
语法分析：构建出 SQL 语法树，这样方便后面模块获取 SQL 类型、表名、字段名、 where 条件等等

执行 SQL

预处理器
- 检查 SQL 查询语句中的表或者字段是否存在；
- 将 select * 中的符号，扩展为表上的所有列；
优化器
- 将 SQL 查询语句的执行方案确定下来，可以用 explain 语句查看
执行器
- 主键索引查询
- 全表扫描
- 索引下推

总结

执行一条 SQL 查询语句，期间发生了什么？

连接器：建立连接，管理连接、校验用户身份；
查询缓存：查询语句如果命中查询缓存则直接返回，否则继续往下执行。MySQL 8.0 已删除该模块；
解析 SQL，通过解析器对 SQL 查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型；
执行 SQL：执行 SQL 共有三个阶段：
- 预处理阶段：检查表或字段是否存在；将 select * 中的符号扩展为表上的所有列。
- 优化阶段：基于查询成本的考虑，选择查询成本最小的执行计划；
- 执行阶段：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端；

一行记录是怎么存储的

MySQL_1774514123727

/var/lib/mysql/my_database 目录中有什么文件？

db.opt，用来存储当前数据库的默认字符集和字符校验规则。
t_xx.frm ，表结构会保存在这个文件。在 MySQL 中建立一张表都会生成一个.frm 文件，该文件是用来保存每个表的元数据信息的，主要包含表结构定义。
t_xx.ibd，表数据会保存在这个文件。表数据既可以存在共享表空间文件（文件名：ibdata1）里，也可以存放在独占表空间文件（文件名：表名字.ibd）。这个行为是由参数 innodb_file_per_table 控制的，若设置了参数 innodb_file_per_table 为 1，则会将存储的数据、索引等信息单独存储在一个独占表空间，从 MySQL 5.6.6 版本开始，它的默认值就是 1 了，因此从这个版本之后， MySQL 中每一张表的数据都存放在一个独立的 .ibd 文件。

InnoDB 逻辑存储结构

MySQL_1774514239526

为了高效地管理磁盘空间并提升 I/O 性能，InnoDB 将数据划分成了五个层级。

$1 \text{ Tablespace} \rightarrow n \text{ Segments} \rightarrow n \text{ Extents} \rightarrow 64 \text{ Pages} \rightarrow n \text{ Rows}$

这种分层设计主要是为了平衡空间利用率和运行效率。通过“区”来保证物理连续性以优化扫描速度，通过“页”来规范内存交换的大小，通过“段”来区分索引和数据的物理分布。以下是按照从大到小的层级解释：

1. 表空间 (Tablespace)

位于结构的最顶层。在 File-Per-Table 模式下，每个表都有一个独立的 .ibd 表空间文件。它是一个逻辑容器，包含了该表所有的段、区、页和行数据。

2. 段 (Segment)

段是表空间中的逻辑管理单位。InnoDB 主要是基于 B+ 树组织的，为了优化搜索和顺序读写，它将不同用途的数据分开管理：

数据段 (Data Segment)： 存放 B+ 树的叶子节点（即实际的数据行）。
索引段 (Index Segment)： 存放 B+ 树的非叶子节点（即目录项、索引键）。
回滚段 (Rollback Segment)： 专门用于存放 Undo Log，确保事务的原子性和多版本并发控制 (MVCC)。

3. 区 (Extent)

区是段的下一级，也是空间分配的最小单位。

大小： 默认情况下，一个区的大小为 1MB。
组成： 因为每页 16KB，所以一个区由 64 个连续的页组成。
存在的意义： 引入“区”是为了保证页在物理磁盘上是尽量连续的。这样在进行全表扫描或大范围查询时，可以使用 顺序 I/O 替代随机 I/O，显著提升性能。

4. 页 (Page)

页是 InnoDB 磁盘管理的最小单位，也是数据库读写交互的基本单位。

特性： 数据库每次从磁盘读取数据到内存，或将数据刷回磁盘，都是以“页”为单位进行的（默认 16KB）。
类型： 除了存放数据的“数据页”（Index Page），还有 Undo 页、系统页、索引页等。

5. 行 (Row)

位于结构的最底层。我们的数据最终都是以“行”的形式存储在页之中的。常见的行格式有 Redundant 、Compact、Dynamic 和 Compressed。

COMPACT 行格式

MySQL_1774514453730

变长字段长度列表 存储变长字段（如 varchar ）长度用的，通过 1 字节或 2 字节来记录每个变长字段的长度。
- 一个字节表示长度：变长字段长度≤255 字节。因为 1 字节的二进制范围是 00000000 到 11111111，对应十进制的 0 到 255。比如某一个字段定义为 varchar(255)，字段长度为 255
- 两个字节表示长度：变长字段长度>255 字节
变长字段的真实数据占用的字节数会按照列的顺序逆序存放，原因：使得位置靠前的记录的真实数据和数据对应的字段长度信息可以同时在一个 CPU Cache Line 中，这样就可以提高 CPU Cache 的命中率。
NULL 值列表

MySQL 的 Compact 行格式中会用「NULL 值列表」来标记值为 NULL 的列，NULL 值并不会存储在行格式中的真实数据部分。
- 二进制位的值为 1 时，代表该列的值为 NULL。
- 二进制位的值为 0 时，代表该列的值不为 NULL。
记录头信息
- delete_mask ：标识此条数据是否被删除。从这里可以知道，我们执行 detele 删除记录的时候，并不会真正的删除记录，只是将这个记录的 delete_mask 标记为 1。
- next_record：下一条记录的位置。从这里可以知道，记录与记录之间是通过链表组织的。指向的是下一条记录的「记录头信息」和「真实数据」之间的位置，这样的好处是向左读就是记录头信息，向右读就是真实数据，比较方便。
- record_type：表示当前记录的类型，0 表示普通记录，1 表示 B+ 树非叶子节点记录，2 表示最小记录，3 表示最大记录
真实数据
- row_id: 如果我们建表的时候指定了主键或者唯一约束列，那么就没有 row_id 隐藏字段了。如果既没有指定主键，又没有唯一约束，那么 InnoDB 就会为记录添加 row_id 隐藏字段。row_id 不是必需的，占用 6 个字节。
- trx_id: 事务 id，表示这个数据是由哪个事务生成的。 trx_id 是必需的，占用 6 个字节。
- roll_pointer: 这条记录上一个版本的指针。roll_pointer 是必需的，占用 7 个字节。

变长字段长度列表逆序存放

核心目的是为了提高 CPU 处理数据的效率（缓存局部性）。我们可以从以下三个层面来拆解：

假设一张表有三列变长字段：col1, col2, col3。

在“记录的真实数据”部分，它们是顺序存放的：[col1的数据][col2的数据][col3的数据]。
但在“变长字段长度列表”中，它们的长度信息是逆序存放的：[col3的长度][col2的长度][col1的长度]。

这意味着，col1 的“长度信息”和“真实数据”在物理磁盘/内存地址上是距离最近的。

CPU 从内存读取数据时，并不是按字节读，而是按 Cache Line（缓存行，通常是 64 字节） 为单位整块读取的。

如果不逆序： col1 的长度信息可能在这一块 Cache Line 的开头，而 col1 的真实数据可能在很远之后，CPU 需要发起两次内存读取。
因为逆序： col1 的长度信息和它的真实数据极大概率落在同一个 64 字节的 Cache Line 中。

结果： 当 CPU 解析这条记录时，一次读取就能把长度和数据同时加载进高速缓存，减少了访问内存的次数，从而大幅提升了处理速度。

一句话总结： 逆序是为了让同个字段的“长度信息”和“真实数据”在物理上靠得更近，从而利用 CPU 缓存的特性加速数据解析。

varchar(n) 中 n 的最大取值

MySQL 规定，除了 TEXT 和 BLOB 这种会存放在溢出页（Off-page）的大字段外，普通字段、变长字段长度列表以及 NULL 值列表的总和不能超过 65535 个字节。

所有字段的长度 + 变长字段字节数列表所占用的字节数（字段长度决定列表占用字节数） + NULL 值列表所占用的字节数 <= 65535

如果一张表只有一个 varchar(n) 字段，且允许为 NULL，字符集为 ascii（一个字符占一个字节）。varchar(n) 中 n 最大取值为 65532：

$65535 - 2 (\text{长度列表}) - 1 (\text{NULL 标识}) = 65532 \text{ 字节}$

行溢出后，MySQL 是怎么处理的？

如果一个数据页存不了一条记录，InnoDB 存储引擎会自动将溢出的数据存放到「溢出页」中。

Compact 行格式针对行溢出的处理是这样的：当发生行溢出时，在记录的真实数据处只会保存该列的一部分数据，而把剩余的数据放在「溢出页」中，然后真实数据处用 20 字节存储指向溢出页的地址，从而可以找到剩余数据所在的页。

Compressed 和 Dynamic 这两种格式采用完全的行溢出方式，记录的真实数据处不会存储该列的一部分数据，只存储 20 个字节的指针来指向溢出页。而实际的数据都存储在溢出页中。

索引

索引常见问题

MySQL_1774517357996

在创建表时，InnoDB 存储引擎会根据不同的场景选择不同的列作为索引：

如果有主键，默认会使用主键作为聚簇索引的索引键（key）；
如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键（key）；
在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键（key）；

其它索引都属于辅助索引（Secondary Index），也被称为二级索引或非聚簇索引。创建的主键索引和二级索引默认使用的是 B+Tree 索引。

主键索引的 B+Tree 和二级索引的 B+Tree 区别如下：

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

回表：用二级索引查询时，先检二级索引中的 B+Tree 的索引值（商品编码，product_no），找到对应的叶子节点，然后获取主键值，然后再通过主键索引中的 B+Tree 树查询到对应的叶子节点，然后获取整行数据。这个过程叫「回表」，也就是说要查两个 B+Tree 才能查到数据。不过，当查询的数据是能在二级索引的 B+Tree 的叶子节点里查询到，这时就不用再查主键索引查。

索引覆盖：二级索引的 B+Tree 就能查询到结果的过程。

按字段特性分类：

主键索引 PRIMARY KEY：建立在主键字段上的索引，通常在创建表的时候一起创建，一张表最多只有一个主键索引，索引列的值不允许有空值。
唯一索引 UNIQUE KEY：建立在 UNIQUE 字段上的索引，一张表可以有多个唯一索引，索引列的值必须唯一，但是允许有空值。
普通索引 INDEX：建立在普通字段上的索引，既不要求字段为主键，也不要求字段为 UNIQUE
前缀索引：对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引，前缀索引可以建立在字段类型为 char、 varchar、binary、varbinary 的列上。使用前缀索引的目的是为了减少索引占用的存储空间，提升查询效率

按索引个数分类：

单列索引
联合索引：将多个字段组合成一个索引，如（a，b）。当在联合索引查询数据时，先按 a 字段比较，在 a 相同的情况下再按 b 字段比较。

最左匹配原则：按照最左优先的方式进行索引的匹配。在使用联合索引进行查询的时候，如果不遵循「最左匹配原则」，联合索引会失效，这样就无法利用到索引快速查询的特性了。是因为 (a, b, c) 联合索引，是先按 a 排序，在 a 相同的情况再按 b 排序，在 b 相同的情况再按 c 排序。所以，b 和 c 是全局无序，局部相对有序的（只有在 a 相同的情况才，b 才是有序的），这样在没有遵循最左匹配原则的情况下，是无法利用到索引的。利用索引的前提是索引里的 key 是有序的。

联合索引的最左匹配原则，在遇到范围查询（如 >、<）的时候，就会停止匹配，也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。注意，对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配。

MySQL_1774519631256

从数据页的角度看 B+ 树

MySQL_1774518057116

MySQL_1774520068548

(1). 每个节点都是一个数据页，这张 B+ 树共有三层，代表了查找数据的路径：

根节点 (Root Node, 页 38)：索引的入口。它不存储实际数据，只存储“页目录”，记录下层节点的最小主键值及其对应的页号。
非叶子节点 (Internal Nodes, 页 30、页 36)：充当“目录”的角色。它们存储主键值和指向下一层页面的指针，引导搜索流向。
叶子节点 (Leaf Nodes, 页 10、22、16、8)：索引的终点。这是真正存放数据（Data）的地方。在聚簇索引（通常是主键索引）中，这里存储的是完整的行记录。

(2). 观察图片中的每一个“框”（即一个页），你会看到几个关键组件：

最小记录 & 最大记录： 每个页都有两个虚拟的边界记录，用来标识当前页存储的主键范围。
页目录 (Page Directory)： 这是页内部的“小索引”。为了避免在页内进行耗时的遍历，InnoDB 将页内的记录分组，并在页目录中保存每组最后一条记录的偏移量。这样在页内查找时，可以用二分法快速定位。
记录 (Records)：
- 在非叶子节点中，记录是 [主键值 + 页号]。
- 在叶子节点中，记录是 [主键值 + 实际数据]。
双向链表： 你会注意到同层级的页之间（例如页 10、22、16 之间）有双向箭头。这意味着所有页组成了一个双向链表。
- 作用： 这使得数据库在进行范围查询（例如 SELECT * FROM table WHERE id > 1 AND id < 5）时，只需要找到起始页，然后顺着链表往后读即可，不需要每次都从根节点重新查找。

(3). 模拟一次查询过程，假设你要查找 ID = 5 的数据：

从根节点（页 38）开始：发现 $1 \le 5 < 7$ ，于是根据指针跳到页 30。
在页 30 查找：发现 $3 \le 5 < \text{更大值}$ （根据目录发现 5 落在页 16 的范围），于是跳到页 16。
在页 16 查找：通过页目录进行二分查找，迅速定位到 ID=5 的记录，读取其对应的 Data。

这种设计的核心优势在于减少磁盘 I/O 次数：

即使表中有上千万行数据，B+ 树的高度通常也只有 3~4 层。
这意味着找到任何一条数据，通常只需要 3~4 次磁盘读取即可。

(4). B+ 树是存储在 .ibd 文件里，在 MySQL 中，如果你开启了 innodb_file_per_table（现在默认都是开启的），那么每张表都会对应磁盘上的一个 .ibd 文件。

这个文件在物理上是由连续的 16KB 页面 (Page) 组成的。
B+ 树的所有节点（根节点、中间节点、叶子节点）都本质上是这个文件里的某一个“页”。

(5). B+ 树是怎么加载到内存的？

InnoDB 在内存中开辟了一块空间，叫做 Buffer Pool（缓冲池）。

不是加载全表：数据库平时绝大部分数据是老老实实呆在磁盘（.ibd 文件）里的。
以“页”为单位：当你查询某条数据时，InnoDB 只会把这条数据所在的那个 16KB 的页从磁盘读到 Buffer Pool 里。
只留热点：如果 Buffer Pool 满了，它会根据 LRU（最近最少使用）算法，把那些很久没人访问的页踢回磁盘，腾出空间给新的页。

你担心的“占用巨大空间”主要来自于叶子节点（因为存了真实数据）。但为了找到数据，我们其实主要只需要把“非叶子节点”（目录项）常驻内存即可：

假设一个非叶子节点页（16KB）能存约 1000 个主键 + 指针。
一棵 3 层的 B+ 树：
- 第一层（根节点）：1 个页。
- 第二层：1000 个页。
- 这两层加起来也就 1001 个页，约 16MB。
结果：只要花 16MB 内存，你就能管住第三层（叶子节点）里 100 万项数据。

对于数据库来说，常驻这 16MB 的目录太划算了。查询时，先在内存里的目录快速定位，最后只去磁盘读那一个特定的 16KB 数据页就行了。

“预读”机制（Read-Ahead）：为了让你觉得“树就在内存里”，InnoDB 还会做预判：如果你连续访问了同一个“区”里的好几个页，它会猜测你可能要进行顺序扫描，于是提前把相邻的页也加载进 Buffer Pool。

所以没有任何节点是“跳过磁盘”直接存在内存里的，它们在磁盘上都有备份。但是，它们在内存里的“驻留时间”完全不同：

根节点 (Root Node)：几乎永久常驻内存。它是所有查询的入口。由于几乎所有的查询都要经过它，它的访问频率极高，因此它几乎 100% 常驻在内存（Buffer Pool）中。
中间节点 (Internal Nodes)：绝大部分常驻内存。它们的数量也不多（比如千万级数据，中间节点也就几百到几千个）。因为它们被访问的概率也很高，所以它们也极大概率会被缓存在内存里，很少被挤出去。
叶子节点（真实数据）：按需加载，只有被你查到的、或者最近刚查过的才在内存里。

(6). 为什么 SELECT * 且不加索引（全表扫描）是性能杀手

它会强迫数据库把磁盘里所有的叶子节点页按顺序搬进内存。
这会瞬间把 Buffer Pool 里原本缓存好的“热点数据”全部挤出去（这叫 Buffer Pool 污染）。
导致接下来一段时间，别人的正常查询都要重新去磁盘读，系统性能瞬间暴跌。

为什么采用 B+ 树作为索引

MySQL_1774518081301

MySQL 是会将数据持久化在硬盘，而存储功能是由 MySQL 存储引擎实现的，所以讨论 MySQL 使用哪种数据结构作为索引，实际上是在讨论存储引使用哪种数据结构作为索引，InnoDB 是 MySQL 默认的存储引擎，它就是采用了 B+ 树作为索引的数据结构。

要设计一个 MySQL 的索引数据结构，不仅仅考虑数据结构增删改的时间复杂度，更重要的是要考虑磁盘 I/0 的操作次数。因为索引和记录都是存放在硬盘，硬盘是一个非常慢的存储设备，我们在查询数据的时候，最好能在尽可能少的磁盘 I/0 的操作次数内完成。

二分查找树虽然是一个天然的二分结构，能很好的利用二分查找快速定位数据，但是它存在一种极端的情况，每当插入的元素都是树内最大的元素，就会导致二分查找树退化成一个链表，此时查询复杂度就会从 O(logn) 降低为 O(n)。

为了解决二分查找树退化成链表的问题，就出现了自平衡二叉树，保证了查询操作的时间复杂度就会一直维持在 O(logn) 。但是它本质上还是一个二叉树，每个节点只能有 2 个子节点，随着元素的增多，树的高度会越来越高。

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

B 树和 B+ 都是通过多叉树的方式，会将树的高度变矮，所以这两个数据结构非常适合检索存于磁盘中的数据。

但是 MySQL 默认的存储引擎 InnoDB 采用的是 B+ 作为索引的数据结构，原因有：

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

单表不超过 2000w 行？

MySQL_1774518102869

计算方式

我们来计算 3 层 B+ 树最多可以有多少个叶子节点、多少行数据？

MySQL_1774520068548

(1) 决定层数的两个核心变量：M 和 L

M（扇出值）： 指一个非叶子节点（索引页）能存储多少个 [主键 + 指针]。
- 公式： $M \approx \frac{16KB - \text{页头空间}}{\text{主键长度} + \text{指针长度}}$
- 通常情况下， $M$ 的值在 1000 到 1200 之间（假设主键是 8 字节的 BIGINT）。
- 主键越短，扇出值 $M$ 越大，树就越“矮胖”，层数就越少。
L（行密度）： 指一个叶子节点（数据页）能存储多少条真实的行记录。
- 公式： $L \approx \frac{16KB - \text{页头空间}}{\text{单行数据字节数}}$
- 如果一行 1KB， $L \approx 15$ ；如果一行很小（如 100B）， $L \approx 150$ 。
- 行记录越小，叶子节点能承载的数据越多，在同等数据量下，层数可能更少。

(2) 什么时候 3 层达到极限？

当总数据量 $N$ 超过了 3 层树所能承载的最大上限时，根节点会分裂，树增高。这个上限的数学表达式是：

$\text{Max } N_{\text{3-layer}} = M \times M \times L$

这意味着，当你的数据量 $N$ 满足以下条件时，树会变成 4 层：

$N > M^2 \times L$

以标准 BIGINT 主键和 1KB 行高为例：

$M \approx 1170$
$L \approx 15$
$1170 \times 1170 \times 15 = 20,533,500$ （约 2000 万行）

结论： 当你插入第 20,533,501 条数据时，由于原有的 3 层结构已经塞满了（根节点连通的 1170 个二级节点都已经各自连通了 1170 个叶子节点），数据库必须再往上顶一层，产生新的根节点，从而变成 4 层。

(3) 哪些因素会“提前”让树变成 4 层？

并不是所有的表都能撑到 2000 万行才变 4 层。以下情况会让树提前长高：

主键太长（M 减小）：

如果你使用 UUID（36 字节）或很长的字符串做主键，原本一个索引页能存 1170 个指针，现在可能只能存 300 个。 $300 \times 300 \times 15 = 1,350,000$ 。这种情况下，135 万行数据就会让树变成 4 层。
行记录太宽（L 减小）：

如果你的表字段非常多，平均一行记录占 4KB，那么一个叶子节点只能存 3 条记录。 $1170 \times 1170 \times 3 = 4,106,700$ 。这种情况下，400 万行数据就会变 4 层。
页碎片与填充因子：

实际存储中，页通常不会 100% 塞满（为了留出插入余地，InnoDB 默认可能只用到 15/16）。如果存在大量随机插入导致页分裂，空间利用率可能只有 50%，这会显著降低 $M$ 和 $L$ 的实际值，让树更快长高。

(4) 2000w 行数据物理上的占用情况

为了存这 2000 万行数据，磁盘上的 .ibd 文件实际上占用了：

索引页： $1 + 1170 = 1171$ 个页 $\approx 18$ MB。
数据页： $1 170 \times 1170 = 11,368,900$ 个页 $\approx 21$ GB。

(5) 超过 2000w 行数据应该怎么办？

当数据量真的大到单表无法承受时，在逻辑上，它绝不会分裂成“两个 B+ 树”，而是原本的那棵 B+ 树向上“拔高”了一层，变成了 4 层。虽然树可以长到 4 层、5 层甚至更高，但性能会发生断崖式下跌：

磁盘 I/O 翻倍： 3 层树通常只需要 1 次磁盘 I/O（因为前两层基本在内存里）。到了 4 层，如果第 3 层没法完全缓存，每次查询可能要多跑一次磁盘，可能需要 2 次磁盘 I/O，响应时间直接慢了一倍。
缓存命中率： 当表太大的时候，内存（Buffer Pool）无法完全覆盖索引页，会导致频繁的页置换，产生明显的 I/O 抖动。

但在架构层面，当数据量真的大到单表无法承受时，我们可以通过手动手段把它变成“多个 B+ 树”。

分库分表 (Sharding) 我们将一张 4000w 行的表拆成两张各 2000w 行的表（比如 table_0 和 table_1）。
- 结果： 此时确实变成了两个独立的 B+ 树。
- 代价： 你的业务代码必须引入中间件（如 ShardingSphere），手动决定去哪棵树里查。
分区表 (Partitioning) 在 MySQL 内部，你可以定义“分区”。
- 原理： 表面上是一张表，底层其实是多个独立的 .ibd 文件。
- 结果： 每个分区都有自己独立的 B+ 树。

总结

MySQL 的表数据是以页的形式存放的，页在磁盘中不一定是连续的。
页的空间是 16K, 并不是所有的空间都是用来存放数据的，会有一些固定的信息，如，页头，页尾，页码，校验码等等。
在 B+ 树中，叶子节点和非叶子节点的数据结构是一样的，区别在于，叶子节点存放的是实际的行数据，而非叶子节点存放的是主键和页号。
索引结构不会影响单表最大行数，2000W 也只是推荐值，超过了这个值可能会导致 B + 树层级更高，影响查询性能。

索引失效

MySQL_1774518292910

发生索引失效的 6 种情况：

使用左或者左右模糊匹配：like %xx 或者 like %xx%；
对索引列使用函数；
对索引列进行表达式计算；
MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。如果字符串是索引列，而条件语句中的输入参数是数字的话，那么索引列会发生隐式类型转换，由于隐式类型转换是通过 CAST 函数实现的，等同于对索引列使用了函数，所以就会导致索引失效；
联合索引要能正确使用需要遵循最左匹配原则，也就是按照最左优先的方式进行索引的匹配，否则就会导致索引失效；
在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效；

count(*) 和 count(1)

MySQL_1774518399952

MySQL_1774538224728

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

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

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

为什么要通过遍历的方式来计数?

InnoDB 存储引擎是支持事务的，同一个时刻的多个查询，由于多版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少行”也是不确定的，所以无法像 MyISAM 一样，只维护一个 row_count 变量。

count 性能分析

聚簇索引（主键索引）： 它的叶子节点存放的是整行记录的所有字段。如果一行有 1KB，一个页只能存 15 条数据。
二级索引（辅助索引）： 它的叶子节点非常“苗条”，只存放 [索引字段的值 + 主键值]。假设这个字段是 INT 类型，加上主键指针，一条记录可能才 20 字节。

统计 100 万行数据：

扫描聚簇索引：可能需要读取 66,666 个页（约 1GB 数据）。
扫描二级索引：由于每条记录极小，一个页能塞下近 800 条记录，可能只需要读取 1,250 个页（约 20MB 数据）。

总结：

COUNT(*) 或 COUNT(1)：MySQL 优化器非常聪明。它知道你只是想数行数，所以它会自动在所有索引里找那个体积最小（最矮胖）的二级索引去扫描。如果没有二级索引，它才被迫去扫聚簇索引。
COUNT(字段)：要求统计该字段不为 NULL 的个数。此时，MySQL 必须去检查这个特定字段的值。
- 如果没索引：它只能全表扫描，逐行把字段读出来判断是否为 NULL。
- 如果有索引：索引本身就是按该字段排序的，且索引树里已经排除了（或者标记了）NULL 的位置，扫描速度极快。

如何优化 count(*)？

近似值使用 show table status 或者 explain 命令来表进行估算
额外表保存计数值

分页性能问题及优化

MySQL_1774518437270

limit offset, size 比 limit size 要慢，且 offset 的值越大，sql 的执行速度越慢。
当 offset 过大，会引发深度分页问题，目前不管是 mysql 还是 es 都没有很好的方法去解决这个问题。只能通过限制查询数量或分批获取的方式进行规避。
遇到深度分页的问题，多思考其原始需求，大部分时候是不应该出现深度分页的场景的，必要时多去影响产品经理。
如果数据量很少，比如 1k 的量级，且长期不太可能有巨大的增长，还是用 limit offset, size 的方案吧，整挺好，能用就行。

规避深度分页？

游标/标签法（Seek Method）： 不使用 offset，而是记住上一页最后一条记录的 ID。 WHERE id > 1000000 LIMIT 10。这样 B+ 树可以直接通过索引定位到 100 万的位置开始往后读，效率是 $O(\log N)$ 。
业务侧限制： 这就是建议“去影响产品经理”的原因。绝大多数正常用户不会去翻第 10,000 页。你可以限制搜索结果只展示前 100 页（比如 Google 或百度其实都有隐藏的翻页上限）。
滚动 API： 在 ES 中使用 search_after 或 Scroll API。它们通过维护一个快照或状态，像“拉链”一样一点点往后读，而不是每次都从头数起。

事务

事务隔离级别的实现

MySQL_1774540345334

（1）事务并发问题

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

(2) 4 种隔离级别

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

读未提交（read uncommitted），指一个事务还没提交时，它做的变更就能被其他事务看到；
读提交（read committed），指一个事务提交之后，它做的变更才能被其他事务看到；
可重复读（repeatable read），指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，MySQL InnoDB 引擎的默认隔离级别；
串行化（serializable ）；会对记录加上读写锁，在多个事务对这条记录进行读写操作时，如果发生了读写冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行；

(3) 四种隔离级别是如何实现的？

对于「读未提交」隔离级别的事务来说，因为可以读到未提交事务修改的数据，所以直接读取最新的数据就好了；
对于「串行化」隔离级别的事务来说，通过加读写锁的方式来避免并行访问；
对于「读提交」和「可重复读」隔离级别的事务来说，它们是通过 Read View 来实现的，它们的区别在于创建 Read View 的时机不同，大家可以把 Read View 理解成一个数据快照，就像相机拍照那样，定格某一时刻的风景。「读提交」隔离级别是在「每个语句执行前」都会重新生成一个 Read View，而「可重复读」隔离级别是「启动事务时」生成一个 Read View，然后整个事务期间都在用这个 Read View。

隔离级别	ReadView 生成时机	表现结果
读提交 (RC)	每一条 `SELECT` 语句执行前都会生成一个新的 ReadView。	事务能看到其他事务已经提交的最新修改。
可重复读 (RR)	只有事务中第一条 `SELECT` 语句执行时生成一个 ReadView，之后整个事务都用这一个。	无论其他事务如何修改并提交，当前事务看到的数据始终一致。

(4) 隔离级别解决的问题

MySQL_1774542314297

MySQL InnoDB 引擎的默认隔离级别虽然是「可重复读」，但是它很大程度上避免幻读现象（并不是完全解决了），解决的方案有两种：

针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。
针对当前读（select … for update 等语句），特指 SELECT ... FOR UPDATE、SELECT ... LOCK IN SHARE MODE、UPDATE、DELETE，它们必须读取数据库中最新的记录。既然要读最新的，MVCC 的历史快照就没用了。如果不加锁，别的事务趁机塞进一条新数据，你读到的“最新”就会多出一行，这就是幻读。所以通过 next-key lock（记录锁 + 间隙锁）方式解决了幻读，当执行当前读时，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题。

(5) Read View 在 MVCC 里如何工作的

MySQL_1774543016169

MySQL_1774543023015

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

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

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

(6) 总结

事务是在 MySQL 引擎层实现的，我们常见的 InnoDB 引擎是支持事务的，事务的四大特性是原子性、一致性、隔离性、持久性，我们这次主要讲的是隔离性。

当多个事务并发执行的时候，会引发脏读、不可重复读、幻读这些问题，那为了避免这些问题，SQL 提出了四种隔离级别，分别是读未提交、读已提交、可重复读、串行化，从左往右隔离级别顺序递增，隔离级别越高，意味着性能越差，InnoDB 引擎的默认隔离级别是可重复读。

要解决脏读现象，就要将隔离级别升级到读已提交以上的隔离级别，要解决不可重复读现象，就要将隔离级别升级到可重复读以上的隔离级别。

而对于幻读现象，不建议将隔离级别升级为串行化，因为这会导致数据库并发时性能很差。MySQL InnoDB 引擎的默认隔离级别虽然是「可重复读」，但是它很大程度上避免幻读现象（并不是完全解决了）。

读提交和可重复读的实现，是通过「事务的 Read View 里的字段」和「记录中的两个隐藏列」的比对，来控制并发事务访问同一个记录时的行为，这就叫 MVCC（多版本并发控制）。

在可重复读隔离级别中，普通的 select 语句就是基于 MVCC 实现的快照读，也就是不会加锁的。而 select .. for update 语句就不是快照读了，而是当前读了，也就是每次读都是拿到最新版本的数据，但是它会对读到的记录加上 next-key lock 锁。

可重复读隔离级别，完全解决幻读吗

MySQL_1774540370075

当前读：MySQL 里除了普通查询是快照读，其他都是当前读，比如 update、insert、delete，这些语句执行前都会查询最新版本的数据，然后再做进一步的操作。

可重复读发生幻读场景 1

MySQL_1774543565882

可重复读发生幻读场景 2
- 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，从而避免其他事务插入一条新记录。

总结：在 RR 隔离级别下，如果你想彻底杜绝幻读，必须在事务的第一条查询语句就加锁（FOR UPDATE）。

锁

锁类型

MySQL_1774553840981

全局锁

FTWRL (Flush Table with Read Lock)： 执行后，整个数据库处于只读状态。所有的写操作（增删改、建表、修改表结构）都会被阻塞。
典型场景： 主要用于全库逻辑备份（如 mysqldump）。它能确保备份出来的所有表在时间点上是一致的，不会出现“订单表存了数据，但余额表还没扣钱”的尴尬情况。

表级锁

这类锁作用于整张表，开销小但并发度低。

表锁： 手动通过 lock tables 锁定。由于 InnoDB 支持更细的行锁，现在很少手动用这个。
元数据锁 (MDL)： 当你对一个表做增删改查时，自动加 MDL 读锁；当你修改表结构（DDL）时，自动加 MDL 写锁。
- 作用：防止你在查询数据的同时，别人把表结构删了或者改了。
意向锁 (Intention Lock)： 这是一种“通知”机制。如果你想锁住某一行，必须先在表上打个“意向锁”的招呼。
- 作用：提高效率。当别人想加“表锁”时，看一眼有没有意向锁就知道表里有没有行正在被锁定，不需要一行行去检查。
AUTO-INC 锁： 专门为自增主键设计的。在插入数据时获取，保证多个事务并发插入时，拿到的 ID 是连续且不重复的。

行级别锁

这是 InnoDB 的核心竞争力。它只锁定特定的行，允许不同事务同时操作不同的行，并发性能极高。

Record Lock (记录锁)： 锁定索引记录本身。例如 WHERE id = 1 FOR UPDATE，只锁住 ID 为 1 的这一行。
Gap Lock (间隙锁)： 锁定一个范围，但不包括记录本身。它的唯一目的就是防止幻读。它锁住记录之间的“空隙”，不让别人往里插数据。
Next-Key Lock (临键锁)： 记录锁 + 间隙锁。它是 InnoDB 在“可重复读 (RR)”隔离级别下解决幻读的杀手锏。它既锁住记录，也锁住记录前面的间隙。

MySQL InnoDB 引擎的默认隔离级别虽然是「可重复读」，但是它很大程度上避免幻读现象（并不是完全解决了）解决的方案有两种：

针对快照读（普通 select 语句），是通过 MVCC 方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条数据，是查询不出来这条数据的，所以就很好了避免幻读问题。
针对当前读（select … for update 等语句），是通过 next-key lock（记录锁 + 间隙锁）方式解决了幻读，因为当执行 select … for update 语句的时候，会加上 next-key lock，如果有其他事务在 next-key lock 锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好了避免幻读问题。

共享锁 (S) 与排他锁 (X)

LOCK IN SHARE MODE (共享锁/S 锁)： 允许其他事务读，但禁止修改。
FOR UPDATE (排他锁/X 锁)： 自己要修改，禁止其他事务读和写。

    begin;
    //对读取的记录加共享锁
    select ... lock in share mode;
    commit; //锁释放
    
    begin;
    //对读取的记录加排他锁
    select ... for update;
    commit; //锁释放

共享和排他的间隙锁是没有区别的，他们相互不冲突，且功能相同，即两个事务可以同时持有包含共同间隙的间隙锁。

间隙锁不分共享还是排他：共享和排他的间隙锁是没有区别的，他们相互不冲突，且功能相同，即两个事务可以同时持有包含共同间隙的间隙锁：

间隙锁的唯一目的就是防止幻读（禁止别人往这个间隙插数据）。
场景：事务 A 拿到了 (5, 10) 的间隙锁，事务 B 也可以拿到 (5, 10) 的间隙锁。它们都只是在说：“谁也别想往这里面插新东西。”它们的目标是一致的，所以互相兼容。

Next-Key Lock

$\text{Next-Key Lock} = \text{Gap Lock (间隙锁)} + \text{Record Lock (记录锁)}$

锁定区间： 前开后闭 (a, b]。
冲突发生在记录锁上。
- 间隙部分 (a, b) 是兼容的。
- 记录部分 b 是不兼容的。如果事务 A 拿到了 b 的 X 型记录锁，事务 B 想拿 b 的 S/X 记录锁，就会被阻塞。
- 也就是如果一个事务获取了 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，是会被阻塞的（记录锁：要考虑 X 型与 S 型关系。X 型的记录锁与 X 型的记录锁是冲突的）
- 范围为 (1006, +∞] 的 next-key lock，两个事务是可以同时持有的，不会冲突。因为 +∞ 并不是一个真实的记录，无法被修改，自然就不需要考虑 X 型与 S 型关系。

锁的退化

加锁的对象是索引，加锁的基本单位是 next-key lock，它是由记录锁和间隙锁组合而成的，next-key lock 是前开后闭区间 (a, b]，而间隙锁是前开后开区间 (a, b)。在能使用记录锁或者间隙锁就能避免幻读现象的场景下， Next-Key Lock 就会退化成记录锁或间隙锁。

为了提升并发性能，InnoDB 优化器会尝试减小锁的范围：

退化为记录锁： 查询一个确实存在的唯一记录。
- 在使用唯一索引（如主键）进行等值查询，且记录存在时。因为唯一性保证了不会有幻读，所以不需要间隙锁，只锁住这一行就行。
退化为间隙锁： 查询一个不存在的记录。
- 比如查询一个不存在的记录。既然记录本身不存在，只要锁住它所在的间隙就能防止别人插入，所以不需要记录锁。

加锁的对象永远是索引。如果你查询的字段没加索引，InnoDB 就会被迫升级为表锁（其实是给聚簇索引的所有行都加锁），那性能就彻底崩了。

加锁流程

MySQL_1774553861285

加行级锁的 SQL

在 InnoDB 中，普通的 SELECT 是快照读，不加锁。以下操作会触发当前读并加行级锁：

显式锁定读： SELECT ... LOCK IN SHARE MODE（加 S 锁）和 SELECT ... FOR UPDATE（加 X 锁）。
修改类语句： UPDATE 和 DELETE。它们在执行前需要先定位到记录，这个定位过程就是一次“当前读”。
插入语句： INSERT 会加“插入意向锁”，但在处理主键冲突时也可能触发 S 锁或 X 锁。

行级锁的三种形态

Record Lock (记录锁)： 仅锁定索引的一行记录。Lock = R
Gap Lock (间隙锁)： 锁定一个区间，不包括记录本身。目的是防止幻读（禁止插入）。Lock = (a, b)
Next-Key Lock (临键锁)： 记录锁 + 间隙锁的组合，锁定一个前开后闭的区间，如 (5, 10]。Lock = (a, b]

如何加行级锁

加锁规则：

锁是加载在索引上的，不是加在数据行上的。
一切努力都是为了防止幻读。如果唯一性已经能保证不产生幻读，锁就会变轻（退化）；如果不能保证，锁就会变重（维持 Next-Key 或间隙锁）。

针对不同索引的加锁策略，不同的加锁方式

唯一索引（主键）等值查询

记录存在： Next-Key Lock 退化为 Record Lock（仅锁住这一行）。
记录不存在：锁在第一条大于该查询记录的记录， next-key lock 会退化成「间隙锁」。查找 id=2 的记录：

MySQL_1774556602627

唯一索引（主键）范围查询

这是一种复杂的场景。比如 WHERE id >= 5 AND id < 10。
MySQL 会根据扫描到的每一个索引项加 Next-Key Lock。如果是 >= 且值存在，起始位置的锁会退化为记录锁。
大于：select * from user where id > 15 for update

MySQL_1774557170976

大于等于：select * from user where id >= 15 for update;

MySQL_1774557197070

小于 (记录不存在)：select * from user where id < 6 for update;

MySQL_1774557347363

小于 (记录存在)： select * from user where id < 5 for update;

MySQL_1774557359162

小于等于：select * from user where id <= 5 for update;

MySQL_1774557423504

非唯一索引（普通索引）等值查询

记录存在：不仅要对符合条件的记录加 Next-Key Lock，由于该值不唯一，为了防止后面插入相同的值，还会对下一个索引项加 Gap Lock。select * from user where age = 22 for update;

MySQL_1774557546790

记录不存在：同样退化为 Gap Lock。select * from user where age = 25 for update;

MySQL_1774557574965

非唯一索引范围查询

非唯一索引在进行范围查询时，基本不会发生退化，通常会对扫描到的所有区间加 Next-Key Lock。这在高并发下非常容易导致死锁。select * from user where age >= 22 for update;

MySQL_1774557594314

没有加索引的查询（危险操作）

如果你执行 UPDATE t_user SET age = 10 WHERE name = '张三' 而 name 字段没有索引：InnoDB 会进行全表扫描。由于它是边扫描边加锁，最终会把聚簇索引上的所有行都加上 X 型的 Next-Key Lock。效果上等同于锁住了整张表，导致其他所有的增删改操作全部阻塞。

总结

非唯一索引和主键索引的范围查询的加锁规则不同之处在于：

唯一索引在满足一些条件的时候，索引的 next-key lock 退化为间隙锁或者记录锁。
非唯一索引范围查询，索引的 next-key lock 不会退化为间隙锁和记录锁。

其实理解 MySQL 为什么要这样加锁，主要要以避免幻读角度去分析，这样就很容易理解这些加锁的规则了。

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

唯一索引（主键索引）加锁的流程图如下。（注意这个流程图是针对「主键索引」的，如果是二级索引的唯一索引，除了流程图中对二级索引的加锁规则之外，还会对查询到的记录的主键索引项加「记录锁」，流程图没有提示这一个点，所以在这里用文字补充说明下）

MySQL_1774558383332

非唯一索引加锁的流程图：

MySQL_1774558105158

update 没加锁会锁全表吗

如果不小心写了一条没带索引的 UPDATE 语句，你可能会瞬间锁死整张表。

行锁是怎么变成“表锁”的？

InnoDB 的行锁（Row Lock）实际上是索引锁。

有索引时： 数据库通过索引 B+ 树精准定位到那几行，只给这几行加锁。
没索引时： 优化器被迫进行全表扫描（Full Table Scan）。
连锁反应： InnoDB 在扫描过程中，为了保证一致性，会给扫描到的每一条记录都加上 X 型（排他）临键锁（Next-Key Lock）。

结论： 虽然名义上是“行锁”，但因为你锁住了表里的每一行，效果上就等同于锁住了整张表。此时，任何其他的增删改操作（甚至某些查操作）都会因为拿不到锁而陷入阻塞。

当一条耗时较长的 UPDATE 锁住了全表：

连接堆积： 后续所有的请求都在等待锁释放，导致数据库连接数迅速耗尽。
雪崩效应： 应用层因为拿不到数据库连接，线程被占满，最终导致整个应用服务器停止响应。
恢复困难： 即使你意识到了问题并杀掉进程，Undo Log 的回滚操作依然可能耗费大量时间。

三道防线

开发规范（WHERE 带索引）

在执行 UPDATE 或 DELETE 之前，先用 EXPLAIN 检查一下执行计划。如果 type 列显示是 ALL，绝对不能在生产环境运行。

系统开关 (sql_safe_updates)

开启后，如果你的 UPDATE 语句没有带 WHERE 条件，或者 WHERE 条件里没用到索引，MySQL 会直接报错拒绝执行。命令：SET sql_safe_updates = 1;（建议在生产环境的连接配置中默认开启）。

FORCE INDEX

有时候 MySQL 优化器会“间歇性抽风”。

场景： 表里有索引，但优化器觉得全表扫描更快（比如数据量较小时）。
风险： 优化器的“快”只是针对查询，它没考虑到全表加锁对并发的影响。
解决： 使用 FORCE INDEX([index_name]) 强行指定索引，确保走的是行级锁定。

总结与建议

在生产环境下，每一条 UPDATE 都要认真对待：

小批量： 如果要更新大量数据，建议拆分成 LIMIT 小批次执行。
显式事务： 尽量在事务中操作，方便出问题时快速排查。
索引优先： 没索引的字段，哪怕加个临时索引也比全表扫描强。

记录锁 + 间隙锁可以防止删除操作而导致的幻读吗

可以。

在 MySQL 的可重复读隔离级别下，针对当前读的语句会对索引加记录锁 + 间隙锁，这样可以避免其他事务执行增、删、改时导致幻读的问题。

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

死锁

死锁场景

MySQL_1774553907593

在新增订单的时候做了幂等性校验，做法就是在新增订单记录之前，先通过 select ... for update，语句查询订单是否存在，如果不存在才插入订单记录。而正是因为这样的操作，当业务量很大的时候，就可能会出现死锁：

MySQL_1774559149272

死锁原因：两个事务共同持有了同一段间隙的锁。事务 A 在 select 语句中因为 1007 不存在产生间隙锁，同理事务 B，这里产生的同一范围的间隙锁是互相兼容的，直到 insert 进来，申请了插入意向锁，事务 A 的 insert 在等事务 B 的间隙锁释放，事务 B 的 insert（紧接着执行）也在等事务 A 的间隙锁释放。

两个事务即使生成的间隙锁的范围是一样的，也不会发生冲突，因为间隙锁目的是为了防止其他事务插入数据，因此间隙锁与间隙锁之间是相互兼容的。在执行插入语句时，如果插入的记录在其他事务持有间隙锁范围内，插入语句就会被阻塞，因为插入语句在碰到间隙锁时，会生成一个插入意向锁，然后插入意向锁和间隙锁之间是互斥的关系（不同事务而言，同一事务不影响，同一事务的操作，允许锁的 “自兼容”）。如果两个事务分别向对方持有的间隙锁范围内插入一条记录，而插入操作为了获取到插入意向锁，都在等待对方事务的间隙锁释放，于是就造成了循环等待，满足了死锁的四个条件：互斥、占有且等待、不可强占用、循环等待，因此发生了死锁。

如何避免：直接在 order_no 上加唯一索引 (Unique Key)，不要先 SELECT，而是直接 INSERT

Insert 加锁

通常我们认为增删改都要加锁，但 INSERT 很特殊。为了提高并发，InnoDB 默认不为新插入的记录创建复杂的锁结构。

工作原理： 利用聚簇索引记录中隐藏的 trx_id（事务 ID）。
逻辑： 事务 A 插入一条记录，记录里标记了 A 的事务 ID。如果此时事务 B 想要修改或删除这行，它会检查 trx_id：
- 如果事务 A 还没提交，事务 B 就会帮事务 A 创建一个显式的 排他锁 (X Lock)，然后自己进入等待状态。
好处： 如果没有竞争，就完全没有锁开销。

在某些特殊情况下，INSERT 必须“亮出”自己的锁，不能再隐藏（加锁）：

遇到间隙锁 (Gap Lock)

如果你想插入的地方已经被别人用间隙锁封锁了（为了防止幻读），你不能直接插。

动作： 事务会生成一个 插入意向锁 (Insert Intention Lock)。
状态： 事务进入等待状态，直到对方释放间隙锁。

唯一键冲突 (Duplicate Key)

当你尝试插入一个已经存在的主键或唯一索引值时，为了保证一致性，InnoDB 会对已存在的记录加锁：

主键冲突： 给已存在的记录加 S 型记录锁 (Shared Record Lock)。
唯一二级索引冲突： 给已存在的记录加 S 型临键锁 (Next-Key Lock)。
- 注意：这正是导致死锁的高发区。因为 S 锁（读锁）之间是兼容的，多个事务可能同时持有一行的 S 锁，随后它们又都想升级为 X 锁（写锁）去修改，从而导致循环等待。

插入意向锁

这是 INSERT 专用的一种特殊“间隙锁”。

温和之处： 多个事务可以在同一个间隙内同时插入。比如 (5, 10) 之间，事务 A 插 6，事务 B 插 7，它们持有的插入意向锁互不冲突，可以并发执行。
强硬之处： 它与 间隙锁 (Gap Lock) 和 临键锁 (Next-Key Lock) 完全互斥。只要有人锁住了这段空隙，你的插入意向锁就拿不到，必须排队。

如何避免死锁

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

MySQL_1774560159283

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

日志

MySQL_1774560971410

ACID 特性

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

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

undo log（回滚日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的原子性，主要用于事务回滚和 MVCC。
redo log（重做日志）：是 Innodb 存储引擎层生成的日志，实现了事务中的持久性，主要用于掉电等故障恢复；
binlog （归档日志）：是 Server 层生成的日志，主要用于数据备份和主从复制。

UPDATE t_user SET name = 'xiaolin' WHERE id = 1; 的流程如下:

执行器负责具体执行，会调用存储引擎的接口，通过主键索引树搜索获取 id = 1 这一行记录：
- 如果 id=1 这一行所在的数据页本来就在 buffer pool 中，就直接返回给执行器更新；
- 如果记录不在 buffer pool，将数据页从磁盘读入到 buffer pool，返回记录给执行器。
执行器得到聚簇索引记录后，会看一下更新前的记录和更新后的记录是否一样：
- 如果一样的话就不进行后续更新流程；
- 如果不一样的话就把更新前的记录和更新后的记录都当作参数传给 InnoDB 层，让 InnoDB 真正的执行更新记录的操作；
开启事务， InnoDB 层更新记录前，首先要记录相应的 undo log，因为这是更新操作，需要把被更新的列的旧值记下来，也就是要生成一条 undo log，undo log 会写入 Buffer Pool 中的 Undo 页面，不过在内存修改该 Undo 页面后，需要记录对应的 redo log。
InnoDB 层开始更新记录，会先更新内存（同时标记为脏页），然后将记录写到 redo log 里面，这个时候更新就算完成了。为了减少磁盘 I/O，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘。这就是 WAL 技术，MySQL 的写操作并不是立刻写到磁盘上，而是先写 redo 日志，然后在合适的时间再将修改的行数据写到磁盘上。
至此，一条记录更新完了。
在一条更新语句执行完成后，然后开始记录该语句对应的 binlog，此时记录的 binlog 会被保存到 binlog cache，并没有刷新到硬盘上的 binlog 文件，在事务提交时才会统一将该事务运行过程中的所有 binlog 刷新到硬盘。
事务提交（为了方便说明，这里不说组提交的过程，只说两阶段提交）：
- prepare 阶段：将 redo log 对应的事务状态设置为 prepare，然后将 redo log 刷新到硬盘；
- commit 阶段：将 binlog 刷新到磁盘，接着调用引擎的提交事务接口，将 redo log 状态设置为 commit（将事务设置为 commit 状态后，刷入到磁盘 redo log 文件）；

MySQL_1774563166840

Undo Log 回滚日志

Undo Log 的核心作用是“撤销”。它记录了数据被修改前的值。

保障事务的原子性： 如果你的事务执行到一半报错了，或者你手动执行了 ROLLBACK，MySQL 就会根据 Undo Log 把数据“变回去”。
MVCC（多版本并发控制）： 它是实现“快照读”的关键。当 A 事务修改数据但未提交时，B 事务查询该数据，可以通过 Undo Log 找到历史版本，从而实现非阻塞读。

Buffer Pool 缓冲池

Buffer Pool 的核心作用是“加速”。 数据库的数据最终是存在磁盘上的，但磁盘太慢。

它在内存中开辟了一块空间，缓存了数据页和索引页。
当你查询或修改数据时，MySQL 会先看 Buffer Pool 里有没有。如果有，直接操作内存；如果没有，再从磁盘加载。这样就把大量的磁盘随机 I/O 变成了内存操作。

Redo Log 重做日志

Redo Log 的核心作用是“保命”（持久性）。 既然我们都在 Buffer Pool（内存）里改数据，那万一突然断电，内存数据不就丢了吗？

什么时候刷盘： 遵循 WAL（Write-Ahead Logging） 协议。事务提交时，先顺序写 Redo Log（非常快），只要日志进磁盘了，数据就算安全了。
写满了怎么办： Redo Log 是循环写的（像个圆环）。当写满一圈时，必须停下来，把 Buffer Pool 里的脏数据真正刷到磁盘（Checkpoint），腾出空间继续写。

刷盘时机：

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 参数控制（写入 Page cache 视为写入 redo log，写入磁盘是持久化。两阶段提交）：

MySQL_1774561652489

数据安全性：参数 1 > 参数 2（操作系统宕机，丢失 1s 数据） > 参数 0 （数据库宕机，丢失 1s 数据）
写入性能：参数 0 > 参数 2> 参数 1

Binlog 归档日志

Binlog 的核心作用是“同步”和“恢复”。 它是 MySQL Server 层产生的逻辑日志。

与 Redo Log 的区别：
- 适用对象不同
  - Redo Log 是 InnoDB 引擎特有的，物理日志，记录的是“某个页改了什么”。
  - Binlog 是全引擎通用的，逻辑日志，记录的是“执行了哪条 SQL”。
- 文件格式不同
- 写入方式不同
  - redo log 是循环写，日志空间大小是固定，全部写满就从头开始，保存未被刷入磁盘的脏页日志。
  - binlog 是追加写，写满一个文件，就创建一个新的文件继续写，不会覆盖以前的日志，保存的是全量的日志。
- 用途不同
  - redo log 用于掉电等故障恢复。
  - binlog 用于备份恢复、主从复制；
主从复制如何实现： 从库（Slave）读取主库（Master）的 Binlog，并在本地重新执行一遍，从而达到数据同步的目的。
刷盘时机： 由 sync_binlog 参数控制。

主从复制

MySQL_1774562028366

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

MySQL_1774562081076

两阶段提交

事务提交后，redo log 和 binlog 都要持久化到磁盘，但是这两个是独立的逻辑，可能出现半成功的状态，这样就造成两份日志之间的逻辑不一致。如果没有这两阶段提交，你的数据库主库和从库（或者备份恢复的数据）极有可能会出现数据不一致的情况。

假设没有两阶段提交，我们只能先写一个再写另一个。想象一下如果写到一半断电了：

先写 Redo Log，再写 Binlog： Redo Log 写完了，突然断电，Binlog 没写成。MySQL 重启后，通过 redo log 能将 Buffer Pool 中 id = 1 这行数据的 name 字段恢复到新值 xiaolin，但是 binlog 里面没有记录这条更新语句，在主从架构中，binlog 会被复制到从库，由于 binlog 丢失了这条更新语句，从库的这一行 name 字段是旧值 jay，与主库的值不一致性；
先写 Binlog，再写 Redo Log： Binlog 写完了，突然断电，Redo Log 没写成。由于 redo log 还没写，崩溃恢复以后这个事务无效，所以 id = 1 这行数据的 name 字段还是旧值 jay，而 binlog 里面记录了这条更新语句，在主从架构中，binlog 会被复制到从库，从库执行了这条更新语句，那么这一行 name 字段是新值 xiaolin，与主库的值不一致性；

为了保证这两个日志“要么都成功，要么都失败”，MySQL 采用了类似“分布式事务”的思路，把写入过程拆成了两个阶段：Prepare（准备） 和 Commit（提交）。

第一阶段：Prepare 阶段
1. InnoDB 引擎将更新记录写入 Redo Log。
2. 将这条 Redo Log 的状态标记为 prepare。
3. 此时事务还没正式提交。
第二阶段：Commit 阶段
1. MySQL Server 层将对应的事务写入 Binlog。
2. Binlog 写入成功后，调用引擎的接口，将 Redo Log 的状态修改为 commit。
3. 事务正式完成。

MySQL_1774563199531

如果此时系统在任意环节断电，MySQL 重启后会进行“对账”：

场景 A：Redo Log 只有 prepare，且 Binlog 里没这条记录。 说明 Binlog 还没写完就挂了。
- 处理： 既然 Binlog 没写，为了保证主从一致，直接回滚事务。
场景 B：Redo Log 是 prepare，但 Binlog 里已经有这条记录了。 说明 Binlog 写完了，但在把 Redo Log 改成 commit 之前挂了。
- 处理： 既然 Binlog 都有了，说明从库肯定会同步。
- 对账： 只要 Redo Log 里的事务 ID（XID）能在 Binlog 里找到，就认定事务有效，直接提交事务。

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 文件」意味着写入到了操作系统的文件缓存，然后交由操作系统控制持久化到磁盘的时机。但是这样做的风险是，主机掉电的时候会丢数据。

内存

MySQL_1774563238281

Innodb 存储引擎设计了一个缓冲池（Buffer Pool），来提高数据库的读写性能。Buffer Pool 以页为单位缓冲数据，可以通过 innodb_buffer_pool_size 参数调整缓冲池的大小，默认是 128 M。

Innodb 通过三种链表来管理缓页：

Free List （空闲页链表），管理空闲页；
Flush List （脏页链表），管理脏页；
LRU List，管理脏页 + 干净页，将最近且经常查询的数据缓存在其中，而不常查询的数据就淘汰出去。；

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

预读失效；

Buffer Pool 污染；

什么是预读失效？

先来说说 MySQL 的预读机制。程序是有空间局部性的，靠近当前被访问数据的数据，在未来很大概率会被访问到。所以，MySQL 在加载数据页时，会提前把它相邻的数据页一并加载进来，目的是为了减少磁盘 IO。但是可能这些被提前加载进来的数据页，并没有被访问，相当于这个预读是白做了，这个就是预读失效。

如果使用简单的 LRU 算法，就会把预读页放到 LRU 链表头部，而当 Buffer Pool 空间不够的时候，还需要把末尾的页淘汰掉。如果这些预读页如果一直不会被访问到，就会出现一个很奇怪的问题，不会被访问的预读页却占用了 LRU 链表前排的位置，而末尾淘汰的页，可能是频繁访问的页，这样就大大降低了缓存命中率。

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

我们不能因为害怕预读失效，而将预读机制去掉，大部分情况下，局部性原理还是成立的。要避免预读失效带来影响，最好就是让预读的页停留在 Buffer Pool 里的时间要尽可能的短，让真正被访问的页才移动到 LRU 链表的头部，从而保证真正被读取的热数据留在 Buffer Pool 里的时间尽可能长。

那到底怎么才能避免呢？

InnoDB 对 LRU 做了一些优化，我们熟悉的 LRU 算法通常是将最近查询的数据放到 LRU 链表的头部，而 InnoDB 做 2 点优化：

将 LRU 链表分为 young 和 old 两个区域，加入缓冲池的页，优先插入 old 区域；页被访问时，才进入 young 区域，目的是为了解决预读失效的问题。
当「页被访问」且「 old 区域停留时间超过 innodb_old_blocks_time 阈值（默认为 1 秒）」时，才会将页插入到 young 区域，否则还是插入到 old 区域，目的是为了解决批量数据访问，大量热数据淘汰的问题。

可以通过调整 innodb_old_blocks_pct 参数，设置 young 区域和 old 区域比例。

在开启了慢 SQL 监控后，如果你发现「偶尔」会出现一些用时稍长的 SQL，这可因为脏页在刷新到磁盘时导致数据库性能抖动。如果在很短的时间出现这种现象，就需要调大 Buffer Pool 空间或 redo log 日志的大小。下面几种情况会触发脏页的刷新：

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

MySQL