MySQL 索引失效：六大场景与原理剖析

---

我们都熟知索引是优化 MySQL 查询性能的利器。但你是否遇到过这样的困境：明明在表上建立了索引，查询却依然缓慢，EXPLAIN 分析后发现索引并未被使用？这就是所谓的“索引失效”。

索引失效并非一个 Bug，而是 MySQL 查询优化器 (Query Optimizer) 基于成本模型 (Cost Model) 作出的“理性”选择。它认为全表扫描 (Full Table Scan) 比使用索引的成本更低。

本文将深入数据库的底层，为你揭示索引失效背后的秘密，探讨常见的失效场景，剖析其根本原因，并提供切实可行的诊断与优化方案。

---

一、索引工作的核心基石：B+ 树与成本优化器

在探讨“失效”之前，我们必须先理解索引是如何“生效”的。这主要依赖于两个核心概念：

1. 数据结构：有序的 B+ 树

InnoDB 存储引擎使用 B+ 树作为索引的数据结构。其核心特点是：所有数据（或数据指针）都存储在叶子节点上，并且叶子节点之间通过双向指针连接，形成一个有序链表。

这种有序性是索引能够高效查询的关键。无论是等值查询、范围查询还是排序，MySQL 都可以利用 B+ 树的有序性，像查字典一样快速定位，避免了遍历整张表的开销。

2. 决策大脑：查询优化器 (CBO)

MySQL 使用的是基于成本的优化器 (Cost-Based Optimizer, CBO)。当一条 SQL 到达时，CBO 会生成多种可能的执行计划（例如，是走索引 A，还是索引 B，或是全表扫描），然后为每种计划估算一个执行成本，最后选择成本最低的那个去执行。

这个成本主要由两部分构成：

• I/O 成本: 从磁盘读取数据页到内存的成本。这是成本的主要部分。
• CPU 成本: 在内存中对数据进行比较、排序、计算等操作的成本。

索引失效的本质，就是 CBO 经过计算后，认为“走索引”的成本超过了“全表扫描”的成本。 我们的所有分析，都将围绕这个核心展开。

---

二、常见的索引失效场景及其底层原理

现在，让我们结合具体的 SQL 例子，剖析那些让 CBO“放弃”索引的典型场景。

场景 1：在索引列上进行函数或运算操作

这是最经典的失效场景。

SQL 示例:

-- `create_time` 列上有索引
-- 失效场景：对索引列使用函数
SELECT * FROM orders WHERE YEAR(create_time) = 2025;-- 失效场景：对索引列进行运算
SELECT * FROM user WHERE age + 10 = 30;

底层原因:
B+ 树中存储的是 create_time 或 age 的原始值，并且这些值是排好序的。当你在查询中对列使用了函数 YEAR() 或运算 + 10，MySQL 无法直接使用索引中的原始值去进行匹配。

为了执行查询，MySQL 必须对表中的每一行数据都应用这个函数或运算，然后再将结果与目标值进行比较。这个过程无法利用 B+ Tee 的快速查找能力，其成本等同于全表扫描。因此，CBO 会果断放弃索引。

优化建议:
始终保持索引列的“纯净”。将计算操作移到等号右边。

-- 优化后：将函数运算移到值的身上
SELECT * FROM orders WHERE create_time BETWEEN '2025-01-01 00:00:00' AND '2025-12-31 23:59:59';SELECT * FROM user WHERE age = 20; -- 30 - 10

---

场景 2：LIKE 查询以通配符 % 开头

模糊查询是常见需求，但错误的用法会导致索引形同虚设。

SQL 示例:

-- `username` 列上有索引
-- 生效场景
SELECT * FROM user WHERE username LIKE 'admin%';-- 失效场景
SELECT * FROM user WHERE username LIKE '%admin';

底层原因:
再次回到 B+ 树的有序性。索引是按字母顺序排列的。

• 'admin%'：前缀是确定的，CBO 可以利用索引定位到以 ‘admin’ 开头的第一个节点，然后向后顺序扫描，直到不匹配为止。这个范围是确定的，效率很高。
• '%admin'：前缀是不确定的，MySQL 不知道从哪里开始查找。它无法利用 B+ 树的有序性，只能退化为全表扫描，逐一检查每个 username 是否以 ‘admin’ 结尾。

优化建议:

• 尽可能避免前缀模糊查询。
• 如果业务上无法避免，可以考虑使用全文索引 (Full-Text Index) 或引入外部搜索引擎如 Elasticsearch。

---

场景 3：隐式类型转换

这是一个非常隐蔽的“杀手”，尤其容易在数字和字符串类型之间发生。

SQL 示例:

-- `phone` 列是 VARCHAR(20) 类型，并建有索引
-- 失效场景：传入的值是数字，导致类型不匹配
SELECT * FROM user WHERE phone = 13800138000;

底层原因:
MySQL 的规则是，当字符串和数字进行比较时，会将字符串转换为数字。因此，上述查询在 MySQL 内部实际上被转换成了：

-- MySQL 内部的隐式转换
SELECT * FROM user WHERE CAST(phone AS SIGNED) = 13800138000;

看，这又回到了场景 1 的问题——在索引列 phone 上应用了 CAST() 函数。结果自然是索引失效。

优化建议:
保证查询条件中的值类型与列定义类型完全一致。

-- 优化后：用字符串进行比较
SELECT * FROM user WHERE phone = '13800138000';

---

场景 4：违反复合索引的“最左前缀原则”

复合索引（或称联合索引）是提高多条件查询效率的利器，但使用不当则会失效。

SQL 示例:

-- 在 (name, age, position) 上建立复合索引
CREATE INDEX idx_name_age_pos ON employees (name, age, position);-- 生效场景
SELECT * FROM employees WHERE name = 'Tom'; -- 遵守
SELECT * FROM employees WHERE name = 'Tom' AND age = 30; -- 遵守
SELECT * FROM employees WHERE name = 'Tom' AND age = 30 AND position = 'Manager'; -- 遵守-- 失效场景
SELECT * FROM employees WHERE age = 30; -- 未从最左侧开始，索引失效
SELECT * FROM employees WHERE position = 'Manager'; -- 未从最左侧开始，索引失效
SELECT * FROM employees WHERE name = 'Tom' AND position = 'Manager'; -- 跳过了中间的 age，只有 name 部分的索引生效

底层原因:
复合索引的 B+ 树结构是“多重排序”的。以上述 (name, age, position) 索引为例，其排序规则是：

1. 首先按 name 字段排序。
2. 如果 name 相同，则按 age 字段排序。
3. 如果 age 也相同，则按 position 字段排序。

当你直接查询 age = 30 时，由于 name 是不确定的，数据在 age 维度上是无序的，无法利用 B+ 树进行快速定位。必须从索引的最左列开始，并且不能跳过中间的列，索引才能被完整地利用。

优化建议:

• 严格按照复合索引的顺序设计和编写查询。
• 将最常用、选择性最高的列放在复合索引的最左侧。

---

场景 5：OR 条件的使用

SQL 示例:

-- `user_id` 是主键索引, `email` 是普通索引
-- 失效场景：OR 的一边没有索引
SELECT * FROM user WHERE user_id = 10 OR email = '[e-mail]';

在旧版的 MySQL 中，OR 常常导致索引失效。但随着版本迭代，MySQL 引入了 索引合并 (Index Merge) 优化。如果 OR 两边的条件列都有索引，优化器可能会分别使用两个索引，然后将结果集合并。

但索引仍然可能失效，通常是因为：

• OR 的其中一个条件列没有索引：此时优化器无法对整个 OR 查询进行有效的索引操作，它会认为全表扫描后用 user_id = 10 OR email = '...' 进行过滤的成本更低。
• 优化器认为全表扫描更快：即使两边都有索引，如果优化器估算 OR 条件会返回大量数据（例如，status = 'active' OR age > 20），它可能会判断全表扫描比索引合并的成本（两次索引扫描 + 结果去重合并）更低。

优化建议:

• 确保 OR 两边的列都有索引。
• 如果业务允许，可以考虑将 OR 查询拆分成两个独立的查询，用 UNION ALL 合并。

  SELECT * FROM user WHERE user_id = 10
  UNION ALL
  SELECT * FROM user WHERE email = '[e-mail]' AND user_id != 10;
  

---

场景 6：范围查询或不等式（!=, <>)

SQL 示例:

-- `age` 列有索引
SELECT * FROM user WHERE age > 20;-- `status` 列有索引
SELECT * FROM user WHERE status != 'active';

底层原因:
这不是绝对的失效，而是“可能”失效，根本原因在于回表成本和选择性 (Selectivity)。

• 选择性：指索引列中不同值的比例。如果一个索引的选择性很差（例如 status 列只有 ‘active’ 和 ‘inactive’ 两个值），那么 status != 'active' 几乎会返回一半的数据。
• 回表成本：对于非覆盖索引，通过索引找到主键后，还需要根据主键去聚簇索引中查找完整的行数据，这个过程叫回表。

当优化器估算，一个范围查询或不等式查询需要扫描大量的索引条目，并且每次扫描后还需要进行大量的回表操作时，它会认为这个 I/O 成本总和超过了直接全表扫描的成本。全表扫描是一次顺序 I/O，而大量的回表是随机 I/O，后者通常更昂贵。

优化建议:

• 尽量避免使用 != 或 <>。
• 对于选择性差的列，不适合单独建立索引。
• 对于频繁的范围查询，可以考虑使用覆盖索引（查询的所有列都包含在索引中），以避免回表，从而大大降低成本。

---

三、诊断与优化：让索引“起死回生”

1. 神器 EXPLAIN

EXPLAIN 是诊断索引问题的首要工具。将它放在你的 SELECT 语句前执行，可以查看 MySQL 的执行计划。

EXPLAIN SELECT * FROM user WHERE age = 30;

重点关注以下几列：

• type: 连接类型。ALL 代表全表扫描，是性能最差的情况。理想值是 const, eq_ref, ref, range 等。
• possible_keys: 可能使用的索引。
• key: 实际使用的索引。如果为 NULL，则表示索引失效。
• rows: 估算需要扫描的行数。数值越小越好。
• Extra: 额外信息。Using filesort（需要额外排序）、Using temporary（使用了临时表）都是危险信号。Using index 是个好信号，表示使用了覆盖索引。

2. 更新统计信息

优化器依赖表的统计信息（如行数、索引的基数等）来做决策。如果数据表发生大量增删改，统计信息可能过时，导致优化器做出错误判断。

可以手动更新统计信息：

ANALYZE TABLE your_table_name;

3. 强制索引（谨慎使用）

如果你确信优化器的选择是错误的，可以使用 FORCE INDEX 来强制它使用某个索引。

SELECT * FROM employees FORCE INDEX (idx_name_age_pos) WHERE age = 30;

⚠️ 警告：这通常是最后的手段。它绕过了优化器的智能判断，可能在数据分布变化后导致性能问题。首选应该是优化查询或索引设计。

---

四、总结

索引失效并非玄学，而是 MySQL 查询优化器基于 B+ 树结构、数据统计信息和成本模型进行权衡后的理性选择。

为了让你的索引持续高效工作，你需要像优化器一样思考：

1. 保持索引列的纯粹：避免函数、运算和隐式转换。
2. 遵循索引的结构：利用好最左前缀原则和索引的有序性。
3. 降低回表成本：善用覆盖索引。
4. 相信但要验证：以 EXPLAIN 的结果为准绳，诊断并指导优化。

希望这篇博客可以帮助你理解索引失效的场景和原理。