项目中将 MySQL 的报错、异常、执行时间长的都打到了钉钉群中，这样有利于平时及时处理。今天要聊的是无法复现的慢查询。

一、为什会出现无法复现的“慢”SQL

在一生挚友redo log、binlog《死磕MySQL系列 二》中详细的说明了 redo log、binlog。此时你知道了在更新时当事务提交后，并非直接修改数据库的数据，而是先更新内存并在 redo log 中记录相关的操作。

总归是要把内存的数据刷入磁盘中，也可以称之为刷脏页（flush）。

什么是脏页、干净页

大多数资料都提及到脏页，那么脏页到底是什么呢？脏页时内存数据页的数据跟磁盘数据不一致时，就称这个内存页为脏页。

当内存页写入磁盘后，内存和磁盘的数据页就一致了，此时称这个内存页为干净页。

什么时候脏页会变为干净页

第一种

Innodb 的 redo log 写满了，也就是下图的 write pos  追上了 check point 了，此时系统所有的更新操作都会停止。

直至 check point 推进了，对应的脏页都 flush 到磁盘了，redo log 才可以继续写。

一般情况下这个 redo log 日志在开发前期根据 innodb_log_file_size 参数设置好后就不会出现 redo log 写满的情况。

第二种

内存不足导致，更新一条语句会先更新内存再更新到 redo log，若内存不足就无法申请新的内存就需要淘汰一些数据页。就需要把脏页 flush 到磁盘。

有没有想过既然更新操作给内存和 redo log 都存了一份，那么能不能直接把内存页淘汰掉，再有请求时从磁盘读入数据页再把 redo log 拿出来应用不行吗？

内存满时不刷脏页而直接淘汰掉，那下次请求磁盘中的干净页到内存时，还需要额外的判断 redo log 中是否有对该页的修改，有的话还需要对它应用 redo log。这个脏页始终都是要刷盘的，但现在缺额外多了应用 redo log 的操作。所以不能直接淘汰内存，而是内存满时直接 flush。

另外，redo log 是循环写的，若想应用 redo log 那么 redo log 就要一直存在，不能删除。违背了系统设计。

第三种

MySQL 在系统低峰期时进行刷脏页

第四种

MySQL 正常关闭时会把内存的脏页都刷到磁盘中，重启后从磁盘直接读数据，启动速度会很快。

结论

到这里你就应该明白，莫名其妙的慢 SQL 就是因为 flush 造成的，那么这四种情况都是怎么影响 MySQL 的呢？

二、四种 flush 对性能的影响

第三、四种情况不会因为 flush 而导致 MySQL 执行慢，一个是系统空闲时段，另一个是数据库本来就要关闭了。

redo log 写满了，需要 flush 脏页

这种情况在第二期文章中就已经给了方案，redo log 一旦写满整个系统就不再接受更新操作了， 所有的更新操作都得停滞，直到 check point 推进了。

扩展

在 MySQL 中提供了 innodb_log_file_size 参数来优化 redo log 日志。

对于 innodb_log_file_size 的设置也是有一些计算规则的，下面将为你介绍。

若 innodb_log_file_size 设置太小，将导致 redo log 文件频繁切换，频繁的触发数据库的检查点（check point），导致记录更新到数据文件的次数增加，从而影响 IO 性能。

同样，如果有一个大的事务，并且所有 redo log 日志都已写满，但是还没有完成，将导致日志无法切换，从而导致 MySQL 直接堵死。

innodb_log_file_size 设置太大，虽然极大地提高了 IO 性能，但是在 MySQL 重启或宕机时，恢复时间会因为 redo log 文件过大而延长。而这种恢复时间通常是无法控制的。

如何合理的设置 innodb_log_file_size？

用一个脚本定时执行，记录对应时间的 sequenumber 再取平均值，计算出的误差将减至最小。sequenumber 是当每个 binlog 生成时，该值从 1 开始，然后递增，每增加一个事务， sequenumber 就加上 1。

系统内存不足，要刷脏页

Innodb 中管理内存的是 buffer pool，内存页在上文可得知存在三种状态，未使用的、使用了是干净页、使用了是脏页。

对于一个长时间运行的库来说，未被使用的页非常少，当内存不足时，就只能把最久不使用的数据页从内存中淘汰掉。

若淘汰的是一个干净页，就直接释放使用，但如果是脏页就必须先把脏页刷盘，变为干净页进行复用。

查询的数据没有在内存中，就需要把数据从磁盘中读入数据，若读的数据太多就需要淘汰多个脏页，会导致查询时间边长。

redo log 日志写满，所有的更新系统都不执行，对于大多数业务来说都不能接受。

为了防止这种情况的发生就需要控制刷脏页的频率。

三、如何设置刷脏页的速度

刷脏页到磁盘的快慢必定跟系统的 IO 能力有关，在 MySQL 中 innodb_io_capacity 是控制刷脏页的速度。

在从缓冲区刷新脏页时（check point）,每秒刷新脏页的数量就等于 innodb_io_capacity 的值。

这个值可以设置成磁盘的 IOPS，可以使用 fio 工具来测试，具体使用这里就不聊了。

刷脏页的速度也要根据脏页比例、redo log 写盘速度来决定。

参数 innodb_max_dirty_pages_pct 是脏页比例上限，在 MySQL8.0 这个比例默认为 90%，MySQL5.6 还是 75%。

一般情况下对于 innodb_io_capacity 的值设置为脏页比例上限与写 redo log 日志时的日志序号减去 checkpoint 的值，俩个值取最大的即可。

脏页比例的计算公式是Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total，具体执行命令为

mysql> select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';select @a/@b;

在这个 SQL 语句中可以看到使用的是 global_status 这张表在 performance_schema 这个库里边。执行命令前需要执行 use performance_schema。

当你的 MySQL 写入速度很慢，TPS 很低，IO 压力不大时需要排查的地方

出现这个问题时就考虑下一下 innodb_io_capacity 这个参数值设置是否合理。

在 1 核 2G 的服务器默认值是 200，在公司服务器上看是 2000，也是跟服务器配置有关系的。

四、有趣参数

在 MySQL8.0 中参数 innodb_flush_neighbors 默认值为 0。

当一个查询需要在执行过程中先 flush 掉一个脏页时，如果这个数据页旁边的数据页刚好是脏页，就会把这个数据页一同刷掉，而这个连带的逻辑会持续下去。会使 SQL 的查询变的更慢。

坚持学习、坚持写作、坚持分享是咔咔从业以来所秉持的信念。愿文章在偌大的互联网上能给你带来一点帮助，我是咔咔，下期见。