MySQL 索引失效的常见原因

Aug 5, 2026 // 大蚊子

1.1 概述有时候不知道小伙伴有没有跟我一样的情况，明明已经建立了索引，但是通过explain发现语句并没有使用上索引，这可能是某些原因导致了我们的索引失效。所以本篇主要介绍的是索引失效的常见原因和如何用好索引，给有需要的朋友一个参考。 1.2 索引失效 1.2.1 索引失效常见原因我们先来看一张图，总结了常见的索引失效的原因 1.2.2 索引失效常见误区 1.2.3 索引设计的几个建议

1.3 索引示例 1.3.1 准备工作创建一张表 test1 CREATE TABLE test1 ( id bigint NOT NULL, code varchar(30) NOT NULL, age int NOT NULL, name varchar(30) NOT NULL, height int NOT NULL, PRIMARY KEY (id), KEY idx_code_age_name (code,age,name) USING BTREE, KEY idx_height (height) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 插入两条数据：

INSERT INTO test1(id, code, name, age,height) VALUES (1, ‘001’, ‘张飞’, 18,‘7’); INSERT INTO test1(id, code, name, age,height) VALUES (2, ‘002’, ‘关羽’, 19,‘8’); 查询一下： select * from test1; 结果：此外建立了两个索引：idx_code_age_name( code , age , name )联合索引和 idx_height(height)普通索引 1.3.2 索引失效常见原因 1.3.2.1 不满足最左前缀原则 1.3.2.1.1 第1种情况 where 条件后的字段包含了联合索引的所有索引字段，并且顺序是按照： code 、 age 、 name 执行sql如下： explain select * from test1 where code=‘001’ and age=18 and name=‘张飞’ ; 可以看出已经走了联合索引idx_code_name_age，并且索引的长度是 188， 188 = 30 * 3 + 2

30 * 3 + 2 + 4 ，索引是使用充分的，索引使用效率最佳。 1.3.2.1.2 第2种情况

where 条件后的字段包含了联合索引的所有索引字段，顺序是不按照： code 、 age 、 name。执行sql如下： explain select * from test1 where code=‘001’ and name=‘张飞’ and age=18; 可以看出执行结果跟第一种情况一样。注意：这种情况比较特殊，在查询之前mysql会自动优化顺序 1.3.2.1.3 第3种情况 where 条件后的字段包含了联合索引中的： code 字段执行sql如下： explain select * from test1 where code=‘001’; 也走了索引，但是索引长度有所变化，现在变成了 92 ， 92 = 303 + 2 ，只用到了一个索引字段code，索引使用不充分。1.3.2.1.4 第4种情况where 条件后的字段包含了联合索引中的： age 字段执行sql如下：explain select * from test1where age=18;12从上图中看变成了全表扫描，所有的索引都失效了。1.3.2.1.5 第5种情况where 条件后的字段包含了联合索引中的： name 字段执行sql如下：explain select * from test1where name=‘张飞’;12从上图中看变成了全表扫描，所有的索引都失效了。1.3.2.1.6 第6种情况where 条件后的字段包含了联合索引中的： code 和 age 字段执行sql如下：explain select * from test1where code=‘001’ and age=18;12从上图中看出还是走了索引，但是索引长度变成了： 96 ， 96 = 303 + 2 + 4 ，只用到了两个索引字段code和age，索引使用也不充分。 1.3.2.1.7 第7种情况 where 条件后的字段包含了联合索引中的： code 和 name 字段执行sql如下： explain select * from test1

where code=‘001’ and name=‘张飞’; 从上图中看出走的索引长度跟第3种情况一样，长度也是 92 。也就是说只用到了一个索引字段 code ，而 age 字段的索引失效了。 1.3.2.1.8 第8种情况 where 条件后的字段包含了联合索引中的： age 和 name 字段执行sql如下： explain select * from test1 where age=18 and name=‘张飞’; 从上图中看出变成了全表扫描，所有的索引都失效了。 1.3.2.2 小结如果中间出现断层，如： code、name ，只会走第一个索引code，从断层后的索引都会失效。 SQL语句中字段的顺序不需要和联合索引中定义的字段顺序一致，查询优化器会自己调整顺序，mysql查询优化器会判断纠正这条sql语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划。所以，当然是我们能尽量的利用到索引时的查询顺序效率最高咯，所以 mysql查询优化器会最终以这种顺序进行查询执行 1.3.3 范围索引列没有放在最后 where 条件后的字段 age 用了大于等于，具体sql如下： EXPLAIN select * from test1 where code=‘001’ and age>18 and name=‘张飞’ ; 从上图中看出索引长度变成： 96 ， 96 = 30*3 + 2 + 4 ，只用到了两个索引字段 code 和age ，而 name 字段的索引失效了。如果范围查询的语句放到最后：

EXPLAIN select * from test1 where code=‘001’ and name=‘张飞’ and age>18 ; 索引长度还是： 96 这是一个非常经典的错误范围查询放最后是指创建联合索引的字段顺序，现在的顺序是：调整一下把索引字段name和age的顺序调整一下：再执行上面的sql，结果：从上图中看出索引长度变成： 188 ，索引使用充分了。回过头再执行刚开始的那条sql： EXPLAIN select * from test1 where code=‘001’ and age>18 and name=‘张飞’; 索引长度也是： 188 。注意：范围查询放最后，指的是联合索引中的范围列放在最后，不是指where条件中的范围列放最后。如果联合索引中的范围列放在最后了，即使where条件中的范围列没放最后也能正常走到索引。 1.3.4 使用了select * 其实在《阿里巴巴开发手册》中也明确说了，禁止使用select * 。 EXPLAIN select * from test1 可以看出走了全表扫描。那么如果查询的是索引列：

EXPLAIN select code,age,name from test1 可以看出这种情况走了全索引扫描，比全表扫描效率更高。其实这里用到了：覆盖索引。如果 select 的列都是索引列，则被称为覆盖索引。如果 select 的列不只包含索引列，则需要回表，即回到表中再查询出其他列，效率相当更低一些。 select * 大概率需要查询非索引列，需要回表，因此要少用。当然，本文中很多示例都使用了 select * ，主要是我表中只有两条数据，为了方便演示，正常业务代码中是要杜绝这种写法的。 1.3.5 索引列上有计算执行sql如下： explain select * from test1 where height+1 =7; 可以看出变成全表扫描了，由此可见在索引列上有计算，索引会失效。 1.3.5 索引列上使用了函数如果在索引列加某个函数，具体sql如下： explain select * from test1 where SUBSTR(height,1,1)=8; 从上图中可以看出变成全表扫描了，由此可见在索引列上加了函数，索引也会失效。 1.3.6 字符类型没加引号 explain select * from test1 where name = 123;

可以看出走的全表扫描，索引失效了。 name 字段是字符类型，而等于号右边的是数字类型，类型不匹配导致索引丢失。所以在使用字符类型字段做判断时，一定要加上单引号。类型不匹配导致索引丢失问题，是我们平时工作中非常容易忽视的问题，一定要引起足够的重视。 1.3.7 用is null和is not null没注意字段是否允许为空前面创建的test1表中height字段是非空的。查询sql如下： explain select * from test1 where height is null; explain select * from test1 where height is not null; 结果都是全表扫描，索引都失效了。如果height字段改成允许为空的呢？上面第一条sql执行结果：看出走了 ref 类型的索引。上面第二条sql执行结果：

看出走了 range 类型的索引。 1.3.7.1 小结如果字段不允许为空，则is null 和 is not null这两种情况索引都会失效。如果字段允许为空，则is null走 ref 类型的索引，而is not null走 range 类型的索引。 1.3.8 like查询左边有% like查询主要有三种情况： like ‘%a’ like ‘a%’ like ‘%a%’ 1.3.8.1 第一种情况 explain select * from test1 where code like ‘%001’; 看出走的全表扫描，索引失效了。 1.3.8.2 第二种情况 explain select * from test1 where code like ‘001%’; 看出走的 range 类型的索引。 1.3.8.3 第三种情况 explain select * from test1 where code like ‘%001%’;

看出走的全表扫描，索引也失效了。从这三种结果看出 like 语句只有 % 在右边才能走索引。如果有些场景就是要使用 like 语句 % 在左边该怎么办呢？答案：使用覆盖索引具体sql如下： explain select code,age,name from test1 where code like ‘%001%’; 看出走的 index 类型的全索引扫描，相对于全表扫描性能更好。当然，最佳实践是在 sql 中要避免 like 语句 % 在左边的情况，如果有这种业务场景可以使用 es 代替 mysql 存储数据。 1.3.8.4 小结 like ‘%a’ 索引失效 like ‘a%’ 走range类型索引 like ‘%a%’ 索引失效 1.3.9 使用or关键字时没有注意用法如下： explain select * from test1 where height = 8 or height = 9; 看出走了 range 类型的索引再把sql改一下： explain select * from test1 where code = ‘001’ or height = 8;

可以看出变成了全表扫描，索引失效了。我们不妨单独查询一下： explain select * from test1 where code = ‘001’; 和 explain select * from test1 where height = 8; 两种单独查询的情况都走了 ref 类型的索引，但是使用 or 关键字后sql的索引会失效。那么，我们在想使用 or 的场景，又想让索引有效，该怎么办呢？ explain (select * from test1 where code = ‘001’) union (select * from test1 where height = 8); 没错，使用 union 关键字，但是跟 or 关键字的语法稍微有点区别，不过查询的数据结果是一样的。上面sql执行结果如下：走了 ref 类型索引。 or关键字会让索引失效，可以用union代替 1.4 索引失效的常见误区 1.4.1 使用not in会导致索引失效用法如下： explain select * from test1 where height not in (7,8); 看出是走了 range 类型索引的，并没失效。

需要特别说明的是mysql5.7和5.8不同的版本效果不一样，5.7中这种情况sql执行结果是全表扫描，而5.8中使用了 range 类型索引。 1.4.2 使用不等于号会导致索引失效用法如下： explain select * from test1 where height!=8; 看出走了 range 类型的索引。需要特别说明的是mysql5.7和5.8不同的版本效果不一样，5.7中这种情况sql执行结果是全表扫描，而5.8中使用了 range 类型索引。5.7中如果想使用索引该怎么办呢？答案：使用大于和小于代替不等于。在这里温馨的提醒一声，不等于号不只是 != ，还包括 <> 。 1.4.3 order by索引字段顺序不当导致索引失效 sql中除了 where 后面的字段能走索引之外， order by 后面的字段也能走索引。 EXPLAIN select * from test1 where code=‘001’ order by age,name; 看出走了 ref 类型的索引，索引长度是 92 ，并且没有额外信息。但是如果把 order by 后面的条件改成如下两种排序： EXPLAIN select * from test1 where code=‘001’ order by name; EXPLAIN select * from test1 where code=‘001’ order by name,age;

看出还是走了 ref 类型的索引，索引长度是 92 ，但是额外信息中提示： Using filesort ，即按文件重排序。上面两个例子能够看出有没有使用索引跟 where 后面的条件有关，而跟 order by 后面的字段没关系。而需不需要按文件重排序，则跟 order by 后面的字段有直接关系。问题来了，额外信息中提示： Using filesort 这种该如何优化？答：这种情况一般是联合索引中索引字段的顺序，跟 sql 中 where 条件及 order by 不一致导致的，只要顺序调整一致就不会出现这个问题。 1.5 索引设计建议 😀 😀 😀 😀 😀 😀 😀 😀

1.6 特别说明索引失效除了上述的常见问题之外，mysql 通过索引扫描的行记录数超过全表的10%~30% 左右，优化器也可能不会走索引，自动变成全表扫描。