MySQL 慢 SQL 排查实战——explain 深度解读、profiling 工具、索引优化方法论
MySQL 慢 SQL 排查实战——explain 深度解读、profiling 工具、索引优化方法论
适读人群：需要处理数据库性能问题的后端工程师、DBA | 阅读时长：约17分钟 | 核心价值：掌握一套完整的慢 SQL 排查方法论，从发现到解决形成闭环
一个坑了团队三天的慢查询
去年初，我帮一家做医疗信息系统的公司做性能排查。他们的系统运行了三年，最近半年响应越来越慢，特别是报表相关的查询，有时候要跑几分钟。运维和开发轮流排查了三天，没找到根因。
我进去之后，第一步打开慢查询日志，过滤出 Top10 慢 SQL。排在第一位的一条 SQL，执行时间 78 秒：
SELECT p.patient_id, p.name, v.visit_date, d.diagnosis_code
FROM patients p
LEFT JOIN visits v ON p.patient_id = v.patient_id
LEFT JOIN diagnoses d ON v.visit_id = d.visit_id
WHERE v.visit_date BETWEEN '2023-01-01' AND '2023-12-31'
ORDER BY v.visit_date DESC
LIMIT 100;EXPLAIN 一看，visits 表的 visit_date 没加索引，全表扫描了 230 万行；diagnoses 表的 visit_id 也没索引，JOIN 时嵌套循环扫描了 5000 万行。
加完两个索引，这条 SQL 从 78 秒降到了 0.3 秒。
三天没找到，被我 15 分钟解决了。不是我多厉害，是因为我有一套固定的排查方法论，而他们是在凭感觉猜。
今天，把这套方法论完整分享出来。
一、慢查询日志：发现问题的第一步
1.1 开启慢查询日志
-- 查看当前慢查询日志状态
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
-- 动态开启（无需重启，但重启后失效）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1; -- 超过 1 秒的 SQL 记录到慢查询日志
SET GLOBAL log_queries_not_using_indexes = ON; -- 记录未使用索引的 SQL
-- 永久生效（写入 my.cnf）
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1我的建议：生产环境 long_query_time 设置为 1 秒，发现问题后可以临时调低到 0.5 秒或 0.1 秒，精细化排查。不要关慢查询日志，磁盘占用可控，但价值极大。
1.2 用 mysqldumpslow 分析慢日志
# 按查询时间排序，显示最慢的 10 条
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# 按执行次数排序（找高频慢查询，累计代价更大）
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log
# 输出示例：
# Count: 1523 Time=2.34s (3564s) Lock=0.00s (0s) Rows=100.0 (152300)
# SELECT * FROM orders WHERE user_id = N AND status = N ORDER BY create_time DESC LIMIT N二、EXPLAIN 深度解读
EXPLAIN 是分析 SQL 执行计划的最核心工具，但很多人只会看 type 和 key，其实每一列都有重要信息。
// Java 代码中动态获取 SQL 的执行计划（调试用）
@Repository
public class SqlAnalyzer {
@Autowired
private JdbcTemplate jdbcTemplate;
/**
* 分析 SQL 的执行计划
* 注意：只用于开发和调试环境，生产不要暴露此接口
*/
public List<Map<String, Object>> explainSql(String sql) {
return jdbcTemplate.queryForList("EXPLAIN " + sql);
}
/**
* 获取 SQL 的详细执行分析（包含成本估算）
* MySQL 5.7+ 支持
*/
public List<Map<String, Object>> explainAnalyzeSql(String sql) {
// MySQL 8.0+ 支持 EXPLAIN ANALYZE，直接执行 SQL 并显示实际行数
return jdbcTemplate.queryForList("EXPLAIN ANALYZE " + sql);
}
}2.1 type 列：访问类型（最重要）
按性能从好到差：
| type | 含义 | 备注 |
|---|---|---|
system | 表只有一行 | 极少见 |
const | 主键或唯一索引等值查询 | 最快，1次查找 |
eq_ref | JOIN 时被驱动表走主键/唯一索引 | |
ref | 非唯一索引等值查询 | 常见，好的情况 |
range | 索引范围扫描 | BETWEEN/IN/>/< |
index | 全索引扫描（遍历整个索引树） | 比 ALL 好，但仍然慢 |
ALL | 全表扫描 | 危险，必须优化 |
重点：看到 ALL 或 index，立刻分析原因，这两种情况在大表上几乎必然导致性能问题。
2.2 key 和 key_len 列
key：实际使用的索引名。如果是 NULL，说明没有使用索引（可能是索引不存在，也可能是优化器认为全扫更快）。
key_len：使用的索引长度（字节数）。用来判断联合索引用了几列：
-- 假设联合索引 INDEX(a INT, b INT, c VARCHAR(50)) charset=utf8mb4
-- a: 4字节, b: 4字节, c: 50*4+2(变长) = 202字节
EXPLAIN SELECT * FROM t WHERE a=1 AND b=2 AND c='x';
-- key_len = 210 ← 三列都用了
EXPLAIN SELECT * FROM t WHERE a=1 AND b=2;
-- key_len = 8 ← 只用了 a, b 两列
EXPLAIN SELECT * FROM t WHERE a=1;
-- key_len = 4 ← 只用了 a 列2.3 rows 列
优化器估算的扫描行数。这是估算值，不是准确值，但数量级应该准确。如果 rows 显示要扫描几十万行，即使最终结果只有 100 行，这个 SQL 也是有问题的。
2.4 Extra 列：关键信号
| Extra 值 | 含义 | 是否需要优化 |
|---|---|---|
Using index | 覆盖索引，无回表 | 好，不需要 |
Using where | 在引擎层过滤后，服务层还在过滤 | 一般，可接受 |
Using filesort | 文件排序，无法利用索引排序 | 需要优化 |
Using temporary | 使用了临时表 | 需要优化 |
Using index condition | 索引下推（ICP），在索引层过滤 | 好的优化 |
Using filesort 意味着 ORDER BY 的列没有索引，MySQL 需要把数据拿出来在内存（或磁盘）里排序。Using temporary 通常出现在 GROUP BY 或 DISTINCT 场景，意味着用了临时表。这两个信号同时出现，SQL 一定很慢。
三、PROFILE：精细化耗时分析
EXPLAIN 告诉你用没用索引，但如果你想知道 SQL 的哪个环节最耗时，需要 PROFILE：
-- 开启 profiling
SET profiling = 1;
-- 执行你要分析的 SQL
SELECT * FROM orders WHERE user_id = 12345 ORDER BY create_time DESC LIMIT 100;
-- 查看 profile 列表
SHOW PROFILES;
-- Query_ID | Duration | Query
-- 查看指定 Query 的详细耗时
SHOW PROFILE ALL FOR QUERY 1;SHOW PROFILE ALL 会显示每个阶段的耗时，重点关注：
Sending data：读取数据的时间，如果这里很长，说明扫描的数据太多（索引问题）Creating sort index/Sorting result：排序时间，如果很长，说明排序没走索引Opening tables/System lock：如果锁等待时间很长，说明有锁竞争Converting HEAP to MyISAM：临时表超过内存限制，溢出到磁盘，极慢
注意：PROFILE 在 MySQL 8.0 中已标记为废弃，MySQL 8.0 推荐用 EXPLAIN ANALYZE 或 Performance Schema。
四、慢 SQL 优化方法论
经过多年实战，我总结了一套固定的排查流程：
第一步：复现和量化
-- 在测试环境复现，获取准确的执行时间
SELECT SQL_NO_CACHE * FROM orders WHERE user_id = 12345;
-- SQL_NO_CACHE 防止查询缓存影响结果（MySQL 8.0 已移除查询缓存）第二步：看执行计划
EXPLAIN 分析 type、key、rows、Extra，定位问题类型：
ALL+ 大表 → 缺索引或索引失效Using filesort→ ORDER BY 缺索引Using temporary→ GROUP BY / DISTINCT 需要优化- rows 过大 → 索引选择性差，或统计信息不准
第三步：针对性优化
// 优化示例：把分页查询从 OFFSET 改为游标分页
// 原方式：OFFSET 越大越慢（需要扫描并丢弃 OFFSET 行数据）
@Select("SELECT * FROM orders WHERE user_id = #{userId} " +
"ORDER BY id DESC LIMIT #{size} OFFSET #{offset}")
List<Order> findByUserIdWithOffset(@Param("userId") Long userId,
@Param("size") int size,
@Param("offset") int offset);
// 优化后：游标分页，利用主键索引，任何页都一样快
@Select("SELECT * FROM orders WHERE user_id = #{userId} " +
"AND id < #{lastId} " +
"ORDER BY id DESC LIMIT #{size}")
List<Order> findByUserIdWithCursor(@Param("userId") Long userId,
@Param("lastId") Long lastId,
@Param("size") int size);第四步：验证和上线
优化后的 SQL 在测试环境验证效果，EXPLAIN 确认执行计划改善，压测确认性能提升，再上生产。
五、几个常见的慢 SQL 模式
N+1 查询：在循环里查数据库，100 个订单就查 100 次用户信息。改成批量查询（WHERE id IN (...))，减少数据库往返次数。
分页深翻：LIMIT 100000, 20 要先扫描 10 万行再取 20 行。改用上文的游标分页，或者先查 ID 再查数据。
JOIN 太多：超过 5 张表的 JOIN，MySQL 的执行计划复杂度急剧上升，优化器容易选错计划。考虑拆分查询，在应用层合并结果。
子查询 vs JOIN：MySQL 5.x 的子查询优化很差，尽量改成 JOIN。MySQL 8.0 有了物化子查询优化，部分场景子查询性能已经和 JOIN 持平。
慢 SQL 排查不是玄学，是有方法可循的工程实践。建立起这套方法论，面对任何性能问题都能有条不紊地推进。