@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直接堵死。
【无法复现的“慢”SQL《死磕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的查询变的更慢。
坚持学习、坚持写作、坚持分享是咔咔从业以来所秉持的信念。愿文章在偌大的互联网上能给你带来一点帮助，我是咔咔，下期见。
