【Mysql】索引失效的原因

你这份总结整体是对的，我按“为什么会失效”来解释一遍。核心记住一句话：

索引能不能用，本质看 MySQL 能不能直接拿索引树里的值去定位数据。
如果你把索引列“加工”了，或者查询条件不符合索引树的排序规则，MySQL 就很难走索引。

1. 对索引字段使用函数

比如：

select*fromt_userwheresubstring(name,1,1)='张';

假设name有索引。

索引树中存的是原始的name值，比如：

'张三' '李四' '王五'

但是你的查询条件不是：

name='张三'

而是：

substring(name,1,1)='张'

这意味着 MySQL 需要对每一行的name执行函数，然后判断结果是不是'张'。

也就是说，它不能直接在索引树中查找，只能把数据一条条拿出来算：

substring('张三', 1, 1) substring('李四', 1, 1) substring('王五', 1, 1)

所以索引容易失效。

类似的还有：

wheredate(create_time)='2026-05-27'wherelower(username)='tom'whereyear(create_time)=2026

更推荐写法是：

wherecreate_time>='2026-05-27 00:00:00'andcreate_time<'2026-05-28 00:00:00'

这样create_time没有被函数包裹，索引可以正常使用。

2. 对索引字段使用表达式计算

比如：

select*fromt_userwhereage+1=19;

假设age有索引。

虽然这个条件等价于：

whereage=18;

但是 MySQL 看到的是：

age+1=19

它需要先对每一行的age做计算：

18 + 1 20 + 1 30 + 1

再判断是否等于 19。

所以不容易直接走age索引。

更推荐写法：

select*fromt_userwhereage=18;

再比如：

whereid*2=10

应该改成：

whereid=5

总结一下：

where索引列=某个值

通常能走索引。

where索引列+计算=某个值

容易导致索引失效。

3. 隐式类型转换导致索引失效

你举的例子很好：

select*fromt_userwherephone=1300000001;

假设phone是varchar类型，并且有索引。

但是你查询的时候写的是数字，没有加引号。

MySQL 在比较字符串和数字时，会把字符串转成数字再比较。

所以它可能相当于执行：

select*fromt_userwhereCAST(phoneASsignedint)=1300000001;

也就是说，索引字段phone被函数转换了。

一旦对索引字段做了转换，就容易导致索引失效。

正确写法应该是：

select*fromt_userwherephone='1300000001';

这里有一个面试重点：

字段是什么类型，查询条件就尽量用什么类型。

如果字段是varchar，条件值就加引号；
如果字段是int，条件值就不要加引号。

4. Like 以通配符开头

假设name有索引。

可以走索引的情况：

select*fromt_userwherenamelike'张%';

因为它表示查找所有以“张”开头的数据。

索引树中的字符串是按顺序排列的，比如：

张三 张伟 张敏 李四 王五

MySQL 可以定位到“张”开头的位置，然后向后扫描。

但是如果写成：

select*fromt_userwherenamelike'%三';

或者：

select*fromt_userwherenamelike'%三%';

这就不行了。

因为%三表示前面是什么不知道，只要最后是“三”就行。

比如：

张三 王三 李三 赵三

MySQL 无法根据索引树的前缀顺序快速定位，只能一个个判断，所以索引容易失效。

总结：

like'abc%'-- 可以利用索引like'%abc'-- 一般不能利用索引like'%abc%'-- 一般不能利用索引

5. 联合索引与最左前缀法则

假设有联合索引：

index(a,b,c)

它不是给a、b、c各自建了三个独立索引，而是建立了一棵联合索引树。

这棵树的排序规则是：

先按 a 排序 a 相同，再按 b 排序 b 相同，再按 c 排序

类似这样：

a b c 1 1 1 1 1 2 1 2 1 1 2 3 2 1 1 2 2 1 3 1 1

所以联合索引(a, b, c)的核心规则是：

必须先有 a，才能进一步利用 b；必须先有 a、b，才能进一步利用 c。

这些可以用到联合索引

wherea=1;

可以用，因为从最左边的a开始了。

wherea=1andb=2;

可以用，符合a -> b的顺序。

wherea=1andb=2andc=3;

可以用，完整符合a -> b -> c。

whereb=2anda=1;

也可以用。

因为 SQL 里条件顺序不重要，优化器会帮你调整。它本质还是：

wherea=1andb=2;

所以能用。

wherec=3andb=2anda=1;

也可以用，本质还是同时有a、b、c。

wherea=1andc=3;

这个要注意。

它可以用到联合索引中的a，但是通常用不到b，因为中间缺了b。

也就是说，不是整个联合索引完全失效，而是：

a 可以用 c 一般不能用于精准定位

所以更准确地说：

wherea=1andc=3;

索引不会完全失效，但只能利用到 a 这一部分。

这些不符合最左前缀法则

whereb=2;

不行，因为缺少最左边的a。

wherec=3;

不行，因为缺少a和b。

whereb=2andc=3;

也不行，因为还是缺少a。

原因是联合索引树首先按照a排序。

如果你不提供a，MySQL 就无法确定从索引树的哪个范围开始找。

举个简单类比：

通讯录按照：

姓氏 -> 名字 -> 年龄

排序。

如果你知道姓氏是“张”，就很容易找到所有张姓的人。

如果你知道“姓张，名字叫三”，就更容易找。

但如果你只知道“名字叫三”，不知道姓氏，那就很难根据这个排序快速定位。因为名字叫“三”的人可能分散在不同姓氏下面。

联合索引也是一样。

6. OR 条件导致索引失效

比如：

select*fromt_userwhereid=1orage=18;

假设：

id 有索引 age 没有索引

那么问题来了：

id=1

这部分可以走索引。

但是：

age=18

这部分不能走索引。

因为是OR，表示只要满足其中一个条件就可以。

MySQL 必须找出：

id = 1 的数据 或者 age = 18 的数据

但age没有索引，它还是要全表扫描去找age = 18的数据。

既然都要全表扫描了，MySQL 可能就干脆不走id的索引了，直接全表扫描。

所以总结：

whereid=1orage=18;

如果id有索引，age没有索引，容易导致索引失效。

如果id和age都有索引，MySQL 有可能使用索引合并，但不一定每次都会用，最终还是要看优化器判断成本。

最后帮你整理成面试版

索引失效常见情况有：

1. 对索引列使用函数

wheredate(create_time)='2026-05-27'

1. 对索引列进行表达式计算

whereage+1=18

1. 发生隐式类型转换

phone 是varcharwherephone=1300000001

应该写成：

wherephone='1300000001'

1. like 以%开头

wherenamelike'%张'wherenamelike'%张%'

1. 联合索引不符合最左前缀法则

联合索引(a, b, c)，必须从a开始使用。

whereb=2wherec=3whereb=2andc=3

都不符合。

1. OR 两边有一边没有索引

whereid=1orage=18

如果age没索引，可能导致全表扫描。

你可以这样理解索引失效：

索引就像一本按规则排序的字典。只要查询条件破坏了这个排序规则，或者让 MySQL 不能直接按索引值查找，就可能导致索引失效。