MySQL

MySQL :: MySQL 8.4 Reference Manual

select 语句的执行过程

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

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

而存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。

连接器

作用：

与客户端进行TCP三次握手建立连接
校验用户名、密码
校验权限
维持和管理连接

MySQL的最大线程数可以通过参数max-connections来控制，如果到来的客户端连接超出该值时，新到来的连接都会被拒绝，关于最大连接数的一些命令主要有两条：

show variables like '%max_connections%';：查询目前DB的最大连接数。默认151
set GLOBAL max_connections = 200;：修改数据库的最大连接数为指定值。

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

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

MySQL 的连接也跟 HTTP 一样，有短连接和长连接的概念。长连接的好处就是可以减少建立连接和断开连接的过程，但是，使用长连接后可能会占用内存增多。有两种解决方式：

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

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

查询缓存

MySQL 服务收到 SQL 语句后，就会解析出 SQL 语句的第一个字段，看看是什么类型的语句。

如果 SQL 是查询语句（select 语句），MySQL 就会先去查询缓存（ Query Cache ）里查找缓存数据，看看之前有没有执行过这一条命令，这个查询缓存是以 key-value 形式保存在内存中的，key 为 SQL 查询语句，value 为 SQL 语句查询的结果。

MySQL 8.0 版本已经将查询缓存删掉。

分析器

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

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

检查 SQL 查询语句中的表或者字段是否存在也是在分析器中做的。

优化器

优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。

优化器主要负责将 SQL 查询语句的执行方案确定下来

执行器

执行器和存储引擎交互，交互是以记录为单位的。

update 语句的执行过程

查询语句的那一套流程，更新语句也是同样会走一遍，与查询流程不一样的是，更新流程还涉及两个重要的日志模块：redo log（重做日志）和 binlog（归档日志）：

客户端先通过连接器建立连接，连接器自会判断用户身份、权限校验；
因为这是一条 update 语句，所以不需要经过查询缓存，但是表上有更新语句，会把整个表的查询缓存清空。
解析器会通过词法分析识别出关键字 update，表名等等，构建出语法树，接着还会做语法分析，判断输入的语句是否符合 MySQL 语法；
优化器确定执行计划；
执行器负责具体执行。

执行器和 InnoDB 引擎内部执行流程：

执行器和 InnoDB 引擎在执行 update 语句时的内部流程：update T set c=c+1 where ID=2;

执行器先找引擎取ID=2这一行。如果ID=2这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
执行器拿到引擎给的行数据，把这个值加上1，比如原来是N，现在就是N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
执行器生成这个操作的binlog，并把binlog写入磁盘。
执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交（commit）状态，更新完成。

图中浅色框表示是在InnoDB内部执行的，深色框表示是在执行器中执行的。

存储结构

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

mysql> SHOW VARIABLES LIKE 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)

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

db.opt，用来存储当前数据库的默认字符集和字符校验规则。
表名.frm ，数据库表的表结构会保存在这个文件。在 MySQL 中建立一张表都会生成一个.frm 文件，该文件是用来保存每个表的元数据信息的，主要包含表结构定义。
表名.ibd，数据库表的表数据会保存在这个文件。 MySQL 中每一张表的数据都存放在一个独立的 .ibd 文件。

表空间

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

记录是按照行来存储的

InnoDB 的数据是按「页」为单位来读写的，默认每个页的大小为 16KB。一次最少从磁盘中读取 16K 的内容到内存中，一次最少把内存中的 16K 内容刷新到磁盘中。

区（extent）

我们知道 InnoDB 存储引擎是用 B+ 树来组织数据的。

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

解决这个问题也很简单，就是让链表中相邻的页的物理位置也相邻，这样就可以使用顺序 I/O 了，那么在范围查询（扫描叶子节点）的时候性能就会很高。

那具体怎么解决呢？

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

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

InnoDB 行格式

InnoDB 提供了 4 种行格式，分别是 Redundant、Compact、Dynamic和 Compressed 行格式。

Compact 行格式：

记录的额外信息

变长字段长度列表

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

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

第一条记录：

name 列的值为 a，真实数据占用的字节数是 1 字节，十六进制 0x01；
phone 列的值为 123，真实数据占用的字节数是 3 字节，十六进制 0x03；
age 列和 id 列不是变长字段，所以这里不用管。

这些变长字段的真实数据占用的字节数会按照列的顺序逆序存放，所以「变长字段长度列表」里的内容是「 03 01」

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

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

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

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

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

NULL值列表

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

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

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

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

第三条记录 phone 列和 age 列是 NULL 值，所以，对于第三条数据，NULL 值列表用十六进制表示是 0x06。

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

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

所以在设计数据库表的时候，通常都是建议将字段设置为 NOT NULL，这样可以至少节省 1 字节的空间

记录头信息

记录头信息中包含的内容很多：

delete_mask ：标识此条数据是否被删除。从这里可以知道，我们执行 detele 删除记录的时候，并不会真正的删除记录，只是将这个记录的 delete_mask 标记为 1。
next_record：下一条记录的位置。从这里可以知道，记录与记录之间是通过链表组织的。在前面我也提到了，指向的是下一条记录的「记录头信息」和「真实数据」之间的位置，这样的好处是向左读就是记录头信息，向右读就是真实数据，比较方便。
record_type：表示当前记录的类型，0表示普通记录，1表示B+树非叶子节点记录，2表示最小记录，3表示最大记录

记录的真实数据

记录真实数据部分除了我们定义的字段，还有三个隐藏字段，分别为：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 个字节。

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

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

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

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

真实数据
真实数据占用的字节数
NULL 标识，如果不允许为NULL，这部分不需要

所以，我们在算 varchar(n) 中 n 最大值时，需要减去「变长字段长度列表」和「NULL 值列表」所占用的字节数的。

CREATE TABLE test ( 
`name` VARCHAR(65532)  NULL
) ENGINE = InnoDB DEFAULT CHARACTER SET = ascii ROW_FORMAT = COMPACT;

上述例子，在数据库表只有一个 varchar(n) 字段且字符集是 ascii 的情况下，varchar(n) 中 n 最大值 = 65535 - 2 - 1 = 65532。

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

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

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

索引

在 MySQL 中，索引是在存储引擎层实现的。在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。又因为 InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。

每一个索引在 InnoDB 里面对应一棵 B+ 树。

按「数据结构」分类：B+tree索引、Hash索引、Full-text索引。

按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）。

按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。

按「字段个数」分类：单列索引、联合索引。

B+Tree索引

1、B+Tree vs 二叉树

对于有 N 个叶子节点的 B+Tree，其搜索复杂度为O(logdN)，其中 d 表示节点允许的最大子节点个数为 d 个。

在实际的应用当中， d 值是大于100的，这样就保证了，即使数据达到千万级别时，B+Tree 的高度依然维持在 3~4 层左右，也就是说一次数据查询操作只需要做 3~4 次的磁盘 I/O 操作就能查询到目标数据。

而二叉树的每个父节点的儿子节点个数只能是 2 个，意味着其搜索复杂度为 O(logN)，这已经比 B+Tree 高出不少，因此二叉树检索到目标数据所经历的磁盘 I/O 次数要更多。如果索引的字段值是按顺序增长的，二叉树会转变为链表结构，检索的过程和全表扫描无异。

2、B+Tree vs 红黑树

红黑树虽然对比二叉树来说，树高有所降低，但数据量一大时，依旧会有很大的高度。每个节点中只存储一个数据，节点之间还是不连续的，依旧无法利用局部性原理。

3、B+Tree vs B Tree

B+Tree 只在叶子节点存储数据，而 B 树的非叶子节点也要存储数据，所以 B+Tree 的单个节点的数据量更小，在相同的磁盘 I/O 次数下，就能查询更多的节点。

另外，B+Tree 叶子节点采用的是双链表连接，适合 MySQL 中常见的范围查询，而 B 树无法做到这一点。

B+Tree插入和删除效率更高，不会涉及复杂的树的变形

4、B+Tree vs Hash

Hash 在做等值查询的时候效率贼快，搜索复杂度为 O(1)。

但是 Hash 表不适合做范围查询，它更适合做等值的查询，这也是 B+Tree 索引要比 Hash 表索引有着更广泛的适用场景的原因。

聚集索引和二级索引

在查询时使用了二级索引，如果查询的数据能在二级索引里查询的到，那么就不需要回表，这个过程就是覆盖索引。

如果查询的数据不在二级索引里，就会先检索二级索引，找到对应的叶子节点，获取到主键值后，然后再检索主键索引查询到数据，这个过程就是回表。

唯一索引和普通索引

普通索引和唯一索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。所以，建议尽量选择普通索引。

查询过程：select id from T where k=5;

对于普通索引来说，查找到满足条件的第一个记录 k=5 后，需要查找下一个记录，直到碰到第一个不满足 k=5 条件的记录。
对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

那么，这个不同带来的性能差距会有多少呢？答案是，微乎其微。因为引擎是按页读写的，所以说，当找到 k=5 的记录的时候，它所在的数据页就都在内存里了。不在同一页的概率很低，可以忽略不计。那么，对于普通索引来说，要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算。

更新过程：insert into T values(4, 400);

第一种情况是，这个记录要更新的目标页在内存中。这时，InnoDB 的处理流程如下：

对于唯一索引来说，找到 3 和 5 之间的位置，判断到没有冲突，插入这个值，语句执行结束；
对于普通索引来说，找到 3 和 5 之间的位置，插入这个值，语句执行结束。

这样看来，普通索引和唯一索引对更新语句性能影响的差别，只是一个判断，只会耗费微小的 CPU 时间。

第二种情况是，这个记录要更新的目标页不在内存中。这时，InnoDB 的处理流程如下：

对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；
对于普通索引来说，则是将更新记录在 change buffer，语句执行就结束了。

将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

前缀索引

前缀索引的特点是短小精悍，我们可以利用一个字段的前N个字符创建索引，相较于使用一个完整字段创建索引，前缀索引能够更加节省存储空间。

但是无法通过前缀索引来完成ORDER BY、GROUP BY等分组排序工作，同时也用不上覆盖索引对查询性能的优化了。

当要给字符串创建前缀索引时，有什么方法能够确定我应该使用多长的前缀呢？我们在建立索引时关注的是区分度，区分度越高越好。

遇到前缀的区分度不够好的情况时，我们要怎么办呢？1. 使用倒序存储，再创建前缀索引 2. 使用hash字段索引

联合索引

使用联合索引时，存在最左匹配原则，也就是按照最左优先的方式进行索引的匹配。在使用联合索引进行查询的时候，如果不遵循「最左匹配原则」，联合索引会失效。

联合索引有一些特殊情况，并不是查询过程使用了联合索引查询，就代表联合索引中的所有字段都用到了联合索引进行索引查询。联合索引的最左匹配原则会一直向右匹配直到遇到「范围查询」就会停止匹配。也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。

select * from t_table where a > 1 and b = 2，联合索引（a, b）哪一个字段用到了联合索引的 B+Tree？

由于联合索引（二级索引）是先按照 a 字段的值排序的，所以符合 a > 1 条件的二级索引记录肯定是相邻，于是在进行索引扫描的时候，可以定位到符合 a > 1 条件的第一条记录，然后沿着记录所在的链表向后扫描，直到某条记录不符合 a > 1 条件位置。所以 a 字段可以在联合索引的 B+Tree 中进行索引查询。

但是在符合 a > 1 条件的二级索引记录的范围里，b 字段的值是无序的。所以 b 字段无法利用联合索引进行索引查询。

这条查询语句只有 a 字段用到了联合索引进行索引查询，而 b 字段并没有使用到联合索引。

select * from t_table where a >= 1 and b = 2，联合索引（a, b）哪一个字段用到了联合索引的 B+Tree？

由于联合索引（二级索引）是先按照 a 字段的值排序的，所以符合 >= 1 条件的二级索引记录肯定是相邻，于是在进行索引扫描的时候，可以定位到符合 >= 1 条件的第一条记录，然后沿着记录所在的链表向后扫描，直到某条记录不符合 a>= 1 条件位置。所以 a 字段可以在联合索引的 B+Tree 中进行索引查询。

虽然在符合 a>= 1 条件的二级索引记录的范围里，b 字段的值是「无序」的，但是对于符合 a = 1 的二级索引记录的范围里，b 字段的值是「有序」的.

于是，在确定需要扫描的二级索引的范围时，当二级索引记录的 a 字段值为 1 时，可以通过 b = 2 条件减少需要扫描的二级索引记录范围。也就是说，从符合 a = 1 and b = 2 条件的第一条记录开始扫描，而不需要从第一个 a 字段值为 1 的记录开始扫描。

所以，这条查询语句 a 和 b 字段都用到了联合索引进行索引查询。

SELECT * FROM t_table WHERE a BETWEEN 2 AND 8 AND b = 2，联合索引（a, b）哪一个字段用到了联合索引的 B+Tree？

在 MySQL 中，BETWEEN 包含了 value1 和 value2 边界值，类似于 >= and =<，所以这条查询语句 a 和 b 字段都用到了联合索引进行索引查询。

SELECT * FROM t_user WHERE name like 'j%' and age = 22，联合索引（name, age）哪一个字段用到了联合索引的 B+Tree？

由于联合索引（二级索引）是先按照 name 字段的值排序的，所以前缀为 ‘j’ 的 name 字段的二级索引记录都是相邻的，于是在进行索引扫描的时候，可以定位到符合前缀为 ‘j’ 的 name 字段的第一条记录，然后沿着记录所在的链表向后扫描，直到某条记录的 name 前缀不为 ‘j’ 为止。

虽然在符合前缀为 ‘j’ 的 name 字段的二级索引记录的范围里，age 字段的值是「无序」的，但是对于符合 name = j 的二级索引记录的范围里，age字段的值是「有序」的

所以，这条查询语句 a 和 b 字段都用到了联合索引进行索引查询。

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

联合索引进行排序：

这里出一个题目，针对针对下面这条 SQL，你怎么通过索引来提高查询效率呢？

select * from order where status = 1 order by create_time asc

有的同学会认为，单独给 status 建立一个索引就可以了。

但是更好的方式给 status 和 create_time 列建立一个联合索引，因为这样可以避免 MySQL 数据库发生文件排序。

因为在查询时，如果只用到 status 的索引，但是这条语句还要对 create_time 排序，这时就要用文件排序 filesort，也就是在 SQL 执行计划中，Extra 列会出现 Using filesort。

所以，要利用索引的有序性，在 status 和 create_time 列建立联合索引，这样根据 status 筛选后的数据就是按照 create_time 排好序的，避免在文件排序，提高了查询效率。

索引设计原则

什么时候适合索引？

针对数据量较大，且查询比较繁琐的表建立索引；
针对于常作为查询条件（where），排序（order by），分组(group by)操作的字段，建立索引；
尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高使用索引的效率越高；
如果是字符串类型的字段，字段的长度过长，可以针对字段的特点，建立前缀索引；
建立联合索引，应当遵循最左前缀原则，将多个字段之间按优先级顺序组合；
尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率；
要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率；
如果索引列不能存储null值，在创建表时使用not null约束它。当优化器知道每列是否包含null值时，它可以更好地确定哪个索引最有效地用于查询。
表的主外键或连表字段，必须建立索引，因为能很大程度提升连表查询的性能。

什么时候不适合索引？

大量重复值的字段
当表的数据较少，不应当建立索引，因为数据量不大时，维护索引反而开销更大。
经常增删改的字段，因为索引字段频繁修改，由于要维护 B+Tree的有序性，那么就需要频繁的重建索引，这个过程是会影响数据库性能。
索引不能参与计算，因此经常带函数查询的字段，并不适合建立索引。
一张表中的索引数量并不是越多越好，一般控制在3，最多不能超过5。
索引的字段值无序时，不推荐建立索引，因为会造成页分裂，尤其是主键索引。

索引失效

左或左右模糊查询 like %x 或者 like %x%。因为索引 B+ 树是按照「索引值」有序排列存储的，只能根据前缀进行比较。
查询中对索引做了计算、函数、类型转换操作。因为索引保存的是索引字段的原始值，而不是经过函数计算后的值，自然就没办法走索引了。
联合索引要遵循最左匹配原则
联合索引中，出现范围查询（>,<），范围查询右侧的列索引失效。
在 WHERE 子句中，如果 OR 前后有条件列不是索引列，那么索引会失效。因为 OR 的含义就是两个只要满足一个即可，只要有条件列不是索引列，就会进行全表扫描。
隐式类型转换或隐式字符编码转换

索引隐式类型转换：

如果索引字段是字符串类型，但是在条件查询中，输入的参数是整型的话，你会在执行计划的结果发现这条语句会走全表扫描；

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

MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。验证：select “10” > 9;

索引下推

对于联合索引（a, b），在执行 select * from table where a > 1 and b = 2 语句的时候，只有 a 字段能用到索引，那在联合索引的 B+Tree 找到第一个满足条件的主键值后，还需要判断其他条件是否满足（看 b 是否等于 2），那是在联合索引里判断？还是回表通过主键索引找出数据行去判断呢？

在 MySQL 5.6 之前，只能一个个回表，到主键索引上找出数据行，返回给server层再对比 b 字段值。
而 MySQL 5.6 引入的索引下推优化（index condition pushdown)，可以在联合索引遍历过程中，对联合索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

change buffer

使用 change buffer 对更新过程的加速作用。

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

虽然名字叫作 change buffer，实际上它是可以持久化的数据。也就是说，change buffer 在内存中有拷贝，也会被写入到磁盘上。

将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。

change buffer 用的是 buffer pool 里的内存。change buffer 的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。

对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。

反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。所以，对于这种业务模式来说，change buffer 反而起到了副作用。

change buffer vs. redo log：

执行 insert into t(id,k) values(id1,k1),(id2,k2);

假设 k1 所在的数据页在内存 (InnoDB buffer pool) 中，k2 所在的数据页不在内存中

这条更新语句做了如下的操作（按照图中的数字顺序）：

Page 1 在内存中，直接更新内存；
Page 2 没有在内存中，就在内存的 change buffer 区域，记录下“我要往 Page 2 插入一行”这个信息
将上述两个动作记入 redo log 中（图中 3 和 4）。

redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。如果没有change buffer, 执行更新的“当时那一刻”，就要求从磁盘把数据页读出来（这个操作是随机读）。

事务

在 MySQL 中，事务支持是在引擎层实现的，并不是所有的引擎都支持事务。比如 MySQL 原生的 MyISAM 引擎就不支持事务，这也是 MyISAM 被 InnoDB 取代的重要原因之一。

事务的特性：

原子性：一个事务中的所有操作，要么全部完成，要么全部失败。
一致性：是指事务操作前和操作后，数据满足完整性约束，数据库保持一致性状态。
隔离性：多个事务同时使用相同的数据时，不会相互干扰，每个事务都有一个完整的数据空间，对其他并发事务是隔离的。
持久性：一个事务一旦被提交，它会保持永久性，所更改的数据都会被写入到磁盘做持久化处理。

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

持久性是通过 redo log （重做日志）来保证的，宕机后能数据恢复；
原子性是通过 undo log（回滚日志）来保证的，事务能够进行回滚；
隔离性是通过 MVCC（多版本并发控制）或锁机制来保证的；
一致性则是通过持久性+原子性+隔离性来保证；

并行事务会引发的问题：

脏读：读到其他事务未提交的数据；
不可重复读：一个事务内，前后读取的数据不一致；
幻读：一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。

严重性：脏读 > 不可重读读 > 幻读

幻读

产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。

因此，为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁 (Gap Lock)。

幻读说明：

在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现。
幻读仅专指“新插入的行”。

隔离级别

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

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

对于「读未提交」隔离级别的事务来说，写操作加排他锁，读操作不加锁；
对于「串行化」隔离级别的事务来说，所有写操作加临键锁，所有读操作加共享锁；
对于「读提交」和「可重复读」隔离级别的事务来说，它们是通过（读视图）Read View 来实现的，它们的区别在于创建 Read View 的时机不同，「读提交」隔离级别是在「每个语句执行前」都会重新生成一个 Read View，而「可重复读」隔离级别是「启动事务时」生成一个 Read View，然后整个事务期间都在用这个 Read View。

MVCC

在MySQL众多的开源存储引擎中，几乎只有InnoDB实现了MVCC机制，仅在RC读已提交级别、RR可重复读级别才会使用MVCC机制。

多版本主要依赖Undo-log日志来实现，而并发控制则通过表的隐藏字段+ReadView快照来实现。当一个事务尝试改动某条数据时，会将原本表中的旧数据放入Undo-log日志中；当一个事务尝试查询某条数据时，MVCC会生成一个ReadView快照。

InnoDB 为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务 ID。“活跃”指的就是，启动了但还没提交。

数组里面事务 ID 的最小值记为低水位，当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位。

这个视图数组和高水位，就组成了当前事务的一致性视图（read-view）。

对于当前事务的启动瞬间来说，一个数据版本的 row trx_id，有以下几种可能：

如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的；
如果落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的；
如果落在黄色部分，那就包括两种情况：
若 row trx_id 在数组中，表示这个版本是由还没提交的事务生成的，不可见；
若 row trx_id 不在数组中，表示这个版本是已经提交了的事务生成的，可见。

Read View 中的字段：

m_ids：表示在生成当前ReadView时，系统内活跃的事务ID列表。启动但还未提交的事务ID
min_trx_id：活跃的事务列表中，最小的事务ID。
max_trx_id：表示在生成当前ReadView时，系统中要给下一个事务分配的ID值。
creator_trx_id：代表创建当前这个ReadView的事务ID。

聚簇索引记录中两个跟事务有关的隐藏列：

TRX_ID：最近一次改动当前这条数据的事务ID
ROLL_PTR：回滚指针。当一个事务对一条数据做了改动后，都会将旧版本的数据放到Undo-log日志中，而ROLL_PTR就是一个地址指针，指向Undo-log日志中旧版本的数据，当需要回滚事务时，就可以通过这个隐藏列，来找到改动之前的旧版本数据。

可重复读如何工作的？

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

读已提交如何工作的？

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

MySQL 可重复读和幻读

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

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

MySQL Innodb 中的 MVCC 并不能完全避免幻读现象

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

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

更新数据都是先读后写的，而这个读，只能读当前的值，称为“当前读”（current read）。

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

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，从而避免其他事务插入一条新记录。

锁

全局锁

对整个数据库实例加锁。

使用全局锁 flush tables with read lock 后数据库处于只读状态，unlock tables 释放全局锁，会话断开全局锁自动释放。

应用场景：全库逻辑备份

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

官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数 –single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。single-transaction 方法只适用于所有的表使用事务引擎的库。

表级锁

表级锁包括表锁、元数据锁、意向锁、自增锁

表锁

//表级别的共享锁，也就是读锁；
lock tables t_student read;

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

// 释放会话所有表锁，会话退出后，也会释放所有表锁
unlock tables;

表锁除了会限制别的线程的读写外，也会限制本线程接下来的读写操作。表锁的颗粒度太大，尽量避免使用。

元数据锁（Meta Data Lock）

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

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

对一张表进行 CRUD 操作时，加的是 MDL 读锁；对一张表做结构变更操作的时候，加的是 MDL 写锁；读锁之间不互斥，读写锁之间、写锁之间是互斥的。

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

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

意向锁（Intention Lock）

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

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

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

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

意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁和独占表锁发生冲突。

自增锁（AUTO-INC Lock）

声明 AUTO_INCREMENT 属性的字段数据库自动赋递增的值，主要是通过 AUTO-INC 锁实现的。

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

在 MySQL 5.1.22 版本开始，InnoDB 存储引擎提供了一种轻量级的锁来实现自增。一样也是在插入数据的时候，会为被 AUTO_INCREMENT 修饰的字段加上轻量级锁，然后给该字段赋值一个自增的值，就把这个轻量级锁释放了，而不需要等待整个插入语句执行完后才释放锁。

行级锁

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

行级锁包括记录锁、间隙锁、临键锁、插入意向锁

在读已提交隔离级别下，行级锁的种类只有记录锁，也就是仅仅把一条记录锁上。

在可重复读隔离级别下，行级锁的种类除了有记录锁，还有间隙锁（目的是为了避免幻读）

记录锁（Record Lock）

记录锁，也就是仅仅把一条记录锁上；

间隙锁（Gap Lock）

间隙锁，锁定一个范围，但是不包含记录本身；

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

跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作。间隙锁之间都不存在冲突关系。

临键锁（Next-Key Lock）

临键锁，间隙锁+行锁的组合，锁定一个范围，并且锁定记录本身，即锁定左开右闭的区间。

如果一个事务获取了 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，是会被阻塞的。数据库默认加临键锁。

插入意向锁（Insert Intention Lock）

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

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

加锁规则

两个“原则”、两个“优化”和一个“bug”

原则 1：加锁的对象是索引，加锁的基本单位是 next-key lock。next-key lock 是前开后闭区间。
原则 2：查找过程中访问到的对象才会加锁。
优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

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

select * from performance_schema.data_locks\G;

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

LOCK_MODE 可以确认是 next-key 锁，还是间隙锁，还是记录锁：

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

分析加锁规则的时候可以用 next-key lock 来分析。但是具体执行的时候，是要分成间隙锁和行锁两段来执行的。

唯一索引等值查询

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

由于表 t 中没有 id=7 的记录，所以根据加锁规则：

根据原则 1，加锁单位是 next-key lock，session A 加锁范围就是 (5,10]；
同时根据优化 2，这是一个等值查询 (id=7)，而 id=10 不满足查询条件，next-key lock 退化成间隙锁，因此最终加锁的范围是 (5,10)。

所以，session B 要往这个间隙里面插入 id=8 的记录会被锁住，但是 session C 修改 id=10 这行是可以的。

非唯一索引等值查询

这里 session A 要给索引 c 上 c=5 的这一行加上读锁。

根据原则 1，加锁单位是 next-key lock，因此会给 (0,5] 加上 next-key lock。
要注意 c 是普通索引，因此仅访问 c=5 这一条记录是不能马上停下来的，需要向右遍历，查到 c=10 才放弃。根据原则 2，访问到的都要加锁，因此要给 (5,10] 加 next-key lock。
但是同时这个符合优化 2：等值判断，向右遍历，最后一个值不满足 c=5 这个等值条件，因此退化成间隙锁 (5,10)。
根据原则 2 ，只有访问到的对象才会加锁，这个查询使用覆盖索引，并不需要访问主键索引，所以主键索引上没有加任何锁，这就是为什么 session B 的 update 语句可以执行完成。

但 session C 要插入一个 (7,7,7) 的记录，就会被 session A 的间隙锁 (5,10) 锁住。

需要注意，在这个例子中，lock in share mode 只锁覆盖索引，但是如果是 for update 就不一样了。执行 for update 时，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。

这个例子说明，锁是加在索引上的；同时，如果你要用 lock in share mode 来给行加读锁避免数据被更新的话，就必须得绕过覆盖索引的优化，在查询字段中加入索引中不存在的字段。比如，将 session A 的查询语句改成 select d from t where c=5 lock in share mode。

唯一索引范围查询

分析：

开始执行的时候，要找到第一个 id=10 的行，因此本该是 next-key lock(5,10]。根据优化 1，主键 id 上的等值条件，退化成行锁，只加了 id=10 这一行的行锁。
范围查找就往后继续找，找到 id=15 这一行停下来，因此需要加 next-key lock(10,15]。

所以，session A 这时候锁的范围就是主键索引上，行锁 id=10 和 next-key lock(10,15]。

需要注意，首次 session A 定位查找 id=10 的行的时候，是当做等值查询来判断的，而向右扫描到 id=15 的时候，用的是范围查询判断。

唯一索引范围锁 bug：

session A 是一个范围查询，按照原则 1 的话，应该是索引 id 上只加 (10,15] 这个 next-key lock，并且因为 id 是唯一键，所以循环判断到 id=15 这一行就应该停止了。

但是实现上，InnoDB 会往前扫描到第一个不满足条件的行为止，也就是 id=20。而且由于这是个范围扫描，因此索引 id 上的 (15,20] 这个 next-key lock 也会被锁上。

所以你看到了，session B 要更新 id=20 这一行，是会被锁住的。同样地，session C 要插入 id=16 的一行，也会被锁住。

非唯一索引范围查询

分析：

在第一次用 c=10 定位记录的时候，索引 c 上加了 (5,10] 这个 next-key lock 后，由于索引 c 是非唯一索引，没有优化规则，也就是说不会蜕变为行锁，因此最终 sesion A 加的锁是，索引 c 上的 (5,10] 和 (10,15] 这两个 next-key lock。

所以从结果上来看，sesson B 要插入（8,8,8) 的这个 insert 语句时就被堵住了。

这里需要扫描到 c=15 才停止扫描，是合理的，因为 InnoDB 要扫到 c=15，才知道不需要继续往后找了。

没有索引的查询

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

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

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

limit 语句加锁

插入记录：

insert into t values(30,10,30);

session A 的 delete 语句加了 limit 2。表 t 里 c=10 的记录其实只有两条，因此加不加 limit 2，删除的效果都是一样的，但是加锁的效果却不同。

delete 语句明确加了 limit 2 的限制，因此在遍历到 (c=10, id=30) 这一行之后，满足条件的语句已经有两条，循环就结束了。因此，索引 c 上的加锁范围是从（c=5,id=5) 到（c=10,id=30) 这个前开后闭区间。

在删除数据的时候尽量加 limit。这样不仅可以控制删除数据的条数，让操作更安全，还可以减小加锁的范围。

日志

undo log

undo log 是 Innodb 存储引擎层生成的日志，主要用于事务回滚和 MVCC。

InnoDB默认是将Undo-log存储在xx.ibdata共享表数据文件当中，默认采用段的形式存储。

也就是当一个事务尝试写某行表数据时，首先会将旧数据拷贝到xx.ibdata文件中，将表中行数据的隐藏字段：roll_ptr回滚指针会指向xx.ibdata文件中的旧数据，然后再写表上的数据。

那Undo-log究竟在xx.ibdata文件中怎么存储呢？

在共享表数据文件中，有一块区域名为Rollback Segment回滚段，每个回滚段中有1024个Undo-log Segment，每个Undo段可存储一条旧数据，而执行写SQL时，Undo-log就是写入到这些段中。在MySQL5.5版本前，默认只有一个Rollback Segment，而在MySQL5.5版本后，默认有128个回滚段，即支持128*1024条Undo记录同时存在。

当一个事务需要回滚时，本质上并不会以执行反SQL的模式还原数据，而是直接将roll_ptr回滚指针指向的Undo记录，从xx.ibdata共享表数据文件中拷贝到xx.ibd表数据文件，覆盖掉原本改动过的数据。

redo log

redo log是 Innodb 存储引擎层生成的日志，记录当前SQL归属事务的状态，以及记录的是在某个数据页做了什么修改。主要用于掉电等故障恢复。

redo log是一种预写式日志（WAL 的全称是 Write-Ahead Logging），会先记录日志再去更新缓冲区中的数据。

具体来说，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log（粉板）里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面。

写的Redo-log日志，也是写在内存中的redo_log_buffer缓冲区，刷盘策略（innodb_flush_log_at_trx_commit控制）：

0：有事务提交情况下，每间隔1秒刷写一次日志到磁盘；
1：每次提交事务时，都刷写一次日志到磁盘。默认
2：每次提交事务时，把日志记录放到内核缓冲区，刷写实际交给操作系统控制。

redo log 和 undo log 区别在哪？

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

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

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

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

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

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

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

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

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

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

主要有下面几个时机：

MySQL 正常关闭时；
当 redo log buffer 中记录的写入量大于 redo log buffer 内存空间的一半时，会触发落盘；
InnoDB 的后台线程每隔 1 秒，将 redo log buffer 持久化到磁盘。
每次事务提交时都将缓存在 redo log buffer 里的 redo log 直接持久化到磁盘

redo log 和 binlog 有什么区别？

redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。
redo log 主要用于掉电等故障恢复；binlog 用于归档、主从复制

binlog

bin log 是 Server 层生成的日志，记录每条SQL操作日志，主要是用于数据的主从复制与数据归档。

binlog的写入机制：

binlog 的写入逻辑比较简单：事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中。

一个事务的 binlog 是不能被拆开的，因此不论这个事务多大，也要确保一次性写入。

系统给 binlog cache 分配了一片内存，每个线程有自己 binlog cache，但是共用同一份 binlog 文件。事务提交的时候，执行器把 binlog cache 里的完整事务写入到 binlog 中（指把日志写入到文件系统的 page cache，并没有把数据持久化到磁盘），并清空 binlog cache，最后将数据持久化到磁盘。

binlog 的三种格式：statement、row、mixed

为什么会有 mixed 这种 binlog 格式的存在场景？

因为有些 statement 格式的 binlog 可能会导致主备不一致，所以要使用 row 格式。
但 row 格式的缺点是，很占空间。比如你用一个 delete 语句删掉 10 万行数据，用 statement 的话就是一个 SQL 语句被记录到 binlog 中，占用几十个字节的空间。但如果用 row 格式的 binlog，就要把这 10 万条记录都写到 binlog 中。这样做，不仅会占用更大的空间，同时写 binlog 也要耗费 IO 资源，影响执行速度。
所以，MySQL 就取了个折中方案，也就是有了 mixed 格式的 binlog。mixed 格式的意思是，MySQL 自己会判断这条 SQL 语句是否可能引起主备不一致，如果有可能，就用 row 格式，否则就用 statement 格式。

现在越来越多的场景要求把 MySQL 的 binlog 格式设置成 row。这么做的理由有很多，我来给你举一个可以直接看出来的好处：恢复数据。

主备原理：

主库接收到客户端的更新请求后，执行内部事务的更新逻辑，同时写 binlog。
备库 B 跟主库 A 之间维持了一个长连接。
备库会启动两个线程，就是图中的 io_thread 和 sql_thread。其中 io_thread 负责与主库建立连接。
主库 A 从本地读取 binlog，发给 B。
备库 B 拿到 binlog 后，写到本地文件，称为中转日志（relay log）。
sql_thread 读取中转日志，解析出日志里的命令，并执行。(sql_thread 可以为多个线程)

两阶段提交

将 redo log 的写入拆成了两个步骤： prepare 和 commit，这就是"两阶段提交"。为了使两个日志之间保持一致。

当在写bin log之前崩溃时：此时 binlog 还没写，redo log 也还没提交，事务会回滚。日志保持一致
当在写bin log之后崩溃时：重启恢复后redo log虽没有commit，但满足prepare和binlog完整，自动commit。日志保持一致

溃恢复时的判断规则：

如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交；
如果 redo log 里面的事务只有完整的 prepare，则判断对应的事务 binlog 是否存在并完整：
如果是，则提交事务；
否则，回滚事务。

为什么需要两阶段提交？

redo log 影响主库的数据，binlog 影响从库的数据，所以 redo log 和 binlog 必须保持一致才能保证主从数据一致。

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

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

两个阶段提交就是将 redo log 的写入拆成了两个步骤：prepare 和 commit，中间再穿插写入binlog，具体如下：

prepare 阶段：将 redo log 持久化到磁盘（innodb_flush_log_at_trx_commit = 1 的作用），将 redo log 对应的事务状态设置为 prepare；
commit 阶段：将 binlog 持久化到磁盘（sync_binlog = 1 的作用），然后将 redo log 状态设置为 commit。

两阶段提交有什么问题？

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

组提交

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

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

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

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

Buffer Pool

存储引擎

MyISAM引擎 vs InnoDB引擎

磁盘文件

其中使用MyISAM引擎的表：zz_myisam_index，会在本地生成三个磁盘文件：

zz_myisam_index.frm：该文件中存储表的结构信息。
zz_myisam_index.MYD：该文件中存储表的行数据。
zz_myisam_index.MYI：该文件中存储表的索引数据。

从这里可得知一点：MyISAM引擎的表数据和索引数据，会分别放在两个不同的文件中存储。

而反观使用InnoDB引擎的表：zz_innodb_index，在磁盘中仅有两个文件：

zz_innodb_index.frm：该文件中存储表的结构信息。
zz_innodb_index.ibd：该文件中存储表的行数据和索引数据。

聚簇索引支持

MyISAM表数据和索引数据是分别放在.MYD、.MYI文件中，所以注定了MyISAM引擎只支持非聚簇索引。而InnoDB引擎的表数据、索引数据都放在.ibd文件中存储，因此InnoDB是支持聚簇索引的。

聚簇索引的要求是：索引键和行数据必须在物理空间上也是连续的，而MyISAM表数据和索引数据，分别位于两个磁盘文件中，这也就注定了它无法满足聚簇索引的要求。

事务机制

使用InnoDB存储引擎的表，可以借助undo-log日志实现事务机制。而MyISAM并未设计类似的技术，在启动时不会在内存中构建undo_log_buffer缓冲区，磁盘中也没有相应的日志文件，因此MyISAM并不支持事务机制。

故障恢复

InnoDB引擎由于redo-log日志的存在，因此只要事务提交，机器断电、程序宕机等各种灾难情况，都可以用redo-log日志来恢复数据。但MyISAM引擎同样没有redo-log日志，所以并不支持数据的故障恢复，如果表是使用MyISAM引擎创建的，当一条SQL将数据写入到了缓冲区后，SQL还未被写到bin-log日志，此时机器断电、DB宕机了，重启之后由于数据在宕机前还未落盘，所以丢了也就无法找回。

锁粒度

MyISAM仅支持表锁，而InnoDB同时支持表锁、行锁。

MyISAM引擎优势：

MyISAM引擎中会记录表的行数，也就是当执行count()时，如果表是MyISAM引擎，则可以直接获取之前统计的值并返回。InnoDB引擎中是不具备的。
当使用delete命令清空表数据时，MyISAM会直接重新创建表数据文件，而InnoDB则是一行行删除数据，因此对于清空表数据的操作，MyISAM比InnoDB快上无数倍。同时MyISAM引擎的表，对于delete过的数据不会立即删除，而且先隐藏起来，后续定时删除或手动删除。
MyISAM引擎中，所有已创建的索引都是非聚簇索引，每个索引之间都是独立的，在索引中存储的是直接指向行数据的地址，而并非聚簇索引的索引键，因此无论走任何索引，都仅需一次即可获得数据，无需做回表查询。

Memory 引擎 vs InnoDB引擎

InnoDB 表的数据总是有序存放的，而内存表的数据就是按照写入顺序存放的；
当数据文件有空洞的时候，InnoDB 表在插入新数据的时候，为了保证数据有序性，只能在固定的位置写入新值，而内存表找到空位就可以插入新值；
数据位置发生变化的时候，InnoDB 表只需要修改主键索引，而内存表需要修改所有索引；
InnoDB 表用主键索引查询时需要走一次索引查找，用普通索引查询的时候，需要走两次索引查找。而内存表没有这个区别，所有索引的“地位”都是相同的。
InnoDB 支持变长数据类型，不同记录的长度可能不同；内存表不支持 Blob 和 Text 字段，并且即使定义了 varchar(N)，实际也当作 char(N)，也就是固定长度字符串来存储，因此内存表的每行数据长度相同。

不建议在生产环境上使用内存表，原因主要包括两个方面：

锁粒度问题：内存表不支持行锁，只支持表锁。
数据持久化问题。

SQL优化

客户端与连接层的优化：调整客户端DB连接池的参数和DB连接层的参数。

MySQL结构的优化：合理的设计库表结构，表中字段根据业务选择合适的数据类型、索引。一张表最多最多只能允许设计30个字段左右，否则会导致查询时的性能明显下降。

MySQL参数优化：调整参数的默认值，根据业务将各类参数调整到合适的大小。

整体架构优化：引入中间件减轻数据库压力，优化MySQL架构提高可用性。例如redis、MQ、读写分离、分库分表。

编码层优化：根据库表结构、索引结构优化业务SQL语句，提高索引命中率。

主键优化

满足业务需求的情况下，尽量降低主键的长度；
插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键；
尽量不要使用UUID做主键或者是其他自然主键，如身份证号；
业务操作时，避免对主键的修改。

order by优化

MySQL的排序，有两种方式:

对于以上的两种排序方式，Using index的性能高，而Using filesort的性能低，我们在优化排序操作时，尽量要优化为 Using index。

order by优化原则：

根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则；
尽量使用覆盖索引；
多字段排序, 一个升序一个降序，此时需要注意联合索引在创建时的规则(ASC/DESC)；
如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认256k)。

group by优化

在分组操作中，我们需要通过以下两点进行优化，以提升性能:

如果对 group by 语句的结果没有排序要求，要在语句后面加 order by null；
尽量让 group by 过程用上表的索引，确认方法是 explain 结果里没有 Using temporary 和 Using filesort；
如果 group by 需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大 tmp_table_size 参数，来避免用到磁盘临时表；
如果数据量实在太大，使用 SQL_BIG_RESULT 这个提示，来告诉优化器直接使用排序算法得到 group by 的结果。

limit优化

在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。

因为，当在进行分页查询时，如果执行 limit 2000000,10 ，此时需要MySQL排序前2000010 记录，仅仅返回 2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非常大。

优化思路: 一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。

explain select * from tb_sku t , (select id from tb_sku order by id limit 2000000,10) a where t.id = a.id;

count优化

如果数据量很大，在执行count操作时，是非常耗时的。InnoDB 引擎中，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加，最后返回累计值。

性能：

count(*) = count(1) > count(主键字段) > count(字段)

count(1)、 count(*)、 count(主键字段)在执行的时候，如果表里存在二级索引，优化器就会选择二级索引进行扫描。因为二级索引记录比聚簇索引记录占用更少的存储空间。

count(1)时， server 层每从 InnoDB 读取到一条记录，就将 count 变量加 1，不会读取任何字段。

count(*) 其实等于 count(0)，也就是说，当你使用 count(*) 时，MySQL 会将 * 参数转化为参数 0 来处理。count(*) 执行过程跟 count(1) 执行过程基本一样的

count(字段) 来统计记录个数，它的效率是最差的，会采用全表扫描的方式来统计。

优化思路：

近似值：使用 show table status 或者 explain 命令来表进行估算。
用缓存系统保存计数：奔溃丢失问题、数据不一致问题
用数据库保存计数：InnoDB 是支持崩溃恢复不丢数据的、使用事务解决数据不一致问题

update优化

我们主要需要注意一下update语句执行时的注意事项。

update course set name = 'javaEE' where id = 1 ;

当我们在执行删除的SQL语句时，会锁定id为1这一行的数据，然后事务提交之后，行锁释放。

当我们开启多个事务，再执行如下SQL时：

update course set name = 'SpringBoot' where name = 'PHP' ;

我们发现行锁升级为了表锁。导致该update语句的性能大大降低。

Innodb的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级成表锁。

SQL性能分析

sql执行频率

Mysql客户端链接成功后，通过以下命令可以查看当前数据库的 insert/update/delete/select 的访问频次：

show [session|global] status like ‘com_____’;

session: 查看当前会话；

global: 查看全局数据；

com_insert: 插入次数；

com_select: 查询次数；

com_delete: 删除次数；

com_updat: 更新次数；

通过查看当前数据库是以查询为主，还是以增删改为主，从而为数据库优化提供参考依据，如果以增删改为主，可以考虑不对其进行索引的优化；如果以查询为主，就要考虑对数据库的索引进行优化

慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time,单位秒，默认10秒）的所有sql日志：

开启慢查询日志前，需要在mysql的配置文件中（/etc/my.cnf）配置如下信息：

开启mysql慢日志查询开关：

1	`slow_query_log = 1`

设置慢日志的时间，假设为2秒，超过2秒就会被视为慢查询，记录慢查询日志：

1	`long_query_time=2`

配置完毕后，重新启动mysql服务器进行测试:

1	`systemctl restarmysqld`

查看慢查询日志的系统变量，是否打开：

1	`show variables like “slow_query_log”;`

查看慢日志文件中（/var/lib/mysql/localhost-slow.log）记录的信息：

1	`Tail -f localhost-slow.log`

最终发现，在慢查询日志中，只会记录执行时间超过我们预设时间（2秒）的sql，执行较快的sql不会被记录。

Profile 详情

show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。

通过 have_profiling 参数，可以看到mysql是否支持profile 操作：

1	`select @@have_profiling;`

通过set 语句在session/global 级别开启profiling:

1	`set profiling =1;`

开关打开后，后续执行的sql语句都会被mysql记录，并记录执行时间消耗到哪儿去了。比如执行以下几条sql语句：

select * from tb_user;

select * from tb_user where id = 1;

select * from tb_user where name = '白起';

select count(*) from tb_sku;

查看每一条sql的耗时基本情况：

1	`show profiles;`

查看指定的字段的sql 语句各个阶段的耗时情况：

1	`show profile for query Query_ID;`

查看指定字段的sql语句cpu 的使用情况：

1	`show profile cpu for query Query_ID;`

explain 详情

EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中，表如何连接和连接的顺序。

EXPLAIN 并不会真的去执行相关的语句，而是通过查询优化器对语句进行分析，找出最优的查询方案，并显示对应的信息。

语法 :直接在 select 语句之前加上关键字 explain/desc;

type 字段就是描述了找到所需数据时使用的扫描方式是什么，常见扫描类型的执行效率从低到高的顺序为：

All（全表扫描）：在这些情况里，all 是最坏的情况，因为采用了全表扫描的方式。
index（全索引扫描）：index 和 all 差不多，只不过 index 对索引表进行全扫描，这样做的好处是不再需要对数据进行排序，但是开销依然很大。所以，要尽量避免全表扫描和全索引扫描。
range（索引范围扫描）：range 表示采用了索引范围扫描，一般在 where 子句中使用 < 、>、in、between 等关键词，只检索给定范围的行，属于范围查找。从这一级别开始，索引的作用会越来越明显，因此我们需要尽量让 SQL 查询可以使用到 range 这一级别及以上的 type 访问方式。
ref（非唯一索引扫描）：ref 类型表示采用了非唯一索引，或者是唯一索引的非唯一性前缀，返回数据返回可能是多条。因为虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。
eq_ref（唯一索引扫描）：eq_ref 类型是使用主键或唯一索引时产生的访问方式，通常使用在多表联查中。比如，对两张表进行联查，关联条件是两张表的 user_id 相等，且 user_id 是唯一索引，那么使用 EXPLAIN 进行执行计划查看的时候，type 就会显示 eq_ref。
const（结果只有一条的主键或唯一索引扫描）：const 类型表示使用了主键或者唯一索引与常量值进行比较，比如 select name from product where id=1。需要说明的是 const 类型和 eq_ref 都使用了主键或唯一索引，不过这两个类型有所区别，const 是与常量进行比较，查询效率会更快，而 eq_ref 通常用于多表联查中。

extra 几个重要的参考指标：

Using filesort ：当查询语句中包含 group by 操作，而且无法利用索引完成排序操作的时候，这时不得不选择相应的排序算法进行，甚至可能会通过文件排序，效率是很低的，所以要避免这种问题的出现。
Using temporary：使了用临时表保存中间结果，MySQL 在对查询结果排序时使用临时表，常见于排序 order by 和分组查询 group by。效率低，要避免这种问题的出现。
Using index：所需数据只需在索引即可全部获得，不须要再到表中取数据，也就是使用了覆盖索引，避免了回表操作，效率不错。

范式

第一范式：确保原子性，表中每一个列数据都必须是不可再分的字段。

第二范式：确保唯一性，每张表都只描述一种业务属性，一张表只描述一件事。

第三范式：确保独立性，表中除主键外，每个字段之间不存在任何依赖，都是独立的。

巴斯范式：主键字段独立性，联合主键字段之间不能存在依赖性。

第一范式

所有的字段都是基本数据字段，不可进一步拆分。

第二范式

在满足第一范式的基础上，还要满足数据表里的每一条数据记录，都是可唯一标识的。而且所有字段，都必须完全依赖主键，不能只依赖主键的一部分。

把只依赖于主键一部分的字段拆分出去，形成新的数据表。

第三范式

在满足第二范式的基础上，不能包含那些可以由非主键字段派生出来的字段，或者说，不能存在依赖于非主键字段的字段。

巴斯-科德范式（BCNF）

巴斯-科德范式也被称为3.5NF，至于为何不称为第四范式，这主要是由于它是第三范式的补充版，第三范式的要求是：任何非主键字段不能与其他非主键字段间存在依赖关系，也就是要求每个非主键字段之间要具备独立性。而巴斯-科德范式在第三范式的基础上，进一步要求：任何主属性不能对其他主键子集存在依赖。也就是规定了联合主键中的某列值，不能与联合主键中的其他列存在依赖关系。

+-------------------+---------------+--------+------+--------+
| classes           | class_adviser | name   | sex  | height |
+-------------------+---------------+--------+------+--------+
| 计算机-2201班     | 熊竹老师      | 竹子   | 男   | 185cm  |
| 金融-2201班       | 竹熊老师      | 熊猫   | 女   | 170cm  |
| 计算机-2201班     | 熊竹老师      | 子竹   | 男   | 180cm  |
+-------------------+---------------+--------+------+--------+

例如这张学生表，此时假设以classes班级字段、class_adviser班主任字段、name学生姓名字段，组合成一个联合主键。在这张表中，一条学生信息中的班主任，取决于学生所在的班级，因此这里需要进一步调整结构：

SELECT * FROM `zz_classes`;
+------------+-------------------+---------------+
| classes_id | classes_name      | class_adviser |
+------------+-------------------+---------------+
|          1 | 计算机-2201班     | 熊竹老师      |
|          2 | 金融-2201班       | 竹熊老师      |
+------------+-------------------+---------------+

SELECT * FROM `zz_student`;
+------------+--------+------+--------+
| classes_id | name   | sex  | height |
+------------+--------+------+--------+
|          1 | 竹子   | 男   | 185cm  |
|          2 | 熊猫   | 女   | 170cm  |
|          1 | 子竹   | 男   | 180cm  |
+------------+--------+------+--------+

经过结构调整后，原本的学生表则又被拆为了班级表、学生表两张，在学生表中只存储班级ID，然后使用classes_id班级ID和name学生姓名两个字段作为联合主键。

问题

数据库抖动

问题：一条 SQL 语句，正常执行的时候特别快，但是有时也不知道怎么回事，它就会变得特别慢。

原因：平时执行很快的更新操作，其实就是在写内存和日志，而 MySQL 偶尔“抖”一下的那个瞬间，可能就是在刷脏页（flush）。

什么情况会引发数据库的 flush 过程呢？

redo log 写满了。这时候系统会停止所有更新操作，把 checkpoint 往前推进，将对应的所有脏页都 flush 到磁盘上，redo log 留出空间可以继续写。
系统内存不足。当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。
MySQL 系统“空闲”的时候刷脏页
MySQL 正常关闭前

只查一行的语句，也执行这么慢？

问题1：查询结果长时间不返回

原因：一般碰到这种情况的话，大概率是表 t 被锁住了。一般都是首先执行一下 show processlist 命令，看看当前语句处于什么状态。

等 MDL 锁。解决：通过查询 sys.schema_table_lock_waits 这张表，我们就可以直接找出造成阻塞的 process id，把这个连接用 kill 命令断开即可。
等 flush
等行锁

问题2：查询慢

原因：全表扫描未走索引；undo log链路太长

自增值

执行 insert into t values(null, 1, 1); 插入一行数据，再执行 show create table 命令，就可以看到如下图所示的结果：

表定义里面出现了一个 AUTO_INCREMENT=2，表示下一次插入数据时，如果需要自动生成自增值，会生成 id=2。

不同的引擎对于自增值的保存策略不同：

MyISAM 引擎的自增值保存在数据文件中。
InnoDB 引擎的自增值，其实是保存在了内存里，并且到了 MySQL 8.0 版本后，才有了“自增值持久化”的能力，也就是才实现了“如果发生重启，表的自增值可以恢复为 MySQL 重启前的值”，具体情况是：
在 MySQL 5.7 及之前的版本，自增值保存在内存里，并没有持久化。每次重启后，第一次打开表的时候，都会去找自增值的最大值 max(id)，然后将 max(id)+1 作为这个表当前的自增值。举例来说，如果一个表当前数据行里最大的 id 是 10，AUTO_INCREMENT=11。这时候，我们删除 id=10 的行，AUTO_INCREMENT 还是 11。但如果马上重启实例，重启后这个表的 AUTO_INCREMENT 就会变成 10。也就是说，MySQL 重启可能会修改一个表的 AUTO_INCREMENT 的值。
在 MySQL 8.0 版本，将自增值的变更记录在了 redo log 中，重启的时候依靠 redo log 恢复重启之前的值。

在 MySQL 里面，如果字段 id 被定义为 AUTO_INCREMENT，在插入一行数据的时候，自增值的行为如下：

如果插入数据时 id 字段指定为 0、null 或未指定值，那么就把这个表当前的 AUTO_INCREMENT 值填到自增字段；
如果插入数据时 id 字段指定了具体的值，就直接使用语句里指定的值。

根据要插入的值和当前自增值的大小关系，自增值的变更结果也会有所不同。假设，某次要插入的值是 X，当前的自增值是 Y。

如果 X<Y，那么这个表的自增值不变；
如果 X≥Y，就需要把当前自增值修改为新的自增值。

自增值的修改时机：

假设，表 t 里面已经有了 (1,1,1) 这条记录，这时我再执行一条插入数据命令：

insert into t values(null, 1, 1); 

这个语句的执行流程就是：

执行器调用 InnoDB 引擎接口写入一行，传入的这一行的值是 (0,1,1);
InnoDB 发现用户没有指定自增 id 的值，获取表 t 当前的自增值 2；
将传入的行的值改成 (2,1,1);
将表的自增值改成 3；
继续执行插入数据操作，由于已经存在 c=1 的记录，所以报 Duplicate key error，语句返回。

自增值改成 3，是在真正执行插入数据的操作之前。这个语句真正执行的时候，因为碰到唯一键 c 冲突，所以 id=2 这一行并没有插入成功，但也没有将自增值再改回去。所以，在这之后，再插入新的数据行时，拿到的自增 id 就是 3。也就是说，出现了自增主键不连续的情况。

唯一键冲突是导致自增主键 id 不连续的第一种原因。

事务回滚也会产生类似的现象，这就是第二种原因。

自增上限：

表定义的自增值达到上限后的逻辑是：再申请下一个 id 时，得到的值保持不变。

如果你创建的 InnoDB 表没有指定主键，那么 InnoDB 会给你创建一个不可见的，长度为 6 个字节的 row_id。InnoDB 维护了一个全局的 dict_sys.row_id 值，所有无主键的 InnoDB 表，每插入一行数据，都将当前的 dict_sys.row_id 值作为要插入数据的 row_id，然后把 dict_sys.row_id 的值加 1。

实际上，在代码实现时 row_id 是一个长度为 8 字节的无符号长整型 (bigint unsigned)。但是，InnoDB 在设计时，给 row_id 留的只是 6 个字节的长度。写入表的 row_id 是从 0 开始到 2^48-1。达到上限后，下一个值就是 0，然后继续循环。