> **百日打怪升级 · 第25天** · 索引失效的典型场景

---

前两天有个哥们儿私信我，语气挺急的：

> "老司机救命——我给查询条件字段建了索引，explain 也显示 possible_keys，但 type 是 ALL，这什么情况？"

我让他把 SQL 发过来一看，大概长这样：

```sql
SELECT * FROM user WHERE DATE(create_time) = '2026-05-20';
```

create_time 字段上有索引，但查的时候包了一层 `DATE()` 函数。

**索引不是没用，是他写了个让 MySQL 没法用索引的写法。**

这种事我见得太多次了。面试里头"索引失效的场景"也是高频考点——背结论谁都会，但落到自己代码里该踩的坑一个没少。今天干脆把最常见的 6 种翻车现场摆出来，每个都带复现 SQL，你对着跑一遍，下次就不慌了。

---

## 翻车①：在索引列上用了函数

```sql
-- 失效 ❌
SELECT * FROM user WHERE DATE(create_time) = '2026-05-20';

-- 有效 ✅
SELECT * FROM user WHERE create_time >= '2026-05-20 00:00:00'
                      AND create_time <  '2026-05-21 00:00:00';
```

**为什么？** B+ 树里存的是原始值，不是 `DATE()` 之后的结果。你在条件里对列做计算，MySQL 只能把每个值算出来再比——全表扫描。

MySQL 8.0.13 之后支持函数索引，可以建 `CAST(create_time AS DATE)` 的虚拟列索引来绕过。但说实话，**改查询写法比建函数索引更优雅**——不挑版本、不占额外空间。

同样的坑还包括：`YEAR(create_time)`、`MONTH(create_time)`、`LENGTH(name)`、`ABS(amount)`……只要列出现在函数参数位置，索引就废。

---

## 翻车②：隐式类型转换

```sql
-- 假设 phone 字段是 VARCHAR 类型，建有索引

-- 失效 ❌
SELECT * FROM user WHERE phone = 13800138000;

-- 有效 ✅
SELECT * FROM user WHERE phone = '13800138000';
```

**为什么？** MySQL 遇到字符串列跟数字比，会把字符串转成数字。一旦转换发生在索引列上——跟翻车①一样，索引失效。

最蛋疼的是：MySQL **不报错**。查询能正常返回结果，你根本意识不到它走了全表扫描。

用 `EXPLAIN` 看一眼 Extra 列，如果出现 `Using where; Using index` 可能还在用索引，如果直接 type = ALL，恭喜踩坑。

> **判断技巧**：`SELECT '13800138000' = 13800138000` 返回 1 说明字符串被转成了数字，索引大概率会失效。

**还有一个贼隐蔽的兄弟：JOIN 时字符集不一致。**

```sql
-- 表 A（utf8mb4） JOIN 表 B（latin1）
-- 关联字段都有索引，但 JOIN 就是慢

SELECT * FROM a INNER JOIN b ON a.user_id = b.user_id;
```

**为什么？** 两张表的字符集不同，MySQL 会自动把低优先级（latin1）转成高优先级（utf8mb4）。如果转换发生在被驱动表的索引列上——索引失效，全表扫描。

最坑的是：**EXPLAIN 看不出来**。rows 显示正常，Extra 也没警告。得用 `SHOW WARNINGS` 才能看到隐式转换信息。

**判断方法**：
```sql
SHOW CREATE TABLE a;  -- 看 CHARSET 和 COLLATE
SHOW CREATE TABLE b;  -- 看 CHARSET 和 COLLATE，不一样就要小心
```

> **判断技巧**：`EXPLAIN` 看不出来这个坑，得 `SHOW WARNINGS` 才能看到"Converting character set"的隐式转换信息。

**解决方案**：
1. **统一字符集**——从根上解决，强烈推荐。
2. 无法马上改表，临时硬扛时，**把函数写在非索引的那一侧**：

```sql
-- 假设 a 表 utf8mb4（驱动表，a.user_id 不一定是索引）
--       b 表 latin1（被驱动表，b.user_id 有索引）

-- 正确写法：函数写在非索引列上，索引列保持干净 ✅
SELECT * FROM a INNER JOIN b ON CONVERT(a.user_id USING latin1) = b.user_id;

-- 错误写法：函数写在索引列上 = 索引报废 ❌
SELECT * FROM a INNER JOIN b ON a.user_id = CONVERT(b.user_id USING utf8mb4);
```

`CONVERT(a.user_id USING latin1)` 把 a 的值转成 latin1 再去匹配 b，b.user_id 的索引不受影响。但注意：latin1 不支持 emoji 等 4 字节字符，转换可能截断数据，**只应作为临时方案**。

> 这种坑通常在**老系统迁移**时出现——旧表 latin1，新表 utf8mb4，JOIN 一把梭，全员从头扫。

---

## 翻车③：OR 条件

```sql
-- 假设 id 有索引，name 也有索引

-- 可能失效 ❌
SELECT * FROM user WHERE id = 1 OR name = '张三';

-- 有效 ✅
SELECT * FROM user WHERE id = 1
UNION
SELECT * FROM user WHERE name = '张三';
```

**为什么？** OR 的语义是"任意一个条件匹配就行"，MySQL 必须把两个条件的结果合并。问题是：OR 两边的字段如果只有部分有索引，或者优化器觉得分别走索引再合并比全表扫描还慢，它就干脆不走索引了。

MySQL 5.0+ 引入了 **Index Merge** 优化，在某些情况下 OR 条件也能走索引（type = index_merge）。但这事儿很玄学——取决于数据分布、成本估算。

> **实操判断**：`EXPLAIN` 看到 `type=index_merge` 且 `rows` 很小 → MySQL 已经自动优化好了，不用改。看到 `type=ALL` → 立刻上 `UNION`，别犹豫。

---

## 翻车④：LIKE 通配符在开头

```sql
-- 失效 ❌
SELECT * FROM user WHERE name LIKE '%张三%';

-- 有效 ✅
SELECT * FROM user WHERE name LIKE '张三%';
```

**为什么？** B+ 树的索引是有序的。`'张三%'` 能定位到"张三"开头的范围，但 `'%张三%'` 不知道从哪开始找——因为前缀是模糊的。

但 MySQL 5.6+ 的 **ICP（Index Condition Pushdown，索引条件下推）** 能稍微缓解一下：存储引擎层用索引先过滤掉一部分明显不匹配 `%张三%` 的行，减少回表行数。Extra 列会显示 `Using index condition`。

注意：**`Using index condition` 不是覆盖索引。** `Using index` 才是覆盖索引（所有数据直接从索引拿，不用回表）。你的查询是 `SELECT *`，不可能覆盖索引所有列。ICP 只是减速带，不是刹车——type 依然是 ALL，该慢还是慢。

> 实际业务中 `'%关键词%'` 是搜索刚需。别指望 MySQL 硬扛——上 Elasticsearch 或者至少用 `FULLTEXT` 索引的 `MATCH ... AGAINST`。

---

## 翻车⑤：联合索引没用最左前缀

```sql
-- 联合索引：(city, age, name)

-- 有效 ✅
SELECT * FROM user WHERE city = '北京' AND age = 25;

-- 有效 ✅
SELECT * FROM user WHERE city = '北京' AND name = '张三'; -- city 走了索引，name 走不了

-- 失效 ❌
SELECT * FROM user WHERE age = 25 AND name = '张三';   -- 跳过了 city
```

**为什么？** 联合索引就像一本电话黄页——先按城市排，城市里再按年龄排，年龄里再按姓名排。你直接跳过城市查年龄和姓名，相当于让人在整本黄页里翻——翻不了。

**最左前缀原则**：查询条件必须从联合索引的最左列开始，不能跳过中间的列。

还有一种隐蔽的翻车：写成 `WHERE age = 25 AND city = '北京'`——你以为顺序反了会失效？**不会。** MySQL 优化器会帮你重排 WHERE 条件的顺序，只要条件里包含了最左列就行。

---

## 翻车⑥：NOT IN / != / <>

```sql
-- 失效 ❌
SELECT * FROM user WHERE status != 1;
SELECT * FROM user WHERE status <> 1;
SELECT * FROM user WHERE status NOT IN (1, 2);

-- 某些情况下有效（看数据分布）✅
SELECT * FROM user WHERE status IN (2, 3, 4);
```

**为什么？** 索引的目的是快速定位少量数据。`NOT IN` 和 `!=` 的语义是"排除少数，取剩下的大部分"，对优化器来说，**全表扫描比走索引再合并效率更高**——因为回表的随机 IO 成本可能比顺序扫描还高。

如果 `status = 1` 只占数据的 5%，改成 `SELECT * FROM user WHERE status IN (2,3,4,5,...)` 且选择性够高，索引是能用的。但实际中不建议依赖这个——数据分布一变，执行计划就变了。

---

## 🛠️ 排查工具箱：怎么证明索引失效了？

看完上面 6 种场景，回到你自己的 SQL 怎么查？三个 EXPLAIN 排查动作，即插即用：

```text
1. EXPLAIN 看 type 列：ALL → 基本就是全表扫描
2. EXPLAIN 看 key 列：NULL → 索引完全没被用上
3. EXPLAIN FORMAT=JSON 看 used_key_parts 数组：为空 → 索引有名字但没真正用
```

> 留个作业：找一条你觉得"应该走索引但好像没走"的业务 SQL，跑一遍上面三步。评论区告诉我你发现了什么。

---

## 面试一句话总结

面试官问"索引失效的场景有哪些"，不需要背 6 条。**记住这一句话就够了：**

> **索引失效的本质就一个原因——MySQL 无法从 B+ 树的左侧开始，精确地定位到你要的那一小段数据。**

逐一对照：
- 函数/类型转换 → 列的值被改了，B+ 树里找不到改完的值
- OR → 要合并多个范围，不如全扫
- `%like` → 不知道前缀是什么，无法定位起点
- 跳过最左列 → 不知道从哪一段开始翻
- NOT IN → 取大部分数据，不如全扫

**把"能不能从 B+ 树左侧开始定位"这个逻辑想通了，索引失效根本不用背。**

---

**下期预告**：覆盖索引与最左前缀原则 —— 同样是联合索引，为什么你的查询 extra 显示 Using index，我的显示 Using where？


"老司机救命 —— 我给查询条件字段建了索引，explain 也显示 possible_keys，但 type 是 ALL，这什么情况？"

面试官：你写的 SQL 凭什么不走索引？我给他复现了 6 种翻车现场

《百日打怪升级·第25天》聚焦MySQL索引失效6大高频翻车场景：函数操作（如`DATE()`）、隐式类型转换、`OR`滥用、`LIKE`前导通配符、联合索引未遵最左前缀、`!=/NOT IN`误用。每例附可复现SQL与优雅解法，助你精准避坑、秒判失效。

运维

MySQL索引失效的常见场景包括：对索引列使用函数（如DATE()）、隐式类型转换、OR条件未优化、LIKE以通配符开头、联合索引未遵循最左前缀原则，以及使用NOT IN或!=操作符。这些情况导致MySQL无法利用B+树的有序性进行快速定位，转而进行全表扫描。通过EXPLAIN分析SQL的执行计划，观察type列是否为ALL或key列是否为NULL，可以快速定位索引失效问题。优化查询写法或调整索引策略可有效提升性能。

MySQL

JSON

腾讯云数据库特惠，新用户首单0.5折起


文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

EdgeOne AI 安全实战专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

MySQL索引失效的常见场景包括：对索引列使用函数（如DATE()）、隐式类型转换、OR条件未优化、LIKE以通配符开头、联合索引未遵循最左前缀原则，以及使用NOT IN或!=操作符。这些情况导致MySQL无法利用B+树的有序性进行快速定位，转而进行全表扫描。通过EXPLAIN分析SQL的执行计划，观察type列是否为...

面试官：你写的 SQL 凭什么不走索引？我给他复现了 6 种翻车现场-腾讯云开发者社区-腾讯云

面试官：你写的 SQL 凭什么不走索引？我给他复现了 6 种翻车现场

面试官：你写的 SQL 凭什么不走索引？我给他复现了 6 种翻车现场

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐