Sqlserver的Join算法

SQLserver在执行Join语句时有三种join方法，分别为nested loops join、merge join和hash join。

（DB2为4种：nested loops join、merge-scan join、hash join、Partition Join）

SQL Server会根据两个结果集所基于的表格结构，以及结果集的大小，选择最合适的联接方法。

1、Nested Loop Join

Nested Loops是一种最基本的联接方法，被SQL Server广泛使用。对于两张要被join在一起的表格，SQL Server选择一张做Outer table(在执行计划的上端，SalesOrderHeader_test)，另外一张做Inner table(在执行计划的下端，SalesOrderDetail_test)。

foreach(row r1 in outer table) --尽量小
    foreach(row r2 in inner table)
        if( r1, r2 符合匹配条件 )
            output(r1, r2);

1.算法复杂度

A表数据集大小xB表数据集大小,外表只扫描一遍，内表扫描N遍

2. 外表最好为有序

Outer table的数据集最好能够事先排序好，以便提高检索效率。

3. 内表的搜索字段最好有索引，加快扫描速度

2、Merge Join

如果需要连接的两张表在联接列上排序（例如，如果它们是通过扫描已排序的索引获得的），则Merge Join是最快的联接操作。如果两个联接输入都很大，而且这两个输入的大小差不多，则预先排序的Merge Join提供的性能与Hash Join相近。但是，如果这两个输入的大小相差很大，则Hash Join操作通常快得多。

get first row R1 from input 1 
get first row R2 from input 2 
while not at the end of either input 
begin 
    if (R1 joins with R2) 
    begin 
    output (R1, R2) 
    get next row R2 from input 2 
end 
else if (R1 < R2)
    get next row R1 from input 1 
else 
    get next row R2 from input 2 
end 

从两边的数据集里各取一个值，比较一下。如果相等，就把这两行联接起来返回。如果不相等，那就把小的那个值丢掉，按顺序取下一个更大的。两边的数据集有一边遍历结束，整个Join的过程就结束。所以整个算法的复杂度是O(M+N)，这个比起Nested Loops Join两个数据集相乘的复杂度O(M*N)，的确是小了很多。所以在数据集大的情况下，Merge Join的优势是非常明显的。

merge join的局限性

1.  做联接的两个数据集必须要事先按照Join的字段排好序。

2.  Merge Join只能做以“值相等”为条件的联接，而且如果数据集可能有重复的数据，Merge Join要采用Many-To-Many这种很费资源的联接方式。

merge join的使用

1. 适用于搜索值唯一的数据

2. merge join返回的结果集为有序结果集

Hash join

Hash Join就是利用哈希算法作匹配的联接算法。简单来说，哈希算法分成两步，“构建哈希桶(Buildhash bucket)”和“探测哈希桶中的值(Probe hash bucket)”。在“Build”阶段，SQL Server选择两个要做Join的数据集中的一个，根据记录的值建立起一张在内存中的Hash表。然后在“Probe”阶段，SQL Server选择另外一个数据集，将里面的记录值依次带入，返回符合条件可以做联接的行。

for each row R1 in the build table
   begin
      calculate hash value on join key(s) of R1
      insert R1 into the appropriate hash bucket
   end
for each row R2 in the probe table
   begin
      calculate hash value on join key(s) of R2
      for each row R1 in the corresponding hash bucket
         if R1 joins with R2
            output (R1, R2)
   end

算法描述：

1. 选择需要两个join的表中的一个a，对a中的每一个记录R1，计算其连接列的hash值，然后根据hash值将R1插入到hash bucket当中

2. 选择两外一张表b，对于b中的每一条记录R2，我们也计算其连接列的hash值，然后去hash bucket上查找。如果hash bucket上有R1能够跟R2进行连接，那么就输出（R1，R2）的联接结果，可能有多个R1的记录

算法优点：

1. 它的算法复杂度就是分别遍历两边的数据集各一遍。

2. 不需要数据集事先按照什么顺序排序，也不要求上面有索引

3. 它不需要数据集事先按照什么顺序排序，也不要求上面有索引

4. 可以比较容易地升级成使用多处理器的并行执行计划。

Hash Join是一种适合于要Join的数据集比较大，上面没有合适的索引的情况

结论：仅了解，SQLserver选择的方案一般是最优的，不要轻易尝试自己指定使用哪种join的算法