1. 为什么我把EXPLAIN当作SQL优化的首要工具先讲个真实场景。前两年我接手了一个电商后台的订单查询接口线上偶发超时监控里能看到数据库CPU偶尔飙到80%以上。排查时第一反应不是去看代码而是把那条订单列表SQL拿出来在前面加了个EXPLAIN关键字跑了一遍。结果让人一惊明明订单表才两百万行查询计划却显示要扫描全表预估扫描行数接近一百万行Extra字段里还飘着一个刺眼的Using filesort。而这条SQL应用层的索引设计看起来是齐的——where条件里的字段建了索引order by的字段也建了索引。问题就出在索引字段的排列顺序上单看“有没有索引”完全看不出来但EXPLAIN一看key_len和type就全明白了。这件事之后我基本把EXPLAIN当成SQL优化的第一道工序。EXPLAIN说白了就是MySQL对一条SQL语句生成的一份“查询执行计划说明书”它不会真正去执行这条SQL除非你用了EXPLAIN ANALYZE在8.0版本里会实际执行而是告诉你优化器会按照什么顺序、用什么方式、扫描多少行、用到哪些索引来完成这次查询。你看懂了这份说明书就相当于在SQL真正跑之前就知道它会怎么走哪里慢、哪里能改一目了然。这篇内容我打算把EXPLAIN的每个核心字段拆开讲透重点放在type、key_len、rows、Extra这几个直接影响性能判断的字段上。适合的读者包括刚接触数据库优化、面对慢SQL不知道从哪里下手的后端开发也适合已经在用EXPLAIN但只停留在“看到ALL就慌”阶段的同学。毕竟EXPLAIN的输出有十几列每一列单独看不难难的是把它们串起来读出完整故事。需要先说明的一点不同MySQL版本的EXPLAIN输出略有差异。5.6、5.7版本里常见的字段包括id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra5.7还多了filtered和partitions8.0在此基础上部分版本支持EXPLAIN ANALYZE和JSON格式输出。下面的分析以5.7和8.0的主流行为为基准涉及版本差异的地方我会单独说明。2. 从id和select_type开始理解查询计划的“执行顺序”很多人扫EXPLAIN结果的时候会下意识先看type和key把id、select_type、table当成分隔符一样草草略过。这种习惯需要改一下。id和select_type其实决定了你对整条SQL的解读顺序尤其是遇到子查询、UNION、派生表这些复杂结构时先看这三列能把执行计划的行与行之间的逻辑关系理清楚。2.1 id不是越大越靠前执行而是越小越先执行id这个字段是SELECT的标识符MySQL会为每个SELECT顶层查询、子查询、派生表、UNION分支分配一个编号。关键理解点是id越大越先被“架子”搭起来id越小越先真正执行。更严谨地说输出结果中id相同的行是一组这组内部的表连接顺序由optimizer决定id不同的行之间按照id从大到小的顺序优先“生成”但实际读取数据时是从小到大取。这个逻辑听起来有点绕。我举个嵌套子查询的例子。一条SQL是SELECT * FROM orders WHERE user_id IN ( SELECT id FROM users WHERE status 1 );EXPLAIN的结果大致是两行第一行id为1table是orderstype可能是ALL或ref第二行id为2table是users。执行顺序上MySQL会先构造id2这个子查询填充物料的步骤但如果你只看到这个结论容易误以为先执行users做子查询再去连orders。在5.6版本之前MySQL确实会物理上先物化子查询结果但5.6及之后的优化器可能会把这种IN子查询改写成semi-join实际执行时会先扫描orders再到users里去做匹配。这就是为什么不能只看id大小判断“谁先跑”——它代表的是语法结构上的层级不是执行顺序的绝对标准。实际优化中我反而很少单独看id更多是拿id来对输出行进行“分组边界”的划分。比如一个大SQL里出现了id为1、2、3三种值我会逐个分组去看确认每个子查询是否独立、是否产生了临时表再判断能不能把嵌套子查询改成JOIN。比如上面的例子改成JOIN写法经常能减少一层物化开销SELECT o.* FROM orders o JOIN users u ON u.id o.user_id WHERE u.status 1;不过这种改写要小心IN和JOIN虽然结果等价但JOIN可能会因为orders里user_id重复而让结果行数翻倍需要加上DISTINCT或GROUP BY。这些都是实战中必须注意的细节。2.2 select_type不只是SIMPLE和PRIMARYselect_type列的值直接告诉你这一行是哪种SELECT类型。最常见的SIMPLE表示不包含子查询和UNION的普通查询PRIMARY表示最外层查询SUBQUERY是子查询中的第一个SELECTDERIVED是FROM子句中的派生表UNION和UNION RESULT则在UNION查询里出现。还有一个不太常见但容易踩坑的DEPENDENT SUBQUERY表示子查询依赖外层查询的列也就是相关子查询。DEPENDENT SUBQUERY非常危险它意味着子查询要对每一行外层结果都执行一次。比如SELECT name, (SELECT COUNT(*) FROM orders o WHERE o.user_id u.id) AS order_count FROM users u;这条SQL如果users有十万行子查询就要执行十万次。EXPLAIN里select_type那列写着DEPENDENT SUBQUERYrows又是全表扫描的话这条SQL基本就废了。遇到这种情况改写思路通常是LEFT JOIN GROUP BY把相关子查询的逐行计算压成一次关联聚合SELECT u.name, COUNT(o.id) FROM users u LEFT JOIN orders o ON o.user_id u.id GROUP BY u.id, u.name;有时候优化器会自动做子查询展开但相关子查询通常还是会保守地逐行执行。因此看到DEPENDENT SUBQUERY就要条件反射地警惕起来。另外还有一个值得说的DERIVED派生表在5.7之前的版本里会物化成临时表且无法使用索引性能比较差。5.7之后有了derived_merge优化很多简单的派生表会被合并到外层查询不再物化这是一个很大的进步。但如果你在派生表里用了LIMIT、DISTINCT、GROUP BY或聚合函数优化器可能仍然选择物化。EXPLAIN里如果看到table列是 并且Extra里有Using temporary就要检查派生表是否有必要存在。2.3 table这一行在操作谁table字段对应的是输出行操作的表名除此之外还有一个信息容易被忽视如果table的值写的是derived2、union1,2、subquery3这类尖括号加数字的形式表示这一行不是在直接读物理表而是在读前面某个子查询的结果集。数字实际上对应id。举个例子table列为derived2代表读取id2的派生表结果。这种表在物理上可能是临时表、也可能是合并后的结果集取决于优化器决定。这个字段在分析的时候不用花太多心思但它能帮你快速定位一个复杂SQL中“哪一步在读临时结果”。我遇到过一种情况生产环境里一条SQL执行计划显示table是derived2Extra里还有Using temporary每次查询耗时都在三秒以上。仔细看id2的那行发现是一个统计子查询没有加合适的索引条件派生表物化时估算的行数又特别大。优化方案不是去优化外层读派生表的方式而是回到id2那个子查询本身给子查询里的where字段加上复合索引物化后的派生表行数从几十万降到几千整条SQL的耗时立刻降到了几十毫秒。这就是table列在排查问题时的价值——它让你知道瓶颈源头在哪一层。3. type字段数据访问方式是SQL性能的“生死线”type应该是EXPLAIN里最受关注的字段了它的取值描述了MySQL如何读取这张表的数据直接决定了一次扫描到底要碰多少行数据。性能从好到差的顺序大致是system const eq_ref ref range index ALL。很多资料把这个顺序背下来却说不清每种访问方式的适用场景和判断标准导致优化时只知道要把ALL改成range却不知道怎么引导优化器走range。3.1 system和const理想中的“点查”system是const的一个特例表示表中最多只有一行数据只有MyISAM或Memory引擎可能出现这个值InnoDB几乎不会出现。const表示MySQL通过主键或唯一索引定位到最多一行记录。判断依据是在WHERE条件里出现了主键或唯一索引的等值匹配。比如SELECT * FROM users WHERE id 1024;这条SQL的type就是constpossible_keys里能看到PRIMARY或对应的唯一索引key列也会显示实际使用的索引。const之所以快是因为MySQL能够把等值条件推导成常量整个查询只需要索引里的一次点查就能完成。这里有个小技巧如果一条SQL的WHERE条件里同时包含主键和一个普通索引优化器通常会选择主键因为主键的索引树更紧凑查询路径更短但如果主键是UUID这种随机字符串实际性能可能反而不如一个有序的整型普通索引。这种情况下type显示const也好、ref也好真正判断快慢的标准反而是key_len和回表次数。type不是唯一标准这一点后面细说。3.2 eq_ref和ref“一条”还是“多条”eq_ref一般出现在JOIN场景中表示被驱动表通过主键或唯一索引进行等值连接每一行外层查询只会匹配到一行被驱动表记录。典型形态SELECT * FROM orders o JOIN users u ON o.user_id u.id;如果users表在id上是主键那么users表的访问类型就是eq_ref这是JOIN查询里最优的驱动模式意味着MySQL不会因为连接查询而出现被驱动表的全表扫描。需要强调的是eq_ref只出现在被驱动表且连接条件必须命中主键或唯一索引。ref则出现在使用普通二级索引做等值匹配的场景可能匹配到多行。比如SELECT * FROM orders WHERE status 1;如果status上有个普通索引访问类型就是ref预估匹配的行数取决于status1这个值的分布。ref比eq_ref性能差一些但在大数据量下用二级索引等值查询回表行数能控制得住的话性能通常还是可以接受的。真正的问题在于如果ref预估的行数很大比如通过一条索引等值条件筛出的行占全表30%以上优化器有时会放弃索引转做全表扫描因为二级索引回表在高选择比场景下反而比顺序全扫描更慢。这种情况下单纯的等值字段索引已经到头了需要引入覆盖索引或者其他条件字段做成复合索引来降低选择性。这条经验在优化慢SQL时非常高频后面案例部分我会展开。3.3 range、index和ALL扫描范围的分水岭range表示索引范围扫描通常出现在比较运算符中包括BETWEEN、大于/小于、IN、LIKE前缀匹配比如name LIKE abc%。range的性能取决于范围内包含多少索引条目范围内行数少回表次数就少整体很快范围内行数多性能就会下滑。判断一个范围查询是否合理不能只看typerange就放心必须结合rows估算值。之前遇到一个案例订单表的created_at字段有索引一个WHERE created_at BETWEEN 2024-01-01 AND 2024-01-31的查询EXPLAIN里type是range看似没问题但rows显示两百多万行——这个月是全年的高峰期。这种情况下type再好看也是虚的因为范围内的数据量太大回表开销已经接近全表扫描。index表示全索引扫描MySQL遍历整棵索引树比ALL好一点因为索引一般比数据表小但本质上还是全量扫描。这种访问方式常见于查询的字段全部在某个索引中的场景比如SELECT user_id, status FROM orders WHERE status ! 0;如果(user_id, status)是一个复合索引优化器可能会选择index方式扫描整个索引来避免回表。但如果查询需要回表拿更多字段index访问就会退化成更糟糕的行为——不仅扫描整个索引还要逐个回表成本比ALL还高。因此看到index类型时要特别检查SELECT的字段列表是否真的能被索引完全覆盖也就是Extra里有没有Using index两者组合才是真“覆盖扫描”。ALL是全表扫描意味着MySQL要逐行读取整张表的数据文件。大部分优化目标就是把ALL消灭掉但也要说明一点小表上的ALL未必是问题比如一张只有几百行的配置表无论怎么查都是全表扫性能也好得很。判断ALL是否必须优化关键看表的大小和SQL的执行频率。一张千万级的大表每天被调用上万次每次都是ALL那必须处理一张几百行的字典表每天被调用十万次ALL也无所谓数据全在内存里一次扫描的开销微乎其微。优化不是教条地追求typeconst而是要把开销控制在合理范围。3.4 逼出range而不是ALL的实战手法很多开发提交的SQL里where条件写了一个普通索引字段但EXPLAIN出来还是ALL因为索引字段上做了函数运算或者隐式类型转换。比如SELECT * FROM orders WHERE DATE(created_at) 2024-06-01;虽然created_at有索引但DATE()函数包裹了字段导致索引失效优化器只能全表扫描。改成范围查询就能用上索引SELECT * FROM orders WHERE created_at 2024-06-01 00:00:00 AND created_at 2024-06-02 00:00:00;这类问题几乎每周都能在各家的慢查询日志里看到。还有一种更隐蔽的隐式类型转换。字段是字符串类型但查询条件传的是数字MySQL会在比较时对字段进行类型转换索引一样会失效。比如phone字段是varchar类型查询写成WHERE phone 13800138000此时EXPLAIN的type会变成ALL而把数字改成字符串WHERE phone 13800138000type就会变回ref。排查这一类问题的窍门很简单多留意EXPLAIN列里type类型的变化ALL突然出现时优先检查WHERE条件里的函数包裹、类型转换、以及是否用了%abc%这种前置模糊匹配。4. key_len、ref、rows用数据量化判断索引“用没用透”前面讲了type这个访问方式的定级现在该深入看索引是怎么用的了。EXPLAIN里的possible_keys、key、key_len、ref、rows这一组字段组合起来能精确回答三个问题优化器考虑了哪些索引possible_keys、实际选择了哪个索引key、这个索引用到了哪些字段、到底能过滤掉多少数据key_len、ref、rows。4.1 possible_keys和key候选集与实际选择possible_keys列出查询中所有可能被用到的索引key是优化器最终选择的那个索引。很多人看到possible_keys里有索引key却是NULL就开始怀疑优化器是不是傻。其实这种情况通常意味着优化器计算后发现用这个索引反而没比全表扫描快。典型例子在性别字段上建索引如果表里男女比例接近1比1用这个索引过滤出一半的数据回表代价比直接全表扫还高优化器就干脆不用。possible_keys的价值在于告诉你“有哪些路可以走”但选了哪条路是优化器基于成本模型算出来的。如果possible_keys为空那说明WHERE条件涉及的字段上压根没有索引这才是真正的设计缺口。4.2 key_len算一算索引到底被“吃掉”了几列key_len是实际使用索引的长度字节数它是判断复合索引“部分可用”还是“全部可用”的最直接证据。比如复合索引是(a, b, c)EXPLAIN显示key_len等于a字段单独的长度那就说明b、c两个字段没有被索引条件用到或者被用到的条件无法参与索引过滤。key_len的估算规则是字符串字段按字符集计算utf8mb4下每个字符占4字节varchar还要额外加2字节存长度INT类型占4字节BIGINT占8字节字段允许NULL时再加1字节。举例来看有一个复合索引(user_id, status)其中user_id是INT4字节允许NULL加1status是TINYINT1字节。如果EXPLAIN显示key_len5说明只有user_id被用于索引查询如果显示key_len6说明user_id和status都被用上了。遇到这个场景时你可以检查SQL是否满足“最左前缀原则”——如果where里只写了status没写user_id那么复合索引就只能用到user_id这一列之前的范围key_len自然只算5。这正是EXPLAIN字段联动分析的价值所在type和key只告诉你“用了哪个索引”key_len告诉你“索引用到了第几层”。同时也要提醒一点key_len偏大不一定是好事。索引覆盖字段越长索引树每一页能容纳的索引条目就越少查询的IO代价会高。如果一条查询只需要索引匹配一列就能完成过滤那刻意把一个大段varchar字段也塞进复合索引前缀里反而会让key_len膨胀影响索引扫描效率。复合索引的设计方法论里有一句话很常用“等值条件放前面范围条件放后面。”这句话落实到EXPLAIN上就是等值条件能撑满前缀字段范围条件只占最后一个位置key_len才能最大化利用。4.3 ref索引匹配时“拿什么比”ref显示索引列是在与什么进行比较。常见值是const与常量比较、某个列名与另一张表的列比较、或者func。看到refconst基本是好事说明查询条件是一个固定值看到ref显示一个列名说明是关联查询中使用了另一张表某列的值。如果你发现某个索引列明明在WHERE条件里有等值条件ref却显示func那就要警惕了——很可能这个字段在比较前被做了函数运算索引没有被正常使用。这种情况下的性能问题很隐蔽但EXPLAIN里reffunc就像一记警钟提示你去检查SQL里是否有WHERE DATE(created_at) ...这类写法和隐式转换的存在。4.4 rows和filtered优化器眼里的“成本账”rows是优化器预估的需要扫描的行数。记住这是基于统计信息和索引区分度的估算不是精确值但它的量级非常有参考价值。一般来说rows在数千以内点查和范围查都能接受rows到了十万、百万量级即使type是ref整体性能大概率也好不到哪里去。filtered是5.7开始展示的字段表示经过索引条件筛选后剩下的行有多少比例能通过WHERE的其他条件。比如rows10000filtered10表示最终结果估计有1000行如果filtered很小比如0.1说明这个索引的过滤选择性其实还不错主要问题是rows本身太大。rows的另一个用途是验证索引设计是否“错位”。我遇到过一条SQLEXPLAIN显示typeref、key_len正常、rows却高达几十万仔细一看SQL的where条件里有三个等值字段复合索引却只建了前面两个实际选择性很差。给复合索引补上第三个字段后rows直接从几十万降到几千。这个过程说明一个道理EXPLAIN不是只用来“看对不对”更关键的是用它来量化“差异有多大”驱动你去看索引设计能不能做得更精准。5. Extra字段隐藏的“真相”往往在这里很多朋友看EXPLAIN时主要盯住type和key我却想专门把Extra单独拿出来聊聊。Extra字段里会出现各种辅助信息其中有些关键词一旦出现往往意味着查询里藏着不小的性能隐患比如Using filesort、Using temporary也有一些关键词是“好消息”比如Using index。准确理解这些关键词比背字段定义本身更有实战价值。5.1 Using filesort排序没走索引的警钟Using filesort表示MySQL需要对结果集进行额外的排序操作排序无法直接利用索引顺序完成。filesort并不一定发生在磁盘文件里数据量小的时候内存排序就够了数据量大的时候才会落盘。但不管哪种情况它都代表一次额外的排序开销且不受索引顺序约束。ORDER BY是常见触发场景。假如SQL里写了WHERE user_id 100 ORDER BY created_at而复合索引只建了(user_id)type是ref但Extra里有Using filesort因为created_at的排序无法通过索引顺序提供。这时只要把索引改成(user_id, created_at)通常排序就能直接利用索引Extra里的Using filesort会消失。还有一个很容易踩坑的情况ORDER BY的字段和WHERE里等值条件的字段顺序不符也会导致排序无法使用索引。比如复合索引是(status, created_at)SQL是WHERE status 1 ORDER BY created_at表面上两个字段都在索引里但执行计划可能仍然Using filesort原因在于等值条件status1已经限定了同一status值下的created_at顺序按理说索引顺序可满足但某些版本优化器对这类场景的处理并不总是聪明需要实际验证。最稳妥的方法是EXPLAIN跑一遍看Extra里还有没有Using filesort有就调整复合索引字段顺序或者调整查询写法。5.2 Using temporary临时表出现的提醒Using temporary表示MySQL在查询过程中使用了内部临时表来保存中间结果。常见于GROUP BY、DISTINCT、UNION以及ORDER BY和GROUP BY字段不一致的场景。内部临时表如果在内存里能用MEMORY引擎开销还可控一旦数据量超出tmp_table_size或max_heap_table_size临时表会落到磁盘上使用MyISAM或InnoDB临时表性能就会明显下降。排查时EXPLAIN里出现Using temporary通常还会伴随Using filesort这两兄弟几乎是一块出现的代表一个典型的“分组排序”低效路径。优化手段有两种思路。一种是改索引让GROUP BY的字段顺序能与索引顺序一致这样分组时可以直接按索引顺序扫描省掉额外临时表另一种是改写SQL把子查询或视图里的聚合逻辑上推减少中间结果集的大小。比如对一个大表的多个维度做统计拆成按小维度分组再汇总效果往往立竿见影。这里提醒一下DISTINCT在某些场景下也会触发Using temporary如果只是想去重可以考虑使用GROUP BY代替两者在结果一致的情况下执行计划可能完全不同。5.3 Using index覆盖索引的绿色标识Using index代表SELECT的字段完全能从索引中获取不需要回表读取数据行。这是优化中非常值得追求的一种状态。举个例子如果有一个(status, created_at)复合索引查询SELECT status, created_at FROM orders WHERE status 1那么整个查询只需要扫描索引树不需要回表Extra里就会有Using index。如果把SELECT改成SELECT *就要回表拿整行数据Using index就没了。这也是为什么很多优化指南建议不要轻易SELECT *——它很容易让优化器放弃覆盖索引。这里要区分两个容易混淆的概念Using index和Using index condition。后者ICPIndex Condition Pushdown在5.6之后出现表示MySQL把WHERE条件的部分过滤下推到存储引擎层通过索引先判断条件再回表。比如复合索引(status, created_at)SQL里写了WHERE status 1 AND created_at 2024-01-01回表前就可以用索引里的created_at字段做范围判定减少回表次数。虽然Extra里没有Using index但Using index condition同样是一个好信号。很多8.0的新手把这两者混为一谈一看到“index”字样就以为走了覆盖索引实际上完全不同。判断覆盖索引的唯一标准就是Extra是否包含Using index。5.4 Using where需要结合其他字段一起理解Extra里出现Using where表示MySQL在获取行后还要再进行一次WHERE条件过滤。这个描述本身不一定是坏事因为很多查询天然需要二次过滤。不过有一种常见组合值得注意typeALL Using where这意味着虽然WHERE里有条件但没有索引可供使用先全表扫描再逐行判断where条件已经是比较糟糕的访问路径了。对比起来typeref Using where则正常得多因为索引已经过滤掉大部分行剩余的行再做where检查也没问题。因此看到Using where不要单独做判断必须和type、key_len、rows组合起来一起看。6. 两个生产环境的真实调优案例聊了这么多字段不如用两个完整案例串一遍。这两个案例都是我实际处理过的线上问题都很典型一个是深分页引发的慢查询一个是多表关联排序导致的临时表膨胀。借此展示如何综合利用EXPLAIN的各字段信息定位瓶颈、制定方案和验证结果。6.1 深分页慢查询优化LIMIT深偏移线上有一条运营后台的列表SQL按照订单创建时间倒序分页展示SELECT * FROM orders WHERE status IN (1, 2, 3) ORDER BY created_at DESC LIMIT 200000, 20;这条SQL在页码靠前时响应尚可翻到5000页以后就卡到无法接受。EXPLAIN结果显示typerange使用了created_at的索引看起来没啥大问题。但注意rows显示约250万行——因为IN条件里有三个值优化器选择走created_at索引做范围扫描从最新一条开始往前扫直到偏移掉20万行再取20行回表。每次翻页都重复扫描前20万条记录成本自然随着偏移量线性增长。Extra里还有Using filesort说明排序没有完全利用索引顺序进一步放大了开销。优化方案选择了业界常用的“延迟关联”写法再用游标思想替代大偏移量分页。第一版改进是用子查询先只取主键再关联原表取完整数据SELECT o.* FROM ( SELECT id FROM orders WHERE status IN (1, 2, 3) ORDER BY created_at DESC LIMIT 200000, 20 ) tmp JOIN orders o ON o.id tmp.id ORDER BY tmp.created_at DESC;这个改动让子查询里的回表被去掉了只扫描主键和created_at字段。EXPLAIN里子查询部分依然存在深偏移问题但扫描的数据量小了很多因为索引页只需要读主键和排序字段性能比原来提升了大约两倍。第二版改进了分页方案把LIMIT偏移改成基于上一页最后一条记录的created_at做游标查询SELECT * FROM orders WHERE status IN (1, 2, 3) AND created_at 2024-05-01 12:00:00 ORDER BY created_at DESC LIMIT 20;这种写法彻底消灭了深偏移EXPLAIN结果里的rows从250万降到了几千每次查询只扫描游标之后最近的那些记录。缺点是用户不能随意跳页但对运营后台的“下一页”场景完全够用。最终这个接口的响应时间从原来的3秒级降到了50毫秒级别。这个案例里typerange本身没有错错的是LIMIT偏移策略与回表放大效应的叠加。EXPLAIN的rows字段在这里给出了最直接的数据支撑看起来优化过的索引方案在深偏移面前依然会扫描海量中间数据。6.2 多表关联排序复合索引设计失误另一个案例是报表查询涉及订单表orders与用户表users关联同时按订单创建时间和用户级别排序SELECT u.name, o.order_no, o.created_at FROM orders o JOIN users u ON u.id o.user_id WHERE u.level 2 ORDER BY o.created_at DESC LIMIT 100;orders表有1000万行users表有200万行。EXPLAIN中orders表的访问类型是ALLusers表是eq_refExtra里有Using temporary和Using filesort。初步看问题出在orders表没走索引。但orders表在user_id和created_at上其实各有一个单列索引为什么优化器还是选了全表扫描关键点在于WHERE条件是过滤users表的level字段而JOIN条件是通过orders.user_id去匹配users.id。优化器可能会选择users作为驱动表过滤level2后只剩几十万行再去连orders。由于orders.user_id上的索引是单列索引排序created_at时没法借助索引只能把关联后的结果放到临时表里排序于是出现了Using temporary Using filesort。优化方向是让驱动表过滤后的结果尽可能小同时让排序可以利用索引。最直接的方式是把orders表的索引改成(user_id, created_at)复合索引这样当users表驱动orders时JOIN会走user_id排序走created_at两个步骤合并到一次索引扫描中。改完索引后再跑EXPLAINorders表的访问类型变成了refExtra里的Using temporary和Using filesort消失查询耗时从1.2秒降到80毫秒。这个案例里有两个教训一是在关联查询中单列索引并不总能满足排序需求复合索引的顺序设计必须同时考虑JOIN字段和ORDER BY字段二是Extra里的Using temporary往往不是“SQL写法问题”而是“索引设计问题”不要一上来就去改写SQL先检查索引方案是否合理。7. EXPLAIN使用中的常见误区与版本差异最后这部分谈一些我在实践中的经验教训以及容易被忽略的版本差异和工具使用建议。7.1 误用EXPLAIN的三个高频场景第一个误区是拿EXPLAIN当作基准测试工具。EXPLAIN不执行SQL所以它给出的rows是估算值不能代替真实的性能测量。我之前遇到过团队里有人用EXPLAIN验证一条SQL“优化好了”理由是rows从一百万降到了一千但真正接上生产数据一跑响应时间还是很高——原因在于索引之外的锁等待、网络传输和查询缓存等其他因素没有被EXPLAIN体现出来。所以EXPLAIN给出的是执行计划的“形状”真实性能必须用实测数据验证特别是改动到索引结构后务必在生产环境的从库或预发环境上用真实数据量做对比测试。第二个误区是在分析EXPLAIN时忽略版本差异。MySQL 5.6的优化器行为和5.7、8.0完全不同特别是衍生表合并、子查询半连接、ICP索引条件下推等功能对执行计划的影响非常大。同一个SQL在5.7里typerefUsing index condition在8.0里可能变成refUsing where解读方式也要随之调整。为了准确使用EXPLAIN最好固定在目标环境的实际版本上做分析不要拿本地的8.0结果去套生产环境的5.7。第三个误区是只看单条SQL不关注全局索引结构。EXPLAIN分析很难脱离表结构独立进行。比如分析一条关联查询前必须知道两个表的索引分布、字段类型、字符集否则key_len的计算和possible_keys的解读都是纸上谈兵。我习惯的做法是在看EXPLAIN的同时打开SHOW INDEX FROM查看表索引定义两侧对照着分析这样能更快发现“看似该走却走不上的索引”和“被埋没的冗余索引”。7.2 8.0扩展功能EXPLAIN ANALYZE与FORMATJSONMySQL 8.0提供了更强大的工具EXPLAIN ANALYZE会真实执行查询并输出每一步的实际耗时、行数和循环次数这比传统EXPLAIN的估算值要精确得多。它的输出格式不再是传统表格而是树状结构。比如EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id 100 ORDER BY created_at DESC LIMIT 100;输出会展示类似“实际耗时”“返回行数”“循环次数”这样的数据。它在优化复杂查询时非常好用能直接告诉你哪个步骤耗时最多。不过EXPLAIN ANALYZE会真实执行SQL所以对写操作类的大查询要慎用别在生产环境的写库上直接跑。另一个有用的是EXPLAIN FORMATJSON输出一组结构化JSON数据里面包含query_block、cost_info、table等键值可以看到优化器的cost计算。对于已经熟悉传统EXPLAIN字段的人来说JSON格式提供的成本数值可以帮你量化“为什么优化器选A不选B”比如两个索引之间成本差异只有0.1%时出现偶发性执行计划抖动原因一目了然。这两个工具5.7都不完全支持如果你还在用5.7那就专心把传统EXPLAIN的字段吃透效果已经很好了。7.3 收尾的一个小技巧把EXPLAIN固化到团队SQL评审流程里这里分享一个我个人的习惯把EXPLAIN结果直接写进SQL评审模板里。任何要合并到主干的SQL提交时都带上一条EXPLAIN结果截图或文本白纸黑字地标注type、key_len、rows、Extra这几个核心指标并附上一个简短的“是否满足要求”的确认。看起来多了一步操作实际上能省掉很多线上事故。因为开发同学写SQL时往往只关注“结果对不对”EXPLAIN能强制他们审视“过程快不快”。这个习惯坚持上半年之后你会发现自己团队里的慢SQL新增数量明显减少很多低级错误在评审阶段就被拦住了。根据我个人这几年和EXPLAIN打交道的体会它是我见过的性价比最高的SQL优化工具——没有任何部署成本没有额外的监控依赖一行关键字就能看到查询计划的全貌。但要真正发挥它的价值不能只停留在“看懂字段”的层面而是要把id、type、key_len、rows、Extra这些字段串成一条完整的分析链路先看执行顺序再判断访问方式再量化索引利用程度最后盯住Extra里的危险信号。配合EXPLAIN ANALYZE做精准定位配合SHOW INDEX看索引结构这套组合拳打下来绝大多数慢SQL问题都能在十分钟内找到头绪。希望这篇拆解能帮你在日常工作中少走一些弯路。
EXPLAIN核心字段详解：从执行计划到SQL优化实战
1. 为什么我把EXPLAIN当作SQL优化的首要工具先讲个真实场景。前两年我接手了一个电商后台的订单查询接口线上偶发超时监控里能看到数据库CPU偶尔飙到80%以上。排查时第一反应不是去看代码而是把那条订单列表SQL拿出来在前面加了个EXPLAIN关键字跑了一遍。结果让人一惊明明订单表才两百万行查询计划却显示要扫描全表预估扫描行数接近一百万行Extra字段里还飘着一个刺眼的Using filesort。而这条SQL应用层的索引设计看起来是齐的——where条件里的字段建了索引order by的字段也建了索引。问题就出在索引字段的排列顺序上单看“有没有索引”完全看不出来但EXPLAIN一看key_len和type就全明白了。这件事之后我基本把EXPLAIN当成SQL优化的第一道工序。EXPLAIN说白了就是MySQL对一条SQL语句生成的一份“查询执行计划说明书”它不会真正去执行这条SQL除非你用了EXPLAIN ANALYZE在8.0版本里会实际执行而是告诉你优化器会按照什么顺序、用什么方式、扫描多少行、用到哪些索引来完成这次查询。你看懂了这份说明书就相当于在SQL真正跑之前就知道它会怎么走哪里慢、哪里能改一目了然。这篇内容我打算把EXPLAIN的每个核心字段拆开讲透重点放在type、key_len、rows、Extra这几个直接影响性能判断的字段上。适合的读者包括刚接触数据库优化、面对慢SQL不知道从哪里下手的后端开发也适合已经在用EXPLAIN但只停留在“看到ALL就慌”阶段的同学。毕竟EXPLAIN的输出有十几列每一列单独看不难难的是把它们串起来读出完整故事。需要先说明的一点不同MySQL版本的EXPLAIN输出略有差异。5.6、5.7版本里常见的字段包括id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra5.7还多了filtered和partitions8.0在此基础上部分版本支持EXPLAIN ANALYZE和JSON格式输出。下面的分析以5.7和8.0的主流行为为基准涉及版本差异的地方我会单独说明。2. 从id和select_type开始理解查询计划的“执行顺序”很多人扫EXPLAIN结果的时候会下意识先看type和key把id、select_type、table当成分隔符一样草草略过。这种习惯需要改一下。id和select_type其实决定了你对整条SQL的解读顺序尤其是遇到子查询、UNION、派生表这些复杂结构时先看这三列能把执行计划的行与行之间的逻辑关系理清楚。2.1 id不是越大越靠前执行而是越小越先执行id这个字段是SELECT的标识符MySQL会为每个SELECT顶层查询、子查询、派生表、UNION分支分配一个编号。关键理解点是id越大越先被“架子”搭起来id越小越先真正执行。更严谨地说输出结果中id相同的行是一组这组内部的表连接顺序由optimizer决定id不同的行之间按照id从大到小的顺序优先“生成”但实际读取数据时是从小到大取。这个逻辑听起来有点绕。我举个嵌套子查询的例子。一条SQL是SELECT * FROM orders WHERE user_id IN ( SELECT id FROM users WHERE status 1 );EXPLAIN的结果大致是两行第一行id为1table是orderstype可能是ALL或ref第二行id为2table是users。执行顺序上MySQL会先构造id2这个子查询填充物料的步骤但如果你只看到这个结论容易误以为先执行users做子查询再去连orders。在5.6版本之前MySQL确实会物理上先物化子查询结果但5.6及之后的优化器可能会把这种IN子查询改写成semi-join实际执行时会先扫描orders再到users里去做匹配。这就是为什么不能只看id大小判断“谁先跑”——它代表的是语法结构上的层级不是执行顺序的绝对标准。实际优化中我反而很少单独看id更多是拿id来对输出行进行“分组边界”的划分。比如一个大SQL里出现了id为1、2、3三种值我会逐个分组去看确认每个子查询是否独立、是否产生了临时表再判断能不能把嵌套子查询改成JOIN。比如上面的例子改成JOIN写法经常能减少一层物化开销SELECT o.* FROM orders o JOIN users u ON u.id o.user_id WHERE u.status 1;不过这种改写要小心IN和JOIN虽然结果等价但JOIN可能会因为orders里user_id重复而让结果行数翻倍需要加上DISTINCT或GROUP BY。这些都是实战中必须注意的细节。2.2 select_type不只是SIMPLE和PRIMARYselect_type列的值直接告诉你这一行是哪种SELECT类型。最常见的SIMPLE表示不包含子查询和UNION的普通查询PRIMARY表示最外层查询SUBQUERY是子查询中的第一个SELECTDERIVED是FROM子句中的派生表UNION和UNION RESULT则在UNION查询里出现。还有一个不太常见但容易踩坑的DEPENDENT SUBQUERY表示子查询依赖外层查询的列也就是相关子查询。DEPENDENT SUBQUERY非常危险它意味着子查询要对每一行外层结果都执行一次。比如SELECT name, (SELECT COUNT(*) FROM orders o WHERE o.user_id u.id) AS order_count FROM users u;这条SQL如果users有十万行子查询就要执行十万次。EXPLAIN里select_type那列写着DEPENDENT SUBQUERYrows又是全表扫描的话这条SQL基本就废了。遇到这种情况改写思路通常是LEFT JOIN GROUP BY把相关子查询的逐行计算压成一次关联聚合SELECT u.name, COUNT(o.id) FROM users u LEFT JOIN orders o ON o.user_id u.id GROUP BY u.id, u.name;有时候优化器会自动做子查询展开但相关子查询通常还是会保守地逐行执行。因此看到DEPENDENT SUBQUERY就要条件反射地警惕起来。另外还有一个值得说的DERIVED派生表在5.7之前的版本里会物化成临时表且无法使用索引性能比较差。5.7之后有了derived_merge优化很多简单的派生表会被合并到外层查询不再物化这是一个很大的进步。但如果你在派生表里用了LIMIT、DISTINCT、GROUP BY或聚合函数优化器可能仍然选择物化。EXPLAIN里如果看到table列是 并且Extra里有Using temporary就要检查派生表是否有必要存在。2.3 table这一行在操作谁table字段对应的是输出行操作的表名除此之外还有一个信息容易被忽视如果table的值写的是derived2、union1,2、subquery3这类尖括号加数字的形式表示这一行不是在直接读物理表而是在读前面某个子查询的结果集。数字实际上对应id。举个例子table列为derived2代表读取id2的派生表结果。这种表在物理上可能是临时表、也可能是合并后的结果集取决于优化器决定。这个字段在分析的时候不用花太多心思但它能帮你快速定位一个复杂SQL中“哪一步在读临时结果”。我遇到过一种情况生产环境里一条SQL执行计划显示table是derived2Extra里还有Using temporary每次查询耗时都在三秒以上。仔细看id2的那行发现是一个统计子查询没有加合适的索引条件派生表物化时估算的行数又特别大。优化方案不是去优化外层读派生表的方式而是回到id2那个子查询本身给子查询里的where字段加上复合索引物化后的派生表行数从几十万降到几千整条SQL的耗时立刻降到了几十毫秒。这就是table列在排查问题时的价值——它让你知道瓶颈源头在哪一层。3. type字段数据访问方式是SQL性能的“生死线”type应该是EXPLAIN里最受关注的字段了它的取值描述了MySQL如何读取这张表的数据直接决定了一次扫描到底要碰多少行数据。性能从好到差的顺序大致是system const eq_ref ref range index ALL。很多资料把这个顺序背下来却说不清每种访问方式的适用场景和判断标准导致优化时只知道要把ALL改成range却不知道怎么引导优化器走range。3.1 system和const理想中的“点查”system是const的一个特例表示表中最多只有一行数据只有MyISAM或Memory引擎可能出现这个值InnoDB几乎不会出现。const表示MySQL通过主键或唯一索引定位到最多一行记录。判断依据是在WHERE条件里出现了主键或唯一索引的等值匹配。比如SELECT * FROM users WHERE id 1024;这条SQL的type就是constpossible_keys里能看到PRIMARY或对应的唯一索引key列也会显示实际使用的索引。const之所以快是因为MySQL能够把等值条件推导成常量整个查询只需要索引里的一次点查就能完成。这里有个小技巧如果一条SQL的WHERE条件里同时包含主键和一个普通索引优化器通常会选择主键因为主键的索引树更紧凑查询路径更短但如果主键是UUID这种随机字符串实际性能可能反而不如一个有序的整型普通索引。这种情况下type显示const也好、ref也好真正判断快慢的标准反而是key_len和回表次数。type不是唯一标准这一点后面细说。3.2 eq_ref和ref“一条”还是“多条”eq_ref一般出现在JOIN场景中表示被驱动表通过主键或唯一索引进行等值连接每一行外层查询只会匹配到一行被驱动表记录。典型形态SELECT * FROM orders o JOIN users u ON o.user_id u.id;如果users表在id上是主键那么users表的访问类型就是eq_ref这是JOIN查询里最优的驱动模式意味着MySQL不会因为连接查询而出现被驱动表的全表扫描。需要强调的是eq_ref只出现在被驱动表且连接条件必须命中主键或唯一索引。ref则出现在使用普通二级索引做等值匹配的场景可能匹配到多行。比如SELECT * FROM orders WHERE status 1;如果status上有个普通索引访问类型就是ref预估匹配的行数取决于status1这个值的分布。ref比eq_ref性能差一些但在大数据量下用二级索引等值查询回表行数能控制得住的话性能通常还是可以接受的。真正的问题在于如果ref预估的行数很大比如通过一条索引等值条件筛出的行占全表30%以上优化器有时会放弃索引转做全表扫描因为二级索引回表在高选择比场景下反而比顺序全扫描更慢。这种情况下单纯的等值字段索引已经到头了需要引入覆盖索引或者其他条件字段做成复合索引来降低选择性。这条经验在优化慢SQL时非常高频后面案例部分我会展开。3.3 range、index和ALL扫描范围的分水岭range表示索引范围扫描通常出现在比较运算符中包括BETWEEN、大于/小于、IN、LIKE前缀匹配比如name LIKE abc%。range的性能取决于范围内包含多少索引条目范围内行数少回表次数就少整体很快范围内行数多性能就会下滑。判断一个范围查询是否合理不能只看typerange就放心必须结合rows估算值。之前遇到一个案例订单表的created_at字段有索引一个WHERE created_at BETWEEN 2024-01-01 AND 2024-01-31的查询EXPLAIN里type是range看似没问题但rows显示两百多万行——这个月是全年的高峰期。这种情况下type再好看也是虚的因为范围内的数据量太大回表开销已经接近全表扫描。index表示全索引扫描MySQL遍历整棵索引树比ALL好一点因为索引一般比数据表小但本质上还是全量扫描。这种访问方式常见于查询的字段全部在某个索引中的场景比如SELECT user_id, status FROM orders WHERE status ! 0;如果(user_id, status)是一个复合索引优化器可能会选择index方式扫描整个索引来避免回表。但如果查询需要回表拿更多字段index访问就会退化成更糟糕的行为——不仅扫描整个索引还要逐个回表成本比ALL还高。因此看到index类型时要特别检查SELECT的字段列表是否真的能被索引完全覆盖也就是Extra里有没有Using index两者组合才是真“覆盖扫描”。ALL是全表扫描意味着MySQL要逐行读取整张表的数据文件。大部分优化目标就是把ALL消灭掉但也要说明一点小表上的ALL未必是问题比如一张只有几百行的配置表无论怎么查都是全表扫性能也好得很。判断ALL是否必须优化关键看表的大小和SQL的执行频率。一张千万级的大表每天被调用上万次每次都是ALL那必须处理一张几百行的字典表每天被调用十万次ALL也无所谓数据全在内存里一次扫描的开销微乎其微。优化不是教条地追求typeconst而是要把开销控制在合理范围。3.4 逼出range而不是ALL的实战手法很多开发提交的SQL里where条件写了一个普通索引字段但EXPLAIN出来还是ALL因为索引字段上做了函数运算或者隐式类型转换。比如SELECT * FROM orders WHERE DATE(created_at) 2024-06-01;虽然created_at有索引但DATE()函数包裹了字段导致索引失效优化器只能全表扫描。改成范围查询就能用上索引SELECT * FROM orders WHERE created_at 2024-06-01 00:00:00 AND created_at 2024-06-02 00:00:00;这类问题几乎每周都能在各家的慢查询日志里看到。还有一种更隐蔽的隐式类型转换。字段是字符串类型但查询条件传的是数字MySQL会在比较时对字段进行类型转换索引一样会失效。比如phone字段是varchar类型查询写成WHERE phone 13800138000此时EXPLAIN的type会变成ALL而把数字改成字符串WHERE phone 13800138000type就会变回ref。排查这一类问题的窍门很简单多留意EXPLAIN列里type类型的变化ALL突然出现时优先检查WHERE条件里的函数包裹、类型转换、以及是否用了%abc%这种前置模糊匹配。4. key_len、ref、rows用数据量化判断索引“用没用透”前面讲了type这个访问方式的定级现在该深入看索引是怎么用的了。EXPLAIN里的possible_keys、key、key_len、ref、rows这一组字段组合起来能精确回答三个问题优化器考虑了哪些索引possible_keys、实际选择了哪个索引key、这个索引用到了哪些字段、到底能过滤掉多少数据key_len、ref、rows。4.1 possible_keys和key候选集与实际选择possible_keys列出查询中所有可能被用到的索引key是优化器最终选择的那个索引。很多人看到possible_keys里有索引key却是NULL就开始怀疑优化器是不是傻。其实这种情况通常意味着优化器计算后发现用这个索引反而没比全表扫描快。典型例子在性别字段上建索引如果表里男女比例接近1比1用这个索引过滤出一半的数据回表代价比直接全表扫还高优化器就干脆不用。possible_keys的价值在于告诉你“有哪些路可以走”但选了哪条路是优化器基于成本模型算出来的。如果possible_keys为空那说明WHERE条件涉及的字段上压根没有索引这才是真正的设计缺口。4.2 key_len算一算索引到底被“吃掉”了几列key_len是实际使用索引的长度字节数它是判断复合索引“部分可用”还是“全部可用”的最直接证据。比如复合索引是(a, b, c)EXPLAIN显示key_len等于a字段单独的长度那就说明b、c两个字段没有被索引条件用到或者被用到的条件无法参与索引过滤。key_len的估算规则是字符串字段按字符集计算utf8mb4下每个字符占4字节varchar还要额外加2字节存长度INT类型占4字节BIGINT占8字节字段允许NULL时再加1字节。举例来看有一个复合索引(user_id, status)其中user_id是INT4字节允许NULL加1status是TINYINT1字节。如果EXPLAIN显示key_len5说明只有user_id被用于索引查询如果显示key_len6说明user_id和status都被用上了。遇到这个场景时你可以检查SQL是否满足“最左前缀原则”——如果where里只写了status没写user_id那么复合索引就只能用到user_id这一列之前的范围key_len自然只算5。这正是EXPLAIN字段联动分析的价值所在type和key只告诉你“用了哪个索引”key_len告诉你“索引用到了第几层”。同时也要提醒一点key_len偏大不一定是好事。索引覆盖字段越长索引树每一页能容纳的索引条目就越少查询的IO代价会高。如果一条查询只需要索引匹配一列就能完成过滤那刻意把一个大段varchar字段也塞进复合索引前缀里反而会让key_len膨胀影响索引扫描效率。复合索引的设计方法论里有一句话很常用“等值条件放前面范围条件放后面。”这句话落实到EXPLAIN上就是等值条件能撑满前缀字段范围条件只占最后一个位置key_len才能最大化利用。4.3 ref索引匹配时“拿什么比”ref显示索引列是在与什么进行比较。常见值是const与常量比较、某个列名与另一张表的列比较、或者func。看到refconst基本是好事说明查询条件是一个固定值看到ref显示一个列名说明是关联查询中使用了另一张表某列的值。如果你发现某个索引列明明在WHERE条件里有等值条件ref却显示func那就要警惕了——很可能这个字段在比较前被做了函数运算索引没有被正常使用。这种情况下的性能问题很隐蔽但EXPLAIN里reffunc就像一记警钟提示你去检查SQL里是否有WHERE DATE(created_at) ...这类写法和隐式转换的存在。4.4 rows和filtered优化器眼里的“成本账”rows是优化器预估的需要扫描的行数。记住这是基于统计信息和索引区分度的估算不是精确值但它的量级非常有参考价值。一般来说rows在数千以内点查和范围查都能接受rows到了十万、百万量级即使type是ref整体性能大概率也好不到哪里去。filtered是5.7开始展示的字段表示经过索引条件筛选后剩下的行有多少比例能通过WHERE的其他条件。比如rows10000filtered10表示最终结果估计有1000行如果filtered很小比如0.1说明这个索引的过滤选择性其实还不错主要问题是rows本身太大。rows的另一个用途是验证索引设计是否“错位”。我遇到过一条SQLEXPLAIN显示typeref、key_len正常、rows却高达几十万仔细一看SQL的where条件里有三个等值字段复合索引却只建了前面两个实际选择性很差。给复合索引补上第三个字段后rows直接从几十万降到几千。这个过程说明一个道理EXPLAIN不是只用来“看对不对”更关键的是用它来量化“差异有多大”驱动你去看索引设计能不能做得更精准。5. Extra字段隐藏的“真相”往往在这里很多朋友看EXPLAIN时主要盯住type和key我却想专门把Extra单独拿出来聊聊。Extra字段里会出现各种辅助信息其中有些关键词一旦出现往往意味着查询里藏着不小的性能隐患比如Using filesort、Using temporary也有一些关键词是“好消息”比如Using index。准确理解这些关键词比背字段定义本身更有实战价值。5.1 Using filesort排序没走索引的警钟Using filesort表示MySQL需要对结果集进行额外的排序操作排序无法直接利用索引顺序完成。filesort并不一定发生在磁盘文件里数据量小的时候内存排序就够了数据量大的时候才会落盘。但不管哪种情况它都代表一次额外的排序开销且不受索引顺序约束。ORDER BY是常见触发场景。假如SQL里写了WHERE user_id 100 ORDER BY created_at而复合索引只建了(user_id)type是ref但Extra里有Using filesort因为created_at的排序无法通过索引顺序提供。这时只要把索引改成(user_id, created_at)通常排序就能直接利用索引Extra里的Using filesort会消失。还有一个很容易踩坑的情况ORDER BY的字段和WHERE里等值条件的字段顺序不符也会导致排序无法使用索引。比如复合索引是(status, created_at)SQL是WHERE status 1 ORDER BY created_at表面上两个字段都在索引里但执行计划可能仍然Using filesort原因在于等值条件status1已经限定了同一status值下的created_at顺序按理说索引顺序可满足但某些版本优化器对这类场景的处理并不总是聪明需要实际验证。最稳妥的方法是EXPLAIN跑一遍看Extra里还有没有Using filesort有就调整复合索引字段顺序或者调整查询写法。5.2 Using temporary临时表出现的提醒Using temporary表示MySQL在查询过程中使用了内部临时表来保存中间结果。常见于GROUP BY、DISTINCT、UNION以及ORDER BY和GROUP BY字段不一致的场景。内部临时表如果在内存里能用MEMORY引擎开销还可控一旦数据量超出tmp_table_size或max_heap_table_size临时表会落到磁盘上使用MyISAM或InnoDB临时表性能就会明显下降。排查时EXPLAIN里出现Using temporary通常还会伴随Using filesort这两兄弟几乎是一块出现的代表一个典型的“分组排序”低效路径。优化手段有两种思路。一种是改索引让GROUP BY的字段顺序能与索引顺序一致这样分组时可以直接按索引顺序扫描省掉额外临时表另一种是改写SQL把子查询或视图里的聚合逻辑上推减少中间结果集的大小。比如对一个大表的多个维度做统计拆成按小维度分组再汇总效果往往立竿见影。这里提醒一下DISTINCT在某些场景下也会触发Using temporary如果只是想去重可以考虑使用GROUP BY代替两者在结果一致的情况下执行计划可能完全不同。5.3 Using index覆盖索引的绿色标识Using index代表SELECT的字段完全能从索引中获取不需要回表读取数据行。这是优化中非常值得追求的一种状态。举个例子如果有一个(status, created_at)复合索引查询SELECT status, created_at FROM orders WHERE status 1那么整个查询只需要扫描索引树不需要回表Extra里就会有Using index。如果把SELECT改成SELECT *就要回表拿整行数据Using index就没了。这也是为什么很多优化指南建议不要轻易SELECT *——它很容易让优化器放弃覆盖索引。这里要区分两个容易混淆的概念Using index和Using index condition。后者ICPIndex Condition Pushdown在5.6之后出现表示MySQL把WHERE条件的部分过滤下推到存储引擎层通过索引先判断条件再回表。比如复合索引(status, created_at)SQL里写了WHERE status 1 AND created_at 2024-01-01回表前就可以用索引里的created_at字段做范围判定减少回表次数。虽然Extra里没有Using index但Using index condition同样是一个好信号。很多8.0的新手把这两者混为一谈一看到“index”字样就以为走了覆盖索引实际上完全不同。判断覆盖索引的唯一标准就是Extra是否包含Using index。5.4 Using where需要结合其他字段一起理解Extra里出现Using where表示MySQL在获取行后还要再进行一次WHERE条件过滤。这个描述本身不一定是坏事因为很多查询天然需要二次过滤。不过有一种常见组合值得注意typeALL Using where这意味着虽然WHERE里有条件但没有索引可供使用先全表扫描再逐行判断where条件已经是比较糟糕的访问路径了。对比起来typeref Using where则正常得多因为索引已经过滤掉大部分行剩余的行再做where检查也没问题。因此看到Using where不要单独做判断必须和type、key_len、rows组合起来一起看。6. 两个生产环境的真实调优案例聊了这么多字段不如用两个完整案例串一遍。这两个案例都是我实际处理过的线上问题都很典型一个是深分页引发的慢查询一个是多表关联排序导致的临时表膨胀。借此展示如何综合利用EXPLAIN的各字段信息定位瓶颈、制定方案和验证结果。6.1 深分页慢查询优化LIMIT深偏移线上有一条运营后台的列表SQL按照订单创建时间倒序分页展示SELECT * FROM orders WHERE status IN (1, 2, 3) ORDER BY created_at DESC LIMIT 200000, 20;这条SQL在页码靠前时响应尚可翻到5000页以后就卡到无法接受。EXPLAIN结果显示typerange使用了created_at的索引看起来没啥大问题。但注意rows显示约250万行——因为IN条件里有三个值优化器选择走created_at索引做范围扫描从最新一条开始往前扫直到偏移掉20万行再取20行回表。每次翻页都重复扫描前20万条记录成本自然随着偏移量线性增长。Extra里还有Using filesort说明排序没有完全利用索引顺序进一步放大了开销。优化方案选择了业界常用的“延迟关联”写法再用游标思想替代大偏移量分页。第一版改进是用子查询先只取主键再关联原表取完整数据SELECT o.* FROM ( SELECT id FROM orders WHERE status IN (1, 2, 3) ORDER BY created_at DESC LIMIT 200000, 20 ) tmp JOIN orders o ON o.id tmp.id ORDER BY tmp.created_at DESC;这个改动让子查询里的回表被去掉了只扫描主键和created_at字段。EXPLAIN里子查询部分依然存在深偏移问题但扫描的数据量小了很多因为索引页只需要读主键和排序字段性能比原来提升了大约两倍。第二版改进了分页方案把LIMIT偏移改成基于上一页最后一条记录的created_at做游标查询SELECT * FROM orders WHERE status IN (1, 2, 3) AND created_at 2024-05-01 12:00:00 ORDER BY created_at DESC LIMIT 20;这种写法彻底消灭了深偏移EXPLAIN结果里的rows从250万降到了几千每次查询只扫描游标之后最近的那些记录。缺点是用户不能随意跳页但对运营后台的“下一页”场景完全够用。最终这个接口的响应时间从原来的3秒级降到了50毫秒级别。这个案例里typerange本身没有错错的是LIMIT偏移策略与回表放大效应的叠加。EXPLAIN的rows字段在这里给出了最直接的数据支撑看起来优化过的索引方案在深偏移面前依然会扫描海量中间数据。6.2 多表关联排序复合索引设计失误另一个案例是报表查询涉及订单表orders与用户表users关联同时按订单创建时间和用户级别排序SELECT u.name, o.order_no, o.created_at FROM orders o JOIN users u ON u.id o.user_id WHERE u.level 2 ORDER BY o.created_at DESC LIMIT 100;orders表有1000万行users表有200万行。EXPLAIN中orders表的访问类型是ALLusers表是eq_refExtra里有Using temporary和Using filesort。初步看问题出在orders表没走索引。但orders表在user_id和created_at上其实各有一个单列索引为什么优化器还是选了全表扫描关键点在于WHERE条件是过滤users表的level字段而JOIN条件是通过orders.user_id去匹配users.id。优化器可能会选择users作为驱动表过滤level2后只剩几十万行再去连orders。由于orders.user_id上的索引是单列索引排序created_at时没法借助索引只能把关联后的结果放到临时表里排序于是出现了Using temporary Using filesort。优化方向是让驱动表过滤后的结果尽可能小同时让排序可以利用索引。最直接的方式是把orders表的索引改成(user_id, created_at)复合索引这样当users表驱动orders时JOIN会走user_id排序走created_at两个步骤合并到一次索引扫描中。改完索引后再跑EXPLAINorders表的访问类型变成了refExtra里的Using temporary和Using filesort消失查询耗时从1.2秒降到80毫秒。这个案例里有两个教训一是在关联查询中单列索引并不总能满足排序需求复合索引的顺序设计必须同时考虑JOIN字段和ORDER BY字段二是Extra里的Using temporary往往不是“SQL写法问题”而是“索引设计问题”不要一上来就去改写SQL先检查索引方案是否合理。7. EXPLAIN使用中的常见误区与版本差异最后这部分谈一些我在实践中的经验教训以及容易被忽略的版本差异和工具使用建议。7.1 误用EXPLAIN的三个高频场景第一个误区是拿EXPLAIN当作基准测试工具。EXPLAIN不执行SQL所以它给出的rows是估算值不能代替真实的性能测量。我之前遇到过团队里有人用EXPLAIN验证一条SQL“优化好了”理由是rows从一百万降到了一千但真正接上生产数据一跑响应时间还是很高——原因在于索引之外的锁等待、网络传输和查询缓存等其他因素没有被EXPLAIN体现出来。所以EXPLAIN给出的是执行计划的“形状”真实性能必须用实测数据验证特别是改动到索引结构后务必在生产环境的从库或预发环境上用真实数据量做对比测试。第二个误区是在分析EXPLAIN时忽略版本差异。MySQL 5.6的优化器行为和5.7、8.0完全不同特别是衍生表合并、子查询半连接、ICP索引条件下推等功能对执行计划的影响非常大。同一个SQL在5.7里typerefUsing index condition在8.0里可能变成refUsing where解读方式也要随之调整。为了准确使用EXPLAIN最好固定在目标环境的实际版本上做分析不要拿本地的8.0结果去套生产环境的5.7。第三个误区是只看单条SQL不关注全局索引结构。EXPLAIN分析很难脱离表结构独立进行。比如分析一条关联查询前必须知道两个表的索引分布、字段类型、字符集否则key_len的计算和possible_keys的解读都是纸上谈兵。我习惯的做法是在看EXPLAIN的同时打开SHOW INDEX FROM查看表索引定义两侧对照着分析这样能更快发现“看似该走却走不上的索引”和“被埋没的冗余索引”。7.2 8.0扩展功能EXPLAIN ANALYZE与FORMATJSONMySQL 8.0提供了更强大的工具EXPLAIN ANALYZE会真实执行查询并输出每一步的实际耗时、行数和循环次数这比传统EXPLAIN的估算值要精确得多。它的输出格式不再是传统表格而是树状结构。比如EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id 100 ORDER BY created_at DESC LIMIT 100;输出会展示类似“实际耗时”“返回行数”“循环次数”这样的数据。它在优化复杂查询时非常好用能直接告诉你哪个步骤耗时最多。不过EXPLAIN ANALYZE会真实执行SQL所以对写操作类的大查询要慎用别在生产环境的写库上直接跑。另一个有用的是EXPLAIN FORMATJSON输出一组结构化JSON数据里面包含query_block、cost_info、table等键值可以看到优化器的cost计算。对于已经熟悉传统EXPLAIN字段的人来说JSON格式提供的成本数值可以帮你量化“为什么优化器选A不选B”比如两个索引之间成本差异只有0.1%时出现偶发性执行计划抖动原因一目了然。这两个工具5.7都不完全支持如果你还在用5.7那就专心把传统EXPLAIN的字段吃透效果已经很好了。7.3 收尾的一个小技巧把EXPLAIN固化到团队SQL评审流程里这里分享一个我个人的习惯把EXPLAIN结果直接写进SQL评审模板里。任何要合并到主干的SQL提交时都带上一条EXPLAIN结果截图或文本白纸黑字地标注type、key_len、rows、Extra这几个核心指标并附上一个简短的“是否满足要求”的确认。看起来多了一步操作实际上能省掉很多线上事故。因为开发同学写SQL时往往只关注“结果对不对”EXPLAIN能强制他们审视“过程快不快”。这个习惯坚持上半年之后你会发现自己团队里的慢SQL新增数量明显减少很多低级错误在评审阶段就被拦住了。根据我个人这几年和EXPLAIN打交道的体会它是我见过的性价比最高的SQL优化工具——没有任何部署成本没有额外的监控依赖一行关键字就能看到查询计划的全貌。但要真正发挥它的价值不能只停留在“看懂字段”的层面而是要把id、type、key_len、rows、Extra这些字段串成一条完整的分析链路先看执行顺序再判断访问方式再量化索引利用程度最后盯住Extra里的危险信号。配合EXPLAIN ANALYZE做精准定位配合SHOW INDEX看索引结构这套组合拳打下来绝大多数慢SQL问题都能在十分钟内找到头绪。希望这篇拆解能帮你在日常工作中少走一些弯路。