说实话很多朋友用 MySQL 的 EXPLAIN习惯性地看一眼有没有走索引key 字段里有个名字就长舒一口气key 是 NULL就垂头丧气觉得索引肯定失效了。但我在一线排查慢查询这么久最想说的一句话就是别只盯着 key。EXPLAIN 输出的是一整张执行计划表key 只是“实际用了哪个索引”的结论真正能让你看懂“索引为什么失效”的往往是 type、key_len、rows、Extra 这些列之间的组合关系。这篇文章不会只讲概念我会把 EXPLAIN 每一列的含义、索引失效的典型场景、以及一次真实的线上排查过程完整拆给你看。不管你是刚接触 MySQL 的新手还是已经写过多年 SQL 的开发希望读完之后你能养成“拿到 EXPLAIN 先整体看、再逐列分析”的习惯。1. 执行计划里的门道为什么我说“别只盯着 key”1.1 一张执行计划表到底有哪些列我见过太多人只会在终端里敲一句EXPLAIN SELECT ...然后对着输出里的几个字段发呆。其实 MySQL 8.0 默认返回的列有 12 个左右不同版本略有差异它们的完整含义可以先用一张表概览字段核心含义判断重点idSELECT 标识符数字越大越先执行判断子查询、UNION 的顺序select_type查询类型比如 SIMPLE、SUBQUERY、DERIVED判断是否存在嵌套查询table当前步骤访问的表或派生表结合 id 看数据来源partitions命中的分区没做分区就是 NULLtype访问类型从 system 到 ALL 的“鄙视链”最该先看的性能指标possible_keys优化器认为可能用到的候选索引有候选但没用就是线索key实际选择的索引别只盯这里key_len实际使用的索引字节数用来判断联合索引用到第几列ref与索引比较的列或常量配合 key 看连接方式rows优化器估算的需要扫描的行数估算值不是实际值filtered经过 WHERE 条件过滤后剩余比例和 rows 相乘才是最终行数Extra额外的执行信息Using filesort / Using temporary 等隐藏风险看到这你不会觉得懵吧其实 EXPLAIN 就像一个“体检报告单”单项指标异常不能直接下结论你要把这几项合起来看。比如typeALL加上keyNULL和typeALL但keyidx_xxx两种情况背后的原因完全不同。1.2 综合解读不要拿单个字段“定罪”我经常在代码 review 里看到有人这样下结论这条 SQL 没走索引因为 EXPLAIN 里key是空的。听着没问题但为什么 key 是空的是没建索引还是建了索引但被函数包住了还是优化器认为全表扫比走索引更快这些“为什么”恰恰才是真正要解决的问题。反过来说也常有朋友指着keyidx_order_status说“这条 SQL 已经走了索引不用优化了”。结果我一看type 是ALLExtra 里还带着Using filesort。这种情况我可以负责任地告诉你优化器有时会把某个索引当作“覆盖统计”的捷径或者因为其他原因选择全表扫但还在 key 里保留索引名所以只看 key 数字非但不能说明问题还会掩盖问题。正确的打开方式是拿 EXPLAIN 输出从 type 开始看然后是 key_len、rows、Extra最后回头对照 possible_keys 和 key。这一套组合拳打下来索引为什么失效基本就藏在里面了。2. 逐列拆解从 id 到 Extra 看索引选路2.1 id 与 select_type查询结构怎么写计划就怎么长先看 id。它代表 SELECT 的标识符在一条复杂 SQL 里可能出现多个 id。如果两个行的 id 相同说明这两个表是同一次查询里的关联表执行顺序从上往下如果 id 不同数字越大执行越早。常见idNULL的情况大多出现在UNION的结果合并那一行是“合并临时表”操作。举个例子EXPLAIN SELECT ... FROM a WHERE id IN (SELECT id FROM b)这种子查询通常你会看到两行一行是 b 表的查询id2一行是 a 表的查询id1。从 select_type 也能看出来b 是SUBQUERYa 是PRIMARY。这说明 MySQL 会先执行子查询再去执行主查询。我的建议是如果 EXPLAIN 结果超过了三行你就得留心SQL 嵌套层级越深执行计划越复杂潜在问题也越多。很多慢查询就是由于三层甚至四层子查询嵌套导致每一层都产生临时表索引根本无从发挥。2.2 type 列访问类型的“鄙视链”type 列是整个执行计划里最值得盯的位置它描述的是“访问表的方式”从好到坏大致是systemconsteq_refrefrangeindexALLsystem表只有一行一般是系统表的极限情况基本遇不到。const根据主键或者唯一索引做等值查询最多返回一行。比如WHERE id 1。eq_ref被驱动表通过主键或唯一索引进行连接匹配每次只读一行。典型场景是多表 JOIN 时驱动表的某行去关联被驱动表的主键。ref通过非唯一索引进行等值匹配可能返回多行。比如普通字段user_code X001且该字段有索引。range索引范围扫描常见于BETWEEN、、、IN、LIKE abc%属于“能用索引但不得不扫一段”的类型。index全索引扫描把整棵 B 树从头扫到尾几乎等同于全表扫只不过扫描的是索引文件。ALL全表扫描从头扫到尾最差的情况。很多索引“看似失效”表现在 type 列上就是明明建过索引但 type 从ref降到了ALL或者从range变成了index。判断一条 SQL 是否健康我一般先看 type 是否到了ALL或index如果到这两个值优先把 SQL 或索引设计调优。2.3 key_len 与 possible_keys/key判断联合索引用了多少个列怎么判断一个联合索引到底“用了多少列”不是看 key 里索引名后面有没有写着列名而是看key_len。key_len是实际使用索引的字节数。因为 MySQL 索引的本质是字符串/数值的排序结构联合索引 (a, b, c) 只有在一列等值匹配后b 列才可能继续参与比较c 列同理。所以key_len越长说明利用的索引列越多。这里有个常用计算规则INT 类型4 字节。BIGINT8 字节。VARCHAR(n)字符集字节数 * n 变长标记 2 字节。如果字段允许 NULL还要再加 1 字节。UTF8MB4 字符集下VARCHAR(50) 可空字段的 key_len 50 * 4 2 1 203。日期时间类型DATETIME 通常 5 字节MySQL 5.6TIMESTAMP 通常 4 字节。所以当我看到联合索引(user_id, order_status, create_time)而 EXPLAIN 里 key_len 4我就知道优化器只用了user_id这一列如果 key_len 10说明用了两列比如 user_id4字节 order_status假设是 tinyint 或 int。别小看这个细节很多时候 SQL 能够命中索引但 key_len 比别人预期短说明索引利用不彻底这也是一种“失效”形态。再配合 possible_keys 和 key 看possible_keys 里明明有索引但 key 是 NULL这说明优化器觉得候选索引用不上或者没必要用需要回查 SQL 条件possible_keys 为空但 key 有值通常出现在覆盖索引扫描中MySQL 直接扫索引树就能满足查询。2.4 ref、rows、filtered估算出来的代价明细ref列显示的是与索引进行等值匹配的对象。如果是常量就显示const如果是另一个表的某列会显示类似db.t2.user_id。通过 ref 能看出表的关联是否高效。比如被驱动表的 ref 是一个常量说明是很好的等值匹配。真正值得琢磨的是rows和filtered。rows是优化器估算的“需要读取的行数”不是实际值filtered表示经过 WHERE 条件后剩余记录的百分比用rows * filtered / 100可以大致估算最终返回行数。这个数字直接影响优化器的成本决策——如果估算太大它就会放弃索引而选择全表扫。我曾经查过一条关联查询驱动表 rows12000被驱动表 rows8900000看起来执行计划已经很差。但用户因为只看了 key 列非空一直以为没问题。所以记住rows 是从亿级缩减到百级还是从万级到千级这中间的差距才是调优空间。2.5 Extra 列用这几个关键字识别索引失效的幕后问题Extra 列特别容易暴露索引失效的“暗病”常见值我挑了这几个Using index使用了覆盖索引不需要回表这是很好的信号。Using where数据从存储引擎取回来后还要在 MySQL 服务层再过滤一次。如果一张表只有Using where且 typeALL说明索引基本没生效大量数据被取出来后再丢掉了。Using filesort不是不允许而是说明排序没走索引MySQL 需要额外执行排序操作。如果 ORDER BY 字段不在当前索引的有序范围内就会出现这个提示。这个常常比“没走索引”更隐蔽因为明明 key 有值可排序依然慢。Using temporary用了临时表通常出现在 GROUP BY 或 DISTINCT 与索引列顺序不一致时。Using index condition索引下推说明存储引擎层先过滤了一部分索引项是 MySQL 5.6 的优化可接受。Impossible WHERE查询条件本身矛盾比如WHERE id1 AND id2不会返回数据。这些提示单看一个可能影响不大但如果Using filesort和Using temporary同时出现恭喜你这条 SQL 大概率需要进行索引重构或者改写查询逻辑。3. 索引失效的六大典型场景我帮你们都复现了一遍3.1 最左前缀法则跳过第一列说啥都不好使联合索引的排序规则是先按第一列排第一列相同再按第二列排以此类推。所以查询条件必须从最左列开始。比如建了联合索引(user_id, order_status, create_time)如果查询是WHERE order_status 1 AND create_time 2024-01-01由于条件里没有第一列 user_idMySQL 无法利用联合索引的有序性去快速定位。EXPLAIN 大概率出现possible_keys里有这个索引但key为 NULLtypeALL。这种失效非常典型——索引明明在用不上看着最心疼。解决办法要么把查询条件补上 user_id如果业务本来就限定用户要么为高频组合(order_status, create_time)单独建索引。在实际系统里有时你的 SQL 只是少了前端传参的一个过滤条件这会导致整个索引失效需要格外注意代码里“参数拼接是否完整”的问题。3.2 你在索引列上动手脚优化器只能放手我经常看到有人对索引列做函数处理比如WHERE DATE(create_time) 2024-06-01 WHERE YEAR(create_time) 2024 WHERE id 1 100第一个例子的问题是你拿函数包裹了 create_time索引存的是原始日期时间但你需要的是“函数处理后的值”两者不一致MySQL 只能把全表数据都取出来算一遍。正确做法是把函数运算放到“条件侧”比如WHERE create_time 2024-06-01 AND create_time 2024-06-02这样 create_time 列本身没变索引就有机会命中type 会变成range。如果你的数据库版本支持 MySQL 5.7 的生成列Generated Columns也可以把 DATE(create_time) 建一个生成列然后对生成列建索引。3.3 隐式类型转换varchar 字段和数字的“致命诱惑”字段类型是 VARCHAR查询条件却写成了数字比如用户编号user_no是 varchar(20)有人写WHERE user_no 123456MySQL 会做隐式类型转换把字符串列转成数字去比较。这个过程相当于在索引列上加了CAST(user_no AS SIGNED)索引自然失效。你执行 EXPLAIN 时很可能看到typeALL、keyNULL或者 rows 暴涨。这个坑在手机号、证件号、订单号这类“看起来像数字”的字符串字段上非常常见。解决办法就是养成条件类型和字段类型完全对齐的习惯常量写成字符串123456或者使用 bind parameter 时指定对应的 string 类型。3.4 LIKE 以通配符开头索引直接断臂WHERE name LIKE %张这种后置模糊查询为什么无法走索引因为索引 B 树是按完整字符串的前缀排序的查询字符串以%开头时优化器根本不知道从树的哪个节点开始遍历只能全表扫。但WHERE name LIKE 张%就可以走 range 扫描因为“张”字已知能定位索引树的起点然后向后扫到“张”开头的边界。所以业务上如果一定要做含后缀或中间位置的模糊匹配通常要考虑全文索引或外部搜索方案而不是硬在 MySQL 里用 LIKE。不过这里也有个反直觉的点如果表很小优化器也可能选择全表扫而不是走索引。毕竟全表扫成本可能更低。3.5 OR 条件里有非索引列MySQL 选择全表突围一条 SQL 是这样WHERE a 1 OR b 2假设 a 列有索引b 列没有索引。你是希望 MySQL 先走 a 的索引再对 b 做全表扫实际上 MySQL 很难把两部分结果优雅地合并经常是直接全表扫因为 OR 只要有一部分条件无法用索引整体走索引的代价可能比全表扫还高。我处理过最典型的案例如下订单表查询order_status 1 OR pay_type 2order_status 有索引pay_type 没有结果全表扫。后来改成“分开查询再 UNION ALL”SELECT * FROM orders WHERE order_status 1 UNION ALL SELECT * FROM orders WHERE order_status 1 AND pay_type 2这样让第一个查询走索引第二个查询用另一条索引或接受小范围扫描性能立刻不一样。当然最理想的是给 pay_type 也加上合适的索引。3.6 优化器嫌索引太“贵”全表扫描有时是“理性选择”这条最容易让人误解。有时你的 SQL 写得很标准索引条件也符合最左前缀函数也没用类型也一致但 EXPLAIN 出来还是typeALL。别急着骂 MySQL它其实是在“算账”。如果一条 SQL 要读取表中 20% 以上的数据优化器通过索引逐行回表反而比直接全表顺序扫描更慢因为随机 IO 比顺序 IO 贵得多。这时候就算有索引也会被放弃。典型比如WHERE is_deleted 0而is_deleted0占了表里 99% 的行就算这个字段有索引MySQL 也会觉得我干脆把全表扫一遍算了反正结果都差不多还省得每行都跳来跳去回表。想知道优化器为什么做这个决定可以用EXPLAIN FORMATJSON看成本估算或者用后面的 optimizer trace 来深挖。遇到这种情况不要盲目 FORCE INDEX而是要想清楚业务是否真的需要一次性取出这么多行或者换一种查询粒度。4. 用 EXPLAIN ANALYZE 和 TRACE 把执行计划看到骨子里4.1 EXPLAIN ANALYZE从估算到实跑MySQL 8.0.18 以后提供了EXPLAIN ANALYZE。它不是估算而是真正执行这条 SQL然后返回每一步的实际耗时、实际行数。用法是EXPLAIN ANALYZE SELECT * FROM orders WHERE order_status 1 AND create_time 2024-01-01;输出结果会比普通 EXPLAIN 多很多信息比如每步的行数、实际执行时间、循环次数。这解决了“优化器估算不准”的问题。要注意的是它会真实执行查询所以 DML / 大查询要谨慎使用别在生产大表上直接跑不然可能造成额外压力。4.2 OPTIMIZER_TRACE看优化器怎么“内心纠结”MySQL 的 optimizer trace 可以记录优化器在整个决策过程中考虑了哪些索引、算出了什么成本。打开方式SET optimizer_traceenabledon; SELECT ... 你的慢SQL; SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;你会发现里面记录了优化器对每个可用索引的成本估算、扫描行数以及最终选择的原因。我遇到过一条 SQL明明逻辑上走索引更好但 optimizer trace 显示 it 选择了另一个索引是因为排序字段正好在那个索引上可以省掉 filesort。看到这些细节你才能给出合理优化建议而不是盲目加索引。4.3 统计信息不准导致的“误判”以及怎么刷新有时候索引本身没问题SQL 也没问题但优化器手里拿的“统计信息”太旧了。MySQL 会定期采样更新索引统计信息但不一定实时。如果一张表经历过大批量增删统计信息滞后就会导致 rows 估算偏差巨大优化器做出错误选择。这种情况下可以先执行ANALYZE TABLE 表名;刷新统计信息然后在跑 EXPLAIN 对比。很多“莫名其妙”的索引失效其实就是统计信息过期惹的祸。但这个操作会短暂锁表吗ANALYZE TABLE在 InnoDB 里整体是很轻量的但仍建议在低峰期执行。5. 实战复盘一次线上慢查询的完整排查记录5.1 现象接口偶尔超时慢查询里发现大表全扫某跨平台系统的订单查询接口在业务高峰期会出现偶发超时慢查询日志里频繁抓到了这样一条 SQLSELECT o.order_no, o.amount, u.nickname, p.product_name FROM orders o JOIN users u ON o.user_id u.id JOIN products p ON o.product_id p.id WHERE o.order_status 1 AND o.create_time 2024-03-01 ORDER BY o.create_time DESC LIMIT 20;orders 表当时有 900 万行左右order_status和create_time上已经有一个联合索引但线上响应始终不稳定。5.2 定位Explain 给出了关键证据我先在测试库跑了EXPLAIN结果大概如下idselect_typetabletypepossible_keyskeykey_lenrowsExtra1SIMPLEoALLidx_user_status_time, idx_status_timeNULLNULL1700000Using where; Using filesort1SIMPLEueq_refPRIMARYPRIMARY41NULL1SIMPLEpeq_refPRIMARYPRIMARY41NULL注意看 orders 表possible_keys 明明有idx_status_time但 key 却是 NULLtype 是 ALL。这说明优化器在考虑索引时算出来走索引的成本比全表扫还高所以弃用了。而且 Extra 里带着Using filesortORDER BY create_time 也没能通过索引排序。查看建表语句后我才发现索引idx_status_time建于 (order_status, create_time)看起来完全符合这条 SQL 的条件。为什么不走索引继续深挖发现线上这个索引的区分度已经比较差了。order_status1的数据占了订单表 95% 以上的行。优化器算了一笔账就算我用(order_status, create_time)索引定位到order_status1的数据这个结果集基本等于全表订单然后还要拿着主键一个个回表查询完整行最后排序。这个代价比直接全表扫 filesort 不一定便宜。加上create_time 2024-03-01这个范围条件会进一步放大扫描区间于是优化器直接选择了 ALL。5.3 修复调整索引 验证执行计划这样的场景怎么改最直接的做法是改变查询形态让索引减少回表压力。我当时的处理思路是拆成两步第一步把明细查询改成覆盖索引查询用索引把符合条件的 ID 先取出来SELECT o.id FROM orders o WHERE (order_status 1 AND create_time 2024-03-01) ORDER BY create_time DESC LIMIT 20;这个查询只查 id 和两个条件字段可以完全走(order_status, create_time, id)或者已有索引的覆盖形态避免回表。第二步再用JOIN或者IN去关联完整信息SELECT o.order_no, o.amount, u.nickname, p.product_name FROM ( SELECT id FROM orders WHERE order_status 1 AND create_time 2024-03-01 ORDER BY create_time DESC LIMIT 20 ) AS tmp JOIN orders o ON o.id tmp.id JOIN users u ON o.user_id u.id JOIN products p ON o.product_id p.id ORDER BY o.create_time DESC;这样一来最核心的订单查询范围被压缩到 20 条再回表查明细IO 开销大大降低。同时在原表上新建了一个(order_status, create_time, id)的覆盖索引让第一步能够完全走 index / range 扫描。修完后再次 EXPLAINorders 表的 type 变成了rangekey 是新的覆盖索引rows 从 170 万降到了 7.1 万Extra 里也没有Using filesort只看到Using where; Using index。线上实测响应时间从平均 3.2 秒降到 90 毫秒以内。5.4 复查压测结果和进一步优化空间压测后我又看了一条有意思的执行计划即使业务量高峰期这个覆盖索引方案的 rows 虽然还是几万但由于没有回表实际 IO 消耗很低。如果再进一步可以考虑把order_status1这种低频且庞杂的状态单独拿出来做缓存或者分表但这属于业务设计层面的优化不是 SQL 层面能解决的了。这个案例给我的启发是当优化器因为“索引选择性太差”而放弃走索引时你别总想着去和优化器对着干。让索引覆盖的列尽可能精简让查询先拿到“最小的主键集合”再回表通常比 FORCE INDEX 更巧妙也更稳定。6. 养成这几个习惯慢慢就成“执行计划人形翻译机”老话讲“熟能生巧”看 EXPLAIN 也一样。这里分享几个我踩过不少次坑之后形成的习惯希望你能直接用上。第一每一条新上线的核心 SQL都必须带上 EXPLAIN 走查一遍。不只是看 key 有没有值而是把 type 和 rows 作为一个评审门槛。我一般要求 type 不能低于 range如果 ref 能解决不要 range当然也得结合数据量不能死板。第二拿到 EXPLAIN 后先看 Extra 里的 Using filesort / Using temporary。如果它俩出现即使 key 有值我也会把这条 SQL 视为“待优化”。因为这通常说明 ORDER BY 或 GROUP BY 和索引顺序产生冲突我们的索引设计还不够贴切。第三建立自己的索引失效 checklist。把最左前缀、函数运算、隐式类型转换、LIKE 通配符、OR 条件、统计信息过期这六类问题贴在工位旁每次排查慢查询时一项项排除基本都能快速定位。第四善用 EXPLAIN FORMATJSON。有些平面上看不懂的信息比如成本估算在 JSON 里一目了然。结合 optimizer trace你仿佛能看到优化器“内心的小算盘”。最后再补一句MySQL 的执行计划优化是一个“成本决策”过程不是“是否用索引”的二元判断。真正的高手不是看到 key 非空就安心也不是看到 ALL 就慌乱而是能看懂优化器背后的那笔账然后通过 SQL 和索引设计让这笔账朝有利于性能的方向走。希望这篇文章能帮你也拥有这种“翻译执行计划”的能力。