MySQL 索引失效的 8 种场景，90% 开发者都踩过坑
MySQL 索引失效的 8 种场景，90% 开发者都踩过坑
导读：你是否遇到过这样的尴尬：明明给字段加了索引，
EXPLAIN一看却全是ALL（全表扫描）？查询慢如蜗牛，CPU 飙升到 100%？在 MySQL 性能优化中，“索引失效”是最隐蔽也最致命的陷阱。本文深入剖析导致索引失效的 8 大经典场景，结合真实代码案例和底层原理，助你彻底避开这些深坑，让查询飞起来！
一、为什么索引会“凭空消失”？
MySQL 的索引（尤其是 B+ 树索引）是有序的数据结构。优化器（Optimizer）决定是否使用索引的核心逻辑很简单：如果通过索引查找的成本 < 全表扫描的成本，则走索引；否则，直接全表扫描。
但在很多情况下，由于 SQL 写法不当或数据类型隐式转换，优化器被迫放弃索引。以下是 8 种最高频的“翻车”现场。
场景 1：最左前缀法则被打破（复合索引篇）
❌ 错误写法： 假设有一个复合索引idx_name_age_city(name, age, city)。
-- 情况 A：跳过中间列 SELECT * FROM user WHERE name = '张三' AND city = '北京'; -- 结果：name 走索引，age 和 city 不走索引（因为 age 断了） -- 情况 B：直接从第二列开始查 SELECT * FROM user WHERE age = 25 AND city = '北京'; -- 结果：索引完全失效，全表扫描！✅ 正确写法： 必须严格遵循最左前缀原则，从索引的最左边列开始匹配，不能跳过中间的列。
-- 只有这种写法能完美利用索引 SELECT * FROM user WHERE name = '张三' AND age = 25 AND city = '北京'; -- 或者只查前两列 SELECT * FROM user WHERE name = '张三' AND age = 25;💡原理：B+ 树是先按
name排序，name相同再按age排序。如果没指定name，age在全局是无序的，索引无法定位。
场景 2：在索引列上做计算或函数操作
❌ 错误写法：
-- 对索引列进行函数运算 SELECT * FROM order WHERE DATE(create_time) = '2026-03-14'; -- 对索引列进行计算 SELECT * FROM product WHERE price * 0.9 > 100;✅ 正确写法： 将计算移到等号右边，保持索引列的“纯净”。
-- 改造为范围查询 SELECT * FROM order WHERE create_time >= '2026-03-14 00:00:00' AND create_time < '2026-03-15 00:00:00'; -- 移项处理 SELECT * FROM product WHERE price > 100 / 0.9;💡原理：索引存储的是原始值。如果对列做了函数处理，MySQL 必须取出每一行数据计算后才能比较，这等同于全表扫描。
场景 3：隐式类型转换（字符串不加引号）
这是新手最容易踩的坑，也是生产环境最常见的“幽灵”问题。
❌ 错误写法： 假设phone字段是VARCHAR类型。
-- 数字没有加引号，MySQL 会自动把 phone 转为数字进行比较 SELECT * FROM user WHERE phone = 13800138000;✅ 正确写法：
-- 加上引号，保持类型一致 SELECT * FROM user WHERE phone = '13800138000';💡原理：当字符串字段与数字比较时，MySQL 会将字符串字段隐式转换为数字（类似
CAST(phone AS SIGNED)）。一旦对列进行了类型转换函数操作，索引立即失效！检查方法：EXPLAIN结果中Extra列出现Using where且type为ALL或index而非ref。
场景 4：模糊查询%在前缀
❌ 错误写法：
-- 通配符在最前面 SELECT * FROM user WHERE name LIKE '%张%'; SELECT * FROM user WHERE name LIKE '%三';✅ 正确写法：
-- 通配符只在后面，可以走索引 SELECT * FROM user WHERE name LIKE '张%';💡原理：B+ 树是从左向右排序的。
'张%'可以利用有序性快速定位到“张”开头的所有记录；而'%张'意味着“张”可能出现在任何位置，破坏了有序性，只能全表扫描。进阶方案：如果必须前缀模糊搜索，考虑使用Elasticsearch或倒排索引。
场景 5：OR 连接条件中包含非索引列
❌ 错误写法：
-- name 有索引，但 email 没有索引 SELECT * FROM user WHERE name = '张三' OR email = 'test@example.com';✅ 正确写法： 确保OR两边的字段都有索引，或者改写为UNION ALL。
-- 方案 A：给 email 也加上索引 ALTER TABLE user ADD INDEX idx_email (email); -- 方案 B：手动拆分查询（推荐，更可控） SELECT * FROM user WHERE name = '张三' UNION ALL SELECT * FROM user WHERE email = 'test@example.com';💡原理：只要
OR连接的条件中有一个字段没索引，优化器为了保证数据完整性，往往会放弃所有索引，直接全表扫描。
场景 6：NOT IN / != / <> 操作
❌ 错误写法：
SELECT * FROM user WHERE status != 1; SELECT * FROM user WHERE id NOT IN (1, 2, 3);✅ 优化思路： 尽量避免使用否定操作符。如果业务允许，改为正向查询或使用IS NULL配合特定逻辑。
-- 如果状态只有 1(正常) 和 0(删除)，查删除的可以用 SELECT * FROM user WHERE status = 0; -- 对于 NOT IN，如果数据量小没关系；数据量大时，考虑用 LEFT JOIN + IS NULL 替代 SELECT u.* FROM user u LEFT JOIN black_list b ON u.id = b.user_id WHERE b.user_id IS NULL;💡原理：不等于或不包含通常意味着要扫描大部分数据，优化器认为全表扫描效率更高。但在某些覆盖索引场景下，MySQL 8.0+ 可能会尝试使用索引，但不稳定。
场景 7：IS NULL 与 IS NOT NULL 的误区
很多人认为IS NULL一定不走索引，其实不然。
- 情况 A：如果字段定义为
NOT NULL，则IS NULL肯定查不到数据，优化器直接优化掉。 - 情况 B：如果字段允许
NULL，且NULL值占比很高（例如超过 20%），优化器可能放弃索引。 - 情况 C：如果是覆盖索引（Covering Index），即使
IS NOT NULL也可能走索引。
✅ 最佳实践： 尽量将字段定义为NOT NULL，并设置默认值（如 0 或空字符串）。这样不仅能避免索引失效的歧义，还能节省存储空间。
-- 推荐定义 CREATE TABLE user ( id INT PRIMARY KEY, age INT NOT NULL DEFAULT 0, ... );场景 8：字符集不一致导致隐式转换
这是一个跨表关联（JOIN）时的高发场景。
❌ 错误写法： 表 A 的user_id是utf8字符集，表 B 的user_id是utf8mb4字符集。
SELECT * FROM order o JOIN user u ON o.user_id = u.user_id;✅ 正确写法： 确保关联字段的字符集和排序规则（Collation）完全一致。
-- 修改表字符集 ALTER TABLE order MODIFY user_id VARCHAR(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;💡原理：字符集不同会导致 MySQL 在关联时进行隐式的字符集转换函数操作，从而导致驱动表的索引失效。
🛠️ 实战工具箱：如何快速发现索引失效？
不要猜，用工具说话！
1. EXPLAIN 命令
这是最基本的诊断工具。重点关注以下字段：
- type：
system>const>eq_ref>ref>range>index>ALL。如果出现ALL或index，警惕！ - key：实际使用的索引。如果是
NULL，说明没用到索引。 - rows：预计扫描行数。越大越慢。
- Extra：
Using filesort：需要额外排序，性能差。Using temporary：使用了临时表，性能差。Using index condition：正常走索引。
2. Slow Query Log
开启慢查询日志，捕获执行时间超过阈值（如 1s）的 SQL。
[mysqld] slow_query_log = 1 long_query_time = 1 log_queries_not_using_indexes = 1 # 重点：记录没用索引的查询3. pt-query-digest
Percona toolkit 的神器，分析慢日志，聚合出最耗资源的 SQL 模板。
🚀 总结：避坑口诀
为了方便记忆，送大家一首《索引避坑歌》：
最左前缀要记牢，中间断开全扫飘。 列上莫把函数套，计算统统右边抛。 字符串要加引号，隐式转换是毒药。 百分号别放头跑，OR 两边索引保。 字符集需对齐好，NOT IN 尽量少。 遇事不决 Explain，性能优化没烦恼！
最后的话： 索引不是万能药，乱加索引反而拖慢写入速度。真正的优化在于理解业务场景，写出符合 B+ 树特性的 SQL。
下次写 SQL 时，先问自己一句：“这条语句，会让我的索引失效吗？”