SQL索引失效场景及优化策略

题目描述
索引是SQL数据库性能优化的核心手段，但错误的使用会导致索引失效，引发全表扫描，严重降低查询效率。面试官常通过索引失效场景考察你对索引原理和SQL编写规范的理解。本文将系统讲解索引失效的常见原因及优化策略。

索引失效的根本原因
索引失效的本质是数据库优化器认为使用索引的成本高于全表扫描。常见原因包括：

1. 对索引列进行运算或函数处理

   • 失效示例：WHERE YEAR(create_time) = 2023
   • 原理：索引存储的是原始值，对列计算后无法使用索引树定位
   • 优化：改写为范围查询

     -- 优化后
     WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01'
     

2. 隐式类型转换

   • 失效示例：WHERE user_id = '123'（user_id为整型）
   • 原理：数据库需要将整列转换为字符串再比较，相当于对索引列使用函数
   • 优化：确保数据类型一致

     WHERE user_id = 123  -- 整型直接匹配
     

3. 前导模糊查询

   • 失效示例：WHERE name LIKE '%张三'
   • 原理：B+树索引按前缀排序，无法定位以模糊字符开头的值
   • 优化方案：
     • 改用后置模糊查询：WHERE name LIKE '张三%'
     • 使用全文索引（如MySQL的FULLTEXT）

4. OR连接非索引列条件

   • 失效示例：WHERE indexed_column = 1 OR non_indexed_column = 2
   • 原理：优化器需要同时扫描索引和全表，通常直接选择全表扫描
   • 优化：

     -- 拆分为UNION查询
     SELECT * FROM t WHERE indexed_column = 1
     UNION
     SELECT * FROM t WHERE non_indexed_column = 2
     

5. 不符合最左前缀原则

   • 复合索引（a, b, c）失效场景：
     • 缺失左列：WHERE b = 1 AND c = 2
     • 跳列查询：WHERE a = 1 AND c = 2（只能用到a列索引）
   • 优化：调整查询顺序或创建覆盖索引

6. 索引列使用NOT或!=

   • 失效示例：WHERE status != 'deleted'
   • 原理：需要扫描大部分索引条目，成本可能高于全表扫描
   • 优化：
     • 改为正向查询：WHERE status IN ('active', 'pending')
     • 对区分度高的列可尝试强制索引

高级优化策略

1. 覆盖索引优化

   • 通过索引直接返回查询列，避免回表操作
   • 示例：创建索引(category, price)优化查询

     SELECT category, price FROM products WHERE category = 'electronics'
     

2. 索引下推（ICP）

   • MySQL 5.6+特性，将WHERE条件过滤下推到存储引擎层
   • 示例：索引(city, age)，查询WHERE city='北京' AND age>20
   • 传统方式：先检索所有city='北京'的记录再回表过滤age
   • ICP：在索引层直接过滤age>20，减少回表次数

3. 索引选择性评估

   • 选择性 = 不重复值数量 / 总记录数
   • 低选择性列（如性别）建索引效果差，应结合其他列建复合索引

实战检查清单

1. 使用EXPLAIN分析执行计划，关注type列（index/range为佳）
2. 避免在WHERE子句中对索引列进行表达式计算
3. 复合索引遵循最左前缀原则，按区分度降序排列
4. 大数据量时优先考虑覆盖索引
5. 定期使用ANALYZE TABLE更新索引统计信息

通过理解这些原理和策略，你能在编写SQL时主动避免索引失效，并针对具体场景选择最优索引方案。