MySQL SQL 多个Join on(表连接) 和Where间的执行顺序(nest loop join机制)

在mysql中,多表连接采用nest loop join,即循环嵌套连接的方式,其他还有归并排序连接,哈希连接;

mysql sql优化器会对sql的表的连接顺序做一定的优化,并不见得一定是我们写的sql的表连接顺序,会尽量使用查询结果集最小的表作为驱动表,前提是连接顺序改变不会改变查询结果,然后按照优化后的顺序和其他的表逐渐连接查询。 也就是说left join连接并不一定是从左边关联到右边,也有可能是从右边关联到左边,left join仅仅是保证左侧表符合条件的记录会进入到结果集中,具体顺序使用explain执行查看。

先分析内连接或者是多表关联查询的情况:,假设sql是这样的:

select a.id,b.name,c.adress 
from A a 
    inner join B b on a.bid = b.id 
    inner join C c on a.cid = c.id 
    inner join D d on a.did = d.id
where a.id in (1,23456) and b.class = '1001' and c.id > 15 and d.age < 30;
select a.id,b.name,c.adress 
from A a ,B b ,C c ,D d
where a.bid = b.id 
    and c on a.cid = c.id 
    and d on a.did = d.id
    and a.id in (1,23456) 
    and c.id > 15 
    and d.age < 30;

如何确定A,B,C,D之间的连接顺序?如何确定驱动表?MySQL会优先选用结果集最小的那张表作为驱动表来和其他的表连接。如何得到结果集最小的表? MySQL会估算每一个表的结果集大小,如何估算? 会使用WHERE中的针对各个表的查询条件来估算每个表的结果集大小,此时索引就发挥了作用。

表A的条件是a.id in (1,2,3,4,5,6),假设id是唯一键,则A的结果集是6;

表B的条件是b.class = ‘1001’,若class上有索引,则使用此索引估算,假设B的总记录数是100,class索引的分布率是10%,即class有10个不同的值,则估算为100*10% = 10,即B的结果集为10;

表C的条件是 c.id > 15,假设C的总记录数是20,id是唯一键,则C的结果集最多为5;估算D的结果集方法类似,D的结果集为12;

若WHERE中不含指定表的查询条件或者查询的条件不包含索引,则此表的结果集默认为记录数最大值,但并不代表此表的结果集大于其他的表,因为有些表本身就没几条数据。

如此就确定了表的结果集大小,C , A , B , D。当然结果集大小是决定表连接顺序的一个关键因素,SQL优化器在做物理查询优化时不仅仅会考虑结果集,还会考虑查询所需的数据是否在缓存中,如果不在从磁盘读取的代价,读取到数据后将二进制数据解析成数据行的代价,以及表与表连接可能的结果集大小等等因素,综合各方面来确定表的连接顺序。

假设sql执行器按照C , A , B , D的顺序来连接,先根据where内C表的条件查询C表,得到一个结果集。然后关联查询A表,在关联时以ON 内a.cid = c.id 为关联条件。若关联的C表的字段不是唯一的话,可能生成一对多的关联,即一行A表记录生成多条结果集的记录。以on条件关联后,在执行where内关于C表的查询条件c.id > 15;那么c.id > 15这个查询条件可以放在on之后,也可以放在where之后,on之后是在生成临时表之前过滤C表的记录,而WHERE之后是对临时表的记录做过滤,理论上放在on之后效率会高一点,但where的条件若有索引则会使用索引,所以效率也不算低。而且若on之后跟的条件不是针对C表的话,可能会对结果集产生影响。AC结果集的临时表再去关联B表,一样先on关联,然后where过滤,依次关联和过滤直到表关联结束。

当然以上的sql都是a.bid = b.id and a.cid = c.id,关联条件均在A表上,有些时候时间接关联,比如a.bid = b.id and b.cid = c.id,此时表的关联顺序可能是A > B > C,或者 C > B >A,或者先B > C,然后关联A等等。SQL优化器会确保无论如何调整关联顺序不会对结果集有影响。

以上均是内连接时的情况,外连接要稍微复杂点。

假设SQL如下:

select a.id,b.name,c.adress 
from A a 
    left join B b on a.bid = b.id 
    left join C c on b.cid = c.id 
    right join D d on d.age = c.dage
where a.id in (1,23456) and b.class = '1001' and c.id > 15 and d.age < 30;

对于外连接,有一个主表的概念,即left的左边表或者right的右边表,对于主表,会返回主表所有符合条件的记录行,而对于副表,则只返回能和主表关联的行,一条主表记录根据on条件和副表内每一行匹配,有多少行匹配上了就生成多少临时表的记录,也就是说主表的一行记录可能生成临时表的多条记录,若副表中没有匹配行时,则副表生成一条所有字段均为null的记录和主表的记录行匹配, 确保主表的符合条件的行能进入临时表。

对于外连接,大部分情况会以我们的SQL顺序来执行,因为有主表的结果集限制,上述SQL一般会先根据WHERE条件从A表重 查询出符合条件的记录行,作为主表和B表以ON条件关联,A结果集中的每条记录均和B表中符合条件的每条记录行生成AB临时表的一条记录,若B表有多个符合记录行,则生成多个临时表行,若B表没有符合条件的行,则生成一条所有字段均为null的行与A的记录行连接,若没有ON条件,则以“笛卡尔积”的形式连接,即A结果集的每一行和B表的每一行均连接生成临时表的记录。

当ON执行完之后,同样用WHERE条件过滤临时结果集中不符合条件的记录行,和内连接的机制相同,之后再次关联其他表。最后right join D,此时D表时主动表,D表关联A,B,C查询后的临时表,最终会返回D表中所有符合条件的记录行。

以上就是nest loop join机制,嵌套循环连接。一层一层的连接,循环用外层结果集的记录行和内层的所有符合条件ON条件的记录依次连接,内层没有符合条件的生成所有字段为null的记录行,当不存在ON条件是以“笛卡尔积”的形式连接。连接过后where过滤,再连接,在过滤,直到左右表均连接完毕。连接完毕后有group by字句则执行分组,有having字句的则对分组后的结果集再过滤,所以having执行在where之后,因此有些条件放where字句内能缩小分组前的结果集,提高执行效率。之后还有order by字句的则执行排序,最后得到查询的结果。

阅读更多
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/qq_27529917/article/details/78447882
文章标签: mysql sql
个人分类: mysql
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭