Interview Lab / MySQL 数据库 / 查询优化

MySQL 索引失效：10 类原因、底层原理与 EXPLAIN 排查

索引就像按固定规则排序的目录，可以跳到一小段数据，而不必从头到尾逐行寻找。所谓“索引失效”，通常是指一条 SQL（Structured Query Language，结构化查询语言）没有高效地利用已有索引进行定位，或者 MySQL 判断使用它不划算。

本文以 MySQL 8.0 / 8.4 的 InnoDB 普通 B+Tree 索引为主。B+Tree 是一种按键值保持有序、可逐层缩小搜索范围的树形结构；InnoDB 是 MySQL 常用的表存储引擎。文章中的数据和执行计划示意用于解释机制，真实结果应以本机 EXPLAIN 为准。

① 高效定位

索引告诉数据库从哪里开始找，只检查较小的范围。例如用用户编号查几十笔订单。

② 用了索引，但没定位好

例如沿索引从头扫到尾，或联合索引只有前几列用于确定扫描范围。

③ 没选择这个索引

例如匹配 60% 的整表数据，优化器可能认为直接扫描表成本更低。

先记住一句：“是否选择索引”与“是否高效缩小扫描范围”是两个不同问题。索引失效不是非黑即白的状态。

10 类高频场景4 张推演式 SVG 图EXPLAIN / EXPLAIN ANALYZEMySQL 8.0 / 8.4

一、索引失效的 10 种常见原因

先看现象，再逐个追问“为什么”。下面 SQL 是独立的教学例子；每条是否选择索引仍与真实建表、数据分布和 MySQL 版本有关。

原因	典型 SQL / 前提	真正要理解的结论
1. 联合索引缺少最左列	`WHERE b=1`；索引 `(a,b,c)`	无法按 `b` 直接找到一个全局连续区间；某些条件允许 Skip Scan。
2. 索引列使用函数	`YEAR(create_time)=2026`	普通索引按原始日期排序，而非按年份表达式排序。
3. 索引列参与计算	`age+1=30`	原始索引不能直接按 `age+1` 的结果定位。
4. 隐式类型转换	`phone=13800138000`；列是 `VARCHAR`	字符串列与数字比较可能要求对列值转换。
5. LIKE 前面带通配符	`name LIKE '%张三'`	不知道字符串开头，就不能直接找到相邻的前缀区间。
6. OR 两侧索引条件不佳	`WHERE a=1 OR b=2`	需要兼顾两侧的候选记录；可能走 Index Merge，也可能全表扫描。
7. 范围列后继续过滤	`a=1 AND b>10 AND c=3`；索引 `(a,b,c)`	`c` 通常不能继续缩小连续索引扫描范围，但仍可能在索引层过滤。
8. 否定条件	`status != 1`	可能命中大量行；不等于操作符绝非“必定不用索引”。
9. 区分度低	`gender=1`；符合条件占 60%	大量回表可能比直接读表更贵。
10. 数据少或返回太多	`SELECT * FROM orders`	没有限制条件时，全表扫描往往就是合理方案。

不要死记“10 条禁令”：OR、!=、范围条件、缺少最左列都不是必然导致“完全不使用索引”。面试官更在意为什么、如何验证，以及具体是哪一部分无法参与定位。

二、先搞懂本质：B+Tree 为什么能快？

假设订单表有 8 个按 user_id 排好序的索引条目。要找编号 100，索引能够引导数据库进入这组相邻条目；如果只能按另一种规则逐条检查，就失去了快速定位的主要收益。真正的百万级索引是多层 B+Tree，下图把“逐层找叶子”的部分压缩为一行有序条目，只关注访问范围。

最应记住：索引的价值是跳到较小的有序范围，不是“出现索引名字就必定快”；容易误解的是把全索引扫描也当成快速定位。

三、联合索引：为什么缺少最左列就难定位？

联合索引是用多列一起组成一份有序目录。例如 CREATE INDEX idx_abc ON orders(a,b,c)：先按 a 排；a 相同，再按 b 排；a、b 都相同，最后按 c 排。可以把它想成先按省、再按市、最后按区排序的通讯录。

CREATE INDEX idx_abc ON orders(a, b, c);
-- A：a、b 从左到右连续，能找出一段相邻条目
SELECT * FROM orders WHERE a = 1 AND b = 2;
-- B：只给 b、c，不知道 a 分成了哪几组
SELECT * FROM orders WHERE b = 2 AND c = 3;

最应记住：a=1 AND b=2 对应连续下标 1、2；只用 b=2 AND c=3 的命中位置是 1、5、7，不连续。易错点：最左前缀讲的是有序定位能力，不是“缺 a 就绝对不能访问这份索引”。

版本例外：MySQL 8.0 开始的 Skip Scan（跳跃扫描）在满足特定限制时，会逐个尝试缺失的前缀取值，再对后续列做范围扫描。可用 EXPLAIN 的 Extra: Using index for skip scan 识别；并非任意省略首列的查询都会触发。

四、函数与计算：为什么“处理过的列”不好查？

普通索引保留原始列值的排序关系：create_time 索引按完整时间排序，而不是预先存下 YEAR(create_time) 的计算结果。需要对每条候选值计算年份时，通常无法直接按普通索引定位“年份 = 2026”的范围。

-- 不推荐：在普通索引列上计算
SELECT * FROM orders WHERE YEAR(create_time) = 2026;
-- 推荐：直接查原始时间的左闭右开范围
SELECT * FROM orders
WHERE create_time >= '2026-01-01'
  AND create_time <  '2027-01-01';
-- 同理：把运算移到常量侧
SELECT * FROM users WHERE age = 29; -- 代替 age + 1 = 30

最应记住：把函数写法改成原始列的范围条件，可以恢复按日期定位；易错点是认为函数本身“一定无法建索引”。

MySQL 8.0.13 起支持函数索引（为表达式计算结果建立的索引），例如：

CREATE INDEX idx_order_year ON orders ((YEAR(create_time)));
-- 有匹配表达式的函数索引时，优化器可以考虑使用
SELECT * FROM orders WHERE YEAR(create_time) = 2026;

优先考虑改写范围条件，因为它能复用已有的原始时间索引；函数索引适合频繁且难以改写的固定表达式查询，但会增加索引维护成本。

五、隐式类型转换：为什么字符串字段要加引号？

假设 phone 的类型是 VARCHAR(20)（可变长度字符串），已经有 idx_phone 索引。数据库比较字符串列与数字常量时，会按类型比较规则进行转换，可能不能沿原始字符串索引做准确的等值定位。

-- 不推荐：右边是数字，左边是字符串列
SELECT * FROM users WHERE phone = 13800138000;
-- 推荐：两侧语义一致，号码就是字符串标识
SELECT * FROM users WHERE phone = '13800138000';

原因并不只是“类型不同”：例如字符串 '1' 与 '01' 转成数字后都可能等于 1，却不是相同的字符串键。原本按照字符串排序的索引不能直接保证数字比较意义下的唯一连续位置。

这不是对所有隐式转换的一刀切结论。判断关键是转换是否使索引列原来的有序关系不能用于定位。业务代码应为参数指定正确类型，并用执行计划验证。

六、LIKE：为什么“张三%”与“%张三%”不同？

LIKE 是字符串模式匹配，百分号 % 表示任意长度的字符序列。普通 B+Tree 索引按字符串的开头排序，因此知道开头就有机会定位，只有中间片段就难以定位。

-- 前面有任意字符：不能利用普通索引直接做前缀范围定位
SELECT * FROM users WHERE name LIKE '%张三%';
-- 明确知道开头：有机会进行索引范围查找
SELECT * FROM users WHERE name LIKE '张三%';

例如目录里“张三丰、张三李、张三王”在同一段；“李张三、王张三、张三丰”没有共同开头，可能散落在多处。前导通配符并不等于完全不访问索引：覆盖索引的全索引扫描仍可能出现，但不是高效前缀定位。全文搜索或专门搜索系统适合大量包含匹配场景。

七、范围查询、OR、否定条件：看清“扫描范围”和“过滤”

1. 联合索引遇到第一个范围条件

假设索引是 (a,b,c)，条件为 a=1 AND b>10 AND c=3。利用 a=1 能找到一组，利用 b>10 能找到组内的范围。但按 b 排列后，各个 b 下面的 c=3 往往不能拼成一个连续的扫描区间。

SELECT * FROM orders
WHERE a = 1 AND b > 10 AND c = 3;

最应记住：这里 b 决定需要扫描的连续 6 条，c 再筛出其中 3 条；易错点是把“c 不能继续缩小连续范围”说成“c 完全没作用”。

如果使用索引条件下推（Index Condition Pushdown，简称 ICP，即在读取完整行之前，尽可能先用索引里已有的字段判断条件），c=3 可能在索引层过滤，减少回表。它能省成本，但不等于把前面的范围缩短。

2. OR 为什么可能扫描整表？

SELECT * FROM orders WHERE a = 1 OR b = 2;

数据库需要找出两侧任意一侧成立的所有行。仅有 idx_a 时，b=2 的行可能散落整表。即使有 idx_a 和 idx_b，优化器也要比较“分别查两份索引再合并”与“直接扫描整表”的代价。前者可能使用 Index Merge（索引合并：组合多个索引结果集），但不是保证。

3. !=、NOT IN、NOT LIKE 为什么不一定失效？

否定条件可能覆盖大量值，例如 100 万行有 90 万行满足 status != 1。索引扫描 + 大量回表可能不划算；但如果只匹配很少行、查询被索引覆盖，或者范围访问成本低，仍可能选索引。结论必须结合数据分布和执行计划。

八、优化器为什么主动放弃索引？

优化器是 MySQL 在执行前比较多种查询方式、估算成本并选择方案的组件。考虑 100 万行用户表，gender=1 命中 60 万行；gender 已经有单列索引。

CREATE INDEX idx_gender ON users(gender);
SELECT * FROM users WHERE gender = 1;

如果走二级索引，先找到 60 万个匹配的索引条目；若 SELECT * 需要索引未包含的字段，还要回表（通过主键取出完整记录）。这些读取可能分散在许多数据页上。直接扫描表反而可能更便宜。

路径 A：二级索引 + 回表

命中约 60 万条索引记录；需要完整记录时，再访问相应主键记录。潜在随机读多。

路径 B：全表扫描

扫描约 100 万条记录，顺着数据页批量读取。读得多，但访问方式可能更便宜。

不是 60 万一定比 100 万慢：真实优化选择还取决于页数、缓存、聚簇程度、选择列、统计信息和存储成本。区分度低只是“容易让回表不划算”的信号。小表、不带过滤条件或需要返回大多数行时，ALL 也可能是正确的选择。

九、怎么判断索引是否真正失效？

先用 EXPLAIN（执行计划说明命令）看优化器打算如何执行 SQL。最好在真实数据规模或接近生产的测试库中分析。

EXPLAIN SELECT * FROM orders WHERE user_id = 1001;
-- MySQL 8.0.18+：实际执行 SELECT 并测量
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 1001;

字段	怎么读	典型提醒
`possible_keys`	优化器考虑的候选索引	列在这里，不代表最终使用。
`key`	实际选用的索引；`NULL` 表示本步未选择索引	有值也可能扫描整份索引。
`type`	访问方式：`const`、`ref`、`range`、`index`、`ALL` 等	`ALL` 是全表扫描；`index` 通常是全索引扫描。
`key_len`	选择的索引键长度，单位字节	辅助判断联合索引使用情况；受类型、字符集、NULL 等影响，不能单独断言每列是否用于范围定位。
`rows`	优化器预计需要检查的记录数	是估算值，不等于真实扫描量。
`Extra`	额外操作，例如 `Using filesort`、`Using temporary`、`Using index condition`	这些提示不能脱离整个计划直接判定性能好坏。

type=range，key 有值

能够按索引范围读记录；继续核对范围是否足够小，以及实际检查行数。

type=index，key 有值

使用了索引，但可能把整份索引从头扫到尾；不要误判为高效点查。

type=ALL，key=NULL

当前步骤为全表扫描；先确认是否本来就要返回大部分行。

建议按四步排查

先查建表与索引：SHOW INDEX FROM orders;，确认字段顺序、类型、选择列和查询谓词。
再查访问路径：看 key → type → rows → Extra，不要仅看 possible_keys。
比较等价 SQL：例如把 YEAR(create_time)=2026 改成时间范围，比较执行计划及扫描量；只改变一个条件更容易定位原因。
必要时实际测量：在安全环境使用 EXPLAIN ANALYZE；它会真正执行查询。结合慢查询日志、统计信息和索引设计判断是否优化。

注意：EXPLAIN ANALYZE 会实际运行查询，可能消耗资源。不要在生产库直接对高成本语句反复执行；普通 EXPLAIN 只提供估算计划。FORCE INDEX 适合对照验证，不应未经数据证实就作为长期修复方案。

如果想深入逐列读执行计划，继续看《MySQL 执行计划：10 分钟看懂 EXPLAIN》。

十、面试怎么回答？再准备三个追问

推荐回答：“MySQL 索引失效主要分三类。第一类是 SQL 表达式问题，例如索引列使用函数、计算、发生影响排序关系的隐式类型转换，或 LIKE 前置百分号，使普通 B+Tree 不能直接做有效范围定位。第二类是联合索引使用问题，例如缺少最左前缀、范围列之后不能继续缩小连续扫描范围，但后续列仍可能用于过滤。第三类是优化器成本选择，例如匹配行多、字段区分度低、回表太贵，最终选全表扫描。排查时我先确认索引结构，再看 EXPLAIN 的 key、type、rows、Extra，必要时用 EXPLAIN ANALYZE 核对实际行为。”

追问 1：只要 key 不是 NULL，就算索引生效吗？

不算完整回答。type=index 可能扫描整份索引；联合索引后续列也可能只是过滤。要看索引有没有显著缩小扫描范围、回表次数和实际耗时。

追问 2：为什么 `a=1 AND b>10 AND c=3` 说 c 用不上？

更准确的说法是：一般情况下，c 无法进一步缩小由前面范围条件决定的连续索引区间；但它仍可作为索引条件被检查，有机会通过 ICP 减少回表。

追问 3：为什么索引设计正确，MySQL 反而全表扫描？

因为优化器对比的是成本，不是“优先使用索引”。当命中比例过高、二级索引回表代价大时，全表扫描可能更合理；也要检查统计信息是否陈旧。

十一、实际业务：订单查询如何排查一次慢 SQL？

场景：订单表有创建时间索引，运营页面需要查询“2026 年订单”，写成了 YEAR(create_time)=2026，查询明显变慢。

复现：拿慢 SQL 做 EXPLAIN，确认是否出现全表扫描或全索引扫描。
最小改写：把函数条件改为 create_time >= '2026-01-01' AND create_time < '2027-01-01'，其余字段、排序和分页不变。
对比：检查 key 是否选中时间索引、type 是否变为 range、估算扫描行数是否下降；在可控环境测实际耗时。
再决策：如果仍匹配数百万条订单，还要考虑分页、排序、覆盖索引及返回列，而不是机械地加更多索引。

面试表达：“我不把索引失效等同于没索引。遇到慢 SQL，我会先确认扫描范围和回表成本，再通过执行计划对比验证 SQL 改写是否真的有效。”

资料依据与版本边界

本专题中的优化器规则基于 MySQL 官方参考手册。详细条件及限制以对应数据库版本文档为准：

图中的下标和样例数据是教学用的简化模型；不代表 MySQL 真实的页布局或固定执行结果。InnoDB B+Tree 的多层磁盘页模型请参阅索引结构专题。