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排序。如果没指定nameage在全局是无序的索引无法定位。场景 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 [phone];✅ 正确写法-- 加上引号保持类型一致 SELECT * FROM user WHERE phone [phone];原理当字符串字段与数字比较时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或倒排索引。场景 5OR 连接条件中包含非索引列❌ 错误写法-- name 有索引但 email 没有索引 SELECT * FROM user WHERE name 张三 OR email testexample.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 testexample.com;原理只要OR连接的条件中有一个字段没索引优化器为了保证数据完整性往往会放弃所有索引直接全表扫描。场景 6NOT 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 可能会尝试使用索引但不稳定。场景 7IS 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 命令这是最基本的诊断工具。重点关注以下字段typesystemconsteq_refrefrangeindexALL。如果出现ALL或index警惕key实际使用的索引。如果是NULL说明没用到索引。rows预计扫描行数。越大越慢。ExtraUsing 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-digestPercona toolkit 的神器分析慢日志聚合出最耗资源的 SQL 模板。 总结避坑口诀为了方便记忆送大家一首《索引避坑歌》最左前缀要记牢中间断开全扫飘。 列上莫把函数套计算统统右边抛。 字符串要加引号隐式转换是毒药。 百分号别放头跑OR 两边索引保。 字符集需对齐好NOT IN 尽量少。 遇事不决 Explain性能优化没烦恼最后的话 索引不是万能药乱加索引反而拖慢写入速度。真正的优化在于理解业务场景写出符合 B 树特性的 SQL。下次写 SQL 时先问自己一句“这条语句会让我的索引失效吗”