MySQL索引失效的9大常见原因及优化策略

前言：索引与优化器机制

合理的索引设计能大幅提高数据库查询速度。未使用索引时，数据库需扫描表中所有记录（全表扫描），在数据量大时性能极差。大多数情况下，MySQL默认采用 B+ 树 构建索引；空间列类型使用 R-树，而 MEMORY 表支持 Hash 索引。

是否使用索引由 MySQL 的优化器决定。MySQL 采用的是 基于成本的优化器 (Cost-Based Optimizer, CBO)，而非基于规则或语义。优化器会评估不同执行路径的成本（如IO次数、CPU开销），选择成本最低的方案。SQL是否使用索引，还受数据库版本、数据总量及数据选择性（Selectivity）的影响。

1. 违反“最佳左前缀法则”

这是联合索引中最常见的问题。Alibaba《Java开发手册》指出：索引文件具有 B-Tree 的最左前缀匹配特性。如果联合索引为 (col1, col2, col3)，查询条件中必须从最左侧开始匹配。若左边列的值未确定或未参与查询，则无法使用该索引。

2. 主键插入顺序不当导致性能损耗

虽然这主要影响写入性能而非查询时的索引查找效率，但它是索引设计的核心原则。若主键值无序插入，会导致 B+ 树节点频繁分裂（Page Split）和记录移位，产生巨大的性能损耗。

假设当前页面已满，插入新记录时若发生页分裂，系统需将部分记录移动到新页。为避免此损耗，建议让主键具有 AUTO_INCREMENT 属性，使存储引擎自动按递增顺序生成主键。这样可实现顺序写入，减少页分裂开销，保持索引结构紧密。

3. 对索引列进行计算、函数操作或类型转换

若在 WHERE 子句中对索引列使用了函数（如 DATE(), UPPER()）或进行了算术运算，MySQL 将无法直接使用索引。此外，若索引列是字符串类型，而查询时传入的是数字（或反之），MySQL 会进行隐式的类型转换，这同样会导致索引失效。

4. 范围条件右侧的列索引失效

在联合索引中，如果中间某列出现了范围查询（如 >, <, BETWEEN），则该列右侧的所有列都无法再使用索引进行精确查找。

对比实验：

-- 创建联合索引 idx_age_classid_name

EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE age = 30 AND classId > 20 AND name = 'abc';

结果分析：该查询使用了 age 和 classId，但 name 列为 type: ALL（全表扫描），因为 classId > 20 是范围查询，导致其后的 name 索引失效。

-- 创建新的联合索引 idx_age_name_classid

EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE age = 30 AND classId > 20 AND name = 'abc';

结果分析：调整顺序后，age（等值）、name（等值）均能被有效利用，仅 classId 用于范围过滤。这说明在设计联合索引时，应将等值查询列放在前面，范围查询列放在后面，以最大化索引覆盖范围。

5. 使用 != 或 <> (不等于) 导致索引失效

虽然某些情况下优化器仍可能走索引，但在多数统计场景中，不等于操作返回的数据比例较大，优化器往往认为全表扫描的成本更低，从而放弃索引。

6. IS NULL 可用，IS NOT NULL 常失效

IS NULL 通常可以使用索引。但 IS NOT NULL 在很多情况下会导致索引失效，因为优化器倾向于认为过滤掉大量数据的全表扫描比回表查询更高效。具体效果取决于数据分布情况。

7. LIKE 以通配符 % 开头导致索引失效

Alibaba《Java开发手册》强制规定：页面搜索严禁左模糊或全模糊查询。若使用 LIKE '%abc' 或 LIKE '%abc%'，MySQL 无法利用 B+ 树的有序性进行定位，从而导致索引失效，退化为全表扫描。只有 LIKE 'abc%'（右模糊）才能有效使用索引。

8. OR 前后存在非索引列导致整体失效

如果一条 SQL 语句中包含 OR 条件，且 OR 连接的两个条件中，只要有一个字段没有索引，MySQL 优化器通常会放弃对整个结果的索引检索，转而进行全表扫描。为确保性能，应确保 OR 两侧的条件均有索引。

9. 字符集不统一导致隐式转换失效

若表的字符集与查询条件的字符集不一致（例如表为 utf8mb4，而连接层为 gbk），MySQL 会在比较前进行隐式字符集转换。这种转换发生在索引列上，会导致索引失效。建议数据库、表及客户端连接均统一使用 utf8mb4（MySQL 5.5.3+ 支持），以避免此类问题。

总结

避免索引失效的核心在于理解 B+ 树的查找逻辑与 MySQL 优化器的决策机制。在编写 SQL 时，应遵循最左前缀原则，避免在索引列上进行计算或函数操作，谨慎使用范围和不等于操作，并统一字符集设置。以上就是MySQL索引失效的常见九种原因的详细内容，更多关于MySQL查询优化与索引设计的资料请关注本站其它相关文章！