别只盯着EXPLAIN的key：索引失效的真正原因与排查方法

排查慢SQL的时候我见过太多人盯着 EXPLAIN 结果里的 key 字段看一眼发现不是 NULL就长舒一口气“索引走了稳了。” 结果数据量一上来接口照样超时数据库 CPU 照样被打满。说实话只盯着 key 判断索引有没有生效是新手最容易踩的坑。MySQL 的 EXPLAIN 执行计划是一张能直接告诉你 SQL 到底怎么跑的表但它不是只靠一列就能读懂的。这篇文章我想带你系统地过一遍 EXPLAIN 里的关键列重点讲清楚一个核心问题索引为什么失效以及我们到底该怎么从执行计划里找到真实原因。这篇文章适合谁看刚接触索引优化的开发同学被慢查询折磨过几次的后端工程师以及想理清执行计划各个字段含义的人。我会结合具体的 SQL 现场和真实排查思路把那些文档里写得不痛不痒、实际却特别要命的细节都讲透。1. 先搞清楚 EXPLAIN 到底在“解释”什么1.1 为什么说“别只盯着 key”我刚开始用 EXPLAIN 的时候和很多人一样只关心 key 这一列。后来遇到一个典型的线上问题一个订单查询接口where 条件里有状态字段也建了联合索引EXPLAIN 输出里 key 确实显示了那个索引但查询依然慢得离谱。后来仔细看才发现type 显示的是 index不是 ref也不是 range。这是什么意思typeindex 表示这个查询扫描了整个索引树而不是通过索引精确定位到某几行。相当于你有一本字典不去按拼音查目录而是把整本字典从头到尾读了一遍虽然碰巧读的是“目录页”而不是“正文页”但代价依然很大。问题就出在 where 条件里用了联合索引的非最左列优化器只能选择全索引扫描来“硬扛”。如果我当时只看 key这问题永远排查不出来。所以“别只盯着 key”不是一句口号而是执行计划分析的第一步key 只是冰山一角type、key_len、rows、filtered、Extra 组合起来才能还原一条 SQL 的真实执行路径。1.2 EXPLAIN 输出字段速查EXPLAIN 的结果有很多列每一列都有自己的诊断价值。我平时最关注下面几张表格里的字段。字段含义判断要点id查询中每个 SELECT 子句的序号多表连接时id 相同从上往下执行子查询时 id 递增越大的越先执行select_typeSELECT 的类型SIMPLE 是简单查询SUBQUERY、DERIVED、UNION 等需要留意table当前行访问的是哪张表多表查询时看访问顺序partitions命中的分区分区表时需要关注type访问类型核心中的核心后面详细讲possible_keys可能用到的索引只是“可能”不代表一定用key实际选用的索引只看这一列最容易误判key_len实际使用的索引长度判断联合索引用到了几列很有用ref与索引比较的列或常量显示 const 表示等值常量匹配rows预估扫描行数估算值不是实际值filtered满足条件的行数百分比5.7 才有配合 rows 估算最终返回行数Extra额外的执行信息这里藏着 Using where、Using index、Using filesort 等关键线索不要一上来就背字段而是先建立一个大印象type 告诉你访问方式rows 告诉你预估代价Extra 告诉你有没有额外的排序、临时表、回表操作。三个配合才能判断一个索引到底用得好不好。2. type 列判断索引是否真正生效的关键2.1 type 取值从好到差怎么排type 是执行计划里最有信息量的一列它直接反映 MySQL 访问表数据的方式。按性能从好到差常见值如下type 值含义典型场景system表只有一行系统表的特殊场景业务基本见不到const主键或唯一索引等值查询最多返回一行where 主键 1eq_ref被驱动表通过唯一索引或主键等值匹配多表 join 中驱动表每行对应被驱动表一行ref普通索引等值查询返回多行where user_id 100user_id 有普通索引range索引范围扫描between、、、like abc%index扫描整个索引树联合索引非最左前缀查找ALL全表扫描基本等于噩梦日常优化目标至少要让 SQL 达到 range 级别最好能到 ref。如果看到 index 或 ALL就要警惕哪怕 key 字段有值。我见过不少 casekey 写着 idx_xxx慢查询日志里也一样type 却是 ALL说明索引根本没被利用起来可能是函数计算、类型隐式转换、统计信息偏差等问题。2.2 key_len 与 rows看懂索引到底“用了几分力”key_len 是判断联合索引使用程度的好帮手。举个例子你建了一个联合索引 idx_user_status(user_id, status)其中 user_id 是 BIGINT8 字节status 是 TINYINT1 字节两个字段都 NOT NULL。那么只用到 user_id 一列时key_len 大约是 8。两列都用上时key_len 大约是 9。也就是说通过 key_len 的变化你能精准推断出优化器到底用了联合索引的哪几个字段。如果一个联合索引有三四列但 EXPLAIN 显示 key_len 只覆盖了第一列那就得想想 where 条件里是不是没带全。rows 是优化器估算的扫描行数filtered 表示根据 where 条件过滤后剩余的比例。两者配合可以粗估真实返回行数rows × filtered / 100。如果 rows 很大filtered 又很低说明查询扫描了大量行才筛出极少结果这种情况即使 key 有值效率也好不到哪去。这一点在慢查询优化里特别常见。3. 索引失效的常见场景与现场还原3.1 对索引列做函数运算优化器直接放弃索引最典型的失效场景就是对索引列使用函数。比如一条统计语句SELECT * FROM order_info WHERE DATE(create_time) 2024-01-15;create_time 明明建了索引但执行计划通常会显示字段值typeALLkeyNULLrows约等于全表行数ExtraUsing where原因很直接索引是按原始值排序存储的DATE(create_time)已经对列里的每个值做了一次函数变换原本的索引顺序完全打乱了优化器没法通过 B 树直接定位。这就像你把所有人的生日都格式化成“今天是星期几”然后让系统按星期几去查原始生日索引根本帮不上忙。正确写法是改成范围查询SELECT * FROM order_info WHERE create_time 2024-01-15 00:00:00 AND create_time 2024-01-16 00:00:00;这样 create_time 本身没有发生任何运算优化器就能正常走索引范围扫描。3.2 隐式类型转换索引“假装没看见”隐式类型转换是另一种很容易踩的坑。假设 order_no 是 VARCHAR 类型查询条件写成SELECT * FROM order_info WHERE order_no 123456;MySQL 会把字符串列和数字进行比较时尝试把字符串转成数字或者把数字转成字符串。关键在于如果转换发生在索引列这一侧索引就失效了。执行计划会从 keyuk_order_no 变成 keyNULLtypeALL。这也是为什么我经常建议写 SQL 的时候参数类型一定要和字段类型保持一致。ORM 框架有时候会自动处理类型但处理不完全可靠尤其在拼接原生 SQL 时类型问题只能靠人眼盯出来。排查的时候用 SHOW WARNINGS 能看到优化器重写后的查询那里面有非常明显的结果。提示在 EXPLAIN 之后执行 SHOW WARNINGS可以看到 MySQL 优化后的 SQL 文本能帮你快速发现隐式转换这类问题。3.3 联合索引不满足最左前缀等于白建联合索引是业务里最常用也最容易被理解错的索引。假设我建了 idx_user_status(user_id, status)那么查询SELECT * FROM order_info WHERE status 1;大概率走不了这个索引——因为联合索引的排序规则是先按 user_id 排再按 status 排。没有 user_id 的条件SQL 根本无法快速定位到指定 status 所在的索引区间。之前的实操里这种查询 EXPLAIN 后可能出现 typeindex、Extra 带 Using where如果查询列恰好都在索引里甚至会出现 Using index但它仍然是整个索引树全扫。理解最左前缀只需要记住一个类比联合索引像一本先按“姓氏”排、再按“名字”排的通讯录。你想找所有名字叫“伟”的人却不让系统先用姓氏定位就得把整本通讯录翻一遍。3.4 模糊查询、OR、范围条件等特殊场景除了函数运算和隐式类型转换索引失效还高发在这些场景场景失效原因处理建议LIKE %关键词前导通配符导致无法按索引前缀匹配改成 LIKE 关键词%或考虑全文索引OR 连接非索引列OR 两侧条件无法合并扫描路径改写为 UNION或给 OR 两侧都建索引范围查询之后接等值条件联合索引中 range 后续列无法用于过滤把范围字段放在联合索引最后IS NOT NULL / NOT IN优化器往往认为走索引回表代价太高结合数据分布判断必要时改写法这里面%关键词的问题我反复强调B 树索引天然支持前缀匹配不支持中缀和后缀匹配。好比字典的拼音目录你能按“bei”快速翻到“北京”却不能直接按“京”字翻出所有含“京”的词条除非另建一个倒排索引。4. 实操演示从建表到执行计划全解读4.1 建表与造数纸上谈兵没用我们实际建一张表把上面说的现象都复现一遍。表结构如下CREATE TABLE order_info ( id BIGINT PRIMARY KEY AUTO_INCREMENT, order_no VARCHAR(64) NOT NULL, user_id BIGINT NOT NULL, goods_id BIGINT NOT NULL, status TINYINT NOT NULL DEFAULT 0, create_time DATETIME NOT NULL, KEY idx_user_status (user_id, status), KEY idx_create_time (create_time), UNIQUE KEY uk_order_no (order_no) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;为了模拟真实场景我写了一个存储过程批量插入约 50 万行数据数据和统计信息都不算太干净这更贴近线上实际情况。夜深人静的时候跑一下大概几十秒。DELIMITER $$ CREATE PROCEDURE init_order_info() BEGIN DECLARE i INT DEFAULT 1; WHILE i 500000 DO INSERT INTO order_info(order_no, user_id, goods_id, status, create_time) VALUES ( CONCAT(NO, LPAD(i, 10, 0)), i % 5000, i % 1000, i % 3, DATE_SUB(NOW(), INTERVAL (i % 365) DAY) ); SET i i 1; END WHILE; END$$ DELIMITER ; CALL init_order_info();4.2 四条 SQL 的 EXPLAIN 结果解读SQL 1联合索引正常命中EXPLAIN SELECT * FROM order_info WHERE user_id 12345 AND status 1;预期输出typekeykey_lenrowsExtrarefidx_user_status9约 30无临时表无额外排序这里 key_len9说明 user_id8 字节和 status1 字节两列都用到了属于联合索引的完整使用方式。ref 列会显示 const,const。这个 SQL 是模范生。SQL 2函数导致索引失效EXPLAIN SELECT * FROM order_info WHERE DATE(create_time) 2024-01-15;预期输出typekeykey_lenrowsExtraALLNULLNULL约 50 万Using where这个例子就是典型的“看着字段有索引实际全表扫描”。只要 create_time 被 DATE() 包裹索引就形同虚设。修法前面说过了用区间查询替代函数。SQL 3联合索引不满足最左前缀EXPLAIN SELECT user_id, status FROM order_info WHERE status 1;预期输出typekeykey_lenrowsExtraindexidx_user_status9约 50 万Using where; Using index这里很有意思key 有值、key_len 还很完整甚至 Extra 出现了 Using index看起来是很完美的执行计划。但 typeindex意思是扫描了整棵索引树。因为我查询列恰好只有 user_id 和 status都在联合索引里所以不需要回表可代价依然是全索引扫描。如果这张表有千万级数据这种 SQL 同样能把资源耗尽。这就是“别只盯着 key”的最有力实证。SQL 4优化器放弃索引EXPLAIN SELECT * FROM order_info WHERE user_id 0;预期输出typekeykey_lenrowsExtraALLNULLNULL约 50 万Using where这不是写错了而是优化器故意选择全表扫描。user_id 0 几乎能命中全表所有记录如果走 idx_user_status 索引再回表代价比全表扫描高得多。执行计划的生成依据是成本模型遇到这样的条件索引确实“没被选择”但本质上不是索引失效而是优化器认为“用了更亏”。这种场景需要结合业务实际考虑比如按 user_id 分页是否合理、是否需要加其他过滤条件。4.3 EXPLAIN ANALYZE 进一步验证传统 EXPLAIN 输出的是估算值不是实际执行数据。MySQL 8.0.18 提供了 EXPLAIN ANALYZE可以直接看真实执行时间和循环次数EXPLAIN ANALYZE SELECT * FROM order_info WHERE user_id 12345 AND status 1;输出会带着 actual time、rows、loops 等真实数据。我在定位慢 SQL 时经常先用 EXPLAIN 看结构再用 EXPLAIN ANALYZE 确认实际行数和耗时两者差距大时多半是统计信息不准需要 ANALYZE TABLE 刷新。5. 常见误判与排查技巧实录5.1 三个最常见的误判第一个误判key 是 NULL 就认为没救了。其实 key 为 NULL 只是说明这步执行没选索引不代表没有可用索引可能是函数运算、类型隐式转换也可能是优化器觉得全表扫描更划算。此时应该先看 type 和 Extra。第二个误判key 有值就认为索引生效了。这个前面反复说过typeindex 就是反例。还有一种情况联合索引只用了前缀部分key_len 很短说明后面的字段没用上索引生效程度也要打个问号。第三个误判rows 小就认为快。rows 是优化器的估算值不是精确值而且它没算上回表代价。如果 Extra 里没有 Using index说明还要回表回表次数多了即使 rows 看起来不大实际查询也很慢。我把几个关键结论整理成速查表现象可能原因排查方向keyNULL typeALL函数运算、隐式转换、统计信息偏差SHOW WARNINGS调整 SQLkey有值 typeindex整树扫描联合索引最左前缀缺失调整查询条件或索引顺序key_len 异常偏短联合索引只用到前缀列检查 where 条件是否包含后续列rows 很大 filtered 很低先大范围筛选再小比例过滤考虑增加更精准的过滤列Extra 出现 Using filesort排序字段未能使用索引顺序让 order by 字段与索引顺序一致5.2 排查索引失效的实操流程我在线上排查慢查询时有一套相对固定的流程分享出来供参考。第一步先拿到慢 SQL用 EXPLAIN 看 type 级别。如果 type 是 ALL 或 index进入第二步如果是 range 或更好再看 Extra 里的其他信息。第二步看 key 和 key_len。key 是否为 NULLkey_len 是否符合预期判断联合索引是否用全。这里要熟悉自己的表结构和索引设计能通过 key_len 快速判断。第三步看 Extra 里的额外操作。Using filesort 意味着排序没走索引需要检查 order by 字段是否在索引中Using temporary 意味着临时表参与运算对大结果集非常致命Using index condition 则说明用上了索引条件下推通常不算坏消息。第四步用 SHOW WARNINGS 看优化器重写后的 SQL确认有没有隐式类型转换、子查询改写之类的隐性操作。第五步如果 SQL 看着没问题但优化器就是选了全表扫描可以考虑刷新统计信息ANALYZE TABLE order_info;如果刷新后依然如此可能是数据分布太偏优化器基于成本模型做出的判断确实是有道理的此时别死磕索引应该考虑改写 SQL 或调整索引设计。5.3 平时容易忽略的细节有几个细节属于平时很容易忽略、但坑很深的点。一是字符集和排序规则不一致。如果两张表 join 的字段一个是 utf8mb4一个是 gbkMySQL 需要做字符集转换这个转换会阻止索引使用。建表和设计字段时就要统一字符集。二是统计信息过期。大批量删改数据后统计信息不会实时更新优化器可能拿着旧的估算值做出错误决策。定期 ANALYZE TABLE 是非常有必要的。三是范围查询在联合索引中的位置。联合索引里范围条件后面的字段无法参与索引过滤。比如 idx(a, b, c)where a1 and b10 and c2c 的过滤条件在索引定位时用不上只能通过回表后的 where 再过滤。设计联合索引时尽量把范围查询的字段放在靠后的位置。四是关于 order by 的隐藏索引需求。order by 字段如果不在索引中就会出现 Using filesort。有时候查询本身不慢但加上 order by 之后突然变慢多半是排序没有索引可用。解决方式之一是让 order by 字段与现有联合索引顺序匹配。我个人在实际操作中的体会是EXPLAIN 不是用来“应付面试”的而是用来做决策的。拿到一条慢 SQL先把执行计划打印出来逐列看一遍再对照索引定义推演一遍 B 树的查找路径基本就能找到问题症结。这种方法论比记住一百个“索引失效例子”有用得多。最后再分享一个实用习惯每次优化完 SQL我会对比优化前后两次 EXPLAIN 的结果重点看 type 是否提升、rows 是否明显下降、Extra 是否少了 filesort。如果这三项没有明显好转那这个优化方案多半是无效的不要自我安慰。慢 SQL 优化这条路说到底就是不断用执行计划验证假设的过程坚持下来你也会形成自己的排查直觉。