MySQL慢查询优化实战：从EXPLAIN读懂执行计划与索引设计

1. 背一百遍 type 的含义不如亲眼看一次全表扫描我先说个真实场景。前阵子团队做代码评审一个小伙子信誓旦旦说他知道typeALL意味着全表扫描typeref是普通索引查找typeconst是主键或唯一索引等值查询。等我把一条实际慢查询的EXPLAIN输出甩到他面前问他这条语句到底慢在哪、该怎么改他盯着输出看了半天憋出一句这个 rows 是啥意思来着。这就是典型的八股背得熟动手全抓瞎。慢 SQL 优化这件事你可以在面试前把EXPLAIN的每个列背得滚瓜烂熟但只要没亲手跑过几次、没对着真实业务的慢查询分析过那些知识点永远是悬在空中的。所以我特别想写一篇纯实战向的东西不堆理论就拿真实 SQL 一步步拆给你看告诉你EXPLAIN输出到底怎么读、读完怎么定位问题、定位完怎么改。这篇文章适合谁适合刚接触 SQL 优化、想系统搞懂EXPLAIN的后端开发也适合写过不少 SQL 但一直靠加索引就行糊弄过去的同学。文章会用 MySQL 8.0 环境下的实际输出做演示8.0 和 5.7 的EXPLAIN输出基本一致个别字段有差异部分细节我会单独标注版本差异。看的过程中建议你打开自己的数据库找一条平时执行比较慢的查询跟着走一遍效果比干看强十倍。先明确一个基本前提EXPLAIN的作用是让 MySQL 优化器把它打算怎么执行这条 SQL告诉你。注意是打算不是实际执行结果。它输出的是执行计划先读哪张表、用哪个索引、预估扫多少行、是否需要排序、是否需要临时表。你读懂了这份计划就等于看穿了优化器的思路然后才能判断它的思路是不是有问题、要怎么引导它走更好的路径。举个最直观的例子。假设有张用户订单表结构大致是CREATE TABLE t_order ( id bigint NOT NULL AUTO_INCREMENT, user_id bigint NOT NULL, order_no varchar(64) NOT NULL, status tinyint NOT NULL DEFAULT 0, amount decimal(10,2) NOT NULL, created_at datetime NOT NULL, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;表里我插了 20 万行测试数据其中user_id是随机分布的大概 5 万个不同值。执行这条查询EXPLAIN SELECT * FROM t_order WHERE user_id 12345;你会看到类似这样的输出id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra 1 | SIMPLE | t_order| NULL | ALL | NULL | NULL | NULL | NULL | 198432| 10.00 | Using wheretypeALLkeyNULLrows198432。翻译成人话就是MySQL 打算把整张表从头到尾扫一遍20 万行全看然后挑出user_id12345的。想象一下数据量到 2000 万、2 亿的时候这条 SQL 会慢成什么样。这就是全表扫描的真实长相。你在八股里背一百遍ALL 是全表扫描性能最差不如亲眼在输出里看到一次typeALL、rows20万带来的冲击感强。对比一下我们在user_id上建个普通索引ALTER TABLE t_order ADD INDEX idx_user_id (user_id);再跑一次同样的EXPLAINid | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra 1 | SIMPLE | t_order| NULL | ref | idx_user_id | idx_user_id | 8 | const | 42 | 100.00 | NULL同样是这条 SQL执行计划完全变了type从ALL变成refkey从NULL变成idx_user_idrows从 198432 降到 42。这意味着 MySQL 只需要通过索引精确定位到 user_id12345 的记录然后回表读取完整行数据。索引前后预估的扫描行数相差近 5000 倍。这就是EXPLAIN最核心的价值它让你在 SQL 真正跑起来之前就看到优化器的执行路线从而提前判断这条路走得对不对。这里有个细节值得注意建索引之前Extra列是Using where建索引之后Extra变成了NULL。为什么会这样因为没索引时MySQL 得把全表数据读出来再用where条件逐行过滤这个过滤动作被标记为Using where有索引后MySQL 直接在索引树里定位根本不需要额外的过滤步骤所以Extra为空。同样一个where条件有没有索引支撑在优化器眼里的成本模型是完全不同的。所以我的建议是别满足于背结论手动建一张测试表、插个几十万行数据反复执行不同 SQL 的EXPLAIN观察索引对type、rows、Extra的影响。这种体感一旦建立起来你以后看任何一条慢查询脑子里会自动浮现出它可能的执行计划长什么样定位问题的速度会快非常多。2. EXPLAIN 每一列到底在说什么从 id 到 filtered 的完整拆解很多同学看EXPLAIN输出只看type和key觉得这两个字段最关键。这个方向没错但如果你想真正掌握执行计划的解读每一列都有它存在的意义而且某些情况下恰恰是那些不起眼的列比如key_len、Extra暴露了真实问题。下面我把每个字段逐个过一遍结合实际案例说明该怎么读、读到什么值该警惕。2.1 id 和 select_type认清查询的复杂结构id是查询中每个SELECT子句的编号。如果是单表简单查询id永远是 1。如果你看到id有多个不同的值说明这条 SQL 里包含了子查询、联合查询或者多表关联执行顺序也有讲究id越大越先执行id相同则从上往下执行。select_type表示这个SELECT的类型常见的有SIMPLE最简单的查询没有子查询和 UNION。PRIMARY最外层的查询。SUBQUERY子查询非 FROM 子句中的。DERIVEDFROM 子句中的子查询MySQL 会把它当成一个临时表来处理。UNIONUNION 中的第二个或后续的 SELECT。DEPENDENT SUBQUERY依赖外层查询结果的子查询这种往往意味着性能隐患。举个多表关联的例子。假设除了订单表还有一张用户表t_user结构是CREATE TABLE t_user ( id bigint NOT NULL AUTO_INCREMENT, name varchar(32) NOT NULL, phone varchar(20) DEFAULT NULL, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;查询某个用户名下的所有订单金额大于 100 元的订单EXPLAIN SELECT o.* FROM t_order o INNER JOIN t_user u ON o.user_id u.id WHERE u.name 张三 AND o.amount 100;输出的前面几列大概长这样id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra 1 | SIMPLE | u | ref | idx_name | idx_name| 98 | const | 1 | 100.00 | Using index condition 1 | SIMPLE | o | ref | idx_user_id | idx_user_id| 8 | test.u.id | 42 | 33.33 | Using where注意这里id都是 1说明两个表是关联查询执行顺序由优化器决定通常是谁的过滤效果更好、成本更低谁就先被访问。本例优化器先通过idx_name找到张三对应的用户记录1 行再根据user_id去订单表找这个用户的所有订单最后用amount 100过滤。虽然执行计划写的是先访问u再访问o但你要清楚这只是逻辑上的执行顺序实际执行时 InnoDB 会按照驱动表和被驱动表的嵌套循环方式来执行。2.2 table 和 partitions这次访问的是哪张表table列告诉你当前这行执行计划访问的是哪张表。多表关联时这个字段帮助你把每一行输出和 SQL 里的表对应起来。partitions列是分区表相关的如果你的表没做分区它永远是NULL可以忽略。这里有个容易懵的地方table字段偶尔会显示成derived2或union1,2这样的形式。derived2表示这是 id2 的子查询派生出来的临时表union1,2表示这是 UNION 结果合并的临时表。看到这种值说明查询里有子查询或者 UNION优化器需要物化一个临时表来处理往往伴随着额外的内存和磁盘消耗需要引起重视。2.3 type访问类型执行计划里最该盯紧的一列type列是判断查询性能最直观的指标表示 MySQL 用什么方式在表中找到所需行。性能从好到差大致是system const eq_ref ref range index ALL我用一个表格把每种类型出现的前提、含义和典型场景列出来方便对照type含义触发条件示例性能评价system表只有一行系统表是 const 的特例系统表、且只有一条记录极好const用主键或唯一索引等值查询最多返回一行WHERE id 100id 是主键极好eq_ref被驱动表通过主键或唯一索引等值查找每次最多匹配一行多表关联中用主键关联被驱动表很好ref通过普通索引等值查询可能返回多行WHERE user_id 123user_id 有普通索引好range索引范围扫描WHERE id BETWEEN 100 AND 200、WHERE status IN (1,2,3)较好index遍历整棵索引树才能找到需要的数据SELECT COUNT(*) FROM t_order且只有主键索引时一般ALL全表扫描WHERE user_id 123user_id 无索引差看到这里你应该明白了目标至少要做到range最好能达到ref或const。如果一条查询的type是ALL而且rows预估很大那基本可以断定这条 SQL 需要优化。但要注意一个坑typeindex看起来比ALL好一级实际上也可能很慢。它虽然不用扫全表数据但要扫整棵索引树如果索引树很大、或者需要回表的行很多性能依然堪忧。比如EXPLAIN SELECT order_no FROM t_order WHERE status 1;如果status上没有索引这条语句能用的只有主键索引type会显示为index意味着 MySQL 决定遍历整个主键索引树来检查每一行。虽然它不会去读完整的行数据但扫描的索引条目依然是全表数量级照样慢。2.4 possible_keys 和 key优化器可能用和实际用的索引possible_keys列出查询中可能用到的索引key列出优化器实际选用的索引。这两个字段对比着看很有价值如果possible_keys有值但key是NULL说明存在可用索引但优化器基于成本评估后决定不使用。常见原因包括索引选择性太差比如一个 sex 字段区分度极低、优化器认为全表扫描成本更低、或者查询条件里对索引列做了函数操作导致索引失效。如果possible_keys和key都是NULL说明这条 SQL 压根没有索引可以用大概率全表扫描。比较理想的情况是key非空并且possible_keys中包含多个索引优化器从中选了一个最优的。这时你要确认它选的索引是否合理。我举个possible_keys有值但keyNULL的典型例子EXPLAIN SELECT * FROM t_order WHERE DATE(created_at) 2024-06-01;created_at上如果没有建索引这没啥好说的如果建了索引idx_created_at你会发现possible_keys里依然可能是NULL。原因是对索引列使用DATE()函数后索引字段本身被改变了优化器无法再走常规索引查找路径。这就是大家常说的索引列上使用函数会导致索引失效。那如果我把条件改成范围查询呢EXPLAIN SELECT * FROM t_order WHERE created_at 2024-06-01 AND created_at 2024-06-02;这次possible_keys里就能看到idx_created_atkey也会是它type变成range。同样是查某一天的数据写法不同执行计划天差地别。这种细节就是EXPLAIN实操的价值所在在真实执行计划面前你背过的不要在索引列上使用函数才真正有落地的感觉。2.5 key_len这个字段容易被忽略但含金量很高key_len表示索引中实际使用的字节数。很多人不关注这个列但它能帮你判断多列索引中到底有几个列真正被用上了。这里必须提到最左前缀原则MySQL 使用联合索引时会从最左边的列开始匹配直到遇到范围查询或其他无法继续匹配的条件为止。举个例子。假设我们在订单表上建一个联合索引ALTER TABLE t_order ADD INDEX idx_user_status (user_id, status);执行EXPLAIN SELECT * FROM t_order WHERE user_id 123 AND status 1;此时key_len的值会是user_id和status两个字段字节数之和。bigint占 8 字节tinyint占 1 字节加上变长字段长度等等最终key_len 8 1 1 10左右额外 1 字节是变长记录标志。看到key_len10说明两个索引列都生效了。但如果只执行EXPLAIN SELECT * FROM t_order WHERE user_id 123;key_len会变成 8 左右只用了user_id一个列因为where条件里没有status联合索引只能从最左列user_id开始匹配。再比如条件变成EXPLAIN SELECT * FROM t_order WHERE user_id 123 AND status 1;当第二个索引列是范围查询时key_len同样只显示user_id的长度status并没有被用于索引查找。原因是最左前缀原则遇到范围查询就中断了。所以通过key_len这个数字你能精确判断联合索引到底吃到了几个列而不是靠猜。再看一个进阶案例。假设有联合索引(a, b, c)如果查询条件是WHERE a 1 AND c 2由于中间跳过了bkey_len只会显示a的长度c用不上索引。这时候最有效的调整是把c提到索引前面或者把b也加入查询条件。这些都是EXPLAIN能直接告诉你的结论。2.6 ref索引列用什么东西来比较ref列显示的是索引列与什么进行比较。可能是const常数如WHERE user_id 123、某个表的字段名如WHERE t1.user_id t2.idref 会显示test.t2.id也可能是函数或表达式。这一列和key_len配合能让你更清楚地理解索引的匹配方式。如果看到ref是func意味着索引列被某个函数结果匹配通常也代表索引使用方式不理想需要检查查询条件里是否对索引列做了函数操作。2.7 rows 和 filtered优化器的心算草稿rows是优化器预估的需要扫描的行数filtered是经过where条件过滤后剩余行数占扫描行数的百分比。两者的乘积约等于最终返回的结果集大小。举个例子rows1000filtered20.00说明优化器预计扫描 1000 行后最终返回约 200 行。这个预估对判断查询成本非常关键rows越大扫描成本越高。但注意rows是基于统计信息的估算不一定是真实的行数。如果表的统计信息过期比如大量增删改后没跑ANALYZE TABLErows可能严重失真。这是EXPLAIN的一个局限后面我会专门讲怎么规避。另外还有个细节5.7 之后优化器引入了条件过滤Condition Filtering机制filtered列从这个版本开始才真正有意义。5.6 及之前版本里filtered列也存在但意义不同5.7 的估算逻辑更贴近真实情况。2.8 Extra藏着很多问题信号的附加信息列Extra列是EXPLAIN输出里信息密度最高、也最容易暴露问题的一列。常见值有Using where通过where条件过滤但并没有使用索引来定位数据。通常表明查询条件里用到的列没有被索引覆盖需要逐行判断。Using index覆盖索引扫描查询所需的列全部在索引中不需要回表。这是比较理想的状态。Using index condition索引条件下推MySQL 把部分where条件下推到索引层判断减少回表次数。MySQL 5.6 以后引入的优化机制通常说明相关列建了索引但索引覆盖不完全。Using filesort需要额外排序操作但注意这里的filesort并不一定会用磁盘文件也可能在内存中完成排序。无论如何看到它就说明排序无法利用索引顺序需要额外的排序成本。Using temporary需要创建临时表来处理查询常见于GROUP BY、DISTINCT、UNION等操作。大量数据时的临时表可能落到磁盘性能会显著下降。Using join buffer关联查询时被驱动表无法使用索引MySQL 用 join buffer 做缓存通常意味着关联字段没索引。Impossible WHEREwhere条件恒为假优化器直接返回空结果不会实际执行。这些信号中Using filesort和Using temporary最值得警惕。举个例子EXPLAIN SELECT user_id, status, COUNT(*) FROM t_order WHERE created_at 2024-06-01 GROUP BY user_id, status;如果created_at有索引type可能是range但Extra里大概率会出现Using temporary; Using filesort。为什么因为GROUP BY的字段顺序(user_id, status)和用于范围查询的索引idx_created_at不一致优化器没法利用索引顺序来完成分组只能先把结果放到临时表再做排序分组。看到这个组合你就应该考虑是不是需要调整索引设计或者改写 SQL 来避免临时表。3. 一次真实慢查询的排查全过程从发现问题到索引落地的完整链路前面把EXPLAIN的每一列过了一遍现在我把一次真实的慢查询排查全过程完整走一遍把前面那些零散知识点串起来。这个案例是我在自己测试库上还原的业务场景很简单订单列表页需要展示某个用户最近 30 天的订单按金额降序SQL 大致长这样SELECT id, order_no, amount, status, created_at FROM t_order WHERE user_id 12345 AND created_at 2024-05-01 ORDER BY amount DESC LIMIT 20;测试表结构和前面的一样数据量 20 万行。这条 SQL 的执行计划我直接贴出来id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra 1 | SIMPLE | t_order| ALL | idx_user_id | NULL | NULL | NULL | 198432| 5.00 | Using where; Using filesort看到这个输出我的第一反应是有idx_user_id可用但优化器没选。为什么因为它需要同时满足user_id等值、created_at范围和amount排序单个索引无法同时搞定所有条件优化器评估后可能认为直接全表扫描 过滤 排序的成本比先走idx_user_id再回表过滤排序更低。于是它选择了ALL然后Extra里冒出了Using filesort。针对这种情况常见的优化方向有两个方向一让WHERE条件的过滤尽量走索引。在user_id和created_at上建联合索引ALTER TABLE t_order ADD INDEX idx_user_created (user_id, created_at);再来一遍EXPLAINid | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra 1 | SIMPLE | t_order| range| idx_user_id,idx_user_created | idx_user_created | 16 | NULL | 21 | 100.00 | Using index condition; Using filesorttype从ALL变成了rangerows从 198432 降到了 21Extra里依然有Using filesort。为什么会这样因为created_at是范围条件走联合索引后ORDER BY amount依然无法利用索引顺序。排序操作虽然存在但需要排序的数据量从 20 万行骤降到 21 行内存排序完全扛得住所以实际执行速度会有质的飞跃。方向二如果想要彻底消除Using filesort就得在索引设计上做文章把ORDER BY的字段也纳入索引。比如建这样一个联合索引ALTER TABLE t_order ADD INDEX idx_user_created_amount (user_id, created_at, amount);注意amount是排序字段索引顺序是(user_id, created_at, amount)在user_id等值、created_at范围的条件组合下MySQL 能否利用amount的有序性来避免排序取决于created_at的范围类型。如果created_at是等值条件可以直接利用三列索引避免 filesort但如果created_at是范围条件优化器通常还是会选择 filesort因为范围内每条记录的amount顺序不保证。所以实际业务里我的经验是优先保证过滤条件的索引排序交给 filesort 反而更稳妥因为真正的问题通常是扫描行数太大而不是排序本身。用rows21的 filesort 换掉rows20万的 filesort性能提升已经是天壤之别。再回到这个案例本身。修改索引后这条 SQL 的执行时间从原始的 800ms 左右降到了 20ms 以内。这个提升不是靠背八股得到的而是靠EXPLAIN一步步观察、验证、调整索引设计得到的。这个过程值得每个开发自己动手跑一遍因为只有亲手调整过索引顺序、亲眼看到type和rows的变化你对索引的理解才会真正牢固。排查链路简单梳理一下拿到慢查询 SQL先跑EXPLAIN看type、key、rows、Extra四个核心字段。根据typeALL、keyNULL判断没有走索引或者索引失效。看possible_keys里有哪些可用的索引为什么优化器没选。根据where条件和order by设计合适的联合索引重点考虑等值条件放前面、范围条件和排序字段放后面的原则。调整后重新EXPLAIN对比type、rows、Extra的变化。最后用真实执行时间验证确保优化真的有效。这套链路看起来简单但每一步都需要对EXPLAIN输出有准确的理解。你第一次跑的时候可能会卡在优化器为什么不选这个索引这个问题上没关系多试几次把possible_keys里的索引逐个建一遍、删一遍观察执行计划的变化很快就能摸清优化器的脾气。4. 最常见的三类性能瓶颈以及它们在 EXPLAIN 里的长相实际操作中我发现慢 SQL 的问题大多可以归为三类索引没走对、排序和分组代价过大、关联查询的设计不合理。下面分别说说它们在EXPLAIN输出里长什么样以及通常怎么处理。4.1 索引没走对typeALL 且 rows 巨大这是最典型的一类慢查询特征就是typeALLkeyNULLrows很大Extra里经常带Using where。原因不外乎以下几种查询条件涉及的列没有索引。有索引但优化器觉得没必要用索引区分度太低。索引列被函数、隐式类型转换等影响了。使用了LIKE %keyword这种无法走索引的前缀模糊匹配。处理思路很直接给where条件里的列建合适的索引。但索引不是越多越好索引过多会增加写入成本和存储开销所以要根据实际高频查询来设计联合索引尽量一个索引覆盖多条查询路径。4.2 排序和分组代价过大Extra 里的 filesort 和 temporary排序和分组这两类操作在EXPLAIN里经常表现为ExtraUsing filesort或ExtraUsing temporary。它们不一定会让 SQL 立刻变慢但数据量一大代价就会呈指数级上升。拿分组举例EXPLAIN SELECT status, COUNT(*) FROM t_order GROUP BY status;如果status上没索引输出里会出现Using temporary因为 MySQL 需要建立临时表来统计分组结果。若数据量达到百万甚至千万级别临时表可能从内存挪到磁盘性能断崖式下跌。另一个常见场景是DISTINCT和ORDER BY组合EXPLAIN SELECT DISTINCT user_id FROM t_order ORDER BY created_at DESC;这条语句容易同时出现Using temporary和Using filesort。原因是DISTINCT需要去重ORDER BY需要排序而两个操作涉及的字段不同无法在一次索引扫描中同时完成。改进的方向有两个。一是把GROUP BY的字段顺序和联合索引的最左前缀对齐让索引天然有序省掉临时表和排序二是改写 SQL比如把DISTINCT改成GROUP BY或者把排序操作前置、缩小排序结果集。这里有个实操小技巧如果GROUP BY只是想去重而字段本身有索引尽量用SELECT DISTINCT而不是GROUP BY因为DISTINCT在部分场景下可以直接走索引去重省掉临时表。如果GROUP BY还需要聚合函数那就得让GROUP BY字段有索引支撑否则Using temporary很难避免。4.3 关联查询设计不合理驱动表选错或关联字段缺索引多表关联查询的慢通常和两个因素有关驱动表选得不好、被驱动表的关联字段没索引。EXPLAIN输出里多表关联会显示多行记录执行顺序id相同的从上到下就是驱动顺序。优化器会选择小表驱动大表的策略先访问小表拿到结果集再用这个结果集去大表匹配。如果驱动表选成了大表会导致被驱动表被反复扫描性能很差。举例说明。假设我们要查用户 ID 在某个范围内的订单信息EXPLAIN SELECT o.id, u.name FROM t_user u INNER JOIN t_order o ON o.user_id u.id WHERE u.id BETWEEN 1 AND 100;正确的执行计划应该是u作为驱动表通过主键范围扫描拿到 100 个用户然后去t_order通过idx_user_id查找订单。这类查询的关键在于被驱动表t_order.user_id必须有索引否则 MySQL 会对每一行用户记录去全表扫描订单表产生嵌套循环 全表扫描的灾难性计划。EXPLAIN输出里如果被驱动表的typeALL、ExtraUsing join buffer基本就是关联字段没索引的典型信号。解决方式是给被驱动表的关联字段加索引或者反过来调整关联顺序使用STRAIGHT_JOIN可以让优化器按指定顺序执行但不推荐作为常态手段。另外多表关联还有一个容易被忽略的问题关联字段的字符集和排序规则collation必须一致。如果两表的关联字段一个是utf8mb4另一个是utf8或者排序规则不同MySQL 无法直接比较索引可能失效。这与EXPLAIN输出的关系不大但排查慢查询时碰到明明有索引却不用的情况值得检查一下这个点。4.4 一个多表关联的完整排查案例我把前面的知识点串起来给一个真实的多表关联慢查询案例。表结构是t_order和t_user之前已经建好了。现在执行SELECT u.name, o.order_no, o.amount FROM t_user u INNER JOIN t_order o ON o.user_id u.id WHERE u.phone 13800001111;这条查询的本意是根据手机号找到用户再查该用户的订单。假设t_user.phone和t_order.user_id上都没有索引EXPLAIN输出会非常难看id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra 1 | SIMPLE | u | ALL | NULL | NULL | NULL | NULL | 50000 | 10.00 | Using where 1 | SIMPLE | o | ALL | NULL | NULL | NULL | NULL | 198432| 10.00 | Using where; Using join buffer两张表都是ALL相当于先全表扫描t_user5 万行再对每一行去全表扫描t_order20 万行总扫描量是 5 万乘以 20 万堪称灾难。修复方案分两步第一步给t_user.phone加普通索引。这样驱动表的扫描量会从 5 万降到个位数。第二步给t_order.user_id加普通索引如果还没有。这样被驱动表能通过ref方式快速查找。改完后的EXPLAINid | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra 1 | SIMPLE | u | ref | idx_phone | idx_phone | 83 | const | 1 | 100.00 | NULL 1 | SIMPLE | o | ref | idx_user_id | idx_user_id | 8 | test.u.id | 42 | 100.00 | NULL执行计划从两个 ALL变成了两个 ref扫描行数从天文数字变成了几十行。这个案例我强烈建议你自己复现一遍因为多表关联的执行计划变化比单表更直观也更容易理解驱动表和被驱动表的概念。5. EXPLAIN 不只是看个大概JSON 格式、实际执行与预估的差距基础用法掌握之后再聊几个容易被忽略但非常重要的进阶细节。EXPLAIN不只是输出一张表格它还有更详细的信息模式并且它的预估和实际执行存在差距这些你都应该知道。5.1 EXPLAIN FORMATJSON看到优化器的成本账本MySQL 从 5.6 开始支持EXPLAIN FORMATJSON输出会包含更详细的信息尤其是成本估算。比如EXPLAIN FORMATJSON SELECT * FROM t_order WHERE user_id 12345;输出里有query_cost、read_cost等字段这是优化器用来做最终决策的成本数据。read_cost是读取成本eval_cost是计算过滤成本prefix_cost是前缀成本最后一个步骤的prefix_cost通常就是整个查询的总成本。这些数字有什么用它帮你量化优化器为什么选了 A 方案而不是 B 方案。比如你有两个索引可用优化器选了其中一个你可以用 JSON 输出里的cost_info对比如果强制用另一个索引的成本从而判断优化器的决策是否合理。举个实际例子EXPLAIN FORMATJSON SELECT * FROM t_order WHERE user_id 12345 AND amount 100;如果user_id和amount上分别有索引优化器需要决定用哪个索引作为主要访问路径。因为WHERE user_id 12345 AND amount 100是一个等值加范围的组合优化器可以选择先用idx_user_id过滤出 42 行再在内存中过滤amount也可以选择先用idx_amount范围扫描再过滤user_id。从 JSON 输出里你能看到两种方案各自的cost_info从而确认优化器的选择是否合理。JSON 格式还有个好处能看到attached_condition它会显示优化器下推到索引层的具体条件。比如Using index condition时你可以看到哪些条件下推了、哪些没有这对理解索引条件下推机制非常有帮助。5.2 预估和实际执行差距太大怎么办ANALYZE TABLE 和 EXPLAIN ANALYZEEXPLAIN输出的rows是估算值基于表的统计信息。如果统计信息过期预估可能严重偏离实际。这种情况下第一步是更新统计信息ANALYZE TABLE t_order;这个命令会重新计算索引的基数cardinality和表的大致行数让优化器的成本估算更准确。大多数情况下几条ANALYZE TABLE就能让执行计划恢复正常。如果更新统计信息后依然有问题可以用 MySQL 8.0 提供的EXPLAIN ANALYZE看实际执行情况。它不只是预估而是真正执行查询并输出每个步骤实际扫描的行数和耗时EXPLAIN ANALYZE SELECT * FROM t_order WHERE user_id 12345;输出类似- Index lookup on t_order using idx_user_id (user_id12345) (cost4.67 rows42) (actual time0.025..0.045 rows42 loops1)注意actual time和rows42这是真实执行数据不是预估。如果EXPLAIN预估的rows和EXPLAIN ANALYZE的真实rows相差很大说明统计信息不准跑一遍ANALYZE TABLE通常能解决。这里要提醒EXPLAIN ANALYZE会真实执行 SQL如果是INSERT、UPDATE、DELETE或者非常慢的查询要谨慎使用避免在生产环境造成额外压力。可以用EXPLAIN ANALYZE FORMATJSON来减少输出量。5.3 FORCE INDEX 和 IGNORE INDEX必要时引导优化器有些极端情况下优化器选错了索引而你确认另一个索引明显更优可以通过FORCE INDEX或USE INDEX来引导它SELECT * FROM t_order FORCE INDEX (idx_user_created) WHERE user_id 12345 AND created_at 2024-05-01;但这不是一个值得长期依赖的方案。索引名写死在 SQL 里一旦索引名变更或者数据分布变化强制索引可能适得其反。更好的做法是调整索引设计本身让优化器自然选择最佳路径。我见过不少团队遇到优化器选错索引就上FORCE INDEX这是治标不治本。大多数情况下统计信息过时或者索引选择性变化才是根因先把统计信息更新好再检查索引设计是否合理最后才考虑强制指定。5.4 慢查询日志是 EXPLAIN 的最佳入口EXPLAIN不是凭空跑的它的输入来源通常是慢查询日志。生产环境应该开启慢查询日志并设置合理的阈值SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1; SET GLOBAL log_queries_not_using_indexes ON;long_query_time设为 1 秒超过 1 秒的查询都会被记录。log_queries_not_using_indexes可以记录所有没走索引的查询帮你发现隐藏的慢查询隐患。拿到慢查询 SQL 后第一时间EXPLAIN这是排查的黄金流程。6. 把 EXPLAIN 变成日常习惯开发自测阶段的几个实操建议最后这部分我想聊聊怎么把EXPLAIN从排查工具升级为日常开发习惯。毕竟在慢查询出现之后再优化属于被动救火在开发阶段就通过EXPLAIN验证 SQL 质量才是治本之道。6.1 SQL 提交前先跑一遍 EXPLAIN我对团队的基本要求是任何涉及查询的 SQL在提交代码前至少跑一次EXPLAIN并且确认以下几个指标达到合格线type不是ALL全表扫描最好是range或以上。key不是NULL除非表很小比如几千行以内。rows与实际数据规模相符如果实际 20 万行而预估 20 万行说明确实在扫全表。Extra里没有Using filesort和Using temporary或者有但数据量很小可以接受。如果上面任意一条不达标就需要慎重考虑是否合并。这个习惯的价值在于把问题在开发阶段截住而不是等线上告警。很多同学觉得这条 SQL 本地数据量小跑挺快上线再说结果线上数据量一上来就出问题。EXPLAIN能在数据量小的环境里就暴露执行计划的隐患因为执行计划是由表结构和统计数据决定的和具体数据量关系不大。6.2 索引设计不是拍脑袋而是基于 EXPLAIN 迭代我见过最常见的索引设计方式是看到哪个字段在 where 里就给它加个索引。这种办法有一定效果但效率不高而且容易造出一堆冗余索引。更好的思路是拿到业务高频 SQL先不加索引跑EXPLAIN记录问题再根据where、group by、order by的字段组合设计联合索引每加一个索引就再跑EXPLAIN验证效果。整个过程是SQL 驱动索引而不是字段驱动索引。6.3 定期检查线上慢查询日志即使开发阶段把关严格线上环境的查询模式和数据分布还是会出现意外。建议定期比如每周拉一次慢查询日志把 Top N 慢查询的 SQL 拿出来EXPLAIN分析。这个过程积累了足够多的案例后你会慢慢对哪些 SQL 容易慢形成直觉以后写 SQL 时自然会更谨慎。6.4 关注 MySQL 8.0 的优化器新特性MySQL 8.0 引入了descending index支持倒序索引对ORDER BY xxx DESC的查询可以从根本上避免 filesort。如果你的业务有很多倒序排序的查询可以考虑把索引改成ALTER TABLE t_order ADD INDEX idx_user_created_desc (user_id, created_at DESC);不过归根结底工具和新功能只是手段核心还是你能不能熟练读懂执行计划、理解优化器的判断逻辑。EXPLAIN就是你和优化器之间的翻译官把它用熟了慢 SQL 优化就不再是玄学。我在实际工作中最大的体会是EXPLAIN这个东西看十篇教程不如自己跑一个下午。找个测试库造点数据把各种 SQL 都跑一遍EXPLAIN观察索引对执行计划的影响遇到不理解的就查文档、查资料。等你能对着输出准确说出这条 SQL 慢在哪个环节、应该加什么索引、加了之后会变成什么样那你对 MySQL 查询优化的理解基本就到下一个层次了。