结论先行:读 explain 只需要盯住四个关键点——type 访问类型、key 实际用到的索引、rows 预估扫描行数、Extra 附加动作。九成的索引问题能从这四个字段直接看出来。 explain 不会真正执行查询,只让优化器"口头描述"它打算怎么执行,是成本最低的 SQL 排查工具。
用法与输出
在查询前加 EXPLAIN 即可。MySQL 8.0 推荐补充 EXPLAIN FORMAT=TREE,疑难杂症用 EXPLAIN ANALYZE(会真实执行)看每一步的实际耗时和行数:
EXPLAIN SELECT o.*
FROM orders o
JOIN user u ON o.user_id = u.id
WHERE o.status = 1 AND o.created_at >= '2026-01-01';
-- 8.0:实际执行并输出每步真实耗时与行数
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 10086;
读输出的顺序也有讲究:先看驱动表(第一行的 id 相同、select_type 为 SIMPLE 或 PRIMARY 的那张)是不是小表——join 场景下驱动表选错,后面全盘皆错;再按行逐个核对 type、key、rows、Extra。多表 join 时每一行代表一次表访问,行数越多的表越要重点看。
完整输出还有几个次级字段值得认识:id 相同表示同一查询块内的表,id 不同则内层子查询先执行;select_type 里的 DEPENDENT SUBQUERY 是危险信号,意味着子查询可能对外层每行重复执行;ref 列显示索引匹配的常量或被驱动表的列;filtered 是这一步过滤后剩余行数的百分比估算,rows × filtered 就是传递给下一个 join 表的行数,多表 join 时这个乘积决定了整体的行数放大链条。
type:访问类型是第一优先级
按好到差排序,重点记住这条线:
const / eq_ref > ref > range > index > ALL
| type | 触发条件 | 典型场景 |
|---|---|---|
| const | 唯一索引等值命中 | 主键或唯一键等值查 |
| eq_ref | join 被驱动表走主键/唯一索引 | ON o.user_id = u.id 且 u.id 是主键 |
| ref | 普通索引等值匹配 | WHERE user_id = 10086 |
| range | 索引范围扫描 | >、between、in |
| index | 扫全索引树 | 索引比表小时的降级选择 |
| ALL | 全表扫描 | 大表出现即优化信号 |
- const:唯一索引等值命中,最多一行;
- eq_ref:join 时被驱动表走主键或唯一索引,每次匹配最多一行;
- ref:普通索引等值匹配;
- range:索引范围扫描(
>、between、in); - index:扫全索引,比 ALL 好一点,因为索引通常比表小;
- ALL:全表扫描,大表出现 ALL 基本就是要优化的信号。
线上经验值:核心接口的 SQL,大表访问 type 至少要到 ref;报表类可以放宽到 range;出现 index 要警惕——它意味着优化器决定"全索引扫完再过滤",通常是索引设计或写法有问题。
const 之所以排在最前,是因为主键或唯一索引的等值查询在优化器阶段就被折叠成常量,执行时几乎零开销;eq_ref 只出现在 join 的被驱动表上,是 join 可达的最优形态。range 之下还有一个容易被忽略的坑:in 大列表有时被优化器按 range 处理,有时因元素过多退化为 ALL,判断依据还是回到 rows 与 key 两个字段本身,不要凭条件写法想当然。
key 与 rows:用了哪个索引,扫多少行
possible_keys 只是候选,key 才是优化器的真实选择。它可能放弃你预期的索引——最常见原因是估算出回表成本高于全表扫描,此时与其强行 FORCE INDEX,不如先理解优化器为什么不选:把 force index 当成对付优化器的武器之前,先用 optimizer_trace 看清成本估算过程:
-- 打开 optimizer trace,看优化器对每个候选索引的成本计算
SET optimizer_trace = 'enabled=on';
SELECT * FROM orders WHERE user_id = 10086 AND status = 1;
SELECT TRACE FROM information_schema.OPTIMIZER_TRACE\G
SET optimizer_trace = 'enabled=off';
rows 是估算值,量级明显失真时通常是统计信息过期,用 ANALYZE TABLE 重新采集。判断失真有个简单办法:explain 的 rows 与 EXPLAIN ANALYZE 报告的实际行数差了一个数量级以上,基本就是统计信息的问题。
key_len 用来确认联合索引用到了第几列:整型定长好算,可空列额外加 1 字节,varchar 还要加长度信息。比如 idx(user_id, status) 的查询里 status 没有等值条件,key_len 就只体现 user_id 部分。联合索引是否被"用到最后一列",key_len 比肉眼判断可靠得多。
Extra:藏在最后的真正动作
| Extra | 含义 | 处理 |
|---|---|---|
| Using index | 覆盖索引,免回表 | 理想状态 |
| Using where | server 层再过滤 | 关注过滤后的比例 |
| Using filesort | 需额外排序 | 让 order by 匹配索引顺序 |
| Using temporary | 用了临时表 | group by / distinct 优化重点 |
| Using index condition | ICP 下推,存储层提前过滤 | 正常优化,无需处理 |
| Select tables optimized away | 聚合被优化成读一行 | 理想状态 |
两个高频误读:Using where 不一定是坏事,但若 rows 很大而最终结果集很小,说明大量行被拉到 server 层丢弃,索引选择性不足;Using filesort 不一定在磁盘上排——内存排序也算——但它意味着排序动作没有复用索引顺序,数据量大时是性能杀手。让排序走索引的经典做法是把排序列追加进联合索引的最后一列。另外 Using index condition 不是问题而是优化:5.6 引入的 ICP(Index Condition Pushdown)把过滤条件下推到存储层,先在索引上过滤再回表,减少无效回表次数。
版本差异也值得留意:5.6 引入 ICP,5.7 引入派生表合并(derived merge),8.0 引入跳过扫描(skip scan)与倒序索引扫描。同一条 SQL 在不同大版本上的执行计划可能不同,升级数据库后核心 SQL 的 explain 要重新过一遍。
Extra 看完最后回到写法本身。同一个索引,写法不同结果天差地别,联合索引 (status, created_at) 的例子最典型:
-- 错误写法:缺最左列,范围条件单独出现,(status, created_at) 用不上
EXPLAIN SELECT * FROM orders WHERE created_at >= '2026-01-01';
-- type=ALL,key=NULL
-- 正确写法:等值列在最左,范围列在最后,整条索引参与过滤
EXPLAIN SELECT * FROM orders
WHERE status = 1 AND created_at >= '2026-01-01';
-- type=range,key=idx_status_created,key_len 覆盖两列
同类问题还有:隐式类型转换(varchar 列与数字比较)、对索引列套函数、前导 % 的 like、or 连接非索引列。这些写法问题的系统清单,在慢查询优化一篇里展开。看 explain 时养成一个习惯:key 不为空但 rows 异常大时,先怀疑"索引只被用了一半"(范围列之前的列没等值条件),再用 key_len 验证;key 为空时,优先对照写法问题清单排查,最后才考虑加索引——多数"没索引"的结论,其实是写法把索引挡在了门外。
三个常见误判
- type=ALL 不一定慢:几行的维表全扫,可能比回表几千次还快;
- rows 少不代表快:如果扫描结果全要回表,逻辑读被放大数倍;
- explain 是"预估",真实执行可能不同——优化前后务必用
EXPLAIN ANALYZE或实际压测对比。
还有一个流程层面的提醒:explain 结论只在"当时的表结构、索引、数据分布"下成立。表加了索引、数据量翻倍、统计信息重采之后,旧结论随时作废。把 explain 截图当作一劳永逸的证据存档没有意义,有效的做法是把它写进变更记录,并注明采集时间与当时的版本——explain 是诊断快照,不是终身判决。
一句话收尾:explain 是优化器的想法,ANALYZE 是执行器的账本,优化 SQL 时两个都要看,只信其一容易白忙一场。
生产案例
某天上午收到接口超时告警,一条订单列表 SQL 在高峰期偶发耗时数秒,平时却只有几十毫秒。拿慢日志里的样本跑 explain,发现 type=ref、key 正常,rows 也只有几千——计划看起来无可挑剔。用 EXPLAIN ANALYZE 陪跑了几分钟才现出原形:该查询带 ORDER BY created_at DESC LIMIT 20,优化器大部分时间选择按 created_at 索引倒序扫再过滤,但当 status 条件的匹配行数估算偏低时,它切换成 ref + filesort,几千行排序叠加回表,毛刺就是这么来的。处理方式是把索引改成 (status, created_at),让等值条件先收窄、排序复用索引序,两种估算路径都收敛到同一计划。这次排查的教训是:explain 只反映"那一刻"的计划,波动型慢查询要抓慢日志样本、用 ANALYZE 复现真实执行,而不是盯着一次 explain 下结论。
检查清单
- 大表 SQL 的 type 至少达到 ref,杜绝 ALL 与 index
- key 与 possible_keys 不一致时,先查统计信息(ANALYZE TABLE)再考虑 force index
- 用 key_len 确认联合索引用到了最后一列
- Extra 出现 Using filesort / Using temporary 时优先改造索引或改写 SQL
- rows 与实际行数差一个数量级以上,重采统计信息
- 波动型慢查询用 EXPLAIN ANALYZE 看真实执行,不只看预估
- 上线前在测试环境跑一遍核心 SQL 的 explain,纳入 code review 材料