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 < constkey: 实际使用的索引possible_keys: 可能使用的索引rows: 扫描的行数Extra: 额外信息(Using where, Using index, Using filesort等)
使用 SHOW WARNINGS 查看优化后的SQL
EXPLAINSELECT...;SHOWWARNINGS;-- 可以看到MySQL优化器实际执行的SQL七、优化建议
- 避免在索引列上做运算或使用函数
- 注意数据类型一致性
- 合理使用组合索引,遵循最左前缀
- 等值条件列前置于范围查询列
- 控制索引数量,不是越多越好
- 定期分析表和优化索引(
ANALYZE TABLE,OPTIMIZE TABLE) - 监控慢查询日志,针对性优化
- 考虑使用覆盖索引减少回表
-- 查看索引使用情况SHOWINDEXFROMtable_name;-- 分析表ANALYZETABLEtable_name;-- 查看索引统计信息SHOWTABLESTATUSLIKE'table_name';