16.面试高频：MySQL 索引失效常见场景有哪些？如何用 EXPLAIN 排查？

大家好，我是牛哥。

在后端面试里，MySQL 索引失效绝对是高频考点。

索引本是 MySQL 的性能加速器，但一旦踩中失效场景，它就会瞬间罢工，让数据库被迫陷入全表扫描的泥潭。

今天，牛哥就结合真实业务场景和面试高频考点，把 MySQL 索引失效的常见场景、EXPLAIN 排查技巧一次性说清楚。

索引为啥会失效？

要搞懂索引失效，首先得明白索引的核心作用：

它本质是 MySQL 为了快速定位数据，建立的 “有序数据结构”，就像书籍的目录，能帮我们快速找到目标章节。

一旦查询操作破坏了这个 “有序结构” 的匹配规则，索引就无法发挥作用，只能退回全表扫描，也就是所谓的“索引失效”。

为了更直观地理解 MySQL 索引失效的本质，我们以一张标准化的user业务表为载体，结合实际业务查询场景，逐一拆解索引失效的核心问题与解决方案。

user 表结构

CREATE TABLE `user` (
  `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '用户ID（主键，自增）',
  `username` VARCHAR(50) NOT NULL COMMENT '用户名（唯一，普通索引）',
  `phone` VARCHAR(20) NOT NULL COMMENT '手机号（唯一，普通索引）',
  `create_time` DATETIME NOT NULL COMMENT '注册时间（普通索引）',
  `gender` CHAR(1) NOT NULL DEFAULT '0' COMMENT '性别（0-未知/1-男/2-女）',
  `age` INT(3) DEFAULT NULL COMMENT '年龄',
  `email` VARCHAR(100) DEFAULT NULL COMMENT '邮箱',
  `status` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '状态（1-正常/0-禁用）',
  -- 索引配置
  PRIMARY KEY (`id`), -- 主键索引（聚簇索引）
  KEY `idx_username` (`username`), -- 普通索引：用户名
  KEY `idx_phone` (`phone`), -- 普通索引：手机号
  KEY `idx_create_time` (`create_time`), -- 普通索引：注册时间
  KEY `idx_username_age` (`username`, `age`) -- 联合索引：用户名+年龄
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';

MySQL 索引失效的 12 大场景

在对 user 表进行业务查询时，任何一类高频查询都可能因操作不当触发索引失效。

但这些查询并非没有共性，按照问题产生的原因来划分，这些导致索引失效的查询可以归为三类：

第一类：字段操作类失效

这类问题发生在我们对索引字段进行了额外处理，导致索引无法发挥其有序性优势。

场景 1：对索引字段使用函数

日常工作中，我们经常需要按日期查询数据。比如想查询 2024 年 1 月 1 日注册的用户：

-- 错误写法
SELECT * FROM user WHERE DATE(create_time) = '2024-01-01';

索引失效是因为 DATE 函数改变了字段的原始值，破坏了索引的有序性，导致 MySQL 无法使用idx_create_time索引。

正确的做法是直接使用原始字段进行范围查询：

-- 正确写法
SELECT * FROM user WHERE create_time BETWEEN '2024-01-01 00:00:00' AND '2024-01-01 23:59:59';

这样写可以充分利用idx_create_time索引，快速定位到指定时间段的记录。

场景 2：索引字段参与算术运算

有时我们需要查询某个 ID 附近的用户，比如想查询满足 “ID+1=1000” 条件的用户：

-- 错误写法
SELECT * FROM user WHERE id + 1 = 1000;

索引失效是因为这里 id 是主键索引，但我们对它进行了加法运算。这会导致 MySQL 无法使用主键索引，因为运算改变了索引的有序性。

正确的做法是将运算移到等式的右侧：

-- 正确写法
SELECT * FROM user WHERE id = 1000 - 1;

这样写可以正常使用主键索引，快速定位到 ID 为 999 的记录。

场景 3：使用隐式类型转换

当查询手机号时，我们可能会习惯性地不加引号：

-- 错误写法
SELECT * FROM user WHERE phone = 13800138000;

索引失效在于 phone 是字符串类型，查询时用数字会触发隐式类型转换，相当于对索引字段做函数操作，破坏了有序性

正确的做法是保持查询值的类型与字段类型一致：

-- 正确写法
SELECT * FROM user WHERE phone = '13800138000';

加上引号后，查询值为字符串类型，与phone字段类型匹配，可以正常使用idx_phone索引。

第二类：查询条件类失效

这类问题往往发生在我们的查询条件不符合索引的匹配规则时，索引无法发挥定位作用，只能退回全表扫描。

场景 4：使用 OR 连接非索引字段

例如业务场景中需要查询两类用户 — 要么用户名是 "zhangsan"，要么年龄是 25 岁：

-- 错误写法
SELECT * FROM user WHERE username = 'zhangsan' OR age = 25;

索引失效在于这里 username 有索引，但是 age 没有索引。使用 OR 连接时，只要有一个字段没有索引，MySQL 就无法使用索引。

正确的做法是要么给age字段添加索引：

ALTER TABLE user ADD INDEX idx_age (age);

要么使用 UNION ALL 拆分查询：

-- 正确写法
SELECT * FROM user WHERE username = 'zhangsan'
UNION ALL
SELECT * FROM user WHERE age = 25;

第二种查询虽然还是全表扫描，但整体效率通常比单一的全表扫描更高。

场景 5：非主键索引用 NOT IN / NOT EXISTS

有时我们需要查询不在特定用户列表中的用户：

-- 风险写法
SELECT * FROM user WHERE username NOT IN ('zhangsan', 'lisi');

索引失效在于 username 虽然有索引，但使用 NOT IN 时，MySQL 通常不会使用它。因为"不在列表中"的判断需要 username 回表查询完整数据，效率地下，不如直接全表扫描。

更好的做法是使用 LEFT JOIN ... IS NULL：

-- 正确写法
CREATE TEMPORARY TABLE temp_names (name VARCHAR(50));
INSERT INTO temp_names VALUES ('zhangsan'), ('lisi');

SELECT u.* FROM user u
LEFT JOIN temp_names t ON u.username = t.name
WHERE t.name IS NULL;

这种写法可以更好地利用idx_username索引，提高查询效率。

场景 6：LIKE 以通配符 "%" 开头

若业务需求是查询用户名以 "san" 结尾的用户：

-- 错误写法
SELECT * FROM user WHERE username LIKE '%san';

索引失效在于 username 虽然有索引，但 LIKE 以 "%" 开头会导致索引失效。因为索引是按字段前缀排序的，无法确定查询的起点，只能进行全表扫描。

更好的做法是如果业务允许，可以改为前缀匹配：

-- 正确写法（前缀匹配）
SELECT * FROM user WHERE username LIKE 'zhang%';

这种写法可以正常使用idx_username索引。如果确实需要后缀匹配，考虑使用搜索引擎如 Elasticsearch 来实现。

场景 7：联合索引不满足 "最左前缀"

已知 user 表有一个联合索引 idx_username_age (username, age) ，当我们需要查询年龄为 25 岁的用户时：

-- 错误写法
SELECT * FROM user WHERE age = 25;

索引失效是因为联合索引是按 "username → age" 的顺序排序的，跳过第一个字段直接查询第二个字段，相当于失去了索引的 "目录入口"。

正确的做法是要使用这个联合索引，查询条件需要包含最左前缀字段：

-- 正确写法
SELECT * FROM user WHERE username = 'zhangsan' AND age = 25;

如果经常需要单独按 age 查询，可以考虑给 age 单独建立索引，或者建立 (age, username) 的联合索引。

场景 8：联合索引中，范围查询字段右侧的索引字段失效

假设业务上需要查询 "用户名前缀为 'zhang' 且年龄为 25 岁的用户"，使用联合索引idx_username_age(username, age) 查询时：：

-- 错误写法
SELECT * FROM user WHERE username LIKE 'zhang%' AND age = 25;

索引失效是因为这个查询中，左侧的 username 用了范围查询（ LIKE 'zhang%'），导致右侧的 age 在索引中失去了有序性，无法利用索引筛选，只能逐行判断。

正确的做法是新增适配的联合索引，并将范围查询字段放在最后：

-- 新增联合索引：将精确查询的 age 放在左侧，范围查询的 username 放在右侧
ALTER TABLE `user` ADD KEY `idx_age_username` (`age`, `username`);

-- 优化后的查询
SELECT * FROM user WHERE age = 25 AND username LIKE 'zhang%';

这样 age = 25 是精确匹配，可以使用索引，后面的 username LIKE 'zhang%' 作为范围查询也能有效利用索引。

第三类：索引设计类失效

这类问题源于索引本身设计不合理，即使查询语句写得正确，也无法高效工作。

场景 9：索引字段选择性过低

假设我们给 user 表的 gender 字段建立了索引 idx_gender (gender) ，当查询所有男性用户时：

-- 低效写法（假设已建立idx_gender索引）
SELECT * FROM user WHERE gender = '男';

索引失效是因为gender 字段只有 3 个可能值（' 男 '、' 女 '、' 未知 '），这种情况下，使用索引的效率并不比全表扫描高，MySQL 可能会直接选择全表扫描。

正确的做法是不单独给 gender 建索引，而是建立联合索引：

-- 优化：建立联合索引
ALTER TABLE user ADD INDEX idx_gender_age (gender, age);

-- 更高效的查询
SELECT * FROM user WHERE gender = '男' AND age = 25;

联合索引 (gender, age) 的选择性更高，可以有效过滤数据，提高查询效率。

场景 10：重复索引与冗余索引

当有冗余索引时：

-- 问题：存在冗余索引
KEY `idx_username` (`username`),
KEY `idx_username_age` (`username`,`age`)

索引失效的原因在于冗余索引不仅会浪费存储空间，还会降低插入、更新和删除操作的性能，导致索引无法发挥应有的高效作用。

正确的做法是删除冗余索引：

-- 正确做法：删除冗余索引
DROP INDEX idx_username ON user;

只保留idx_username_age索引，既可以支持 username 的单独查询，也可以支持 username+age 的联合查询。

场景 11：未使用覆盖索引导致回表

当业务中需要查询用户的用户名、年龄及邮箱这三个字段时：

-- 问题写法
SELECT username, age, email FROM user WHERE username = 'zhangsan';

索引失效是因为联合索引里只有 username 和 age 这两个字段，可查询时还需要 email 字段。这就导致 MySQL 得先靠联合索引找到匹配的记录，再根据主键回表查询 email 字段，多了一次磁盘读写操作，索引的高效性就打折扣了。

正确的做法是创建覆盖索引，包含查询所需的所有字段：

-- 优化：创建覆盖索引
ALTER TABLE user ADD INDEX idx_username_age_email (username, age, email);

-- 更高效的查询
SELECT username, age, email FROM user WHERE username = 'zhangsan';

覆盖索引包含了查询所需的所有字段，MySQL 可以直接从索引中获取数据，无需回表，大大提高查询效率。

场景 12：主键索引因删除数据出现空洞

当我们需要删除大量数据时，可能会这样操作：

-- 问题操作
DELETE FROM user WHERE id < 10000;

索引失效是因为删除大量连续的主键数据后，会留下 "索引空洞" — 物理空间未释放，索引中仍存在无效条目。这会导致后续查询需要扫描更多的索引节点，降低查询效率。

正确的做法是定期优化表结构：

-- 优化表结构，整理索引碎片
OPTIMIZE TABLE user;

这个操作会重建表和索引，释放空洞空间，提高索引查询效率。另外，也可以避免大批量删除连续主键数据，改为分批删除，减少空洞产生。

实战：用 EXPLAIN 排查索引失效的全流程

前面我们把 12 种索引失效场景都梳理了一遍，但开发中遇到的情况往往更灵活，这时候，靠 “猜场景” 效率太低，必须有工具帮我们 “透视” 索引的真实状态。

MySQL 自带的 EXPLAIN 就是排查索引失效的利器，只要看懂它输出的关键字段，就能精准定位问题。

我们以 “场景 1：给索引字段套函数” 为例，完整地走一遍从 “发现问题” 到 “验证优化” 的全流程。

第一步：先看有问题的 SQL

EXPLAIN 的用法特别简单，在要排查的 SQL 语句前加 EXPLAIN 关键字 就会生成 SQL 执行计划：

EXPLAIN SELECT * FROM user WHERE DATE(create_time) = '2024-01-01';

执行后，MySQL 会返回一张表格，我们重点关注 type、key、rows、Extra 这 4 个核心字段

type	key	rows	Extra
ALL	NULL	10000	Using where

第二步：解读 EXPLAIN 结果

拿到了这 4 个核心字段，我们就能从不同维度精准判断索引有没有生效

字段名	作用说明	关键判断标准
type	数据访问类型，反映查询效率	从优到劣：system > const > eq_ref > ref > range > ALL（全表扫描，索引失效）
key	实际使用的索引名称	非 NULL：用到索引；NULL：索引失效（或无合适索引）
rows	预估扫描行数（估算值）	越小越好；接近表总数据量 → 大概率全表扫描（索引失效）
Extra	索引使用细节（回表、排序等）	警惕：Using filesort / Using temporary（索引设计可能有问题）；最优：Using index（覆盖索引）

结合前面的执行结果分析：

• type = ALL：说明是全表扫描，索引没生效
• key = NULL：进一步确认没用到任何索引
• rows = 10000：预估扫描 1 万行（假设表总数据量就是 1 万行），验证了扫描范围覆盖全表

三个字段相互印证，明确指向 “索引失效” 的结论。

第三步：优化 SQL 并验证

明确索引失效原因后，我们按照场景 1 的解决思路进行优化，将 DATE() 函数从索引字段 create_time 上移除，改用原始字段搭配时间范围查询，改写后的 SQL 如下：

EXPLAIN SELECT * FROM user WHERE create_time BETWEEN '2024-01-01 00:00:00' AND '2024-01-01 23:59:59';

再次执行EXPLAIN，得到优化后的结果：

type	key	rows	Extra
range	idx_create_time	50	Using where

分析优化效果：

• type = range：表示用到了索引的范围查询，索引生效
• key = idx_create_time：确认用到了 create_time 的索引
• rows = 50：仅需扫描 50 行，效率大幅提升

这三个字段都印证了优化后索引已正常生效，查询效率大幅提升。

总结

到这里，关于 MySQL 索引失效的核心知识就讲完了。其实说到底，索引失效的本质就一个：查询操作或索引设计，违背了索引 “有序存储、高效匹配” 的核心逻辑。

具体来看，所有失效场景都能归为三类：

• 对索引字段做函数、运算等操作，本质是破坏了它的 “有序性”；
• 查询用 OR 连非索引字段、LIKE 以 % 开头等，是违背了索引的 “匹配规则”；
• 给性别建单索引、重复建索引，则是索引设计 “先天不足”。

记住这三类核心逻辑，遇到问题时就能快速归类，找到解决方向。

而 EXPLAIN 工具，则是把 “索引是否生效”从看不见摸不着变成有数据可查的关键。不用死记所有字段，只要盯着 type 是不是全表扫描 、key 用没用到索引、rows 要扫多少行、Extra 有没有低效操作 这四个核心信息，就能快速判断问题。

优化后再用它验证，通过数据变化确认效果，形成 “排查 - 优化 - 验证” 的闭环。