@TOC

系列文章

四、S 锁与 X 锁的爱恨情仇《死磕 MySQL 系列 四》

五、如何抉择一般索引和惟一索引《死磕 MySQL 系列 五》

六、五分钟，让你明确 MySQL 是怎么抉择索引《死磕 MySQL 系列 六》

七、字符串能够这样加索引，你知吗？《死磕 MySQL 系列 七》

我的项目中将 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 的查问变的更慢。

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