线上接口变慢,八成是慢 SQL。排查慢 SQL 是一套固定动作,不是玄学:找到它 → 看执行计划 → 定位原因 → 动手优化。这篇用一条真实案例走完整流程。
打开慢查询日志(记录超过阈值的 SQL 的日志),阈值按业务定,一般 1 秒:
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1; -- 超过1秒记日志
日志文件里每条都带耗时,谁慢一目了然。排名别只看单次耗时,要看「次数 × 单次耗时」的总贡献——一条 200 毫秒但每天跑十万次的 SQL,比一条 5 秒每天跑三次的更值得治,按总耗时排队,不按单次。
如果有监控平台(Prometheus + 应用 APM),直接看接口维度的 SQL 耗时排名更快。找不到具体 SQL 就谈不上优化,这一步价值占一半。
接口正在慢的「直播现场」还有两个命令:
SHOW PROCESSLIST; -- 当前在跑的SQL,看有没有同一条刷屏堆积
SELECT * FROM information_schema.INNODB_TRX; -- 活跃事务和开始时间
同一条 SQL 出现几十个副本,是并发打满的典型;某事务 trx_started 是十分钟前,锁等待的元凶多半就是它。慢日志是录像,这两条是直播——事后排查用录像,救火用直播。
案例:订单列表页接口,3 秒才返回。
SELECT * FROM `order`
WHERE user_id = 42 AND status = 1
ORDER BY create_time DESC
LIMIT 20;
EXPLAIN SELECT ... \G
-- type: ALL ← 全表扫描!
-- rows: 13000000 ← 预估扫1300万行
-- Extra: Using where; Using filesort ← 还额外排序!
三个信号全是红灯。type(访问类型)从好到差:const > eq_ref > ref > range > index > ALL,出现 ALL 就是全表扫;rows 是预估扫描行数;Extra 里 filesort 说明排序没吃到索引红利(字段含义回看 9.3)。
还有两个进阶列值得认识:possible_keys 列出「本可以用」的索引,key 是「实际用了」的——possible 里有一堆而 key 是 NULL,正是索引失效的指纹;select_type 出现 DERIVED 说明 FROM 里有子查询派生表,大派生表会物化成临时表,是隐性杀手。
全表扫(ALL) → 无索引 / 索引失效 / 优化器放弃
rows 巨大 → 过滤条件区分度低 / 深分页
filesort → 排序列不在索引里 / 排序键套了表达式
temporary → GROUP BY / DISTINCT / UNION 产生临时表
症状映射到病因,再对照 9.3 的十大失效清单下药。速查表的用法是「先定症状、再翻病因清单」,两篇合起来就是一本完整的诊断手册。更聪明的做法是把这套动作固化成定时任务:每天自动分析慢日志、生成优化建议,人只做复核——防患永远比救火便宜。
逐条翻译红灯:
1. type=ALL:user_id + status 上没有能用的索引(或索引失效了)
2. rows=1300 万:全表行数,等于每查一页都把整张表摸一遍
3. filesort:按 create_time 排序时索引帮不上忙,内存里现排
复合起来就是:过滤无索引 + 排序无索引,双重开销。
核心思路:让「等值过滤 + 排序」吃同一棵联合索引。
CREATE INDEX idx_user_status_time
ON `order`(user_id, status, create_time);
-- 复查执行计划:
-- type: ref ← 走索引了
-- rows: 500 ← 预估行数从1300万降到500
-- Extra: Using index condition
-- 实测:3秒 → 50毫秒
为什么这棵树这么灵?等值条件排最前(user_id、status 精确定位),排序列殿后(索引天然有序,filesort 消失)。这就是「等值在前、范围和排序靠后」的联合索引设计口诀。
复盘这单案例的收益:接口 P99 从 3.2 秒落到 45 毫秒;代价是订单表多养一棵索引树,写入耗时增加约 3%。用 3% 的写入换几十倍的读取,这笔账怎么算都划算——优化报告就该有数有据,而不是「感觉快了」。
列表页翻到几千页后,每页要 8 秒:
SELECT * FROM orders ORDER BY id LIMIT 9000000, 20;
-- 沿主键数900万行扔掉,只返回20行
-- 止血改写:延迟关联,先在索引里只数主键
SELECT o.* FROM orders o
JOIN (SELECT id FROM orders ORDER BY id
LIMIT 9000000, 20) x ON o.id = x.id;
-- 子查询走覆盖索引(只碰主键列),回表仅20次
延迟关联实测 8 秒降到 1 秒出头;再把交互改成游标翻页(WHERE id > ? ORDER BY id LIMIT 20)后只要 30 毫秒。三个数字连起来记:延迟关联是改不了交互时的止血,游标分页才是根治。
LIMIT 1000000,10 改 WHERE id > ? ORDER BY id LIMIT 10顺序也有讲究:先改写 SQL,再动索引,最后才动架构。索引解决 80% 的问题,别一上来就喊分库分表(代价见 9.7)。优先级口诀:一改写、二索引、三架构——改写零成本,索引一次 DDL,架构伤筋动骨。见过太多团队跳过前两步直接上分库分表,最后在跨库事务的泥潭里后悔。
加完索引必须复查:EXPLAIN 再跑一遍、真实耗时压一次、观察写入性能有没有被拖累(索引多一棵,INSERT 就慢一分)。上线后盯一两天慢查询日志,防止数据量变化后优化器改道。
读优化讲了一整篇,写侧三个习惯同样值钱:批量插入合并成多值 INSERT(一条语句插百行,比一百条语句快一个量级);大删除分批跑(一次 DELETE 百万行,binlog、锁、主从延迟三座山一起来,每次删几千行循环执行更稳);索引维护避开高峰(新索引的写放大在高峰期会被放大感知)。读写两手抓,才配叫 SQL 性能治理。
误区一:加索引总是对的——写多读少的表上,索引税可能超过读收益。误区二:慢 SQL 是 DBA 的事——写代码的人最清楚过滤条件,索引设计天然是研发的活。误区三:优化一次管永远——数据涨十倍,执行计划可能整个改写,慢日志要常态化盯着。
慢 SQL 排查是流水线:慢日志找嫌疑、EXPLAIN 定罪名、索引和 SQL 改写下药、复测收工——病因永远写在 type、rows、Extra 三列里。