【分享】深入SQL Server连接（JOIN)系列–part4（hash join 哈希连接）

在物理连接操作符中，hash连接执行的是那些繁重的任务。嵌套循环连接适合于相对较小的数据集，合并连接适用于中等规模的数据集，而hash连接适用于大型的数据集。hash连接在并行和扩展方面比其他的连接都要好，同时也非常适用于有超大吞吐量的数据仓库。

hash连接与合并连接有很多的共同点，与合并连接一样，hash连接也要求至少一个equijoin谓词（相等谓词），也支持剩余谓词(residual predicate)，也支持所有的外连接和半连接（semi-join)。但与合并连接不同，hash连接不要求输入事先排好序，同时，在支持完全外连接的时候（full outer join），不要求一个相等谓词（quijoin predicate）

算法
hash连接分两步执行：构建和裁剪（build and probe）。在构建阶段，它会从第一个输入里面读取所有的行（通常也将第一个输入叫做左输入或构建输入），然后计算equijoin 键的hash值，然后创建一个在内存中的hash表。在裁剪阶段，它会从第二个输入读取所有的行（通常也将第二个输入叫做右输入或裁剪输入），在相同的equijoin键上计算hash只，然后根据hash表进行查找或者裁剪。因为hash函数会导致冲突（两个不同的键值在经过hash计算后会得出相同的hash值）。我们一般都必须坚持每个潜在的匹配来确保确实进行了连接。

伪代码如下：

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

注意到，不同于嵌套循环连接及合并连接会立刻开始返回输出行，hash连接会在它的构建输入时，阻止输出。也就是说，在返回任何输出之前它必须读取和处理它的整个构建输入。更进一步讲，不同于其他连接方法，hash连接要求一块内存来存放hash表。也就是说，对某个指定的时间点，能同时执行hash连接的数目就需要有一个限制。当然，数据仓库上这个问题可能不是很严重。

内存和溢出

在hash连接开始执行之前，SQL Server会尝试估算它需要多少内存来构建它的hash表。我们使用基数来评估构建输入的大小以及预期的平均行大小来估算内存的要求。为了尽量减少hash连接对内存的要求，我们会尝试选择两个表中较小的一个来作为构建输入。然后，我们会尝试保存这么多的内存，确保hash连接可以成功执行。

如果因为我们给了hash连接较少的内存，也就是比它所请求或者所估算的来得少，会发生什么呢？在这些情况下，hash连接的构建阶段就可能会出现运行内存不足。如果hash连接耗尽了内存，它会开始将总的hash表中的一小部分溢出（spill)到磁盘中（也就是tempdb中的worktable里）。hash连接会跟踪hash表中的哪些部分仍然在内存中，哪些部分已经溢出到磁盘中。当我们从构建表（build table）中读取每一新行时，我们会检查一下是否hash到了内存中或者磁盘上。如果是hash到内存中，则进行正常的hash处理。如果是hash到磁盘上的，我们会将该行写入磁盘。这一耗尽内存和溢出到磁盘的过程可能重复多次，直到构建阶段已经完成为止。

我们在裁剪阶段会进行一个类似的过程。对裁剪表的每个新行，我们需要检查以查看是否hash到了内存中或者磁盘上。如果是hash到内存部分，我们会对hash表进行裁剪，生成任何合适的连接的行，并丢弃该行。如果hash到了磁盘部分，我们则将该行写入磁盘。一旦我们完成了裁剪表的对一次遍历，我们会逐个返回已经溢出的部分，将构建表中的行读回内存，为每一部分重建hash表，接着读取对应的裁剪部分来完成连接。

Left deep vs. right deep vs. bushy hash join trees

(备注：deep 和 bushy的区别：If all internal nodes of such a tree have at least one leaf as a child,then the tree is called deep. Otherwise, it is called bushy，也就是说如果一棵树如果至少有一个叶子节点作为子节点，那么该树就叫做deep，否则叫做bushy）。

查询计划中对应的每类树的形状如下：


连接树的形状对hash连接而言是特别有趣的，因为它会影响到内存消耗。

在一棵（left deep tree）里，一个hash连接的输出是下一个hash连接的构建输入。因为hash连接在进入裁剪阶段会消耗整个构建输入，而在一棵left deep树里，只有相邻的那对hash连接会被激活。例如，在上图中，我们从为HJ1构建一个hash表开始。当HJ1开始裁剪时，我们是用HJ1的输出来为HJ2构建hash表。当HJ1完成裁剪时，我们可以释放HJ1的hash表所占的内存。接着，我们才能开始HJ2的裁剪阶段和为HJ3构建hash表。也就是说HJ1和HJ3不可能在同一时间被激活，并同时消耗内存。我们所需要的总的内存值为 max(HJ1+HJ2, HJ2+HJ3）

在一棵right deep tree里，一个hash连接的输出是下一个hash连接的裁剪输入。所有hahs连接必须完成所有的hash表的构建，才能继续进行裁剪。所有的hash连接都立刻被激活了，从而不能共享内存。当我们开始裁剪时，hash连接的整棵树种的所有行都可以不被阻塞的获取。也就是说，我们所需要的总体内存数量是sum(HJ1,HJ2,HJ3)。

例子
下面是例子中所用表的脚本：

create table T1 (a int, b int, x char(200))

create table T2 (a int, b int, x char(200))

create table T3 (a int, b int, x char(200))

set nocount on

declare @i int

set @i = 0

while @i < 1000

  begin

    insert T1 values (@i * 2, @i * 5, @i)

    set @i = @i + 1

  end

declare @i int

set @i = 0

while @i < 10000

  begin

    insert T2 values (@i * 3, @i * 7, @i)

    set @i = @i + 1

  end

declare @i int

set @i = 0

while @i < 100000

  begin

    insert T3 values (@i * 5, @i * 11, @i)

    set @i = @i + 1

  end

下面是一个简单例子：

select *

from T1 join T2 on T1.a = T2.a

select *

from T1 join T2 on T1.a = T2.a

注意到，T2的行数是T1行数的10倍，而优化器确实选择了T1作为构建表，而T2作为裁剪表。

现在，考虑如下三个表连接：

select *

from (T1 join T2 on T1.a = T2.a)

    join T3 on T1.b = T3.a

Rows	Executes	 
334	1	  |–Hash Match(Inner Join, HASH:([T1].)=([T3].[a]), RESIDUAL:([T1].[b]=[T3].[a]))
334	1	       |–Hash Match(Inner Join, HASH:([T1].[a])=([T2].[a]), RESIDUAL:([T1].[a]=[T2].[a]))
1000	1	       |    |–Table Scan(OBJECT:([T1]))
10000	1	       |    |–Table Scan(OBJECT:([T2]))
100000	1	       |–Table Scan(OBJECT:([T3]))

注意到优化器已经选择一个left deep的执行计划，首先，我们会连接两个较小的表，T1和T2。该连接只产生334行的结果集，我们可以方便使用该结果集来构建一个hash表，以便与大表T3进行连接。

现在观察如果我们添加一个谓词来限制两个小表的大小时可能会发生的事。（一个单独where语句就已经足够了，优化器可以从“T1.a < 100” 和 “T1.a = T2.a“中继承”T2.a < 100″

select *

from (T1 join T2 on T1.a = T2.a)

    join T3 on T1.b = T3.a

where T1.a < 100

Rows	Executes	 
17	1	  |–Hash Match(Inner Join, HASH:([T2].[a])=([T1].[a]), RESIDUAL:([T1].[a]=[T2].[a]))
34	1	       |–Table Scan(OBJECT:([T2]), WHERE:([T2].[a]<(100)))
50	1	       |–Hash Match(Inner Join, HASH:([T1].[b])=([T3].[a]), RESIDUAL:([T1].[b]=[T3].[a]))
50	1	            |–Table Scan(OBJECT:([T1]), WHERE:([T1].[a]<(100)))
100000	1	            |–Table Scan(OBJECT:([T3]))

这一次，优化器选择了一个right deep 的执行计划。T1和T2现在都很小了（只有34行和50行），因此，在这些表上建立一个hash表，并使用大表T3进行裁剪会比在中间结果上建立一个hash表的方式来得好。

[b]下一步
现在，我们已经了解了这三个物理连接操作符的工作方式。在后面的文章中，我们会对这些操作符的特定做一个小结，并给出更多的例子来展示SQL Server是如果在决定怎么连接表时做出各类权衡的。

除非注明，本站文章均为原创或编译，转载请注明： 文章来自sqlpub.net

50 个解决方案

#1

顶

#2

该回复于2013-03-22 09:40:12被管理员删除

#3

HASH 连接的SQL语句一个也执行不了啊

#4

感谢分享..

#5

谢谢分享！！

#6

竟然没发现有这贴？

#7

技术内幕上面的？含泪收藏了

#8

又涨知识了，感谢分享！
怀着感激之心收下了。

#9

该回复于2013-08-09 13:12:27被管理员删除

#10

看看各种连接方法

#11

好东西，都来学习！

#12

顶，我想赚积分

#13

感觉子查询效率更高，平时不太倾向使用连接。但连接写起来确实语法清晰易懂

#14

收藏了再看

#15

收了..........再说。

#16

谢谢分享！！ 

#17

该回复于2013-08-11 09:42:50被管理员删除

#18

看了遍，学习下

#19

学习下 ！

#20

#21

经典教程，谢谢分享！

#22

谢谢分享！！ 
谢谢分享！！ 

#23

好东西，谢谢分享！

#24

学习中   对这方面需要了解的会很多。

#25

http://blogs.msdn.com/b/craigfr/archive/2006/08/10/687630.aspx

这个应该属于原创吧！

#26

开户行哦开机即可看看

#27

引用 25 楼 匆匆过客 的回复:

http://blogs.msdn.com/b/craigfr/archive/2006/08/10/687630.aspx

这个应该属于原创吧！

我也发现了，楼主的文章没有一篇是原创的，翻译就翻译，还“编译”，编什么译，唬人呢。

#28

该回复于2013-08-12 16:19:50被管理员删除

#29

该回复于2013-08-12 21:14:23被管理员删除

#30

该回复于2013-08-13 08:50:17被管理员删除

#31

谢谢分享！！ 

#32

楼主分析得比较透彻。

#33

谢谢楼主分享。

#34

该回复于2013-08-14 15:00:34被管理员删除

#35

该回复于2013-08-14 16:05:52被管理员删除

#36

该回复于2013-08-14 17:02:37被管理员删除

#37

竟然没发现有这贴？
竟然没发现有这贴？

#38

该回复于2014-05-31 11:50:45被管理员删除

#39

该回复于2013-08-15 15:26:48被管理员删除

#40

谢谢楼主分享

#41

该回复于2013-08-16 16:40:06被管理员删除

#42

不错，顶了。。。。。。。。。。

#43

该回复于2013-08-17 17:02:19被管理员删除

#44

谢谢分享！！！

#45

该回复于2013-08-18 14:33:30被管理员删除

#46

学习了，谢谢。。

#47

该回复于2013-08-18 15:17:57被管理员删除

#48

该回复于2013-08-19 08:58:56被管理员删除

#49

该回复于2013-08-19 16:48:25被管理员删除

#50

又涨知识了，感谢分享！
怀着感激之心收下了。