MySQL中索引失效的N种情况

下面的示例，现场演示了具体有哪些情况会出现索引失效。

例：表结构及数据如下：

select * from tb_user;
show index from tb_user;

建立了username、age、gender的联合索引

1、全值匹配我最爱，最左前缀要遵守

where条件中，联合索引时一定要遵从最左前缀原则，一旦跳过某一列，其后的索引将会失效。当然，全值匹配的效率是最高的

explain select * from tb_user where username = 'ye17186';
explain select * from tb_user where username = 'ye17186' and age = 15;
explain select * from tb_user where username = 'ye17186' and age = 15 and gender = 'M'

可以看到，三个查询都走了索引。

2、带头大哥不能死，中间兄弟不能丢

然后试着去掉where条件中的username或者gender

explain select * from tb_user where age = 15 and gender = 'M';
explain select * from tb_user where username = 'ye17186' and gender = 'M';

前一种，索引完全失效；后一种，只有部分索引生效（username有效）

3、索引列上少计算

在索引列上进行任何操作，计算、函数、类型转换（下例中username = 1，即将varchar转为了int）等等，都会导致索引失效

explain select * from tb_user where left(username, 2) = 'ye';

explain select * from tb_user where username = 1;

4、范围之后全失效

在索引列上进行大于大于这类的比较后，这个列的索引是有效的，但是其后的索引将失效

explain select * from tb_user where username = 'ye17186' and age = 15;
explain select * from tb_user where username = 'ye17186' and age > 15;
explain select * from tb_user where username = 'ye17186' and age > 15 and gender = 'M';

2和1比较，可以发现type有ref变为了range，key_len都为38，索引username和age的索引是有效的

3和2比较，where条件多了个gender='M'，但是key_len还是38，没有发生变化，说明gender列上的索引失效。

5、like百分写最右

like查询时，百分号%写在最左边将导致索引失效

explain select * from tb_user where username like '171%';
explain select * from tb_user where username like '%000';

6、覆盖索引不写*

尽量使用覆盖索引，而不要用select *。示例中，如果我们业务上只需要使用username、age、gender这三个字段，那么我们就使用select username, age, gender，而不要用select *。这是因为这三个字段在索引中就已经维护了它们的值，定位索引后，就能检索出它们的值。如果使用select *的话，定位到索引后，由于索引没有维护city的值，所以其后还会去检索city的值，造成了时间开销。

explain select username, age, gender from tb_user where username = 'ye17186';
explain select * from tb_user where username = 'ye17186';

7、不等空值还是or，索引影响要注意

使用不等（!=、<>），or，is null 或not null时，可能会影响索引

explain select * from tb_user where username != 'ye17186';
explain select * from tb_user where username = 'ye17186' or username = 'ye';
explain select * from tb_user where username = 'ye17186' and age is not null;

其中，看图3，key_len为3，与单独使用username效果一致，说明后面的age列上的索引失效。

8、varchar引号不能丢

同3中的类型转换

explain select * from tb_user where username = 1;