刚入行那会儿我最怵的就是线上一条慢SQL甩过来业务方催得急我却连从哪儿下手都不知道。索引建了一堆性能该慢还是慢。后来带我的老大哥只丢了一句话“先跑一下EXPLAIN看看它到底怎么查的。”从那之后EXPLAIN就成我排查MySQL性能问题的第一工具也是唯一绕不开的工具。一句话说清楚EXPLAIN就是给你的SELECT语句生成一份“执行计划说明书”它把MySQL优化器打算怎么查这张表、走哪个索引、要不要临时表、要不要排序原原本本地摊开给你看。你不需要猜也不需要试一条SQL跑得慢先EXPLAIN一下十有八九问题摆在明面上。这篇文章不打算抄手册我把自己用EXPLAIN的完整思路、看输出表的经验顺序、还有踩过的一些坑全部展开讲一遍。想让查询变快的开发、运维或者刚接触MySQL优化的新手都能从这里拿到一套能直接用的排查方法。1. EXPLAIN输出里的每一列到底在说什么很多同学一看到EXPLAIN输出十几列第一反应是懵。其实没必要一次全看懂你只要抓住几个核心列90%的性能问题都能定位出来。我的习惯是先看type和key再看rows和Extra最后用key_len和ref验证细节。我直接拿一个常见查询做演示你先感受一下输出长什么样。EXPLAIN SELECT o.order_id, u.user_name FROM orders o JOIN users u ON o.user_id u.id WHERE o.status 1 ORDER BY o.created_at DESC LIMIT 10;执行后大概会返回这么一张表列名因版本略有差异MySQL 8.0一般完整包含这些id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra下面逐列拆开讲。1.1 id这条SQL里到底有几个SELECTid不是执行顺序而是SELECT的编号。一个查询里出现的SELECT越多id就越多。理解id有个简单规则id相同的一组从上到下依次执行代表这些表在同一个查询里做关联id不同的行id越大越先执行因为子查询要先生成结果外层才能用。举个例子一条带子查询的SQLexplain出来会有两行子查询那行id是2外层查询id是1实际执行时id2的先跑。如果你看到id为NULL的行一般代表UNION的合并结果行它负责把多个SELECT的结果去重或合并后返回。看id最大的价值是帮你判断SQL的复杂度——id层次很深、数量很多的时候通常意味着查询被拆成了很多步性能隐患已经埋下了。注意id不是越小越快它只是编号。真正决定快慢的是后面type和rows别被id误导。1.2 select_type每个SELECT扮演什么角色这一列描述的是当前行对应的SELECT类型我用一张表把常见的列全了。select_type含义需要关注吗SIMPLE简单查询不含子查询、不含UNION不用特别关注PRIMARY最外层的SELECT比如子查询外面的主查询不用特别关注SUBQUERY非相关子查询子查询先执行、结果固定少量可以接受DEPENDENT SUBQUERY相关子查询外层每读一行子查询就执行一次重点关注通常很慢DERIVEDFROM后面的子查询生成的派生表注意是否被物化成临时表UNIONUNION中第二个及之后的SELECT不用特别关注UNION RESULT从UNION临时表里读结果的行不用特别关注UNCACHEABLE SUBQUERY子查询结果无法缓存每次都要重算重点关注基本是性能杀手我最想提醒的是DEPENDENT SUBQUERY。以前排查过一个慢查询外层表50万行子查询结果明明和外部表没关系但因为写在了WHERE. EXISTS里优化器没做去关联化导致每一行都要执行一次子查询整体耗时直接爆炸。这种SQL的正确改法是改成JOIN或者把子查询结果先算出来。EXPLAIN里看到DEPENDENT SUBQUERY第一反应就应该是“这里要改写”。1.3 table告诉你在访问哪张表这列比较直观显示当前行访问的表名或别名。需要留意的是可能出现这几种特殊值derivedN访问的是id为N的派生表也就是FROM子句里那坨子查询生成的临时表unionN,M访问的是UNION合并出来的结果集subqueryN访问的是物化子查询看到derivedN的时候你要意识到优化器把子查询结果物化成了临时表这个临时表有没有索引直接决定后续关联快不快。MySQL 8.0在这方面优化了不少会考虑自动给派生表加索引但5.7及以下版本还是经常在这里栽跟头。1.4 partitions命中了哪些分区只有在使用分区表时这一列才有意义显示的是查询会访问的分区编号。没分区的表这列显示为NULL。平时开发遇到的少但如果你的表是分区表EXPLAIN能看出有没有做分区裁剪——如果明明只查一个月的数据却命中了所有分区说明SQL写法或分区键用得不到位扫描量白白翻了几倍。2. type列决定访问方式这张表把执行效率排了个队如果说EXPLAIN只需要看一列那一定是type。它描述的是MySQL在表里找到目标行的方式从最好到最差大概是这个顺序system const eq_ref ref range index ALL记住这个排序你心里就有了一把尺子。下面逐个说重点讲实际开发里最常见的几种。2.1 从system到const理论上最快的方式system表只有一行基本是系统表或临时表才可能出现普通业务表见不到const用主键或唯一索引的等值匹配最多返回一行所以是常量级别的查找平时写WHERE id 10086type就是const。这种SQL没得说已经是天花板了。看到一个查询type是const基本可以放心它的瓶颈只会在别的地方比如ORDER BY或者JOIN的其它表。2.2 eq_ref和refJOIN场景里的两个关键角色eq_ref出现在JOIN关联查询里表示被驱动表是通过主键或唯一索引去匹配的对于驱动表返回的每一行被驱动表最多只有一行能配上。这是JOIN的理想状态性能极好。ref则是指用普通二级索引做等值匹配可能匹配到多行。比如WHERE user_id 123user_id上有普通索引返回的可能是这个用户的10条订单type就是ref。这两者最大的区别在于“匹配到的行数是否唯一”eq_ref是一对一ref是一对多。你在EXPLAIN里看到eq_ref和ref说明索引生效了需要关注的是rows列看它估算要读多少行。2.3 range和index一个合格一个勉强及格range索引范围扫描常见于BETWEEN、、、IN、LIKE abc%这类查询。走了索引但扫的是索引里的一段范围比ref更重一些但仍然可以接受index全索引扫描。它和ALL的区别是ALL扫的是整张表的数据index扫的是整棵索引树。如果查询的列正好都在索引里覆盖索引场景type会显示index数据全在索引里有时候效率还不错但本质上是全量遍历数据量大了还是会慢我看到typeindex时不会直接判死刑会结合key_len和Extra再判断。如果索引体积比表小很多且只返回少量字段那覆盖索引的index扫描甚至比回表还快。2.4 ALL全表扫描性能重灾区typeALL意味着MySQL把整张表的聚簇索引从头扫到尾每一行都翻一遍。小表还好一旦百万级以上的表出现ALL几乎必然导致慢查询。我在实际排查中看到ALL的第一反应不是急着加索引而是先去分析WHERE条件——到底是没索引还是有索引但没被用上。这两者处理方式完全不同。牢记一条经验一个OLTP系统的核心查询type至少应该到range最好稳定在ref及以上。如果出现系统性、高频的ALL别心存侥幸这是迟早要出事的。2.5 possible_keys和key优化器的“候选”和“选择”possible_keys列出这个查询可能用到的索引优化器根据条件候选出来的key则是最终实际选用的索引。经常出现一种情况possible_keys里有索引名key却是NULL说明优化器判断用这个索引还不如全表扫描快。为什么不用明明存在的索引最常见两个原因索引的选择性太差。比如性别字段区分度极低如果用索引要回表查大量行优化器算下来全表扫描更划算数据量太小。表一共几百行全表扫描成本趋近于零走索引反而要额外读索引页得不偿失这种场景别硬加索引优化器的选择通常是合理的。真正问题大的反而是possible_keys为空那说明WHERE条件里的字段根本没有可用的索引属于“无米下锅”这时候才需要考虑建索引。3. 通过key_len反推联合索引用到了哪几列不少同学看EXPLAIN只看type和rows忽略了key_len这其实浪费了最重要的信息。key_len表示的是“MySQL在索引中实际使用的字节数”它不是数据本身的长度而是索引键值的长度。通过key_len你能精确判断出联合索引到底用到了哪几列以及索引使用是否完整。3.1 先掌握基础类型的字节长度在innodb里key_len的计算依赖字段类型和字符集。常见的参考值我整理了一下列类型字节数备注INT4不管UNSIGNED都是4字节BIGINT8同理DATE3日期类型存储压缩DATETIME8MySQL 5.6之后是8字节TIMESTAMP4时间戳存储CHAR(n)字符集单字符字节数*nutf8mb4下是4*nVARCHAR(n)字符集单字符字节数*n 2额外2字节记录变长长度可空列额外1NULL标志位占1字节这里有个高频计算场景utf8mb4字符集下VARCHAR(50)一个字段的key_len是50 * 4 2 202如果是可空字段就是203。3.2 用key_len判断联合索引前缀假设有一张表联合索引是idx_user_type_status(user_type, status, create_time)其中user_type是INTstatus是INTcreate_time是DATETIME都非空。如果WHERE里只用了user_type 1key_len应该是4如果WHERE里用了user_type 1 AND status 2key_len应该是8如果三个条件都用了key_len应该是16**key_len一变长说明联合索引往右多“吃”进了一列。**这个信息特别有用因为有些SQL你看着像三个条件都走到索引了实际上最右边的字段由于范围查询、函数包裹等原因没进索引key_len直接暴露真相。有一次我看同事排查慢查询EXPLAIN里possible_keys明明写着联合索引type也是range但rows还是很高。我让他看一眼key_len发现只有4立刻明白他其实只用了联合索引的第一列后面两列都白搭了。3.3 实战一条SQL的key_len推演比如这个查询EXPLAIN SELECT * FROM orders WHERE user_id 1024 AND status 3 AND created_at 2024-01-01;索引是idx_user_status_time(user_id, status, created_at)三列都是INT/NON-NULL的话user_id等值匹配占用4字节status等值匹配占用4字节created_at是范围条件索引只能用到“定位到范围起点”key_len到此为止不再增加所以最终key_len8说明created_at没有作为等值条件参与索引定位。这个信息直接决定了你对SQL的优化方向——如果想把created_at也“用满”唯一办法是把它也变成等值条件或者调整索引顺序。范围字段放最后这条索引设计原则本质上就是由key_len的工作原理决定的。4. Extra列出现的几个危险信号filesort、temporary等Extra列是EXPLAIN里的“备注信息”但很多时候它比type更致命。typeALL还能通过加索引救回来如果Extra里出现Using filesort和Using temporary往往意味着内存和CPU在偷偷消耗而且问题更隐蔽。4.1 Using filesort排序没走索引看到“filesort”别慌它不是指文件排序就一定用了磁盘实际上内存排序也这么叫。它的意思是**MySQL没能在索引顺序上直接拿到有序结果必须自己额外做一次排序操作。**数据量小的时候无所谓一旦排几万行以上排序的CPU开销和临时空间消耗就会明显拖慢查询。典型的反面案例SELECT * FROM orders WHERE user_id 10086 ORDER BY created_at DESC;如果只有user_id索引EXPLAIN会看到typeref但Extra里大概率有Using filesort。因为索引只能帮你快速定位user_id10086的行但created_at的顺序在二级索引里并没有和user_id连在一起MySQL只能把命中的行取出来再排一遍。解决办法很经典把排序字段加到索引里去比如改成联合索引(user_id, created_at)这样二级索引本身在user_id相同的情况下已经按created_at排好了序filesort直接消失。4.2 Using temporary临时表的代价比你想的高Group By、Distinct、Union这类操作经常会触发临时表。如果查询涉及大量数据MySQL可能会先在内存里建临时表不够大再落盘到磁盘临时表这个过程极伤性能。最常见的场景是GROUP BY和ORDER BY字段不一致或者Group By的字段没有索引支撑。比如SELECT status, COUNT(*) FROM orders GROUP BY status;如果status上没有索引Extra里就会出现Using temporary; Using filesort。这在数据量大的时候非常难受——每一行都要进临时表、分组、排序。我的建议和filesort一样优先让分组字段走索引实在不行考虑在应用层做预聚合或改用汇总表。4.3 Using index这个要开心别误会Extra里出现Using index表示“覆盖索引”意思是查询需要的列全部在索引里可以直接从索引返回结果不需要回表。这个属于求之不得的好信号。但注意一个常见误解看到Using index不代表SQL没问题。如果type还是ALL或index说明它仍然是全索引扫描只是因为没有回表所以比ALL略好。覆盖索引能解决的是回表带来的随机I/O解决不了扫描量本身大的问题。4.4 Using index conditionICP索引条件下推MySQL 5.6开始支持的优化表示部分WHERE条件被下推到存储引擎在索引层先过滤一批数据减少回表次数。比如联合索引(a, b)查询条件是a 1 AND b LIKE x%b不是精确匹配但能参与过滤就可能出现Using index condition。这不是坏事说明优化器在努力帮你省成本但它也暗示着b没有完全用上索引等值匹配。4.5 Extra常见值速查表Extra值实际含义处理建议Using index覆盖索引不回表理想保持Using where索引过滤后Server层又做了一次过滤检查条件能否进索引Using index conditionICP下推过滤正常可尝试优化索引列Using filesort额外排序把排序字段加进索引Using temporary使用临时表让分组/去重字段走索引Using join bufferJOIN没走索引用了Join Buffer给关联字段加索引Impossible WHEREWHERE条件恒为假检查业务逻辑Zero limitLIMIT 0不会执行查询无需处理提醒一下Using join bufferhash join在MySQL 8.0.18 里比较常见等值JOIN没索引时优化器会尝试哈希连接。它比老版本的BNL快一些但依然意味着被驱动表没走索引该加索引还是得加。5. 实战三个慢查询用EXPLAIN定位并解决的完整过程理论讲了半天不如直接上三个我实际排查过的案例。每个案例我都按“现场描述 - EXPLAIN输出 - 分析 - 优化结果”的顺序写你可以直接当模板套用。5.1 案例一订单列表页加载慢翻页越翻越慢现场是一个电商后台的订单列表SQL长这样SELECT * FROM orders WHERE user_id 12345 ORDER BY created_at DESC LIMIT 10;EXPLAIN输出关键列type: ref possible_keys: idx_user_id key: idx_user_id key_len: 4 rows: 356 Extra: Using filesort这个查询其实已经用到user_id索引了但问题出在Extra里的Using filesort。user_id12345的订单有356行MySQL把这356行全部捞出来再按created_at做一次内存排序最后取10条。优化方案是把索引改成联合索引idx_user_created(user_id, created_at)。改完后EXPLAIN变成type: ref key: idx_user_created key_len: 4 rows: 356 Extra: NULLfilesort消失因为索引已经按user_idcreated_at排好了MySQL沿着索引读前10条就是结果查询耗时从300ms降到5ms。这个case属于典型的“查询快但排序慢”很多人只看type忽略了Extra就会错过真正的优化点。5.2 案例二JOIN查询全表扫小表驱动大表是关键现场是一条报表SQL关联两张表业务方反馈跑一次要十几秒SELECT u.user_name, COUNT(o.order_id) FROM users u LEFT JOIN orders o ON u.id o.user_id WHERE u.level 3 GROUP BY u.id;EXPLAIN里orders表的type是ALLExtra是Using join buffer (hash join)rows显示120万。问题很清楚orders.user_id上没有索引导致每一行users都要和整个orders表做关联。120万行的大表被全表扫描慢是必然的。我做的第一件事是在orders.user_id上建索引ALTER TABLE orders ADD INDEX idx_user_id(user_id);再EXPLAINorders表的type变成了refrows从120万降到个位数query秒回。这个case说出一个最基础的JOIN原则被驱动表的关联字段必须有索引。还有一个潜在问题LEFT JOIN在GROUP BY COUNT时容易产生重复计数也要留意业务口径。5.3 案例三GROUP BY统计查询出现temporary filesort双杀现场是一个按天统计用户增长数的查询SELECT DATE(created_at) AS d, COUNT(*) FROM users GROUP BY DATE(created_at);EXPLAIN关键列type: ALL rows: 80万 Extra: Using temporary; Using filesort这个SQL犯了两个典型错误。第一DATE(created_at)对索引字段做了函数处理导致索引失效type变成ALL第二GROUP BY的表达式不是索引的原始列排序和分组都必须靠临时表完成。我当时的优化方案分两步。第一步SQL改成范围条件写法SELECT DATE(created_at) AS d, COUNT(*) FROM users WHERE created_at 2024-01-01 AND created_at 2024-02-01 GROUP BY DATE(created_at);这样至少把扫描量限制在一个月的数据范围内。第二步如果查询频率高直接在表里冗余一个day字段并在(day)上建索引GROUP BY直接走day列临时表和filesort全部消失。我给你的通用结论是**能用范围改写就用范围改写能避免函数包裹就避免函数包裹GROUP BY的字段尽量是索引原生列。**统计报表如果数据量大别指望一条SQL硬扛物化汇总表才是更稳的方案。6. 用EXPLAIN时容易踩的坑和几个查漏小技巧最后分享一些我在实际排查中总结的经验包括几个特别好用但容易被忽略的EXPLAIN功能。6.1 EXPLAIN显示的rows是估算值不是实际值这是新手最容易踩的坑。EXPLAIN本身不执行查询它只是基于统计信息和成本模型估算出一个执行计划rows列的数值是优化器“猜”出来的。你看到rows356实际可能只有30行也可能有3000行。如果发现rows和实际数量出入太大往往是表统计信息过期了建议先跑一下ANALYZE TABLE刷新统计信息。MySQL 8.0.18开始提供的EXPLAIN ANALYZE会真实执行查询并输出实际行数和耗时适合用来和EXPLAIN的估算做对比。差别巨大的时候基本可以断定优化器基于错误的统计信息做了错误选择。6.2 索引失效的几个高频写法EXPLAIN会直接暴露WHERE后面用了函数WHERE DATE(created_at) 2024-01-01索引失效type直接变ALL隐式类型转换WHERE phone 13800138000phone字段是VARCHAR等号右边是数字MySQL会把字段转成数字再比较索引失效LIKE左侧通配符LIKE %abc索引失效OR连接的条件里有一个字段没索引整个查询可能走全表扫描联合索引违反最左前缀原则EXPLAIN里key_len会告诉你真相6.3 用EXPLAIN FORMATJSON看更多隐藏细节普通格式的输出已经够用但有些场景你需要更底层的成本数据。MySQL 8.0支持EXPLAIN FORMATJSON SELECT ...输出里包含cost_info、used_key_parts、rows_examined_per_scan这些关键字段。我最常用used_key_parts来确认联合索引到底用到了哪几列它比手工算key_len更直观。还有cost_info里的read_cost和eval_cost能看到优化器估算的I/O成本适合在多个执行计划之间做对比。虽然绝大多数情况不需要这么细但遇到棘手问题时JSON格式能给到超出普通EXPLAIN的信息维度。6.4 不要忽略SHOW WARNINGS给出的优化器改写SQLEXPLAIN执行完后紧接着执行SHOW WARNINGSMySQL会把优化器实际重写后的查询语句显示出来。这个技巧是我排查“为什么我写的SQL和预期效果不一样”时的救命稻草。见过一个案例我明明写的WHERE a 1 OR b 2EXPLAIN却显示走了奇怪的方式用SHOW WARNINGS一看优化器把OR改写成UNION了——这就是为什么EXPLAIN里会出现UNION RESULT行。了解优化器怎么改写的你才能真正理解它的意图而不是靠自己脑补。6.5 EXPLAIN能看执行计划但看不到所有执行问题这一点必须说清楚EXPLAIN只回答“优化器打算怎么查”回答不了“实际执行时有没有锁等待”“有没有杀进程”“事务里有没有大量回滚”。我工作中见过不少同事SQL慢就疯狂EXPLAIN查半天发现type已经是ref了性能还是上不去最后用performance_schema一看是锁等待和批量写入导致的行锁竞争。EXPLAIN是定位SQL性能问题的起点不是终点。遇到慢查询先用EXPLAIN排除执行计划问题再检查锁、I/O、事务隔离级别这个排查顺序才是完整的。6.6 最后分享一个排查习惯我自己每次看EXPLAIN固定按三步走先看type低于range的直接标红再看key_len和Extra确认索引是否完整使用、有没有filesort或temporary最后看rows和filtered评估实际扫描量是否合理。这套流程下来一条SQL的问题基本无所遁形。还有个小建议建索引不要贪多。索引不是越多越好每多一个索引写入和更新就多一份维护成本。EXPLAIN让你看清哪些索引真正被用到了多余的就果断删掉这比单纯加索引更重要。记住EXPLAIN是拿来看的真正把索引和SQL理顺还是得靠你对数据分布和业务场景的理解。