当前位置：首页 > article >正文

MySQL45讲第十二讲为什么我的MySQL会“抖”一下？

article 2024/11/4 15:02:23

在日常使用 MySQL 时，可能会遇到 SQL 语句执行速度不稳定的情况，有时正常执行很快，但偶尔会突然变慢，就像数据库 “抖” 了一下。这篇文章将深入探究这种现象背后的原因以及应对方法。

当内存数据页与磁盘数据页内容不一致时，内存页被称为 “脏页”；内存数据写入磁盘后，内存和磁盘上的数据页内容一致，称为 “干净页”。在 MySQL 中，InnoDB 处理更新语句时，先写内存和 redo log（重做日志），然后返回给客户端更新成功，而将内存数据写入磁盘的过程称为 flush。

redo log 写满
- 对应场景：类似咸亨酒店掌柜粉板记满赊账记录。
- 原理：此时系统停止更新操作，推进 checkpoint，将 checkpoint 位置修改前的日志对应的脏页 flush 到磁盘，为 redo log 腾出空间。若不及时处理，更新操作将被阻塞，更新数会跌为 0。
内存不足
- 对应场景：掌柜记忆有限，事情太多时需将账先记入账本。
- 原理：InnoDB 用缓冲池管理内存，缓冲池内存页有未使用、干净页、脏页三种状态。长时间运行的库未使用页面少，当需要新内存页而内存不够时，淘汰最久不使用的数据页，若为脏页则先刷到磁盘。若一个查询要淘汰的脏页个数过多，会导致查询响应时间变长。
MySQL 认为系统 “空闲” 时
- 对应场景：生意不忙或打烊后掌柜更新账本。
- 原理：MySQL 会利用空闲时间刷脏页，但此场景对性能影响相对较小，因为系统此时压力不大。
MySQL 正常关闭时
- 对应场景：年底酒店关门结清账目。
- 原理：MySQL 正常关闭时会将内存脏页 flush 到磁盘，以便下次启动时能快速从磁盘读数据，此场景下性能影响可忽略不计。

该参数用于告知 InnoDB 所在主机的 IO 能力，建议设置为磁盘的 IOPS（可通过 fio 工具测试）。若设置不当，如某公司案例中 SSD 磁盘但 innodb_io_capacity 设置为 300，会导致 InnoDB 刷脏页过慢，脏页累积，影响查询和更新性能。

计算参考因素
- 脏页比例：参数 innodb_max_dirty_pages_pct 默认值 75% 为脏页比例上限。InnoDB 根据当前脏页比例（假设为 M）算出 0 - 100 之间数字，伪代码为若 M>=innodb_max_dirty_pages_pct 则返回 100，否则返回 100*M/innodb_max_dirty_pages_pct。
- redo log 写盘速度：InnoDB 每次写入日志有序号，当前写入序号与 checkpoint 对应序号差值假设为 N，根据 N 算出 0 - 100 之间数字（F2 (N) 算法较复杂，N 越大值越大）。
计算刷盘速度
- 取上述根据脏页比例和 redo log 写盘速度算出的两个数字（F1 (M) 和 F2 (N)）中较大值记为 R，引擎按 innodb_io_capacity 定义能力乘以 R% 控制刷脏页速度。

该参数控制刷脏页时是否连带 “邻居” 脏页一起刷。值为 1 时，准备刷一个脏页时，若旁边数据页是脏页会一起刷，逻辑可蔓延；值为 0 时不找邻居，自己刷自己。在机械硬盘时代，找 “邻居” 可减少随机 IO 提升性能，但使用 SSD 等 IOPS 高的设备时，建议设为 0，可减少 SQL 语句响应时间。MySQL 8.0 中默认值已为 0。

MySQL “抖” 一下的原因主要是刷脏页操作，其在 redo log 写满、内存不足、系统空闲、正常关闭等场景下触发，其中前两种场景可能影响性能。
应对策略包括合理设置 innodb_io_capacity 参数，关注脏页比例（通过 Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total 获取）避免接近 75%，根据存储设备类型合理设置 innodb_flush_neighbors 参数。通过这些方法可优化 MySQL 性能，减少 “抖动” 现象对业务的影响。