MySQL索引失效？这8种情况我都踩过

写这篇是因为上周Code Review看到同事写了一条查询加了索引但慢得离谱explain 一看 type 是 ALL全表扫描。索引失效这个问题几乎每个写过一段时间 SQL 的人都踩过。我把自己遇到过的情况整理了一下8种每种附上 SQL 例子对照着查比较直接。先说怎么判断索引有没有生效EXPLAIN SELECT * FROM user WHERE name 张三;看type字段ALL→ 全表扫描索引没用上index→ 扫了整个索引效果有限range→ 范围查询索引生效ref→ 普通索引查找正常eq_ref/const→ 最优主键或唯一索引type是ALL就说明索引失效了需要排查原因。情况一对索引列做了函数操作-- 失效对 create_time 列用了函数 SELECT * FROM order WHERE DATE(create_time) 2024-01-01; -- 生效改成范围查询 SELECT * FROM order WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00;只要对索引列做了任何函数操作DATE、YEAR、SUBSTRING、LENGTH……索引就失效。MySQL 没法用索引直接找函数计算之后的值。情况二对索引列做了运算-- 失效索引列参与了加减运算 SELECT * FROM product WHERE price 10 100; -- 生效把运算移到右边 SELECT * FROM product WHERE price 90;和函数同理索引列一旦出现在运算式里索引就废了。情况三联合索引没遵守最左前缀假设有联合索引(a, b, c)-- 生效 SELECT * FROM t WHERE a 1; SELECT * FROM t WHERE a 1AND b 2; SELECT * FROM t WHERE a 1AND b 2AND c 3; -- 失效跳过了 a SELECT * FROM t WHERE b 2; SELECT * FROM t WHERE b 2AND c 3; -- 部分生效a 生效但 c 跳过了 b 所以不生效 SELECT * FROM t WHERE a 1 AND c 3;联合索引必须从最左边的列开始用中间不能断。这是联合索引最容易踩的坑。情况四使用了 ! 或 -- 失效 SELECT * FROMuserWHEREstatus ! 1; SELECT * FROMuserWHEREstatus 1; -- 考虑换一种写法如果状态值有限 SELECT * FROMuserWHEREstatusIN (0, 2, 3);不等于查询通常会让 MySQL 觉得扫全表比用索引更划算直接放弃索引。情况五LIKE 以通配符开头-- 失效% 在前面 SELECT * FROM user WHERE name LIKE %张; SELECT * FROM user WHERE name LIKE %张%; -- 生效% 只在后面 SELECT * FROM user WHERE name LIKE 张%;%开头意味着前缀不确定索引没法用。如果业务确实需要模糊搜索考虑全文索引或者 Elasticsearch。情况六字符串没加引号隐式类型转换-- 假设 phone 字段是 VARCHAR 类型有索引 -- 失效传了数字发生隐式类型转换 SELECT * FROM user WHERE phone 13812345678; -- 生效加上引号 SELECT * FROM user WHERE phone 13812345678;MySQL 在比较时如果字段类型和传入值类型不一致会对字段做隐式转换等于对索引列用了函数索引失效。这个问题在代码里很隐蔽因为很多时候 ORM 框架帮你拼 SQL参数类型写错了不会报错只是变慢了。情况七OR 连接的条件有一个没有索引-- 假设 name 有索引age 没有索引 -- 失效OR 两边只要有一个没索引整个查询就走全表扫描 SELECT * FROMuserWHEREname 张三OR age 25; -- 解决给 age 也加上索引或者改用 UNION SELECT * FROMuserWHEREname 张三 UNION SELECT * FROMuserWHERE age 25;OR 查询时MySQL 需要把两个条件的结果合并如果其中一个条件没有索引没法走索引合并直接全表扫。情况八数据量太少MySQL 觉得全表扫更快-- 表里只有几百条数据即使有索引MySQL 可能直接走全表扫描 -- explain 看到 type ALL但实际上不是索引失效是优化器的选择这不是真正的索引失效是 MySQL 查询优化器的决策——当表数据量小或者查询结果集占比大的时候全表扫描的成本比走索引还低优化器会主动选择全表扫。测试环境数据少的时候经常遇到这种情况上线数据量大了之后反而自动走索引了。一张表总结情况原因解决方向对索引列用函数索引值被变换无法直接查改写 SQL把函数移到右边对索引列做运算同上把运算移到右边联合索引不满足最左前缀索引树结构决定的调整查询条件或索引顺序使用 ! / 结果集太大优化器放弃改用 IN 或范围查询LIKE 以 % 开头无法确定前缀索引无用全文索引 / ES字符串未加引号隐式类型转换注意参数类型加引号OR 有一边无索引无法走索引合并补充索引或改 UNION数据量太小优化器主动选全表扫不用管上线自然好索引失效大部分时候都能通过EXPLAIN发现养成写完慢查询就EXPLAIN一下的习惯能省很多事。遇到过其他奇怪的索引失效场景欢迎评论区说说不定你的经历能帮到别人。