MySQL 索引失效的场景有哪些

MySQL 索引失效是指虽然表中存在索引，但优化器在执行查询时未使用该索引，转而进行全表扫描或其他低效操作。以下是导致索引失效的主要场景：

1. 违背最左前缀原则

在联合索引（如 (a, b, c)）中，查询条件必须从最左侧字段开始连续匹配。若跳过最左列（如直接使用 b 或 c 作为条件），或中间列缺失，则后续列无法用于索引定位 。范围查询（>, <, BETWEEN, LIKE 'abc%'）会导致其右侧列无法继续用于精确查找，仅能用于过滤（ICP）。

2. 模糊查询以通配符开头

使用 LIKE '%value' 或 LIKE '%value%' 时，由于 B+Tree 无法利用前缀进行二分查找，索引将失效 。若改为 'value%' 或使用覆盖索引（仅查询索引包含的字段），则索引仍可能生效 。

3. 索引列上执行函数或运算

对索引列使用函数（如 ABS(), DATE(), SUBSTRING()）或算术表达式（如 id + 1 = 5），会破坏索引值的有序性，导致无法使用 B+Tree 快速定位 。

4. 隐式类型转换

当查询条件中的数据类型与索引列定义不一致时（如字符串列传入数字 WHERE phone = 13800000000），MySQL 会对索引列进行隐式 CAST 转换，等同于对索引列使用了函数，从而导致索引失效 。

5. OR 条件中包含非索引列

若 OR 连接的条件中，任一列无索引，优化器通常会放弃所有索引而选择全表扫描，因为“索引扫描 + 全表扫描 + 合并结果”的成本往往高于直接全表扫描 。即使两侧都有索引，也可能因回表成本过高而失效。

6. 优化器基于成本主动放弃索引

即使查询命中索引，若预估返回数据量占全表比例较高（通常 >20%~30%），优化器会认为顺序全表扫描比随机回表 IO 更高效，从而主动放弃索引 。此外，表数据量极少时，全表扫描开销更低，索引也会被忽略 。

7. 其他常见失效场景

• 不等于操作：!= 或 <> 通常无法有效利用索引排序特性 。
• IS NOT NULL：在某些版本和统计信息下可能导致索引未被选用 。
• 排序/分组字段与索引不匹配：ORDER BY 或 GROUP BY 的字段顺序、方向与索引不一致时，无法利用索引避免 filesort 。
• 索引列区分度过低：如性别、状态等枚举值重复率极高，优化器可能判定索引无效 。

建议始终通过 EXPLAIN 分析执行计划，结合业务场景验证索引实际使用情况。