MySQL 索引失效的常见场景

MySQL 索引失效的常见场景

目标：知道什么时候本来可以用索引但没用上，并学会避免。

---

一、先说一句前提

MySQL 有没有用到索引，不要靠猜，用：

EXPLAIN SELECT ...;

看 type、key、rows 等字段，才是判断是否“走索引”的标准。
下面这些都是高概率导致索引失效或索引利用不佳的场景。

---

二、常见索引失效场景

1. 没有满足最左前缀原则

场景：

CREATE INDEX idx_user_status_time ON t_order(user_id, status, create_time);

以下 SQL 使用情况：

-- ✅ 能用上 idx_user_status_time（用到 user_id）
SELECT * FROM t_order
WHERE user_id = 1;

-- ✅ 能用上 idx_user_status_time（用到 user_id, status）
SELECT * FROM t_order
WHERE user_id = 1 AND status = 0;

-- ✅ 能用上 idx_user_status_time（user_id + status + 范围 create_time）
SELECT * FROM t_order
WHERE user_id = 1 AND status = 0 AND create_time > '2025-01-01';

-- ❌ 很可能用不上 idx_user_status_time（跳过了最左 user_id）
SELECT * FROM t_order
WHERE status = 0;

-- ❌ 很可能用不上 idx_user_status_time（WHERE 里没有 user_id）
SELECT * FROM t_order
WHERE status = 0 AND create_time > '2025-01-01';

**原因：**联合索引是按 (user_id, status, create_time) 的顺序排的，
必须从最左边开始连续使用，否则这个索引就相当于废了。

优化建议：

• 设计联合索引时按照常用查询条件顺序来排；
• WHERE 条件尽量包含联合索引的最左列。

---

2. 在索引列上使用函数或表达式

场景：

-- create_time 上有普通索引 idx_create_time

-- ❌ 索引往往失效
SELECT * FROM t_order
WHERE DATE(create_time) = '2025-01-01';

-- ❌ 索引往往失效
SELECT * FROM t_order
WHERE YEAR(create_time) = 2025;

-- ❌ 索引往往失效
SELECT * FROM t_user
WHERE LEFT(name, 3) = 'Tom';

原因：

• 索引是基于列原始值建立的；
• 对列使用函数/表达式后，MySQL 无法利用已有 B+Tree 的顺序，只好全表扫描。

正确写法示例：

-- ✅ 转换为范围查询，索引可用
SELECT * FROM t_order
WHERE create_time >= '2025-01-01 00:00:00'
  AND create_time < '2025-01-02 00:00:00';

优化建议：

• 避免对索引列做函数、计算、表达式；
• 优先将函数挪到常量一侧，写成范围条件。

---

3. 在索引列上进行运算（+、-、*、/ 等）

场景：

-- age 有索引

-- ❌ 索引基本失效
SELECT * FROM t_user
WHERE age + 1 = 30;

-- ❌ 索引基本失效
SELECT * FROM t_user
WHERE salary * 12 > 100000;

原因：

• 和使用函数类似，索引是建立在列的原值上的，运算改变了表达式形态。

优化建议：

• 改写为：

-- ✅ 索引可用
SELECT * FROM t_user
WHERE age = 29;

-- ✅ 索引可用（能改则改，尽量写到常量这边）
SELECT * FROM t_user
WHERE salary > 100000 / 12;

---

4. 隐式类型转换导致索引失效

场景：

-- mobile 字段类型为 VARCHAR，有索引 idx_mobile

-- ❌ 字面量是数字，可能触发隐式转换
SELECT * FROM t_user
WHERE mobile = 13800138000;

MySQL 会在内部做类型转换：

• 可能会把 mobile 转成数字后比较；
• 一旦对列做转换，就和函数一样，会破坏索引利用。

正确写法：

-- ✅ 使用字符串字面量
SELECT * FROM t_user
WHERE mobile = '13800138000';

反过来的情况也可能出现：

-- id 是 INT，有主键索引

-- ✅ 索引可用
SELECT * FROM t_user WHERE id = 123;

-- ❌ 不推荐这样写（虽然很多情况下优化器能兜底）
SELECT * FROM t_user WHERE id = '123';

**建议：**字段什么类型，就用什么类型的常量，不要让 MySQL 帮你“猜”。

---

5. 前置模糊匹配：LIKE ‘%xxx’ 或 ‘%xxx%’

场景：

-- name 上有索引

-- ✅ 可能利用索引（前缀匹配）
SELECT * FROM t_user
WHERE name LIKE 'Tom%';

-- ❌ 索引基本失效（前面有 %）
SELECT * FROM t_user
WHERE name LIKE '%Tom';

-- ❌ 索引基本失效（前后都有 %）
SELECT * FROM t_user
WHERE name LIKE '%Tom%';

原因：

• B+Tree 索引按从左到右的前缀有序；
• LIKE 'Tom%' 可以转化为 'Tom' <= name < 'Ton' 范围；
• LIKE '%Tom' 前缀不确定，无法利用树的前缀有序性。

优化建议：

• 尽量使用前缀匹配 LIKE 'xxx%'；
• 真正需要任意位置模糊查询：
  • 考虑 全文索引 FULLTEXT；
  • 或者借助搜索引擎（ES 等）。

---

6. 不等于、NOT IN、NOT LIKE 等“反向条件”

场景：

-- status 上有索引

-- ❌ 一般情况下索引利用度很低
SELECT * FROM t_order
WHERE status <> 1;

-- ❌ 一般情况下索引利用度较差
SELECT * FROM t_order
WHERE status NOT IN (1, 2);

-- ❌ 一般情况下索引利用度较差
SELECT * FROM t_user
WHERE name NOT LIKE 'Tom%';

原因：

• 当条件表示“绝大部分值”时（比如 <> 1），
  使用索引反而比全表扫描成本更高，优化器通常会放弃索引。

备注：
优化器会根据统计信息估算成本，某些极端情况下仍可能部分使用索引，具体要看 EXPLAIN。

建议：

• 能用“正向条件”表示，就尽量不用 NOT。

---

7. OR 条件导致索引失效

场景：

-- idx_a(a)、idx_b(b) 各自都有单列索引

-- ❌ 可能导致索引失效
SELECT * FROM t_table
WHERE a = 1 OR b = 2;

在很多版本和场景下：

• 如果 OR 两边使用的是不同的字段，MySQL 可能选择不使用索引，直接全表扫描；
• 新版本可能会做索引合并（index_merge），但性能未必理想。

优化建议：

• 能拆成 UNION 的可以拆：

SELECT * FROM t_table WHERE a = 1
UNION
SELECT * FROM t_table WHERE b = 2;

• 或者用 IN 重写（视业务场景而定）；
• 或建立合适的联合索引。

---

8. 复合索引中，范围查询后面的列失效

场景：

CREATE INDEX idx_a_b_c ON t_table(a, b, c);

-- a 精确匹配，b 是范围，c 精确匹配
SELECT * FROM t_table
WHERE a = 1 AND b > 10 AND c = 5;

通常情况：

• 索引可以利用 (a, b) 这两列；
• c 由于位于范围列 b 之后，往往无法继续利用这个联合索引；
• 实际执行可能是：利用索引找到满足 a = 1 AND b > 10 的行，再对 c 进行过滤。

原因：

• 对 B+Tree 来说，一旦中间列是范围条件，后面的列就难以继续利用“有序性”；
• 索引的有序性被范围打断。

优化建议：

• 根据查询场景调整索引顺序；
• 尽量把等值匹配列放在联合索引前面。

---

9. 低选择性字段上的索引可能被放弃

场景：

-- is_deleted 只有 0/1 两种值

CREATE INDEX idx_deleted ON t_order(is_deleted);

-- ❌ 很可能直接全表扫描
SELECT * FROM t_order
WHERE is_deleted = 0;

原因：

• 索引列的区分度太低（不同值太少，数据太集中）；
• 用索引需要先“遍历大量索引项 + 回表”，成本反而更高；
• 优化器会估算“需要访问的行数”，如果发现绝大多数行都要访问，就可能放弃索引。

优化建议：

• 低选择性字段单独索引意义不大；
• 常见做法是与其它字段组合建立联合索引：

CREATE INDEX idx_deleted_status ON t_order(is_deleted, status);

---

10. ORDER BY / GROUP BY 与索引不匹配

场景：

CREATE INDEX idx_a_b ON t_table(a, b);

-- ✅ 可以使用 idx_a_b 完成排序
SELECT * FROM t_table
WHERE a = 1
ORDER BY b;

-- ❌ 可能无法利用 idx_a_b 做排序
SELECT * FROM t_table
WHERE a > 1
ORDER BY b;

-- ❌ ORDER BY 字段与索引顺序不一致
SELECT * FROM t_table
ORDER BY b, a;

原因：

• ORDER BY 想利用索引，要求：
  • WHERE 条件 & ORDER BY 顺序要与索引定义匹配；
  • 且中间不能出现“破坏有序性”的条件（如范围）；
• 一旦不满足，MySQL 需要额外排序（filesort），可能放弃索引。

优化建议：

• 设计索引时兼顾 WHERE 和 ORDER BY；
• 尽量遵守“按索引顺序排序”。

---

11. 使用不合适的字符集/排序规则导致索引利用异常

场景：

• 表和连接使用的字符集不一致；
• 或排序规则（collation）不一致。

可能导致：

• MySQL 在比较时需要做字符集/排序规则转换；
• 实际上也是一种“对列做函数处理”，从而使索引利用不佳。

建议：

• 表、库、连接的字符集/排序规则尽量保持一致；
• 避免无谓的字符串转换。

---

12. 统计信息不准确导致优化器选错索引

场景：

• 索引本身是好的；
• SQL 也写得没问题；
• 但执行计划（EXPLAIN）显示走了一个“奇怪的索引”甚至不走索引。

可能原因：

• 表数据变化较大，统计信息未更新；
• 估算行数严重偏差，导致优化器误判。

解决方式：

• 对表进行 ANALYZE TABLE，更新统计信息；
• 必要时调整或删除干扰性的索引；
• 极端情况下可以使用 FORCE INDEX 强制指定索引使用（慎用）。

---

三、排查与优化套路

1. 怀疑索引失效时，第一步一定是：

   EXPLAIN SELECT ...;
   

2. 关注几个关键字段：

   • type：访问类型（ALL、index、range、ref、const 等）；
   • key：实际使用的索引；
   • rows：预估扫描行数；
   • extra：是否有 Using index、Using where、Using filesort 等。

3. 遇到问题时，从以下几个点排查：

   • 有没有对索引列使用函数 / 运算 / 隐式类型转换；
   • 联合索引是否符合最左前缀原则；
   • WHERE 条件是否高选择性；
   • ORDER BY / GROUP BY 是否与索引顺序匹配；
   • 是否使用了 OR / NOT / %xxx 这类索引不友好的写法。

---

四、总结

• 索引失效往往不是“没建索引”，而是：
  • SQL 写法破坏了索引的可用性；
  • 条件本身不适合走索引；
  • 或优化器基于成本选择了全表扫描。
• 关键是理解：B+Tree 的前缀有序性，并且：
  • 遵守最左前缀原则；
  • 避免在索引列上乱用函数 / 运算 / 隐式转换；
  • 谨慎对待 OR、NOT、低选择性列；
  • 使用 EXPLAIN + 统计信息来验证和调整。

掌握这些常见“坑位”，基本就能避免绝大部分索引失效问题。