慢 SQL 定位与执行计划

慢 SQL 是绝大多数 MySQL 故障的源头，但“找出慢 SQL”和“知道它为什么慢”是两件事。前者靠日志与统计表排序，后者只能靠执行计划。本章按“先汇总找指纹、再单条看计划、最后按原因分类处置”的顺序展开，并给出 EXPLAIN 各列的判读标准。

定位思路

用慢日志或 events_statements_summary_by_digest 排序，找出累计耗时最大的 SQL 指纹。
取指纹对应的常量参数（DIGEST_TEXT 是带 ? 的模板，必须拿真实参数复现）。
对真实语句执行 EXPLAIN，必要时 8.0 用 EXPLAIN ANALYZE 看实际行数。
对照“常见慢因”清单定位类型：缺索引、索引失效、深分页、大事务等。
小流量验证后再全量发布，关键变更先在从库或影子环境观察一轮。

只按“单次最慢”排序会误导：一条偶尔慢 10 秒的报表 SQL，危害往往小于每秒执行 2000 次、每次 50 毫秒的核心接口 SQL。

第一步：慢查询日志

-- 5.7 / 8.0 通用；8.0 中 log_output 可用 TABLE，5.7 建议 FILE
SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'slow_query_log_file';
SHOW VARIABLES LIKE 'log_output';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';
SHOW VARIABLES LIKE 'log_throttle_queries_not_using_indexes';

-- 动态开启，无需重启（写进配置文件才能持久化）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;
SET GLOBAL log_queries_not_using_indexes = ON;
SET GLOBAL log_throttle_queries_not_using_indexes = 50;

log_queries_not_using_indexes 是双刃剑：它会把大量小表全扫也记进日志，日志可能一天涨几十 GB。线上建议配合 log_throttle_queries_not_using_indexes（5.7.1 起支持）限流，或者干脆关掉它、只依赖 long_query_time。

# 汇总慢日志 TOP SQL；pt-query-digest 输出最完整，优先用它
pt-query-digest --limit 20 --order-by Query_time:sum /var/lib/mysql/slow.log

# 没有 Percona Toolkit 时用自带工具按总耗时排序
mysqldumpslow -s t -t 20 /var/lib/mysql/slow.log   # -s c 改为按出现次数排序

第二步：从 performance_schema 找当前最耗时的指纹

慢日志是历史数据，events_statements_summary_by_digest 是运行期累计，适合故障当下应急。

-- 5.7 与 8.0 通用
SELECT DIGEST_TEXT, COUNT_STAR,
       ROUND(SUM_TIMER_WAIT/1e12, 2)  AS total_sec,
       ROUND(AVG_TIMER_WAIT/1e9, 2)   AS avg_ms,
       ROUND(MAX_TIMER_WAIT/1e9, 2)   AS max_ms,
       SUM_ROWS_EXAMINED, SUM_ROWS_SENT,
       SUM_NO_INDEX_USED, FIRST_SEEN, LAST_SEEN
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;

再看“正在执行的语句”，它能给出 SQL 卡在哪一步：

SELECT thread_id, event_name, timer_wait/1e9 AS wait_ms, sql_text
FROM performance_schema.events_statements_current
WHERE sql_text IS NOT NULL
ORDER BY timer_wait DESC
LIMIT 10;

AVG_TIMER_WAIT 与 COUNT_STAR 相乘就是总代价。排序时用 SUM_TIMER_WAIT，不要用平均值，平均值会漏掉“高频小慢”的典型杀手。

第三步：EXPLAIN 各列判读

EXPLAIN SELECT id, user_id, amount FROM orders
WHERE user_id = 10086 AND status = 1
ORDER BY created_at DESC
LIMIT 20;

-- 8.0 独有：真正执行并给出实际行数与耗时，可用 FORMAT=TREE 看优化器改写结果
EXPLAIN ANALYZE SELECT ... ;

列	关注点	好的表现	坏的表现
type	访问类型档次	`const`、`eq_ref`、`ref`、`range`	`ALL`（全表扫描）、`index`（全索引扫描）、大表上的 `index_merge`
key	实际使用的索引	命中预期索引	`NULL` 表示没走索引
possible_keys	候选索引	包含目标索引	候选为空说明根本没有可用索引
rows	估算扫描行数	与结果集量级接近	远大于结果集，说明条件区分度差或统计信息过期
filtered	条件过滤后剩余比例	接近 100%	很低说明大量行被回表后才过滤
Extra	额外操作	`Using index`（覆盖索引）	`Using filesort`、`Using temporary`、`Using join buffer`

type 的档次从好到坏：system > const > eq_ref > ref > range > index > ALL。线上核心接口的驱动表出现在 ALL 或 rows 上万，基本就锁定了问题。Extra 里 Using index condition 表示用上了索引下推（ICP），是好事；Using filesort 只是“需要额外排序”，小结果集排序可以接受，几十万行的 filesort 必须治理。

常见慢因与对策

慢因	典型特征	对策
缺索引	`type=ALL`、`key=NULL`、`SUM_NO_INDEX_USED` 增长	按等值条件+排序列建联合索引
索引失效（函数包裹列）	`WHERE DATE(created_at)=...` 导致 `key=NULL`	改写为范围查询 `created_at >= ? AND created_at < ?`
隐式类型转换	字符串列用数字比较，`key` 有值但 `rows` 很大	参数类型与列类型严格一致
违反最左前缀	联合索引只有后段列参与条件	调整索引列顺序或补建索引
深分页	`LIMIT 1000000, 20` 扫描量巨大	改用“上次最大 ID”游标式分页
大事务	一次更新几十万行、长时间持锁	分批 `LIMIT` 循环提交
统计信息过期	`rows` 估算与实际差几个数量级	`ANALYZE TABLE`，或调大 `innodb_stats_persistent_sample_pages`
回表过多	`rows` 小但 `filtered` 低、随机 IO 重	建覆盖索引，把查询列加进索引

深分页的改写示例：

-- 反例：扫描并丢弃 100 万行
SELECT id, title FROM articles ORDER BY id LIMIT 1000000, 20;

-- 正例：用上次返回的最大 id 做游标
SELECT id, title FROM articles WHERE id > 1000000 ORDER BY id LIMIT 20;

处置动作

加索引：优先在从库或影子环境验证 EXPLAIN 与耗时，再上主库。
线上加索引必须走 Online DDL（8.0 多数加二级索引支持 ALGORITHM=INPLACE）或 gh-ost，不要在业务高峰执行。
联合索引注意区分度：把等值条件列放前面、范围与排序列放后面，量大的表用 cardinality 估一下列区分度。
改 SQL 前先 EXPLAIN，改完对比 rows 与真实耗时；有从库的先在从库跑一遍。
无法立刻改索引的，先加限流或缓存把 SQL 调用量降下来止血。

-- 加索引的标准动作，5.7/8.0 都支持 ONLINE 关键字
ALTER TABLE orders ADD INDEX idx_user_status_created (user_id, status, created_at),
  ALGORITHM=INPLACE, LOCK=NONE;

预防与巡检项

long_query_time 固定为 0.5~1 秒并长期开启慢日志，按周出一份 TOP SQL 报告。
对 SUM_NO_INDEX_USED 持续增长的指纹建立告警，比等用户投诉早得多。
核心接口的 SQL 纳入代码评审，DDL 变更与 SQL 变更一起评审执行计划。
定期 ANALYZE TABLE 维护大表统计信息，尤其是数据倾斜明显的状态列。
报表类大查询迁到专用从库，禁止在主库上跑聚合与导出。

小结：慢 SQL 的定位分为汇总与判读两步，先用慢日志或 digest 表按累计耗时排序锁定指纹，再用真实参数跑 EXPLAIN；判读时看 type 档次、key 是否命中、rows 与 filtered 的偏差、Extra 里的 filesort 与 temporary，按缺索引、索引失效、深分页、大事务四类原因对症处置，任何线上索引与 SQL 变更都要先 EXPLAIN 验证再用 Online DDL 低峰执行。

笔记加载中…

标签：#故障排查 #运维

返回知识库