MySQL 索引失效的 10 种情况 — 我踩过的坑全记录作者没有逆称标签MySQL数据库索引学习笔记阅读时间约 15 分钟写在前面大家好我是大三在读的学生。最近在做一个课程项目数据库里塞了几十万条测试数据之后有个查询接口突然变得超级慢……打开 Datagrip 一看 EXPLAIN发现明明建了索引结果type ALL全表扫描查了一圈资料踩了一堆坑整理出来分享给大家。希望对也在学数据库的同学有帮助目录先说说什么是索引失效测试用的表情况一隐式类型转换最坑情况二违反最左前缀原则情况三对索引列用函数或运算情况四OR 条件导致失效情况五LIKE 左模糊情况六不等于 / IS NULL情况七优化器选错索引情况八JOIN 字段编码不一致情况九范围查询后的列失效情况十表数据太少不走索引怎么用 EXPLAIN 排查学习总结1. 先说说什么是索引失效简单理解索引失效 你建了索引但 MySQL 决定不用它跑去全表扫描就像你明明有通讯录索引但找人时却把整本通讯录从头到尾翻一遍全表扫描……可以用EXPLAIN命令查看 SQL 的执行计划EXPLAINSELECT*FROMordersWHEREuser_id12345;重点关注这两个字段字段含义正常值type访问类型ref/range最好ALL最差key实际用的索引不是NULL就行2. 测试用的表我建了一张订单表来测试各种情况大家可以跟着一起试CREATETABLEorders(idBIGINTPRIMARYKEYAUTO_INCREMENT,user_idVARCHAR(32)NOTNULL,merchant_idINTNOTNULL,statusTINYINTNOTNULL,-- 1待付款 2已付款 3已完成amountDECIMAL(10,2)NOTNULL,created_atDATETIMENOTNULL,KEYidx_user_id(user_id),KEYidx_merchant_status(merchant_id,status,created_at))ENGINEInnoDBDEFAULTCHARSETutf8mb4;3. 情况一隐式类型转换最坑我踩的坑-- user_id 是 VARCHAR但我传入的是数字SELECT*FROMordersWHEREuser_id12345;EXPLAIN 一看type ALL索引没生效 为什么会这样MySQL 遇到类型不匹配时会把索引列转换成数字再比较相当于偷偷改成了-- MySQL 内部实际执行的是类似这样SELECT*FROMordersWHERECAST(user_idASSIGNED)12345;-- ^^^^^^^^^^^^^^^^^^^^^^^^^-- 对索引列用了函数BTree 索引存的是原始字符串现在对列做了转换当然没法用索引了。正确写法-- ✅ 传入字符串类型匹配SELECT*FROMordersWHEREuser_id12345;小结写法user_id 类型结果user_id 12345VARCHAR❌ 索引失效user_id 12345VARCHAR✅ 索引生效经验SQL 里的字符串一定要加引号这是最容易犯的错。4. 情况二违反最左前缀原则复合索引的最左前缀原则我建了一个复合索引KEYidx_merchant_status(merchant_id,status,created_at)这个索引就像一本三级目录的书第一级目录merchant_id商家ID 第二级目录status订单状态 第三级目录created_at创建时间你必须从第一级开始查不能跳过前面的目录测试各种写法-- ✅ 全匹配索引完全生效EXPLAINSELECT*FROMordersWHEREmerchant_id100ANDstatus1ANDcreated_at2024-01-01;-- type rangekey idx_merchant_status-- ❌ 跳过 merchant_id直接从 status 开始索引失效EXPLAINSELECT*FROMordersWHEREstatus1ANDcreated_at2024-01-01;-- type ALLkey NULL-- ⚠️ 只用 merchant_id created_at跳过 status-- 索引只用到了 merchant_id 部分EXPLAINSELECT*FROMordersWHEREmerchant_id100ANDcreated_at2024-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 -- 只用 AC 失效跳过 B5. 情况三对索引列用函数或运算错误示例-- ❌ 对索引列用函数SELECT*FROMordersWHEREDATE(created_at)2024-01-01;-- ❌ 对索引列做运算SELECT*FROMordersWHEREamount*1.1100;原理BTree 索引是按照列的原始值有序存储的。DATE(created_at)会把每一行的created_at都算一遍MySQL 没法利用索引的有序性只能全表扫描。正确改写-- ✅ 改写成范围查询SELECT*FROMordersWHEREcreated_at2024-01-01 00:00:00ANDcreated_at2024-01-02 00:00:00;-- ✅ 把运算放到等号右边SELECT*FROMordersWHEREamount100/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_id12345ORamount1000;user_id有索引但amount没有索引。MySQL 无法同时用两个索引处理 OR很可能放弃使用索引。正确写法-- ✅ 改写成 UNION ALLSELECT*FROMordersWHEREuser_id12345UNIONALLSELECT*FROMordersWHEREamount1000;-- 或者给 amount 也建个索引ALTERTABLEordersADDINDEXidx_amount(amount);7. 情况五LIKE 左模糊问题 SQL-- ❌ 左模糊索引失效SELECT*FROMordersWHEREuser_idLIKE%2345;-- ❌ 全模糊索引失效SELECT*FROMordersWHEREuser_idLIKE%123%;原理BTree 索引是从左到右有序的只能做前缀匹配。索引里存的是12345, 12346, 12347... LIKE 123% → 可以用索引前缀匹配✅ LIKE %2345 → 没法用不知道前缀是什么❌正确写法-- ✅ 右模糊索引生效SELECT*FROMordersWHEREuser_idLIKE12345%;如果必须左模糊怎么办我们课程项目里有个搜索订单号的功能用户可能输入订单号的中间部分……方案用Elasticsearch做全文搜索这是后话了毕设再研究 8. 情况六不等于 / IS NULL问题 SQL-- ❌ 不等于通常索引失效SELECT*FROMordersWHEREstatus1;-- ❌ IS NULL可能失效SELECT*FROMordersWHEREupdated_atISNULL;为什么会失效status 1意味着要匹配除了 1 以外的所有状态相当于要扫描大部分数据。MySQL 优化器会算一笔账走索引成本 索引查找 回表次数可能很多 全表扫描成本 顺序 IO可能更便宜如果优化器觉得走索引更贵就会放弃索引。改写建议-- ✅ 改写成 IN等值查询索引生效SELECT*FROMordersWHEREstatusIN(2,3,4,5);9. 情况七优化器选错索引奇怪的现象-- 明明有索引但 EXPLAIN 显示 key NULLEXPLAINSELECT*FROMordersWHEREmerchant_id100ANDstatus1;原因统计信息过期了MySQL 优化器依赖**索引区分度cardinality**来做决策。如果统计信息不准确优化器会犯傻。解决方法-- 查看索引统计信息SHOWINDEXFROMorders;-- 重新采集统计信息超有用ANALYZETABLEorders;临时强制走索引-- 强制使用某个索引临时方案SELECT*FROMordersFORCEINDEX(idx_merchant_status)WHEREmerchant_id100ANDstatus1;⚠️FORCE INDEX是临时止血方案根本解法是ANALYZE TABLE。10. 情况八JOIN 字段编码不一致我踩的另一个坑-- orders 表utf8mb4_general_ci-- users 表utf8mb4_unicode_ci ← 排序规则不同EXPLAINSELECT*FROMorders oJOINusers uONo.user_idu.id;-- type ALL全表扫描原理编码或排序规则不同MySQL 会在 JOIN 时做隐式转换相当于对索引列用了函数导致索引失效。排查方法-- 查看两张表的字符集SHOWCREATETABLEorders;SHOWCREATETABLEusers;解决方法-- 统一排序规则ALTERTABLEusersCONVERTTOCHARACTERSETutf8mb4COLLATEutf8mb4_general_ci;经验建表时一定要统一字符集我们团队项目里因为这个问题熬到凌晨两点 11. 情况九范围查询后的列失效现象-- 索引(merchant_id, status, created_at)EXPLAINSELECT*FROMordersWHEREmerchant_id100ANDstatus1-- 范围查询ANDcreated_at2024-01-01;结果索引只用到了merchant_id statuscreated_at没有用到原理BTree 复合索引中第一个范围查询列之后的列索引失效。索引顺序(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_id100;-- type ALL没走索引原因当表的数据量很小时比如 1000 行全表扫描比走索引还快省去了回表的开销。这是 MySQL 的正常优化行为不是 Bug不需要处理。等数据量上去了MySQL 自然会用索引的 13. 怎么用 EXPLAIN 排查EXPLAIN 各字段含义EXPLAINSELECT*FROMordersWHEREuser_id12345;字段重点关注说明id不用管查询序号type⭐⭐⭐⭐⭐访问类型ALL 最差key⭐⭐⭐⭐⭐实际使用的索引NULL 没走key_len⭐⭐⭐索引用了多少字节判断复合索引用了几列rows⭐⭐⭐⭐预估扫描行数越大越慢Extra⭐⭐⭐Using filesort/Using temporary 危险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. 学习总结索引失效情况速查表#情况根因怎么改1隐式类型转换VARCHAR 数字字符串加引号2违反最左前缀跳过复合索引前列按索引列顺序写 WHERE3对索引列用函数破坏有序性改 SQL 或建函数索引4OR 条件多列 OR 难合并改写成 UNION ALL5LIKE 左模糊BTree 不支持改用右模糊6不等于 / IS NULL选择性差改写成 IN7优化器选错索引统计信息过期ANALYZE TABLE8JOIN 编码不一致隐式转换统一字符集9范围查询后列失效复合索引顺序错调整索引列顺序10数据量太少正常现象不管它一句话记住核心原理索引失效的本质MySQL 对索引列做了额外的事情转换、运算、函数导致没法利用 BTree 的有序性。记住这句话大部分索引问题都能想通 写在最后这篇文章是我在做课程项目时踩坑后的总结如果有哪些地方写得不对欢迎评论区指正如果对你有帮助点个赞吧 参考资料《高性能 MySQL第4版》《MySQL 必知必会》—— Ben FortaMySQL 8.0 官方文档 - EXPLAIN 输出尚硅谷 MySQL 高级教程B站有