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

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

会发现,若不符合最左前缀原则,则 type为 index,若符合,则 type 为 ref。

index 代表的是会对整个索引树进行扫描,如例子中的,最右列 address,就会导致扫描整个索引树。

ref 代表 mysql 会根据特定的算法查找索引,这样的效率比 index 全扫描要高一些。但是,它对索引结构有一定的要求,索引字段必须是有序的。而联合索引就符合这样的要求!

联合索引内部就是有序的,我们可以把它理解为类似于 order by name,age,address 这样的排序规则。会先根据 name 排序,若name 相同,再根据 age 排序,依次类推。

所以,这也解释了,为什么我们要遵守最左前缀原则。当最左列有序时,才可以保证右边的索引列有序。

退而求其次,若不符合最左前缀原则,但是符合覆盖索引,就可以扫描整个索引树,从而找到覆盖索引对应的列(避免了回表)。

若不符合最左前缀原则,且也不符合覆盖索引(形同 select *),则需要扫描整个索引树。完成之后,还需要再回表,查询对应的行记录。

此时,查询优化器,就会认为,这样的两次查询索引树,还不如全表扫描来的快(因为联合索引此时不符合最左前缀原则,要比普通单列索引查询慢的多)。因此,此时就会走全表扫描。

有童鞋就要问了,你在这废话了一大堆,还是没有解答最初的疑惑啊 !!!

不然,其实上边的分析就已经解答了。我们仔细观察最开始的 user 表,和此时的 student 表有什么不同。

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

user 表中,和 student 表相比,少了 sex 字段。但是,它们所建立的联合索引却是一样的 KEY(name,age,address)。

所以,在 user 中,我们最初的 sql 语句就等同于 ,

-- 最初的sql EXPLAIN select * from user where address='beijing'; -- 等同于 EXPLAIN select id,name,age,address from user where address='beijing';

这个结构就是我们上边讨论的情况:不符合最左前缀原则,但是符合索引覆盖。这种情况,是会走索引的。

结论

那么,结论也就出来了。并不是最左前缀原则失效了,也不是 Mysql 变的更智能了,而是此时创建的表结构,以及查询的 sql 语句恰好符合了索引覆盖而已。真的是虚惊一场 !!

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

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