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

最新推荐文章于 2024-10-09 15:36:01 发布

低调的JVM

最新推荐文章于 2024-10-09 15:36:01 发布

阅读量4.7w

点赞数 19

分类专栏： mysql 文章标签： mysql sql

本文链接：https://blog.csdn.net/qq_27529917/article/details/78447882

版权

mysql 专栏收录该内容

6 篇文章 5 订阅

订阅专栏

写的有些错误，最新的可以查看Mysql多表连接查询的执行细节（一），这篇就不要看了。

在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,2，3，4，5，6) 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,2，3，4，5，6) 
	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,2，3，4，5，6) 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字句的则执行排序，最后得到查询的结果。