MySQL 索引失效的 8 种场景，90% 开发者都踩过坑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 时，先问自己一句： “这条语句，会让我的索引失效吗？”

MySQL 索引失效的 8 种场景，90% 开发者都踩过坑