专栏首页JavaEEMySQL索引失效分析

MySQL索引失效分析

为了演示索引失效的问题,首先来建一张表:

create table staffs(
   id int primary key auto_increment,
   name varchar(100) not null default '' comment '姓名',
   age int not null default 0 comment '年龄',
   pos varchar(100) not null default '' comment '职位',
   add_time timestamp not null default current_timestamp comment '入职时间'
) charset utf8 comment '员工表';

insert into staffs(name, age, pos, add_time) values('张三', 22, 'manager', now());
insert into staffs(name, age, pos, add_time) values('july', 23, 'dev', now());
insert into staffs(name, age, pos, add_time) values('tom', 23, 'dev', now());
insert into staffs(name, age, pos, add_time) values('2000', 23, 'dev', now());

alter table staffs add index idx_staffs_nameAgePos(name, age, pos);

索引问题大概可以分为以下十种情况:

  • 最好的情况就是全值匹配
  • 最佳左前缀法则
  • 不在索引列上做任何操作(计算、函数、类型转换),这些操作会导致索引失效
  • 存储引擎不能使用索引中范围条件右边的列
  • 尽量使用覆盖索引(查询列和索引列一致),避免select *
  • MySQL中使用不等于(!= 或者 <>)的时候会导致索引失效
  • is nullis not null也无法使用索引
  • like以通配符开头('%abc')会导致索引失效
  • 字符串不加单引号索引会失效
  • 少用or,用它来连接时索引会失效

下面就详细说说这十种情况。

1. 全值匹配:

staffs表建表时我们建立了一个联合索引,如下:

联合索引

可以看到,一楼是name,二楼是age,三楼是pos。

依次查看下面语句的执行计划:**

explain select  * from staffs where name = 'july';
explain select  * from staffs where name = 'july' and age = 23;
explain select  * from staffs where name = 'july' and age = 23 and pos = 'dev';

执行结果如下:

结果

第三种情况,就是全值匹配。即我们建立的索引一楼是name,二楼是age,三楼是pos,查询的条件也是先name再age最后pos,从上面图中第三条语句的执行计划可以看出,使用三个const,效率是很高的。

2. 最佳左前缀法则:

再执行下面的语句,看结果:

explain select  * from staffs where age = 23 and pos = 'dev';
explain select  * from staffs where pos = 'dev';
explain select  * from staffs where name = 'july';
explain select  * from staffs where name = 'july' and pos = 'dev';

结果

可以发现,第一第二条语句的索引是失效了,而第三和第四条是用到了索引的。第一第二条没有用到name,即把一楼楼梯拆了,所以二楼三楼也就用不到了;第三条用到了一楼,所以没问题;第四条用到了一楼和三楼,但是中间的二楼没了,不能直接跳到三楼,所以也只能用到一楼,会部分失效。这就是最佳左前缀法则,即一楼一定不能少,带头大哥不能死,否则就会导致索引全部失效,中间兄弟不能断,否则会导致索引部分失效。

那么如果是这样的语句能不能用到索引呢?

explain select  * from staffs where pos= 'dev' and age = 23 and name= 'july';

看结果:

执行计划

可以看到,三个索引都用到了。我们建立的索引是name,age,pos,用的时候反过来了,但是这个并不影响,带头大哥没死,中间兄弟没断,经过MySQL的优化器,就会自动进行调整,以达到最优。

如果是这样呢?

explain select  * from staffs where name = 'july' and pos > 'dev' and age = 23;

执行计划

可以看到,key len还是608,说明还是三个都用到了。为什么?不是说范围之后全失效吗?别忘了,优化器会把age条件放到前面去,pos条件放到最后,所以三个都可以用上。

3. 不在索引列做任何操作:

执行下面的语句:

explain select * from staffs where name = 'july';
explain select * from staffs where left(name,4) = 'july';

第一条语句是用name查询,第二条语句是在name列上包了一个函数,即查询name列左边四位等于july的记录。查看执行计划如下:

执行计划

可以看到,第一句是用到了索引的,但是第二句没有,因为第二句中索引列使用了函数。所以索引列上少计算

4. 存储引擎不能使用索引中范围条件右边的列:

这个是啥意思?请看案例,执行下面两条sql:

explain select  * from staffs where name = 'july' and age = 23 and pos = 'dev';
explain select  * from staffs where name = 'july' and age > 23 and pos = 'dev';

执行计划

第一条语句毫无疑问,全值匹配,最佳情况。第二条,带头大哥没死,中间兄弟没断,索引列上没计算,但是age不是常量,给的是一个范围,结果执行计划看到的是range。这种情况,name索引用到了,age也用到了,但不是精确检索,而是一个范围,最后的pos就没有用到,所以结论就是范围之后全失效

5. 尽量使用覆盖索引(查询列和索引列一致),避免select *

查看以下两句的执行计划:**

explain select  * from staffs where name = 'july' and age = 23 and pos = 'dev';
explain select  name, age, pos from staffs where name = 'july' and age = 23 and pos = 'dev';

执行计划

可以看到,如果查询字段和索引列完全一致,或者在索引列的范围内,比如select name, age,那么extra中是有using index的,这个效率是高于select *的。

6. MySQL中使用不等于(!= 或者 <>)的时候会导致索引失效:

查看下面语句的执行计划:

explain select  * from staffs where name != 'july' and age = 23 and pos = 'dev';
explain select  * from staffs where name <> 'july' and age = 23 and pos = 'dev';

执行计划

可以看到,使用了!=或者<>确实导致索引失效了。

7. is null,is not null也无法使用索引:

查看下面语句的执行计划:

explain select  * from staffs where name is null and age = 23 and pos = 'dev';
explain select  * from staffs where name is not null and age = 23 and pos = 'dev';

执行计划

is null的情况是最糟糕的,所以我们数据列如果经常用来当查询条件的话,最好设置默认值,而不能让它为null。

8. like以通配符开头('%abc')会导致索引失效:

查看如下语句的执行计划:

explain select  * from staffs where name like '%july';
explain select  * from staffs where name like 'july%';
explain select  * from staffs where name like '%july%';

结果如下:

执行计划

根据结果可以发现,只要左边出现了百分号,那么索引就失效了。所以百分like加右边。但是有些情况必须得百分号写左边,那么怎么解决索引失效的问题呢?一般我们会采用覆盖索引来解决。比如上面这种情况,不要select *,像下面这样就行了:

explain select  name from staffs where name like '%july%';
explain select  age from staffs where name like '%july%';
explain select  age,pos from staffs where name like '%july%';
explain select  name,age,pos from staffs where name like '%july%';

执行计划

可以看到,全部都是用到了索引的。

like也是表示范围,但是如果是百分号写右边,这种范围和大于小于是不一样的,百分号写右边的like,后面的字段索引也是不会失效的。

9. 字符串不加单引号索引会失效:

查看如下语句执行计划:

explain select  * from staffs where name = '2000';
explain select  * from staffs where name = 2000;

执行计划

可以发现,没加单引号,就会导致索引失效的。varchar类型的,没加单引号,存在类型转换,从而索引失效。

10. 少用or,用它来连接时索引会失效:

查看下面语句的执行计划:

explain select  * from staffs where name = '2000' or name = 'july';

执行计划

可以看到,用了or以后,索引失效了。

总结:

全值匹配我最爱, 最左前缀要遵守; 带头大哥不能死, 中间兄弟不能断; 索引列上少计算, 范围之后全失效; 模糊百分写最右, 覆盖索引不写星; 不等空值还有或, 索引失效要少用; 字符引号不可丢, 牢记以上就无忧。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MySQL 索引失效

    Parker
  • MySQL索引失效

    但也可能因为你使用错误的SQL语句而无法使用。其中有以下几种,在使用sql查询时尽量避免。

    兜兜毛毛
  • MySQL 索引失效问题

    除了上面的几个明显的问题外,还有索引的选择问题。MySQL 在执行一段 sql 的时候,会先决定使用哪一个索引,如果 选了一个性能比较差的索引,即使走了索引,也...

    付威
  • Mysql索引失效的场景

    可以使用explain命令加在要分析的sql语句前面,在执行结果中查看key这一列的值,如果为NULL,说明没有使用索引。

    php007
  • MySQL索引失效及使用索引的优缺点

    由以下三张图的key_len字段我们可以得出三个索引的长度分别为:title长303,author长122,price长5.

    Java学习录
  • MySQL索引分析(一)

    在学习MySQL开发规范-索引规范的时候,强调过一个要点:每张表都建议有主键。我们在这里来简单分析一下为什么?

    jeanron100
  • 索引失效的场景有哪些?索引何时会失效?

    虽然你这列上建了索引,查询条件也是索引列,但最终执行计划没有走它的索引。下面是引起这种问题的几个关键点。

    民工哥
  • MySQL索引优化分析

    为什么你写的sql查询慢?为什么你建的索引常失效?通过本章内容,你将学会MySQL性能下降的原因,索引的简介,索引创建的原则,explain命令的使用,以及ex...

    lyb-geek
  • MySQL查询索引分析

    前一段时间修改数据表时,给一个表添加一个datetime字段,当时遇到了一个问题:我是否需要给该datetime字段上加索引呢?如果不给该字段加索引,当wher...

    kevindang

扫码关注云+社区

领取腾讯云代金券