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;