当前位置：首页 > news >正文

MySQL 索引失效的七种情况

news 2026/5/24 6:09:50

我刚工作的时候，有次上线了个新功能，结果 SQL 查询慢得要命。DBA 帮我一看执行计划，发现索引失效了，全表扫描 2000 万条数据。

从那以后，我每次写完 SQL 都会检查索引是否失效，避免线上事故。

今天咱们就来聊聊 MySQL 索引失效的七种情况，看完这篇，你就能避开 90% 的索引失效坑。

索引失效是啥？

索引失效指的是：你以为 SQL 会走索引，结果 MySQL 优化器觉得走索引更慢，放弃了索引，改成全表扫描。

验证索引是否失效

-- 看执行计划EXPLAINSELECT*FROMusersWHEREage=25;

如果key字段是NULL，说明索引失效了。

情况 1：对索引字段用函数

问题：对索引字段用函数，索引失效。

-- age 有索引，但用了函数，索引失效EXPLAINSELECT*FROMusersWHERELEFT(name,5)='Alice';

为什么失效？索引存的是字段的原始值，不是函数计算后的值。MySQL 没法用索引快速定位，只能全表扫描。

优化方案：改成范围查询或者等号查询。

-- 优化后：用 LIKE 前缀匹配（能用索引）SELECT*FROMusersWHEREnameLIKE'Alice%';

如果一定要用函数，考虑生成列（Generated Column）+ 索引。

-- 创建生成列，存 name 的前 5 个字符ALTERTABLEusersADDCOLUMNname_prefixCHAR(5)AS(LEFT(name,5))STORED;-- 给生成列加索引CREATEINDEXidx_name_prefixONusers(name_prefix);-- 查询时用生成列SELECT*FROMusersWHEREname_prefix='Alice';

情况 2：隐式类型转换

问题：查询条件的类型和索引字段的类型不一致，导致隐式类型转换，索引失效。

-- age 是 INT，但查询条件传了字符串，索引失效EXPLAINSELECT*FROMusersWHEREage='25';

为什么失效？MySQL 会把索引字段age转换成字符串，再和'25'比较（相当于对索引字段用了函数）。

优化方案：查询条件的类型和索引字段的类型保持一致。

-- 优化后：传 INTSELECT*FROMusersWHEREage=25;

隐藏得更深的类型转换

-- user_id 是 VARCHAR(50)，但查询条件传了 INT，索引失效EXPLAINSELECT*FROMordersWHEREuser_id=123;

为什么失效？MySQL 会把user_id转换成数字，再和123比较（相当于对索引字段用了函数）。

优化方案：查询条件的类型和索引字段的类型保持一致。

-- 优化后：传字符串SELECT*FROMordersWHEREuser_id='123';

情况 3：模糊查询前缀通配符

问题：LIKE查询用前缀通配符（%开头），索引失效。

-- name 有索引，但 LIKE 前缀通配符，索引失效EXPLAINSELECT*FROMusersWHEREnameLIKE'%Alice';

为什么失效？索引是有序的，LIKE '%Alice'没法用索引快速定位（因为'%'匹配任意字符）。

优化方案 1：改成后缀通配符（%结尾）。

-- 优化后：LIKE 后缀通配符（能用索引）SELECT*FROMusersWHEREnameLIKE'Alice%';

优化方案 2：用覆盖索引（只查索引字段）。

-- 优化后：覆盖索引（能用索引）SELECTnameFROMusersWHEREnameLIKE'%Alice';

优化方案 3：用全文索引（FULLTEXT INDEX）。

-- 创建全文索引ALTERTABLEusersADDFULLTEXTINDEXft_idx_name(name);-- 用 MATCH AGAINST 查询SELECT*FROMusersWHEREMATCH(name)AGAINST('Alice');

情况 4：OR 连接的条件中有字段没索引

问题：OR连接的条件中，只要有一个字段没索引，整个索引失效。

-- age 有索引，但 name 没索引，索引失效EXPLAINSELECT*FROMusersWHEREage=25ORname='Alice';

为什么失效？MySQL 认为，既然name没索引，那还不如全表扫描（一次扫描搞定），不用先走索引再全表扫描。

优化方案 1：给OR连接的所有字段加索引。

-- 给 name 加索引CREATEINDEXidx_nameONusers(name);-- 再执行查询，索引生效SELECT*FROMusersWHEREage=25ORname='Alice';

优化方案 2：用UNION代替OR。

-- 优化后：UNION（两个子查询都能走索引）SELECT*FROMusersWHEREage=25UNIONALLSELECT*FROMusersWHEREname='Alice';

情况 5：联合索引没遵循最左前缀

问题：联合索引有"最左前缀"原则，如果查询条件没用到最左前缀，索引失效。

-- 联合索引 (age, name)CREATEINDEXidx_age_nameONusers(age,name);-- 没用到最左前缀 age，索引失效EXPLAINSELECT*FROMusersWHEREname='Alice';

为什么失效？联合索引是先按age排序，再按name排序。如果没指定age，MySQL 没法快速定位。

优化方案：查询条件遵循最左前缀原则。

-- 能用索引的情况：WHEREage=25ANDname='Alice'-- 用到 (age, name)WHEREage=25-- 用到 (age)WHEREage>20ANDage<30-- 用到 (age)-- 不能用索引的情况：WHEREname='Alice'-- 没用到最左前缀 age

如果一定要查name，考虑单独给name建索引。

CREATEINDEXidx_nameONusers(name);

情况 6：索引选择性太差

问题：如果索引字段的选择性太差（比如性别字段，只有男/女两个值），MySQL 可能放弃索引，选择全表扫描。

-- gender 有索引，但选择性太差，MySQL 可能放弃索引EXPLAINSELECT*FROMusersWHEREgender='男';

为什么失效？如果gender='男'的记录占全表的 50%，那走索引还得回表 50% 的记录，不如全表扫描（减少随机 I/O）。

优化方案 1：如果业务允许，加其他条件缩小范围。

-- 优化后：加其他条件，缩小范围SELECT*FROMusersWHEREgender='男'ANDage=25;

优化方案 2：用覆盖索引（只查索引字段，不需要回表）。

-- 优化后：覆盖索引SELECTgenderFROMusersWHEREgender='男';

情况 7：优化器估计走索引更慢

问题：MySQL 优化器会根据统计信息估算走索引的成本，如果估计走索引更慢，会放弃索引。

-- 假设 users 表有 1000 万行，age=25 的有 900 万行-- MySQL 估计走索引要回表 900 万次，不如全表扫描EXPLAINSELECT*FROMusersWHEREage=25;

为什么失效？如果符合条件的记录太多，走索引要回表很多次（随机 I/O），全表扫描反而更快（顺序 I/O）。

优化方案 1：加LIMIT限制返回行数。

-- 优化后：加 LIMIT，MySQL 可能走索引SELECT*FROMusersWHEREage=25LIMIT10;

优化方案 2：用覆盖索引（只查索引字段，不需要回表）。

-- 优化后：覆盖索引SELECTageFROMusersWHEREage=25;

优化方案 3：FORCE INDEX强制走索引（不推荐，除非你确定索引更快）。

-- 强制走 idx_age 索引SELECT*FROMusersFORCEINDEX(idx_age)WHEREage=25;

实战建议

1. 每次写完 SQL 都用 EXPLAIN 检查

这是最重要的建议。很多线上慢查询，都是因为索引失效，导致全表扫描。

2. 避免对索引字段用函数

如果一定要用函数，考虑生成列 + 索引。

3. 查询条件的类型和索引字段的类型保持一致

避免隐式类型转换，导致索引失效。

4. 模糊查询尽量用后缀通配符

如果一定要用前缀通配符，考虑全文索引。

5. OR 连接的条件要给所有字段加索引

或者改用UNION代替OR。

6. 联合索引要遵循最左前缀原则

如果查询条件不遵循最左前缀，考虑单独给字段建索引。

7. 索引选择性太差时，考虑覆盖索引或者加其他条件缩小范围

如果优化器估计走索引更慢，会放弃索引。

总结

索引失效的七种情况：
1. 对索引字段用函数
1. 隐式类型转换
1. 模糊查询前缀通配符
1. OR 连接的条件中有字段没索引
1. 联合索引没遵循最左前缀
1. 索引选择性太差
1. 优化器估计走索引更慢
- 实战建议：每次写完 SQL 都用 EXPLAIN 检查、避免对索引字段用函数、查询条件的类型和索引字段的类型保持一致、模糊查询尽量用后缀通配符、OR 连接的条件要给所有字段加索引、联合索引要遵循最左前缀原则、索引选择性太差时考虑覆盖索引
  如果你能把这七种情况讲清楚，并且能优化索引失效的 SQL，面试官绝对觉得你有实战经验。