我去,为什么最左前缀原则失效了? (2)

还是以上边的表为例,现在 zs 对应的索引树上边,只有它本身和主键的数据,并不能覆盖到 age 字段。那么,我们就可以创建联合索引,如 KEY(name,age)。并且,查询的时候,显式的写出联合索引对应的字段(name和age)。

创建联合索引如下,

KEY `idx_stu` (`name`,`age`)

查询语句修改如下,

-- 覆盖联合索引中的字段 select id,name,age from student where and age=12;

这样,当查询索引树的时候,就不用回表,可以一次性查出所有的字段。对应的索引树结构如下:

联合索引

PS:图中,联合索引中的字段(name,age)都应该出现在索引树上的,这里为了画图方便,且因数据量太小,没有画出来。只表现出了:叶子节点存储了所有的联合索引字段。

最左前缀原则

最左前缀原则,顾名思义,就是最左边的优先。指的是联合索引中,优先走最左边列的索引。如上表中,name和age的联合索引,相当于创建了 name 单列索引和 (name,age)联合索引。在查询时,where 条件中若有 name 字段,则会走这个联合索引。

对于多个字段的联合索引,也同理。如 index(a,b,c) 联合索引,则相当于创建了 a 单列索引,(a,b)联合索引,和(a,b,c)联合索引。

为了验证最左前缀原则,我们需要对原来的表结构进行改造。再添加两个字段(address,sex),然后创建三列的联合索引(name,age,address)。

drop table student; CREATE TABLE `student` ( `id` int(11) NOT NULL, `name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL, `age` int(11) DEFAULT NULL, `address` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL, `sex` int(1) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_stu` (`name`,`age`,`address`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin; insert into student(id,name,age,address,sex) values(1,'zs',12,'beijing',1); insert into student(id,name,age,address,sex) values(5,'ls',14,'tianjin',0); insert into student(id,name,age,address,sex) values(9,'ww',12,'shanghai',1); insert into student(id,name,age,address,sex) values(11,'sq',13,'hebei',1);

查看表数据如下,

表数据

分别用三种方式,使之符合最左前缀原则。

explain select * from student where; explain select * from student where and age=12; explain select * from student where and age=12 and address='beijing';

然后查看它们的执行计划如下,

我去,为什么最左前缀原则失效了?

可以看到,最终都走了索引。现在,修改 sql 如下,如何?

explain select * from student where address='beijing';

我去,为什么最左前缀原则失效了?

如我们所料,这不符合最左前缀原则,因此索引失效,走了全表扫描。

PS:拓展思考,若 sql 改为如下,会导致全表扫描吗?(自己动手尝试哦)

explain select * from student where and address='beijing'; 问题解惑

到现在为止,我们发现最左前缀原则一切正常。然后回到最开始抛出的问题,为什么这个原则就不生效了呢?(创建的联合索引,还有 sql 语句都是一样的啊!)

别着急,还记得前面我们说的索引覆盖吗? 这次,我们利用索引覆盖原理,只查询特定的字段(只有主键和联合索引字段)。

explain select id,name,age,address from student where address='beijing';

再查看执行计划,

我去,为什么最左前缀原则失效了?

问题来了,此时违反了最左前缀原则,但是符合覆盖索引,为什么就走索引了呢?

我们对比一下,若用最左列,和不用最左列,它们的执行计划有何不同。

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/wsxgpp.html