MySQL 索引失效的 7 个坑，我踩过 4 个：函数、隐式转换、左模糊、OR

导读慢查询日志天天报警EXPLAIN 一看 typeALL 全表扫。索引明明建了就是不生效查了一周把 MySQL 索引失效的场景踩了个遍。这篇把我踩过的 4 个坑和另外 3 个高频场景整理出来每一条都带 SQL 和修复写法建议收藏。MySQL 索引失效的 7 个坑我踩过 4 个函数、隐式转换、左模糊、OR先说最典型的场景。职位表 50 万行按发布时间查SELECT*FROMjob_WHEREDATE(create_time)2026-10-01;create_time明明建了索引EXPLAIN 却显示typeALL全表扫 50 万行。问题就出在DATE()函数把索引列包住了。坑一对索引列用函数索引直接报废-- 慢DATE() 包住索引列MySQL 无法用索引SELECT*FROMjob_WHEREDATE(create_time)2026-10-01;-- 快改成范围查询索引正常生效SELECT*FROMjob_WHEREcreate_time2026-10-01 00:00:00ANDcreate_time2026-10-02 00:00:00;索引列上做任何函数运算、算术运算索引都失效。这是最常见的坑我栽过两次。坑二隐式转换VARCHAR 列传数字用户表phone是 VARCHAR接口收到的是数字-- 慢phone 是 VARCHAR传数字触发隐式转换索引失效SELECT*FROMuser_WHEREphone13700001234;-- 快带引号类型匹配索引生效SELECT*FROMuser_WHEREphone13700001234;MySQL 对字符串列和数字比较会把字符串转成数字再比较相当于对索引列做了函数转换。修复方案代码层保证类型一致或者查 SQL 看EXPLAIN的 key 是不是 null。坑三左模糊 LIKE%在开头必失效-- 慢% 在开头索引树没法定位SELECT*FROMcompany_WHEREnameLIKE%科技%;-- 快后缀匹配可以用索引但实际业务很少这么查SELECT*FROMcompany_WHEREnameLIKE晴空%;%keyword%这种中间模糊基本告别索引。搜索场景要么上 ES要么用前缀匹配别指望 MySQL 索引扛全量模糊搜索。我项目里职位搜索直接走 ESMySQL 只做精确匹配。坑四OR 连接一侧没索引全表扫-- 慢id 有索引status 没有OR 导致全表扫SELECT*FROMjob_WHEREid123ORstatus1;-- 快拆成两个查询 UNION 合并SELECT*FROMjob_WHEREid123UNIONSELECT*FROMjob_WHEREstatus1;OR 要两侧都能用索引才走索引合并只要有一侧扫全表整个查询就全表扫。要么给 status 补索引要么拆 UNION。坑五联合索引没按最左前缀建了idx_tenant_status (tenant_id, status)但查询只带 status-- 慢跳过了最左列 tenant_id联合索引用不上SELECT*FROMjob_WHEREstatus1;-- 快带上最左列SELECT*FROMjob_WHEREtenant_id1ANDstatus1;联合索引最左前缀原则查询条件必须从联合索引的第一列开始。中间跳过某一列后面的列也失效。坑六对索引列做运算-- 慢对列做运算SELECT*FROMjob_WHEREsalary*1.110000;-- 快运算放到常量侧SELECT*FROMjob_WHEREsalary10000/1.1;列在运算左侧索引失效。把运算挪到等号/不等号右侧让索引列保持裸列。坑七NOT IN / IS NOT NULL选择性太低-- 慢NOT IN 优化器评估走索引不如全表快SELECT*FROMjob_WHEREstatusNOTIN(2,3);-- 慢IS NOT NULL 选择性低SELECT*FROMjob_WHEREremarkISNOTNULL;这类条件优化器经常选择全表扫。能给默认值别留 NULL表设计时把可空字段尽量NOT NULL DEFAULT 省一堆麻烦。踩坑索引建了不生效字符集不一致现象职位表和公司表 JOIN 查公司名company_id两边都建了索引EXPLAIN 却是全表扫 驱动表都不对。排查过程SHOW CREATE TABLE对比两张表一个 utf8 一个 utf8mb4。JOIN 时字符集不同MySQL 要先转码再比较索引失效。定位思路JOIN 两边的列类型、字符集、排序规则必须一致否则索引用不上。最终解决统一改成 utf8mb4ALTERTABLEcompany_CONVERTTOCHARACTERSETutf8mb4COLLATEutf8mb4_unicode_ci;改完 EXPLAIN 变成typeref从 8 秒降到 30ms。例外数据量小的时候优化器可能故意不走索引别一看到 typeALL 就觉得索引废了。表里总共 500 行数据全表扫比走索引快索引要两次 IO索引树 回表优化器直接选全表扫这是正常行为。判断标准很简单数据量上来几十万行还 typeALL那才是真失效。用EXPLAIN看 rows 估算值和数据总量对比如果rows接近总量大概率索引没生效或选择性太低。可直接复用的要点索引列禁止函数、运算、隐式转换保持裸列。VARCHAR 列查询永远带引号JOIN 两列类型、字符集、排序规则一致。%关键词%搜索别指望 MySQL 索引上 ES。OR 两侧都要有索引否则拆 UNION。联合索引遵循最左前缀字段顺序按查询频次排。表设计少留 NULL能 DEFAULT 就 DEFAULT。写完 SQL 养成习惯EXPLAIN 看一眼 key 和 typetype 不是 ref/range 就重写。