龙空技术网

浅聊 MySQL索引失效情况

小瓜瓜666 333

前言:

今天小伙伴们对“mysql 索引不生效”大致比较关心,你们都想要剖析一些“mysql 索引不生效”的相关文章。那么小编在网络上汇集了一些有关“mysql 索引不生效””的相关知识,希望看官们能喜欢,你们一起来了解一下吧!

1、最左前缀法则

如果索引了多列(联合索引),要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始, 并且不跳过索引中的列。如果跳跃某一列,索引将会部分失效(后面的字段索引失效)。

以 tb_user 表为例,我们先来查看一下之前 tb_user 表所创建的索引。

通过show index from tb_user;语句我们可以看到tb_user表中有四个索引,一个主键索引,一个唯一索引:user_phone,两个普通索引:user_name,idx_email;

现在我们创建一个组合索引:通过三个字段:profession(专业),age,status

create index user_pro_age_status on tb_user(profession,age,status);

对于最左前缀法则指的是,查询时,最左边列,也就是profession(专业)必须存在,否则索引全部失效。 而且中间不能跳过某一列,否则该列后面的字段索引将失效。 接下来,我们来演示几组案例,看一下 具体的执行计划:

explain select * from tb_user where profession = '软件工程' and age = 31 and status = '0';

从上面的sql语句执行结果截图,我们发现是走了索引的,但是出现了回表查询;应该我们使用的是:select *

explain select * from tb_user where profession = '软件工程' and age = 31;

上面的sql也是走了索引的,因为最左列profession出现在了where后面

explain select * from tb_user where profession = '软件工程';

以上的这三组测试中,我们发现只要联合索引最左边的字段 profession存在,索引就会生效,只不过索引的长度不同。 而且由以上三组测试,我们也可以推测出profession字段索引长度为47、age 字段索引长度为2、status字段索引长度为5。

explain select * from tb_user where age = 31 and status = '0';
explain select * from tb_user where status = '0';

而通过上面的这两组测试,我们也可以看到索引并未生效,原因是因为不满足最左前缀法则,联合索引 最左边的列profession不存在。

explain select * from tb_user where profession = '软件工程' and status = '0';

上述的SQL查询时,存在profession字段,最左边的列是存在的,索引满足最左前缀法则的基本条 件。但是查询时,跳过了age这个列,所以后面的列索引是不会使用的,也就是索引部分生效,所以索 引的长度就是47。

思考题:

当执行SQL语句: explain select * from tb_user where age = 31 and status = '0' and profession = '软件工程'; 时,是否满足最左前缀法则,走不走 上述的联合索引,索引长度?

可以看到,是完全满足最左前缀法则的,索引长度54,联合索引是生效的。

注意 : 最左前缀法则中指的最左边的列,是指在查询时,联合索引的最左边的字段(即是 第一个字段)必须存在,与我们编写SQL时,条件编写的先后顺序无关。

2、范围查询

联合索引中,出现范围查询(>,<),范围查询右侧的列索引失效。

explain select * from tb_user where profession = '软件工程' and age > 30 and status = '0';

当范围查询使用> 或 < 时,走联合索引了,但是索引的长度为49,就说明范围查询右边的status字 段是没有走索引的。

explain select * from tb_user where profession = '软件工程' and age >= 30 and status = '0';

当范围查询使用>= 或 <= 时,走联合索引了,但是索引的长度为54,就说明所有的字段都是走索引 的。

所以,在业务允许的情况下,尽可能的使用类似于 >= 或 <= 这类的范围查询,而避免使用 > 或 < 。

3、索引列运算

不要在索引列上进行运算操作, 否则索引将失效。

在tb_user表中,除了前面介绍的联合索引之外,还有一个索引,是phone字段的单列索引。

//当根据phone字段进行等值匹配查询时, 索引生效。explain select * from tb_user where phone = '17799990015';

当根据phone字段进行函数运算操作之后,索引失效。

explain select * from tb_user where substring(phone,10,2) = '15';

4、字符串不加引号

字符串类型字段使用时,不加引号,索引将失效。

接下来,我们通过两组示例,来看看对于字符串类型的字段,加单引号与不加单引号的区别:

explain select * from tb_user where phone = '17799990015';explain select * from tb_user where phone = 17799990015;

经过上面两组示例,我们会明显的发现,如果字符串不加单引号,对于查询结果,没什么影响,但是数 据库存在隐式类型转换,索引将失效。

5、模糊查询

如果仅仅是尾部模糊匹配,索引不会失效。如果是头部模糊匹配,索引失效。

接下来,我们来看一下这三条SQL语句的执行效果,查看一下其执行计划:

由于下面查询语句中,都是根据profession字段查询,符合最左前缀法则,联合索引是可以生效的, 我们主要看一下,模糊查询时,%加在关键字之前,和加在关键字之后的影响。

explain select * from tb_user where profession like '软件%'; explain select * from tb_user where profession like '%工程';explain select * from tb_user where profession like '%工%';

6、or连接条件

用or分割开的条件, 如果or前的条件中的列有索引,而后面的列中没有索引,那么涉及的索引都不会 被用到。

explain select * from tb_user where id = 10 or age = 23; explain select * from tb_user where phone = '17799990017' or age = 23;

由于age没有索引,所以即使id、phone有索引,索引也会失效。所以需要针对于age也要建立索引。

然后,我们可以对age字段建立索引。

create index idx_user_age on tb_user(age);

建立了索引之后,我们再次执行上述的SQL语句,看看前后执行计划的变化。

最终,我们发现,当or连接的条件,左右两侧字段都有索引时,索引才会生效。

标签: #mysql 索引不生效 #mysql失效情况