数据库索引的失效场景与排查方法

题目描述
数据库索引是提升查询性能的关键技术，但在特定场景下索引可能失效，导致查询性能急剧下降。这个问题探讨索引失效的常见场景、底层原理，以及如何系统性地排查和避免失效。

一、索引失效的根本原因
索引失效的本质是优化器认为全表扫描比索引扫描更高效。主要原因包括：

1. 数据分布倾斜：当条件值在表中占比过高时，优化器可能跳过索引
2. 索引选择度不足：索引列区分度低，导致索引效率不如全表扫描
3. 计算/函数转换：对索引列进行运算导致无法使用索引树定位

二、典型失效场景详解
场景1：隐式类型转换

• 示例：SELECT * FROM users WHERE phone = 13800138000（phone为字符串类型）
• 失效原因：数字被转换为字符串，等价于对索引列使用函数CAST(phone AS NUMBER)
• 底层原理：B+树索引按原数据类型排序，类型转换后无法直接比较

场景2：最左前缀原则违反

• 复合索引(name, age, city)：
  • 有效：WHERE name='A' AND age=20（使用索引前两列）
  • 失效：WHERE age=20 AND city='Beijing'（跳过name列无法定位）
• 类比：查字典时直接翻"第20页"而不知道首字母

场景3：范围查询后的索引列

• 示例：WHERE name='A' AND age>18 AND city='Beijing'
• 结果：仅能使用(name,age)两列索引，city列失效
• 原理：B+树中age范围查询后city值无序排列

场景4：函数/表达式操作索引列

• 失效操作：

  WHERE YEAR(create_time)=2023        -- 应改为范围查询
  WHERE amount*2>100                  -- 应重写为amount>50
  WHERE SUBSTRING(name,1,3)='ABC'    -- 应改用LIKE 'ABC%'
  

三、系统性排查方法
步骤1：执行计划分析
使用EXPLAIN或EXPLAIN ANALYZE查看：

• type列为ALL表示全表扫描
• key列为NULL表示未使用索引
• Extra列出现Using filesort/Using temporary需警惕

步骤2：索引使用统计
通过性能库（如INFORMATION_SCHEMA.INDEX_STATISTICS）分析索引命中率，长期未使用的索引考虑删除。

步骤3：数据分布检查

-- 检查选择度
SELECT 
  COUNT(DISTINCT status)/COUNT(*) as selectivity 
FROM orders;
-- 当选择度<0.1时索引效果显著

四、优化实践方案

1. SQL重写优化

   • 避免隐式转换：确保条件值与列类型一致
   • 前置高选择度列：将区分度高的列放在复合索引左侧

2. 索引策略调整

   • 使用覆盖索引：SELECT字段全部包含在索引中
   • 索引下推技术：将WHERE条件在存储引擎层过滤

3. 特殊场景处理

   • 范围查询较多时：使用IN()替代部分范围查询
   • 模糊查询：前缀匹配使用索引，%value%需全文索引

通过理解索引失效机理并结合执行计划分析，可有效避免性能陷阱，提升查询效率。