join中级篇---------hash join & merge join & nested loop Join

嵌套循环连接(Nested Loop Join)

循环嵌套连接是最基本的连接,正如其名所示那样，需要进行循环嵌套，嵌套循环是三种方式中唯一支持不等式连接的方式，这种连接方式的过程可以简单的用下图展示:

Dn144699.445175445CEAC8D4EDDFA908C1FC8B6A(zh-cn,MSDN.10).png

图1.循环嵌套连接的第一步

Dn144699.575AFB26620304AB254113AC028B57E1(zh-cn,MSDN.10).png

图2.循环嵌套连接的第二步

由上面两个图不难看出，循环嵌套连接查找内部循环表的次数等于外部循环的行数，当外部循环没有更多的行时，循环嵌套结束。另外，还可以看出，这种连接方式需要内部循环的表有序（也就是有索引），并且外部循环表的行数要小于内部循环的行数，否则查询分析器就更倾向于Hash Join(会在本文后面讲到)。

通过嵌套循环连接也可以看出，随着数据量的增长这种方式对性能的消耗将呈现出指数级别的增长，所以数据量到一定程度时，查询分析器往往就会采用这种方式。

合并连接(Merge Join)

Merge Join其实上就是将两个有序队列进行连接，需要两端都已经有序，所以不必像Loop Join那样不断的查找循环内部的表。其次，Merge Join需要表连接条件中至少有一个等号查询分析器才会去选择Merge Join。

Merge Join的过程我们可以简单用下面图进行描述:

Dn144699.2DEB0AF993E7A453430109CEC260A8ED(zh-cn,MSDN.10).png

图8.Merge Join第一步

Merge Join首先从两个输入集合中各取第一行，如果匹配，则返回匹配行。加入两行不匹配，则有较小值的输入集合+1，如图9所示。

Dn144699.CDD5667F45C6CA9271F38411AA9C0EA8(zh-cn,MSDN.10).png

.更小值的输入集合向下进1

因此，通常来说Merge Join如果输入两端有序，则Merge Join效率会非常高，但是如果需要使用显式Sort来保证有序实现Merge Join的话，那么Hash Join将会是效率更高的选择。但是也有一种例外，那就是查询中存在order by,group by,distinct等可能导致查询分析器不得不进行显式排序，那么对于查询分析器来说，反正都已经进行显式Sort了,何不一石二鸟的直接利用 Sort后的结果进行成本更小的MERGE JOIN？在这种情况下，Merge Join将会是更好的选择。

另外，我们可以由Merge Join的原理看出，当连接条件为不等式(但不包括!=)，比如说> < >=等方式时，Merge Join有着更好的效率。

哈希匹配(Hash Join)

哈希匹配连接相对前面两种方式更加复杂一些，但是哈希匹配对于大量数据，并且无序的情况下性能均好于Merge Join和Loop Join。对于连接列没有排序的情况下(也就是没有索引)，查询分析器会倾向于使用Hash Join。

哈希匹配分为两个阶段,分别为生成和探测阶段，首先是生成阶段，第一阶段生成阶段具体的过程可以如图12所示。

Dn144699.CB4D97D8CFF4F9C6A2E51E11FBA103A2(zh-cn,MSDN.10).png

图12.哈希匹配的第一阶段

图 12中，将输入源中的每一个条目经过散列函数的计算都放到不同的Hash Bucket中，其中Hash Function的选择和Hash Bucket的数量都是黑盒，微软并没有公布具体的算法，但我相信已经是非常好的算法了。另外在Hash Bucket之内的条目是无序的。通常来讲，查询优化器都会使用连接两端中比较小的哪个输入集来作为第一阶段的输入源。

接下来是探测阶段，对于另一个输入集合，同样针对每一行进行散列函数，确定其所应在的Hash Bucket,在针对这行和对应Hash Bucket中的每一行进行匹配，如果匹配则返回对应的行。

通过了解哈希匹配的原理不难看出，哈希匹配涉及到散列函数，所以对CPU的消耗会非常高，此外，在Hash Bucket中的行是无序的，所以输出结果也是无序的。

上面的情况都是内存可以容纳下生成阶段所需的内存，如果内存吃紧，则还会涉及到Grace哈希匹配和递归哈希匹配，这就可能会用到TempDB从而吃掉大量的IO。这里就不细说了,有兴趣的同学可以移步:http://msdn.microsoft.com/zh-cn/library/aa178403(v=SQL.80).aspx。

总结

下面我们通过一个表格简单总结这几种连接方式的消耗和使用场景:

	嵌套循环连接	合并连接	哈希连接
适用场景	外层循环小，内存循环条件列有序	输入两端都有序	数据量大，且没有索引
CPU	低	低（如果没有显式排序）	高
内存	低	低（如果没有显式排序）	高
IO	可能高可能低	低	可能高可能低

参考文献:

http://msdn.microsoft.com/zh-cn/library/aa178403(v=SQL.80).aspx

http://www.dbsophic.com/SQL-Server-Articles/physical-join-operators-merge-operator.html

以下博客做了一些三种join的耗时实验，可以参考分析：

http://www.jasongj.com/2015/03/07/Join1/