MySQL索引失效的常见场景整理

MySQL索引失效场景全面整理

一、查询条件导致的索引失效

1. 在索引列上使用函数或表达式

-- ❌ 索引失效SELECT*FROMuserWHEREYEAR(create_time)=2024;SELECT*FROMuserWHEREage+1=25;-- ✅ 正确写法SELECT*FROMuserWHEREcreate_time>='2024-01-01'ANDcreate_time<'2025-01-01';SELECT*FROMuserWHEREage=24;

2. 隐式类型转换

-- ❌ phone是varchar类型,传入数字会导致索引失效SELECT*FROMuserWHEREphone=13800138000;-- ✅ 正确写法SELECT*FROMuserWHEREphone='13800138000';-- 注意:如果字段是int类型,传入字符串不会失效(MySQL会把字符串转成数字)

3. 使用 NOT、!=、<> 操作符

-- ❌ 通常会导致索引失效(优化器可能选择全表扫描)SELECT*FROMuserWHEREstatus!=1;SELECT*FROMuserWHEREstatus<>1;SELECT*FROMuserWHEREageNOTIN(18,20);-- ✅ 可以改写为SELECT*FROMuserWHEREstatusIN(0,2,3);-- 如果状态值已知

4. 使用 OR 连接条件

-- ❌ OR连接的列如果有一个没有索引,整个查询索引失效SELECT*FROMuserWHEREname='张三'ORage=25;-- ✅ 如果两个列都有索引,可以正常使用(index merge)SELECT*FROMuserWHEREid=1ORemail='test@example.com';-- ✅ 或者改写为UNIONSELECT*FROMuserWHEREname='张三'UNIONSELECT*FROMuserWHEREage=25;

5. LIKE 通配符开头

-- ❌ 以%开头无法使用索引SELECT*FROMuserWHEREnameLIKE'%张三';SELECT*FROMuserWHEREnameLIKE'%张三%';-- ✅ 前缀匹配可以使用索引SELECT*FROMuserWHEREnameLIKE'张三%';

6. IS NULL 和 IS NOT NULL

-- ❌ 可能导致索引失效(取决于NULL值的比例)SELECT*FROMuserWHEREemailISNULL;SELECT*FROMuserWHEREemailISNOTNULL;-- 说明:如果列中NULL值很少,IS NULL可能使用索引-- 如果NULL值很多,IS NOT NULL可能使用索引-- 具体看优化器的选择

二、组合索引相关失效

7. 违反最左前缀原则

-- 假设有组合索引: idx_abc(a, b, c)-- ❌ 没有使用最左列a,索引失效SELECT*FROMtWHEREb=2ANDc=3;-- ✅ 符合最左前缀SELECT*FROMtWHEREa=1;SELECT*FROMtWHEREa=1ANDb=2;SELECT*FROMtWHEREa=1ANDb=2ANDc=3;SELECT*FROMtWHEREa=1ANDc=3;-- a可以用索引,c用不上

8. 范围查询导致后续列失效

-- 假设索引: idx_abc(a, b, c)-- ⚠️ a用了范围查询,b和c无法使用索引SELECT*FROMtWHEREa>1ANDb=2ANDc=3;-- ✅ 如果有范围和等值混合,等值列应该前置-- 应该建索引: idx_bca(b, c, a)SELECT*FROMtWHEREa>1ANDb=2ANDc=3;

9. 索引列顺序与查询条件顺序不一致(部分场景)

-- 假设索引: idx_ab(a, b)-- ✅ MySQL优化器会自动调整,这个没问题SELECT*FROMtWHEREb=2ANDa=1;-- ⚠️ 但如果是范围查询,顺序就重要了SELECT*FROMtWHEREb>2ANDa=1;-- a能用索引,b用不上

三、数据分布导致的失效

10. 数据区分度太低

-- ❌ 如果status只有0和1两个值,且分布均匀-- 优化器可能认为全表扫描更快SELECT*FROMuserWHEREstatus=1;-- 说明:当查询结果集超过表数据的30%(经验值),优化器倾向于全表扫描

11. 数据量太小

-- 如果表只有几百行数据,MySQL可能直接全表扫描-- 因为索引的维护成本可能大于收益

四、查询本身的问题

12. SELECT * 导致回表代价大

-- ⚠️ 即使使用了索引,但需要大量回表查询SELECT*FROMuserWHEREage=25;-- ✅ 如果只需要部分字段,考虑覆盖索引-- 建立索引: idx_age_name_email(age, name, email)SELECTname,emailFROMuserWHEREage=25;

13. ORDER BY 和 GROUP BY 失效

-- 假设索引: idx_age(age)-- ❌ WHERE条件没用索引,ORDER BY也无法利用索引SELECT*FROMuserWHEREname='张三'ORDERBYage;-- ✅ WHERE和ORDER BY都使用索引列SELECT*FROMuserWHEREage>20ORDERBYage;-- 假设组合索引: idx_ab(a, b)-- ✅ GROUP BY 使用索引SELECTa,COUNT(*)FROMtGROUPBYa;-- ❌ GROUP BY 跳过最左列SELECTb,COUNT(*)FROMtGROUPBYb;

五、其他特殊情况

14. JOIN 字段类型不一致

-- ❌ 如果 a.user_id 是varchar, b.id 是intSELECT*FROMorderaJOINuserbONa.user_id=b.id;-- 会发生隐式转换,可能导致索引失效

15. IN 包含大量值

-- ⚠️ IN 中值太多可能导致优化器放弃索引SELECT*FROMuserWHEREidIN(1,2,3,...,10000个值);-- 建议:IN的值控制在1000以内

16. 使用 FORCE INDEX 强制使用错误索引

-- ❌ 强制使用了不合适的索引SELECT*FROMuserFORCEINDEX(idx_age)WHEREname='张三';

六、如何排查索引失效

使用 EXPLAIN 分析

EXPLAINSELECT*FROMuserWHEREname='张三';

关键字段:

• type: ALL(全表扫描) < index < range < ref < const
• key: 实际使用的索引
• possible_keys: 可能使用的索引
• rows: 扫描的行数
• Extra: 额外信息(Using where, Using index, Using filesort等)

使用 SHOW WARNINGS 查看优化后的SQL

EXPLAINSELECT...;SHOWWARNINGS;-- 可以看到MySQL优化器实际执行的SQL

七、优化建议

1. 避免在索引列上做运算或使用函数
2. 注意数据类型一致性
3. 合理使用组合索引,遵循最左前缀
4. 等值条件列前置于范围查询列
5. 控制索引数量,不是越多越好
6. 定期分析表和优化索引(ANALYZE TABLE,OPTIMIZE TABLE)
7. 监控慢查询日志,针对性优化
8. 考虑使用覆盖索引减少回表

-- 查看索引使用情况SHOWINDEXFROMtable_name;-- 分析表ANALYZETABLEtable_name;-- 查看索引统计信息SHOWTABLESTATUSLIKE'table_name';