MySQL 12 为什么我的MySQL会“抖”一下？

火眼看世界 發表於 2025-7-12 20:33:00

MySQL 12 为什么我的MySQL会“抖”一下？

一条SQL语句，正常执行时候特别快，但有时会变得特别慢，且这种情况很难复现，随机且持续时间很短，看上去像是“抖”了一下。
<h3 id="你的sql语句为什么变慢了">你的SQL语句为什么变“慢”了</h3>
在MySQL 02中，介绍了WAL机制，InnoDB在处理更新语句时，更新内存写完redo log后，就返回给客户端，本次更新成功。
而内存里的数据最后要写入磁盘，这个操作称为flush。当内存数据页跟磁盘数据页内容不一致时，称这个内存页为脏页，内存数据写入磁盘后两者上的数据页内容一致，称为干净页。不论脏页还是干净页，都是指的内存中的数据页。
对于开头提出的场景，平时执行很快的更新操作，其实就是在写内存和日志，而“抖”的瞬间，很有可能就是在flush脏页。
有几种情况会引发数据库的flush过程：
<ul>
<li>
InnoDB的redo log写满，这时候系统会停止所有的更新操作，通过flush把redo log中的checkpoint往前推进，使得redo log能有空间可以继续写；
</li>
<li>
系统内存不足。当需要新的内存页，而内存不够用，就需要淘汰一些数据页，空出内存给别的数据页。如果要淘汰的是脏页，就需要先将脏页刷到磁盘。可能会问，这种情况为什么不能直接淘汰内存，下次请求时从磁盘读入数据页，然后应用redo log？这里是从性能考虑的，对于刷脏页写盘的方法，保证每个数据页有两种状态：
<ul>
<li>
内存里存在，肯定是正确结果，能直接返回；
</li>
<li>
内存里不存在，磁盘中肯定是正确结果，读入内存后返回。
</li>
</ul>
</li>
<li>
MySQL认为系统“空闲”，有机会就会进行flush；
</li>
<li>
MySQL正常关闭时，会把内存的脏页都flush到磁盘，这样下次MySQL启动时，直接从磁盘读数据即可，启动速度很快。
</li>
</ul>
分析上述四种情况对性能的影响：
<ul>
<li>
第一种情况要尽量避免，因为这种情况下整个系统不能再更新，从监控上看更新数会跌为0；
</li>
<li>
第二种情况较常见。在InnoDB中，用缓冲池（buffer pool）管理内存，缓冲池中的内存页有三种状态：
<ul>
<li>
还没有使用；
</li>
<li>
使用了且是干净页；
</li>
<li>
使用了且是脏页；
</li>
</ul>
由于InnoDB策略是尽量使用内存，因此对于一个长时间运行的库来说，未被使用的页面很少。如果要读入的数据页不在内存中，就需要在缓冲池申请一个数据页，把内存中最久未使用的数据页淘汰。如果淘汰的是脏页，就需要先flush。
而如果一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长，会明显影响性能。
</li>
<li>
第三种情况，系统没什么压力；
</li>
<li>
第四种情况，数据库本身就要关闭了，不需要太关注性能问题。
</li>
</ul>
<h3 id="innodb刷脏页的控制策略">InnoDB刷脏页的控制策略</h3>
接下来讲讲控制策略，以及和策略相关的参数。
首先，需要正确告诉InnoDB所在主机的IO能力，这样InnoDB才能知道需要全力刷脏页的时候可以刷多快。这需要用到<code>innodb_io_capacity</code>参数，它会告诉InnoDB你的系统的磁盘能力，其值建议设置成磁盘的IOPS，而磁盘的IOPS可以通过fio工具进行测试。
知道了“全力刷脏页”的能力，但使用时不可能一直全力刷，毕竟磁盘能力不能只用来刷脏页，还需要服务用户请求。因此，需要让InnoDB控制引擎按照“全力”的一定百分比来刷页。
那么，如果设计策略控制刷脏页的速度，你会考虑哪些因素呢？考虑到如果刷太慢，内存中脏页数会太多，且redo log可能写满，因此主要参考因素就是：
<ul>
<li>
脏页比例；
</li>
<li>
redo log写盘速度。
</li>
</ul>
InnoDB会根据这两个因素单独算出来两个数字：
<ul>
<li>
参数<code>innodb_max_dirty_pages_pct</code>是脏页比例上限，默认为75%。InnoDB会根据当前脏页比例\(M\)，算出一个范围在0-100间的数字，计算方法为：
<pre><code class="language-sql">F1(M)
{
if M>=innodb_max_dirty_pages_pct then
return 100;
return 100*M/innodb_max_dirty_pages_pct;
}
</code></pre>
</li>
<li>
InnoDB每次写入的日志都有一个序号，假设当前写入的序号跟checkpoint对应的序号之间的差值为\(N\)。InnoDB会根据\(N\)算出一个范围在0-100之间的数字，\(N\)越大算出来的值\(F2(N)\)越大。
</li>
</ul>
根据两个数字\(F1(M)\)和\(F2(N)\)，取其中较大的值记为\(R\)，之后引擎可以按照<code>innodb_io_capacity</code>定义的能力乘以\(R\)%来控制刷脏页的速度。
上述过程的流程如图：
<div align="center"><img src="https://img2024.cnblogs.com/blog/3389949/202507/3389949-20250712203223858-1974001220.png" width="30%"></div>
讲到这里你应该知道，无论是查询语句在需要内存的时候可能要求淘汰一个脏页，还是由于刷脏页会占用IO资源影响更新语句，都可能感知到MySQL“抖了一下。要尽量避免这种情况，就需要合理设置<code>innodb_io_capacity</code>的值，并且平时要多关注脏页比例，不要让它经常接近75%。
其中，脏页比例计算方法是<code>Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total</code>。
 
接下来，再看一个策略。
一旦一个查询请求需要在执行过程中先 flush 掉一个脏页时，这个查询就可能要比平时慢了。MySQL中有个机制，可能让查询更慢：在准备刷一个脏页的时候，如果这个数据页旁边的数据页也是脏页，就会把这个“邻居”也带着一起刷掉，那么可能不断往后顺延。
在InnoDB中，用<code>innodb_flush_neighbors</code>参数控制该行为，值为1会有上述机制。
这类优化在机械磁盘时代比较有意义，可以减少很多随机IO。而对IOPS比较高的设备比如SSD，建议把值设为0，因为这时候IOPS往往不是瓶颈，而只刷自己能更快执行完必要的刷脏页操作，减少SQL语句响应时间。
在MySQL 8.0中，该参数默认值已经为0了。 
来源：https://www.cnblogs.com/san-mu/p/18981030

頁: [1]

琼殿技术论坛's Archiver

MySQL 12 为什么我的MySQL会“抖”一下？