@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的查问变的更慢。

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