MySQL EXPLAIN执行计划详解：从慢查询定位到索引优化实战-北京尧图网络

MySQL的EXPLAIN命令大概是所有做数据库性能优化的人最先接触也最常用到的工具。一句EXPLAIN加在SQL前面就能看到优化器打算怎么执行这条查询先读哪张表、走没走索引、预估扫多少行、要不要排序和建临时表。恰恰是这些信息决定了线上SQL是几十毫秒返回还是拖到超时告警。我前阵子处理一个订单查询慢的问题表里不到三十万行数据接口稳定在1.8秒EXPLAIN一发出来问题就摆在脸上WHERE条件里对索引字段做了函数运算type直接降到ALL。类似场景几乎每个月都会遇到。这篇文章想把自己实际用EXPLAIN的经验做一次完整梳理从命令怎么用、输出每一列怎么读到真实案例的调优过程和常见坑点尽量覆盖日常排查慢查询会碰到的绝大多数情况。适合手里已经能写SQL、开始被线上性能问题折磨的开发者也适合需要定期Review慢日志的DBA或后端负责人。我会尽量用实际场景来讲因为执行计划这东西光背列名没有意义得结合真实SQL和索引设计去理解才有感觉。1. EXPLAIN怎么用基础命令与输出全貌1.1 一条EXPLAIN命令到底能告诉我们什么EXPLAIN的用法简单粗暴在SELECT、UPDATE、DELETE、INSERT语句前面加上EXPLAIN关键字就可以了。MySQL 5.6之后对UPDATE和DELETE也支持EXPLAIN这点比早期版本方便不少很多改数脚本的慢查询排查都靠它。执行后返回的是一张结果表每一行代表查询计划中的一个步骤。多行结果就说明这个SQL被拆成了多个执行步骤比如子查询、UNION、多表JOIN都会出现多行。每一行里包含了访问哪张表、用什么访问方式、预计读取多少行、Extra列还带着排序、临时表等额外信息。需要特别强调的一点默认的EXPLAIN并不会真正执行SQL它只做优化器层面的计划生成和成本估算所以线上库可以直接跑不会对业务数据产生写操作也不会真的把慢查询完整执行一遍。但后面要讲的EXPLAIN ANALYZE是例外它会真实执行查询使用时要谨慎。新版本里EXPLAIN还支持几种输出格式常见的包括EXPLAIN FORMATTREE SELECT ...树形结构展示执行计划层次感更强MySQL 8.0.16起默认支持。EXPLAIN FORMATJSON SELECT ...返回JSON结构里面包含更详细的成本估算数据是我们理解优化器决策的重要线索。EXPLAIN ANALYZE SELECT ...MySQL 8.0.18引入会实际执行SQL并输出每一步的真实耗时、真实返回行数、循环次数属于性能分析利器。日常我们先看默认表格输出遇到复杂SQL再用FORMATJSON或ANALYZE做深入分析。这个顺序能保证大多数问题在五分钟内定位。1.2 输出列一眼看懂重点关注哪些字段不同MySQL版本输出的列略有差异但关键列是稳定的。常见的列大致如下列名含义关注度id查询步骤的编号id越大越先执行相同id从上往下执行中select_type查询类型比如SIMPLE、PRIMARY、SUBQUERY、DERIVED、UNION等中table当前步骤访问的表名或衍生表名低partitions命中的分区如果是分区表低type访问类型从system到ALL的性能阶梯是调优的第一观察点高possible_keys优化器认为可能用到的索引中key实际选择的索引高key_len实际使用的索引字节长度能看出复合索引用了几个字段高ref与索引比较的列或常量低rows优化器预估要读取的行数高filtered预估被WHERE条件过滤后剩余行数的百分比中Extra附加信息包含是否排序、是否用临时表、是否覆盖索引等高四张表里最需要盯紧的就是type、key、rows、Extra。type决定访问数据的方式key决定走了哪条索引rows反应了优化器对这个方案的成本预估Extra则透露了查询中隐藏的排序、临时表、回表代价。很多新人一上来喜欢死抠select_type和ref的含义但实际排查慢查询时这两个字段提供的信息没有前面四个那么直接。我的建议是先抓住主要矛盾理解type和Extra再看key_len和rows做深入确认等遇到子查询和复杂JOIN时再回头研究select_type也不迟。2. 访问类型与索引细节type、key_len、rows怎么配合着看2.1 type列从system到ALL的完整性能阶梯type列是执行计划里最有信息量的字段它描述了MySQL如何从表中读取数据。从最优到最差常见类型大致有这么一档type典型场景性能含义system表中只有一行记录系统表级别常数级几乎无成本const根据主键或唯一索引等值查询最多返回一行常数级优化器把结果当成常量处理eq_refJOIN关联时被驱动表用主键或唯一索引做等值匹配每次只匹配一行联表查询中最理想的情况ref使用普通二级索引做等值查询可能返回多行比ALL好很多但回表次数取决于匹配行数range使用索引做范围查询比如BETWEEN、、、IN有界扫描数据量大时也能接受index扫描整棵二级索引树和ALL类似但数据量更小如果没有回表某些场景还能接受ALL全表扫描优化器决定把整张表读一遍性能杀手线上大表出现这个基本就要介入const和eq_ref是执行计划里最赏心悦目的两种形态。比如主键等值查询或者JOIN时被驱动表通过主键关联这类查询无论数据量多大单次读取成本都基本固定。ref则是普通索引等值匹配比如WHERE status 1且status上建了二级索引这个级别通常已经算健康。range是我个人设的一个及格线。线上业务SQL如果过滤条件不走索引且没有覆盖索引兜底type掉到ALL通常意味着查询成本线性增长。而index级别是个容易迷惑人的状态——它没有全表扫描那么吓人但本质上是把整棵二级索引树扫了一遍如果还需要大量回表实际性能未必比ALL好到哪里去。需要补充的是MySQL在5.6以后支持index_merge某些特殊条件下优化器会把两个索引合并使用type显示为index_merge。另外子查询中还有unique_subquery、index_subquery等类型这些相对少见遇到时单独看官方文档确认即可。2.2 key_len索引到底吃了几斤几两key_len是很多人在读执行计划时会忽略的列但它恰恰是判断复合索引利用率的核心依据。它表示MySQL在本次查询中实际使用的索引前缀长度单位是字节。对于单列索引key_len通常固定对于复合索引比如(a, b, c)三个字段如果key_len只等于a字段的长度说明只用到了索引的最左前缀b和c没有参与匹配。key_len的计算规则不复杂但非常容易算错。以utf8mb4字符集下常见的varchar(50) NOT NULL字段为例utf8mb4字符集每个字符最多占4字节所以这个字段的理论最大字节数是50×4200字节。由于是变长字段MySQL还需要额外2字节记录实际长度允许NULL时还要再加1字节。如果字段加上NOT NULL约束那就没有这1字节了。举一个我常用的例子CREATE TABLE users ( id INT NOT NULL, name VARCHAR(50) NOT NULL, age INT NOT NULL, KEY idx_name_age (name, age) );在这个表上执行EXPLAIN SELECT * FROM users WHERE name abc; EXPLAIN SELECT * FROM users WHERE name abc AND age 20;第一条语句的key_len是202第二条是206。计算逻辑是name在utf8mb4下是50×42202字节age是INT占4字节加起来206。如果看到第一条的key_len是202、第二条是206就说明复合索引只用到了name一个字段或者nameage都用上了。学会看key_len之后你能做一件很实用的事不用猜优化器的意图直接通过字节数验证复合索引的利用程度。比如索引建的是(category_id, create_time)执行计划里key_len如果只等于category_id的长度说明排序列create_time没有参与索引匹配这时候出现Using filesort就可以理解了。2.3 rows与filtered估算值背后的判断逻辑rows列表示优化器预估需要读取的行数这个值是成本模型的核心输入。它不是真实返回行数只是基于统计信息做的估算。我把一个包含100万行的表做过测试EXPLAIN显示rows有时会偏差三四倍尤其在统计信息过期或者条件列数据分布严重倾斜时。所以看到rows不要当成真值把它理解为优化器心中的“预期工作量”。filtered列从MySQL 5.7开始出现在EXPLAIN输出中表示存储引擎返回的数据经过WHERE条件过滤后预计还剩多少比例的行。比如rows10000filtered10意味着预计有1000行会进入下一阶段处理。在JOIN场景里这个值对判断驱动表的过滤效果很有帮助。读这两列的正确姿势是结合看。如果typeALLrows50万filtered1说明优化器认为虽然要扫全表但最终只有500行通过过滤条件。这种计划不好看但并非不可接受可能因为小表全表扫描比走索引更快。反过来typerefrows100filtered100说明每条匹配的行都会被保留过滤条件帮不上忙那就要思考是否需要在SQL层面增加更 selective 的过滤条件。实践中我发现一个高频误判很多人看到rows很小就觉得查询没问题忽略了filtered可能很低暗示的统计问题。如果在EXPLAIN里看到rows和实际情况明显不符先执行一次ANALYZE TABLE更新统计信息再重新看计划往往会有不一样的结果。3. 三个实战案例从看懂执行计划到动手优化3.1 案例一无索引条件下的全表扫描订单查询某次排查一个订单列表接口业务方反馈最近数据量上来之后越来越慢高峰期甚至要两秒多。SQL大概是这个形态SELECT * FROM order_info WHERE status 1 ORDER BY create_time DESC LIMIT 20;EXPLAIN的结果是idselect_typetabletypekeyrowsExtra1SIMPLEorder_infoALLNULL480000Using where; Using filesorttypeALLkeyNULLrows显示48万行Extra里还带着Using filesort。这条SQL涉及两块开销全表扫描48万行然后对所有满足status1的记录做排序取前20。status1在这个业务里大约占全表的四分之一也就是说要扫描48万行、排序十几万行耗时自然压不下来。优化的方向很明确建立复合索引让过滤和排序同时走索引。ALTER TABLE order_info ADD INDEX idx_status_create_time (status, create_time);建完之后的执行计划变成idselect_typetabletypekeykey_lenrowsExtra1SIMPLEorder_inforefidx_status_create_time212000NULLtype提升到refrows降到1.2万Extra里的Using filesort消失了。为什么排序消失因为复合索引把status字段放在前面、create_time放在后面等值匹配status1之后索引叶子节点上的create_time天然有序ORDER BY create_time DESC可以直接反向扫描索引取前20条完全不需要额外排序。实测这个接口从1.8秒降到30毫秒左右。这里有个细节值得展开如果SQL里过滤条件不止status比如还要带上user_id那复合索引的设计就得重新考虑最左前缀原则。核心方法是把等值筛选字段放前面把排序字段放后面让索引顺序与查询需求对齐。基于这个案例的经验回看所有“在线列表类查询”只要满足“等值过滤排序分页”的模式基本都能用同一套思路解决。3.2 案例二filesort排序代价被复合索引消灭商品列表页有这样一个查询按分类筛选商品再按销量倒序取前10条SELECT id, name, price FROM products WHERE category_id 5 ORDER BY sold_count DESC LIMIT 10;原始表上只有category_id单列索引idx_category。EXPLAIN结果如下idselect_typetabletypekeyrowsExtra1SIMPLEproductsrefidx_category8000Using where; Using filesort这个例子和案例一很像但有个关键区别索引上只有category_idsold_count排在索引之外所以即使WHERE过滤走索引ORDER BY还是无法借用索引顺序必须对所有满足category_id5的记录做一次文件排序然后再取前10。当这个分类下有几万商品时filesort的代价就很可观了。我给的建议是增加一个覆盖查询需求的复合索引ALTER TABLE products ADD INDEX idx_category_sold (category_id, sold_count);执行计划变为idselect_typetabletypekeyrowsExtra1SIMPLEproductsrefidx_category_sold8000NULL注意这里Extra里连Using where都不见了因为条件都通过索引直接完成排序直接走索引倒序取10条回表后返回。这种优化本质上是用索引的有序性替代了数据库额外的排序动作代价是每次写数据时要多维护一棵索引树所以索引不是越多越好需要结合查询频率和写放大去权衡。这里也想纠正一个观点Using filesort不代表一定慢。MySQL的filesort如果排序数据量小可能在内存中完成也可以使用optimized single-pass排序算法性能未必差。我之前遇到过一个SQLExtra里一直显示Using filesort但查询本身只要十几毫秒完全没必要折腾。判断标准永远是实际耗时和工作集大小而不是看到某个关键词就动手改。3.3 案例三多表JOIN驱动顺序怎么选联表查询的执行计划里第一行决定的往往不是SQL里写在FROM后面的第一个表而是优化器选择的“驱动表”。驱动表会先被读取然后用它的每一行到被驱动表里去匹配。优化器的目标是让全程代价最小一般来说是选择过滤后行数更少的表作为驱动表。看这个例子SELECT o.order_id, u.nickname, o.amount FROM order_info o INNER JOIN user_info u ON o.user_id u.id WHERE u.vip_level 3;假设order_info有50万行user_info有10万行vip_level3的用户大约500人。执行计划如下idtabletypekeyrowsExtra1user_inforefidx_vip_level500Using where; Using index condition2order_inforefidx_user_id1NULL第一行驱动表是user_info第二行是被驱动表order_info。优化器先取出500个VIP用户然后对每个用户去order_info里用user_id索引查找订单总匹配量是500×平均订单数。相比直接用order_info当驱动表扫完50万行再逐个去user_info匹配代价要低得多。阅读这种执行计划时还有一个很实用的观察点检查被驱动表的关联列上是否建立了索引。如果被驱动表的type是ALL而且Extra里出现Using join buffer说明关联列没有索引MySQL要把驱动表的批量结果放到JOIN buffer里和被驱动表逐行做全表匹配。这种情况对大数据集是灾难性性能问题。只要给被驱动表的关联列加上合适的索引type通常会变成ref或者eq_ref整个查询立刻改观。关于驱动顺序要不要人工干预我的经验是不太需要。MySQL的成本模型大多数情况比人拍脑袋准确与其用STRAIGHT_JOIN强制顺序不如先把统计信息刷够把必要的索引建好让优化器自己选。强制顺序只是临时手段而且很可能在数据量变化后变得不再最优。4. 高频问题排查与进阶技巧4.1 常见执行计划问题速查表把日常工作中反复遇到的情况整理成一张速查表遇到问题直接对号入座效率会高很多。限于篇幅我列几个出现频率最高的组合现象可能原因排查思路typeALLrows很大缺少合适索引索引条件因函数/隐式转换失效优化器认为全表扫描成本更低先确认WHERE字段是否有索引再看有没有对索引列做运算最后用小表场景验证有索引但keyNULL查询条件不满足最左前缀数据量太小优化器放弃索引检查复合索引字段顺序或者用ANALYZE TABLE刷新统计信息后重看计划ExtraUsing filesortORDER BY字段不在索引中复合索引字段顺序与排序方向不匹配分析排序字段能否加入到已有索引注意等值字段放前、排序字段放后ExtraUsing temporaryGROUP BY或DISTINCT字段与索引顺序不一致部分聚合操作无法消去临时表调整索引顺序让group by字段走索引必要时接受合理临时表key_len小于预期复合索引只用了最左前缀的一部分计算各字段字节长度判断查没查出索引的能力边界filtered很低统计信息过期过滤条件背后的数据分布与均匀假设偏差大执行ANALYZE TABLE后复测考虑单独统计高频值Using index condition出现但rows还是大索引下推确实生效但匹配范围本身宽尝试用范围更精确的条件或者重新设计联合索引让更多行列进索引排查这类问题有个通用顺序先看type判断有没有全表扫描级别的问题再看key和key_len确认索引是否被正确使用接着看rows和filtered估算代价是否合理最后根据Extra里的排序、临时表、join buffer等信号定位细节。4.2 SHOW WARNINGS和EXPLAIN ANALYZE纸面判断如何验证EXPLAIN输出的是优化器计划但很多时候我们想知道优化器到底对我们写的SQL做了哪些“手脚”。MySQL里有一个被严重低估的命令在EXPLAIN之后执行SHOW WARNINGS可以看到优化器重写后的SQL语句。比如写过一些复杂IN子查询、派生表嵌套时实际执行的SQL和原始SQL可能已经形态不同。SHOW WARNINGS的Message列会展示优化器改写后的版本这能帮你理解为什么某个索引被选上、某个子查询被转成了JOIN。以前遇到一个IN子查询的性能问题原始语句写法是IN (SELECT id FROM ...)EXPLAIN显示有临时表。后来用SHOW WARNINGS一看优化器已经把IN改写成了EXISTS形态而且EXISTS子查询有独立的执行计划。顺着这个思路调整了子查询的结构问题当场清晰很多。FORMatJSON同样值得用起来。它会暴露优化器的成本数据比如read_cost、eval_cost、prefix_cost这些数值能直观回答“为什么优化器放弃了我觉得更好的索引”。对比两个不同索引方案的JSON成本比靠猜靠谱得多。而在MySQL 8.0.18及以上版本中EXPLAIN ANALYZE把纸面计划变成了真实测量。执行这个命令会真的运行SQL并输出每个执行步骤的actual time、actual rows和loops。它对定位“优化器估算与实际严重偏离”的问题特别有效。不过要切记这是会真实执行的线上大查询需要谨慎使用最好通过只读实例操作。我在一次复杂报表SQL的调优中EXPLAIN显示rows只有几百但实际执行要好几秒。当时差点去改SQL结构。后来用EXPLAIN ANALYZE跑了一次才发现其中一个步骤actual rows达到了十几万与估算差了上百倍根因是历史统计信息完全没有更新。清理统计信息并重建统计后执行计划恢复正常。这个经历提醒我EXPLAIN只是工具最终要以实测为准。4.3 关于索引失效的几个经典误区最后聊几个我在带人和复盘过程中反复遇到的误区每一个都对应过真实的线上事故。第一个误区是“索引一定不会失效失效一定是SQL写法问题”。实际上优化器选择全表扫描很多时候是因为它计算出全表扫描成本更低。比如一张只有几千行的配置表就算WHERE字段有索引优化器也很可能选择ALL因为顺序读几千行比随机IO回表还要快。这时候看到ALL不用慌先看数据量再判断。第二个误区是把“覆盖索引”和“走索引”混为一谈。走索引不等同于不回表。比如SELECT *加上二级索引等值查询如果查询列不在索引里MySQL拿到索引中的引用后还要回表取完整行。Extra里没有Using index就说明回表发生了。真正避免回表的方法是设计覆盖索引让查询需要的字段全部包含在索引中这时Extra才会出现Using index。第三个误区是认为出现Using filesort就必须优化。我刚才说过filesort在小结果集下真的不值一提。不要为了消除一个无伤大雅的filesort去建一堆索引写库压力会把你坑回来。先看实际耗时再看数据规模最后才决定要不要为排序建索引。第四个误区是忽略统计信息的影响。优化器不是神它依赖的是表的统计信息。如果一张大表的统计信息长期不更新EXPLAIN的rows可能偏得离谱进而导致优化器选错索引。数据库不是出了性能问题才去ANALYZE TABLE而是应该在批量数据导入、大量更新删除之后主动刷新统计信息。最后再分享一个排查习惯这个习惯帮我少踩了很多坑。拿到一条慢SQL我从来不会只看一次EXPLAIN而是结合慢日志里的实际执行时间、临时表落盘情况、IO等待指标一起看。EXPLAIN把“优化器打算怎么做”告诉了你但数据库真正经历什么还需要靠真实运行时的观察来交叉验证。只有把纸面计划、成本和运行事实放在一起慢SQL分析才算做得扎实。希望这篇内容能帮你在下一次面对执行计划时更快找到那条通往正确索引和合理SQL的路径。

MySQL EXPLAIN执行计划详解：从慢查询定位到索引优化实战

相关推荐

XSS发现工具包：从爬虫采集到浏览器验证的自动化漏洞挖掘链路

如何为 LLM 挑选函数调用数据集：llm-datasets 的 8 个训练集快速上手指南

如何看懂 Ely Prism Launcher 的 HttpMetaCache：HTTP 元数据缓存避免重复下载的完整指南

更多资讯

Multi-Raft架构设计：从单Raft瓶颈到分布式分片扩展实践

AI大模型面试：RAG怎么处理PDF中的表格？AI不会管你的成本，不会管实现难度

Linux-多文件工程

西邮Linux兴趣小组2024纳新面试题

开源任务编排工具 QClaw 实战：从部署到二次开发的完整复盘

五类主流数据库等保合规配置差异与实操指南

今日更新

NetSpot 实战：用信道分析与频宽优化解决 Wi-Fi 卡顿

C++ split() 字符串分割的五种实现与性能实测

R语言坐标轴范围设置：从xlim/ylim到coord_cartesian的完整指南

本周精选

交通拥堵预测毕设：基于Python+LSTM的车辆流量预测系统全流程解析

MATLAB/Simulink搭建10机39节点电力系统暂态稳定仿真实战

无人机航拍三维重建全流程：从SfM到网格生成的避坑指南

本月热门

我发现了一个新思路：用 Remotion + Claude Code 像写代码一样自动化生成短视频

Windows下 Codex 中 Chrome 和 Computer Use 插件不可用问题排查及解决参考方式：TaoToken 统一 Key 配置与验证

2026 大模型集体涨价：用 Python 做企业 Token 成本测算与选型避坑（附配置）

读完文章，想聊聊您的网站？