在数据库查询优化工作中，索引的正确使用是提升查询效率的关键，但很多开发者写的SQL语句看似合理，实际执行时却没有命中索引，导致查询耗时大幅增加。了解索引失效的常见场景并掌握对应的优化方法，是每一个后端开发者和数据库运维人员必备的技能。
索引失效的15种高频场景及优化写法
1. 对索引字段使用函数操作
当查询条件中对索引字段使用函数时，数据库无法利用索引的有序性进行快速查找，会导致索引失效。
失效SQL示例：
-- 对create_time索引字段使用DATE函数 SELECT * FROM user WHERE DATE(create_time) = '2024-05-01';
优化写法：将函数操作转移到条件值的处理上，避免对索引字段直接操作。
-- 改为范围查询，命中create_time索引 SELECT * FROM user WHERE create_time >= '2024-05-01 00:00:00' AND create_time < '2024-05-02 00:00:00';
2. 隐式类型转换
如果查询条件中索引字段的类型和传入值的类型不匹配，数据库会进行隐式类型转换，导致索引失效。
失效SQL示例：假设user表的phone字段是varchar类型且建有索引，传入数值类型的值：
-- phone是varchar类型，传入数值会触发隐式转换 SELECT * FROM user WHERE phone = 13800138000;
优化写法：保证传入值的类型和索引字段类型一致。
-- 传入字符串类型的值，匹配phone字段类型 SELECT * FROM user WHERE phone = '13800138000';
3. like左模糊匹配
使用like进行左模糊匹配或者全模糊匹配时，索引无法发挥作用，只有右模糊匹配可以命中索引。
失效SQL示例：
-- 左模糊匹配，索引失效 SELECT * FROM article WHERE title LIKE '%SQL优化'; -- 全模糊匹配，索引失效 SELECT * FROM article WHERE title LIKE '%SQL%';
优化写法：尽量使用右模糊匹配，如果必须使用左模糊或全模糊，可考虑使用全文索引替代。
-- 右模糊匹配，命中title索引 SELECT * FROM article WHERE title LIKE 'SQL优化%';
4. 使用or连接条件且部分字段无索引
当查询条件用or连接，且or两边的字段至少有一个没有建立索引时，整个查询的索引都会失效。
失效SQL示例：假设user表只有id字段有索引，name字段无索引：
-- or连接的条件中name无索引，导致id索引也失效 SELECT * FROM user WHERE id = 1 OR name = '张三';
优化写法：给or连接的所有字段都建立索引，或者将查询拆分为两个单独的查询用union连接。
-- 拆分后用union连接，两个查询分别命中各自索引 SELECT * FROM user WHERE id = 1 UNION SELECT * FROM user WHERE name = '张三';
5. 联合索引不满足最左前缀原则
联合索引是按照索引字段的创建顺序排序的，查询时必须从最左端的字段开始匹配，否则索引失效。
失效SQL示例：假设建立了联合索引idx_user_age_name(age, name)：
-- 跳过最左字段age，直接查询name，联合索引失效 SELECT * FROM user WHERE name = '李四';
优化写法：查询条件包含联合索引最左端的字段，符合最左前缀原则。
-- 包含最左字段age，命中联合索引 SELECT * FROM user WHERE age = 20 AND name = '李四';
6. 索引字段参与运算
查询条件中对索引字段进行算术运算时，索引会失效。
失效SQL示例：
-- 对price索引字段做加法运算 SELECT * FROM goods WHERE price + 10 = 100;
优化写法：将运算转移到条件值上，避免对索引字段直接运算。
-- 调整运算逻辑，命中price索引 SELECT * FROM goods WHERE price = 90;
7. 使用不等于（!=、<>）判断
当查询条件使用不等于判断时，数据库通常会认为需要扫描大部分数据，从而放弃使用索引。
失效SQL示例：
-- 使用不等于判断，索引失效 SELECT * FROM user WHERE age != 20; SELECT * FROM user WHERE age <> 20;
优化写法：如果业务允许，改为范围查询，或者结合其他索引字段缩小扫描范围。
-- 改为范围查询，可能命中age索引 SELECT * FROM user WHERE age < 20 OR age > 20;
8. 使用is null或者is not null判断
对于普通索引，当查询条件使用is null或者is not null时，可能触发索引失效，具体和数据库版本及数据分布有关。
失效SQL示例：
-- 使用is null判断，可能索引失效 SELECT * FROM user WHERE email IS NULL; -- 使用is not null判断，可能索引失效 SELECT * FROM user WHERE email IS NOT NULL;
优化写法：如果字段允许，设置默认值替代null值，用等于判断替代is null判断。
-- 假设email默认值为空字符串，替代null判断 SELECT * FROM user WHERE email = '';
9. 范围查询后的联合索引字段失效
联合索引中，如果某个字段使用了范围查询，那么该字段之后的索引字段无法命中索引。
失效SQL示例：假设联合索引idx_user_age_name(age, name)，age使用范围查询：
-- age是范围查询，name字段无法命中联合索引 SELECT * FROM user WHERE age > 20 AND name = '王五';
优化写法：调整联合索引的创建顺序，将范围查询的字段放在联合索引的最后一位。
-- 调整联合索引为idx_user_name_age(name, age) -- 此时age范围查询可以命中索引 SELECT * FROM user WHERE name = '王五' AND age > 20;
10. 查询条件中使用not in
使用not in进行条件过滤时，数据库通常会放弃使用索引，进行全表扫描。
失效SQL示例：
-- 使用not in，索引失效 SELECT * FROM user WHERE age NOT IN (20, 21, 22);
优化写法：如果数据量不大，可改为not exists，或者拆分为多个小于、大于的条件。
-- 改为not exists写法，可能命中索引
SELECT * FROM user u1 WHERE NOT EXISTS (
SELECT 1 FROM (SELECT 20 AS age UNION SELECT 21 UNION SELECT 22) t
WHERE t.age = u1.age
);
11. 查询条件中使用in但参数过多
虽然in通常可以命中索引，但如果in中的参数数量过多，数据库优化器可能会认为全表扫描效率更高，从而放弃索引。
失效SQL示例：in中包含上千个参数：
-- in参数过多，可能索引失效 SELECT * FROM user WHERE id IN (1,2,3,...1000);
优化写法：将参数分批查询，或者将参数存入临时表，用join的方式查询。
-- 创建临时表存储参数 CREATE TEMPORARY TABLE temp_ids (id INT PRIMARY KEY); INSERT INTO temp_ids VALUES (1),(2),(3),...; -- 用join查询，命中id索引 SELECT u.* FROM user u JOIN temp_ids t ON u.id = t.id;
12. 字符集不一致导致隐式转换
如果关联查询的表字符集不一致，或者查询条件中字段和值的字符集不一致，会触发隐式转换导致索引失效。
失效SQL示例：假设user表字符集是utf8mb4，order表字符集是utf8，关联查询时：
-- 字符集不一致，关联字段的索引失效 SELECT u.*, o.order_no FROM user u JOIN order o ON u.id = o.user_id;
优化写法：统一相关表的字符集，或者在查询时显式转换字符集（优先统一表字符集）。
-- 统一字符集后查询，关联字段正常命中索引 -- 或者临时转换字符集： SELECT u.*, o.order_no FROM user u JOIN order o ON u.id = CONVERT(o.user_id USING utf8mb4);
13. 优化器认为全表扫描更快
当查询需要返回表中大部分数据时，数据库优化器会认为全表扫描的成本比走索引更低，从而放弃索引。
失效SQL示例：表中90%的数据都满足查询条件：
-- 返回大部分数据，优化器放弃索引 SELECT * FROM user WHERE age > 10;
优化写法：如果业务不需要返回所有字段，只查询带索引的字段，或者增加更精确的过滤条件缩小返回范围。
-- 只查询索引包含的字段，可能触发索引覆盖 SELECT id, age FROM user WHERE age > 10;
14. 使用select * 且无法覆盖索引
如果查询使用select *，且索引无法覆盖所有查询字段，数据库需要回表查询，当回表成本过高时可能放弃索引。
失效SQL示例：假设idx_user_age只有age字段，查询所有字段：
-- 需要回表查询所有字段，可能放弃索引 SELECT * FROM user WHERE age = 20;
优化写法：只查询需要的字段，或者建立包含所有查询字段的联合索引实现索引覆盖。
-- 只查询需要的字段，减少回表成本 SELECT id, age, name FROM user WHERE age = 20;
15. 查询条件中对索引字段使用case when
在查询条件中使用case when处理索引字段时，索引会失效。
失效SQL示例：
-- 对status字段使用case when，索引失效 SELECT * FROM order WHERE CASE WHEN status = 1 THEN 1 ELSE 0 END = 1;
优化写法：将case when的逻辑转移到查询逻辑中，避免对索引字段做复杂处理。
-- 直接写过滤条件，命中status索引 SELECT * FROM order WHERE status = 1;
索引失效排查方法
当发现SQL查询性能不符合预期时，可以通过以下方式排查是否出现索引失效：
- 使用
EXPLAIN命令查看SQL的执行计划，观察key字段是否显示了预期的索引名称，如果为null则说明没有命中索引。 - 观察执行计划中的type字段，如果值为ALL说明是全表扫描，需要检查索引使用情况。
- 结合业务场景和上述15种场景，逐一核对SQL语句是否存在对应的问题。
总结
索引失效的原因大多和SQL语句的写法有关，开发过程中需要养成良好的SQL编写习惯，避免在索引字段上做函数、运算、类型转换等操作。当遇到查询性能问题时，优先通过执行计划排查索引使用情况，再对照上述场景调整SQL写法或者索引结构，才能有效提升数据库的查询效率。