MySQL innodb笔记（持续更新）

2024-06-19 10:29:32   9  举报





 使用

AI智能生成

有问题可以在评论区讨论 MySQL Innodb笔记是一份详细记录InnoDB存储引擎技术的文档。InnoDB是MySQL的一个关键组件，以其高性能、事务安全、外键支持等特性而备受青睐。这份笔记涵盖了InnoDB的核心概念，如MVCC（多版本并发控制）、索引、事务、锁等。此外，笔记还介绍了InnoDB的优化技巧，如何监控和诊断InnoDB性能问题，以及如何备份和恢复InnoDB数据库。适合希望深入了解MySQL Innodb的技术人员、数据库管理员和开发人员阅读。

MySQL

MySQL主从复制原理

Mysql索引原理

Mysql加锁原理

innodb

作者其他创作

大纲/内容

基础

MySQL的架构

分为两层

Server层

Server 层负责建立连接、分析和执行 SQL

MySQL 大多数的核心功能模块都在这实现，主要包括连接器，查询缓存、解析器、预处理器、优化器、执行器等。另外，所有的内置函数（如日期、时间、数学和加密函数等）和所有跨存储引擎的功能（如存储过程、触发器、视图等。）都在 Server 层实现。

连接器

与客户端进行 TCP 三次握手建立连接；

校验客户端的用户名和密码，如果用户名或密码不对，则会报错；

如果用户名和密码都对了，会读取该用户的权限，然后后面的权限逻辑判断都基于此时读取到的权限；

解析器

词法分析和语法分析

只负责检查语法和构建语法树，但是不会去查表或者字段存不存在。

预处理器

检查 SQL 查询语句中的表或者字段是否存在；

将 select * 中的 * 符号，扩展为表上的所有列；

优化器

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

执行器

在执行的过程中，执行器就会和存储引擎交互了，交互是以记录为单位的。

存储引擎

存储引擎层负责数据的存储和提取

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

执行一条Select语句，期间发生了什么

建立连接

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

如何查看 MySQL 服务被多少个客户端连接了？

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 的连接数有限制吗？

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

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

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

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

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

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

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

查询缓存

MySQL会对查询语句进行key-value形式缓存，key是SQL语句，value是结果。每次查询都先访问缓存

一旦表数据发生变更，这个表的查询缓存都会被清空

可以将参数 query_cache_type 设置成 DEMAND，主动关闭查询缓存

这里说的查询缓存是 server 层的，也就是 MySQL 8.0 版本移除的是 server 层的查询缓存，并不是 Innodb 存储引擎中的 buffer pool。

解析 SQL

词法分析

MySQL 会根据你输入的字符串识别出关键字出来，例如，SQL语句 select username from userinfo，在分析之后，会得到4个Token，其中有2个Keyword，分别为select和from

语法分析

根据词法分析的结果，语法解析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法，如果没问题就会构建出 SQL 语法树，这样方便后面模块获取 SQL 类型、表名、字段名、 where 条件等

执行 SQL

prepare 阶段，也就是预处理阶段；

optimize 阶段，也就是优化阶段；

execute 阶段，也就是执行阶段；

执行器从存储引擎读到记录后，接着判断记录是否符合查询条件，如果符合则发送给客户端，如果不符合则跳过该记录。

执行器查询的过程是一个 while 循环

补充

索引下推

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

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

不使用索引下推

Server 层首先调用存储引擎的接口定位到满足查询条件的第一条二级索引记录，也就是定位到 age > 20 的第一条记录；

存储引擎根据二级索引的 B+ 树快速定位到这条记录后，获取主键值，然后进行回表操作，将完整的记录返回给 Server 层；

Server 层在判断该记录的 reward 是否等于 100000，如果成立则将其发送给客户端；否则跳过该记录；

接着，继续向存储引擎索要下一条记录，存储引擎在二级索引定位到记录后，获取主键值，然后回表操作，将完整的记录返回给 Server 层；

如此往复，直到存储引擎把表中的所有记录读完。

使用索引下推

Server 层首先调用存储引擎的接口定位到满足查询条件的第一条二级索引记录，也就是定位到 age > 20 的第一条记录；

存储引擎定位到二级索引后，先不执行回表操作，而是先判断一下该索引中包含的列（reward列）的条件（reward 是否等于 100000）是否成立。如果条件不成立，则直接跳过该二级索引。如果成立，则执行回表操作，将完成记录返回给 Server 层。

Server 层在判断其他的查询条件（本次查询没有其他条件）是否成立，如果成立则将其发送给客户端；否则跳过该记录，然后向存储引擎索要下一条记录。

如此往复，直到存储引擎把表中的所有记录读完。

一行记录是怎么存储的？

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

my_test 的 database，该 database 里有一张名为 t_order 数据库表

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

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

t_order.ibd，t_order 的表数据会保存在这个文件。

表空间文件的结构是怎么样的？

行（row）

数据库表中的记录都是按行（row）进行存放的，每行记录根据不同的行格式，有不同的存储结构。

页（page）

记录是按照行来存储的，但是数据库的读取并不以「行」为单位，否则一次读取（也就是一次 I/O 操作）只能处理一行数据，效率会非常低。

InnoDB 的数据是按「页」为单位来读写的，默认每个页的大小为 16KB

页是 InnoDB 存储引擎磁盘管理的最小单元，意味着数据库每次读写都是以 16KB 为单位的，一次最少从磁盘中读取 16K 的内容到内存中，一次最少把内存中的 16K 内容刷新到磁盘中。

页的类型

数据页

undo 日志页

溢出页

...

区（extent）

B+ 树中每一层都是通过双向链表连接起来的，如果是以页为单位来分配存储空间，那么链表中相邻的两个页之间的物理位置并不是连续的，可能离得非常远，那么磁盘查询时就会有大量的随机I/O，随机 I/O 是非常慢的。

让链表中相邻的页的物理位置也相邻，这样就可以使用顺序 I/O

在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照区（extent）为单位分配。每个区的大小为 1MB，对于 16KB 的页来说，连续的 64 个页会被划为一个区，这样就使得链表中相邻的页的物理位置也相邻，就能使用顺序 I/O 了。

段（segment）

表空间是由各个段（segment）组成的，段是由多个区（extent）组成的。段一般分为数据段、索引段和回滚段等。

索引段：存放 B + 树的非叶子节点的区的集合；

数据段：存放 B + 树的叶子节点的区的集合；

回滚段：存放的是回滚数据的区的集合，之前讲事务隔离 (opens new window)的时候就介绍到了 MVCC 利用了回滚段实现了多版本查询数据。

InnoDB 行格式有哪些？

Redundant

Redundant 不是一种紧凑的行格式，古老的行格式，5.0之前使用

Compact

Compact 是一种紧凑的行格式

记录的额外信息

变长字段长度列表

char 是定长的，varchar 是变长的，变长字段实际存储的数据的长度（大小）不固定的。

在存储数据的时候，也要把数据占用的大小存起来，存到「变长字段长度列表」里面，读取数据的时候才能根据这个「变长字段长度列表」去读取对应长度的数据。其他 TEXT、BLOB 等变长字段也是这么实现的。

例

CREATE TABLE `t_user` (
  `id` int(11) NOT NULL,
  `name` VARCHAR(20) DEFAULT NULL,
  `phone` VARCHAR(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARACTER SET = ascii ROW_FORMAT = COMPACT;

表中记录

第一条记录

name 列的值为 a，真实数据占用的字节数是 1 字节，十六进制 0x01；

phone 列的值为 123，真实数据占用的字节数是 3 字节，十六进制 0x03；

age不是变长字段

第二条记录

第三条记录

NULL 是不会存放在行格式中记录的真实数据部分里的

为什么「变长字段长度列表」的信息要按照逆序存放？

主要是因为「记录头信息」中指向下一个记录的指针，指向的是下一条记录的「记录头信息」和「真实数据」之间的位置，这样的好处是向左读就是记录头信息，向右读就是真实数据。

「变长字段长度列表」中的信息之所以要逆序存放，是因为这样可以使得位置靠前的记录的真实数据和数据对应的字段长度信息可以同时在一个 CPU Cache Line 中，这样就可以提高 CPU Cache 的命中率。

每个数据库表的行格式都有「变长字段字节数列表」吗？

当数据表没有变长字段的时候，比如全部都是 int 类型的字段，这时候表里的行格式就不会有「变长字段长度列表」了，因为没必要，不如去掉以节省空间。

NULL 值列表

如果把这些 NULL 值都放到记录的真实数据中会比较浪费空间，所以 Compact 行格式把这些值为 NULL 的列存储到 NULL值列表中。

如果存在允许 NULL 值的列，则每个列对应一个二进制位（bit），二进制位按照列的顺序逆序排列。

二进制位的值为1时，代表该列的值为NULL。

二进制位的值为0时，代表该列的值不为NULL。

NULL 值列表必须用整数个字节的位表示（1字节8位），如果使用的二进制位个数不足整数个字节，则在字节的高位补 0。

例

第一条

第二条

第三条

每个数据库表的行格式都有「NULL 值列表」吗？

也不是必须的

当数据表的字段都定义成 NOT NULL 的时候，这时候表里的行格式就不会有 NULL 值列表了。

「NULL 值列表」是固定 1 字节空间吗？如果这样的话，一条记录有 9 个字段值都是 NULL，这时候怎么表示？

不是，9个字段就是2个字节。非Not null字段数除以8取上界

记录头信息

内容比较多，不仅限于如下三个

delete_mask

标识此条数据是否被删除，软删除而不是直接删除数据，但实际的物理删除操作可能会在稍后的时间点执行。MySQL使用称为"垃圾回收"（garbage collection）的过程来清理已删除的数据。

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 个字节。

Dynamic

Compressed

varchar(n) 中 n 最大取值为多少？

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

隐藏列

隐藏的系统列：MySQL 在每个表中都会自动添加一些系统列，用于存储一些元数据信息，例如行的版本号、行的创建时间和更新时间等。这些系统列对用户是不可见的。

隐藏的内部列：MySQL 在某些情况下会为了内部处理需要而添加一些隐藏列。例如，在使用 InnoDB 存储引擎时，会为每个表添加一个隐藏的列 _row_id，用于支持行锁定和 MVCC（多版本并发控制）。

单字段的情况

变长字段的「变长字段长度」需要用多少字节表示

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

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

字符集是 ascii 的情况下,varchar(n) 中 n 最大值 = 65535 - 2 - 1 = 65532

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

多字段的情况

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

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

MySQL 中磁盘和内存交互的基本单位是页，一个页的大小一般是 16KB，也就是 16384字节，而一个 varchar(n) 类型的列最多可以存储 65532字节，一些大对象如 TEXT、BLOB 可能存储更多的数据，这时一个页可能就存不了一条记录。这个时候就会发生行溢出，多的数据就会存到另外的「溢出页」中。

Compact 行格式在发生行溢出后的处理

Compressed 和 Dynamic 这两个行格式和 Compact 非常类似，主要的区别在于处理行溢出数据时有些区别。这两种格式采用完全的行溢出方式，记录的真实数据处不会存储该列的一部分数据，只存储 20 个字节的指针来指向溢出页。

索引

索引是什么

索引是数据的目录。

索引的分类

按「数据结构」分类

B+tree索引

Hash索引

Full-text索引

按「物理存储」分类

聚簇索引（主键索引）

二级索引（辅助索引）

按「字段特性」分类

主键索引

唯一索引

普通索引

前缀索引

按「字段个数」分类

单列索引

联合索引

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

什么样索引的数据结构是好的？

MySQL 的数据是持久化的，意味着数据（索引+记录）是保存到磁盘上的，内存的访问速度是纳秒级别的，而磁盘访问的速度是毫秒级别的，也就是说读取同样大小的数据，磁盘中读取的速度比从内存中读取的速度要慢上万倍，甚至几十万倍。

磁盘读写的最小单位是扇区，扇区的大小只有 512B 大小，操作系统一次会读写多个扇区，所以操作系统的最小读写单位是块（Block）。Linux 中的块大小为 4KB，也就是一次磁盘 I/O 操作会直接读写 8 个扇区。

由于数据库的索引是保存到磁盘上的，因此当我们通过索引查找某行数据的时候，就需要先从磁盘读取索引到内存，再通过索引从磁盘中找到某行数据，然后读入到内存，也就是说查询过程中会发生多次磁盘 I/O，而磁盘 I/O 次数越多，所消耗的时间也就越大。

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

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

要能高效地查询某一个记录，也要能高效地执行范围查找；

各种数据结构对比

从数据页的角度看B+树

Innodb是如何存储数据

不同存储引擎的存储方式不同，以下以Innodb为例

Innodb的数据是按数据页（默认16KB）为单位读写

每一次读数据需要进行一次IO操作，非常耗时，因此不能以行为单位，效率太低

数据页的结构

文件头File Header

表示页的信息

在 File Header 中有两个指针，分别指向上一个数据页和下一个数据页，连接起来的页相当于一个双向的链表

页头Page Header

表示页的状态

最小和最大记录Infimum+Supremum

两个虚拟的伪记录，分别表示页中的最小记录和最大记录

用户记录User Records

存储行记录内容

数据页中的记录按照「主键」顺序组成单向链表

将所有的记录划分成几个组，这些记录包括最小记录和最大记录，但不包括标记为“已删除”的记录；

每个记录组的最后一条记录就是组内最大的那条记录，并且最后一条记录的头信息中会存储该组一共有多少条记录，作为 n_owned 字段（上图中粉红色字段）

页目录用来存储每组最后一条记录的地址偏移量，这些地址偏移量会按照先后顺序存储起来，每组的地址偏移量也被称之为槽（slot），每个槽相当于指针指向了不同组的最后一个记录。

InnoDB 对每个分组中的记录条数都是有规定的，槽内的记录就只有几条：

第一个分组中的记录只能有 1 条记录；

最后一个分组中的记录条数范围只能在 1-8 条之间；

剩下的分组中记录条数范围只能在 4-8 条之间。

空闲空间Free Space

页中还没被使用的空间

页目录Page Directory

存储用户记录的相对位置，对记录起到索引作用

用户数据是单向链表，特点就是插入、删除非常方便，但是检索效率不高，最差的情况下需要遍历链表上的所有节点才能完成检索。因此，数据页中有一个页目录

页目录就是由多个槽组成的，槽相当于分组记录的索引。

通过槽查找记录时，可以使用二分法快速定位要查询的记录在哪个槽（哪个记录分组），定位到槽后，再遍历槽内的所有记录，找到对应的记录

文件尾File Tailer

校验页是否完整

B+树是如何进行查询的？

B+树的特点

只有叶子节点（最底层的节点）才存放了数据，非叶子节点（其他上层节）仅用来存放目录项作为索引。

非叶子节点分为不同层次，通过分层来降低每一层的搜索量；

所有节点按照索引键大小排序，构成一个双向链表，便于范围查询；

B+树查询记录

innodb里的B+树种的每一个节点都是一个数据页

聚簇索引和二级索引

聚簇索引

聚簇索引的叶子节点存放的是实际数据，所有完整的用户记录都存放在聚簇索引的叶子节点；

二级索引（非聚簇索引）

二级索引的叶子节点存放的是主键值，而不是实际数据。

回表

如果某个查询语句使用了二级索引，但是查询的数据不是主键值且不是索引字段，这时在二级索引找到主键值后，需要去聚簇索引中获得数据行，这个过程就叫作「回表」

覆盖索引

当查询的数据是主键值或者索引字段时，因为只在二级索引就能查询到，不用再去聚簇索引查，这个过程就叫作「索引覆盖」，也就是只需要查一个 B+ 树就能找到数据。

MySQL单表不要超过2000W行，靠谱吗

单表数量限制

看主键的类型

int

int类型的范围，大概40多亿

bigint

2^62-1

varchar

表存放数据的上限取决于所选择的 VARCHAR 字段的最大长度

text\blob

不能作为主键

索引结构

单表建议值

Total = 也就是说总数会等于 x 的 z-1 次方与 Y 的乘积

非叶子节点内指向其他页的数量为 x

叶子节点内能容纳的数据行数为 y

B+ 数的层数为 z

能保存数据的总数是叶子节点的数量乘以y，而叶子节点的数量是第z层的节点数是，可以类推出来第一层是1，第二层是x，第三层是

x=?

File Header (38 byte)、Page Header (56 Byte)、Infimum + Supermum（26 byte）、File Trailer（8byte）, 再加上页目录，大概 1k 左右, 那整个页的大小是 16K, 剩下 15k 用于存数据

假设是 Bigint (8 byte), 而页号也是固定的（4Byte）, 那么索引页中的一条数据也就是 12byte，所以 x=15*1024/12≈1280 行。

y=？

叶子节点和非叶子节点的结构是一样的，同理，能放数据的空间也是 15k。

叶子节点中存放的是真正的行数据，这个影响的因素就会多很多，比如，字段的类型，字段的数量。每行数据占用空间越大，页中所放的行数量就会越少。

如果按一条行数据 1k 来算，那一页就能存下 15 条，Y = 15*1024/1000 ≈15

在保持相同的层级（相似查询性能）的情况下，在行数据大小不同的情况下，其实这个最大建议值也是不同的，而且影响查询性能的还有很多其他因素，比如，数据库版本，服务器配置，sql 的编写等

MySQL 为了提高性能，会将表的索引装载到内存中，在 InnoDB buffer size 足够的情况下，其能完成全加载进内存，查询不会有问题。

索引失效

对索引使用左或者左右模糊匹配

like %xx 或者 like %xx%

索引 B+ 树是按照「索引值」有序排列存储的，只能根据前缀进行比较。

MySQL 使用 like “%x“，索引一定会失效吗？

题目1

%在左不能走索引

题目2

%在左能走索引，但是全索引扫描

这张表的字段没有「非索引」字段，所以 select * 相当于 select id,name，然后这个查询的数据都在二级索引的 B+ 树，因为二级索引的 B+ 树的叶子节点包含「索引值+主键值」，所以查二级索引的 B+ 树就能查到全部结果了，这个就是覆盖索引。

为什么选择全扫描二级索引树，而不扫描聚簇索引树呢？

因为二级索引树的记录东西很少，就只有「索引列+主键值」，而聚簇索引记录的东西会更多，比如聚簇索引中的叶子节点则记录了主键值、事务 id、用于事务和 MVCC 的回滚指针以及所有的剩余列。
因为这个查询语句的字段都在索引上，不需要回表，所以优化器认为直接扫描二级索引成本比扫描聚餐索引小

为什么这个数据表加了非索引字段，执行同样的查询语句后，怎么变成走的是全表扫描呢？

要查询的数据就不能只在二级索引树里找了，得需要回表操作才能完成查询的工作，再加上是左模糊匹配，无法利用索引树的有序性来快速定位数据，所以得在二级索引树逐一遍历，获取主键值后，再到聚簇索引树检索到对应的数据行，这样实在太累了。

对索引使用函数

因为索引保存的是索引字段的原始值，而不是经过函数计算后的值

对 length(name) 的计算结果建立一个名为 idx_name_length 的索引。
alter table t_user add key idx_name_length ((length(name)));

对索引进行表达式计算

如果把查询语句的条件改成 where id = 10 - 1，这样就不是在索引字段进行表达式计算了，于是就可以走索引查询了。

对索引隐式类型转换

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

联合索引非最左匹配

为什么联合索引不遵循最左匹配原则就会失效？

在联合索引的情况下，数据是按照索引第一列排序，第一列数据相同时才会按照第二列排序

索引下推

where a = 1 and c = 3 ，符合最左匹配吗？

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

从 MySQL 5.6 之后，有一个索引下推功能，可以在存储引擎层进行索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，再返还给 Server 层，从而减少回表次数。

WHERE 子句中的 OR

OR语句只要有条件列不是索引列，就会进行全表扫描。

什么时候需要创建索引、什么时候不需要创建索引

索引最大的好处是提高查询速度，但是索引也是有缺点的

需要占用物理空间，数量越大，占用空间越大；

创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增大；

会降低表的增删改的效率，因为每次增删改索引，B+ 树为了维护索引有序性，都需要进行动态维护。

什么时候适用索引？

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

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

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

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

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

字段没有区分度，比如性别这种字段，枚举数量不多

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

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

优化索引的方法

前缀索引优化

使用某个字段中字符串的前几个字符建立索引，在一些大字符串的字段作为索引时，使用前缀索引可以帮助我们减小索引项的大小。

局限性

order by 就无法使用前缀索引

无法把前缀索引用作覆盖索引

覆盖索引优化

覆盖索引是指 SQL 中 query 的所有字段，在索引 B+Tree 的叶子节点上都能找得到的那些索引，从二级索引中查询得到记录，而不需要通过聚簇索引查询获得，可以避免回表的操作。

主键索引最好是自增的

InnoDB 创建主键索引默认为聚簇索引，同一个叶子节点内的各个数据是按主键顺序存放的

如果我们使用非自增主键，由于每次插入主键的索引值都是随机的，因此每次插入新的数据时，就可能会插入到现有数据页中间的某个位置，这将不得不移动其它数据来满足新数据的插入，甚至需要从一个页面复制数据到另外一个页面，我们通常将这种情况称为页分裂。页分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。

插入数据7，导致页分裂

插入数据11

主键字段不要太大

主键字段的长度不要太大，因为主键字段长度越小，意味着二级索引的叶子节点越小（二级索引的叶子节点存放的数据是主键值），这样二级索引占用的空间也就越小。

索引最好设置为 NOT NULL

索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂，更加难以优化，因为可为 NULL 的列会使索引、索引统计和值比较都更复杂，比如进行索引统计时，count 会省略值为NULL 的行。

NULL 值是一个没意义的值，但是它会占用物理空间，所以会带来的存储空间的问题，因为 InnoDB 存储记录的时候，如果表中存在允许为 NULL 的字段，那么行格式 (opens new window)中至少会用 1 字节空间存储 NULL 值列表

防止索引失效

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

哪种count的性能最好？

结论

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

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

count() 是什么？

统计符合查询条件的记录中，函数指定的参数不为 NULL 的记录有多少个。

count(name)

name 字段不为 NULL 的记录有多少

count(1)

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

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

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

如果表里只有主键索引，没有二级索引时，那么，InnoDB 循环遍历聚簇索引

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

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

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

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

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

count(*) 执行过程跟 count(1) 执行过程基本一样的

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

没建索引就全表扫描，建了就扫索引

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

MyISAM维护了row_count，由表级锁保证一致性，所以直接读取 row_count 值就是 count 函数的执行结果。

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

而当带上 where 条件语句之后，MyISAM 跟 InnoDB 就没有区别了，它们都需要扫描表来进行记录个数的统计。

如何优化count(*)

近似值

可以使用 show table status 或者 explain 命令来表进行估算。

执行 explain 命令效率是很高的，因为它并不会真正的去查询

额外表保存计数值

实时性要求较高的场景

将更新cnt的sql加入到对应变更行数的事务中。

坏处也比较明显，多个线程对同一个cnt进行写操作，会触发悲观锁，多个线程之间需要互相等待。对于高频写的场景，性能会有折损。

好处是事务内的cnt行数依然符合隔离级别，事务回滚的时候，cnt的值也会跟着回滚。

实时性没那么高的场景

比如可以一天一次，那你可以通过全表扫描后做计算。

事务

事务有哪些特性？

原子性（Atomicity）

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

一致性（Consistency）

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

比如转账，不会出现用户 A 扣除了 200 元，但用户 B 未增加的情况

隔离性（Isolation）

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

持久性（Durability）

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

并行事务会引发什么问题？

脏读

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

如果在上面这种情况事务 A 发生了回滚，那么事务 B 刚才得到的数据就是过期的数据，这种现象就被称为脏读。

不可重复读

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

在这过程中如果事务 B 更新了这条数据，并提交了事务，那么当事务 A 再次读取该数据时，就会发现前后两次读到的数据是不一致的，这种现象就被称为不可重复读。

幻读

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

发现和前一次读到的记录数量不一样了，就感觉发生了幻觉一样，这种现象就被称为幻读。

事务的隔离级别有哪些？

读未提交（read uncommitted）

指一个事务还没提交时，它做的变更就能被其他事务看到；

读提交（read committed）

指一个事务提交之后，它做的变更才能被其他事务看到；

可重复读（repeatable read）

指一个事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，MySQL InnoDB 引擎的默认隔离级别；

串行化（serializable ）

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

MySQL 虽然支持 4 种隔离级别，但是与SQL 标准中规定的各级隔离级别允许发生的现象却有些出入。MySQL 在「可重复读」隔离级别下，可以很大程度上避免幻读现象的发生（注意是很大程度避免，并不是彻底避免）。

MySQL可重复读上避免幻读的解决方案有两种

针对快照读（普通 select 语句）

通过 MVCC 方式解决了幻读

针对当前读（select ... for update 等语句）

通过 next-key lock（记录锁+间隙锁）方式解决了幻读

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

读未提交

隔离级别的事务来说，因为可以读到未提交事务修改的数据，所以直接读取最新的数据就好了；

串行化

隔离级别的事务来说，通过加读写锁的方式来避免并行访问；

读提交

可重复读

Read View 在 MVCC 里如何工作的？

几个关键要素

Read View

creator_trx_id

创建该 Read View 的事务的事务 id。

m_ids

指的是在创建 Read View 时，当前数据库中「活跃事务」的事务 id 列表，注意是一个列表，“活跃事务”指的就是，启动了但还没提交的事务。

min_trx_id

指的是在创建 Read View 时，当前数据库中「活跃事务」中事务 id 最小的事务，也就是 m_ids 的最小值。

max_trx_id

这个并不是 m_ids 的最大值，而是创建 Read View 时当前数据库中应该给下一个事务的 id 值，也就是全局事务中最大的事务 id 值 + 1；

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

trx_id

把该事务的事务 id 记录在 trx_id 隐藏列里

roll_pointer

每次对某条聚簇索引记录进行改动时，都会把旧版本的记录写入到 undo 日志中，然后这个隐藏列是个指针，指向每一个旧版本记录，形成版本链

一个事务去访问记录的时候，这几种情况：

自己的更新记录总是可见

如果记录的 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（多版本并发控制）。

可重复读是如何工作的？

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

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

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

然后事务 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 万的这条记录。

读提交是如何工作的？

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

为什么事务 B 第二次读数据时，读不到事务 A （还未提交事务）修改的数据？

这条记录的 trx_id 是 51，在事务 B 的 Read View 的 min_trx_id 和 max_trx_id 之间，接下来需要判断 trx_id 值是否在 m_ids 范围内，判断的结果是在的，那么说明这条记录是被还未提交的事务修改的，这时事务 B 并不会读取这个版本的记录。

在事务 A 提交后，由于隔离级别是「读提交」，所以事务 B 在每次读数据的时候，会重新创建 Read View，此时事务 B 第三次读取数据时创建的 Read View 如下

事务 B 在找到小林这条记录时，会发现这条记录的 trx_id 是 51，比事务 B 的 Read View 中的 min_trx_id 值（52）还小，这意味着修改这条记录的事务早就在创建 Read View 前提交过了，所以该版本的记录对事务 B 是可见的。

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

什么是幻读？

快照读是如何避免幻读的？

当前读是如何避免幻读的？

针对当前读（select ... for update 等语句），是通过 next-key lock（记录锁+间隙锁）方式解决了幻读

幻读被完全解决了吗？

可重复读隔离级别下虽然很大程度上避免了幻读，但是还是没有能完全解决幻读。

第一个发生幻读现象的场景

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

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

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

第二个发生幻读现象的场景

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 这类当前读的语句

事务的启动时机

第一种：begin/start transaction 命令；

执行了 begin/start transaction 命令后，并不代表事务启动了。只有在执行这个命令后，执行了第一条 select 语句，才是事务真正启动的时机；

第二种：start transaction with consistent snapshot 命令；

执行了 start transaction with consistent snapshot 命令，就会马上启动事务。

锁

MySQL 有哪些锁？

全局锁

flush tables with read lock

整个数据库就处于只读状态了

unlock tables

全局锁应用场景是什么？

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

加全局锁又会带来什么缺点呢？

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

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

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

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

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

表级锁

表锁

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

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

元数据锁MDL

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

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

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

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

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

首先，线程 A 先启用了事务（但是一直不提交），然后执行一条 select 语句，此时就先对该表加上 MDL 读锁；

然后，线程 B 也执行了同样的 select 语句，此时并不会阻塞，因为「读读」并不冲突；

接着，线程 C 修改了表字段，此时由于线程 A 的事务并没有提交，也就是 MDL 读锁还在占用着，这时线程 C 就无法申请到 MDL 写锁，就会被阻塞

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

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

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

意向锁

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

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

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

而普通的 select 是不会加行级锁的，普通的 select 语句是利用 MVCC 实现一致性读，是无锁的。
不过，select 也是可以对记录加共享锁和独占锁的，具体方式如下：

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

AUTO-INC 锁

表里的主键通常都会设置成自增的，这是通过对主键字段声明 AUTO_INCREMENT 属性实现的。可以不指定主键的值，数据库会自动给主键赋值递增的值，这主要是通过 AUTO-INC 锁实现的。

锁不是再一个事务提交后才释放，而是再执行完插入语句后就会立即释放。在插入数据时，会加一个表级别的 AUTO-INC 锁.

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

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

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

innodb_autoinc_lock_mode

用来控制选择用 AUTO-INC 锁，还是轻量级的锁。

innodb_autoinc_lock_mode = 0

innodb_autoinc_lock_mode = 2

采用轻量级锁，申请自增主键后就释放锁，并不需要等语句执行后才释放。

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

两个 session 同时执行向表 t2 中插入数据。

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

解决问题

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

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

innodb_autoinc_lock_mode = 1

普通 insert 语句，自增锁在申请之后就马上释放；

类似 insert … select 这样的批量插入数据的语句，自增锁还是要等语句结束后才被释放；

行级锁

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

分为读锁和写锁

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

分类

Record Lock

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

Gap Lock

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

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

Next-Key Lock

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

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

插入意向锁

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

一个事务在插入一条记录的时候，需要判断插入位置是否已被其他事务加了间隙锁（next-key lock 也包含间隙锁）。
如果有的话，插入操作就会发生阻塞，直到拥有间隙锁的那个事务提交为止（释放间隙锁的时刻），在此期间会生成一个插入意向锁，表明有事务想在某个区间插入新记录，但是现在处于等待状态。

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

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

MySQL 是怎么加行级锁的？

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

加锁的对象是索引，加锁的基本单位是 next-key lock

在能使用记录锁或者间隙锁就能避免幻读现象的场景下， next-key lock 就会退化成记录锁或间隙锁。

有什么命令可以分析加了什么锁？

select * from performance_schema.data_locks\G;

例

隔离级别是「可重复读」

唯一索引加锁的流程图

唯一索引等值查询

当查询的记录是「存在」的，在索引树上定位到这一条记录后，将该记录的索引中的 next-key lock 会退化成「记录锁」。

事务 A 会为 id 为 1 的这条记录就会加上 X 型的记录锁。

select * from performance_schema.data_locks\G;

LOCK_TYPE

RECORD 表示行级锁，而不是记录锁的意思。

LOCK_MODE

X，说明是 next-key 锁；

X, REC_NOT_GAP，说明是记录锁；

为什么唯一索引等值查询并且查询记录存在的场景下，该记录的索引中的 next-key lock 会退化成记录锁？

原因就是在唯一索引等值查询并且查询记录存在的场景下，仅靠记录锁也能避免幻读的问题。

因为是唯一索引，主键冲突导致其他事务不能插入id=1的记录。也不能删除id=1的记录，因为加了记录锁。所以本事务多次读的数据集是一样的，避免幻读的问题

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

此时事务 A 在 id = 5 记录的主键索引上加的是间隙锁，锁住的范围是 (1, 5)。

为什么唯一索引等值查询并且查询记录「不存在」的场景下，在索引树找到第一条大于该查询记录的记录后，要将该记录的索引中的 next-key lock 会退化成「间隙锁」？

为什么 id = 5 记录上的主键索引的锁不可以是 next-key lock？如果是 next-key lock，就意味着其他事务无法删除 id = 5 这条记录，但是这次的案例是查询 id = 2 的记录，只要保证前后两次查询 id = 2 的结果集相同，就能避免幻读的问题了，所以即使 id =5 被删除，也不会有什么影响，那就没必须加 next-key lock。

为什么不可以针对不存在的记录加记录锁？锁是加在索引上的，而这个场景下查询的记录是不存在的，自然就没办法锁住这条不存在的记录。

本例「唯一索引」是用「主键索引」作为案例说明的，加锁只加在主键索引项上。二级索引的「唯一索引」，加锁也是只加在二级索引项上是不对的。如果是用二级索引（不管是不是非唯一索引，还是唯一索引）进行锁定读查询的时候，除了会对二级索引项加行级锁（如果是唯一索引的二级索引，加锁规则和主键索引的案例相同），而且还会对查询到的记录的主键索引项上加「记录锁」。

唯一索引范围查询

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

针对「大于等于」的范围查询，因为存在等值查询的条件，那么如果等值查询的记录是存在于表中，那么该记录的索引中的 next-key 锁会退化成记录锁。

实验一：针对「大于」的范围查询的情况。

事务 A 在主键索引上加了两个 X 型的next-key 锁

实验二：针对「大于等于」的范围查询的情况。

针对「大于等于」条件的唯一索引范围查询的情况下，如果条件值的记录存在于表中，那么由于查询该条件值的记录是包含一个等值查询的操作，所以该记录的索引中的 next-key 锁会退化成记录锁。

针对「小于或者小于等于」的范围查询，要看条件值的记录是否存在于表中：

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

实验一：针对「小于」的范围查询时，查询条件值的记录「不存在」表中的情况。

当条件值的记录在表中，如果是「小于」条件的范围查询，扫描到终止范围查询的记录时，该记录的索引的 next-key 锁会退化成间隙锁

实验二：针对「小于等于」的范围查询时，查询条件值的记录「存在」表中的情况。

实验三：再来看针对「小于」的范围查询时，查询条件值的记录「存在」表中的情况。

退化成间隙锁

非唯一索引加锁的流程图

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

当查询的记录「不存在」时，扫描到第一条不符合条件的二级索引记录，该二级索引的 next-key 锁会退化成间隙锁。因为不存在满足查询条件的记录，所以不会对主键索引加锁。

实验一：针对非唯一索引等值查询时，查询的值不存在的情况。

该二级索引的 next-key 锁会退化成间隙锁，范围是 (22, 39)。

那 LOCK_DATA：39，20 是什么意思？

LOCK_DATA 第一个数值，也就是 39，它代表的是 age 值。从前面我们也知道了，LOCK_DATA 第一个数值是 next-key 锁和间隙锁锁住的范围的右边界值。

LOCK_DATA 第二个数值，也就是 20，它代表的是 id 值。

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

实验二：针对非唯一索引等值查询时，查询的值存在的情况。

没有加索引的查询

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

不是加表锁，而是行锁

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

name没加索引

如何避免这种事故的发生？

我们可以将 MySQL 里的 sql_safe_updates 参数设置为 1，开启安全更新模式。

update 语句必须满足如下条件之一才能执行成功：

使用 where，并且 where 条件中必须有索引列；

使用 limit；

同时使用 where 和 limit，此时 where 条件中可以没有索引列；

delete 语句必须满足以下条件能执行成功：

同时使用 where 和 limit，此时 where 条件中可以没有索引列；

MySQL 死锁了，怎么办？

死锁的发生

本次案例使用存储引擎 Innodb，隔离级别为可重复读（RR）。

为什么会发生死锁？

Time 1 阶段加锁分析

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

Time 2 阶段加锁分析

行锁是 X 类型的间隙锁，间隙锁的范围是(20, 30)

事务 A 和事务 B 的间隙锁范围都是一样的，为什么不会冲突？

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

Time 3 阶段加锁分析

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

Time 4 阶段加锁分析

如何避免死锁？

死锁的四个必要条件：互斥、占有且等待、不可强占用、循环等待。只要系统发生死锁，这些条件必然成立，但是只要破坏任意一个条件就死锁就不会成立。

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

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

开启主动死锁检测

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

日志

为什么需要 undo log？

undo log（回滚日志），实现事务回滚，保障事务的原子性

每当 InnoDB 引擎对一条记录进行操作（修改、删除、新增）时，要把回滚时需要的信息都记录到 undo log 里

在插入一条记录时，要把这条记录的主键值记下来，这样之后回滚时只需要把这个主键值对应的记录删掉

在删除一条记录时，要把这条记录中的内容都记下来，这样之后回滚时再把由这些内容组成的记录插入到表中

在更新一条记录时，要把被更新的列的旧值记下来，这样之后回滚时再把这些列更新为旧值

undo log 还有一个作用，通过 ReadView + undo log 实现 MVCC（多版本并发控制）

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

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

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

为什么需要 Buffer Pool？

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

当读取数据时，如果数据存在于 Buffer Pool 中，客户端就会直接读取 Buffer Pool 中的数据，否则再去磁盘中读取。

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

Buffer Pool 缓存什么？

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

为了更好的管理这些在 Buffer Pool 中的缓存页，InnoDB 为每一个缓存页都创建了一个控制块，控制块信息包括「缓存页的表空间、页号、缓存页地址、链表节点」等等。
控制块也是占有内存空间的，它是放在 Buffer Pool 的最前面，接着才是缓存页

为什么需要 redo log ？

什么是 redo log？

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

为什么要redo log？

实现事务的持久性，让 MySQL 有 crash-safe 的能力

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

为了防止断电导致数据丢失的问题，当有一条记录需要更新的时候，InnoDB 引擎就会先更新内存（同时标记为脏页），然后将本次对这个页的修改以 redo log 的形式记录下来，这个时候更新就算完成了。在事务提交时，只要先将 redo log 持久化到磁盘即可，可以不需要等到将缓存在 Buffer Pool 里的脏页数据持久化到磁盘。

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

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

将写操作从「随机写」变成了「顺序写」

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

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

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

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

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

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

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

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

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

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

将 redo log 留在 redo log buffer 中，InnoDB 的后台线程每隔 1 秒，会把缓存在 redo log buffer 中的 redo log ，通过调用 write() 写到操作系统的 Page Cache，然后调用 fsync() 持久化到磁盘。

MySQL 进程的崩溃会导致上一秒钟所有事务数据的丢失

将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘

写到 redo log 文件，注意写入到「 redo log 文件」并不意味着写入到了磁盘，Page Cache 是专门用来缓存文件数据的，

MySQL 进程的崩溃并不会丢失数据，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失。

这三个参数的数据安全性和写入性能的比较如下：

数据安全性：参数 1 > 参数 2 > 参数 0

写入性能：参数 0 > 参数 2> 参数 1

redo log 文件写满了怎么办？

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

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

有两个Redo Log文件的主要原因是为了实现高可用性和持久性。当一个Redo Log文件写满后，MySQL会将新的事务日志写入另一个Redo Log文件，同时将已写满的Redo Log文件进行刷新和持久化。这样可以确保即使在写满一个Redo Log文件之后，数据库仍然可以继续记录事务操作，而不会中断。

write pos ～ checkpoint 之间的部分（图中的红色部分），用来记录新的更新操作；

check point ～ write pos 之间的部分（图中蓝色部分）：待落盘的脏数据页记录；

如果 write pos 追上了 checkpoint，就意味着 redo log 文件满了，这时 MySQL 不能再执行新的更新操作，也就是说 MySQL 会被阻塞（因此所以针对并发量大的系统，适当设置 redo log 的文件大小非常重要）

为什么需要 binlog ？

undo log 和 redo log 这两个日志都是 Innodb 存储引擎生成的，binlog是Server层生成的。

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

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

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

redo log 和 binlog 有什么区别？

1、适用对象不同：

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

redo log 是 Innodb 存储引擎实现的日志；

2、文件格式不同：

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

STATEMENT

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

ROW

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

MIXED

包含了 STATEMENT 和 ROW 模式，它会根据不同的情况自动使用 ROW 模式和 STATEMENT 模式；

如果一条语句可以在STATEMENT模式下正确执行，并且不会引起数据不一致的问题，那么该语句会以STATEMENT格式记录在binlog中。反之，ROW

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

3、写入方式不同：

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

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

4、用途不同：

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

redo log 用于掉电等故障恢复。

主从复制是怎么实现？

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

写入 Binlog

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

同步 Binlog

把 binlog 复制到所有从库上，每个从库把 binlog 写到暂存日志中。

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

回放 Binlog

回放 binlog，并更新存储引擎中的数据。

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

从库是不是越多越好？

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

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

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

同步复制

MySQL 主库提交事务的线程要等待所有从库的复制成功响应，才返回客户端结果。这种方式在实际项目中，基本上没法用，原因有两个：一是性能很差，因为要复制到所有节点才返回响应；二是可用性也很差，主库和所有从库任何一个数据库出问题，都会影响业务。

异步复制（默认模型）

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

半同步复制

MySQL 5.7 版本之后增加的一种复制方式，介于两者之间，事务线程不用等待所有的从库复制成功响应，只要一部分复制成功响应回来就行，比如一主二从的集群，只要数据成功复制到任意一个从库上，主库的事务线程就可以返回给客户端。这种半同步复制的方式，兼顾了异步复制和同步复制的优点，即使出现主库宕机，至少还有一个从库有最新的数据，不存在数据丢失的风险。

binlog 什么时候刷盘？

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

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

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

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

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

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

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

sync_binlog = 0 的时候，表示每次提交事务都只 write，不 fsync，后续交由操作系统决定何时将数据持久化到磁盘；

sync_binlog = 1 的时候，表示每次提交事务都会 write，然后马上执行 fsync；

sync_binlog =N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

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

两阶段提交

为什么需要两阶段提交？

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

如果在将 redo log 刷入到磁盘之后， MySQL 突然宕机了，而 binlog 还没有来得及写入。

导致主库是新值，从库是旧值

如果在将 binlog 刷入到磁盘之后， MySQL 突然宕机了，而 redo log 还没有来得及写入。

导致从库是新值，主库是旧值

MySQL 为了避免出现两份日志之间的逻辑不一致的问题，使用了「两阶段提交」来解决

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

分两阶段来完成 XA 事务的提交

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 也没有关系，一样会被认为事务已经执行成功

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

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

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

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

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

阶段提交是以 binlog 写成功为事务提交成功的标识

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

binlog 已经写入了，之后就会被从库（或者用这个 binlog 恢复出来的库）使用。
所以，在主库上也要提交这个事务。采用这个策略，主库和备库的数据就保证了一致性

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

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

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

两阶段提交有什么问题？

磁盘 I/O 次数高

对于“双1”配置，每个事务提交都会进行两次 fsync（刷盘），一次是 redo log 刷盘，另一次是 binlog 刷盘。

锁竞争激烈

两阶段提交虽然能够保证「单事务」两个日志的内容一致，但在「多事务」的情况下，却不能保证两者的提交顺序一致，因此，在两阶段提交的流程基础上，还需要加一个锁来保证提交的原子性，从而保证多事务的情况下，两个日志的提交顺序一致。

组提交

MySQL 引入了 binlog 组提交（group commit）机制，当有多个事务提交的时候，会将多个 binlog 刷盘操作合并成一个，从而减少磁盘 I/O 的次数

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

flush 阶段

多个事务按进入的顺序将 binlog 从 cache 写入文件（不刷盘）

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

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

下述例子是5.7之后的版本

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

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

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

sync 阶段

对 binlog 文件做 fsync 操作（多个事务的 binlog 合并一次刷盘）

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

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

commit 阶段

各个事务按顺序做 InnoDB commit 操作

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

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

锁粒度减小了，这样就使得多个阶段可以并发执行，从而提升效率。

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

内存

为什么要有 Buffer Pool？

Buffer Pool 有多大？

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

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

Buffer Pool 缓存什么？

如何管理 Buffer Pool？

如何管理空闲页？

Buffer Pool 是一片连续的内存空间，当 MySQL 运行一段时间后，这片连续的内存空间中的缓存页既有空闲的，也有被使用的。采用遍历的方式找空闲页效率太低。

Free 链表（空闲链表）

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

如何管理脏页？

脏页

Flush 链表

Free Page（空闲页）

表示此页未被使用，位于 Free 链表；

Clean Page（干净页）

表示此页已被使用，但是页面未发生修改，位于LRU 链表。

Dirty Page（脏页）

表示此页「已被使用」且「已经被修改」，其数据和磁盘上的数据已经不一致。当脏页上的数据写入磁盘后，内存数据和磁盘数据一致，那么该页就变成了干净页。脏页同时存在于 LRU 链表和 Flush 链表。

如何提高缓存命中率？

LRU（Least recently used）算法

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

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

预读失效

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

如果使用简单的 LRU 算法，就会把预读页放到 LRU 链表头部，而当 Buffer Pool空间不够的时候，还需要把末尾的页淘汰掉，末尾的页可能是读取比较频繁的页。

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

要避免预读失效带来影响，最好就是让预读的页停留在 Buffer Pool 里的时间要尽可能的短，让真正被访问的页才移动到 LRU 链表的头部，从而保证真正被读取的热数据留在 Buffer Pool 里的时间尽可能长。

LRU 划分了 2 个区域：old 区域和 young 区域

划分这两个区域后，预读的页就只需要加入到 old 区域的头部，当页被真正访问的时候，才将页插入 young 区域的头部。

innodb_old_blocks_pct 参数来设置，默认是 37，代表整个 LRU 链表中 young 区域与 old 区域比例是 63:37。

Buffer Pool 污染

当某一个 SQL 语句扫描了大量的数据时，在 Buffer Pool 空间比较有限的情况下，可能会将 Buffer Pool 里的所有页都替换出去，导致大量热数据被淘汰了，等这些热数据又被再次访问的时候，由于缓存未命中，就会产生大量的磁盘 IO，MySQL 性能就会急剧下降，这个过程被称为 Buffer Pool 污染。

例

子在批量访问这些数据的时候，会被逐一插入到 young 区域头部。

原本在 young 区域的热点数据 6 和 7 号页都被淘汰了，这就是 Buffer Pool 污染的问题。

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

提高进入到 young 区域的门槛，就能有效地保证 young 区域里的热点数据不会被替换掉。

进入到 young 区域条件增加了一个停留在 old 区域的时间判断。

如果后续的访问时间与第一次访问的时间在某个时间间隔内，那么该缓存页就不会被从 old 区域移动到 young 区域的头部；

如果后续的访问时间与第一次访问的时间不在某个时间间隔内，那么该缓存页移动到 young 区域的头部；

这个间隔时间是由 innodb_old_blocks_time 控制的，默认是 1000 ms。

只有同时满足「被访问」与「在 old 区域停留时间超过 1 秒」两个条件，才会被插入到 young 区域头部

脏页什么时候会被刷入磁盘？

innoDB 的更新操作采用的是 Write Ahead Log 策略，即先写日志，再写入磁盘，通过 redo log 日志让 MySQL 拥有了崩溃恢复能力。