前几天帮一个项目排查线上慢查询开发同事把一段MySQL SQL丢给我语气很急这查询我加了索引还是慢我打开EXPLAIN看了一眼执行计划type是ALLrows显示要扫一百多万行而他认为已经加上的索引根本没被选用。这种场景在MySQL性能优化里太常见了——大多数开发者不是不懂EXPLAIN的语法而是没真正读懂它输出的每一列到底在代表什么。EXPLAIN是MySQL提供的执行计划查看工具它把优化器选择的读取方式摊开给你看扫多少行、用哪个索引、有没有排序、有没有临时表。这篇文章我打算从执行计划的底层逻辑讲起把每列含义、type效率层级、从执行计划反推索引设计的套路讲透再结合几个真实案例说清楚优化前后发生了什么变化。无论你是刚接触MySQL的新手还是被慢查询反复折磨的进阶用户都应该能从这篇里找到对应的解法。1. EXPLAIN到底在解释什么 —— 执行计划的基本认知1.1 一条SQL从客户端到执行计划的完整旅程在MySQL里一条SQL从发出到最后拿到结果远不只是查一下这么简单。客户端把SQL文本发给服务器后先经过词法分析、语法分析把字符串变成一棵语法树然后优化器登场它会读取表的统计信息、索引分布情况在多个可能的执行路径里做代价估算选出一条它认为最经济的方案这个方案的产物就是执行计划最后执行器拿着执行计划逐行去存储引擎里取数据。EXPLAIN的作用就是把优化器最终选定的那条路径原原本本打印给你看。很多人误以为EXPLAIN会真的执行SQL其实不会。EXPLAIN本身只做计划生成和展示不触碰实际数据除了下一节要单独说的EXPLAIN ANALYZE。也正因为不执行SQL它不会返回查询结果只会返回一张表格一行对应执行计划里的一个步骤。比如最简单的单表查询只有一行两表JOIN通常有两行但连接顺序、访问方式不同行的内容和顺序会大不一样。理解了这条链路你就明白了一个重要结论EXPLAIN展示的是优化器的想法而不是SQL应该怎么跑的客观答案。优化器也有判断失手的时候比如统计信息过时、估算代价和实际代价偏差太大就会出现选错索引甚至全表扫描的情况。所以EXPLAIN提供的是一份嫌疑人名单真正的定罪证据还要结合数据量、时间分布来验证。1.2 什么时候该用EXPLAIN判断标准与前置准备我见过不少开发者只在应用卡得受不了的时候才想起EXPLAIN这其实有点晚了。更合理的节奏是把它纳入日常巡检。慢查询日志里出现频率高的SQL、线上CPU突然打满时抓到的会话正在执行的SQL、新功能上线前的自测SQL这几类都值得用EXPLAIN过一遍。我的习惯是把慢查询阈值设成1秒定期导出慢日志然后对每条慢SQL跑EXPLAIN凡是type出现ALL、Extra出现Using filesort或Using temporary的直接进入优化清单。另外有个前置准备容易被忽略EXPLAIN的结果依赖统计信息而统计信息不是永远准确的。如果表经常大量插入、删除但很久没跑ANALYZE TABLE优化器拿到的索引基数可能严重过期做出的计划自然不准。所以在对着一张几百上千万行的大表做优化前先执行ANALYZE TABLE 表名; 让统计信息更新一版再跑EXPLAIN才能看到相对真实的情况。遇到底线数据抖动这也是我排查的第一个动作。2. 读懂执行计划的核心列 —— 别被id和type吓住2.1 id列多表连接的读取顺序EXPLAIN的第一行输出往往让人看得一头雾水尤其当id列出现多个值时。id列的实际含义是查询步骤的标识号数字越大对应的行越先执行。多张表做JOIN时如果id值相同说明这几行属于同一个查询层级执行顺序按照显示顺序从上到下。如果id值不同比如子查询常见的场景那么id大的子查询先执行结果作为外层查询的输入。我举个例子你就明白了。一条SQL是SELECT * FROM users WHERE id (SELECT user_id FROM orders WHERE order_no abc123)EXPLAIN结果通常出现两行第二行的id为2执行的是子查询orders表的查询第一行id为1执行的是外层users表查询。虽然显示顺序是先外层后内层但真实执行顺序是子查询先出结果再驱动外层。这个逻辑一旦搞错读执行计划就会绕进死胡同。2.2 type列访问级别的效率金字塔type列是执行计划里信息量最大的一列它描述了MySQL访问表的方式从快到慢大致是这样一条链路system、const、eq_ref、ref、range、index、ALL。我把每一档的典型触发场景整理成了表格方便你对照。type效率典型含义system最高表只有一行系统表或统计表的极致访问const最高主键或唯一索引的等值匹配最多返回一行比如WHERE id5eq_ref高被驱动表用主键或唯一索引做等值关联典型于JOIN中的点查ref中高非唯一索引的等值匹配可能返回多行比如WHERE user_id123range中索引范围扫描BETWEEN、IN、、、LIKE前缀模糊都在此列index较低遍历索引树但效率不如range多见于覆盖索引的整树扫描ALL最低全表扫描直观来说就是把整张表的聚簇索引从头扫到尾看到ALL不要急着下结论说必须消灭。如果一张表只有几百行全表扫描可能比走索引更快因为额外回表的开销比直接扫整个小表还大。优化器不是傻瓜它选择ALL通常意味着在当前数据分布下它觉得这就是最快的路。但如果你发现一张几百万行的表查询type是ALL而WHERE条件里明明有可索引的列那基本可以断定索引没建对或者没被用上这种情况就该深挖了。2.3 key、rows、filtered与Extra真正决定索引命中与否的细节type看大方向但具体到索引用到了哪个扫描了多少行过滤后剩多少要看后面这几列。possible_keys优化器认为可能会用到的索引候选列表。如果这里显示NULL说明它压根没找到可用的索引这时先别管type回去检查WHERE条件的字段有没有索引。key真正选用的索引名。有时候possible_keys里有三个索引但key只选了其中一个说明优化器算过代价后挑了它认为最便宜的那个。key_len这个信息价值很高表示索引中实际被使用的字节长度。对复合索引来说key_len能帮你判断到底用到了前缀的哪几列。比如一个索引是(a,b,c)如果key_len是a列的长度那说明只有a参与了匹配b和c没被用上。这一点在排查我建的复合索引为什么没生效时特别有用。rows优化器预估需要扫描的行数。注意是预估不是实际扫描行数。它基于统计信息计算误差可能很大。实际行数要用EXPLAIN ANALYZE才能看到。filtered表示经过WHERE过滤后预计剩余行数占总扫描行数的百分比。比如rows是10000filtered是10说明大概只有1000行能通过条件进入下一层。这个值越小说明扫描中无效数据越多可优化的空间也越大。Extra列是一个附加信息黑板里面常出现的高频值要记住Using where表示对存储引擎返回的行再做了过滤Using index表示查询完全通过索引解析不需要回表这叫覆盖索引是最理想的形态之一Using filesort表示MySQL在排序缓冲区里对结果做了额外排序一旦出现在大查询里往往是性能杀手Using temporary表示用了内部临时表GROUP BY、DISTINCT、某些子查询容易触发Using join buffer则表示关联时没有索引可用只能把外层数据放到缓冲区去匹配。这几个关键词基本能决定一条SQL的命运后面案例里我会反复用到。3. 通过实际案例定位慢查询 —— 从执行计划到索引优化3.1 案例一全表扫描的orders表如何改成索引查找先说第一个真实场景。一张订单表orders有大约500万行结构设计很普通主键id加上user_id、status、amount、create_time这几个业务字段。某天线上出现一个高频慢查询根据用户ID查最近20条已支付订单SQL长这样SELECT * FROM orders WHERE user_id 123 AND status 1 ORDER BY create_time DESC LIMIT 20;第一次跑EXPLAIN的结果让我印象深刻mysql EXPLAIN SELECT * FROM orders WHERE user_id 123 AND status 1 ORDER BY create_time DESC LIMIT 20\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: orders type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 5234781 filtered: 10.00 Extra: Using where; Using filesorttype是ALLpossible_keys是NULLrows直接按500万估算Extra里挂着Using filesort。这意味着优化器不得已选择了全表扫完、过滤、再排序。500万行排序的代价在一个高频接口里是不可能扛得住的。这里的解决思路分两步先看等值过滤条件user_id和status再看排序字段create_time。复合索引的设计铁律是等值字段放前面排序字段放后面于是我建议加一个索引(user_id, status, create_time)。为什么要按这个顺序因为索引的B树会先按照user_id有序再按status有序最后按create_time有序。等值命中user_id123和status1之后剩下的create_time天然已经排好序MySQL不需要再额外做filesort直接往前读20行就是结果。加完索引再跑EXPLAINmysql EXPLAIN SELECT * FROM orders WHERE user_id 123 AND status 1 ORDER BY create_time DESC LIMIT 20\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: orders type: ref possible_keys: idx_user_status_time key: idx_user_status_time key_len: 9 ref: const,const rows: 56 filtered: 100.00 Extra: Backward index scan关键变化有四个type从ALL变成refrows从523万压到56key_len是9说明user_id8字节和status1字节都参与了等值匹配Extra里的Using filesort消失变成了Backward index scan——这是MySQL 8.0对反向索引扫描的优化说明表示它直接利用索引的有序性倒着读。这条查询的真实耗时从800毫秒掉到了3毫秒左右效果立竿见影。3.2 案例二两表JOIN的驱动表选择与hash join第二个案例是后台报表场景两张表users用户表10万行orders订单表1000万行。需求是查所有等级为3的用户最近下单的金额SQL大概是这样SELECT u.name, o.amount FROM users u JOIN orders o ON o.user_id u.id WHERE u.level 3 AND o.status 1;一开始两张表的关联字段都没加索引EXPLAIN结果里两行type都是ALL第二行Extra出现Using join buffer (hash join)。这个Extra在MySQL 8.0.18之后的版本里很常见表示优化器选择了hash join做连接。问题在于两张大表做全量哈希匹配需要把一方全部读进内存再构hash表内存和CPU开销都非常大。优化方向很清楚在orders表上给user_id加索引让被驱动表能按主键做点查同时在users表上给level加索引把驱动表的过滤范围缩到最小。加完索引后的EXPLAIN输出里第一行是userstype为ref通过idx_level访问第二行是orderstype为refref列显示u.id说明每拿到一个用户都通过索引去订单表里拿对应记录。这就是典型的小表驱动大表、被驱动表走索引的形态。这里我想多说一句驱动表的选择规律。优化器决定谁做驱动表本质是算总代价。外层驱动表的每一条记录都要进内层查一次所以驱动表的扫描行数越小越好这个原则叫小表驱动大表。但小不是指物理行数大小而是经过WHERE过滤后再JOIN那个步骤需要的行数。用EXPLAIN看就是第一行和第二行的rows、filtered综合相对大小。读懂execution plan里第一行的真实身份远比死记硬背规则有用。3.3 案例三GROUP BY引起的临时表与filesort第三个案例来自一次统计需求统计某一天所有下单用户的订单数量。SQL长这样SELECT user_id, COUNT(*) FROM orders WHERE create_time 2025-01-15 AND status 1 GROUP BY user_id;优化前的EXPLAIN很典型——type是ALLExtra是Using where; Using temporary; Using filesort。临时表和文件排序同时出现意味着MySQL要把扫描结果先扔进临时表再对临时表做排序以便完成GROUP BY的聚合这个流程在几百万行数据上非常痛苦。我建议建的索引是(create_time, user_id, status)。这次和案例一的思路略有不同create_time是等值过滤条件放最前面user_id是分组字段必须利用索引的有序性来消灭临时表status放在最后是为了覆盖过滤条件避免回表。创建后EXPLAIN变成mysql EXPLAIN SELECT user_id, COUNT(*) FROM orders WHERE create_time 2025-01-15 AND status 1 GROUP BY user_id\G *************************** 1. row *************************** type: ref possible_keys: idx_create_user_status key: idx_create_user_status key_len: 5 ref: const rows: 1200 Extra: Using indexkey_len是5create_time是datetime类型占5字节这里正好展示了你看到的索引前缀字节数Extra变成了Using index。为什么GROUP BY也一起被优化掉了因为等值条件定位到索引里一个连续段这段叶子上的user_id本来就是按索引顺序排列的MySQL在遍历这段数据时发现相同的user_id紧挨在一起直接数个数就行根本不需要临时表也不需要排序。这就是索引有序性对聚合查询的降维打击。4. EXPLAIN的进阶形态FORMATTREE与EXPLAIN ANALYZE4.1 传统表格的局限与TREE格式的补充EXPLAIN传统表格只有在单表或简单JOIN时够用一旦SQL里出现子查询、半连接、派生表这些结构表格形态就变得很难读行与行之间的依赖关系在代码里可能挨着在逻辑上却是嵌套的。MySQL 8.0.16起提供了EXPLAIN FORMATTREE它用缩进层级把执行计划打印成树形结构算子之间的嵌套关系一眼就清楚。我拿一个真实SQL做演示EXPLAIN FORMATTREE SELECT u.name, o.amount FROM users u JOIN orders o ON o.user_id u.id WHERE u.level 3;输出的树形结构长这样- Nested loop inner join (cost158.21 rows215) - Filter: (u.level 3) (cost2.32 rows23) - Table scan on u (cost2.32 rows345) - Index lookup on o using idx_user_id (user_idu.id) (cost0.78 rows9)看到这个树你就能理解整个执行过程先读users表经过level3过滤得到23行再用这23行挨个去orders表的索引里找user_id对应的记录。cost和rows都打印在每个算子的括号里方便对比每一步的代价占比。TREE格式特别适合排查子查询到底物化没有JOIN顺序为什么和我想的不一样这类问题建议升级到8.0之后把习惯改成优先用TREE。4.2 EXPLAIN ANALYZE真实执行时间与预估值的差距如果你已经升级到MySQL 8.0.18以上那EXPLAIN ANALYZE是不可错过的工具。它和普通EXPLAIN最大的区别是这一步会真的执行SQL然后返回每个算子的实际执行时间、实际扫描行数、循环次数。这就能直接戳穿rows列可能存在的估谎问题。还是用前面orders的例子EXPLAIN ANALYZE SELECT user_id, COUNT(*) FROM orders WHERE create_time 2025-01-15 AND status 1 GROUP BY user_id;输出大概是- Group aggregate: count(*) (cost25.33 rows1200) (actual time0.456..0.912 rows1200 loops1) - Index lookup on orders using idx_create_user_status (create_time2025-01-15, status1) (cost12.11 rows1200) (actual time0.234..0.890 rows1200 loops1)注意actual time单位是毫秒格式是处理第一行的时间..处理所有行的时间后面的rows是真实行数loops表示这个算子被循环执行了多少次。普通EXPLAIN里如果rows估成5600但实际只有1200你会发现这里显示的actual rows和预估差得很远那就是统计信息不准的直观证据。有一点必须反复提醒EXPLAIN ANALYZE会真跑SQL。对UPDATE、DELETE、INSERT这些写操作来说虽然官方说执行完会自动回滚但在生产库大表上跑哪怕回滚也会带来大量锁等待、磁盘I/O和主从延迟。所以我的铁律是只在只读的SELECT上用它而且最好在从库或压测环境里跑。想在生产机验证慢SQL优先用普通EXPLAIN做预判实在要跑ANALYZE就挑业务低峰期把LIMIT外加小一点。5. 执行计划常见误读与我的排查心得5.1 误读一索引命中不代表不再回表一个经常让人掉坑的误解是执行计划里能看到key就认为查询已经足够快。实际上typeref只是说明用索引定位了行但如果查询的列里有一些不在二级索引上MySQL每定位到一行都要回聚簇索引把完整行取出来这叫回表。回表次数多到一定程度查询一样会退化。怎么判断有没有回表看Extra有没有Using index。如果key非NULL、Extra里也写着Using index说明这条查询的所有列都被索引覆盖不需要回表这种状态叫覆盖索引。比如前面案例三里非要建(create_time, user_id, status)而不是只建(create_time, status)一个重要目的就是把SELECT的user_id和WHERE的status、create_time全部收进索引里让Extra能稳定出现Using index。做报表查询时我通常先看SELECT需要的字段能不能塞进某个索引能塞就优先用覆盖策略这不只是提速是把回表开销直接归零。5.2 误读二rows是估算值别太当真rows列在优化器眼里是圣旨但在我们开发者眼里只能当参考值。它来自统计信息而统计信息的更新频率受制于innodb_stats_auto_recalc等配置和大表的采样策略。一张表连续插入几百万行却没触发采样rows展示的基数可能还停留在三个月前和现实差了十倍甚至百倍。有一次我排查一个分区表查询EXPLAIN显示rows只有2万实际执行却扫了500万行应用直接超时。后来让DBA跑了一次ANALYZE TABLErows估算立刻修正到几百万的级别再回头看执行计划优化器才意识到该换一个访问路径。所以遇到EXPLAIN结果和线上表现明显不符时先别怀疑数据库出了玄学问题优先做三件事重新ANALYZE TABLE、查看表当前行数、用EXPLAIN ANALYZE看真实扫描行数。这三步下来绝大多数计划不准的问题都能定位到根因。5.3 误读三加了索引但type还是ALL的排查顺序这是所有MySQL使用者问得最多的一个问题我明明建了索引为什么EXPLAIN出来还是全表扫描我通常在回复前先按下面这个清单逐项过一遍照着顺序排查基本不会漏最左前缀是否被破坏复合索引(a,b,c)查询条件只用了b和c或者从a、b之间跳字段直接让索引整体失效type会退化到ALL或index。WHERE条件列是否被函数或运算包裹WHERE DATE(create_time)2025-01-15这种写法让索引列失去原有值MySQL没法拿索引区间匹配改成create_time 2025-01-15 00:00:00 AND create_time 2025-01-16 00:00:00就能走range。隐式类型转换字段是varchar类型查询条件却写 123MySQL会先把索引列转成数字再比较索引直接失效。反过来数字字段用字符串查一样踩坑。LIKE前模糊LIKE %abc无法利用B树前缀匹配只有LIKE abc%才能走索引。优化器觉得全表扫描更便宜即使索引能用如果某个字段的选择性非常低比如status只有0和1两个值分布又均匀优化器判定扫索引加回表的代价大于直接全表扫它会主动放弃索引。这种情况加一个(status, create_time)之类的复合索引或许能把代价拉低但最关键是让查询条件里的高选择性字段参与匹配。字符集或排序规则不一致两张表JOIN时连接字段一个utf8mb4一个latin1或者colloation不同MySQL需要做换算转换索引就失灵了。检查表结构时顺手核对字符集能避免很多半夜被叫醒的惨剧。按这个清单一条条对照比盲目删索引重建要高效得多。5.4 几条写在最后的实战建议先说EXPLAIN的使用边界。EXPLAIN本身不执行SQL但它仍然会拿到一些元数据锁在高峰期对超大表跑大量EXPLAIN也不是完全没有代价所以要克制别在压测脚本里循环刷EXPLAIN。另外同一张表数据分布变化后同一个SQL的EXPLAIN结果可能完全不同优化完只是当前时点有效。上线后隔一段时间要重新抽查尤其是表增长很快的并发业务。再说一个我常用的排查组合拳。先把慢查询日志里出现的SQL复制出来跑一次EXPLAIN FORMATTREE看结构再针对关键节点跑EXPLAIN ANALYZE验证真实耗时最后把结果和线上监控里的实际延迟做对比。这套流程基本覆盖了计划-验证-测量整个闭环。顺带提一句很多框架会为开发环境自动生成索引但生产环境真正的高频查询往往和开发阶段完全不一样。与其相信ORM自动建的索引不如把生产慢日志里Top 10的SQL全抓出来跑一遍EXPLAIN你会发现索引设计的真正答案都在执行计划里。说回开头那个开发同事的案例。我把SQL原封不动丢回给他的时候他还坚持我明明加了索引。我让EXPLAIN把possible_keys亮出来NULL。后来查表结构才发现他把索引建在一个字符串字段上却拿数值去查隐式类型转换让索引直接失效。把条件改成字符串再跑type变成refrows从124万降到352接口从800毫秒掉到3毫秒。这类问题我在线上见过太多次EXPLAIN没有魔法它只是诚实地告诉你MySQL此刻到底打算怎么干。你读懂了它才能真正和优化器对话。