索引失效的常见场景

一、索引失效的常见场景

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

• 场景：在 WHERE 条件中对索引列使用函数、表达式或类型转换。

• 示例：

  -- 索引失效（对索引列 date_col 使用函数）

  SELECT * FROM orders WHERE YEAR(date_col) = 2023;

  -- 索引失效（隐式类型转换）

  SELECT * FROM users WHERE phone = 13800138000; -- phone 是 VARCHAR 类型

• 解决方案：改写为直接使用索引列原始值

  • SELECT * FROM orders WHERE date_col BETWEEN '2023-01-01' AND '2023-12-31';

  • SELECT * FROM users WHERE phone = '13800138000';

2. 使用前导通配符的 LIKE 查询

• 场景：以 % 或 _ 开头的模糊匹配。

• 示例：

  -- 索引失效（前导通配符）

  SELECT * FROM products WHERE name LIKE '%手机%';

• 解决方案：

  • 避免前导通配符，改为后缀匹配（如 '手机%'）。

  • 使用全文索引优化模糊查询。

3. OR 条件导致索引合并失败

• 场景：OR 连接的条件中部分列无索引。

• 示例：

  -- 假设仅 user_id 有索引，status 无索引

  SELECT * FROM logs WHERE user_id = 1001 OR status = 'error';

• 解决方案：

  • 为 status 添加索引。

  • 拆分为 UNION 查询：

    SELECT * FROM logs WHERE user_id = 1001

    UNION

    SELECT * FROM logs WHERE status = 'error';

4. 复合索引未遵循最左前缀原则

• 场景：复合索引列顺序为 (A, B, C)，但查询未使用最左列。

• 示例：

  -- 索引 (a, b, c) 失效

  SELECT * FROM table WHERE b = 2 AND c = 3;

• 解决方案：

  • 调整查询条件顺序，包含最左列：（按查询频率设计复合索引顺序。）

  SELECT * FROM table WHERE a = 1 AND b = 2 AND c = 3;

5. 数据分布不均导致优化器弃用索引

• 场景：索引列值重复率过高（如性别列）。

• 示例：

  -- 假设 90% 的 status 值为 'active'

  SELECT * FROM tasks WHERE status = 'active';

• 解决方案：

  • 删除低选择性索引，避免浪费资源。

  • 结合其他高选择性列创建复合索引。

6. 使用范围查询后无法使用后续索引列

• 场景：复合索引中，范围查询（如 >、<）后的索引列失效。

• 示例：

  -- 复合索引 (age, salary)

  SELECT * FROM employees WHERE age > 30 AND salary = 10000;

  -- 仅 age 生效，salary 无法利用索引

• 解决方案：

  • 调整复合索引顺序为 (salary, age)，若业务允许。

  • 拆分查询或使用覆盖索引。

7. 使用 NOT、!= 或 <> 操作符

• 场景：否定条件导致全表扫描。

• 示例：

  SELECT * FROM orders WHERE status != 'completed';

• 解决方案：

  • 改写为正向条件（如 status IN ('pending', 'processing')）。

  • 结合其他条件缩小范围。

二、验证索引是否失效的方法

1. 查看执行计划

• MySQL：EXPLAIN SELECT ...

• Oracle：EXPLAIN PLAN FOR SELECT ...

• 关键字段：

  • type：index 表示使用索引，ALL 表示全表扫描。

  • key：实际使用的索引名称。

  • rows：扫描行数（越小越好）。

2. 监控慢查询日志

• 开启数据库慢查询日志，分析未走索引的 SQL。

---

三、索引设计最佳实践

1. 选择性原则：优先为高选择性（唯一值多）的列建索引。

2. 覆盖索引：通过 INCLUDE 或复合索引包含查询所需字段，避免回表。

3. 定期维护：重建碎片化索引，更新统计信息。

4. 避免过度索引：权衡读写性能，删除冗余索引。

四、慢查询优化

• EXPLAIN 分析执行计划。

• 慢查询日志（slow_query_log）定位低效 SQL

---

总结

索引失效常见于运算操作、模糊查询、OR 条件、复合索引顺序等问题。通过优化 SQL 写法、调整索引设计、分析执行计划，可有效避免性能瓶颈。实际开发中需结合业务场景和数据分布灵活调整策略。