MySQL 索引失效是一个常见且非常重要的性能优化话题。当索引失效时，数据库会进行全表扫描，严重拖慢查询速度。

以下是 MySQL 索引失效的主要情况：

1. 违反最左前缀原则 (Leftmost Prefix Principle)

这是复合索引（多列索引）最常见的问题。索引可以类比为字典的目录，它是按字母顺序排列的。你不能跳过前面的字母直接按后面的字母查。

• 原因：复合索引 (a, b, c) 的存储顺序是先按 a 排序，a 相同再按 b 排序，b 相同再按 c 排序。如果你跳过 a 直接查询 b 或 c，索引就无法被有效使用。

• 示例：

  • 表：user，有复合索引 idx_name_age (name, age)

  • 有效：WHERE name = 'Alice' (使用索引第一列)

  • 有效：WHERE name = 'Alice' AND age = 30 (使用索引所有列)

  • 失效：WHERE age = 30 (跳过了第一列 name，无法使用索引树快速定位)

2. 在索引列上做计算、函数或类型转换

• 原因：索引存储的是列原始的值。如果你对列进行加工（如计算、使用函数），MySQL 无法从索引中直接获取加工后的值，只能对每一行数据进行计算后再比较，从而导致全表扫描。

• 示例：

  • WHERE YEAR(create_time) = 2023 (对 create_time 使用了 YEAR() 函数)

    • 优化：WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01'

  • WHERE amount * 2 > 100 (对索引列 amount 进行了计算)

    • 优化：WHERE amount > 50 (将计算移到运算符右侧)

  • WHERE id = ‘123’ (如果 id 是数字类型，但用字符串查询，发生了隐式类型转换)

    • 优化：WHERE id = 123 (确保类型一致)

3. 使用不等于查询 (!=, <> 或 NOT IN)

• 原因：不等于操作符匹配的数据范围非常大，通常超过表的 20%-30% 时，优化器认为全表扫描比走索引再回表更高效。

• 示例：

  • WHERE status != 'done’

  • WHERE id NOT IN (1, 2, 3)

注意：如果查询结果集占全表比例很小（比如 status 的状态值很多，‘done’ 占绝大多数，你要查少数几个非 ‘done’ 的记录），有时强制使用索引 (FORCE INDEX) 可能更快，但这需要具体测试。

4. 使用 LIKE 以通配符开头

• 原因：同样是因为索引的有序性。LIKE ‘%abc’ 或 LIKE ‘%abc%’，MySQL 不知道开头是什么，无法利用索引树的有序性进行查找，只能全表扫描。

• 示例：

  • WHERE name LIKE ‘%son’ (失效)

  • WHERE name LIKE ‘son%’ (有效，因为开头是确定的)

5. 对索引字段使用 OR 连接条件

• 原因：如果 OR 连接的条件中有一个字段没有索引，那么整个查询都不会使用索引。

• 示例：

  • 假设 name 有索引，age 没有索引。

  • WHERE name = ‘Alice’ OR age = 30 (失效，因为 age 无索引，需要全表扫描来满足 OR 条件)

  • 优化：常用方法是改用 UNION 将查询拆分：

    SELECT * FROM user WHERE name = ‘Alice’
    UNION
    SELECT * FROM user WHERE age = 30;

    (前提是 age=30 的结果集很小，否则 UNION 反而更慢)

6. 索引列使用 IS NULL 或 IS NOT NULL

• 原因：这取决于表的数据分布和版本优化。

  • 对于 IS NULL：如果表中绝大多数数据都是 NOT NULL，只有少量 NULL 值，那么查询 IS NULL 时使用索引是高效的。反之，则可能失效。

  • 对于 IS NOT NULL：因为它通常匹配表中大部分数据，优化器很可能选择全表扫描。

• 建议：最好将列定义为 NOT NULL 并设置默认值，避免使用 NULL。

7. 数据分布问题导致优化器放弃索引

• 原因：MySQL 优化器会分析数据的分布（通过索引的统计信息），如果它预判使用索引比全表扫描成本更高，就会放弃索引。

• 场景：

  • 表中数据量非常少。

  • 索引列的值重复度非常高（选择性低）。例如，在 gender 字段（只有 ‘M‘， ’F‘ 两种值）上建索引，查询 WHERE gender = ‘M’ 会匹配大约一半的数据，优化器认为全表扫描更快。

8. 范围查询后的索引列失效

• 原因：在复合索引中，如果某一列使用了范围查询（>, <, BETWEEN, LIKE ‘abc%’），那么它右边的列的索引将失效。

• 示例：

  • 复合索引 (a, b, c)

  • 查询：WHERE a = 1 AND b > 10 AND c = 3

  • 分析：索引 a 和 b 会被使用，但 c 无法再使用索引来快速定位（因为 b 是一个范围，在这个范围内的 c 是无序的）。

如何诊断索引是否失效？

使用 EXPLAIN 命令查看执行计划，重点关注以下字段：

• type：ALL 表示全表扫描。index、range、ref、eq_ref、const 表示使用了索引，性能从差到好。

• key：显示实际使用的索引。如果为 NULL，则表示未使用索引。

• rows：预估需要扫描的行数，数值越大性能越差。

• Extra：出现 Using filesort（需要额外排序）或 Using temporary（使用临时表）通常也是需要优化的信号。

示例：

EXPLAIN SELECT * FROM user WHERE name LIKE ‘%test’;

查看结果中的 key 是否为 NULL 以及 type 是否为 ALL。

总结与建议

1. 设计索引时：理解最左前缀原则，根据查询需求设计合理的复合索引顺序。

2. 编写 SQL 时：避免对索引列做计算、使用函数；谨慎使用 !=, NOT IN, OR；LIKE 尽量不加前导通配符。

3. 表设计时：选择合适的数据类型，尽量使用 NOT NULL 约束。

4. 分析问题时：养成使用 EXPLAIN 分析慢查询的习惯，不要凭感觉猜测。

5. 理解优化器：索引不是总会被使用，优化器会基于成本选择它认为最优的方案。当数据分布发生变化时，执行计划也可能变化。