[SQL Server 2005] hash联接算法

亦凉 2022-08-21 09:39 211阅读 0赞

[SQL Server 2005 hash联接算法][SQL Server 2005 hash]

如果两个联接输入都很大，而且这两个输入的大小差不多，则预先排序的合并联接提供的性能与哈希联接相近。但是，如果这两个输入的大小相差很大，则哈希联接操作通常快得多。

哈希联接可以有效处理未排序的大型非索引输入。它们对复杂查询的中间结果很有用，因为：

·    中间结果未经索引（除非已经显式保存到磁盘上然后创建索引），而且通常不为查询计划中的下一个操作进行适当的排序。

·     查询优化器只估计中间结果的大小。由于对于复杂查询，估计可能有很大的误差，因此如果中间结果比预期的大得多，则处理中间结果的算法不仅必须有效而且必须适度弱化。

###   原理 ###

Hash join一般用于一张小表和一张大表进行join时。Hash join的过程大致如下（下面所说的内存就指sort area，关于过程，后  
面会作详细讨论）：  
    1．  一张小表被hash在内存中。因为数据量小，所以这张小表的大多数数据已经驻入在内存中，剩下的少量数据被放置在临时表空间中；  
    2．  每读取大表的一条记录，就和小表中内存中的数据进行比较，如果符合，则立即输出数据（也就是说没有读取临时表空间中的小表的数据）。而如果大表的数据与小表中临时表空间的数据相符合，则不直接输出，而是也被存储临时表空间中。  
    3．  当大表的所有数据都读取完毕，将临时表空间中的数据以其输出。  
   
   如果小表的数据量足够小（小于hash area size），那所有数据就都在内存中了，可以避免对临时表空间的读写。  
   如果是并行环境下，前面中的第2步就变成如下了：  
      2．每读取一条大表的记录，和内存中小表的数据比较，如果符合先做join，而不直接输出，直到整张大表数据读取完毕。如果内存足够，Join好的数据就保存在内存中。否则，就保存在临时表空间中。

1,处理大量、未排序、无索引的数据  
      2 ,Hash Join一个较大限制是它只能应用于等值联结(equality join)，这主要是由于哈希函数及其桶的确定性及无序性所导致的。

**如何使用Hash联结。**

今天又看了一下阿里巴巴的dba，在Oracle里的研究hash联结的优化和例子：

[http://www.alidba.net/index.php/archives/83][http_www.alidba.net_index.php_archives_83]

他得出的结论:  
    1. hash的时候一定要用小记录集做驱动.  
    2. 大/小记录集作驱动时, 读取数据文件的cost两者是一样的.但是前者的记录集在大到一定程度的时候, 在构建hash桶会产生很多物理读, 而且这些物理读根本无法消除, 每次执行都会产生.

**我们测试一下，看看不同的驱动表对hash联结的性能影响：**

执行一下语句：

select \* from workflowinfo1 a  
inner hash join workflowlog1 b on a.workflowid=b.workflowid  
  
select \* from workflowlog1 a  
inner hash join workflowinfo1 b on a.workflowid=b.workflowid

![o_hash1.jpg][]

这时发现以小的驱动表（workflowinfo1,50多万）成本较少。而多的驱动表（workflowlog1,70多万）成本较高。

**这里我们得出结论：**

1，hash联结适合输入和输出都是大型数据集的情况。

2，联结列必须相等联结，（不相等可以如上方法换成相等联结）

3，使用较少的表为驱动表。（在使用hash提示联结尤其注意）

**本人经过测试, 补充结论如下**: 如果不指定inner hash join, SQL Server2005的查询优化器会自动优化成hash联接. 即以下2种写法,和上面第一种写法的执行计划完全相同.

select \* from workflowinfo1 a, workflowlog1 b on a.workflowid=b.workflowid;

select \* from workflowlog1 a, workflowinfo1 b on a.workflowid=b.workflowid;

[SQL Server 2005 hash]: http://www.cnblogs.com/happyday56/archive/2009/09/11/1564491.html
[http_www.alidba.net_index.php_archives_83]: http://www.alidba.net/index.php/archives/83
[o_hash1.jpg]: /images/20220724/6f850d2f6ee4455fad3472319b46f9ae.png