MySQL 慢 SQL 优化工作流：从慢日志到 EXPLAIN 和索引回归验证

MySQL 慢 SQL 优化不能只靠“看起来该加索引”。比较稳的做法是把它当成一条工作流：先从慢查询日志找到高影响语句，再聚合同类 SQL，接着用 EXPLAIN 看访问方式、索引命中和扫描行数，最后做索引改造与回归验证。

这篇文章不追求一次讲完所有优化技巧，而是给出一套能复用的路线图。你可以把它用在列表页变慢、后台查询卡顿、定时任务拖库、接口 p95 飙升等场景。

目标和边界：慢 SQL 优化先看影响面

先说结论：慢 SQL 优化的目标不是让某条语句在本地跑得很快，而是让线上高频、重要、可复现的查询稳定下降延迟，同时不破坏写入性能和业务结果。

开始动手前，先把边界定清楚：

• 优先处理高频、耗时高、扫描行数大的 SQL。
• 不要只看单次耗时，还要看 p95、总耗时和调用次数。
• 索引改造要关注写入成本、磁盘占用和已有索引冗余。
• 上线后必须验证业务结果、延迟变化和扫描行数变化。

全流程总览：从慢日志到回归验证

一条完整的慢 SQL 治理链路通常分五步：慢日志发现候选语句，按指纹聚合同类 SQL，用 EXPLAIN 看访问路径，按查询条件设计索引，最后用监控和真实请求回归。

阶段一：打开慢查询日志并筛出候选 SQL

目标：先拿到线上真实慢查询，而不是凭感觉猜。

关键动作：确认慢查询日志是否开启，设置合理阈值。测试环境可以短一些，生产环境要结合业务峰值谨慎调整。

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'slow_query_log_file';

如果需要临时观察，可以在低风险窗口调整阈值：

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

检查点：日志里至少要看到这些信息：

Query_time: 3.210
Lock_time: 0.001
Rows_sent: 20
Rows_examined: 12500
SET timestamp=1781600000;
SELECT id, status, create_at
FROM orders
WHERE status = 1
ORDER BY create_at DESC
LIMIT 20;

Rows_examined 很高但 Rows_sent 很低，通常说明 MySQL 为了返回少量结果扫描了大量记录，这类查询就值得继续分析。

阶段二：聚合同类 SQL，先处理高影响语句

目标：不要被一两条偶发 SQL 带偏，先找“总耗时高”的查询形态。

关键动作：把参数不同、结构相同的 SQL 聚合在一起。可以用 mysqldumpslow 快速看概况，也可以用日志平台或性能视图做更细统计。

mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log

这个命令会按总耗时排序，取前 10 类语句。排查时建议同时看三个指标：

• count：出现次数，代表影响范围。
• avg time 或 p95：代表单次请求体验。
• rows：代表扫描压力。

检查点：你应该能选出一个明确候选，例如“订单列表查询每天出现 1.5 万次，p95 超过 3 秒，扫描行数一万以上”。到这一步不要急着建索引，先看它当前怎么访问表。

阶段三：用 EXPLAIN 看访问方式和扫描行数

目标：确认慢的原因是全表扫描、索引选择不佳、排序、临时表，还是返回数据本身太多。

EXPLAIN
SELECT id, status, create_at
FROM orders
WHERE status = 1
ORDER BY create_at DESC
LIMIT 20;

常看这几列：

如果看到 type=ALL、key=NULL、rows 很高，说明查询没有走到合适索引；如果同时出现 Using filesort，排序也可能在放大耗时。

阶段四：按 WHERE 与 ORDER BY 设计联合索引

目标：让 MySQL 先通过筛选条件缩小范围，再按索引顺序拿到排序结果，尽量减少回表、扫描和额外排序。

以订单列表为例，查询条件是 status = 1，排序字段是 create_at DESC，可以先考虑联合索引：

ALTER TABLE orders
ADD INDEX idx_status_create_at (status, create_at);

这个索引不是万能模板，而是服务于当前查询形态。设计时可以按下面的顺序判断：

1. 等值筛选字段通常放前面，例如 status、tenant_id。
2. 范围字段和排序字段要结合查询形态看，避免索引顺序被过早截断。
3. 只为真实高频查询建索引，不为每个可能条件都建一个索引。
4. 检查是否已有相似索引，避免重复索引增加写入成本。

改完后再次查看：

EXPLAIN
SELECT id, status, create_at
FROM orders
WHERE status = 1
ORDER BY create_at DESC
LIMIT 20;

检查点：理想变化是 key 命中新的联合索引，rows 明显下降，排序开销减少。实际效果仍要以数据分布和真实请求为准。

阶段五：上线前后做回归验证

目标：确认优化收益真实存在，并且没有引入新问题。

上线前可以做三类验证：

• 结果一致性：优化前后返回的数据顺序和条数一致。
• 访问路径：EXPLAIN 里索引命中和扫描行数符合预期。
• 写入影响：确认新增索引不会让高频写入明显变慢。

上线后观察这些指标：

• 接口 p95、p99 是否下降。
• 慢日志中同类 SQL 是否减少。
• 数据库 CPU、IO、锁等待是否稳定。
• 业务错误率和返回结果是否正常。

我的推荐流程

1. 先开慢日志或读取现有日志，拿到真实候选 SQL。
2. 按 SQL 指纹聚合，优先处理调用多、总耗时高、扫描行数大的语句。
3. 复制一条代表性 SQL，用接近生产的数据量查看 EXPLAIN。
4. 根据 WHERE、JOIN、ORDER BY 和返回字段设计索引。
5. 改完后再次看 EXPLAIN，确认 key、rows、Extra 变化。
6. 用业务用例验证结果一致性，再灰度上线。
7. 上线后至少观察一个业务高峰，确认慢日志和接口延迟都下降。

容易踩坑的地方

• 只看单次耗时，不看调用次数，导致优化了低影响 SQL。
• 看见慢就加索引，却没有确认当前查询是否真的能用上。
• 联合索引字段顺序凭感觉排，没有结合筛选、排序和数据分布。
• 新增多个相似索引，读性能提升一点，写入和维护成本却上升很多。
• 只在本地小数据量验证，线上数据分布完全不同。
• 上线后没有回归指标，无法判断优化是否真的生效。

落地速查表

慢 SQL 优化最重要的不是记住某个固定索引模板，而是形成稳定判断链路：真实日志发现问题，聚合后确定优先级，计划表定位访问路径，索引或查询改造减少扫描，最后用线上指标证明收益。