MySQL索引失效

MySQL 索引失效的 10 种情况 — 我踩过的坑全记录

作者：没有逆称
标签：MySQL数据库索引学习笔记
阅读时间：约 15 分钟

写在前面

大家好，我是大三在读的学生。最近在做一个课程项目，数据库里塞了几十万条测试数据之后，有个查询接口突然变得超级慢……

打开 Datagrip 一看 EXPLAIN，发现明明建了索引，结果type = ALL，全表扫描

查了一圈资料，踩了一堆坑，整理出来分享给大家。希望对也在学数据库的同学有帮助～

目录

1. 先说说：什么是索引失效
2. 测试用的表
3. 情况一：隐式类型转换（最坑）
4. 情况二：违反最左前缀原则
5. 情况三：对索引列用函数或运算
6. 情况四：OR 条件导致失效
7. 情况五：LIKE 左模糊
8. 情况六：不等于 / IS NULL
9. 情况七：优化器选错索引
10. 情况八：JOIN 字段编码不一致
11. 情况九：范围查询后的列失效
12. 情况十：表数据太少，不走索引
13. 怎么用 EXPLAIN 排查
14. 学习总结

1. 先说说：什么是索引失效

简单理解：

索引失效 = 你建了索引，但 MySQL 决定不用它，跑去全表扫描

就像你明明有通讯录（索引），但找人时却把整本通讯录从头到尾翻一遍（全表扫描）……

可以用EXPLAIN命令查看 SQL 的执行计划：

EXPLAINSELECT*FROMordersWHEREuser_id='12345';

重点关注这两个字段：

2. 测试用的表

我建了一张订单表来测试各种情况，大家可以跟着一起试：

CREATETABLE`orders`(`id`BIGINTPRIMARYKEYAUTO_INCREMENT,`user_id`VARCHAR(32)NOTNULL,`merchant_id`INTNOTNULL,`status`TINYINTNOTNULL,-- 1=待付款 2=已付款 3=已完成`amount`DECIMAL(10,2)NOTNULL,`created_at`DATETIMENOTNULL,KEY`idx_user_id`(`user_id`),KEY`idx_merchant_status`(`merchant_id`,`status`,`created_at`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;

3. 情况一：隐式类型转换（最坑）

我踩的坑

-- user_id 是 VARCHAR，但我传入的是数字！SELECT*FROMordersWHEREuser_id=12345;

EXPLAIN 一看：type = ALL，索引没生效 😱

为什么会这样？

MySQL 遇到类型不匹配时，会把索引列转换成数字再比较，相当于偷偷改成了：

-- MySQL 内部实际执行的是（类似这样）：SELECT*FROMordersWHERECAST(user_idASSIGNED)=12345;-- ^^^^^^^^^^^^^^^^^^^^^^^^^-- 对索引列用了函数！

B+Tree 索引存的是原始字符串，现在对列做了转换，当然没法用索引了。

正确写法

-- ✅ 传入字符串，类型匹配SELECT*FROMordersWHEREuser_id='12345';

小结

💡经验：SQL 里的字符串一定要加引号！这是最容易犯的错。

4. 情况二：违反最左前缀原则

复合索引的"最左前缀原则"

我建了一个复合索引：

KEY`idx_merchant_status`(`merchant_id`,`status`,`created_at`)

这个索引就像一本三级目录的书：

第一级目录：merchant_id（商家ID） 第二级目录：status（订单状态） 第三级目录：created_at（创建时间）

你必须从第一级开始查，不能跳过前面的目录！

测试各种写法

-- ✅ 全匹配，索引完全生效EXPLAINSELECT*FROMordersWHEREmerchant_id=100ANDstatus=1ANDcreated_at>'2024-01-01';-- type = range，key = idx_merchant_status-- ❌ 跳过 merchant_id，直接从 status 开始，索引失效！EXPLAINSELECT*FROMordersWHEREstatus=1ANDcreated_at>'2024-01-01';-- type = ALL，key = NULL-- ⚠️ 只用 merchant_id + created_at，跳过 status-- 索引只用到了 merchant_id 部分EXPLAINSELECT*FROMordersWHEREmerchant_id=100ANDcreated_at>'2024-01-01';

最左前缀速查

索引：(A, B, C) ✅ WHERE A = 1 -- 用到 A ✅ WHERE A = 1 AND B = 2 -- 用到 A, B ✅ WHERE A = 1 AND B = 2 AND C > 3 -- 用到 A, B, C ❌ WHERE B = 2 -- 失效！跳过 A ❌ WHERE A = 1 AND C > 3 -- 只用 A，C 失效（跳过 B）

5. 情况三：对索引列用函数或运算

错误示例

-- ❌ 对索引列用函数SELECT*FROMordersWHEREDATE(created_at)='2024-01-01';-- ❌ 对索引列做运算SELECT*FROMordersWHEREamount*1.1>100;

原理

B+Tree 索引是按照列的原始值有序存储的。

DATE(created_at)会把每一行的created_at都算一遍，MySQL 没法利用索引的有序性，只能全表扫描。

正确改写

-- ✅ 改写成范围查询SELECT*FROMordersWHEREcreated_at>='2024-01-01 00:00:00'ANDcreated_at<'2024-01-02 00:00:00';-- ✅ 把运算放到等号右边SELECT*FROMordersWHEREamount>100/1.1;

MySQL 8.0 的函数索引（进阶）

如果用的是 MySQL 8.0，可以建函数索引：

-- MySQL 8.0+ 支持CREATEINDEXidx_date_createdONorders((DATE(created_at)));-- 现在这条 SQL 可以走索引了！SELECT*FROMordersWHEREDATE(created_at)='2024-01-01';

💡 我们学校实验室用的是 MySQL 5.7，所以这个特性用不了 😭 只能改 SQL。

6. 情况四：OR 条件导致失效

问题 SQL

-- ❌ OR 条件，可能导致全表扫描SELECT*FROMordersWHEREuser_id='12345'ORamount>1000;

user_id有索引，但amount没有索引。MySQL 无法同时用两个索引处理 OR，很可能放弃使用索引。

正确写法

-- ✅ 改写成 UNION ALLSELECT*FROMordersWHEREuser_id='12345'UNIONALLSELECT*FROMordersWHEREamount>1000;-- 或者：给 amount 也建个索引ALTERTABLEordersADDINDEXidx_amount(amount);

7. 情况五：LIKE 左模糊

问题 SQL

-- ❌ 左模糊，索引失效SELECT*FROMordersWHEREuser_idLIKE'%2345';-- ❌ 全模糊，索引失效SELECT*FROMordersWHEREuser_idLIKE'%123%';

原理

B+Tree 索引是从左到右有序的，只能做前缀匹配。

索引里存的是：'12345', '12346', '12347'... LIKE '123%' → 可以用索引（前缀匹配）✅ LIKE '%2345' → 没法用（不知道前缀是什么）❌

正确写法

-- ✅ 右模糊，索引生效SELECT*FROMordersWHEREuser_idLIKE'12345%';

如果必须左模糊怎么办？

我们课程项目里有个"搜索订单号"的功能，用户可能输入订单号的中间部分……

方案：用Elasticsearch做全文搜索（这是后话了，毕设再研究 😂）

8. 情况六：不等于 / IS NULL

问题 SQL

-- ❌ 不等于，通常索引失效SELECT*FROMordersWHEREstatus<>1;-- ❌ IS NULL，可能失效SELECT*FROMordersWHEREupdated_atISNULL;

为什么会失效？

status <> 1意味着要匹配除了 1 以外的所有状态，相当于要扫描大部分数据。

MySQL 优化器会算一笔账：

走索引成本 = 索引查找 + 回表次数（可能很多） 全表扫描成本 = 顺序 IO（可能更便宜）

如果优化器觉得"走索引更贵"，就会放弃索引。

改写建议

-- ✅ 改写成 IN（等值查询，索引生效）SELECT*FROMordersWHEREstatusIN(2,3,4,5);

9. 情况七：优化器选错索引

奇怪的现象

-- 明明有索引，但 EXPLAIN 显示 key = NULLEXPLAINSELECT*FROMordersWHEREmerchant_id=100ANDstatus=1;

原因：统计信息过期了

MySQL 优化器依赖**索引区分度（cardinality）**来做决策。如果统计信息不准确，优化器会"犯傻"。

解决方法

-- 查看索引统计信息SHOWINDEXFROMorders;-- 重新采集统计信息（超有用！）ANALYZETABLEorders;

临时强制走索引

-- 强制使用某个索引（临时方案）SELECT*FROMordersFORCEINDEX(idx_merchant_status)WHEREmerchant_id=100ANDstatus=1;

⚠️FORCE INDEX是临时止血方案，根本解法是ANALYZE TABLE。

10. 情况八：JOIN 字段编码不一致

我踩的另一个坑

-- orders 表：utf8mb4_general_ci-- users 表：utf8mb4_unicode_ci ← 排序规则不同！EXPLAINSELECT*FROMorders oJOINusers uONo.user_id=u.id;-- type = ALL，全表扫描！

原理

编码或排序规则不同，MySQL 会在 JOIN 时做隐式转换，相当于对索引列用了函数，导致索引失效。

排查方法

-- 查看两张表的字符集SHOWCREATETABLEorders;SHOWCREATETABLEusers;

解决方法

-- 统一排序规则ALTERTABLEusersCONVERTTOCHARACTERSETutf8mb4COLLATEutf8mb4_general_ci;

💡经验：建表时一定要统一字符集！我们团队项目里因为这个问题熬到凌晨两点 😭

11. 情况九：范围查询后的列失效

现象

-- 索引：(merchant_id, status, created_at)EXPLAINSELECT*FROMordersWHEREmerchant_id=100ANDstatus>1-- 范围查询ANDcreated_at>'2024-01-01';

结果：索引只用到了merchant_id + status，created_at没有用到！

原理

B+Tree 复合索引中，第一个范围查询列之后的列，索引失效。

索引顺序：(merchant_id, status, created_at) WHERE merchant_id = 100 AND status > 1 AND created_at > '2024-01-01' ↑ 等值，用索引 ↑ 范围，用索引 ↑ 失效！（在范围列后面）

正确的索引设计原则

等值查询列 放前面 范围查询列 放最后

-- 调整索引列顺序ALTERTABLEordersDROPINDEXidx_merchant_status,ADDINDEXidx_merchant_created_status(merchant_id,created_at,status);-- 现在 created_at 范围查询之后的 status 等值仍能走索引

12. 情况十：表数据太少，不走索引

现象

-- 表里只有 100 条测试数据EXPLAINSELECT*FROMordersWHEREmerchant_id=100;-- type = ALL，没走索引？

原因

当表的数据量很小时（比如 < 1000 行），全表扫描比走索引还快（省去了回表的开销）。

这是 MySQL 的正常优化行为，不是 Bug，不需要处理。

等数据量上去了，MySQL 自然会用索引的 👌

13. 怎么用 EXPLAIN 排查

EXPLAIN 各字段含义

EXPLAINSELECT*FROMordersWHEREuser_id='12345';

type 字段性能排序

const (单行匹配，PRIMARY KEY / UNIQUE) ↓ eq_ref (JOIN 唯一索引) ↓ ref (非唯一索引，等值匹配) ← 我们最希望看到的 ↓ range (索引范围扫描，BETWEEN / IN / >) ↓ index (全索引扫描，比 ALL 好一点) ↓ ALL (全表扫描 ⚠️ 必须优化)

我的排查步骤

1. 用 EXPLAIN 看执行计划 → type = ALL？→ 索引失效了！ 2. 检查 WHERE 条件 → 有没有对索引列用函数？ → 有没有类型转换？ → 复合索引有没有跳过前面的列？ 3. 检查 JOIN 条件 → 字段编码是否一致？ 4. 更新统计信息 → ANALYZE TABLE 表名; 5. 重新 EXPLAIN 验证

14. 学习总结

索引失效情况速查表

一句话记住核心原理

索引失效的本质：MySQL 对索引列做了"额外的事情"（转换、运算、函数），导致没法利用 B+Tree 的有序性。

记住这句话，大部分索引问题都能想通 💪

写在最后

这篇文章是我在做课程项目时踩坑后的总结，如果有哪些地方写得不对，欢迎评论区指正！
如果对你有帮助，点个赞吧 😊

参考资料

• 《高性能 MySQL（第4版）》
• 《MySQL 必知必会》—— Ben Forta
• MySQL 8.0 官方文档 - EXPLAIN 输出
• 尚硅谷 MySQL 高级教程（B站有）