<>最左前缀法则

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

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

在上述的表tb_user中,我们设置了一个联合索引,这个联合索引涉及到三个字段,顺序分别为:profession,age,status

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

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

从key中可以看出来,这段查询语句是走了联合索引的,并且索引长度为54,记住这个54后面也会继续用到

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

这次查询我们的条件没有带上status,但是也走了联合索引,因为联合索引最左边的列存在。但是索引的长度由54变成了49,少了5,也就是说对于status索引来说他的长度就是5,我们继续往后面看。
3)
explain select * from tb_user where profession = '软件工程';

当查询条件只是包含联合索引最左边的列时,仍然会走联合索引。通过以上的这三组测试中,我们发现只要联合索引最左边的字段
profession存在,索引就会生效,只不过索引的长度不同。
而且由以上三组测试,我们也可以推测出profession字段索引长度为47、age字段索引长度为2、status字段索引长度为5。

我们再继续往下看:
1)
explain select * from tb_user where age = 31 and status = '0';

2)
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 = '软件工程'; 时,是否满足最左前缀法则,走不走上述的联合索引,索引长度?

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

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

<>范围查询

联合索引中,出现范围查询(>,<),范围查询右侧的列索引失效。
explain select * from tb_user where profession = '软件工程' and age > 30 and status
= '0';

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

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

<>索引失效情况

<>1)不要在索引列上进行运算操作, 索引将失效。

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

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

当根据phone字段进行函数运算操作之后,索引失效。
explain select * from tb_user where substring(phone,10,2) = '15';

<>2)字符串不加引号

字符串类型字段使用时,不加引号,索引将失效。
接下来通过两组对比我们来看看到底是啥情况:
explain select * from tb_user where profession = '软件工程' and age = 31 and status
= '0'; explain select * from tb_user where profession = '软件工程' and age = 31 and
status= 0;

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

<>3)模糊查询

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

接下来,我们来看一下这三条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'%工%';

经过上述的测试,我们发现,在like模糊查询中,在关键字后面加%,索引可以生效。而如果在关键字前面加了%,索引将会失效。

<>4)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也要建立索引。

<>5)数据分布影响

如果MySQL评估使用索引比全表更慢,则不使用索引。
select * from tb_user where phone >= '17799990005'; select * from tb_user
where phone>= '17799990015';

经过测试我们发现,相同的SQL语句,只是传入的字段值不同,最终的执行计划也完全不一样,这是为什么呢?
就是因为MySQL在查询时,会评估使用索引的效率与走全表扫描的效率,如果走全表扫描更快,则放弃 索引,走全表扫描。
因为索引是用来索引少量数据的,如果通过索引查询返回大批量的数据,则还不 如走全表扫描来的快,此时索引就会失效。

技术
下载桌面版
GitHub
百度网盘(提取码:draw)
Gitee
云服务器优惠
阿里云优惠券
腾讯云优惠券
华为云优惠券
站点信息
问题反馈
邮箱:[email protected]
QQ群:766591547
关注微信