MySQL EXPLAIN执行计划详解：从字段含义到慢查询优化实战

上周接了个线上慢接口排查运营打开订单列表页面要卡将近十秒。我拉出慢查询日志锁定几条SQL后第一件事就是给它们加上EXPLAIN看执行计划。MySQL explain执行计划是所有SQL优化绕不开的入口它把优化器最终选择的路径摊开给你看哪张表做了全表扫描、哪个索引真正被用上、有没有临时表、排序发生在哪里。这篇文章把explain输出里的常用关键字逐个拆开讲一遍再配一个我实际优化过的案例说明每个字段到底在传递什么信息。适合刚开始做慢查询优化的开发同学也适合想系统梳理执行计划知识的运维朋友。1. 一句EXPLAIN帮我救回慢接口执行计划到底在做什么1.1 拿到的第一张图纸在MySQL里敲一条语句EXPLAIN SELECT o.order_id, u.user_name FROM orders o JOIN users u ON u.id o.user_id WHERE o.status 1 ORDER BY o.create_time DESC LIMIT 20;这里有个很重要的认知EXPLAIN并不会真正执行你的查询它只是让优化器告诉你——按当前的表结构、索引情况、统计信息我打算怎么去取这批数据。它输出的每一行对应查询计划里的一个步骤。你可以把这份输出当成一张流水线图纸哪个环节先启动、哪张表通过什么方式定位数据、扫出来之后还要进一步过滤多少、最终是否额外排序。举个例子如果某一行显示typeALL说明优化器打算全表扫描。如果一张500万行的表在typeALL的情况下还要参加JOIN慢几乎是必然的。通过这张图纸我们能快速判断慢SQL的瓶颈点是缺索引、索引失效还是驱动表选错。1.2 输出列快速归类EXPLAIN默认输出的列不算少但如果按关注点分组就很好记。分类列关注点连接顺序类id、select_type、table查询步骤先后、子查询还是普通表索引使用类possible_keys、key、key_len、ref优化器用了哪个索引、用到联合索引哪几列代价与附加操作类rows、filtered、Extra预扫行数、过滤比例、是否排序/临时表分区相关partitions命中的分区分区表场景我在带新人看执行计划时经常发现大家只盯key这一列看到不是NULL就觉得索引用上了。实际上key非空只是说明用了索引key_len代表用到索引的哪一部分Extra里可能还藏着Using filesort这样的大坑。所以后面几章我会按这三组来拆开讲。2. id、select_type、table先搞清楚步骤顺序2.1 id决定执行先后id列的数字表达的含义很直接id相同属于同一次连接操作按表格从上到下顺序执行。id不同数字越大越先执行。因为子查询必须先算出结果外层查询才有办法继续。id为NULL通常出现在合并结果集的步骤最典型的是UNION RESULT它本身不是具体表访问而是把多个查询结果合并。我见过一个容易出现理解反了的场景SQL里写了一个子查询比如WHERE user_id IN (SELECT id FROM vip_user WHERE level3)执行计划里往往子查询那行id2外层主查询id1。优化器会先执行id大的子查询部分再执行外层。所以读执行计划第一步不是从第一行往下念而是先按id重新排序。2.2 select_type常见值对照select_type描述这一行查询的类型常用值基本就这些select_type含义典型场景SIMPLE简单查询普通单表或简单JOIN没有子查询和UNIONPRIMARY最外层查询存在子查询时的外层主查询SUBQUERY普通子查询SELECT或WHERE里的独立子查询DEPENDENT SUBQUERY相关子查询子查询依赖外层字段外层每行都要执行一次DERIVED派生表FROM子句里的子查询生成的临时结果集UNIONUNION中的后半部分第二个及以后的SELECTUNION RESULTUNION合并结果合并多个UNION结果集的步骤MATERIALIZED物化子查询IN子查询被优化器物化成临时表这里我想提醒一句DEPENDENT SUBQUERY往往比想象中慢。因为它和外层的每一行都有关联外层扫出多少行子查询可能就要执行多少次。遇到这种执行计划优先考虑改写成JOIN。2.3 table列里的怪名字table列看起来简单但经常出现一些奇怪写法。SQL里写了别名这里就显示别名如果没有别名就显示原表名。真正需要注意的是尖括号里的内容derived2表示这一行访问的是id2那个派生表的结果union1,2表示合并id1和id2两个结果集subquery3表示访问id3那个物化子查询的临时表。看到这些名字不要觉得是表名错误它们指的都是查询计划内部临时产生的数据集。如果临时表数据量很大也会成为新的性能瓶颈这时候就要回头优化子查询本身。3. type字段优化器选择的访问路线3.1 一条路快慢对照表type是执行计划里信息量最大的一列它描述的是MySQL从表里找数据的方式。网上喜欢把访问类型排成一个金字塔从上到下大致是类型含义典型场景评价system表只有一行系统表或虚拟表最快const主键或唯一键等值匹配WHERE id1最快级eq_ref被驱动表通过主键/唯一键等值连接JOIN的关联列是唯一索引很快ref非唯一索引等值匹配WHERE status1且status有普通索引不错ref_or_nullref外加上IS NULL条件等值或NULL查询可以range索引范围扫描BETWEEN、、、IN能接受index全索引扫描遍历整棵索引树比全表好一点ALL全表扫描无索引或优化器放弃索引最危险这个顺序我会在团队里反复强调**目标是让type到达range以上最好落在ref、eq_ref、const。**如果看到ALL或index基本可以断定这条SQL还有优化空间。3.2 等值匹配的分水岭const、eq_ref、ref三者都是等值匹配但细节不一样。const要求主键或唯一索引等值匹配所以结果最多一行。比如WHERE user_id10086user_id是主键。eq_ref多出现在JOIN场景被驱动表的关联列是主键或唯一索引。比如users u JOIN orders o ON u.id o.user_id优化器认为外层传过来一个值后内层最多匹配一行这种访问方式非常高效。ref是普通索引等值匹配比如WHERE status1status上面有普通二级索引。一个索引值可能对应很多行所以效率取决于匹配行数。我见过有人在JOIN条件两边都加了索引但type还是ALL的情况。多半是因为关联列上虽然有索引但不是唯一索引而且数据分布让优化器觉得走索引不如全表扫。这时候光看字段有没有索引不够还得看基数。3.3 range之后要警惕什么range表示索引范围扫描一般出现在BETWEEN、、、LIKE abc%或者IN大批量值的场景。range虽然用了索引但扫描的行数可能很多。比如WHERE create_time BETWEEN 2024-01-01 AND 2024-01-31如果这一个月有100万行就算走了索引代价也不小。index是遍历整个索引树常见于覆盖索引查询比如查询列全在某个索引里优化器认为遍历索引树比回表快。很多人看到typeindex觉得不是ALL就放心了其实index和ALL的差别只是前者读的是索引文件后者读的是数据文件扫描量依旧是全量。如果一张表的索引树很大typeindex一样慢。ALL就是全表扫描通常意味着这一列没有可用索引、条件没法走索引或者优化器评估后发现走索引代价还不如全表扫。常见的造成ALL的情况对索引列做函数运算、隐式类型转换、使用前导通配符LIKE %xxx、OR条件断裂等。后面实战章节会具体看到。4. possible_keys/key/key_len/ref索引用没用、用到哪一节4.1 四个字段要连起来看这一组字段描述索引选择的全过程。possible_keysMySQL认为可能用上的索引列表key优化器最终选择使用的索引key_len实际使用的索引字节数ref哪些列或常量参与了这个索引的等值匹配。有种常见情况possible_keys有值key是NULL。这说明优化器评估过候选索引但认为不值得使用。遇到这种我会先查一下统计信息是否过期多执行几次ANALYZE TABLE再看如果还不用就要检查条件里的数据分布是不是太平均了或者索引列上有隐式类型转换。还有一种情况key非空但key_len很小。比如联合索引(name, age)实际SQL只用了name条件key_len就只会有name的长度说明联合索引没有完全吃满。用一个具体例子演示一下推算过程。4.2 用key_len判断联合索引吃到第几列假设用户表结构如下CREATE TABLE user ( id int NOT NULL, name varchar(20) NOT NULL, age int DEFAULT NULL, PRIMARY KEY (id), KEY idx_name_age (name,age) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;执行EXPLAIN SELECT * FROM user WHERE name 张三 AND age 30;key_len会是87这个数怎么算出来的name是varchar(20)字符集utf8mb4下每个字符最多4字节所以是20*480。varchar属于变长字段需要2字节记录实际长度所以加2等于82。name又是NOT NULL不用额外加可空标记。age是int固定4字节它允许NULL还要再加1字节表示是否为空所以是5。两者相加82 5 87。如果SQL只写WHERE name 张三不写agekey_len就会变成82。也就是说直接用key_len的变化就能判断联合索引吃到第几列。这个技巧对排查明明建了联合索引但优化器只用了前面一列的问题非常实用。有个细节再强调一遍**变长字段加2可空字段加1。**如果是int类型可空算出来是5而不是4如果是varchar可空在字符数×4之外还要加3。用错误公式推算后面定位问题会对不上别小看这点出入。4.3 rows与filtered的估算游戏rows是优化器预估要扫描的行数注意是预估不代表实际扫了多少行。filtered表示存储引擎返回数据后server层再过滤时预计剩余的比例MySQL 5.7以后才有。两者连起来可以估算出最终返回多少行rows × filtered / 100。我实测过rows有时会和真实行数差一个数量级。这在统计信息不准、字段之间有关联性的情况里特别常见。如果你发现预估行数严重离谱先跑一遍ANALYZE TABLE更新统计信息再看看执行计划有没有变化。另外配合SHOW INDEX FROM 表名看Cardinality可以理解为什么优化器会选错索引。5. Extra里的信号词哪种情况要紧张5.1 让人开心的Using indexUsing index意味着覆盖索引也就是查询需要的列都包含在索引里不需要回到聚簇索引主表取其他字段。这是执行计划里少见的好消息。比如执行EXPLAIN SELECT id, name FROM user WHERE name 张三;如果idx_name_age只包含 name、age不包含 id那id作为主键也会被附带进二级索引里所以这个查询可以做到Using index。但如果是SELECT *二级索引里没有的字段就要回表Extra就不再是Using index。工作中我常利用覆盖索引来减少回表开销。尤其在报表查询这种需要刷大量行的场景把SELECT的列尽量收进索引收益非常明显。5.2 让人头疼的Using filesort和Using temporaryUsing filesort不是传统意义上的文件排序它表示MySQL需要额外做一次排序操作而这次排序没法通过索引顺序直接获得。排序可能在内存做也可能落到磁盘amount大的情况下会把查询拖得很慢。举例EXPLAIN SELECT * FROM user WHERE age 20 ORDER BY create_time DESC;如果age有索引但create_time没有执行计划里就很容易出现Using filesort。解决思路是让排序字段成为某个索引的后半部分并保持ASC/DESC方向一致。这里有个容易踩的细节范围查询在前、排序字段在后面排序字段可能依然无法使用索引有序性这就是常说的范围列之后的索引列失效问题。Using temporary表示查询使用了临时表常见于GROUP BY、DISTINCT、ORDER BY与GROUP BY字段不一致、多表 JOIN 后的去重等场景。临时表如果数据量大还可能被放到磁盘上那代价就高了。看到这个信号优先检查能不能让分组或去重字段走索引把临时表消除。5.3 其他需要认识的Extra值Using where存储引擎返回记录后server层还需要再做一次条件过滤。如果type是ALL并且出现Using where意味着它全表扫完再过滤隐患很大。Using index condition用了索引条件下推ICP把部分where条件下推到存储引擎层减少回表次数。现在MySQL优化器经常自动使用算正向信号。Using join buffer说明JOIN过程中被驱动表没有利用索引优化器只能用join buffer去一段段匹配。看到这个值优先检查JOIN关联列有没有索引。Impossible WHERE条件本身矛盾比如WHERE 12优化器判断结果一定为空。Select tables optimized away优化器发现无需访问表就能出结果比如直接基于常量计算。Backward index scan在MySQL 8.0里反向扫描索引用于降序排序看到它不用慌。我说一句长期体感Extra最容易骗人。Using where看起来无害但结合type是ALL就完全不同。所以每次判断都别只看单列要type、key_len、rows、Extra四个字段一起看才能还原真正的执行过程。6. 实战复盘订单列表从十秒到几十毫秒6.1 第一版执行计划的问题背景是一张订单主表orders大约500万行另外关联用户表users。原始SQL类似这样SELECT o.order_id, o.total_amount, u.user_name FROM orders o LEFT JOIN users u ON u.id o.user_id WHERE o.status 1 AND o.create_time BETWEEN 2024-01-01 AND 2024-01-31 ORDER BY o.create_time DESC LIMIT 20;第一版执行计划大概是表typekeyrowsExtraordersALLNULL523万Using where; Using filesortusersALLPRIMARY88万Using join buffer一眼看出两个严重问题orders全表扫描并做文件排序users作为LEFT JOIN的被驱动表居然还在全表匹配直接从join buffer里捞数。这条SQL慢的核心不是单条过滤而是整表扫完还要排一次序最后取20条。6.2 索引调整和第二版执行计划我先确认status的区分度其实不高它只有几种状态值单独建索引意义不大。真正需要的是把过滤、排序、回表三件事统筹起来。经过调整在orders表上建了联合索引ALTER TABLE orders ADD INDEX idx_status_time (status, create_time);这个索引的设计逻辑是status1是等值条件放到前面create_time是范围条件同时它又是排序字段放在等值列后面可以让ORDER BY直接利用索引顺序去掉filesort。第二版执行计划明显不同表typekeyrowsExtraordersrangeidx_status_time约21000Using index conditionuserseq_refPRIMARY1无rows从523万降到2万左右users的访问也变成了eq_ref。实际接口响应时间从九秒多降到不到80毫秒效果立竿见影。这里有个经验**不要一上来就给WHERE里的每个列单独建索引要优先考虑联合索引的列顺序。**等值条件放前范围条件放后排序字段尽量融入索引。6.3 中间踩到的两个坑第一个坑是OR条件把range打断了。同事后来在查询里加了一个OR o.status 2执行计划立刻退化type从range变回ALL。原因很简单优化器认为OR两边很难共用同一个索引范围直接放弃索引。这个场景把它拆成两条SQL再UNION ALL比硬写OR要稳。第二个坑是隐式类型转换。有一张表的status字段是varchar(20)查询里写成WHERE status 1数字没加引号。MySQL会对 varchar 列做类型转换导致该列索引失效type回落到ALL。排错时发现key_len比预期短进而怀疑字段类型一查果然是隐式转换。这提醒我看到possible_keys有索引但key为空时优先检查类型转换和函数处理。7. 别停在EXPLAINJSON格式、真实执行和优化器追踪7.1 FORMATJSON和FORMATTREEEXPLAIN支持多种输出格式日常用表格格式最直观但需要更细信息时可以加参数EXPLAIN FORMATJSON SELECT ...; EXPLAIN FORMATTREE SELECT ...;JSON格式里能看到cost_info包括read_cost、eval_cost、prefix_cost是优化器评估各种执行方案代价的主要依据。同时它会把used_key_parts、attached_condition等细节列出来适合研究优化器为什么选择这个索引而不是另一个。TREE格式更接近优化器的执行树阅读体验比表格更顺适合在终端里快速查看多层子查询的执行路线。7.2 EXPLAIN ANALYZE能给你真实数据MySQL 8.0.18之后增加了EXPLAIN ANALYZE它会真正执行SQL并输出每一步的实际耗时、实际行数、循环次数。例如EXPLAIN ANALYZE SELECT ...;输出里会有类似actual time0.2..1.5 rows100 loops1的信息。这比默认模式的预估值更接近真相因为它是真实执行结果。这里我一定要强调**它会真实执行查询。**在生产环境对着一条1000万行表的SQL跑EXPLAIN ANALYZE等于把这条慢查询真正跑了一遍可能把数据库压垮。所以只建议在测试库或数据量可控的实例上用。默认的EXPLAIN不执行SQL只是估算这两者有本质区别。7.3 优化器为什么放弃索引optimizer_trace有时候我们百思不得其解明明建了很合适的索引优化器就是不走。这时候可以用优化器追踪功能SET optimizer_traceenabledon; SELECT ...; SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;它会记录优化器在解析JOIN顺序、选择索引、计算代价时的完整判断过程。我看得最多的几个点是considered_execution_plans、chosen_range_access_summary、拒绝某个索引时的cause字段。很多看起来反直觉的选择背后其实都是统计信息和索引基数的权衡结果。7.4 我的日常调优闭环我现在处理慢SQL的顺序基本固定先开慢查询日志定位SQL再看表的整体行数和索引结构接着跑EXPLAIN如果判断不清就上FORMATJSON或者测试库的EXPLAIN ANALYZE改完索引后再重跑一次执行计划对比type、key_len、rows、Extra的变化。explain不是撒手锏但它帮助建立了看索引有没有用对的手感。我的个人体会是所有调优动作做完一定要用真实的SQL执行耗时作为最终验证指标执行计划变好而实际没变快的情况虽然少可一旦遇到多半是统计信息或者查询里还有优化器没算到的附加操作。