「这个查询走了索引啊,为什么还是慢?」开发把 EXPLAIN 截图发到群里,key 那列明明白白写着索引名。可 type 是 index,rows 是一万多,Extra 里还有 Using filesort。走索引不等于高效:type=index 的意思是「把整棵索引从头到尾扫一遍」,跟全表扫描差不多,只是扫的东西小一点。
这篇逐列拆 EXPLAIN。目标不是背字段名,而是看到一行计划,能判断「它准备读多少数据、按什么顺序读、在哪一步做了额外操作」。文中的输出都来自本机 MySQL 8.0.12 的真实执行。
场景走查:一张截图看不出的问题
后台订单列表的 SQL 是:
SELECT id, status, amount
FROM orders
ORDER BY created_at DESC
LIMIT 1900, 10;
开发只看到「没报错、能出结果」,没看计划。真实计划是:type=ALL、rows=1998、Extra=Using filesort。全表扫描 1998 行,再排序,最后扔掉前 1900 行。数据量放大到千万级,这个接口就会从 20ms 变成几秒。
如果只取 id,计划会变成 type=index、key=idx_created、rows=1910、Extra=Backward index scan; Using index。看起来「走索引了」,但它仍然要扫 1910 条索引记录,只是不用回表。深分页的成本没有消失,只是换了个地方。
三种查看方式
最常用的是在 SQL 前面加 EXPLAIN:
EXPLAIN SELECT id, status, amount FROM orders
WHERE customer_id = 7
ORDER BY created_at DESC
LIMIT 10;
EXPLAIN 只解释计划,不执行 SQL,所以可以放心在生产上跑(但要注意它也会做权限检查和元数据锁,别在高峰期狂刷)。EXPLAIN FORMAT=JSON 输出结构化信息,包含代价估算,适合细看:
EXPLAIN FORMAT=JSON SELECT id, status, amount FROM orders
WHERE customer_id = 7
ORDER BY created_at DESC
LIMIT 10;
实测 JSON 里能看到 "access_type": "ref"、"key": "idx_customer_created"、"rows_examined_per_scan": 20、"using_filesort": false、"query_cost": "7.00"。代价数字是优化器的估算,不是实际耗时。
8.0.12 也支持 EXPLAIN FORMAT=TREE,输出是一棵缩进的计划树,每个节点后面带 cost,比如 -> Index lookup on orders using idx_customer_created (customer_id=7) (cost=...)。树形的好处是能直接看出「谁在谁里面」:排序套在扫描外面,还是扫描套在排序外面。FORMAT=JSON 适合机器解析,FORMAT=TREE 适合人读,两者都能看到代价。
MySQL 8.0.18 起支持 EXPLAIN ANALYZE,会真正执行 SQL 并给出每一步的实际耗时和行数。本机是 8.0.12,不支持这个语法,所以本文没有它的实测输出。它在排查「估算和实际差很多」时非常有用,但会执行 SQL,生产上要谨慎。
逐列读:哪些列真正有用
| 列 | 含义 | 怎么用 |
|---|---|---|
id | 查询块编号,数字越大越先执行 | 有子查询时看执行顺序 |
select_type | 简单查询、子查询、联合查询等 | DEPENDENT SUBQUERY 往往意味着每行重算 |
table | 这一行对应哪张表 | JOIN 时确认驱动表和被驱动表 |
type | 访问类型,决定怎么读数据 | 最重要的列之一,从好到坏见下节 |
possible_keys | 理论上可用的索引 | 有候选但 key 为空,说明优化器放弃了 |
key | 实际使用的索引 | NULL 表示没走索引 |
key_len | 实际用了索引的前多少字节 | 联合索引用了几列,看这个 |
ref | 和索引比较的值或列 | const 表示常量,其他表名表示 JOIN 关联 |
rows | 估算要扫描多少行 | 估算值,不是实际值;越大越要警惕 |
filtered | 扫描行中预计有多少比例满足条件 | 100 表示全命中,10 表示大量行被过滤掉 |
Extra | 额外操作信息 | Using filesort、Using temporary 要重点看 |
select_type 值得单独说,因为它是「有没有重复执行」的直接信号。最普通的是 SIMPLE,没有子查询和 UNION。PRIMARY 是外层查询,SUBQUERY 是 WHERE 里不依赖外层的子查询,只算一次。DEPENDENT SUBQUERY 最要警惕:它引用了外层的列,外层每取一行它就要重算一次,外层 1998 行就是 1998 次子查询,这个场景在聚合篇里实测出现过。DERIVED 是 FROM 子句里的派生表,MySQL 5.7 之后通常会合并进外层,合并失败才会物化成临时表。UNION 和 UNION RESULT 表示并集以及去重用的临时表。判断方法很简单:看到 DEPENDENT 开头,先想能不能改写成 JOIN;看到 DERIVED 且 Extra 有 Using temporary,再想能不能把派生表拆开单独查。
type 从好到坏
实测四种典型计划:
| SQL 特征 | type | rows | Extra |
|---|---|---|---|
customer_id = 7 ORDER BY created_at DESC | ref | 20 | Using where; Backward index scan |
created_at 窄范围 | range | 60 | Using index condition |
ORDER BY created_at 不带条件、只取 id | index | 1910 | Backward index scan; Using index |
ORDER BY created_at 还要取 status、amount | ALL | 1998 | Using filesort |
注意第三行:type=index,rows=1910,它并不是「快速定位」,而是「从索引一端扫到另一端」。看 type 时别只看有没有索引名,要结合 rows 判断扫描量。
这个扫描量会随偏移量增长。在 19.98 万行的 orders_big 上,SELECT id ... ORDER BY created_at DESC LIMIT 195000, 10 的计划是 type=index、rows=195010、Using index,耗时 54.02 ms;type 看起来「比 ALL 好」,但 rows 已经接近全表。判断标准不是 type 的排名,而是「它准备读多少行、这些行占表的比例」。占比接近 100%,index 和 ALL 的实际差别就只剩「扫索引还是扫表」。
const 和 eq_ref 在实际业务里很常见,只是容易被忽略。WHERE id = 7 走主键,计划是 type=const、rows=1,优化器在生成计划阶段就能定位到这一行,剩下的条件直接拿常量比。eq_ref 只会出现在 JOIN 的被驱动表上,而且关联列必须是主键或唯一索引,比如 ON oi.order_id = o.id,一行订单最多匹配一组明细,一对一。ref 则是普通索引,一对多,一个订单要匹配多行明细。到 range 就只剩范围定位,到 ALL 和 index 已经是「从头扫到尾」,两者区别只在于扫的是表还是索引,都按危险处理,除非你确认表本身很小。
key_len:联合索引到底用了几列
key_len 是索引中使用部分的字节数。对 INT 列,通常 4 字节;对允许 NULL 的列会多 1 字节;VARCHAR 还要算字符集和长度。实测 idx_abc(a, b, c) 三个 INT 列:
WHERE a = 3 AND b = 30 AND c = 30 -- key_len = 12
WHERE a = 3 AND b = 30 -- key_len = 8
WHERE a = 3 -- key_len = 4
12 表示三列全用上,8 表示两列,4 表示只用了第一列。如果看到 key_len=4,却以为「三个条件都命中了索引」,就会误判。另一种情况是条件里有 a = 3 AND c = 30,key_len 仍是 4,因为 c 前面断了 b,只能在索引里做过滤。
字符串列的算法不一样。一个 VARCHAR(50) 的 utf8mb4 列,索引里存的是前缀,按每字符最多 4 字节算,key_len 会明显大于 4。允许 NULL 的列还要额外加 1 字节标记,所以「同是 INT」的 key_len 可能是 4 也可能是 5。判断时别死记数字,先按「参与比较的列的最大字节和」估一个上限,再看实际值;差一截,就说明有列没被用上,或者只用了前缀。这也解释了一个常见困惑:明明两个条件都写了,key_len 却只对应一列——不是优化器偷懒,而是索引没法在这种条件下继续用下一列。
rows 和 filtered 是估算,不是事实
rows 来自统计信息,不是实际扫描行数。表刚导入大量数据、统计信息没更新时,rows 可能差很远。优化器就是拿它算代价的,估算错了,计划就会选错。必要时跑 ANALYZE TABLE 刷新。
filtered 表示「扫描的行里预计有多少比例满足后续条件」。比如 rows=101、filtered=10.00,意思是扫 101 行,预计只有约 10% 命中。它同样是估算。看到 filtered 很低,说明索引只能定位一部分条件,剩下的过滤成本还在。
rows。要把 rows × filtered 理解为「预计参与后续处理的行数」,再结合回表和排序一起看。Extra 里最值得警惕的词
Using filesort:需要额外排序。数据量小是内存排序,数据量大就落磁盘。出现在 ORDER BY 或 GROUP BY 不满足索引顺序时。不是「文件」的意思,是「额外排序」。
Using temporary:需要建临时表,常见于 GROUP BY 没有合适索引、DISTINCT、复杂子查询。实测 SELECT city, COUNT(*) FROM customers GROUP BY city,因为 city 没有索引,计划里出现 Using temporary。
Using index:覆盖索引,查询列全在索引里,不回表。这是好事,但要结合 type 看:如果是 type=index 加 Using index,说明在扫整棵索引。
Using index condition:索引下推,回表前先在索引里过滤。也是好事,减少回表次数。
Using where:服务器层还有过滤条件。它不等于「没走索引」,也不等于「慢」,只是说明部分条件是在取到行之后才判断的。
Backward index scan:MySQL 8.0 的优化,表示反向扫描索引来满足 DESC 排序。它避免了额外排序,是好事。
Using join buffer:JOIN 时被驱动表没有可用索引,用了连接缓冲区。数据量大时要警惕。
还有几个出现频率低但含义明确的词。Impossible WHERE 表示条件恒为假,优化器直接不读表,比如 WHERE 1 = 0;看到它多半是 SQL 拼接出了问题,不是优化成果。Select tables optimized away 表示 MIN/MAX 直接读索引端点就拿到结果,一行都不用扫,这类查询保持原样就好。Range checked for each record 是 JOIN 时被驱动表没有稳定索引,每来一行就重新判断该用哪个索引,代价随驱动表行数上升。Using MRR 表示先把要回表的主键收集起来排序再批量读,减少随机 IO,是否生效取决于 optimizer_switch 里的 mrr 开关。
一条完整计划怎么读
以这条查询为例:
EXPLAIN SELECT id, status, amount FROM orders
WHERE customer_id = 7
ORDER BY created_at DESC
LIMIT 10;
实测输出(整理成表格):
| type | key | key_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|
| ref | idx_customer_created | 4 | const | 20 | 100.00 | Using where; Backward index scan |
读法:用 idx_customer_created 的第一列 customer_id 定位(key_len=4),等值条件是常量 7(ref=const),预计扫 20 行;索引第二列 created_at 正好满足倒序,直接反向扫描(Backward index scan),不需要额外排序。Using where 是因为 customer_id 条件仍会在服务器层确认一次,但成本很低。整体是一份好计划。
再看一条需要警惕的:
EXPLAIN SELECT id, amount FROM orders
WHERE customer_id = 7
ORDER BY amount DESC
LIMIT 10;
实测 type=ref、key=idx_customer_created、rows=20、Extra=Using index condition; Using filesort。它用索引定位了客户,但排序字段 amount 不在索引里,只能把 20 行取出来再排。20 行无所谓,如果返回几千行,Using filesort 就会变成瓶颈。
四个常见误判
第一,「有 key 就是走索引」。要看 type,type=index 可能是全索引扫描。
第二,「Using where 说明没走索引」。不是。它只表示服务器层还有过滤,很多走了索引的查询也有 Using where。
第三,「rows 小就一定快」。rows 是估算,而且没算回表和排序。20 行回表 20 次可能比 200 行覆盖索引还慢。
第四,「EXPLAIN 的执行顺序就是 id 从小到大」。有子查询时,id 大的通常先执行。JOIN 的驱动表也不一定是写在前面那张。
顺带说两个能主动干预计划的工具。USE INDEX / FORCE INDEX 写在表名后面,能强制优化器选某个索引,适合「统计信息不准导致选错」的临时止血;但它把选择写死在 SQL 里,数据分布变了以后可能更糟,长期方案还是改索引或改 SQL。另一种是 8.0 的优化器提示,写成 /*+ JOIN_ORDER(o, c) */ 这样的注释,只影响这一条语句。无论用哪种,改完都要重新看一次 EXPLAIN,确认 type、rows、Extra 真的变了,而不是凭感觉认为「加了提示就会快」。
翻车现场:一份挑不出毛病的计划,用户还是等 3 秒
某次客服反馈「订单列表要转三秒」。开发把 EXPLAIN 截图发出来:type=ref、key=idx_customer_created、rows=20、filtered=100.00、Extra=Backward index scan,无论怎么看都是好计划。可接口就是慢,而且只在白天慢。
原因有三层,都不在截图的这一行里。第一层是回表:这条查询取了 status、amount,两列都不在索引里,20 行要回表 20 次。实测同样返回 20 行,覆盖索引读 11 个页,回表读 71 个页,差了 6 倍多;数据量大、缓冲池装不下时,这 71 个页里不少是随机 IO。
第二层是估算和实际不一致。rows=20 是「预计扫描 20 行」,但计划不会告诉你实际扫了多少、回表命中了多少、有多少页来自磁盘。8.0.12 没有 EXPLAIN ANALYZE,只能侧面验证:
FLUSH STATUS;
SELECT id, status, amount FROM orders
WHERE customer_id = 7 ORDER BY created_at DESC LIMIT 10;
SHOW SESSION STATUS LIKE 'Handler_read%';
SHOW SESSION STATUS LIKE 'Innodb_rows_read';
Handler_read_next 大致反映索引上顺次读了多少条,Handler_read_rnd_next 反映全表扫读了多少条,Innodb_rows_read 是 InnoDB 层实际读出的行数。这几个计数和执行计划对照,能看出「估算 20 行」和「实际读了多少」差多远。
第三层是锁等待。截图只描述访问路径,不描述等待。如果同一行正被另一个事务锁着,计划再好也要排队。前面实测过,持锁 3 秒时另一个更新请求 2.68 秒后报 1205;慢日志里那条 SQL 看起来「简单得不可能慢」,实际时间全花在等锁。
所以一份计划要配合三样东西看:实际耗时(慢日志或应用埋点)、实际读取量(Handler_read_*、Innodb_rows_read)、等待情况(data_lock_waits、innodb_trx)。只看 EXPLAIN,等于只看导航路线,不看路上堵不堵。
现象对照
| 现象 | 真实原因 | 第一步查什么 |
|---|---|---|
| 计划走了索引但依然慢 | type=index 全索引扫描,或返回行太多、回表太多 | 看 type、rows、Extra 三列 |
| 联合索引「全命中」却只用了一列 | 中间断了列,或第一列是范围条件 | 看 key_len 是否为全部列的字节和 |
计划里出现 Using temporary | GROUP BY/DISTINCT 没有可用索引 | 看分组列有没有索引,考虑补索引或改写 |
明明没排序却出现 Using filesort | GROUP BY 隐式排序,或 ORDER BY 不被索引满足 | 确认排序/分组字段与索引顺序 |
实际耗时和 rows 差很多 | 统计信息过期,或 rows 只是估算 | ANALYZE TABLE 后重看;8.0.18+ 用 EXPLAIN ANALYZE |
| 计划很好但接口还是慢 | 回表随机 IO、锁等待、缓存未命中,计划里看不到 | 对照慢日志、Handler_read_*、data_lock_waits |
估算 rows=20,实际读了几百行 | 统计信息偏差,或估算只算单次扫描不含回表 | ANALYZE TABLE 后复看,必要时用 Handler_read 计数 |
| 昨天好的计划今天变差 | 数据分布变化或索引被删/不可见 | 对比 key、possible_keys,检查索引可见性 |
边界与代价
EXPLAIN 展示的是优化器「打算怎么做」,不是「实际做了什么」。它不执行 SQL,所以看不到锁等待、磁盘临时表、实际返回行数这些运行期信息。要这些数据,得用 EXPLAIN ANALYZE(8.0.18+)、慢日志、performance_schema。
优化器的代价模型基于统计信息,统计信息不准就会选错计划。ANALYZE TABLE 能刷新,但大表上要扫描采样,有 IO 成本;频繁执行反而增加负担。InnoDB 会自动更新部分统计,但大批量变更后手动刷新一次仍然必要。
线上还有两个实用技巧。正在跑的慢 SQL 可以用 EXPLAIN FOR CONNECTION 123(123 是 SHOW PROCESSLIST 里的 Id)直接看它的计划,不用把 SQL 复制出来重跑,也不会额外执行一次。另一个是 SHOW WARNINGS:EXPLAIN 之后执行它,能看到优化器改写后的语句和转换警告,隐式类型转换、1739 这类问题就是从这里看到的。
EXPLAIN 不只用于 SELECT,也能加在 UPDATE、DELETE 前面,看它们准备扫多少行、走哪个索引。改数据的语句更需要提前确认范围——一条走错计划的批量更新,可能锁住整张表。加 EXPLAIN 不会真的执行修改,可以在预发环境放心跑。
EXPLAIN ANALYZE 的实测输出,因为本机是 8.0.12。如果你在 8.0.18 及以上,可以按同样的 SQL 验证:EXPLAIN ANALYZE SELECT id, status, amount FROM orders WHERE customer_id = 7 ORDER BY created_at DESC LIMIT 10; 应该看到每个节点带 actual time、actual rows、loops,Sort 节点若存在会显示实际排序行数。按官方文档,输出格式与本文表格不同,具体数值以你的环境为准,标记为待确认;8.0.18 之前只能靠 EXPLAIN 加计数器和计时间接判断。小结
看计划先看 type 和 rows 判断扫描方式与扫描量,再看 key、key_len 判断索引用了几列,最后看 Extra 判断有没有额外排序、临时表、回表。type=index 不是快,Using index 是覆盖索引,Using index condition 是索引下推,Using filesort 是额外排序。
还要记住计划只是估算。它不执行 SQL,不知道锁要等多久、页在不在缓存、临时表会不会落盘。计划能帮你排除「访问路径选错」,排除不了「运行环境的问题」,两者要分开查。
当天可以自己验证的练习:
- 对同一条查询分别取
id和多列,对比type、rows、Extra。 - 用
(a, b, c)联合索引,跑a、a+b、a+b+c,记录key_len。 - 跑一条
GROUP BY无索引列的查询,观察Using temporary。 - 跑
EXPLAIN FORMAT=JSON,找到query_cost和rows_examined_per_scan。 - 对比
EXPLAIN与真实执行耗时,思考估算和实际的差距。
下一篇讲线上慢查询怎么查。会看计划只是第一步,线上真正的难题是「不知道哪条 SQL 慢、慢在 SQL 还是锁、要不要立刻改」。接下来讲慢日志、performance_schema、正在等待的事务,以及止血和根治的顺序。