十二讲笔记——为什么我的 MySQL 会“抖”一下

为什么 SQL 语句变“慢”了

在 InnoDB 处理更新语句的时候，只做了写日志这个磁盘操作，这个日志叫 redo log（重做日志）。

当 InnoDB 有空的时候，就把日志更新一下，这个就是把内存里的数据写入磁盘，这个操作叫做 flush。

在做 flush 这个操作之前，内存中数据页的数据跟磁盘中的数据页数据是不一样的，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和数据库中的数据一致，我们称为“干净页”。

注：不论是脏页还是干净页，都是在内存中的。

可以想象，有时候数据库“抖”一下，可能就是在刷新脏页（flush）。

那什么时候会触发 flush 呢？

• 第一种情况就是，redolog 满了。这个时候，系统会停止所有的更新操作，把 checkpoint 往前推进， 这样 redolog 就有空间可以写了。

  

  如果把 checkpoint 从 cp 位置推进到 cp’ ，那么要把绿色部分对应的脏页都 flush 到磁盘上。之后，图中从 write pos 到 cp' 之间就可以再写入。

• 第二种场景是，内存不足。当内存中放入了太多的数据页，就要放一些回磁盘。如果淘汰的是脏页，就要先将脏页写入磁盘。如果是干净页，则直接释放内存。

  从性能的角度出发，刷脏页一定会写盘，就保证每个数据页有两种状态：

  • 内存中存在，则内存中肯定是正确的结果，直接返回；
  • 内存中不存在，则磁盘上的是正确结果，读入内存，直接返回；

• 第三种场景是，当整个系统“空闲”的时候，就会 flush 。即使是在系统运行的时候，MySQL 也会见缝插针将脏页 flush 回磁盘。

• 第四种场景是，当 MySQL 正常关闭时。

我们来分析前两种场景对于性能的影响

第一种是 redolog 写满了，要 flush 脏页。这种情况 InnoDB 是要避免的，因为此时会堵住整个数据库的更新操作。

第二种是内存不够用了，这种是常态。InnoDB 采用缓冲池（buffer pool）来管理内存，缓冲池中有三种状态：

• 第一种是：还没有使用的数据页；
• 第二种是：使用了并且是干净页；
• 第三种是：使用了并且是脏页；

InnoDB 的策略是尽量使用内存。当使用的数据页不在内存中时，但内存满了就要 flush 掉一些页面。

InnoDB 刷脏页的控制策略

innodb_io_capacity 这个参数可以告诉 InnoDB，你主机的 IO 能力，建议设置成磁盘的 IOPS（用于计算机存储设备性能测试方法，即每秒读写次数）。

这个能定义“全力刷脏页”的行为，但平时总不能全力刷，毕竟还是要提供服务的。

InnoDB 的刷盘次数要参考：脏页比例和 redolog 写盘速度。

InnoDB 会根据这两个因素算出两个数字，M 和 N。

参数 innodb_max_dirty_pages_pct 是脏页比例上限，默认值是 75%。InnoDB 会根据当前脏页比例（M），算出一个在 0 到 100 之间的数字。

InnoDB 每次写入的日志都会有一个序号，当前写入的序号跟 checkpoint 对应的序号之间的差值（N），算出一个在 0 到 100 之间的数字。

然后根据一个算法计算出 F2(M), F2(N) 这两个值，取其中较大的值记为 R，之后按照参数 inoodb_io_capacity 定义的能力乘以 R% 来控制刷脏页的速度。

所以，InnoDB 在后台刷脏页时，无论是在更新语句还是查询语句，都会“抖”一下。

要尽量避免这种情况，要合理的设置 inoodb_io_capacity 的值。

flush 的连带机制

MySQL 中的一个机制：在准备刷掉一个脏页时，如果这个数据页旁边的数据页也是脏页，就会把这个“邻居”一起刷掉；而且这个连带机制会一直蔓延。

在 InnoDB 中，inoodb_flush_neighbors 参数就是用来控制这个行为的，值为 1 时就会有上面的连带机制，值为 0 时，就只刷自己。

如果数据库在机械硬盘，这个机制很有意义。可是如果是 SSD 这类 IOPS 比较高的设备的话，建议关掉。

在 MySQL 8.0 中，inoodb_flush_neighbors 参数的默认值已经是 0 了。