数据库索引失效的常见场景

1. 不符合最左前缀原则（针对复合索引）

复合索引：如创建了 (A, B, C) 的联合索引

失效情况：

• 查询条件中不包含最左列 A（如只查 B=1 或 C=1）
• 跳过了中间列（如 A=1 AND C=1 跳过 B）

失效原因：

• B+树索引的有序性是基于索引定义的列顺序
• 缺少最左列时，后续列的排序是局部的、不连续的
• 例如索引(A,B,C)，没有A时B和C在物理存储上是无序的

优化器视角：

• 无法利用索引的有序性进行二分查找
• 需要扫描所有索引条目 ≈ 全索引扫描

2. 对索引列使用函数或运算

失效情况：

-- 失效示例
SELECT * FROM users WHERE YEAR(create_time) = 2023;  -- 对索引列使用函数
SELECT * FROM products WHERE price + 10 > 100;       -- 对索引列进行运算

失效原因：

• 需要对每行数据都先计算函数值再比较
• 计算成本 + 无法利用索引有序性 → 不如全表扫描

优化器视角：

• 需要对每行数据都先计算函数值再比较
• 计算成本 + 无法利用索引有序性 → 不如全表扫描

3. 使用不等于操作符（!= 或 <>）

失效情况：

-- 通常会使索引失效
SELECT * FROM customers WHERE status != 'active';

失效原因：

• B+树索引擅长定位特定值和范围查询
• !=本质上是两个范围查询的并集：< value OR > value

优化器视角：

• 需要访问索引的多个不连续区间
• 可能覆盖大部分数据页 → 不如顺序扫描

4. 使用 NOT IN、NOT LIKE、NOT EXISTS

失效情况：

-- 索引可能失效
SELECT * FROM employees WHERE dept_id NOT IN (1, 2, 3);
SELECT * FROM products WHERE name NOT LIKE 'Apple%';

失效原因：

• NOT IN (1,2,3) 需要执行三个独立排除操作：≠1 AND ≠2 AND ≠3，索引无法高效合并多个不相交的排除范围
• NOT LIKE 'Apple%' 需要先定位所有 Apple% 记录，然后反向选择剩余记录，但"非前缀匹配"的记录在物理存储上是分散的，当于要扫描索引的多个不连续区间
• NOT EXISTS 子查询执行成本高

5. 使用 OR 连接条件（除非所有列都有索引）

失效情况：

-- 若age或name中有一个无索引，则索引失效
SELECT * FROM users WHERE age > ## 30 OR name = 'John';

失效原因：

• 普通B+树索引不支持多列的OR条件合并
• 除非所有OR条件列都有独立索引(可能触发index merge)

优化器视角：

• 需要分别查找多个条件然后合并结果
• 合并操作可能产生大量临时数据

6. 隐式类型转换

失效情况：

-- 假设user_id是字符串类型，但用数字查询
SELECT * FROM users WHERE user_id = ## 123;  -- 应使用 '123'

失效原因：

• 索引存储的是定义的数据类型
• 类型转换相当于对每行数据应用函数

优化器视角：

WHERE string_column = 123 
→ 
WHERE CAST(string_column AS INT) = 123

需要逐行转换类型 → 函数计算成本

7. 使用 LIKE 以通配符开头

失效情况：

-- 索引失效
SELECT * FROM articles WHERE title LIKE '%数据库%';
-- 可以使用索引（当使用前缀匹配时）
SELECT * FROM articles WHERE title LIKE '数据库%';

失效原因：

• 字符串索引按字典序存储
• LIKE '%xxx'破坏了字符串的前缀顺序性

优化器视角：

• 无法利用索引的有序性
• 相当于对每行字符串做模式匹配

8. 数据分布不均（优化器认为全表扫描更快）

失效情况：

• 当索引列的值分布非常不均匀（如90%数据都满足条件）
• 当查询需要返回表中大部分数据时

失效原因：

• 优化器依赖统计信息(直方图、基数等)
• 当索引列值高度倾斜时统计可能不准确

优化器视角：

• 例如查status=1(占90%数据)
• 使用索引需要大量随机I/O
• 顺序扫描可能更高效

9. 索引列参与表达式比较

失效情况：

-- 索引失效
SELECT * FROM orders WHERE amount / 2 > 500;
-- 可优化为
SELECT * FROM orders WHERE amount > 1000;

失效原因：

• 索引存储的是amount的原始值，不是amount/2的计算结果
• B+树索引无法根据amount/2 > 500这个条件直接定位数据范围
• 必须对每条记录的amount执行除法运算后才能比较

优化器视角：

• 无法利用索引的有序性进行快速范围定位
• 需要将条件转换为等价的列原始值比较：

10. 索引统计信息过时

• 当表数据发生大量变化后，索引统计信息未及时更新
• 可能导致优化器做出错误决策

11. 使用 IS NULL 或 IS NOT NULL（取决于数据库实现）

失效情况：

-- 某些数据库中索引可能失效
SELECT * FROM employees WHERE manager_id IS NULL;

12. 索引失效的本质

索引失效的本质是数据库查询优化器认为"使用索引比全表扫描效率更低"，因此放弃使用索引。这背后有一系列技术原理和决策机制。

最根本的原因是优化器的成本计算(Cost-Based Optimization)， 数据库优化器会根据统计信息计算各种执行计划的成本，当它判定： 使用索引的成本 > 全表扫描的成本 时，就会导致索引失效。

这个决策基于：

1. I/O成本（磁盘读取量）
2. CPU成本（计算复杂度）
3. 内存使用
4. 结果集大小

13. 避免索引失效的建议

• 使用 EXPLAIN 分析查询执行计划
• 避免在索引列上使用函数或计算
• 考虑使用覆盖索引（查询只包含索引列）
• 定期更新统计信息（如 ANALYZE TABLE）
• 对于复杂查询，考虑使用提示（hint）强制使用索引
• 理解这些索引失效场景可以帮助开发者编写更高效的sql查询，充分发挥索引的性能优势。