MySQL 慢查询排查实战：slow log、EXPLAIN 与索引失效的常见原因

背景

线上接口响应变慢、数据库 CPU 升高时，多数情况是少数几条 SQL 拖垮了整体。本文给出一套可复现的排查流程：开启慢日志、定位最耗时的语句、用 EXPLAIN 分析执行计划，再针对索引失效的典型场景做修复。命令基于 MySQL 5.7/8.0，具体参数以所用版本官方文档为准。

1. 开启并查看慢查询日志

先确认当前配置：

SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';

动态开启（重启后失效，持久化需写入 my.cnf）：

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';

注意：long_query_time 对新建连接生效，已有连接需要重连。log_queries_not_using_indexes 在小表上会产生大量噪音，排查结束后建议关闭。云数据库一般在控制台的参数设置和日志管理中配置，入口以控制台为准。

2. 汇总最耗时的语句

慢日志直接看很难找重点，用 mysqldumpslow 按总耗时排序：

# -s t 按总耗时排序，-t 10 取前 10 条
mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log

如果安装了 Percona Toolkit，pt-query-digest 能给出更完整的统计：

pt-query-digest /var/lib/mysql/slow.log | less

重点关注三个指标：总耗时占比、执行次数、扫描行数与返回行数之比（Rows_examined / Rows_sent）。比值很大，说明在扫描大量无用数据。

3. 用 EXPLAIN 看执行计划

EXPLAIN SELECT * FROM orders
WHERE user_id = 1001 AND status = 'PAID'
ORDER BY created_at DESC LIMIT 20;

关键列的含义：

• type：访问类型，从好到差大致为 const、eq_ref、ref、range、index、ALL。出现 ALL 即全表扫描。
• key：实际使用的索引，为 NULL 表示没走索引。
• rows：预估扫描行数。
• Extra：出现 Using filesort、Using temporary 需要警惕；Using index 表示覆盖索引，是好信号。

MySQL 8.0.18 起可用 EXPLAIN ANALYZE 获取真实执行耗时，但它会真正执行语句，生产环境对写入类语句不要使用。

4. 索引失效的常见原因

• 对索引列做函数或计算：WHERE DATE(created_at) = '2026-10-01' 无法使用 created_at 索引，应改写为范围条件：

WHERE created_at >= '2026-10-01' AND created_at < '2026-10-02'

• 隐式类型转换：phone 为 varchar，却写 WHERE phone = 13800000000，会发生转换导致索引失效，应加引号。
• 联合索引不满足最左前缀：索引为 (user_id, status, created_at)，只用 status 查询无法命中。
• LIKE 以通配符开头：LIKE '%abc' 无法使用 B+ 树索引。
• 深分页：LIMIT 1000000, 20 需要先扫描再丢弃前一百万行，可改为基于上次主键游标的查询：

SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 20;

5. 建索引与验证

针对前面的例子，建立覆盖过滤和排序的联合索引：

ALTER TABLE orders ADD INDEX idx_user_status_created (user_id, status, created_at);

再次 EXPLAIN，确认 key 命中新索引且 Using filesort 消失。大表加索引前建议：

• 在业务低峰操作，MySQL 5.6+ 的 Online DDL 通常不阻塞读写，但仍会占用 IO 和产生复制延迟。
• 先在测试库用相近数据量验证收益。
• 不要为每条 SQL 都建索引，索引会增加写入开销和存储。

6. 排查清单

1. 开慢日志，设置合理的 long_query_time。
2. 用 mysqldumpslow 或 pt-query-digest 找出总耗时最高的语句。
3. EXPLAIN 检查 type、key、rows、Extra。
4. 对照索引失效清单改写 SQL 或补充索引。
5. 变更后再次对比耗时与扫描行数，确认收益。

小结

慢查询优化的核心是先定位再动手：用慢日志找到真正耗时的语句，用执行计划判断问题出在索引还是写法，改完再量化验证。避免凭感觉乱加索引。