索引失效场景

1. 最左前缀原则失效假设联合索引CREATE INDEX idx_abc ON user(name, age, city);索引顺序name → age → city可以走索引where nameTomwhere nameTom and age20where nameTom and age20 and cityBJ不能走索引where age20where cityBJwhere age20 and cityBJ因为缺少最左列nameBTree 无法定位起始位置。2. 索引列使用函数例如where YEAR(create_time)2025索引create index idx_time on user(create_time);失效原因数据库需要先计算YEAR(create_time)BTree 存的是2025-06-16 10:00:00不是2025无法直接利用索引。优化where create_time 2025-01-01 and create_time 2026-01-013. 索引列参与运算例如where age 1 20索引idx_age(age)失效。因为数据库需要先算 age1无法直接使用 BTree。改为where age 194. 隐式类型转换表phone varchar(20)索引idx_phone(phone)查询where phone 13800138000注意phone 是 varchar 条件是数字MySQL 可能会CAST(phone AS SIGNED)变成函数作用于索引列导致索引失效。正确写法where phone138001380005. LIKE 左模糊查询索引idx_name(name)可以走索引where name like Tom%因为Tom Tom1 Tom2 Tom3范围明确。不能走索引where name like %Tom或者where name like %Tom%因为前缀未知BTree 无法定位起点。只能全表扫描。6. 范围查询后面的列失效联合索引(name, age, city)查询where nameTom and age 20 and cityBJ执行到age 20之后city无法继续利用索引匹配。原因范围查询会破坏索引连续性。口诀范围之后全失效这里是指索引匹配能力不是一定完全不用索引7. OR 导致索引失效例如where nameTom or salary10000如果name有索引 salary没索引MySQL 很可能直接全表扫描因为走索引 全表扫描成本可能更高。优化UNION ALL拆开。8. 使用 ! 或 例如where age ! 20或者where age 20数据库发现大部分数据都满足需要扫描大量记录。优化器经常选择全表扫描9. NOT IN例如where id not in (1,2,3)通常返回绝大部分数据优化器可能放弃索引。10. NOT EXISTS类似where not exists(...)很多场景索引利用率较低。11. 数据量太小例如10条数据即使有索引全表扫描可能比索引查找回表更快。优化器会主动放弃索引。12. 查询结果占比过高例如where gender男表1000万数据其中900万男即使有索引idx_gender优化器可能认为回表900万次成本太高。直接全表扫描更划算。高频总结MySQL 索引失效常见场景包括不满足联合索引最左前缀原则对索引列使用函数、计算或表达式隐式类型转换LIKE 以%开头联合索引中范围查询后的列无法继续利用索引OR 一侧无索引使用!、、NOT IN、NOT EXISTS数据量过小或查询结果占比过高时优化器主动放弃索引。本质上索引失效的原因要么是BTree 无法利用有序性定位数据要么是优化器评估后认为走索引成本高于全表扫描。