我刚工作的时候有次上线了个新功能结果 SQL 查询慢得要命。DBA 帮我一看执行计划发现索引失效了全表扫描 2000 万条数据。从那以后我每次写完 SQL 都会检查索引是否失效避免线上事故。今天咱们就来聊聊 MySQL 索引失效的七种情况看完这篇你就能避开 90% 的索引失效坑。索引失效是啥索引失效指的是你以为 SQL 会走索引结果 MySQL 优化器觉得走索引更慢放弃了索引改成全表扫描。验证索引是否失效-- 看执行计划EXPLAINSELECT*FROMusersWHEREage25;如果key字段是NULL说明索引失效了。情况 1对索引字段用函数问题对索引字段用函数索引失效。-- age 有索引但用了函数索引失效EXPLAINSELECT*FROMusersWHERELEFT(name,5)Alice;为什么失效索引存的是字段的原始值不是函数计算后的值。MySQL 没法用索引快速定位只能全表扫描。优化方案改成范围查询或者等号查询。-- 优化后用 LIKE 前缀匹配能用索引SELECT*FROMusersWHEREnameLIKEAlice%;如果一定要用函数考虑生成列Generated Column 索引。-- 创建生成列存 name 的前 5 个字符ALTERTABLEusersADDCOLUMNname_prefixCHAR(5)AS(LEFT(name,5))STORED;-- 给生成列加索引CREATEINDEXidx_name_prefixONusers(name_prefix);-- 查询时用生成列SELECT*FROMusersWHEREname_prefixAlice;情况 2隐式类型转换问题查询条件的类型和索引字段的类型不一致导致隐式类型转换索引失效。-- age 是 INT但查询条件传了字符串索引失效EXPLAINSELECT*FROMusersWHEREage25;为什么失效MySQL 会把索引字段age转换成字符串再和25比较相当于对索引字段用了函数。优化方案查询条件的类型和索引字段的类型保持一致。-- 优化后传 INTSELECT*FROMusersWHEREage25;隐藏得更深的类型转换-- user_id 是 VARCHAR(50)但查询条件传了 INT索引失效EXPLAINSELECT*FROMordersWHEREuser_id123;为什么失效MySQL 会把user_id转换成数字再和123比较相当于对索引字段用了函数。优化方案查询条件的类型和索引字段的类型保持一致。-- 优化后传字符串SELECT*FROMordersWHEREuser_id123;情况 3模糊查询前缀通配符问题LIKE查询用前缀通配符%开头索引失效。-- name 有索引但 LIKE 前缀通配符索引失效EXPLAINSELECT*FROMusersWHEREnameLIKE%Alice;为什么失效索引是有序的LIKE %Alice没法用索引快速定位因为%匹配任意字符。优化方案 1改成后缀通配符%结尾。-- 优化后LIKE 后缀通配符能用索引SELECT*FROMusersWHEREnameLIKEAlice%;优化方案 2用覆盖索引只查索引字段。-- 优化后覆盖索引能用索引SELECTnameFROMusersWHEREnameLIKE%Alice;优化方案 3用全文索引FULLTEXT INDEX。-- 创建全文索引ALTERTABLEusersADDFULLTEXTINDEXft_idx_name(name);-- 用 MATCH AGAINST 查询SELECT*FROMusersWHEREMATCH(name)AGAINST(Alice);情况 4OR 连接的条件中有字段没索引问题OR连接的条件中只要有一个字段没索引整个索引失效。-- age 有索引但 name 没索引索引失效EXPLAINSELECT*FROMusersWHEREage25ORnameAlice;为什么失效MySQL 认为既然name没索引那还不如全表扫描一次扫描搞定不用先走索引再全表扫描。优化方案 1给OR连接的所有字段加索引。-- 给 name 加索引CREATEINDEXidx_nameONusers(name);-- 再执行查询索引生效SELECT*FROMusersWHEREage25ORnameAlice;优化方案 2用UNION代替OR。-- 优化后UNION两个子查询都能走索引SELECT*FROMusersWHEREage25UNIONALLSELECT*FROMusersWHEREnameAlice;情况 5联合索引没遵循最左前缀问题联合索引有最左前缀原则如果查询条件没用到最左前缀索引失效。-- 联合索引 (age, name)CREATEINDEXidx_age_nameONusers(age,name);-- 没用到最左前缀 age索引失效EXPLAINSELECT*FROMusersWHEREnameAlice;为什么失效联合索引是先按age排序再按name排序。如果没指定ageMySQL 没法快速定位。优化方案查询条件遵循最左前缀原则。-- 能用索引的情况WHEREage25ANDnameAlice-- 用到 (age, name)WHEREage25-- 用到 (age)WHEREage20ANDage30-- 用到 (age)-- 不能用索引的情况WHEREnameAlice-- 没用到最左前缀 age如果一定要查name考虑单独给name建索引。CREATEINDEXidx_nameONusers(name);情况 6索引选择性太差问题如果索引字段的选择性太差比如性别字段只有男/女两个值MySQL 可能放弃索引选择全表扫描。-- gender 有索引但选择性太差MySQL 可能放弃索引EXPLAINSELECT*FROMusersWHEREgender男;为什么失效如果gender男的记录占全表的 50%那走索引还得回表 50% 的记录不如全表扫描减少随机 I/O。优化方案 1如果业务允许加其他条件缩小范围。-- 优化后加其他条件缩小范围SELECT*FROMusersWHEREgender男ANDage25;优化方案 2用覆盖索引只查索引字段不需要回表。-- 优化后覆盖索引SELECTgenderFROMusersWHEREgender男;情况 7优化器估计走索引更慢问题MySQL 优化器会根据统计信息估算走索引的成本如果估计走索引更慢会放弃索引。-- 假设 users 表有 1000 万行age25 的有 900 万行-- MySQL 估计走索引要回表 900 万次不如全表扫描EXPLAINSELECT*FROMusersWHEREage25;为什么失效如果符合条件的记录太多走索引要回表很多次随机 I/O全表扫描反而更快顺序 I/O。优化方案 1加LIMIT限制返回行数。-- 优化后加 LIMITMySQL 可能走索引SELECT*FROMusersWHEREage25LIMIT10;优化方案 2用覆盖索引只查索引字段不需要回表。-- 优化后覆盖索引SELECTageFROMusersWHEREage25;优化方案 3FORCE INDEX强制走索引不推荐除非你确定索引更快。-- 强制走 idx_age 索引SELECT*FROMusersFORCEINDEX(idx_age)WHEREage25;实战建议1. 每次写完 SQL 都用 EXPLAIN 检查这是最重要的建议。很多线上慢查询都是因为索引失效导致全表扫描。2. 避免对索引字段用函数如果一定要用函数考虑生成列 索引。3. 查询条件的类型和索引字段的类型保持一致避免隐式类型转换导致索引失效。4. 模糊查询尽量用后缀通配符如果一定要用前缀通配符考虑全文索引。5. OR 连接的条件要给所有字段加索引或者改用UNION代替OR。6. 联合索引要遵循最左前缀原则如果查询条件不遵循最左前缀考虑单独给字段建索引。7. 索引选择性太差时考虑覆盖索引或者加其他条件缩小范围如果优化器估计走索引更慢会放弃索引。总结索引失效的七种情况对索引字段用函数隐式类型转换模糊查询前缀通配符OR 连接的条件中有字段没索引联合索引没遵循最左前缀索引选择性太差优化器估计走索引更慢实战建议每次写完 SQL 都用 EXPLAIN 检查、避免对索引字段用函数、查询条件的类型和索引字段的类型保持一致、模糊查询尽量用后缀通配符、OR 连接的条件要给所有字段加索引、联合索引要遵循最左前缀原则、索引选择性太差时考虑覆盖索引如果你能把这七种情况讲清楚并且能优化索引失效的 SQL面试官绝对觉得你有实战经验。实战代码都在我本地跑过你可以放心复制。如果有问题欢迎评论区交流
