数据库查询优化中的索引选择与索引失效场景

题目描述
索引是数据库查询性能优化的核心手段之一，但错误的使用会导致索引失效，反而降低查询效率。面试官常会考察：如何根据查询条件选择合适的索引？哪些场景会导致索引失效？如何通过执行计划判断索引使用情况？理解这些内容对实际工作中的SQL优化至关重要。

一、索引的基本作用与选择原则
索引的本质是减少数据扫描量，通过B+树等结构快速定位数据。选择索引需遵循以下原则：

1. 高选择性列优先：索引列的值越唯一（如身份证号），过滤的数据越多，效果越明显。
2. 覆盖索引减少回表：若索引包含查询所需的所有列（例如SELECT name FROM users WHERE age=20，索引为(age, name)），则无需访问数据行，直接返回结果。
3. 最左前缀匹配：复合索引（如(a, b, c)）只能从左到右依次使用。查询条件必须包含最左列（如a=1或a=1 AND b=2），否则无法利用索引。

二、索引失效的常见场景与原理
以下场景会导致数据库优化器放弃使用索引，转为全表扫描：

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

   • 示例：WHERE YEAR(create_time) = 2023
   • 失效原因：索引存储的是原始值，对列计算后需遍历所有数据才能计算比较值。
   • 优化：改为范围查询WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31'。

2. 隐式类型转换

   • 示例：表中phone列为字符串类型，但查询写为WHERE phone = 13800138000（数值类型）。
   • 失效原因：数据库需将phone列逐行转换为数值再比较，无法使用索引。
   • 优化：确保类型一致WHERE phone = '13800138000'。

3. 模糊查询以通配符开头

   • 示例：WHERE name LIKE '%小明'
   • 失效原因：B+树索引按前缀排序，无法匹配后缀。LIKE '小明%'仍可使用索引。
   • 优化：考虑全文索引或倒排索引。

4. OR连接非索引列条件

   • 示例：WHERE indexed_column = 1 OR non_indexed_column = 2
   • 失效原因：OR要求满足任一条件，即使部分条件能用索引，仍需扫描全表验证其他条件。
   • 优化：拆分为UNION查询：

     SELECT * FROM t WHERE indexed_column = 1  
     UNION  
     SELECT * FROM t WHERE non_indexed_column = 2  
     

5. 复合索引未满足最左前缀

   • 示例：索引为(a, b)，查询条件为WHERE b=2。
   • 失效原因：索引按a排序，在a未知时无法快速定位b。
   • 优化：调整索引顺序或增加单独索引。

三、通过执行计划分析索引使用情况
以MySQL的EXPLAIN命令为例，关键字段解读：

1. type：查询效率从高到低为const > ref > range > index > ALL。若为ALL则表示全表扫描。
2. key：实际使用的索引名称。若为NULL则未使用索引。
3. Extra：
   • Using index：使用覆盖索引，无需回表。
   • Using where：服务器层需过滤数据（可能索引部分生效）。
   • Using filesort：需额外排序，考虑添加索引优化排序。

四、实战案例：索引选择策略
假设表orders有字段user_id（高选择性）、status（低选择性，仅3种状态）、create_time，查询：

SELECT * FROM orders  
WHERE user_id = 1001 AND status = 'shipped'  
ORDER BY create_time DESC;  

• 方案1：单独索引(user_id)
  • 优点：快速定位user_id=1001的数据。
  • 缺点：需在内存中过滤status并排序，数据量大时效率低。
• 方案2：复合索引(user_id, status, create_time)
  • 优点：覆盖查询条件与排序，避免回表及文件排序。
  • 验证：EXPLAIN结果应显示type: ref、Extra: Using index。

总结
索引选择需结合查询条件、排序、分组等需求，避免失效场景。通过执行计划验证索引效果，优先使用覆盖索引和复合索引减少磁盘I/O。实际工作中需权衡索引的读写开销，避免过度索引。