引言

索引在数据库查询性能优化中扮演着至关重要的角色，它可以大大提高数据检索速度，减少磁盘I/O操作。然而，在某些特定情况下，即使数据库表中有相应的索引，查询时也可能无法有效利用这些索引，这就是所谓的“索引失效”现象。本篇技术博客将详细介绍几种常见的索引失效场景，并给出相应示例代码，帮助读者理解并规避这些陷阱。

一、索引失效的常见场景

索引列未出现在WHERE子句中

当查询语句的WHERE子句中不包含索引列时，数据库无法利用索引进行快速定位。

   -- 假设有索引 idx_age ON users(age)
   SELECT * FROM users WHERE name = 'John Doe'; 

上述查询中，尽管users表上有针对age字段的索引，但查询条件却是基于name字段，因此索引idx_age无法起到优化查询的效果。

索引列参与计算或函数操作

当索引列参与到数学运算、函数调用或其他表达式中时，索引可能无法被利用。

   -- 假设有索引 idx_age ON users(age)
   SELECT * FROM users WHERE age + 5 > 20; 

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

复合索引（组合索引）按照索引列的顺序遵循“最左前缀”原则，如果查询条件没有从索引的第一列开始，后续的索引列则无法得到利用

   -- 假设有复合索引 idx_firstname_lastname ON users(first_name, last_name)
   SELECT * FROM users WHERE last_name = 'Doe'; 

索引列参与LIKE查询且模式不匹配

当索引列参与LIKE查询时，如果查询模式以通配符%开头，则索引通常无法被利用。

   -- 假设有索引 idx_email ON users(email)
   SELECT * FROM users WHERE email LIKE '%example.com'; 

在这个查询中，由于email字段值前面的%通配符，数据库无法利用索引idx_email进行有效的搜索。

排序或分组操作未命中索引

如果ORDER BY或GROUP BY操作中的列未被索引，或者排序方向与索引顺序相反，都可能导致索引失效。

   -- 假设有索引 idx_age_desc ON users(age DESC)
   SELECT * FROM users ORDER BY age ASC; 

在这个查询中，虽然age字段有索引，但排序方向与索引定义的方向相反，因此无法利用索引进行排序。

二、避免和解决索引失效的方法

• 优化查询语句：尽可能将查询条件设计得与现有索引相匹配，避免不必要的计算和函数操作。
• 创建合适索引：充分了解查询模式和业务需求，创建能满足查询需求的单列或多列索引，并注意索引顺序。
• 覆盖索引：如果可能，创建覆盖索引，即索引包含查询所需的所有列，这样可以避免回表操作。
• 检查执行计划：使用数据库提供的执行计划分析工具（如MySQL的EXPLAIN）检查查询是否有效地利用了索引。

通过以上深入解析和示例，希望读者能够更清晰地理解索引失效的原因及应对策略，从而在日常数据库查询优化工作中避免这类问题，提升查询性能。