核心思想：索引的本质

首先要明白，索引（尤其是B+Tree索引）就像一个电话簿，它是有序排列的。任何破坏这种顺序或者阻止数据库利用这种顺序的操作，都可能导致索引失效

个人常见索引失效场景总结

1. 违反最左前缀法则

这是复合索引（多列索引）最常见的问题

• 场景：你有一个 INDEX(idx_name_age_sex) 索引，顺序是 (name, age, sex)

有效查询：

WHERE name = 'John'-- √ 使用了索引第一部分
WHERE name = 'John' AND age = 25-- √ 使用了索引前两部分
WHERE name = 'John' AND age = 25 AND sex = 'M' -- √ -- √ 使用了全部索引

失效查询：

WHERE age = 25    -- 跳过了开头的 name
WHERE sex = 'M'    -- 跳过了前两列
WHERE age = 25 AND sex = 'M'    -- 跳过了开头的 name

原因：就像你不能用电话簿的「姓氏」排序去找一个只知道「名字」的人一样，数据库无法利用索引的有序性

2. 在索引列上做运算或使用函数

• 场景：对索引列进行 数学运算、函数调用 或 类型转换

失效查询：

WHERE YEAR(create_time) = 2023-- ✗ 对索引列用了函数
WHERE amount * 2 > 100-- ✗ 对索引列做了运算
WHERE left(name, 3) = 'Joh' -- ✗ 对索引列用了函数

原因：索引中存储的是列的原始值，而不是运算或函数调用后的值。数据库必须对每一行都执行这个操作后才能比较，因此无法使用索引

正确写法：

WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01'-- √ 范围查询
WHERE amount > 50-- √ 直接比较
WHERE name LIKE 'Joh%'-- √ 利用了前缀匹配（如果name是索引）

3. 头部模糊查询 (LIKE '%xxx')

• 场景：使用 LIKE 进行模糊查询，且通配符 % 在开头

失效查询：

WHERE name LIKE '%ohn'-- ✗ 头部模糊，无法利用有序性
WHERE name LIKE '%oh%'-- ✗ 头尾都模糊，同样失效

有效查询：

WHERE name LIKE 'Joh%'-- √ 尾部模糊，可以利用前缀匹配

原因：'Joh%' 可以利用索引找到所有以 "Joh" 开头的条目。而 '%ohn' 要求数据库检查每一个值的结尾，索引的有序性毫无用处

4. 类型转换（隐式类型转换）

• 场景：查询条件中，索引列的类型与传入值的类型不匹配
• 假设：user_id 是 VARCHAR 类型，但有索引

失效查询：

WHERE user_id = 12345    -- ✗ 数字 vs 字符串

有效查询：

WHERE user_id = '12345'    -- √ 类型一致

原因：当类型不匹配时，MySQL 需要先将 user_id 列中的每一行字符串转换为数字，然后再与 12345 比较。这相当于在索引列上使用了函数 (CAST(user_id AS INT))

5. 使用 OR 连接非索引列条件

• 场景：使用 OR 连接多个条件，且并非所有列都有索引
• 假设：name 有索引，但 email 没有索引。

失效查询：

WHERE name = 'John' OR email = 'john@example.com' -- ✗

原因：为了让 OR 生效，数据库必须找出满足任一条件的行。如果 email 没有索引，它就必须进行全表扫描来检查 email 条件。一旦优化器判定需要全表扫描，它通常会放弃使用 name 上的索引，因为额外的索引查找反而会增加开销

WHERE status NOT IN (1, 2)-- ✗ 可能失效，尤其当数据量大时
WHERE id <> 100-- ✗ 可能失效

解决方法：

1. 为 email 也创建索引
2. 使用 UNION 改写查询

SELECT * FROM users WHERE name = 'John'
UNION
SELECT * FROM users WHERE email = 'john@example.com';

6. 不当使用 NOT IN, <>, !=

• 场景：使用负向查询条件

失效查询：

WHERE status NOT IN (1, 2)-- ✗ 可能失效，尤其当数据量大时
WHERE id <> 100-- ✗ 可能失效

原因：这些操作本质上需要检查所有不属于某个集合的行。这与 = 或 IN 不同，后者可以快速定位到索引树中的特定点。负向查询往往需要扫描大部分索引，优化器可能认为直接全表扫描更划算

7. 范围查询右边的列失效

• 场景：在复合索引中，某一列使用了范围查询 ( >,  <,  BETWEEN )，那么它右边的所有列都无法再使用索引进行检索

假设：索引 (name, age, sex)

失效情况：

WHERE name = 'John' AND age > 20 AND sex = 'M' -- ✗ sex 无法用索引

原因：age > 20 是一个范围，在这个范围内，sex 的值是无序的。因此，数据库只能用到索引name 和 age，然后用sex = 'M' 作为过滤条件，在索引结果集里再进行筛选

8. 数据分布的影响

场景：当优化器判断使用索引比全表扫描更慢时

例子：如果一个状态列 status 只有 1 和 2 两个值，并且 99% 的数据都是 1

WHERE status = 2; -- √ 可能会使用索引，因为只需要返回很少的行。
WHERE status = 1; -- ✗ 可能不会使用索引，因为返回几乎所有行，全表扫描更快。

9. 索引列的区分度太低

场景：索引列的值几乎没有变化，区分度很差

例子：在一个有 1000 万行用户的表中，为一个只有 “男 / 女” 的 gender 列创建索引。

原因：即使使用了 gender 索引，你也得返回大约 500 万行数据，然后在其中再做筛选。这种情况下，索引带来的好处微乎其微，优化器可能会忽略它

10. IS NULL 和 IS NOT NULL 的不确定性

场景：取决于列中 NULL 值的分布和数据总量

情况：

• 如果表中绝大多数行的该列都是 NULL，那么 IS NOT NULL 可能会使用索引，因为它只需要找出一小部分非NULL值；
• 反之，如果只有少量 NULL 值，IS NULL 可能会使用索引；
• 如果列被定义为 NOT NULL，则 IS NULL 条件永远不成立

如何诊断索引是否失效？

一般使用 MySQL 的 EXPLAIN 命令

EXPLAIN SELECT * FROM users WHERE name LIKE '%ohn';

关注 EXPLAIN 输出中的以下几个关键字段：

type：

• const, eq_ref, ref, range 表示索引使用良好
• index：表示全索引扫描（比全表扫描好一点，但也不理想）
• ALL：表示全表扫描，索引失效了

key：显示MySQL实际决定使用的索引。如果为 NULL，则表示没有使用索引。

rows：表示MySQL认为它需要检查的行数。这个值越大，说明查询成本越高。

总结

遵循最左前缀原则，设计复合索引时，将最常用的、区分度最高的列放在左边；

避免在 WHERE 子句中对索引列进行运算或使用函数；

谨慎使用模糊查询，尽量避免 % 开头的 LIKE；

保持类型一致，确保查询条件的类型与列定义的类型一致 ……