MySQL 索引失效

面试频率：★★★★★
工作频率：★★★★★

⚡ 30 秒速记

所谓：

索引失效

并不是：

索引坏了

而是：

SQL 无法有效利用索引快速定位数据，或者优化器认为走索引成本太高，最终选择了其他执行方式，例如全表扫描。

常见情况：

1. 对索引列使用函数

2. 隐式类型转换

3. LIKE 左边使用 %

4. 联合索引不满足最左前缀

5. OR 两边索引情况不同

6. 查询返回数据太多 / 索引选择性太低

最重要的一句话：

有索引 ≠ 一定走索引，最终由 MySQL 优化器根据成本选择执行计划。

一、什么叫索引失效？

假设：

CREATE INDEX idx_name
ON user(name);

正常查询：

SELECT *
FROM user
WHERE name = '张三';

可以利用：

B+ Tree
   ↓
快速定位张三
   ↓
找到对应数据

但是某些 SQL：

无法利用B+ Tree的有序性快速定位

或者：

虽然可以利用索引

↓

但是需要查询的数据太多

↓

走索引成本反而更高

优化器就可能：

不走这个索引

↓

选择全表扫描等方式

这就是平时所说的：

索引失效

二、对索引列使用函数

假设：

CREATE INDEX idx_name
ON user(name);

正常：

SELECT *
FROM user
WHERE name = '张三';

可以：

根据name

↓

直接利用B+ Tree定位

但是：

SELECT *
FROM user
WHERE LEFT(name, 1) = '张';

变成：

name
 ↓
先执行LEFT()
 ↓
得到结果
 ↓
再判断是不是张

普通：

INDEX(name)

保存的是：

原始name值

而不是：

LEFT(name,1)

因此通常不能直接按照普通 name 索引快速定位目标范围。

可能导致：

无法有效利用普通索引定位

三、为什么函数会影响索引？

索引：

张三
张五
张小明
李四
王五

按照：

name原始值

排序。

但是查询：

WHERE LEFT(name, 1) = '张'

比较的是：

LEFT(name,1)

而不是：

name

所以普通索引原来的排序方式：

不一定能直接用于这个表达式的快速定位

四、常见函数场景

例如：

WHERE YEAR(create_time) = 2026;

如果：

create_time

有普通索引，也可能影响索引的高效利用。

更常见的优化方式是改写成范围：

WHERE create_time >= '2026-01-01'
AND create_time < '2027-01-01';

这样：

create_time

本身没有被函数包裹。

可以直接利用：

B+ Tree有序性

进行范围查询。

五、隐式类型转换

假设字段：

phone VARCHAR(20)

并且：

CREATE INDEX idx_phone
ON user(phone);

正确：

SELECT *
FROM user
WHERE phone = '13800138000';

因为：

phone

=

VARCHAR

查询参数：

'13800138000'

=

字符串

类型一致。

如果写：

SELECT *
FROM user
WHERE phone = 13800138000;

右边：

数字

左边：

VARCHAR

MySQL 可能需要：

隐式类型转换

如果转换作用到了：

索引列

就可能影响：

索引的正常快速定位

六、隐式转换怎么避免？

最简单：

Java 参数类型、数据库字段类型、SQL 参数类型尽量保持一致。

例如数据库：

phone VARCHAR

Java：

String phone;

SQL：

WHERE phone = '13800138000'

而不是：

WHERE phone = 13800138000

七、LIKE 左边有 %

这个非常经典。

假设：

CREATE INDEX idx_name
ON user(name);

查询：

WHERE name LIKE '张%';

通常可以利用索引进行范围定位。

因为：

张三
张五
张小明
张某某

具有：

确定的左侧前缀

可以找到：

以张开头的一段范围

八、LIKE '%张' 为什么不一样？

查询：

WHERE name LIKE '%张';

可能的数据：

老张

小张

张

王小张

开头是什么：

不知道

因此普通 B+ Tree：

很难根据左侧前缀快速确定查询范围

所以通常：

无法利用普通索引快速定位

同样：

WHERE name LIKE '%张%';

也没有：

确定的左侧前缀

所以通常：

无法利用普通B+ Tree索引快速定位

九、LIKE 怎么记？

LIKE '张%'

↓

左边确定

↓

通常可以利用索引范围查询

LIKE '%张'

↓

左边不确定

↓

通常无法利用普通索引快速定位

LIKE '%张%'

↓

左边不确定

↓

通常无法利用普通索引快速定位

简单记：

左边不能随便%

十、联合索引不满足最左前缀

假设：

CREATE INDEX idx_name_age_sex
ON user(name, age, sex);

索引：

name → age → sex

正常：

WHERE name = ?

可以。

WHERE name = ?
AND age = ?

也可以。

WHERE name = ?
AND age = ?
AND sex = ?

也可以。

但是：

WHERE age = ?

缺少：

最左边name

因此：

无法按照(name,age,sex)

的最左前缀进行正常快速定位

十一、不满足最左前缀 = 一定不走索引吗？

不是。

这个不要死记。

例如：

INDEX(name, age)

执行：

SELECT age
FROM user
WHERE age = 20;

虽然：

缺少最左name

但是：

age就在索引里面

优化器在某些情况下：

可能选择扫描整个索引

而不是：

扫描整张表

因为：

索引页可能比完整数据页更小

所以更准确的说法：

不满足最左前缀时，通常无法利用联合索引进行高效的索引定位，但不代表 MySQL 绝对不会扫描这个索引。

十二、OR 会导致索引失效吗？

不能简单说：

OR

=

索引失效

例如：

WHERE name = '张三'
OR age = 20;

假设：

name有索引

age没有索引

MySQL 可能判断：

name可以走索引

但是

age还是要扫描大量数据

最后：

全表扫描成本更低

于是：

直接全表扫描

十三、OR 两边都有索引呢？

例如：

name有索引

age也有索引

查询：

WHERE name = '张三'
OR age = 20;

MySQL 可能：

分别使用两个索引

然后：

合并结果

这类执行方式可能涉及：

Index Merge

所以：

出现OR

不代表：

100%索引失效

最终还是：

看执行计划

十四、!= 一定导致索引失效吗？

例如：

WHERE age != 20;

很多文章会直接说：

!=

↓

索引失效

这个说法：

不准确

真正的问题往往是：

查询结果太多

假设：

100万条数据

其中：

age = 20

只有1万条

那么：

WHERE age != 20;

意味着：

查询99万条

如果走二级索引：

找到99万个索引记录
      ↓
得到大量主键
      ↓
大量回表
      ↓
拿完整数据

成本可能非常高。

优化器可能判断：

还不如直接全表扫描

所以：

选择全表扫描

十五、NOT IN 一定失效吗？

同样：

WHERE id NOT IN (1,2,3);

不能简单说：

NOT IN一定索引失效

还是需要考虑：

返回多少数据

索引选择性

是否回表

统计信息

执行成本

最终由：

MySQL优化器

决定。

十六、索引选择性

这个概念非常重要。

假设：

sex字段

只有：

0
1

1000万用户：

男：500万

女：500万

给：

sex

建立索引：

CREATE INDEX idx_sex
ON user(sex);

执行：

SELECT *
FROM user
WHERE sex = 1;

会查询：

500万条数据

如果走二级索引：

idx_sex
   ↓
找到500万个索引记录
   ↓
得到500万个主键
   ↓
大量回表
   ↓
拿完整行数据

可能非常贵。

优化器可能觉得：

直接扫描整张表

反而更快

所以：

即使sex有索引

↓

也可能不走

十七、什么是高选择性？

例如：

身份证号
手机号
订单号
用户ID

可能：

一个值只对应很少的数据

例如：

1000万条数据

↓

phone = 138xxxx

↓

只找到1条

这种字段：

选择性高

很适合索引快速定位。

十八、什么是低选择性？

例如：

性别

是否删除

状态

可能只有：

0
1

或者：

大量数据：

值都一样

这种：

选择性低

单独建立索引：

不一定有很好的查询收益

十九、为什么有索引 MySQL 还不走？ ⭐⭐⭐⭐⭐

这是今天最重要的问题。

错误理解：

字段有索引

↓

MySQL必须使用

不对。

MySQL 有：

优化器

优化器会比较：

方案A：

走索引
↓
查B+ Tree
↓
找到大量主键
↓
大量回表


VS


方案B：

直接全表扫描

然后估算：

哪个成本低？

如果：

全表扫描成本更低

那么：

即使存在索引

↓

MySQL也可能不使用

二十、索引失效真正的核心 ⭐⭐⭐⭐⭐

不要只背：

函数

LIKE %

OR

!=

类型转换

真正应该理解：

SQL
 ↓
能不能利用B+ Tree的有序性
快速定位数据？
 ↓
        ┌───────┴───────┐
        ↓               ↓
       能              不能
        ↓               ↓
   有机会走索引      可能大量扫描
        ↓
        ↓
优化器计算执行成本
        ↓
   ┌────┴────┐
   ↓         ↓
索引便宜    全表扫描便宜
   ↓         ↓
走索引     全表扫描

二十一、为什么 B+ Tree 有序性这么重要？

你前面学过：

B+ Tree叶子节点

↓

有序排列

例如：

查询：

WHERE id = 30;

可以：

快速定位30

查询：

WHERE id BETWEEN 20 AND 50;

也可以：

找到20
 ↓
沿叶子节点
 ↓
30
 ↓
40
 ↓
50

所以：

等值查询

范围查询

前缀查询

很多情况下都能利用：

B+ Tree有序性

二十二、为什么函数可能破坏这种能力？

原来：

name索引：

张三
张五
张六
李四
王五

查询：

WHERE name = '张三'

可以直接：

定位张三

但：

WHERE LEFT(name,1) = '张'

变成：

对name计算
↓
得到新的表达式结果
↓
再比较

普通：

INDEX(name)

并不是按照：

LEFT(name,1)

组织的。

所以：

原来的索引排序

可能无法直接用于快速定位

二十三、索引失效和回表的关系

有时候：

索引本身可以定位

但因为：

回表太多

优化器仍然：

放弃索引

例如：

SELECT *
FROM user
WHERE sex = 1;

索引：

idx_sex

确实可以找到：

sex = 1

但是：

500万条
 ↓
500万个主键
 ↓
大量回表

成本太高。

于是：

全表扫描

可能更划算。

所以：

索引失效

不一定是：

索引不能用

还可能是：

索引能用

↓

但是太贵

↓

优化器不想用

二十四、怎么判断到底有没有走索引？

不要猜。

使用：

EXPLAIN
SELECT *
FROM user
WHERE name = '张三';

重点看：

type

possible_keys

key

rows

Extra

例如：

possible_keys

↓

可能使用哪些索引

key

↓

最终实际选择哪个索引

如果：

key = NULL

通常表示：

没有选择索引

后面学习：

EXPLAIN

会详细讲。

二十五、常见索引失效总结

1. 索引列使用函数

例如：

WHERE YEAR(create_time) = 2026;

可能影响普通索引快速定位。

2. 隐式类型转换

例如：

phone VARCHAR

却写：

WHERE phone = 13800138000;

可能影响索引使用。

3. LIKE 左边 %

例如：

LIKE '%张'

LIKE '%张%'

通常无法利用普通 B+ Tree 索引快速定位。

4. 不满足最左前缀

索引：

(name,age)

查询：

WHERE age = 20;

通常无法利用联合索引进行高效定位。

5. OR

如果 OR 两边：

索引情况不同

可能导致优化器选择：

全表扫描

但：

OR ≠ 一定索引失效

6. 返回数据太多

例如：

WHERE sex = 1;

50%的数据

大量：

回表

优化器可能：

直接全表扫描

二十六、最终知识链

现在把前面学的索引知识全部串起来：

MySQL索引
    ↓
B+ Tree
    ↓
利用有序性快速查询
    ↓
┌──────────────┐
↓              ↓
聚簇索引      二级索引
↓              ↓
完整行        索引列+主键
               ↓
            需要完整数据
               ↓
              回表

然后：

联合索引
   ↓
(A,B,C)
   ↓
最左匹配

查询字段全部在索引：

覆盖索引
   ↓
避免回表

如果：

函数

隐式类型转换

LIKE前导%

不满足最左前缀

可能：

无法有效利用B+ Tree快速定位

即使：

可以利用索引

还要：

优化器计算成本

最终：

索引成本低
↓

走索引


全表扫描成本低
↓

全表扫描

🎤 面试回答

问：

哪些情况可能导致 MySQL 索引失效？

答：

常见情况包括对索引列使用函数、发生隐式类型转换、LIKE 使用前导 %、联合索引不满足最左前缀，以及查询返回数据量过大、索引选择性太低等。

另外不能简单认为 OR、!=、NOT IN 一定导致索引失效。MySQL 优化器最终会根据统计信息和执行成本选择执行计划。

所以索引失效的核心，一方面是 SQL 是否还能利用 B+ Tree 的有序性快速定位数据，另一方面是优化器判断走索引的成本是否低于其他执行方式。

🎯 面试追问

Q1：什么叫索引失效？

答：

不是：

索引坏了

而是：

SQL无法有效利用索引快速定位

或者

优化器认为使用索引成本太高

↓

最终没有选择该索引

Q2：为什么索引列使用函数可能影响索引？

答：

因为普通索引：

按照原始字段值组织

而：

函数(字段)

比较的是：

计算后的结果

可能无法直接利用普通索引原来的有序性进行快速定位。

Q3：LIKE 哪种情况通常可以走索引？

答：

LIKE '张%'

通常可以利用：

前缀范围

进行索引查询。

Q4：为什么 LIKE '%张%' 通常不能快速利用普通索引？

答：

因为：

左侧前缀不确定

无法直接确定 B+ Tree 中：

从哪里开始查

Q5：OR 一定导致索引失效吗？

答：

不一定

两边都有合适索引时：

MySQL 可能使用：

Index Merge

等方式。

Q6：!= 一定导致索引失效吗？

答：

不一定

真正需要考虑：

返回数据量

索引选择性

回表成本

优化器成本估算

Q7：为什么字段有索引，MySQL 还是可能全表扫描？

答：

因为：

MySQL优化器会计算成本

如果：

走索引
+
大量回表

比：

全表扫描

还贵：

优化器可能：

直接全表扫描

Q8：为什么性别字段单独建立索引效果可能不好？

答：

因为：

值非常少

↓

大量数据拥有相同值

↓

选择性低

查询可能返回：

大量数据

导致：

大量回表

优化器可能：

不使用索引

Q9：怎么判断 SQL 到底有没有走索引？

答：

EXPLAIN SQL;

重点看：

key
type
rows
Extra

等信息。

⚠️ 易错点

1. 不要背“出现 OR 就失效”

错误：

OR
=
一定索引失效

正确：

看两边索引情况

+

看优化器执行计划

2. 不要背“!= 一定失效”

错误：

!=
=
一定不用索引

正确：

可能使用

也可能因为返回数据太多

↓

优化器选择全表扫描

3. 有索引不代表一定使用

存在索引

≠

最终一定走索引

因为：

优化器会计算成本

4. 使用索引也不代表一定快

如果：

索引查询出几百万条

↓

几百万次相关回表/数据页访问

可能：

比全表扫描还慢

所以：

索引不是越多越好

也不是走索引就一定快

❓ 自测

什么叫索引失效？
索引失效是不是索引坏了？
为什么索引列使用函数可能影响索引？
YEAR(create_time)=2026 可以怎么优化？
什么是隐式类型转换？
VARCHAR 字段为什么最好使用字符串参数查询？
LIKE '张%' 为什么通常可以利用索引？
LIKE '%张' 为什么通常无法利用普通索引快速定位？
LIKE '%张%' 呢？
联合索引不满足最左前缀会怎样？
不满足最左前缀是不是代表 MySQL 绝对不会扫描这个索引？
OR 一定导致索引失效吗？
!= 一定导致索引失效吗？
NOT IN 一定导致索引失效吗？
为什么 sex 字段有索引也可能不走？
什么是索引选择性？
什么样的字段选择性比较高？
什么样的字段选择性比较低？
为什么大量回表可能导致优化器放弃索引？
有索引是不是一定走索引？
走索引是不是一定比全表扫描快？
MySQL 根据什么决定是否使用索引？
怎么确认一条 SQL 实际使用了哪个索引？
索引失效和 B+ Tree 的有序性有什么关系？

分享

MySQL索引失效

MySQL 索引失效

⚡ 30 秒速记

一、什么叫索引失效？

二、对索引列使用函数

三、为什么函数会影响索引？

四、常见函数场景

五、隐式类型转换

六、隐式转换怎么避免？

七、LIKE 左边有 %

八、LIKE '%张' 为什么不一样？

九、LIKE 怎么记？

十、联合索引不满足最左前缀

十一、不满足最左前缀 = 一定不走索引吗？

十二、OR 会导致索引失效吗？

十三、OR 两边都有索引呢？

十四、!= 一定导致索引失效吗？

十五、NOT IN 一定失效吗？

十六、索引选择性

十七、什么是高选择性？

十八、什么是低选择性？

十九、为什么有索引 MySQL 还不走？ ⭐⭐⭐⭐⭐

二十、索引失效真正的核心 ⭐⭐⭐⭐⭐

二十一、为什么 B+ Tree 有序性这么重要？

二十二、为什么函数可能破坏这种能力？

二十三、索引失效和回表的关系

二十四、怎么判断到底有没有走索引？

二十五、常见索引失效总结

1. 索引列使用函数

2. 隐式类型转换

3. LIKE 左边 %

4. 不满足最左前缀

5. OR

6. 返回数据太多

二十六、最终知识链

🎤 面试回答

🎯 面试追问

Q1：什么叫索引失效？

Q2：为什么索引列使用函数可能影响索引？

Q3：LIKE 哪种情况通常可以走索引？

Q4：为什么 LIKE '%张%' 通常不能快速利用普通索引？

Q5：OR 一定导致索引失效吗？

Q6：!= 一定导致索引失效吗？

Q7：为什么字段有索引，MySQL 还是可能全表扫描？

Q8：为什么性别字段单独建立索引效果可能不好？

Q9：怎么判断 SQL 到底有没有走索引？

⚠️ 易错点

1. 不要背“出现 OR 就失效”

2. 不要背“!= 一定失效”

3. 有索引不代表一定使用

4. 使用索引也不代表一定快

❓ 自测

评论