平时执行很快的SQL语句，为什么会突然卡一下？

本文主要是介绍平时执行很快的SQL语句，为什么会突然卡一下？，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

InnoDB在处理更新语句的时候，只做了写日志这一个磁盘操作，这个日志叫作redo log（重做日志），在更新内存写完redo log后，就返回给客户端，本次更新成功。

把内存里的数据写入磁盘的过程，术语是flush。

当内存数据页跟磁盘数据页内容不一致的时候，这个内存页称为“脏页”，内存数据写入磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。

因此，平时执行很快的更新操作，其实就是在写内存和日志，而卡的那一瞬间，可能就是在刷脏页（flush）。

引发数据库的flush过程有以下4种情况：

1、InnoDB的redo log写满了，这时候系统就会停止所有更新操作，把checkpoint往前推进，redo log留出空间可以继续写。

2、系统内存不足，当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用，如果淘汰的是“脏页”，就要先将脏页写到磁盘。

3、MySQL认为系统“空闲”的时候。

4、MySQL正常关闭的时候。

其中，第3、4种情况不太会影响性能，我们主要看第1、2种情况。

第1种，“redo log写满了，要flush脏页”，这种情况InnoDB要尽量避免，因为出现这种情况的时候，整个系统就不能再接受更新了，所有更新都必须堵住，从监控上看，这时候更新数会跌为0。

第2种，“内存不够用了，要先将脏页写到磁盘”，这种情况其实是常态。

InnoDB用缓冲池“buffer pool”管理内存，缓冲池中的内存页有三种状态：

还没有使用的
使用了并且是干净页
使用了并且是脏页

InnoDB的策略是尽量使用内存。当要读入的数据页没有在内存的时候，就必须到缓冲池中申请一个数据页。这时候只能把最久不使用的数据页从内存中淘汰掉：如果要淘汰的是一个干净页，就直接释放出来复用；如果是脏页，就必须将脏页先刷到磁盘，变成干净页后才能复用。

所以，刷脏页虽然是常态，但是出现以下这两种情况，都是会明显影响性能的：

一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长；
日志写满，更新全部堵住，写性能跌为0，这种情况对敏感业务来说，是不能接受的。

所以，InnoDB需要有控制脏页比例的机制，来尽量避免上面的这两种情况。

InnoDB刷脏页的控制策略：

首先，需要告诉InnoDB所在主机的IO能力，这样InnoDB才能知道需要全力刷脏页的时候，可以刷多快。可以通过参数innodb_io_capacity来控制，这个值建议设置成磁盘的IOPS，可通过下述指令获取。

fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest

InnoDB控制刷脏页的速度的策略参考了两个因素：脏页比例和redo log写盘速度。

参数innodb_max_dirty_pages_pct是脏页比例上限，默认值是75%。

InnoDB根据当前的脏页比例（记为M），算出一个范围在0到100之间的数字，记为F1(M)。

InnoDB每次写入的日志都有一个序号，当前写入的序号跟checkpoint对应的序号之间的差值，记为N，同样也会计算出一个0到100之间的数字，记为F2(N)。

取F1(M)和F2(N)中较大的值记为R，InnoDB按照innodb_io_capacity*R%来控制刷脏页的速度。如下图所示

可通过下述语句获取当前的脏页比例：

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

还有一个参数innodb_flush_neighbors，用来控制刷脏页时，如果数据页旁边的数据页刚好是脏页，是否要一起刷掉，这个判断会一直蔓延下去。如果该参数值是1，则会有上述的“连坐”机制，值为0则表示不找邻居，自己刷自己的。如果使用的是SSD这类IOPS比较高的设备，建议把这个参数值设置为0，并且在MySQL8.0中，这个参数值的默认值已经是0了。

正文止。

感兴趣的朋友，欢迎关注我的公众号哈，公众号上已经集成了AI大模型，大家可以过来聊天、问问题了