MySQL / 技术文章

MySQL 查询突然变慢如何定位

当同一条 MySQL 查询在没有明显业务改写的情况下从稳定延迟突然变成秒级,最危险的动作是立刻加索引、强制索引或重启实例。面向负责线上故障处理的后端工程师、SRE 与 DBA,这套排查路径先固定现场证据,再区分“执行计划回退”和“执行环境变慢”,最终把根因收敛到可验证、可回退的修复动作。

先保留现场,不要先改 SQL

“突然变慢”通常来自三层变化:SQL 的访问路径变了,数据与元数据让优化器的成本判断变了,或者执行计划没变但锁、I/O、缓存与并发环境恶化了。第一轮采集的目标不是解释原因,而是建立一个不会被后续操作覆盖的时间点。

先记录实例、版本、当前活动会话和高成本 SQL 摘要:

sql
SELECT NOW() AS captured_at,
       @@hostname AS host,
       @@version AS mysql_version;

SHOW FULL PROCESSLIST;

SELECT DIGEST,
       DIGEST_TEXT,
       COUNT_STAR,
       ROUND(AVG_TIMER_WAIT / 1000000000, 2) AS avg_ms,
       SUM_ROWS_EXAMINED,
       SUM_ROWS_SENT,
       FIRST_SEEN,
       LAST_SEEN
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

预期结果是得到采集时间、实例身份、活跃线程以及当前累计成本最高的一组 SQL digest。Performance Schema 的 digest 汇总是累计视图,不等于历史基线;判断“今天比昨天慢”仍应结合慢日志、监控快照或事故前保存的执行计划。

同时固定真正需要比较的输入:SQL digest、数据库、关键参数值、事务隔离级别、会话级优化器开关以及执行时段。不要拿“同一模板但参数选择性完全不同”的两次执行直接比较,否则很容易把数据分布差异误判成计划回退。

把基线计划与当前计划按同一口径对齐

如果有事故前的 EXPLAIN、EXPLAIN ANALYZE 或慢日志样本,先找同一 SQL digest、同一关键参数范围的记录;没有历史计划时,也要先把当前计划保存下来,再做任何统计信息刷新、DDL 或 hint 调整。

对只读查询,在确认执行成本可控的环境中采集 TREE 与运行时信息:

sql
EXPLAIN FORMAT=TREE
SELECT id, customer_id, created_at
FROM orders
WHERE tenant_id = 42
  AND status = 'PAID'
  AND created_at >= '2026-09-01 00:00:00'
ORDER BY created_at DESC
LIMIT 100;

EXPLAIN ANALYZE
SELECT id, customer_id, created_at
FROM orders
WHERE tenant_id = 42
  AND status = 'PAID'
  AND created_at >= '2026-09-01 00:00:00'
ORDER BY created_at DESC
LIMIT 100;

EXPLAIN ANALYZE 会实际执行语句,线上使用前要确认查询是只读、边界受控且不会放大故障。对比时不要只看 key,至少记录四类差异:访问路径和 JOIN 顺序、估算 rows、实际 rows 与 loops、排序或临时表等额外工作。

对比项 基线与当前差异 优先怀疑
访问路径 ref/range 变成大范围扫描或全表扫描 索引可用性、成本估算、统计信息
JOIN 顺序 小结果集不再作为早期驱动 基数估算失真、数据分布变化
估算与实际行数 当前偏差明显放大 统计信息陈旧、倾斜数据、相关列
计划结构相同 算子实际耗时整体升高 锁等待、I/O、缓存、CPU 或并发

先回答关键分叉:计划变了,还是计划没变

这是整个事故中最重要的分叉。计划已经改变时,重点调查优化器为什么改变选择;计划没有改变时,继续反复研究索引通常收益很低,应转向等待与资源环境。

如果计划结构基本一致但延迟显著升高,先查阻塞和元数据锁:

sql
SELECT *
FROM performance_schema.data_lock_waits
LIMIT 20;

SELECT OBJECT_TYPE,
       OBJECT_SCHEMA,
       OBJECT_NAME,
       LOCK_TYPE,
       LOCK_DURATION,
       LOCK_STATUS,
       OWNER_THREAD_ID
FROM performance_schema.metadata_locks
WHERE LOCK_STATUS = 'PENDING';

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

锁等待表存在记录或元数据锁长期处于 PENDING,说明查询可能不是“跑得慢”,而是在等待别的事务或 DDL。Buffer Pool 指标只能看变化率,不能用单次快照直接判断缓存命中恶化;应与事故前后同长度时间窗口的 I/O、CPU、连接数、运行队列和存储延迟一起比较。

如果只在少数参数值上变慢,而其他参数仍稳定,优先检查选择性和数据倾斜。MySQL 故障排查时要把“同一 SQL 文本”和“同一数据访问规模”分开看:模板一样,不代表执行代价应该一样。

计划变更时沿四类变化源收敛

第一类是结构变化。检查表定义、索引是否被删除、改为不可见、列类型或排序规则是否改变,以及近期 DDL 是否与故障时间重合:

sql
SHOW CREATE TABLE orders;
SHOW INDEX FROM orders;

SELECT @@optimizer_switch AS optimizer_switch,
       @@sql_mode AS sql_mode,
       @@collation_connection AS collation_connection;

第二类是统计信息与数据分布变化。表数据量快速增长、热点租户扩张、状态值高度倾斜,都可能让旧的选择性假设失效。如果 EXPLAIN ANALYZE 中估算行数与实际行数的偏差明显扩大,先把偏差作为证据保存,再在受控环境验证 ANALYZE TABLE、直方图或索引调整是否能恢复合理计划,不要一上来对整库刷新统计信息。

第三类是优化器环境变化。实例升级、optimizer_switch、会话变量、hint、不可见索引和连接排序约束都可能改变候选路径。把这些变量与基线实例逐项 diff,比只盯着 SQL 文本更容易找到“为什么今天选了另一条路”。

第四类是运行数据本身变化。若计划变化只对应某一时间段、某一租户或某一状态值,优先验证条件列的基数和相关性,而不是把索引是否存在当成唯一判断标准。必要时在只读副本或隔离环境使用 optimizer_trace 解释候选路径成本,避免在故障实例上反复做会改变状态的试验。

修复顺序从止血到根因,每一步都要可回退

止血动作应直接对应已经确认的证据。锁等待导致的延迟,应先处理阻塞链和长事务;统计信息失真,应先验证局部刷新是否纠正估算;索引被误删或变为不可用,应恢复原有访问能力;确认为成本模型在特定数据分布下选错计划时,再评估 SQL 改写、联合索引或临时 optimizer hint。

有三类操作不适合作为第一反应:无证据地 FORCE INDEX、为了“清缓存”直接重启实例、对大量表同时执行 ANALYZE TABLE。这些动作都可能让原始现场消失,并引入新的性能波动。

修复后,用同一 SQL digest 和同一关键参数再次验证,不要只看“能不能执行”:

sql
SELECT DIGEST,
       COUNT_STAR,
       ROUND(AVG_TIMER_WAIT / 1000000000, 2) AS avg_ms,
       SUM_ROWS_EXAMINED,
       SUM_ROWS_SENT,
       SUM_CREATED_TMP_DISK_TABLES,
       LAST_SEEN
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST = '<DIGEST>';

预期结果是修复后的延迟、扫描行数、返回行数和磁盘临时表指标回到可接受区间,并且新的 EXPLAIN ANALYZE 不再出现异常的扫描放大或循环放大。若只是平均耗时下降而 rows_examined、loops 或等待时间仍异常,说明症状缓解但根因未闭环。

把一次故障沉淀成可复查的证据包

事故结束前,至少保存 SQL digest、关键参数、事故时间窗、基线计划、故障计划、表与索引定义、统计信息或数据分布变化、锁与资源证据、采取的修复、验证指标以及回退条件。这样下一次相同 digest 再次变慢时,可以直接做差异比较,而不是从“是不是索引有问题”重新猜测。

最终结论应能写成一条可审计的因果链,而不是“优化后变快了”:

text
根因类型:<PLAN_REGRESSION_OR_RUNTIME_WAIT>
触发变化:<CHANGE>
关键证据:<EVIDENCE>
止血动作:<MITIGATION>
根因修复:<FIX>
验证结果:<VALIDATION>
回退条件:<ROLLBACK_CONDITION>

当这条因果链能被另一名工程师依据同样证据复现,才算完成一次线上查询突变的排查闭环。

目录与阅读设置

记录一个想法

MySQL 查询突然变慢如何定位

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通