当前位置： 首页 > news >正文

mysql技巧(十三):索引失效的30种情况，90%的开发者都踩过坑

news 2026/8/15 3:54:39

这是一个几乎所有后端开发者都经历过、又难以启齿的瞬间。执行EXPLAIN后，看到type: ALL和Extra: Using where的那一刻，我知道，索引又失效了。我们总以为 MySQL 会“聪明”地使用我们精心设计的索引。但现实是，在 SQL 函数、隐式转换、类型不匹配面前，MySQL 的优化器比你想象中更“死板”。很多时候，不是索引没用，而是你写的 SQL 让 MySQL没法用。

一、基础高频失效（8 种）

日常开发最容易踩坑，必须熟记

1. 索引列使用函数

索引列直接套函数，MySQL 无法使用索引

sql

-- ❌ 索引失效 SELECT * FROM user WHERE DATE(create_time) = '2024-01-01'; -- ✅ 优化：改为范围查询 SELECT * FROM user WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02';

2. 隐式类型转换

字段类型与查询值类型不匹配，触发隐式转换

sql

-- phone 字段为 VARCHAR 类型 -- ❌ 索引失效 SELECT * FROM user WHERE phone = 13800000000; -- ✅ 优化：保持数据类型一致 SELECT * FROM user WHERE phone = '13800000000';

3. 违反最左前缀法则

联合索引必须遵循从左到右的使用顺序

sql

-- 索引：(name, age, city) -- ❌ 失效：未使用最左列 name SELECT * FROM user WHERE age = 20; -- ❌ 失效：跳过中间列 age SELECT * FROM user WHERE name = 'Tom' AND city = '北京';

4. LIKE 以 % 开头

左模糊 / 全模糊查询会导致索引失效

sql

-- ❌ 索引失效 SELECT * FROM user WHERE name LIKE '%张三%'; -- ✅ 索引有效：右模糊查询 SELECT * FROM user WHERE name LIKE '张三%';

5. OR 的一边没有索引

OR 连接的条件中，只要有一个字段无索引，整体索引失效

sql

-- name 有索引，age 无索引 -- ❌ 索引失效 SELECT * FROM user WHERE name = 'Tom' OR age = 20;

6. 负向查询

!=、<>、NOT IN、NOT LIKE大概率导致索引失效

sql

-- ❌ 索引失效 SELECT * FROM user WHERE status != 1; SELECT * FROM user WHERE id NOT IN (1,2,3); SELECT * FROM user WHERE name NOT LIKE '张%';

7. 查询结果占比过大

查询数据量超过全表 20%-30%，优化器直接选择全表扫描

sql

-- 90% 的数据 status=1 -- ❌ 优化器选择全表扫描 SELECT * FROM user WHERE status = 1;

8. IS NULL / IS NOT NULL 可能失效

根据字段空值占比决定，极端场景下索引失效

sql

-- 字段绝大部分为 NULL：IS NOT NULL 失效 -- 字段绝大部分不为 NULL：IS NULL 失效 SELECT * FROM user WHERE deleted_at IS NOT NULL;

二、进阶隐性失效（7 种）

代码规范不到位，极易引发慢查询

9. 对索引列进行运算

索引列参与数学运算，直接导致索引失效

sql

-- ❌ 索引失效 SELECT * FROM product WHERE price + 10 > 100; -- ✅ 优化：运算移到右侧 SELECT * FROM product WHERE price > 90;

10. 使用 BETWEEN 但范围过大

范围覆盖全表 30% 以上，优化器放弃索引

sql

-- 范围过大，索引失效 SELECT * FROM `order` WHERE amount BETWEEN 1 AND 10000000;

11. 联合索引中跳过中间列

联合索引中间列缺失，后续字段无法使用索引

sql

-- 索引：(a, b, c) -- ❌ 仅用到 a 索引，c 条件无法走索引 SELECT * FROM table WHERE a = 1 AND c = 3;

12. 使用 IN 时值过多或过少

IN 后值数量异常，优化器放弃索引

sql

-- 值太少（1个）或太多（几百个），索引失效 SELECT * FROM user WHERE id IN (1); SELECT * FROM user WHERE id IN (1,2,3,...,500);

13. 排序规则（Collation）不一致

字段排序规则与查询参数不一致，触发隐式转换

sql

-- 表字段：utf8mb4_general_ci，传入：utf8mb4_unicode_ci -- ❌ 隐式转换，索引失效 SELECT * FROM user WHERE name = @input;

14. 前缀索引使用不当

查询长度超过前缀索引定义长度，索引失效

sql

-- 前缀索引：idx_content(content(20)) -- ❌ 索引失效 SELECT * FROM article WHERE content = '很长的完整内容...';

15. JOIN 时两表字符集 / 排序规则不一致

关联字段字符集不匹配，关联索引失效

sql

-- A表：utf8mb4_general_ci，B表：utf8mb4_unicode_ci -- ❌ 关联索引失效 SELECT * FROM A JOIN B ON A.name = B.name;

三、高级隐蔽失效（8 种）

生产环境最难排查，资深开发必掌握

16. ORDER BY 与 WHERE 的索引冲突

排序字段与查询字段索引不匹配，触发 filesort

sql

-- 索引：(status, create_time) -- ❌ 可能失效：产生 filesort SELECT * FROM task WHERE status = 1 ORDER BY create_time LIMIT 10;

17. EXISTS 子查询表顺序问题

子查询返回大量数据，关联索引失效

sql

-- 子查询数据量大，索引失效 SELECT * FROM user u WHERE EXISTS (SELECT 1 FROM `order` o WHERE o.user_id = u.id AND o.amount > 1000);

18. GROUP BY 顺序与联合索引不一致

分组顺序和联合索引顺序不匹配，索引失效

sql

-- 索引：(a, b, c) -- ❌ 索引失效 SELECT a, b, SUM(c) FROM table GROUP BY b, a; -- ✅ 索引有效 SELECT a, b, SUM(c) FROM table GROUP BY a, b;

19. 字符串字段包含前导空格 / 特殊字符

数据存储不规范，导致索引匹配失败

sql

-- 表中存储：' Tom'（带前导空格） -- ❌ 匹配失败+索引失效 SELECT * FROM user WHERE name = 'Tom';

20. 使用 SELECT * 且覆盖索引无法满足

回表数据量过大，优化器放弃索引

sql

-- 索引：(name) -- ❌ 需大量回表，索引失效 SELECT * FROM user WHERE name = 'Tom'; -- ✅ 覆盖索引，强制走索引 SELECT name FROM user WHERE name = 'Tom';

21. 表统计信息过旧

索引基数不准，优化器误判走全表扫描

sql

-- 数据量变更后，更新统计信息 ANALYZE TABLE user;

22. 强制使用 FORCE INDEX 但索引选择错误

强制使用不合适的索引，性能更差

sql

-- 强制使用错误索引，索引失效 SELECT * FROM user FORCE INDEX (idx_name) WHERE age = 20;

23. 索引字段为 ENUM 类型，传入字符串值

ENUM 类型使用字符串匹配，可能导致索引失效

sql

-- status：ENUM('active', 'inactive') -- ❌ 索引失效 SELECT * FROM user WHERE status = 'active'; -- ✅ 索引有效：使用枚举下标 SELECT * FROM user WHERE status = 1;

四、极端 & 边缘场景（7 种）

特殊业务场景下才会出现，极少人知晓

24. DISTINCT 列顺序与索引不一致

去重字段顺序和索引顺序不匹配，索引失效

sql

-- 索引：(a, b) -- ❌ 索引失效 SELECT DISTINCT b, a FROM table;

25. UNION 分支索引使用不一致

部分子查询不走索引，整体性能下降

sql

-- 第一个子查询走索引，第二个不走索引 SELECT * FROM user WHERE name = 'Tom' UNION SELECT * FROM user WHERE age = 20; -- age 无索引

26. 分区表查询未带分区键

未指定分区键，扫描所有分区，索引失效

sql

-- 表按 create_time 分区 -- ❌ 全分区扫描 SELECT * FROM `order` WHERE status = 1; -- ✅ 带分区键，索引有效 SELECT * FROM `order` WHERE create_time >= '2024-01-01' AND status = 1;

27. LIKE 包含转义字符

查询包含_、%等转义字符，索引失效 / 结果错误

sql

-- ❌ 索引失效 SELECT * FROM user WHERE name LIKE '%\_%' ESCAPE '\';

28. 虚拟列索引使用不当

查询未直接使用虚拟列，索引失效

sql

-- 虚拟列：full_name = CONCAT(first_name, ' ', last_name) -- ❌ 索引失效 SELECT * FROM user WHERE CONCAT(first_name, ' ', last_name) = 'Tom Smith'; -- ✅ 索引有效 SELECT * FROM user WHERE full_name = 'Tom Smith';

29. 使用 SQL_CALC_FOUND_ROWS（MySQL 8.0 废弃）

该语法会导致索引失效，且高版本已废弃

sql

-- ❌ 索引失效，MySQL 8.0 不推荐使用 SELECT SQL_CALC_FOUND_ROWS * FROM user WHERE name = 'Tom';

30. JSON 虚拟列索引查询不匹配

JSON 虚拟列索引查询写法错误，索引失效

sql

-- 索引：idx_age ((data->>'$.age')) -- ✅ 索引有效 SELECT * FROM user WHERE data->>'$.age' = 20; -- ❌ 索引失效 SELECT * FROM user WHERE JSON_EXTRACT(data, '$.age') = 20;

📊 30 种失效场景速查表

表格

类别数量典型代表
基础高频8函数、类型转换、最左前缀、LIKE 左模糊、OR、负向、占比过大、IS NULL
进阶隐性7运算、BETWEEN、跳过中间列、IN 异常、排序规则、前缀索引、JOIN 字符集
高级隐蔽8ORDER BY 冲突、EXISTS、GROUP BY 顺序、前导空格、SELECT *、统计信息、FORCE INDEX、ENUM
极端边缘7DISTINCT、UNION、分区表、LIKE 转义、虚拟列、SQL_CALC_FOUND_ROWS、JSON

🔧 索引失效验证与调试方法

sql

-- 1. 查看执行计划（最核心） EXPLAIN SELECT ...; -- type: ALL（全表扫描）= 索引失效 -- type: ref/range = 索引有效 -- key: NULL = 未使用任何索引 -- 2. 强制走索引（测试专用） SELECT * FROM user FORCE INDEX (idx_name) WHERE ...; -- 3. 更新表统计信息 ANALYZE TABLE user; -- 4. 查看索引基数 SHOW INDEX FROM user; -- 5. MySQL 优化器追踪 SET optimizer_trace="enabled=on"; SELECT ...; SELECT * FROM information_schema.OPTIMIZER_TRACE;

🎯 索引失效核心原则

任何让索引列「发生改变」的操作，都会导致索引失效：

  • 函数操作：DATE(col)
  • 数学运算：col+10
  • 类型转换：col=123
  • 字符串拼接：CONCAT(col)
  • 负向查询：!=、NOT IN
  • 隐式转换：字符集 / 排序规则不一致

总结

这份MySQL 30 种索引失效场景是生产环境实战总结，覆盖了从新手到资深开发的所有踩坑点：

  1. 基础 8 种是日常开发必避坑，优先掌握；
  2. 进阶 7 种是慢查询高频诱因，规范代码可避免；
  3. 高级 8 种是线上疑难杂症，排查慢 SQL 必备；
  4. 极端 7 种是特殊场景，遇到直接对照解决；
  5. 核心原则 + 口诀，快速记忆索引失效所有场景。
http://www.cnnetsun.cn/news/1591068.html

相关文章：

  • 3个核心价值：APKMirror安全下载与管理指南
  • 告别重装！用Timeshift给你的Ubuntu系统做个‘时光机’，轻松备份与整盘迁移
  • Python 正则表达式详解：从原理到实践
  • Redis配置文件（redis.conf）超详细详解
  • 别再硬调PI参数了！手把手教你用MATLAB/Simulink搞定PMSM FOC电流环整定（附模型下载）
  • 用Python+Matplotlib动手验证：标准DH和改进DH建模同一机械臂，结果真的相同吗？
  • 探索Pandas中的技术指标：RSI与EMA
  • 环形链表（力扣100）
  • 深度学习实战：从零构建验证码识别模型
  • 为什么92%的Java工程师写错Vector API？：20年JVM性能调优老兵复盘的3个反直觉真相
  • 像素时装锻造坊：零基础5分钟快速部署，开启你的AI像素时装设计之旅
  • ARM单片机中断机制与Cortex-M3优化解析
  • 移植U-Boot驱动到XSDK裸机程序：以RTL8211FS在Zynq上的网络调试为例
  • 【5G NTN语音增强】面向应急通信的IoT NTN低时延语音方案设计与信令优化
  • 3个维度解析G-Helper：华硕笔记本性能优化的轻量级解决方案
  • 5步解锁帧率自由：开源工具突破原神60帧限制全指南
  • 告别笨重电感！用这颗TI的TPS60503电荷泵芯片，给你的便携设备做个高效小体积电源
  • 中科蓝讯AB565X蓝牙耳机通话电流音、回声、杂音？手把手教你用PC工具调通它
  • DXVK架构深度解析：Vulkan驱动的Direct3D转换层技术演进与性能突破
  • 嵌入式SD卡文件处理轻量级工具库LC_SDTools
  • MZmine 3质谱数据分析实战：从原始数据到生物学洞察的完整指南
  • Kind 环境下 Flannel IPsec 模式跨节点通信故障排查流程
  • CPUDoc：革命性CPU智能调度引擎，释放处理器隐藏性能潜能
  • N15 I²C（串行通信总线）
  • 远程控制频繁断连的排查与优化方案
  • 模糊逻辑在年龄分类中的MATLAB实现与优化
  • Java记录模式深度解析：3种你绝对想不到的嵌套解构用法（JDK 21新特性权威解读）
  • 百考通：AI全流程智能化赋能实践报告，让实习总结高效又专业
  • PvZ Toolkit：开源植物大战僵尸修改工具的全方位游戏增强方案
  • Vue3大屏开发踩坑记：transform缩放导致地图偏移的3种解决方案