SQL Server三种表连接原理

 

　　在SQL Server数据库中，查询优化器在处理表连接时，通常会使用一下三种连接方式：

• 嵌套循环连接（Nested Loop Join）
• 合并连接 （Merge Join）
• Hash连接 （Hash Join）

　　充分理解这三种表连接工作原理，可以使我们在优化SQL Server连接方面的代码有据可依，为开展优化工作提供一定的思路。接下来我们来认识下这三种连接。

1. 嵌套循环连接（Nested Loop Join）

　　该连接方式通常在小数据量并且语句比较简单的场景中使用，也是比较常见的连接方式，比如以下示例：

use AdventureWorks2008
go
SELECT H.*
FROM Sales.SalesOrderHeader H
JOIN Sales.Sale
   1:  use AdventureWorks2008
sOrderDetail D
ON H.SalesOrderID=D.SalesOrderID
WHERE H.SalesOrderID = 43659

　　AdventureWorks2008数据库是SQL Server的一个sample，你可以在微软官方网站上自由下载。http://msftdbprodsamples.codeplex.com/releases/view/37109

　　我们在数据库中运行这段代码：

　　通过执行计划我们可以看到，数据库的优化器使用了嵌套连接（Neasted Loops）,上面第一行中的Sales.SalesOrderHeader表因为只有一行数据所以做为外部表使用，SalesOrderDetail有12行数据做为内部表使用。

　　嵌套循环的工作原理如图所示：

图1 嵌套循环工作原理图

　　其原理就是根据条件从表中过滤出一个外部链接表，循环的从外部表中读取一行数据，去内部表中进行匹配，伪码如下：

For (i=0;i< Number of outerTable Row;i++)

{

    OuterTable[i] connect InnerTable[1,2.....N] To Create New Row

    WHERE OuterTable[i].data.value = OuterTable[1,2.....N].data.Value

}

　　了解嵌套的工作原理后，我们不难发现，这种连接的方式具有一定的局限性的：

1. 因为算法是循环进行的，所以比较适合数据量较小的表进行连接，尤其是外部表的数据。

2. 两张表最好是排序的。表中的条件列和连接列最好有索引，尤其是内部表必须有索引，这样工作效率会成倍增加。

当外部表较小，而内部表较大并且连接字段上有索引的情况下，循环嵌套非常高效。并且嵌套循环是三种方式中唯一支持不等式连接的方式。

2. 合并连接 （Merge Join）

　　在SQL Server数据库中，如果查询优化器，发现要连接的两张对象表，在连接列上都已经排序并包含索引，那么优化器将会极大可能选择“合并”连接策略。条件是：两个表都是排序的，并且表连接条件中至少有一个等号连接，查询分析器会去选择合并连接。

　　代码示例：

USE AdventureWorks2008

GO

SELECT P.*

FROM Production.ProductModel P

JOIN Production.ProductModelProductDescriptionCulture PPMD

ON P.ProductModelID = PPMD.ProductModelID

　　根据执行计划我们可以看到，这次的连接操作使用的合并连接：

　　这两张表中，数据量分别为128和762行数据，连接列是表中的主键并且数据是有序的，因此数据库的查询优化器自动选择了合并连接。合并连接的工作原理如下图所示：

图2 合并连接的工作原理

　　数据库优化器在决定使用合并连接后，并行的在两个表（术语叫输入集合）中各取第一行数据，进行匹配，匹配则返回匹配行并进行连接。如果不匹配，那么小的那一个表（输入集合），则顺序取下一行数据继续尝试匹配。

　　通过其工作原理我们可以发现，合并连接可以看成是一个类似于并发工作机制。操作分别在两个表（输入集合）依次获取数据并进行比较，这就要求两张表是有序的，有序的排列会极大的提高工作的效率。

　　有关表排序的问题，如果连接语句中使用Sort关键字来排序数据表，那么SQL Server的优化器会比较倾向于Hash Join。在合并连接中，并不排斥order by, group by, distinct等关键字，在使用这些语句时，查询优化器也有极大的可能选择合并连接。

　　当我们使用一些查询限定条件，比如不等式（>,<,>=等）限定条件范围，那么合并连接的效率会有更好。

　　合并连接的限定条件：

1. 两张表的连接列需要排序

2. 连接列必须有索引

3. 哈希连接（Hash Join）

　　当我们尝试将两张数据量较大，没有排序和索引的两张表进行连接时，SQL Server的查询优化器会尝试使用Hash Join。

代码示例：

SELECT *

FROM Production.Product P

Join Production.ProductSubcategory SPC

on P.ProductSubcategoryID = SPC.ProductSubcategoryID

　　根据执行计划我们可以看到，这次的连接操作使用的哈希连接：

　　该连接在处理大量无序的数据时，效率较高，但是对处理器和内存资源的消耗较大。实现过程如下：

　　Hash Join连接的执行操作分为两个阶段，建立和探测。

　　建立是指对输入表进行的一系列的操作。首先优化器会将输入表中的每一行数据扫描到系统内存中，然后根据内置的散列算法计算出相应散列值，相同散列值的数据会被分到一个Hash池中。这些散列值和数据地址保存在一个Hash表中，提供给探测使用。通常优化器会选择数据较少的表作为建立输入表。

　　建立完成后，开始探查工作。另一个连接表（我们叫探查输入）同样会被逐行的扫描、计算，得出一个Hash值。连接操作会使用探查输入的Hash值和建立输入的Hash值列表进行扫描和匹配工作，最终建立连接。

　　上图是Hash连接的工作流程，接下来我们可以来了解下哈希算法的实现的机制，以下的内容是个人对算法的理解，若有偏颇请指正。

　　Hash的实际含义是“散列”的意思，它主要的功能就是将一组数据，通过算法，变换成固定长度的输出，这个输出我们就称之为散列值（Hash值），通常在安全领域，如密码学中使用较多。

　　在SQL Server里面哈希散列函数是黑盒的，没有具体的算法可以参考。实际上很多开发人员在解决海量数据查询的时候，都会采用Hash方式，并且开发适合需求的散列算法。常用的一些算法包括一些取余、MD2、MD4、MD5 和 SHA-1等等。

　　因为算法，不同的数据可能会生成相同的散列值。它将大量的数据按照规则分散到不同数据堆或者链表中，建立内部的映射关系。我们可以认为他是将数组和链表结合在一起，想要达到一种寻址容易、插入删除方便的数据结构，而Hash表就是一种数据内容和数据存放地址之间的映射关系。

　　散列函数的选择会决定影响Hash表元数量大小和每个键值包含的数据多少，这个是数学上的问题这里不进行进一步讨论。

　　说到这里，可能大家还是不太理解，我们这里举例来说明：

　　比如说有两张表：

表A{A,F,C,D,B,E……}

表B{F,B,E,D,A,F…….}

　　并且表A的数据量小于表B，这两张表进行Hash连接的过程如下：

1. 首先数据库会将表A中的所有数据，扫描存入内存中。

2. 内存中的表A的数据，经过散列函数依次得到对应的散列值（Hash值）。

3. 表A中相同散列值（键值）的数据，会统一的放入到一个Hash池中。个人认为Hash池中的数据，就是数组和链表的集合。Hash的键值可以看到是一个数组的下标，而池中的数据以链表的形式连接在数组中。

Hash【键值】-->数据1-->数据2..............

如图中的一组数据，数据A和数据C具有相同的Hash值，值为001，那么他们都被分配到以001命名的Hash池中。

4. 将Hash值和对应的数据，依次存入到一个Hash表中，建立结束。

5. 探测阶段，数据库依次读取扫描表B中的每一行数据，并通过散列函数计算出一个Hash值。

6. 根据Hash值，去Hash表中和表A的键值进行匹配，找到对应的Hash池。

7. 接下来将表B的数据去和对应的Hash池中的每条数据，去对比和匹配。如果匹配成功则进行数据连接。

　　通过对原理的了解，我们可以看到这种连接方式，需要大量的计算操作，对CPU带来一定的压力。通常Hash 连接操作在内存中进行，如果内存不足，数据库会将数据写入到硬盘中，影响性能。

    4.小结

三种连接方式的特点：

类型	连接列上索引	表的大小	排序	连接子句
嵌套	内部表：必须 外部表：有最好	小	可选	所有类型
合并	内部表：必须 聚簇索引或者覆盖索引 外部表：必须 聚簇索引或者覆盖索引	大	需要	Equi-join
HASH	内部表：不需要 外部表：可选，最好有 小的外部表，大得内部表	任意	不需要	Equi-join

三种方式对资源的压力：

	嵌套循环连接	合并连接	哈希连接
CPU	低	低（如果没有显式排序）	高
内存	低	低（如果没有显式排序）	高
IO	可能高可能低	低	可能高可能低

　　以上是个人对三种连接的个人理解，不当之处请指正。

题外话：

　　其实我们可以把这三种连接比喻成相亲。

　　嵌套连接就是熟人介绍，亲戚朋友根据你的条件，搜索下周围的资源，然后安排你和几个姑娘见面，看看能不能匹配上。如果你的条件很明确（外部表索引），并且朋友对姑娘比较熟悉，对方的要求也很明确（内部表索引），那么成功率就会比较高。

　　合并连接就是社区或者网站组织的小型相亲联谊会，比如电影《恋爱33天中》那种8分钟面对面的形式。男女双方面对面进行交谈（匹配判断），每几分钟就换一个人再次交谈，由于大家条件和目的性明确（都有索引），所以整个流程效率会比较高。

    Hash连接则就像是万人相亲大会，比如上海的中山公园（条件好的已婚人士慎入）。单身青年的父母，入园后由于各种原因随机的分成各个小群组（经过散列函数分成Hash池）。然后参与者根据自己的判断（确认Hash键值），找到合适小组后（Hash键值相等），依次交谈交换条件和信息（尝试匹配），看看里面有没有合适人选，有就进一步了解（匹配成功，连接）。

2013年11月14日 Ralf Wang