SQL之join连接方式

CBO（cost based optimizor）会根据join table的统计信息，选择Join算法、多表Join还有顺序问题。

join算法的三大连接方式：
nested-loop join(嵌套循环连接)
merge join(合并连接)
hash join(哈希连接)

一：nested-loop join
嵌套循环连接，从外层循环表（默认为驱动表）的每一行开始循环遍历内层循环表（内表）直到匹配上外层循环。由此可看出，嵌套循环连接查找内部循环表的次数等于外部循环表的行数，还可以看出这种连接要求内部循环表有序（也就是有索引），并且外部循环表的行数要小于内部循环表的行数。

适用于：驱动表较小，内表条件列有序
复杂度：O(M*N)

二：merge join
合并连接，需要两个有序队列进行连接，此时性能优于hash join。
但是合并连接只能做以“值相等”为条件的联接，而且如果数据集可能有重复的数据，Merge Join要采用Many-To-Many这种很费资源的联接方式。
例如，在SQL Server扫描数据集时，如果数据集1有两个或者多个记录值相等，SQL Server必须得把数据集2里扫描过的数据暂时建立一个数据结构存放起来，万一数据集1里下一个记录还是这个值，那还有用。这个临时数据结构被称为“Worktable”，会被放在tempdb或者内存里，这样做很耗资源。

适用于：两表有序
复杂度：O(M+N)

三：hash join
哈希连接，对于大量数据，并且无序的情况下性能均优于nested-loop join和merge join。对于连接列没有排序的情况下（也就是没有索引），查询分析器更倾向于hash join。
哈希匹配分为两个阶段：生成阶段和探测阶段。
生成阶段，将输入源中的每一个条目经过散列函数的计算都放到不同的Hash Bucket（哈希桶）中，其中Hash Function的选择和Hash Bucket的数量都是黑盒，另外Hash Bucket之内的条目是无序的。
探测阶段，对于另一个输入集合，同样针对每一行进行散列函数，确定其所应在的Hash Bucket,在针对这行和对应Hash Bucket中的每一行进行匹配，如果匹配则返回对应的行。
总之，由于哈希匹配涉及到散列函数，所以对CPU的消耗会非常高，此外，在Hash Bucket中的行是无序的，所以输出结果也是无序的。

适用于：大表，无序

–参考：https://blog.csdn.net/isoleo/article/details/40661955