SQL语句索引失效是数据库使用过程中非常影响查询性能的问题，当索引无法正常生效时，数据库会进行全表扫描，查询耗时可能成倍增加，因此掌握索引失效的原因、避免方法和优化技巧非常重要。
常见的SQL索引失效场景
1. 对索引字段进行函数或运算操作
如果在查询条件中对索引字段做了函数处理或者算术运算，数据库无法直接使用索引，会导致索引失效。比如对日期字段使用DATE()函数，或者对数值字段做加减乘除运算。
-- 索引失效示例，对create_time做了函数处理 SELECT * FROM user WHERE DATE(create_time) = '2024-05-01'; -- 索引失效示例，对age做了运算 SELECT * FROM user WHERE age + 1 = 20;
2. 使用不等于、not in、is not null判断
当查询条件中使用不等于!=、not in或者is not null时，大部分情况下索引会失效，因为这些条件的匹配范围过大，数据库优化器会认为全表扫描效率更高。
-- 索引失效示例 SELECT * FROM user WHERE age != 20; SELECT * FROM user WHERE id NOT IN (1,2,3); SELECT * FROM user WHERE name IS NOT NULL;
3. like左模糊匹配
使用like进行模糊查询时，如果通配符%放在最左侧，即左模糊匹配，索引会失效，因为索引是按照字段值的前缀顺序存储的，无法匹配开头不确定的内容。
-- 索引失效示例，左模糊匹配 SELECT * FROM user WHERE name LIKE '%三'; -- 索引生效示例，右模糊匹配 SELECT * FROM user WHERE name LIKE '张%';
4. 字符串类型字段不加引号
如果索引字段是字符串类型，查询时传入的条件值没有加单引号，数据库会做隐式类型转换，导致索引失效。
-- 假设phone是varchar类型且加了索引，以下语句会索引失效 SELECT * FROM user WHERE phone = 13800138000; -- 正确写法，索引生效 SELECT * FROM user WHERE phone = '13800138000';
5. 复合索引不满足最左前缀原则
复合索引是按照索引字段的定义顺序依次排序的，查询时必须从第一个字段开始匹配，如果跳过前面的字段直接匹配后面的字段，复合索引会失效。
-- 假设有复合索引idx_name_age(name, age) -- 索引生效，匹配了最左的name字段 SELECT * FROM user WHERE name = '张三'; -- 索引失效，跳过了name字段，直接匹配age SELECT * FROM user WHERE age = 20;
如何避免索引失效
针对上述索引失效的场景，可以采取对应的避免措施：
- 不要在查询条件中对索引字段做函数处理或者算术运算，如果需要处理可以先计算好值再传入查询条件。
- 尽量避免使用不等于、not in、is not null这类判断，如果必须使用，可以评估是否可以通过调整查询逻辑来规避，比如用in代替not in。
- 模糊查询尽量使用右模糊匹配，避免左模糊或者全模糊匹配，如果必须做左模糊查询，可以考虑使用全文索引替代普通索引。
- 字符串类型的字段查询时，一定要给条件值加上单引号，避免隐式类型转换。
- 设计复合索引时，把最常用的查询字段放在最左侧，查询时严格按照复合索引的字段顺序编写条件，满足最左前缀原则。
实用的索引优化技巧
1. 选择合适的索引字段
不要给所有字段都加索引，优先给经常作为查询条件、连接条件、排序条件、分组条件的字段加索引，对于区分度低的字段比如性别、状态这类只有几个固定值的字段，加索引的收益很低，不建议加索引。
2. 控制索引数量
索引不是越多越好，每个索引都会占用磁盘空间，并且在插入、更新、删除数据的时候都需要维护索引，索引过多会降低写操作的效率，单张表的索引数量建议控制在5个以内。
3. 使用覆盖索引
覆盖索引是指查询的字段全部都在索引中，不需要回表查询数据行，能大幅提升查询效率。设计索引的时候可以尽量包含查询需要用到的字段，减少回表操作。
-- 假设有索引idx_name_age(name, age)，以下查询不需要回表，使用覆盖索引 SELECT name, age FROM user WHERE name = '张三';
4. 定期分析索引使用情况
可以通过数据库提供的索引分析工具，定期查看索引的使用频率，对于长期没有使用到的冗余索引及时删除，避免浪费资源。
-- MySQL中查看索引使用情况 SHOW INDEX FROM user; -- 查看冗余索引（需要结合具体的分析工具或脚本）
5. 避免索引字段参与表达式计算
除了查询条件中不要对索引字段做运算，在排序、分组的时候也不要对索引字段做运算，否则也会导致索引无法生效，排序分组效率变低。
合理的索引设计和规范的使用习惯，是保障数据库查询性能的基础，开发者需要在实际开发中结合具体业务场景，灵活调整索引策略，才能让索引发挥最大的作用。