MySQL - EXPLAIN 执行计划

一条查询语句写下去之后，MySQL 会先经过查询优化器的一番处理——按成本和规则挑连接顺序、挑每张表的访问方式——最终生成一份"执行计划"。EXPLAIN就是用来把这份计划摊开给我们看的工具。这篇笔记按 EXPLAIN 输出的各个列逐一说明，并在容易混淆的地方配上示例。

目录

1. 为什么需要 EXPLAIN
2. id 与 table：这一行说的是谁
3. select_type：这个小查询是什么身份
4. type：单表访问方式的性能排行
5. possible_keys / key / key_len
6. ref / rows / filtered
7. Extra：常见提示解析
8. JSON 格式执行计划：看到真实成本
9. SHOW WARNINGS：查看语句被优化成什么样
10. 版本差异：5.7 与 8.0 不完全一样

一、为什么需要 EXPLAIN

在查询语句前面加上EXPLAIN，就能看到 MySQL 打算怎么执行这条语句：多表连接的顺序是什么、每张表用什么方式访问、预计要扫多少条记录等等。本文示例使用一个简化的订单场景：

CREATETABLEorders(idINTNOTNULLAUTO_INCREMENT,order_noVARCHAR(32),user_idINT,statusVARCHAR(20),provinceVARCHAR(50),cityVARCHAR(50),districtVARCHAR(50),remarkVARCHAR(255),PRIMARYKEY(id),UNIQUEKEYidx_order_no(order_no),KEYidx_user_id(user_id),KEYidx_status(status),KEYidx_area(province,city,district))ENGINE=InnoDB;CREATETABLEusers(idINTNOTNULLAUTO_INCREMENT,mobileVARCHAR(11),provinceVARCHAR(50),PRIMARYKEY(id),UNIQUEKEYidx_mobile(mobile),KEYidx_province(province))ENGINE=InnoDB;

假设两张表都存了几万条业务数据。

先跑一个完整的例子

在拆开每一列细看之前，先把一整份执行计划摆出来，心里有个整体的参照，后面每一节其实都是在解释这张表里的某一列。执行下面这条连接查询：

EXPLAINSELECT*FROMordersINNERJOINusersONorders.user_id=users.idWHEREorders.status='closed';

得到的执行计划长这样：

先不用管每一列具体怎么算出来的，只需要看懂这几个大方向：

• 两行的id都是 1，说明它们是同一条语句里的连接，不是两个独立的查询。
• table列先orders后users，意味着orders是驱动表，users是被驱动表——先按status = 'closed'筛出 orders 的记录，再拿每一条记录的user_id去 users 表里找对应的用户。
• 两行的type不一样：orders 是ref（走idx_status索引做等值匹配），users 是eq_ref（靠主键等值匹配）——这两个词具体什么意思，后面「type」那一节会细说。
• rows列告诉你规模：orders 预计要扫 4000 条，users 每次只扫 1 条（因为是主键等值匹配，最多命中一条）。

把这张表记在脑子里，接下来我们逐列拆开说，说的就是这张表里的某一格。

二、id 与 table：这一行说的是谁

table很直观，就是这条记录对应的表名。id稍微绕一点：一条语句里每出现一个SELECT关键字，就会分配一个唯一的 id。

连接查询虽然涉及多张表，但只有一个SELECT，所以id相同：

EXPLAINSELECT*FROMordersINNERJOINusersONorders.user_id=users.id;-- orders、users 两行记录的 id 都是 1-- 排在前面的 orders 是驱动表，排在后面的 users 是被驱动表

子查询、UNION 则会引入多个SELECT，id也跟着变多：

EXPLAINSELECT*FROMordersWHEREuser_idIN(SELECTidFROMusers)ORstatus='closed';-- orders（外层查询）id = 1-- users（子查询） id = 2

这里有个很实用的技巧：查询优化器经常会把子查询偷偷改写成连接查询，改没改写光看 SQL 看不出来，但看执行计划一目了然——如果相关表的id变成了同一个值，就说明发生了改写：

EXPLAINSELECT*FROMordersWHEREuser_idIN(SELECTidFROMusersWHEREprovince='北京市');-- orders、users 的 id 全都是 1，说明子查询被转成了连接查询

UNION 因为要去重，会额外借助一张临时表，执行计划里会多一行id为NULL、table显示成<union1,2>的记录；如果用的是不去重的UNION ALL，就不会有这一行。

三、select_type：这个小查询是什么身份

每个id对应的小查询，都会被贴上一个select_type标签，说明它在整条语句里扮演的角色：

其中SUBQUERY和DEPENDENT SUBQUERY最容易混淆，区别在于子查询是否依赖外层查询的值：

-- 不相关子查询：子查询能独立执行一次，结果被物化后复用 —— SUBQUERYEXPLAINSELECT*FROMordersWHEREuser_idIN(SELECTidFROMusers)ORstatus='closed';-- 相关子查询：子查询里引用了外层的 orders.province，必须跟着外层每一行重新算一遍 —— DEPENDENT SUBQUERYEXPLAINSELECT*FROMordersWHEREuser_idIN(SELECTidFROMusersWHEREusers.province=orders.province)ORstatus='closed';

DERIVED和MATERIALIZED也容易搞混，区别在于物化出来的表是被当成"派生表"直接查询，还是被拿去跟外层表做连接：

-- FROM 子句里的子查询本身被物化成一张临时表直接查询 —— DERIVEDEXPLAINSELECT*FROM(SELECTuser_id,COUNT(*)cFROMordersGROUPBYuser_id)tWHEREc>3;-- WHERE 子句里的子查询被物化后，再与外层表 orders 做连接查询 —— MATERIALIZEDEXPLAINSELECT*FROMordersWHEREuser_idIN(SELECTidFROMusers);

四、type：单表访问方式的性能排行

type这一列的取值本身就是一份性能排行榜，从好到差排开：

system → const → eq_ref → ref → fulltext → ref_or_null → index_merge → unique_subquery → index_subquery → range → index → ALL

下面按排行榜的顺序逐个说明。

system：表里只有一条记录，而且该表使用的存储引擎统计数据是精确的（比如 MyISAM、Memory，InnoDB 的行数是估算值，享受不到这个待遇）。假设我们另建一张 MyISAM 的配置表：

CREATETABLEsite_config(idINT)ENGINE=MyISAM;INSERTINTOsite_configVALUES(1);EXPLAINSELECT*FROMsite_config;-- type: system

const：主键或唯一索引与常量做等值匹配，一步到位：

EXPLAINSELECT*FROMordersWHEREid=1001;-- type: const

eq_ref：连接查询中，被驱动表靠主键/唯一索引做等值匹配访问（如果是联合唯一索引，则要求所有列都参与等值比较）——这是被驱动表能拿到的最好成绩：

EXPLAINSELECT*FROMordersINNERJOINusersONorders.user_id=users.id;-- users（被驱动表）的 type 是 eq_ref

ref：最常见的情形，普通二级索引的等值匹配：

EXPLAINSELECT*FROMordersWHEREuser_id=1001;-- type: ref

fulltext：走全文索引进行匹配。假设给remark列建了全文索引：

ALTERTABLEordersADDFULLTEXTINDEXidx_remark_ft(remark);EXPLAINSELECT*FROMordersWHEREMATCH(remark)AGAINST('春节 发货');-- type: fulltext

ref_or_null：在ref的基础上，索引列还允许匹配 NULL：

EXPLAINSELECT*FROMordersWHEREuser_id=1001ORuser_idISNULL;-- type: ref_or_null

index_merge：单张表同时用上了不止一个索引，走 Intersection / Union / Sort-Union 三种索引合并方式之一：

EXPLAINSELECT*FROMordersWHEREuser_id=1001ORstatus='closed';-- 分别可用 idx_user_id、idx_status，MySQL 把两次索引扫描的结果合并-- type: index_merge

unique_subquery：IN 子查询被转成 EXISTS 之后，子查询里的表如果靠主键做等值匹配访问，就是这个类型：

EXPLAINSELECT*FROMordersWHEREuser_idIN(SELECTidFROMusersWHEREusers.province=orders.province)ORstatus='closed';-- users 的 type: unique_subquery（转成 EXISTS 后按主键 id 等值匹配）

index_subquery：和unique_subquery类似，只是子查询里的表用的是普通二级索引而不是主键：

EXPLAINSELECT*FROMordersWHEREremarkIN(SELECTprovinceFROMusersWHEREusers.id=orders.user_id)ORstatus='closed';-- users 的 type: index_subquery（province 走的是普通索引 idx_province）

range：索引区间扫描：

EXPLAINSELECT*FROMordersWHEREuser_id>1000ANDuser_id<2000;-- type: range

index：用上了覆盖索引，但得把整个索引扫一遍（无法用 ref/range 缩小范围）：

EXPLAINSELECTcityFROMordersWHEREdistrict='海淀区';-- 查询列表 city、搜索条件 district 都在联合索引 idx_area 里，-- 但 district 排在联合索引的第 3 列，用不上 ref/range，只能扫完整个索引-- type: index

ALL：全表扫描，最没有效率的一种：

EXPLAINSELECT*FROMorders;-- type: ALL

记住一条规律就够用了：除了ALL，其余方法都在吃索引的红利；除了index_merge，其余方法一次最多只能用一个索引。

五、possible_keys / key / key_len

possible_keys是候选索引，key是优化器最终选定的索引：

EXPLAINSELECT*FROMordersWHEREuser_id>100000ANDstatus='closed';-- possible_keys: idx_user_id, idx_status-- key: idx_status —— 优化器算完成本后，觉得用 idx_status 更划算

候选索引不是越多越好，优化器要给每个候选都算一遍成本，候选太多反而拖慢优化过程本身，用不上的索引该删就删。

key_len看着像是在说存储占用，其实作用是让你能一眼看出联合索引到底吃上了几列。它的计算方式是：索引列本身占用的最大字节数 + （允许 NULL 则 +1）+ （变长类型固定 +2）。以联合索引idx_area(province, city, district)为例（各列VARCHAR(50)，utf8 字符集每字符 3 字节）：

-- 只用上联合索引的第 1 列，key_len = 153（50×3字节 +1可空 +2变长标记）EXPLAINSELECT*FROMordersWHEREprovince='北京市';-- key_len: 153-- 同时用上联合索引的前 2 列，key_len 直接翻倍EXPLAINSELECT*FROMordersWHEREprovince='北京市'ANDcity='朝阳区';-- key_len: 306

看到key_len从 153 变成 306，不用细算就知道：这次多吃上了一列索引。

六、ref / rows / filtered

ref告诉你等值匹配的对象是什么——常量、别的表的某一列，还是一个函数的结果：

EXPLAINSELECT*FROMordersWHEREuser_id=1001;-- ref: const （匹配一个常量）EXPLAINSELECT*FROMordersINNERJOINusersONorders.user_id=users.id;-- ref: shop.users.id （匹配另一张表的列）EXPLAINSELECT*FROMordersINNERJOINusersONusers.mobile=TRIM(orders.remark);-- ref: func （匹配一个函数的结果，索引效果打了折扣）

rows是优化器预估要扫的记录数，filtered是这些记录里还有多少比例能通过其余条件。单独看filtered意义不大，真正有用的地方是算驱动表的扇出：

EXPLAINSELECT*FROMordersINNERJOINusersONorders.user_id=users.idWHEREorders.status='closed';-- orders（驱动表）：rows = 20000, filtered = 5.00

驱动表 orders 的扇出 ≈20000 × 5% = 1000，意味着接下来大概要对被驱动表 users 访问 1000 次左右——扇出越大，被驱动表被访问的次数就越多，这也是优化器挑选驱动表时要考虑的核心指标之一。

七、Extra：常见提示解析

其中最值得展开的是索引条件下推（ICP）。回忆一下之前讲过的"回表"：二级索引查到记录后，要靠主键再去聚簇索引查一次才能拿到完整数据。如果搜索条件里，一部分能确定范围、另一部分虽然用不上范围查找、但好歹也是索引列，与其每扫到一条记录就急着回表，不如先在存储引擎层把这些索引相关的条件一次性判断完，不满足就直接跳过：

EXPLAINSELECT*FROMordersWHEREorder_no>'ORD20240000'ANDorder_noLIKE'%99';-- Extra: Using index condition-- order_no > 'ORD20240000' 能确定范围；order_no LIKE '%99' 用不上范围查找，但同属 order_no 列-- 两个条件都下推到存储引擎层一起判断，省掉大量无谓的回表

因为回表是二级索引特有的负担（聚簇索引本身就包含全部列），所以 ICP 只对二级索引有意义。凡是条件涉及的列不在当前索引里、必须等回表拿到完整记录才能判断的，会显示成Using where：

EXPLAINSELECT*FROMordersWHEREremark='春节延迟发货';-- Extra: Using where —— remark 没有索引，只能全表扫完后在 server 层挨个判断

Using temporary也值得留意，它出现在很多DISTINCT、GROUP BY场景里，说明 MySQL 得现造一张临时表来完成去重或分组：

EXPLAINSELECTstatus,COUNT(*)FROMordersGROUPBYstatus;-- Extra: Using temporary; Using filesort

这里有个容易被忽略的细节：GROUP BY默认会隐式带上ORDER BY，所以哪怕语句里没写排序，也会同时出现Using filesort。如果确实不需要排序，显式写上ORDER BY NULL就能把这个提示去掉：

EXPLAINSELECTstatus,COUNT(*)FROMordersGROUPBYstatusORDERBYNULL;-- Extra: Using temporary （Using filesort 消失了）

八、JSON 格式执行计划：看到真实成本

rows、filtered说到底都是估算，想知道优化器算出来的成本具体是多少，可以在EXPLAIN和查询语句之间加上FORMAT=JSON：

EXPLAINFORMAT=JSONSELECT*FROMordersINNERJOINusersONorders.user_id=users.idWHEREorders.status='closed';

输出里每张表都带一个cost_info：

"cost_info":{"read_cost":"980.32","eval_cost":"102.15","prefix_cost":"1082.47","data_read_per_join":"2M"}

不用深究read_cost、eval_cost各自怎么算的，只需要盯住prefix_cost——它是"截止到这张表为止"的累计成本，所以最后一张表的prefix_cost，就是整条查询预计的总成本，拿来对比不同写法孰优孰劣非常直接。

九、SHOW WARNINGS：查看语句被优化成什么样

EXPLAIN之后紧接着执行一句SHOW WARNINGS，如果返回的Code是 1003，Message会给出优化器重写后大致的样子：

EXPLAINSELECTorders.order_no,users.mobileFROMordersLEFTJOINusersONorders.user_id=users.idWHEREusers.mobileISNOTNULL;SHOWWARNINGS;-- Message 里 LEFT JOIN 变成了 JOIN-- 因为 users.mobile IS NOT NULL 这个条件，让左连接失去了保留 orders 未匹配行的意义-- 优化器索性把它优化成了普通内连接

需要注意的是，Message展示的只是帮助理解的参考，并不是能直接拿去执行的标准 SQL。

十、版本差异：5.7 与 8.0 不完全一样

前面九节说的都是 MySQL 5.7 上的行为，8.0 有几处不一样，值得单独提一下：

被驱动表访问方式的变化：Hash Join 取代了 Block Nested Loop

前面举过的例子——被驱动表用不上索引，只能靠Using join buffer (Block Nested Loop)兜底——这是 5.7 的说法。从 MySQL 8.0.20 开始，优化器对无法使用索引的连接查询，默认改用Hash Join算法，同样的语句在 8.0.20+ 上执行，Extra里大概率会显示成：

EXPLAINSELECT*FROMordersINNERJOINusersONorders.remark=users.mobile;-- 5.7 Extra: Using join buffer (Block Nested Loop)-- 8.0.20+ Extra: Using join buffer (hash join)

两者都是"没用上索引、只能靠内存做暴力匹配"的信号，但 Hash Join 通常比 Block Nested Loop 效率更高，这也是 8.0 优化器的一处实打实的改进。

EXPLAIN ANALYZE：从"预估"到"实测"

前面九节里的rows、filtered、cost_info说到底都是优化器的预估值，实际执行时可能有偏差。MySQL 8.0.18 起新增了EXPLAIN ANALYZE，会真的执行这条语句，返回每一步实际扫描的行数、实际耗时：

EXPLAINANALYZESELECT*FROMordersWHEREuser_id=1001;-- 输出里能看到 actual time=... rows=... loops=... 这类真实执行数据-- 而不再是 EXPLAIN 那种"优化器觉得大概是这样"的估算

如果发现EXPLAIN里的rows、filtered和实际情况明显对不上（比如统计信息过期了），EXPLAIN ANALYZE是 8.0 下更可靠的排查手段。5.7 没有这个语法。

其他小差异：8.0 引入了直方图（histogram）统计信息，能让rows/filtered的估算在数据分布不均匀时更准；早期版本要看这两列必须加EXPLAIN EXTENDED/EXPLAIN PARTITIONS，从 5.7 起才默认随EXPLAIN一起展示——这也是本文第五、六两节内容成立的版本前提。

把这几列串起来看：table/id/select_type告诉你这一行说的是哪张表、属于哪个查询；type/possible_keys/key/key_len告诉你这张表打算怎么被访问；ref/rows/filtered告诉你访问的规模有多大；Extra补充那些没地方安放的细节。把这套读法练熟，看一眼执行计划基本就能判断一条慢查询卡在哪一步。