MySQL索引失效避坑指南：10大典型场景与底层原理深度剖析
明明建了索引，查询却依然慢如蜗牛？你很可能踩中了“索引失效”的陷阱。本文从B+Tree底层原理出发，结合真实案例，全面解析索引失效的10大典型场景，助你写出真正高效的SQL。
一、引言：索引为什么“失效”？
MySQL查询优化器基于成本模型决定是否使用索引。当它认为全表扫描比走索引更“便宜”时，就会放弃索引。理解这些“决策逻辑”，就能避免索引失效。
下面从最常见的场景开始逐一剖析。
二、十大索引失效场景
❌ 场景1：隐式类型转换
问题：索引列是VARCHAR字符串类型，查询条件中却直接写数值（不带引号）。
-- card_code 是 VARCHAR 类型，有索引
SELECT * FROM user WHERE card_code = 123456; -- 隐式转换，索引失效！
❌ 场景2：使用LIKE时通配符在前
SELECT * FROM user WHERE name LIKE '%张三'; -- 索引失效
1. 左边 % → 索引失效（全表扫描）
2.右边 % → 索引有效（范围查找）
SELECT * FROM user WHERE name LIKE '张三%'; -- 索引有效
3.两边都有 %。
SELECT * FROM user WHERE name LIKE '%张三%';-- 索引失效
这是 最典型的索引失效场景，因为 两边都有 %。
三种 LIKE 场景总结图（必背）
原理：B+Tree索引只能按前缀匹配。'%张三'无法确定前缀，只能全表扫描。
❌ 场景3：查询条件使用OR
SELECT * FROM user WHERE id = 10 OR name = '张三';
-- 如果 name 没有索引，则整个查询不走索引。
原因：优化器必须检查OR两边所有条件。只要有一个条件无法使用索引，就必须全表扫描（除非对所有列分别建索引）。
❌ 场景4：对索引列进行函数运算
SELECT * FROM user WHERE YEAR(create_time) = 2024; -- 索引失效
SELECT * FROM user WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01'; -- 索引有效
❌ 场景5：联合索引不满足最左前缀法则
假设联合索引(a, b, c)。以下查询无效：
SELECT * FROM table WHERE b = 2; -- 缺少a
SELECT * FROM table WHERE a = 1 AND c = 3; -- 部分命中索引、部分失效s 部分命中索引（仅a有效，c无效）跳过b，破坏前缀连续性
SELECT * FROM table WHERE c = 3; -- 完全不用索引
优化器小技巧：如果查询条件WHERE b = 2 AND a = 1，优化器会自动重排为a=1 AND b=2，索引有效。
❌ 场景6：使用!=或<>或NOT IN
SELECT * FROM user WHERE status != 0; -- 大概率不走索引
原因：负向查询返回的数据量通常较大，优化器认为全表扫描代价更低。如果满足条件的行数极少（例如status只有1%是0），也可能走索引，但一般不依赖。
❌ 场景7：ORDER BY导致索引失效
索引列顺序与ORDER BY不一致：索引(a, b)，但ORDER BY b, a。
混合排序：ORDER BY a ASC, b DESC，索引无法支持混合方向排序。
只使用部分索引列排序：ORDER BY b（索引(a, b)，缺少a）。
❌ 场景8：查询条件不包含索引列
建了索引的列根本没出现在WHERE、JOIN或ORDER BY中，当然用不到。
❌ 场景9：数据量极小
表只有几十行记录，全表扫描比基于索引的回表更快，优化器会直接选择全表扫描。
❌ 场景10：索引列参与计算或类型不一致
SELECT * FROM user WHERE id + 1 = 10; -- 应改写为 id = 9
SELECT * FROM user WHERE age > '18'; -- age是INT，'18'会自动转数字，但不会失效
❌ 场景11：数据分布导致优化器放弃索引
例如：status 只有 0/1
SELECT * FROM user WHERE status=1;
可能：全表扫描，因为扫描索引+回表 成本更高
三、图解最左前缀匹配原则
联合索引(a, b, c)在B+Tree中的组织方式：先按a排序，a相同再按b，b相同再按c。因此，必须从最左边连续使用索引列。
✅ 能命中：a=1、a=1 AND b=1、a=1 AND b=1 AND c=1
❌ 不能命中：b=1、c=1、a=1 AND c=1（只能用到a无法过滤c）
四、最佳实践：如何避免索引失效
问题类型正确做法
隐式转换
保持类型一致，字符串字段用引号
LIKE通配符在前
尽量避免，可用全文索引或ES替代
OR条件
改为UNION ALL，或确保所有列都有索引
函数运算
改写为范围查询或使用生成列索引（MySQL 8.0函数索引）
最左前缀
调整索引顺序或查询条件
负向查询
改用IN列举正向值
GROUP BY
不符合索引顺序
ORDER BY
让索引顺序与排序顺序一致
数据量小
忽略或强制索引（谨慎）
五、验证索引是否生效：EXPLAIN利器
使用EXPLAIN查看执行计划，关注type、key、Extra列：
type：const、ref、range表示用了索引；ALL表示全表扫描。
key：实际使用的索引名。
Extra：Using index为覆盖索引；Using index condition为索引下推；Using where需要额外过滤。
EXPLAIN SELECT * FROM user WHERE name = '张三';
六、总结
索引失效的根本原因在于优化器认为走索引的成本高于全表扫描。常见诱因包括隐式类型转换、LIKE前缀通配、OR条件、函数运算、违背最左前缀、负向查询等。通过理解B+Tree的特性和优化器的执行计划，可以有效避免这些问题，写出真正高效的SQL。
如果觉得本文对你有帮助，欢迎点赞、收藏、转发！关注我，更多MySQL深度干货持续输出。