MySQL 偶发查询慢（30 秒级 vs 毫秒级）全维度排查与优化实战

引言

在 MySQL 运维中，"同一条 SQL 有时 30 多秒、有时几毫秒"是极具迷惑性的一类问题。它区别于"持续慢"——持续慢通常意味着执行计划本身劣化（缺索引、全表扫描），而偶发抖动的本质是：执行计划可能根本没变，变的是执行期的运行状态。

一句话概括核心认知：

执行计划只固定算子形态，实际的行数、访问页数、锁冲突、IO 等待和刷盘时机，全部由运行期状态决定。

因此，偶发慢的排查不能只盯着 SQL 本身，而要沿"等待类 → 状态类 → 后台干扰类 → 内存配置 → 磁盘 IO"逐层展开。本文汇总所有可能根因，给出可落地的检查命令、优化方向与注意事项，并全程标注 MySQL 5.7 / 8.0 / 9.x 的版本差异。

第一部分：先建立正确的排查观

1.1 两类问题的区分

特征	持续慢	偶发抖动
执行计划	通常已劣化	可能完全没变
主因	缺索引、全表扫描、SQL 写法差	运行期状态变化
排查重点	执行计划与索引	等待事件、锁、内存、IO

1.2 抖动源的三大类别

类别	具体来源	观察入口
等待类	行锁争用、MDL 元数据锁、网络延迟	`data_lock_waits`、`metadata_locks`、`SHOW ENGINE INNODB STATUS`
状态类	缓冲池 miss、AHI 自适应哈希失效、页分裂	缓冲池命中率、BUFFER POOL 段
后台干扰类	脏页刷盘、purge 清理、redo/binlog fsync、统计信息更新	脏页占比、history list、`log_waits`

在此基础上，还有两个常被忽略但极其高频的根因：内存配置不合理和磁盘 IO 瓶颈。它们往往互相放大，必须单独成章。

第二部分：跨版本能力总览（先对号入座）

排查偶发抖动主要依赖 performance_schema（PFS）、information_schema、sys schema 与 SHOW ENGINE INNODB STATUS。三版本差异如下：

能力项	MySQL 5.7	MySQL 8.0	MySQL 9.x
`performance_schema` 默认	默认 OFF，需显式开启	默认 ON	默认 ON
锁等待视图	`information_schema.innodb_locks` / `innodb_lock_waits`	`performance_schema.data_locks` / `data_lock_waits`	同 8.0
`sys.innodb_lock_waits`	支持	支持	支持
`events_statements_summary_by_digest`	支持	支持	支持
`events_statements_history(_long)`	支持（默认关）	支持（默认关）	支持（默认关）
`metadata_locks`	支持	支持	支持
`events_transactions_current`	支持	支持	支持
`EXPLAIN ANALYZE`	不支持	8.0.18+ 支持	支持
列直方图 `UPDATE HISTOGRAM`	不支持	支持	支持
Query Cache	存在（且是抖动源）	已移除	已移除
`innodb_dedicated_server`	不存在	支持	支持
`innodb_redo_log_capacity`	无	8.0.30+	支持
`SHOW ENGINE INNODB STATUS`	支持	支持	支持

⚠️ 最重要的一条：5.7 与 8.0/9.x 的锁视图入口不同，用错会直接报"表不存在"。

第三部分：慢查询的定位入口（三版本通用）

3.1 开启并读取慢日志

SET GLOBAL slow_query_log = ON;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 1;

SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'log_output';

-- log_output=TABLE 时可直接查表
SELECT start_time, query_time, lock_time, rows_examined, rows_sent, sql_text
FROM mysql.slow_log ORDER BY query_time DESC LIMIT 20;

关键认知：慢查询是在语句结束阶段才判定的（THD::update_slow_query_status 判断 执行耗时 > long_query_time 后打标志，再写入慢日志）。因此，卡在锁等待/MDL 中的语句，要等它结束才会出现在慢日志里。

重点看：lock_time 与 query_time 的比例。lock_time 高说明时间花在等锁，此时优化索引无效，应先解决锁争用/长事务。

3.2 高频慢 SQL 聚合

SELECT SCHEMA_NAME, DIGEST_TEXT, COUNT_STAR,
       SUM_TIMER_WAIT, AVG_TIMER_WAIT, SUM_ROWS_EXAMINED
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC LIMIT 20;

SELECT * FROM sys.statement_analysis ORDER BY total_latency DESC LIMIT 20;

慢日志按"频率 × 耗时"排序可优先处理影响最大的 SQL；PFS digest 聚合可发现单次不快但频繁执行的"小慢 SQL"。

3.3 等待事件分解

SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT, AVG_TIMER_WAIT
FROM performance_schema.events_waits_summary_global_by_event_name
ORDER BY SUM_TIMER_WAIT DESC LIMIT 20;

SELECT * FROM sys.waits_by_host_by_latency LIMIT 20;
SELECT * FROM sys.io_by_thread_by_latency LIMIT 20;

3.4 日志分析工具

mysqldumpslow -s at -t 10 /var/log/mysql/slow.log   # at=平均耗时, t=总耗时, c=次数
pt-query-digest /var/log/mysql/slow.log             # 指纹聚合 + 分位数 + 锁等待

第四部分：根因一——锁与阻塞

4.1 锁等待定位（★ 版本差异最大）

MySQL 8.0 / 9.x：

SELECT * FROM performance_schema.data_lock_waits;   -- 谁被谁阻塞
SELECT * FROM performance_schema.data_locks;
SELECT * FROM sys.innodb_lock_waits;                -- 格式化等待链
SELECT * FROM performance_schema.metadata_locks;    -- MDL
SELECT * FROM performance_schema.table_lock_waits;

MySQL 5.7：

SELECT * FROM information_schema.innodb_locks;       -- 5.7 专有
SELECT * FROM information_schema.innodb_lock_waits;  -- 5.7 专有
SELECT * FROM sys.innodb_lock_waits;                 -- 跨版本通用
SELECT * FROM performance_schema.metadata_locks;     -- 三版本通用

4.2 长事务与当前会话（三版本通用）

SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id, trx_query
FROM information_schema.innodb_trx ORDER BY trx_started;

SELECT * FROM performance_schema.events_transactions_current;

SELECT id, user, db, command, time, state, info
FROM information_schema.processlist ORDER BY time DESC;

4.3 机制说明

lock_time 的构成：THD::inc_lock_usec 累计表锁与 InnoDB 数据锁等待时间，通过 get_lock_usec() 提供给慢日志。lock_time 高 = 语句主要在等锁。
MDL 元数据锁：访问表自动加 MDL 读锁，结构变更加 MDL 写锁。会话出现 Waiting for table metadata lock 即被 MDL 阻塞；可设置 lock_wait_timeout 限制等待，并尽量使用短事务。
锁等待超时：innodb_lock_wait_timeout 默认 50 秒，超时后回滚并报错误码 1205（Lock wait timeout exceeded）。对 OLTP 偏长，业务侧通常建议 10–30 秒并配合快速失败重试。
系统内部事务不可 KILL：innodb_trx 中 trx_mysql_thread_id=0 且 trx_query 为 NULL 的是 purge/崩溃恢复等后台事务，KILL 无效，应等待自然结束。

4.4 相关故障模式

故障模式（别名）	核心特征
锁等待超时故障(1205)	等待超 `innodb_lock_wait_timeout` 后报 1205
DDL 被阻塞引发表级元数据锁等待风暴	一个会话等待 MDL 后，该表后续操作连锁阻塞
mysql_long_transaction（长事务）	长时间持锁 + 阻塞 purge
innodb_long_trx_blocking_purge	事务开启超 1 小时，undo 膨胀、查询变慢
系统内部事务无法通过 KILL 终止	`trx_mysql_thread_id=0`、`trx_query` 为 NULL

第五部分：根因二——执行计划漂移与统计信息

5.1 执行计划检查

MySQL 8.0.18+ / 9.x：

EXPLAIN FORMAT=JSON <SQL>;
EXPLAIN ANALYZE <SQL>;    -- 实测执行，看真实行数与耗时

MySQL 5.7（无 EXPLAIN ANALYZE）：

EXPLAIN FORMAT=JSON <SQL>;

-- 用优化器追踪看估算 vs 实际
SET optimizer_trace = 'enabled=on';
<SQL>;
SELECT * FROM information_schema.optimizer_trace\G
SET optimizer_trace = 'enabled=off';

关键认知：EXPLAIN 只能反映当前计划，无法与历史计划对比。若需回溯执行计划变化，可查 performance_schema.events_statements_history（默认可能未开启，排障前建议预开）并结合 sys.statement_analysis。对关键 SQL 建议定期 EXPLAIN FORMAT=JSON 并存档，便于性能变化时对比。

5.2 统计信息检查

SHOW INDEX FROM <表名>;          -- Cardinality 列反映索引区分度
SHOW VARIABLES LIKE 'innodb_stats%';
ANALYZE TABLE <表名>;            -- 重新收集统计信息

MySQL 8.0+ 列直方图（5.7 无）：

ANALYZE TABLE <表名> UPDATE HISTOGRAM ON <列名>;
SELECT * FROM information_schema.column_statistics;

5.3 机制说明

MySQL 基于成本（CBO）选择执行计划，统计信息不准或数据倾斜时估算会偏差，导致偶发选错索引或 Join 顺序。
InnoDB 采样由 innodb_stats_transient_sample_pages 等控制，采样不足会估算失真。
直方图（8.0+）改善非均匀列值分布下的选择率估算，影响 Join 顺序和 range 访问代价。

5.4 相关故障模式

故障模式（别名）	核心特征
统计信息不足导致多表 join 优化器选错 Join order	无统计列 rows 被高估，cost 飙升
同 SQL 同数据同计划执行时间抖动	计划不变但耗时抖动

第六部分：根因三——内存配置不合理（高频）

内存给少了，缓冲池命中率下降，物理 IO 暴增；给多了，实例 OOM、启动失败。内存配置是偶发慢最容易被忽略的根因之一。

6.1 内存检查入口

（1）系统层现状（三版本通用）

free -m
ps -eo pid,rss,comm --sort=-rss | head
top -o %MEM

（2）InnoDB 缓冲池参数与命中率

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'innodb_buffer_pool_instances';
SHOW VARIABLES LIKE 'innodb_dedicated_server';   -- 8.0+ 才存在

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';

SHOW ENGINE INNODB STATUS\G

命中率估算：命中率 ≈ 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)。若 Buffer pool hit rate 低于 990（即 <99%），说明大量物理读，需检查缓冲池容量、负载类型与 AHI 是否生效。

（3）连接与 PFS 内存放大

SHOW VARIABLES LIKE 'max_connections';
SHOW ENGINE PERFORMANCE_SCHEMA STATUS;
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Max_used_connections';

SELECT * FROM sys.memory_global_total;
SELECT * FROM sys.memory_by_thread_by_current_bytes ORDER BY current_allocated DESC LIMIT 10;

（4）执行器内存参数

SHOW VARIABLES LIKE 'sort_buffer_size';
SHOW VARIABLES LIKE 'join_buffer_size';
SHOW VARIABLES LIKE 'tmp_table_size';
SHOW VARIABLES LIKE 'max_heap_table_size';
SHOW VARIABLES LIKE 'innodb_log_buffer_size';
SHOW VARIABLES LIKE 'key_buffer_size';       -- MyISAM 场景
SHOW VARIABLES LIKE 'binlog_cache_size';

SHOW GLOBAL STATUS LIKE 'Created_tmp%';
SHOW GLOBAL STATUS LIKE 'Sort%';

SHOW PROCESSLIST 中频繁出现 copy to tmp table / Sorting result，说明排序或临时表内存不足，正在落盘。

6.2 内存优化方向

参数	优化方向	版本差异
`innodb_buffer_pool_size`	专用库一般设物理内存 50%~75%；混部需留出 OS 与其他进程内存	三版本通用；默认 128MB 偏小
`innodb_buffer_pool_instances`	大缓冲池拆多实例降争用；instances>1 时 size 不得 <1GiB	较新版本默认按 CPU 核数动态设置
`innodb_dedicated_server`	专用物理机可开自动配置；容器/混部应显式 OFF 手工配置	仅 8.0+
`innodb_log_buffer_size`	`Innodb_log_waits` 突增时调大	三版本通用
`sort_buffer_size`	仅当排序确实落盘时调大才有效；全内存排序调大零收益	三版本通用，默认 256KB 偏小
`join_buffer_size`	8.0 起兼作 hash join 内存预算且可 spill；无索引关联适当调大	5.7 无 hash join
`tmp_table_size` + `max_heap_table_size`	内存临时表上限 = `min(两者)`，只调一个无效	8.0.28 起 `tmp_table_size` 才对 TempTable 每表限额生效
`max_connections`	连接数过大会线性放大 PFS 内存，需结合内存评估	三版本通用
`performance_schema_max_*`	连接数高时显式指定，绕过 PFS 自动定容的内存放大	三版本通用

自动配置规则：开启 innodb_dedicated_server 时，缓冲池按内存自动设置——<1GB 设 128MB；1GB~4GB 设为内存的 50%；>4GB 设为内存的 75%。

6.3 内存相关故障模式

故障模式（别名）	核心特征
innodb_buffer_pool_low_hit（缓冲池命中率低）	缓冲池不足或 SQL 效率低，高并发 OLTP 频繁磁盘 IO
执行器内存失控：join/sort_buffer_size 与 hash join spill	hash join 内存飙升、磁盘临时文件暴涨
tmp_table_size 膨胀	最大临时表超 5GB，监控异常、响应变慢
max_connections 调大导致 PFS 内存放大	RSS 立即上升且不回落，OOM 风险
MySQL 实例 OOM 风险	`innodb_dedicated_server` 自动分配 75% 导致超配 + PFS 随连接数放大
缓冲池内存不足导致 InnoDB 启动失败	日志报 `Cannot allocate memory for the buffer pool`
L1：内存不足或内存参数过大导致启动失败	启动失败或被 OOM killer 杀掉
1206 ER_LOCK_TABLE_FULL（锁表满）	单事务锁数超上限，锁内存上限与 `innodb_buffer_pool_size` 联动

第七部分：根因四——磁盘 IO 瓶颈（高频）

7.1 IO 检查入口

（1）系统层 IO 观测（三版本通用）

iostat -x 1 5            # 看 %util、await、aqu-sz（队列深度）
iotop -o                 # 看哪个进程在大量读写
sar -d 1 5
pidstat -d 1

重点看：%util 接近 100%、await 显著升高、aqu-sz 队列积压 → IO 子系统饱和。

（2）区分调度器还是硬件瓶颈

blktrace -d /dev/sdX -o - | blkparse -i -
btt -i <blktrace输出>

I2D 阶段延迟高 → 瓶颈在 I/O 调度器；
D2C 阶段延迟高 → 瓶颈在驱动或存储硬件（磁盘、HBA 卡），是典型硬件 IO 瓶颈。

（3）数据库层 IO 观测

SELECT * FROM performance_schema.file_summary_by_event_name
ORDER BY SUM_TIMER_WAIT DESC LIMIT 20;

SELECT * FROM sys.io_global_by_file_by_latency LIMIT 20;
SELECT * FROM sys.io_by_thread_by_latency LIMIT 20;

SHOW GLOBAL STATUS LIKE 'Innodb_data_reads';
SHOW GLOBAL STATUS LIKE 'Innodb_data_writes';
SHOW GLOBAL STATUS LIKE 'Innodb_data_fsyncs';
SHOW GLOBAL STATUS LIKE 'Innodb_log_waits';         -- 突增说明 log buffer 满
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads'; -- 物理读

（4）现场段：脏页与刷盘

SHOW ENGINE INNODB STATUS\G

重点看 BUFFER POOL AND MEMORY 段：Modified db pages（脏页占比，峰值超 70% 需警惕）、Pending writes 持续非零、Buffer pool hit rate；以及 TRANSACTIONS 段的 History list length。

（5）IO 相关参数核对

SHOW VARIABLES LIKE 'innodb_io_capacity%';
SHOW VARIABLES LIKE 'innodb_flush_method';
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';
SHOW VARIABLES LIKE 'sync_binlog';
SHOW VARIABLES LIKE 'innodb_max_dirty_pages_pct%';
SHOW VARIABLES LIKE 'innodb_adaptive_flushing%';
SHOW VARIABLES LIKE 'innodb_page_cleaners';
SHOW VARIABLES LIKE 'innodb_read_io_threads';
SHOW VARIABLES LIKE 'innodb_write_io_threads';
SHOW VARIABLES LIKE 'innodb_redo_log_capacity';   -- 8.0.30+
SHOW VARIABLES LIKE 'innodb_log_file_size';       -- 5.7 / 8.0.30 前
SHOW VARIABLES LIKE 'innodb_log_files_in_group';

7.2 IO 优化方向

参数	优化方向	版本差异
`innodb_io_capacity`	使后台刷脏速率与磁盘实际 IOPS 匹配。机械盘偏低、SSD 应调高	默认值随版本变化：5.7 常见默认 200；较新版本默认上调（新默认 10000 更适合 SSD）。以实际值为准
`innodb_io_capacity_max`	紧急时允许爆发的 IO 上限，通常设为 `io_capacity` 的 2 倍	三版本通用
`innodb_flush_method`	视存储类型选择；SAN/RAID 场景不同取值差异大	知识库实测 8.0/8.4 默认仍为 `fsync`（部分文档称默认 O_DIRECT 不实），以实际值为准
`innodb_flush_log_at_trx_commit`	最安全为 1；性能敏感可评估 0/2，但需接受崩溃丢窗口	三版本通用
`sync_binlog`	最安全为 1；=0 主库崩溃可能丢 binlog 导致主从不一致	三版本通用
`innodb_max_dirty_pages_pct`	写密集场景适当降低，配合自适应刷脏，避免集中刷盘抢 IO	三版本通用
`innodb_adaptive_flushing`	保持开启，让刷脏速率随负载自适应	三版本通用
`innodb_page_cleaners`	大缓冲池/多核场景适当增加刷脏线程	5.7.4+ 支持
`innodb_read_io_threads` / `innodb_write_io_threads`	视负载类型调整读写 IO 线程数	三版本通用
`innodb_log_buffer_size`	`Innodb_log_waits` 突增时调大	三版本通用
redo 容量	8.0.30+ 用 `innodb_redo_log_capacity`；5.7 / 8.0.30 前用 `innodb_log_file_size` + `innodb_log_files_in_group`，改大小需重启、不能在线调整	版本差异明显

7.3 IO 相关故障模式

故障模式（别名）	核心特征
写高峰后所有 SQL 集体变慢 10 分钟的刷盘 IO 故障	脏页峰值 >70%、`Pending writes` 持续非零、iostat 写队列满
写高峰后同 SQL 变慢的刷脏与 checkpoint 抖动	page cleaner 受 `innodb_io_capacity` 默认 200 节流，与业务 IO 抢带宽
InnoDB Buffer Pool 脏页刷新缓慢导致检查点阻塞	刷脏效率不足，写入密集负载下性能雪崩
MySQL 磁盘 IO 拥堵导致整体性能下降	多条 IO 路径同时饱和
Innodb_log_waits 突增	redo log buffer 被占满，用户线程等待写入 OS
大查询落盘与磁盘 IO 高归因临时表膨胀	排序/分组超出内存临时表容量，InnoDB 临时表落盘
RAID 控制器固件 BUG 导致磁盘 IO 短暂卡顿	blktrace 中多个 IO 的 D 时间不同、C 时间几乎相同，磁盘短暂无响应
崩溃恢复慢：Log checkpoint 落后	脏页刷盘跟不上，需重放更多 redo
sync_binlog=0 主库崩溃丢 binlog 导致主从不一致	binlog 尾部未落盘丢失

第八部分：内存与 IO 的联动分析

这是最关键的一环，很多误判都发生在这里：

先判断是"内存不足"还是"IO 不足"：缓冲池命中率低 + 物理读高 → 优先加内存/调大缓冲池；命中率正常但 await/%util 高 → 优先查 IO 能力与刷盘参数。
内存不足会伪装成 IO 问题：缓冲池太小导致物理 IO 暴增，此时调 innodb_io_capacity 只是治标，根因在内存。
IO 不足会伪装成内存问题：脏页刷不出去导致 free list 紧张，用户线程被迫同步刷页，表现为单查询偶发加长，根因在 IO 吞吐。
单变量调整 + P50/P99 对比：每次只改一个参数，观察改动前后分位数变化，避免"一次改多个变量无法归因"。

第九部分：InnoDB 现场四段（通用终极手段）

SHOW ENGINE INNODB STATUS\G

重点看四段：

TRANSACTIONS：---TRANSACTION <id>, ACTIVE <sec>，ACTIVE 越大事务越老；History list length 持续增长说明 purge 被阻塞、存在长事务；
BUFFER POOL AND MEMORY：Buffer pool hit rate、Free buffers、Modified db pages（脏页占比）；
锁等待 / LATEST DETECTED DEADLOCK：trx id ... lock wait ...；
row operations：回表与扫描量，判断行读取压力。

第十部分：全量故障模式速查表

故障模式（别名）	所属类别	核心特征
同 SQL 同数据同计划执行时间抖动	综合	计划不变但耗时抖动，九类抖动源
slow_query_too_more（慢查询过多）	综合	每秒慢查询数 >1 且占比超 20%
锁等待超时故障(1205)	锁	等待超 `innodb_lock_wait_timeout`
DDL 被阻塞引发表级元数据锁等待风暴	锁	MDL 连锁阻塞
mysql_long_transaction（长事务）	锁	长时间持锁 + 阻塞 purge
innodb_long_trx_blocking_purge	锁	事务开启超 1 小时，undo 膨胀
统计信息不足导致多表 join 优化器选错 Join order	计划	无统计列 rows 被高估
innodb_buffer_pool_low_hit（缓冲池命中率低）	内存	缓冲池不足，频繁物理 IO
执行器内存失控：join/sort_buffer_size 与 hash join spill	内存	内存飙升、临时文件暴涨
tmp_table_size 膨胀	内存	最大临时表超 5GB
max_connections 调大导致 PFS 内存放大	内存	RSS 上升且不回落
MySQL 实例 OOM 风险	内存	dedicated_server 自动 75% + PFS 放大
1206 ER_LOCK_TABLE_FULL（锁表满）	内存	锁内存不足
写高峰后所有 SQL 集体变慢 10 分钟的刷盘 IO 故障	IO	脏页峰值高、Pending writes 非零
InnoDB Buffer Pool 脏页刷新缓慢导致检查点阻塞	IO	刷脏不足，性能雪崩
MySQL 磁盘 IO 拥堵导致整体性能下降	IO	多条 IO 路径饱和
Innodb_log_waits 突增	IO	log buffer 满
大查询落盘与磁盘 IO 高归因临时表膨胀	IO	临时表落盘
RAID 控制器固件 BUG 导致磁盘 IO 短暂卡顿	IO	blktrace D/C 时间异常
wait_synch_query_cache（查询缓存锁等待）	5.7 专有	Query Cache 全局 mutex 瓶颈
sync_binlog=0 主库崩溃丢 binlog	IO/复制	主从不一致

第十一部分：注意事项汇总

先定版本再选命令：执行任何排查前先 SELECT VERSION();，5.7 与 8.0+ 的锁视图入口不同，用错会报"表不存在"。
抓现场要快：偶发问题的关键是问题发生时立即采集——慢日志、PFS 等待数据、SHOW ENGINE INNODB STATUS 快照三者同时保留。
lock_time 与 query_time 分开看：lock_time 高说明在等锁，优化索引无效。
慢日志可能漏记：慢查询在语句结束时才判定；slow_query_log=OFF、log_output=NONE 或文件无权限时会"计数增长但无内容"。
5.7 先开 PFS：否则 digest 聚合表为空，会误判为没有慢 SQL。
不要一次改多个变量：单变量调整并对比改动前后 P50/P99。
区分前台慢与后台干扰：计划未变却偶发慢，优先查刷脏、purge、fsync、统计信息更新等后台干扰源。
长事务优先治理：长事务既持锁又阻塞 purge，是偶发卡顿与 undo 膨胀的常见根因。
系统内部事务不可 KILL：trx_mysql_thread_id=0 且 trx_query 为 NULL 的是后台事务。
8.0/9.x 用直方图：无索引的高倾斜列可借助 UPDATE HISTOGRAM 改善估算；5.7 无此能力。
先定位再调参：内存与 IO 是两套根因，先区分清楚，不要盲目同时调缓冲池和 IO 参数。
缓冲池实例约束：innodb_buffer_pool_instances > 1 时，innodb_buffer_pool_size 不得小于 1GiB。
innodb_dedicated_server 慎用：>4GB 内存会按 75% 自动分配，混部/容器环境易超配，应显式设为 OFF。
PFS 内存放大：调大 max_connections 会触发 PFS 自动定容，内存线性上升且不回落，需显式指定 performance_schema_max_*。
临时表参数成对调：内存临时表上限 = min(tmp_table_size, max_heap_table_size)，只调一个无效。
排序内存调大未必有用：仅当排序确实落盘时调大 sort_buffer_size 才有效。
redo 日志大小不能在线改：5.7 / 8.0.30 前用 innodb_log_file_size 需重启；8.0.30+ 用 innodb_redo_log_capacity。
持久性参数成对评估：sync_binlog 与 innodb_flush_log_at_trx_commit 构成双 fsync 路径，降为 0 会牺牲崩溃一致性。
存储固件也可能是元凶：若 blktrace 显示 D 时间分散但 C 时间集中，需排查 RAID/HBA 固件 BUG。
参数默认值随版本变化：本文章中的默认值不能跨版本套用，务必查询实际值。

结语

MySQL 偶发慢的排查，本质上是一场"缩小范围"的过程：

先确认是不是同一个计划 → 再看时间花在哪个等待上 → 然后对照抖动源逐项归因 → 最后区分是内存、IO 还是锁 → 单变量缓解并对比 P50/P99。

按本文的顺序：慢日志定位 SQL → PFS 分解等待 → 锁/MDL 排查 → 执行计划与统计信息核对 → 内存配置核查 → 磁盘 IO 核查 → InnoDB 现场四段，基本可以覆盖 30 秒级 vs 毫秒级抖动的所有常见根因。

posted on 2026-10-08 16:29 刘子毅阅读(1) 评论(0) 收藏举报

刷新页面返回顶部

liuziyi