CREATE TABLE `user` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(50) NOT NULL,
  `email` VARCHAR(100),
  `age` INT,
  `status` TINYINT DEFAULT 1,
  `create_time` DATETIME,
  `score` DECIMAL(10,2),
  PRIMARY KEY (`id`),
  KEY `idx_username` (`username`),
  KEY `idx_age_status` (`age`, `status`),
  KEY `idx_create_time` (`create_time`),
  KEY `idx_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

三、12种索引失效场景

场景1：索引列上使用函数

-- 失效：在索引列上包裹函数
EXPLAIN SELECT * FROM user WHERE DATE(create_time) = '2023-05-01';
-- type = ALL，全表扫描

-- 有效：改写为范围查询
EXPLAIN SELECT * FROM user 
WHERE create_time >= '2023-05-01 00:00:00' 
  AND create_time < '2023-05-02 00:00:00';
-- type = range，使用索引

原因：B+树按create_time的原始值建立，DATE(create_time)是计算后的值，没有建索引，必须全扫。

场景3：隐式类型转换

-- 失效：email是varchar，传入数字，发生隐式类型转换
EXPLAIN SELECT * FROM user WHERE email = 12345;
-- type = ALL（MySQL将email字段值转为数字比较，相当于对索引列用了函数）

-- 有效：类型一致
EXPLAIN SELECT * FROM user WHERE email = '12345@test.com';
-- type = ref

原因：MySQL的类型转换规则：字符串和数字比较时，字符串转为数字。相当于对字段做了CAST(email AS DECIMAL)，破坏了索引。

反向陷阱：整型字段传字符串通常不失效（字符串转数字），但字符串字段传数字会失效（数字转字符串 = 函数调用）。记住：索引列一侧发生转换，才失效。

场景4：LIKE以通配符开头

-- 失效：前缀通配符
EXPLAIN SELECT * FROM user WHERE username LIKE '%zhang%';
EXPLAIN SELECT * FROM user WHERE username LIKE '%zhang';
-- type = ALL

-- 有效：后缀通配符
EXPLAIN SELECT * FROM user WHERE username LIKE 'zhang%';
-- type = range

原因：B+树是按字符串前缀排序的。'zhang%'可以确定起始位置，'%zhang%'无法确定起始位置，只能全扫。

解决方案：需要全文搜索时，用MySQL全文索引或Elasticsearch。

场景5：违反最左前缀原则

-- 联合索引 (age, status)
-- 失效：跳过了最左列age
EXPLAIN SELECT * FROM user WHERE status = 1;
-- type = ALL

-- 有效：包含最左列
EXPLAIN SELECT * FROM user WHERE age = 25;
-- type = ref，只用了age部分的索引

-- 有效：使用完整联合索引
EXPLAIN SELECT * FROM user WHERE age = 25 AND status = 1;
-- type = ref

原因：联合索引(age, status)在B+树中是先按age排序，age相同再按status排序。单独查status时，status没有整体有序，无法利用索引。

可以看到，age是有序的，但status单独来看（0,1,0,1,1）是无序的。

场景7：使用OR连接，OR两侧不都有索引

-- 失效：id有主键索引，但email没有索引
-- 实际上这里email有索引，换个例子
EXPLAIN SELECT * FROM user WHERE username = 'zhang' OR age = 25;
-- username有索引，age在联合索引里，但OR情况下MySQL可能选择全表扫描

-- 更明显的例子：其中一个字段没有索引
ALTER TABLE user DROP INDEX idx_age_status;
EXPLAIN SELECT * FROM user WHERE username = 'zhang' OR age = 25;
-- age没有单独索引，整体失效，type = ALL

原因：OR的语义是两个条件的并集，如果任意一侧无法用索引，那另一侧用索引再合并也不如全表扫描高效，优化器会放弃索引。

解决方案：给OR两侧的字段都建索引，或者改写为UNION ALL。

场景8：索引列不是最优选择（数据区分度太低）

-- status只有0和1两个值，区分度极低
EXPLAIN SELECT * FROM user WHERE status = 1;
-- MySQL可能选择全表扫描，因为索引回表的代价比全扫还高

-- 验证：强制使用索引
EXPLAIN SELECT * FROM user FORCE INDEX(idx_age_status) WHERE status = 1;
-- 走了索引，但性能未必更好

原因：当索引的选择性（区分度）太低时，比如status=1可能匹配80%的行，走索引反而要在B+树上大量回表，MySQL优化器会判断全表扫描更快。

场景10：字符集不匹配（隐式字符集转换）

-- 两张表字符集不同时，JOIN会触发隐式转换
CREATE TABLE order_info (
  id INT,
  user_email VARCHAR(100)
) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- 注意：utf8，不是utf8mb4

-- 失效：JOIN字段字符集不同，发生隐式转换
EXPLAIN SELECT u.*, o.* 
FROM user u 
JOIN order_info o ON u.email = o.user_email;
-- u.email是utf8mb4，o.user_email是utf8，发生转换，索引失效

原因：字符集转换相当于对字段值做了函数处理，破坏了B+树的有序性。这是实际项目中很隐蔽的性能问题，统一字符集是最好的解决方案。

场景11：索引列为NULL相关查询

-- IS NULL、IS NOT NULL有时失效（取决于NULL的比例）
EXPLAIN SELECT * FROM user WHERE email IS NULL;
-- 如果NULL比例很高，可能全表扫描

-- 有些情况下有效
EXPLAIN SELECT * FROM user WHERE email IS NOT NULL;
-- 取决于优化器代价估算

实际上，MySQL 8.0之后IS NULL可以走索引，这是个老谣言。但如果NULL比例超过30%，优化器可能仍然选择全扫。

场景12：ORDER BY引起的文件排序（filesort）

-- 失效（对于避免filesort来说）
EXPLAIN SELECT * FROM user ORDER BY age, username;
-- 虽然age有索引，但username不在同一索引里，Extra=Using filesort

-- 有效：使用联合索引的索引顺序排序
EXPLAIN SELECT * FROM user ORDER BY age, status;
-- 联合索引(age, status)，排序有序，无需filesort

-- 混合排序方向失效
EXPLAIN SELECT * FROM user ORDER BY age ASC, status DESC;
-- Extra = Using filesort，因为索引的排序方向是一致的(ASC, ASC)

四、面试官追问

追问1：EXPLAIN的type字段有哪几种，从好到差怎么排？

我的回答：从最优到最差依次是：system（表只有一行）、const（主键或唯一索引等值查询）、eq_ref（JOIN时每个主表行匹配唯一一行）、ref（非唯一索引等值查询）、range（范围查询）、index（全索引扫描，比ALL好，因为索引比数据小）、ALL（全表扫描）。生产环境中，至少要保证常见查询达到range级别，核心查询要达到ref或更好。

追问2：覆盖索引是什么，为什么能提升性能？

我的回答：覆盖索引是指查询需要的所有字段都在索引中，不需要回表查主键索引。比如SELECT age, status FROM user WHERE age = 25，联合索引(age, status)已经包含了所有需要的字段，直接从索引树拿到结果，不需要再去主键树上查完整行数据。EXPLAIN中Extra显示Using index就是覆盖索引。性能提升来自两点：减少一次B+树查找、数据量小（索引只有索引列的值，而完整行可能很大）。

追问3：什么是索引下推（ICP），什么时候有效？

我的回答：索引下推（Index Condition Pushdown）是MySQL 5.6引入的优化。在没有ICP之前，联合索引只能利用最左前缀的等值部分，剩余条件需要回表后再过滤。有了ICP，联合索引中的后续条件可以在存储引擎层（索引层）就过滤掉，减少回表次数。比如索引(age, status)，查询WHERE age > 20 AND status = 1，ICP会在索引扫描时就同时判断status=1，只有满足的行才回表。EXPLAIN的Extra中显示Using index condition就是ICP在工作。

五、同类题目举一反三

如何优化一条慢SQL的步骤？

EXPLAIN查看执行计划，关注type、key、rows、Extra
确认索引是否存在、是否被使用
如果没用索引，找出失效原因（参考本文12种场景）
考虑是否可以通过覆盖索引避免回表
分析数据量，考虑是否需要分页、分表
在测试环境验证优化效果

六、踩坑实录

坑一：时间类型字段用了函数，慢查询几十秒

开头故事就是这个坑。WHERE DATE(create_time) = '2023-05-01'，一个DATE()函数，让1200万行数据被全扫。改成范围查询后恢复正常。这个坑我踩了，也在其他公司的代码审查中发现过不下10次。

坑二：字符串字段没加引号

有次线上慢查询告警，找到SQL：SELECT * FROM user WHERE mobile = 13800138000。mobile字段是varchar类型，传入了数字，触发了隐式类型转换，索引失效。加上引号WHERE mobile = '13800138000'问题解决。

教训：Java代码里用MyBatis时，如果字段是varchar，参数类型要用String，不要用Long。

坑三：联合索引顺序建反了

有个同事建了索引(status, user_id)，查询是WHERE user_id = 123。status只有0和1两个值，放最左边等于浪费了最左前缀原则；user_id是高区分度的字段，反而没法用。正确顺序应该是(user_id, status)。

建联合索引时，区分度高的字段放左边，区分度低的字段放右边。

七、总结

MySQL索引失效的12种场景，用一句话总结：一切让MySQL无法利用B+树有序性的操作，都会导致索引失效。

核心原则：

不要在索引列上做函数、运算、类型转换
LIKE查询用后缀通配符，不用前缀通配符
联合索引遵守最左前缀，高区分度列放左边
OR连接时保证两侧都有索引
统一字符集，避免隐式字符集转换

每次写SQL，养成用EXPLAIN看执行计划的习惯，这是数据库优化的第一步。

MySQL索引失效的12种场景：每种都有可复现的SQL反例

MySQL索引失效的12种场景：每种都有可复现的SQL反例

开篇故事

一、高频考点拆解

二、深度原理分析

2.1 B+树索引的工作原理

2.2 建表准备