docs/database/mysql/mysql-index-invalidation.md
在数据库性能优化中，索引是最直接有效的优化手段之一。然而，建了索引并不等于一定能用上索引。实际开发中，我们经常遇到这样的困惑：明明在字段上建立了索引，查询却依然慢如蜗牛，通过 EXPLAIN 分析发现居然是全表扫描。
导致索引失效的原因多种多样，既有 SQL 语句写法问题，也有索引设计不当的因素。有些失效场景是显性的（如违背最左前缀原则），有些则非常隐蔽（如隐式类型转换）。如果不深入了解这些失效场景，很容易在生产环境中埋下性能隐患。
本文将系统总结 MySQL 索引失效的常见场景，分析失效背后的原理机制，并提供相应的优化建议，帮助你在日常开发和排查问题中快速定位并解决索引失效问题。
SELECT * 本身不会直接导致索引失效。它是一种”非覆盖索引”查询，如果 WHERE 条件命中了索引，索引依然会被初步考虑。SELECT 需要的字段 即可。当需要大部分字段时，代码可读性可能比”省几个字段”的微优化更重要，此时用 SELECT * 也无妨。SELECT 需要的字段，能覆盖索引最好；如果需要大量字段且回表不可避免，不必教条地”省字段”。核心定义：最左前缀匹配原则指的是在使用联合索引时，MySQL 会根据索引中的字段顺序，从左到右依次匹配查询条件中的字段。如果查询条件与索引中的最左侧字段相匹配，那么 MySQL 就会使用索引来过滤数据。
范围查询的中断效应：在联合索引中，如果某个字段使用了范围查询（例如 >、<、BETWEEN、前缀匹配 LIKE "abc%"），该字段本身以及其之前的列可以正常匹配并用于索引的精确定位，但该字段之后的列将无法利用 索引进行快速定位（即无法使用 ref 类型的二分查找）。这是因为在 B+Tree 索引结构中，只有当前导列完全相等时，后续列才是有序的。一旦前导列变成一个范围，后续列在整个扫描区间内就呈现相对无序状态，从而中断了精准定位能力。不过，在 MySQL 5.6 及以上版本中，这些后续列并未完全失效，而是降级为使用索引下推（Index Condition Pushdown, ICP）机制，在范围扫描的过程中直接进行条件过滤，以此来减少回表次数。
索引跳跃扫描 (ISS)：MySQL 8.0.13 引入了索引跳跃扫描（Index Skip Scan），允许在缺失最左前缀时，通过枚举前导列的所有 Distinct 值来跳跃扫描后续索引树。
Index Skip Scan 失败路径图：
sequenceDiagram
participant Executor
participant InnoDB_Index
Note over Executor, InnoDB_Index: MySQL 8.0.31 触发 ISS Bug 场景
Executor->>InnoDB_Index: Read Range 1 (Prefix A)
InnoDB_Index-->>Executor: Return Rows, Set End-of-Range = X
Executor->>InnoDB_Index: Read Range 2 (Prefix B)
Note right of InnoDB_Index: [BUG] 未清理上一个 Range 的 End-of-Range X
InnoDB_Index-->>Executor: 发现当前值 > X，错误判定越界，提前终止！
Note over Executor: 导致结果集丢失 (Incorrect Result)
失效示例：
-- 索引：(sname, s_code, address)
SELECT * FROM students WHERE s_code = 1; -- 跳过最左列 sname，索引失效
SELECT * FROM students WHERE sname = 'A' AND address = 'Shanghai'; -- 跳过中间列，仅 sname 走索引（索引下推 ICP 可优化过滤）
SELECT * FROM students WHERE sname = 'A' AND s_code > 1 AND address = 'Shanghai'; -- 范围查询后，address 无法用于定位，仅用于过滤
WHERE 条件中对索引列应用了函数（如 ABS()、DATE()）或算术运算，该列的值在逻辑上发生了改变。失效示例：
SELECT * FROM students WHERE height + 1 = 170; -- 对索引列进行计算
SELECT * FROM students WHERE DATE(create_time) = '2022-01-01'; -- 对索引列使用函数
优化建议：
SELECT * FROM students WHERE height = 169; -- 将计算移到等号右边
SELECT * FROM students WHERE create_time BETWEEN '2022-01-01 00:00:00' AND '2022-01-01 23:59:59';
LIKE 查询必须以具体字符开头才能利用索引有序性，例如 WHERE sname LIKE 'Guide%';。这是因为 B+ 树是从左到右排序的。前缀通配符（%）破坏了有序性，无法定位起始点。% 开头（如 '%abc'），由于索引是按字符从左到右排序的，前缀不确定意味着可能出现在索引树的任何位置，导致无法定位搜索区间的起始点。EXPLAIN 会显示 type: index（Index Full Scan），虽然扫描了整棵树，但无需回表，性能仍优于 ALL。失效示例：
SELECT * FROM students WHERE sname LIKE '%Guide'; -- 前缀模糊，全表扫描
SELECT * FROM students WHERE sname LIKE '%Guide%'; -- 前后模糊，全表扫描
OR 连接的多个条件中，只要有任意一列没有索引，MySQL 就会放弃所有索引转而执行全表扫描。OR 两侧都有索引，MySQL 5.1+ 可能会触发**索引合并（Index Merge）**优化，分别扫描两个索引后取并集。不过，如果两个索引过滤后的数据量都很大，合并结果集的成本可能高于全表扫描，依然会放弃索引。OR 改写为 UNION ALL。UNION ALL 可以让每一段查询独立使用索引，且规避了优化器对 OR 成本估算不准的问题。UNION ALL，否则需用 UNION（涉及临时表去重，有额外开销）。失效示例：
-- 假设 sname 和 address 都有索引，但各匹配 30%+ 数据
SELECT * FROM students WHERE sname = '学生 1' OR address = '上海'; -- 可能放弃索引，全表扫描
-- 建议改写为
SELECT * FROM students WHERE sname = '学生 1'
UNION ALL
SELECT * FROM students WHERE address = '上海'; -- 各自走索引
验证方式：EXPLAIN 中若出现 type: index_merge 和 Extra: Using union; Using where，说明使用了 Index Merge。
IN 列表长度：
eq_range_index_dive_limit（默认 200）并不直接导致索引失效，而是影响行数估算策略：
IN 列表长度超过 eq_range_index_dive_limit（MySQL 5.7.4+ 默认为 200）时，优化器从精确的 Index Dive 切换为基于 index_statistics 的估算。若表数据的基数（Cardinality）统计陈旧，可能导致估算成本异常，从而放弃走范围扫描（Range Scan）而选择全表扫描。eq_range_index_dive_limit 或改写为 JOIN 临时表来规避。NOT IN ：
NOT IN (1,2,3)）：通常全表扫描，因需遍历整个 B+ 树证明"不在集合中"。WHERE id NOT IN (SELECT user_id FROM orders WHERE user_id > 1000) 可用 orders 表的 user_id 索引。NOT EXISTS 或 LEFT JOIN / IS NULL，性能更优且语义更清晰。失效示例：
SELECT * FROM students WHERE s_code IN (1, 2, 3, ..., 500); -- 列表过长，可能改用统计估算导致误判
SELECT * FROM students WHERE s_code NOT IN (1, 2, 3); -- 常量列表，全表扫描
这是开发中最隐蔽的坑，转换的方向决定了索引的生死。
| 场景 | 示例 | 转换方向 | 索引是否有效 |
|---|---|---|---|
| 字符串列 + 数字值 | varchar_col = 123 | 字符串转数字（发生在索引列） | ❌ 失效 |
| 数字列 + 字符串值 | int_col = '123' | 字符串转数字（发生在常量） | ✅ 有效 |
关键点：
int_col = '123' 会被转换为 int_col = CAST('123' AS DOUBLE)，转换发生在常量侧，不影响索引使用。详细介绍：MySQL隐式转换造成索引失效
即使 WHERE 条件精准，如果 ORDER BY 处理不好，依然会出现慢查询。
触发 Using filesort 的条件：
ORDER BY 不一致（如索引 (a,b) 但 ORDER BY b,a）WHERE 与 ORDER BY 分别使用不同索引SELECT * 中非索引列（需回表排序）优化方案：
WHERE 和 ORDER BY。例如索引为 (name, age)，查询 SELECT name, age FROM users WHERE name = 'A' ORDER BY age。ORDER BY。验证方式：EXPLAIN 中 Extra 列出现 Using filesort 即表示触发了排序。
本文系统梳理了 MySQL 索引失效的常见场景，从底层机制上可归纳为以下两大核心类：
1. SQL 写法与底层逻辑冲突（破坏 B+Tree 有序性）
此类问题最为常见，本质是查询条件让底层的 B+Tree 失去了“二分查找”的快速定位能力。
>、<、BETWEEN、LIKE "abc%"）导致后续列中断精确定位，降级为范围扫描加过滤。WHERE 左侧对索引列进行数学计算或应用函数，导致原始数据发生逻辑改变，在索引树中呈现无序状态。LIKE "%abc"，前缀字符的不确定性使得优化器无法锁定扫描区间的起始点。Using filesort）。2. 优化器的成本决策（基于 I/O 成本妥协）
此类问题并非索引本身不可用，而是 MySQL 优化器经过计算后，认为”不走普通索引”整体开销反而更小。需要特别说明的是：优化器选择全表扫描或回表查询，往往是正确的成本决策，而非”性能问题”。
SELECT * 的场景权衡：优先 SELECT 需要的字段，能命中覆盖索引最好。如果需要大量非索引字段且回表不可避免，不必教条地"省字段"——当需要大部分字段时，代码可读性可能比"少传几个字段"的微优化更重要。OR 条件导致全表扫描：只要 OR 连接的任意一侧条件没有对应索引，就会触发全表扫描。即使两侧都有索引，若 Index Merge（索引合并）的预期成本过高，依然会被放弃。IN 列表过长引发估算失真：当 IN 列表长度超过系统阈值（默认 200）时，优化器会从精准的深入探测（Index Dive）切换为粗略的统计估算，极易因统计信息陈旧而产生执行成本的误判。实战建议：
EXPLAIN 分析习惯：在编写复杂 SQL 后，务必使用 EXPLAIN 分析执行计划，重点关注 type、key、rows、Extra 字段。注意：type: ALL 不一定是问题，可能是优化器的正确决策。%keyword%），建议使用 Elasticsearch 等搜索引擎。索引优化是数据库性能优化的基本功，但也需要结合实际业务场景和数据分布进行权衡。理解索引失效的根本原因，才能在遇到性能问题时快速定位并解决。
延伸阅读：