MySQL 索引为什么失效？这10种场景一定要知道！

在日常学习中，很多同学都写过类似这样的表结构：

CREATE TABLE user (
id BIGINT PRIMARY KEY,
name VARCHAR(50),
phone VARCHAR(20),
age INT,
INDEX idx_phone(phone)
);

测试环境里只有几百条数据，你胸有成竹地敲下一行查询：

SELECT * FROM user WHERE phone = '13800000000';

毫秒级响应，速度拉满。然而，某一天系统突然爆出慢 SQL 告警，线上接口延迟激增。你赶紧登录服务器，在 SQL 前面加上了降维打击神器——EXPLAIN，定睛一看：

• type： ALL
• key： NULL

索引明明完好无损地躺在那里，MySQL 却视而不见，硬生生走了最暴力的全表扫描（Full Table Scan）。

很多开发者第一反应通常是：“坏了，MySQL 索引失效了！”

但作为一个对底层有极致追求的高级工程师，我们首先要纠正一个认知上的不严谨：索引本身是绝对不会凭空“失效”或坏掉的。它的物理 B+ 树结构依然在磁盘上安然无恙，只是 MySQL 的核心总指挥官——优化器（Optimizer），最终放弃了选择它。

核心底层前提：MySQL 优化器与成本计算模型（CBO）

要真正理解索引为什么不被使用，我们必须先卸下纯主观的“直觉思维”，换上 MySQL 优化器的基于成本的优化模型（Cost-Based Optimizer, CBO）视角。

MySQL 在执行任何一条 SQL 之前，优化器都会在后台偷偷打小算盘，计算各种执行路径的数学成本：

$$\text{总成本}=\text{I/O 成本（从磁盘/内存读取页的开销）}+\text{CPU 成本（数据比对、过滤、排序的开销）}$$

如果优化器精算后发现：走索引 + 频繁回表（Look-up）去聚簇索引拿整行数据的成本，竟然比直接全表扫描还要高，那么它就会毫不犹豫地无情抛弃索引。

举个最极端的类比：假设有一本 1000 页的技术书，你想找出书中所有“大于 1 岁”的人。由于 99% 的数据都满足条件，如果你先去查最后的索引目录（二级索引），拿到了 990 个页码，然后再不停地在目录和正文之间翻页切换（回表随机 I/O），这显然比你从第 1 页直接一口气死磕翻到第 1000 页（顺序全表扫描）要慢得多。

记住这两条金科玉律，后面所有的失效场景都可以用它们来推导：

深度拆解：必须要知道的 10 大索引失效场景

为了方便大家建立结构化的底层认知，我们将线上最常见的 10 大场景划分为三大业务阵营：

第一阵营：野蛮破坏 B+ 树有序性的基础低级错误

这一阵营的本质是：你的 SQL 写法强行改变了字段的值，或者抹平了 B+ 树在叶子节点上的严格局部有序特性，导致模型根本无法进行二分查找导航。

场景一：联合索引不满足“最左前缀原则”

假设我们对表中字段建立了一个黄金联合索引：INDEX idx_name_age_city(name, age, city)。

B+ 树在底层构建这路多路平衡树时，其排序规则是刚性的：先严格按照 name 排序；在 name 绝对相等的前提下，再按照 age 排序；在 age 也相等的前提下，最后看 city。

• 能走索引： WHERE name='Tom'、WHERE name='Tom' AND age=18
• 索引瘫痪： WHERE age=18、WHERE city='BJ'

由于你的查询条件直接掐掉了最左侧的 name 骨架，对于优化器而言，在没有首要排序列的指引下，后面的 age 和 city 在全局来看完全是散乱、无序的，B+ 树彻底沦为摆设，只能全表扫描。

场景二：在索引列上滥用各种自带函数

很多开发同学在线上编写数据统计时，非常喜欢写出如下代码：

— 极度危险的写法
SELECT * FROM user WHERE YEAR(create_time) = 2026;

这条 SQL 的意图很明显，想查 2026 年入职的用户。但是，create_time 索引树的叶子节点里存的是原始的精确时间（如 2026-06-12 20:34:00），MySQL 根本不可能在不经过计算的前提下，凭空知道哪个节点的 YEAR() 转化后等于 2026。

为了算出每一行的 YEAR() 值，优化器被迫发起全表扫描，挨个加载，在内存中执行函数比对。

• 工业级正确改写（范围查找）：

SELECT * FROM user
WHERE create_time >= '2026-01-01 00:00:00'
AND create_time < '2027-01-01 00:00:00';

这样写保持了索引列的干净，B+ 树能完美执行常规的 range 范围边界检索。

场景三：索引列直接参与了隐式的算术表达式运算

与函数异曲同工，有些同学喜欢在 SQL 里面展现精湛的数学功底：

SELECT * FROM user WHERE age + 1 = 20;

哪怕只是一个极其简单的 +1 加法，也属于代数运算。MySQL 底层的 B+ 树只认原本的 age 值。你给它加上 1 之后，原有的节点大小和指针顺序在代数空间里全部移位了，优化器无法直接在树上导航，再次退化为全表扫描。

• 规范写法： WHERE age = 19;（永远把计算挪到等号的右侧）

场景四：让人防不胜防的“隐式类型转换”

这是在 VARCHAR 字符串字段上极为高频的“连环连环大坑”。注意看我们的表结构，phone 字段定义的类型是 VARCHAR(20)。
当某位粗心的前端或后端同学扔过来一个纯数字的参数时：

— 隐式类型转换典型翻车示例
SELECT * FROM user WHERE phone = 13800000000;

在 MySQL 中，当字符串与数字进行比对时，为了实现兼容，MySQL 会硬性自动将字符串转换为数字。因此，这条 SQL 在底层被优化器默默强行改写成了：

SELECT * FROM user WHERE CAST(phone AS SIGNED) = 13800000000;

瞧，惊人的一幕发生了，这本质上又演变成了在索引列上调用 CAST() 函数！它完美踩中了场景二的雷区，导致优化器不得不放弃索引。

• 规范写法： WHERE phone = '13800000000';（字符串查询必须老老实实加单引号）

场景五：模糊查询 LIKE 以百分号 % 开头

在处理搜索框业务时，以下写法是家常便饭：

— 左模糊查询
SELECT * FROM user WHERE name LIKE '%Tom';

B+ 树是按照字符的 ASCII 码自左向右严格排序的。如果是 LIKE 'Tom%'（右模糊），优化器能顺着 T -> o -> m 的确定前缀，在树中像查字典一样精确定位到起始边界。

但是，一旦写成以 % 开头的左模糊 LIKE '%Tom'，意味着前面是什么字符完全是未知的。优化器连首字母是谁都抓瞎，无法在树形图里完成任何局部剪枝过滤，唯一的方法只能是把整棵树的所有节点全读出来做逐行字符串匹配。

第二阵营：多索引与多条件啮合时的“横向掐断”

这一阵营发生在复杂 SQL 场景下，多条件在联合索引树上的传导发生了“阻断”。

场景六：联合索引中，范围查询后面的列彻底沦为摆设

面试官极其喜欢深入考察的联合索引细节。假设我们依然使用联合索引 (name, age, city)，执行以下查询：

SELECT * FROM user
WHERE name = 'Tom'
AND age > 18
AND city = '北京';

这条 SQL 执行时，优化器能够非常愉快地利用 name 的等值条件快速切入，并且在 name='Tom' 的局部区域内，顺着有序的 age 树节点完美执行 age > 18 的范围截断。

但是，一旦 age 走了范围查询（如 >, <, BETWEEN），在这批被捞出的 age > 18 的离散数据中，第三个字段 city 在物理上其实就已经不再是有序排列的了。 因此，这路查询在底层的 B+ 树上，只有 name 和 age 真正吃到了索引的加速奖励，而最后的 city 无法继续利用树的有序特性，只能在内存里作为普通的 Filter 条件进行被动过滤。在设计联合索引时，务必把带范围的筛选列往后放！

场景七：OR 条件连接了没有索引或者异构的列

线上很多同学为了图省事，用 OR 来揉合查询逻辑：

SELECT * FROM user WHERE phone = '138' OR age = 18;

在 MySQL 中，如果你用 OR 连接条件，它的核心要求非常严苛：两边的字段必须同时拥有独立的索引。 如果 phone 建立了二级索引，而 age 上一丝不挂，那么对于优化器而言，即便通过 phone 索引捞出了数据，为了响应 OR age = 18 这一半的述求，它无论如何还是得去执行一遍全表扫描。既然两边各行其是，优化器为了省去合并临时结果集的极高 I/O 成本，索性直接在一开始就下达全表扫描的指令。

• 解耦策略： 在高并发大流量下，强力建议将复杂的 OR 语句拆分为多条独立的 SQL 语句，或者在应用层利用 UNION ALL 进行结果集的物理拼接。

第三阵营：基于 CBO 成本精算后的“主动断舍离”

这一阵营最能体现数据库设计者的妥协美学：SQL 写得没有任何语法错误，索引结构也对齐了，但优化器算完账后发现，用索引就是赔本买卖。

场景八：使用了低选择性的 !=、NOT IN 或 NOT LIKE 逆向操作

SELECT * FROM user WHERE age != 18;

这一类的逆向查询，在业务层往往意味着你想要收割系统里绝大部分（比如 90% 以上）的数据。

正如我们在一开始提到的“翻书理论”，如果满足 != 18 的记录占据了全表的大半壁江山，那么走二级索引带来的上百万次“回表现象”导致的随机磁盘 I/O 耗时，将远远大于顺序读取整张表磁盘页的耗时。优化器出于自我保护，会果断放弃索引。

注意： 并不是只要写 != 就一定失效。如果表中一共 100 万行数据，其中 age = 18 的刚好有 99.9 万行，!= 18 的只有 1000 行（高选择性），此时优化器算完账发现回表只有 1000 次，代价极小，它依然会高高兴兴地走索引。一切以数据分布的真实成本为准！

场景九：表中的总物理数据量实在太少

如果你的用户表正处于冷启动阶段，或者只是一张静态的系统配置小表，总共加起来只有几十条或者一两百条记录：
优化器一眼就能看穿底牌：我直接把这 2 个磁盘页读进内存全扫一遍，总共花费的 CPU 时钟周期可能只要几纳秒。如果我去先查二级索引页，再导航到聚簇索引回表，还要平白无故多读两个索引页。

因此，小表全表扫描是 MySQL 的完全正常且极其理智的现象，千万不要一看到小表走了 ALL 就大惊小怪地盲目调优。

场景十：死死掐住喉咙的 SELECT * 导致回表成本破防

我们看最经典的黄金对比：

— 语句 A：索引完好流利
SELECT id, phone FROM user WHERE phone LIKE '138%';

— 语句 B：索引瞬间破防
SELECT * FROM user WHERE phone LIKE '138%';

如果 phone 匹配到的用户非常多（比如有数万条）。对于语句 A 而言，它要拿的字段只有 id 和 phone。

别忘了，idx_phone 这个二级索引的叶子节点里，物理上存放的刚好就是 phone 值和它对应的主键 id**！这叫什么？这就是传说中的覆盖索引（Covering Index）**。语句 A 只需要把二级索引树读完就能完美交差，完全不需要执行任何回表操作，所以速度快到极致。

而语句 B 贪婪地写下了 SELECT *，它除了要 phone，还要拿 name 和 age。二级索引树里根本没有这两个字段。这意味着，每匹配到一条 phone 数据，MySQL 就必须拎着对应的 id，跑到聚簇索引树里去回表查询一次拿整行记录。当匹配数据量过大时，这数万次的回表成本瞬间压垮了优化器，导致它直接放弃索引，选择全表扫描。

工业级诊断：如何科学判定索引有没有失效？

在线上排查中，我们要熟练运用 EXPLAIN 工具，重点盯死以下三个黄金核心指标：

EXPLAIN 执行计划关键维度矩阵

面试官直通车：大厂面试的高分话术该如何编排？

如果面试官在现场抛出：“你在项目中遇到过索引失效吗？能跟我聊聊 MySQL 为什么会放弃索引，以及有哪些典型场景吗？”

请丢掉单调、干瘪的“第 1 点、第 2 点”式的背诵，按照以下具备高级系统架构调优视角的逻辑线进行高能作答：

大厂高分回答模板：
“在关系型数据库的底层工程落地中，我们首先要确立一个硬性认知：索引本身并没有失效，‘失效’的物理本质是 MySQL 优化器基于成本模型（CBO）精算后，主动放弃了走二级索引的路径。
在我的知识体系和排查实战中，我通常把导致优化器放弃索引的诱因划分为两大核心哲学原则：
第一核心原则，是 SQL 写法由于人为因素，野蛮破坏了 B+ 树原有的严格局部有序特性。 > 典型场景包括：在联合索引中违反了『最左匹配原则』导致树形导航抓瞎；或者在索引列上直接调用了 YEAR()、CAST() 等各类内置函数或参与了算术表达式运算，这会让优化器无法在不全表遍历的前提下推导出计算结果。此外，像以百分号开头的左模糊 LIKE ‘%Tom’ 查询，由于前缀未知，也会彻底掐断 B+ 树的剪枝过滤能力。
第二核心原则，是数据分布特征导致回表的随机 I/O 成本全面破防。
这其中最经典的刺客就是写下了贪婪的 SELECT *，当联合索引中范围查询后的列阻断了后续排序，或者使用了低选择性的 !=、NOT IN 时，若匹配到的数据量占据了整表极高的比例，优化器精算完发现『走二级索引 + 海量回表』的开销，远远大于单次顺序读取整表磁盘页的开销。为了实现综合性能最优，它会断舍离选择全表扫描。
在日常排查中，我会雷打不动地架设 EXPLAIN 指令，重点观察 type 字段是否恶化为 ALL 或 index，以及 Extra 中是否冒出危险的 Using filesort。在整改策略上，我会通过强力推行『覆盖索引』、将计算右移、以及在应用层解耦复杂的 OR 或联合索引范围，来确保 MySQL 始终跑在低成本的黄金轨道上。”

总结与工程哲学

很多人在学完 MySQL 索引后，往往会得出一个极其单纯的执念：“建立索引 = 查询变快”。

但在千变万化的严肃商业级生产环境里，SQL 能否跑得流畅，取决于你的代码写法、表中的数据分布特征、以及优化器成本精算三者之间的精妙博弈。

死记硬背那 10 种场景是永远背不完的。只有当你在落笔写下每一行 SQL 时，脑海里都能自发浮现出那棵 16KB 磁盘页构筑的、扁平多叉的 B+ 树，并能自觉地顺着优化器的视角去盘算 I/O 账本时，你才算真正掌握了数据库底层调优的真谛。

想了解更多关系型数据库底层工程调优与架构落地实战？欢迎关注、点赞、收藏本博客！

下一期我们将正式进入更加硬核的“MySQL 锁机制与事务隔离深水区”，带大家深度拆解为什么一行普通的 UPDATE 会莫名其妙触发线上死锁（Deadlock），以及 InnoDB 的 MVCC（多版本并发控制）和 Gap 间隙锁是如何在底层防范幻读的。

如果在你的开发生涯中，遇到过哪些让你通宵排查、顿悟良久的奇葩慢 SQL 翻车案例，欢迎在评论区留言交流！点击关注不迷路，我们下期见！