MySQL 索引失效

索引失效的核心原理是：MySQL 优化器决定不使用索引，而选择全表扫描通常是因为它认为使用索引的成本更高，或者索引本身的结构导致无法被有效利用。

---

索引失效的常见场景

1. 对索引列进行了运算或函数操作

这是最常见的情况之一。当你在 WHERE条件中对索引列使用函数或表达式时，MySQL 无法直接利用索引树的结构进行查找，因为它需要对每一行数据都先计算函数或表达式的结果，然后再做比较。

示例：

-- 假设 create_time 字段有索引
-- ❌ 失效：使用了 DATE_FORMAT 函数
SELECT * FROM users WHERE DATE_FORMAT(create_time, '%Y-%m-%d') = '2023-10-01';

-- ✅ 有效：使用范围查询
SELECT * FROM users WHERE create_time >= '2023-10-01 00:00:00' AND create_time < '2023-10-02 00:00:00';

原因： 索引中存储的是 create_time的原始值，而不是 DATE_FORMAT(create_time, ...)的结果。优化器无法在索引树中快速定位。

2. 在索引列上使用 NOT (!=, <>)或 NOT IN

NOT操作本质上是一个范围查询，它需要查找所有“不等于”某个值的记录。在大多数情况下，如果“不等于”的值的选择性不高（即匹配大部分数据），全表扫描会比在索引中遍历所有“不相等”的条目更高效。

示例：

-- 假设 status 字段有索引，且大部分记录 status=1
-- ❌ 可能失效：因为要返回绝大部分数据，优化器认为全表扫描更快
SELECT * FROM orders WHERE status != 1;

-- ✅ 有效：如果 status=2 的记录很少，索引可能生效
SELECT * FROM orders WHERE status = 2;

原因： 优化器基于成本估算。当 NOT条件匹配的数据量过大时，使用索引的回表成本会超过全表扫描。

3. 隐式类型转换

当查询条件中的数据类型与索引列定义的数据类型不匹配时，MySQL 会发生隐式类型转换，这相当于在索引列上使用了函数。

示例：

-- 假设 user_id 是字符串类型（varchar），但有索引
-- ❌ 失效：数字 123 被隐式转换为字符串，相当于在 user_id 上使用了函数
SELECT * FROM users WHERE user_id = 123;

-- ✅ 有效：类型匹配
SELECT * FROM users WHERE user_id = '123';

原因： user_id是字符串，而条件 = 123是数字。MySQL 必须将表中每一行的 user_id转换为数字来比较，相当于 CAST(user_id AS SIGNED) = 123，导致索引失效。

4. 隐式编码转换

在多表关联时，如果关联字段的字符集（charset）或排序规则（collation）不同，MySQL 需要进行编码转换，也会导致索引失效。

原因： 类似于类型转换，这相当于在字段上使用了 CONVERT函数。

5. 使用 LIKE以通配符 %开头

LIKE查询可以利用索引，但前提是模式不能以通配符开头。

示例：

-- 假设 name 字段有索引
-- ❌ 失效：以 % 开头，无法利用索引的排序性
SELECT * FROM users WHERE name LIKE '%小明';

-- ✅ 有效：前缀匹配，可以走索引范围扫描
SELECT * FROM users WHERE name LIKE '小明%';

-- ❓ 可能失效（通常全表扫描更快）：前后都有 %
SELECT * FROM users WHERE name LIKE '%小明%';

原因： 索引的 B+Tree 是按照字段值从左到右排序的。LIKE ‘小明%’可以定位到以“小明”开头的索引项。而 LIKE ‘%小明’无法利用这种排序性，必须遍历所有索引项（即全索引扫描，如果覆盖索引可能使用）或全表扫描。

6. 复合索引未使用最左前缀法则

对于复合索引（多列索引），索引的键值是按创建索引时的列顺序排序的。查询必须从索引的最左列开始，并且不能跳过中间的列。

示例：

假设有一个复合索引 INDEX idx_a_b_c (a, b, c)。

-- ✅ 有效：使用了最左列 a
SELECT * FROM table WHERE a = 1;
SELECT * FROM table WHERE a = 1 AND b = 2;
SELECT * FROM table WHERE a = 1 AND b = 2 AND c = 3;

-- ✅ 有效：使用了最左列 a，c 作用于索引筛选，但 b 断了，只能用到 a
SELECT * FROM table WHERE a = 1 AND c = 3;

-- ❌ 失效：未使用最左列 a
SELECT * FROM table WHERE b = 2;
SELECT * FROM table WHERE b = 2 AND c = 3;

-- ❌ 失效：跳过了中间的列 b（a 和 c 只能用到 a，c 用于排序而非查询）
SELECT * FROM table WHERE a = 1 AND c = 3 ORDER BY c;

原因： 索引的排序规则是先按 a排，再按 b排，最后按 c排。没有 a，就无法在索引树中快速定位。

7. 使用 OR连接条件，且部分条件列无索引

如果 OR连接的多个条件中，有一个列没有索引，那么 MySQL 通常会放弃使用索引，因为即使对有索引的列使用索引，它最终还是要回表去检查另一个无索引列的条件，成本很高。

示例：

-- 假设 name 有索引，但 age 没有索引
-- ❌ 失效：因为 age 无索引，优化器会选择全表扫描
SELECT * FROM users WHERE name = '张三' OR age = 18;

-- 解决方案：使用 UNION 改写
SELECT * FROM users WHERE name = '张三'
UNION
SELECT * FROM users WHERE age = 18;
-- 注意：UNION 会去重，如果确定无重复或允许重复，可用 UNION ALL 提高效率。

原因： 优化器认为分别通过索引查找 name和全表扫描 age，然后合并结果的成本高于直接全表扫描一次。

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

在 MySQL 中，IS NULL通常可以使用索引，但 IS NOT NULL可能不会。

示例：

-- 假设 score 字段有索引，且允许为 NULL
-- ✅ 通常有效：可以使用索引
SELECT * FROM students WHERE score IS NULL;

-- ❌ 可能失效：如果非 NULL 数据占绝大部分，优化器倾向于全表扫描
SELECT * FROM students WHERE score IS NOT NULL;

原因： 同样是基于成本估算。如果 NOT NULL的数据非常多，回表成本高，优化器会选择全表扫描。

9. 优化器估算全表扫描比使用索引更快

这是最终的决定因素。优化器是基于成本的（Cost-Based Optimizer, CBO）。即使你的查询满足了所有能用索引的理论条件，但如果表中数据量很小，或者索引的选择性非常差（例如，在“性别”字段上建索引，只有‘男’，‘女’两个值），优化器会认为直接读取整个数据页（全表扫描）比通过索引树查找再回表要快。

示例：

-- 假设 gender 字段有索引（值只有 ‘M’， ‘F’）
-- ❌ 可能失效：即使查询了 ‘M’，但 ‘M’ 占了约 50% 的数据，全表扫描更快
SELECT * FROM users WHERE gender = 'M';

原因： 随机 I/O（回表）的成本远高于顺序 I/O（全表扫描）。当需要回表的数据量超过全表的 ~20%（这个比例是经验值，会随具体情况变化）时，优化器可能选择全表扫描。

---

如何诊断索引是否失效？

使用 EXPLAIN命令是诊断索引使用情况的最佳工具。

EXPLAIN SELECT * FROM users WHERE name LIKE '%小明';

查看结果中的 key字段。如果为 NULL，则表示未使用索引。同时关注 type字段：

• const、eq_ref、ref、range：表示索引使用良好。
• index：全索引扫描（比全表扫描好，但不理想）。
• ALL：全表扫描，索引失效或未使用索引。

总结与最佳实践

1. 保持列“干净”：避免在索引列上使用函数、计算或类型转换。
2. 理解复合索引：设计索引时，考虑查询的 WHERE、ORDER BY、GROUP BY子句，遵循最左前缀原则。
3. 谨慎使用 OR：尽量用 UNION或 UNION ALL改写。
4. 考虑索引的选择性：在选择性高的列上创建索引（例如用户ID、手机号），避免在低选择性列（如状态、类型）上创建索引，除非经常与其他高选择性列组合查询。
5. 善用覆盖索引：如果查询的列全部包含在索引中（即覆盖索引），即使 WHERE条件不理想，MySQL 也可能使用“索引扫描”来避免回表，这比全表扫描快。
6. 永远使用 EXPLAIN验证：不要猜测，通过 EXPLAIN分析你的关键查询。

希望这份详细的总结能帮助你更好地理解和避免 MySQL 索引失效的问题！