MySQL - EXPLAIN 执行计划

一条查询语句写下去之后MySQL 会先经过查询优化器的一番处理——按成本和规则挑连接顺序、挑每张表的访问方式——最终生成一份执行计划。EXPLAIN就是用来把这份计划摊开给我们看的工具。这篇笔记按 EXPLAIN 输出的各个列逐一说明并在容易混淆的地方配上示例。目录为什么需要 EXPLAINid 与 table这一行说的是谁select_type这个小查询是什么身份type单表访问方式的性能排行possible_keys / key / key_lenref / rows / filteredExtra常见提示解析JSON 格式执行计划看到真实成本SHOW WARNINGS查看语句被优化成什么样版本差异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))ENGINEInnoDB;CREATETABLEusers(idINTNOTNULLAUTO_INCREMENT,mobileVARCHAR(11),provinceVARCHAR(50),PRIMARYKEY(id),UNIQUEKEYidx_mobile(mobile),KEYidx_province(province))ENGINEInnoDB;假设两张表都存了几万条业务数据。先跑一个完整的例子在拆开每一列细看之前先把一整份执行计划摆出来心里有个整体的参照后面每一节其实都是在解释这张表里的某一列。执行下面这条连接查询EXPLAINSELECT*FROMordersINNERJOINusersONorders.user_idusers.idWHEREorders.statusclosed;得到的执行计划长这样idselect_typetabletypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEordersrefidx_statusidx_status63const4000100.00NULL1SIMPLEuserseq_refPRIMARYPRIMARY4shop.orders.user_id1100.00NULL先不用管每一列具体怎么算出来的只需要看懂这几个大方向两行的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_idusers.id;-- orders、users 两行记录的 id 都是 1-- 排在前面的 orders 是驱动表排在后面的 users 是被驱动表子查询、UNION 则会引入多个SELECTid也跟着变多EXPLAINSELECT*FROMordersWHEREuser_idIN(SELECTidFROMusers)ORstatusclosed;-- 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标签说明它在整条语句里扮演的角色取值含义SIMPLE不含 UNION 或子查询的查询含普通连接查询PRIMARY大查询中最左边最外层的查询UNIONUNION/UNION ALL 中除最左边外的其余查询UNION RESULT为 UNION 去重而建的临时表对应的查询SUBQUERY不相关子查询且被物化执行DEPENDENT SUBQUERY相关子查询依赖外层查询的值DEPENDENT UNION依赖外层查询的 UNION 中非最左查询DERIVED以物化方式执行的派生表FROM 子句中的子查询MATERIALIZED子查询被物化后再与外层做连接其中SUBQUERY和DEPENDENT SUBQUERY最容易混淆区别在于子查询是否依赖外层查询的值-- 不相关子查询子查询能独立执行一次结果被物化后复用 —— SUBQUERYEXPLAINSELECT*FROMordersWHEREuser_idIN(SELECTidFROMusers)ORstatusclosed;-- 相关子查询子查询里引用了外层的 orders.province必须跟着外层每一行重新算一遍 —— DEPENDENT SUBQUERYEXPLAINSELECT*FROMordersWHEREuser_idIN(SELECTidFROMusersWHEREusers.provinceorders.province)ORstatusclosed;DERIVED和MATERIALIZED也容易搞混区别在于物化出来的表是被当成派生表直接查询还是被拿去跟外层表做连接-- FROM 子句里的子查询本身被物化成一张临时表直接查询 —— DERIVEDEXPLAINSELECT*FROM(SELECTuser_id,COUNT(*)cFROMordersGROUPBYuser_id)tWHEREc3;-- 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、MemoryInnoDB 的行数是估算值享受不到这个待遇。假设我们另建一张 MyISAM 的配置表CREATETABLEsite_config(idINT)ENGINEMyISAM;INSERTINTOsite_configVALUES(1);EXPLAINSELECT*FROMsite_config;-- type: systemconst主键或唯一索引与常量做等值匹配一步到位EXPLAINSELECT*FROMordersWHEREid1001;-- type: consteq_ref连接查询中被驱动表靠主键/唯一索引做等值匹配访问如果是联合唯一索引则要求所有列都参与等值比较——这是被驱动表能拿到的最好成绩EXPLAINSELECT*FROMordersINNERJOINusersONorders.user_idusers.id;-- users被驱动表的 type 是 eq_refref最常见的情形普通二级索引的等值匹配EXPLAINSELECT*FROMordersWHEREuser_id1001;-- type: reffulltext走全文索引进行匹配。假设给remark列建了全文索引ALTERTABLEordersADDFULLTEXTINDEXidx_remark_ft(remark);EXPLAINSELECT*FROMordersWHEREMATCH(remark)AGAINST(春节 发货);-- type: fulltextref_or_null在ref的基础上索引列还允许匹配 NULLEXPLAINSELECT*FROMordersWHEREuser_id1001ORuser_idISNULL;-- type: ref_or_nullindex_merge单张表同时用上了不止一个索引走 Intersection / Union / Sort-Union 三种索引合并方式之一EXPLAINSELECT*FROMordersWHEREuser_id1001ORstatusclosed;-- 分别可用 idx_user_id、idx_statusMySQL 把两次索引扫描的结果合并-- type: index_mergeunique_subqueryIN 子查询被转成 EXISTS 之后子查询里的表如果靠主键做等值匹配访问就是这个类型EXPLAINSELECT*FROMordersWHEREuser_idIN(SELECTidFROMusersWHEREusers.provinceorders.province)ORstatusclosed;-- users 的 type: unique_subquery转成 EXISTS 后按主键 id 等值匹配index_subquery和unique_subquery类似只是子查询里的表用的是普通二级索引而不是主键EXPLAINSELECT*FROMordersWHEREremarkIN(SELECTprovinceFROMusersWHEREusers.idorders.user_id)ORstatusclosed;-- users 的 type: index_subqueryprovince 走的是普通索引 idx_provincerange索引区间扫描EXPLAINSELECT*FROMordersWHEREuser_id1000ANDuser_id2000;-- type: rangeindex用上了覆盖索引但得把整个索引扫一遍无法用 ref/range 缩小范围EXPLAINSELECTcityFROMordersWHEREdistrict海淀区;-- 查询列表 city、搜索条件 district 都在联合索引 idx_area 里-- 但 district 排在联合索引的第 3 列用不上 ref/range只能扫完整个索引-- type: indexALL全表扫描最没有效率的一种EXPLAINSELECT*FROMorders;-- type: ALL记住一条规律就够用了除了ALL其余方法都在吃索引的红利除了index_merge其余方法一次最多只能用一个索引。五、possible_keys / key / key_lenpossible_keys是候选索引key是优化器最终选定的索引EXPLAINSELECT*FROMordersWHEREuser_id100000ANDstatusclosed;-- 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 15350×3字节 1可空 2变长标记EXPLAINSELECT*FROMordersWHEREprovince北京市;-- key_len: 153-- 同时用上联合索引的前 2 列key_len 直接翻倍EXPLAINSELECT*FROMordersWHEREprovince北京市ANDcity朝阳区;-- key_len: 306看到key_len从 153 变成 306不用细算就知道这次多吃上了一列索引。六、ref / rows / filteredref告诉你等值匹配的对象是什么——常量、别的表的某一列还是一个函数的结果EXPLAINSELECT*FROMordersWHEREuser_id1001;-- ref: const 匹配一个常量EXPLAINSELECT*FROMordersINNERJOINusersONorders.user_idusers.id;-- ref: shop.users.id 匹配另一张表的列EXPLAINSELECT*FROMordersINNERJOINusersONusers.mobileTRIM(orders.remark);-- ref: func 匹配一个函数的结果索引效果打了折扣rows是优化器预估要扫的记录数filtered是这些记录里还有多少比例能通过其余条件。单独看filtered意义不大真正有用的地方是算驱动表的扇出EXPLAINSELECT*FROMordersINNERJOINusersONorders.user_idusers.idWHEREorders.statusclosed;-- orders驱动表rows 20000, filtered 5.00驱动表 orders 的扇出 ≈20000 × 5% 1000意味着接下来大概要对被驱动表 users 访问 1000 次左右——扇出越大被驱动表被访问的次数就越多这也是优化器挑选驱动表时要考虑的核心指标之一。七、Extra常见提示解析提示含义Using index覆盖索引无需回表Using index condition索引条件下推ICP见下方示例Using where有条件需要在 server 层判断Using join buffer (Block Nested Loop)被驱动表无法有效利用索引改用内存块做嵌套循环Using filesort排序无法用索引完成需要文件排序Using temporary需要借助内部临时表完成去重/分组Not exists外连接 IS NULL 场景下的优化提前收工Using intersect(…) / union(…) / sort_union(…)三种索引合并策略Start temporary / End temporarysemi-join 的 DuplicateWeedout 策略LooseScansemi-join 的 LooseScan 策略FirstMatch(tbl_name)semi-join 的 FirstMatch 策略其中最值得展开的是索引条件下推ICP。回忆一下之前讲过的回表二级索引查到记录后要靠主键再去聚簇索引查一次才能拿到完整数据。如果搜索条件里一部分能确定范围、另一部分虽然用不上范围查找、但好歹也是索引列与其每扫到一条记录就急着回表不如先在存储引擎层把这些索引相关的条件一次性判断完不满足就直接跳过EXPLAINSELECT*FROMordersWHEREorder_noORD20240000ANDorder_noLIKE%99;-- Extra: Using index condition-- order_no ORD20240000 能确定范围order_no LIKE %99 用不上范围查找但同属 order_no 列-- 两个条件都下推到存储引擎层一起判断省掉大量无谓的回表因为回表是二级索引特有的负担聚簇索引本身就包含全部列所以 ICP 只对二级索引有意义。凡是条件涉及的列不在当前索引里、必须等回表拿到完整记录才能判断的会显示成Using whereEXPLAINSELECT*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和查询语句之间加上FORMATJSONEXPLAINFORMATJSONSELECT*FROMordersINNERJOINusersONorders.user_idusers.idWHEREorders.statusclosed;输出里每张表都带一个cost_infocost_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是 1003Message会给出优化器重写后大致的样子EXPLAINSELECTorders.order_no,users.mobileFROMordersLEFTJOINusersONorders.user_idusers.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.remarkusers.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_id1001;-- 输出里能看到 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补充那些没地方安放的细节。把这套读法练熟看一眼执行计划基本就能判断一条慢查询卡在哪一步。