SQL Server 2005技术内幕：查询、调整和优化3——连接

连接

SQL Server支持三种物理连接运算符：嵌套循环连接、合并连接及哈希连接。在bookmark lookup示例中，已经接触了嵌套循环连接。没有最好的连接运算符，而且没有连接运算符好或不好。每一个连接运算符在正确的环境都会执行的很好，在错误的环境都会执行的不好。

 

嵌套循环连接

         嵌套循环连接是最简单和最基础的连接方式。它对一个表（外表）的每行和另外一个表（称为内行）的每行比较，寻找满足谓词连接的行。这里的“内表”和“外表”指的是连接的输入。“内连接”和“外连接”指的是逻辑上连接操作符的语义。可以用伪代码表示嵌套循环连接的算法。

 

For each row R1 in the outer table

         For each row R2 in the inter table

                   If R1 joins with R2

                            Return (R1, R2)

 

所有的行都会进行比较，导致这个算法的消耗与外表、内表乘机的大小成比例。因为消耗随着内表大小的增加而变得更大，在实践中优化器试着通过减少对于每个外表必须处理的内表行数来减少消耗。

例如，考虑下面的查询：

 

select  O. [ OrderId ]  

from   [ Customers ]  C  join   [ Orders ]  O  on  C. [ CustomerId ]   =  O. [ CustomerId ]

where  C. [ City ]   =   ' London '

 

 

当执行此查询时，使用下面的查询计划：

 

 

 

在这个计划中外表是客户，内表是订单。因此，对于嵌套循环连接符，SQL Server从客户表开始寻找。连接每一次提取一个客户，对于每个客户，在Order表中执行一次索引。因为这里有6个客户，它在Order表中执行6次索引查询。注意在Order表中的索引查询依赖与客户表中的客户ID。6次中的每一次，SQL Server重复在Order表中查询索引，客户ID有不同的值。因此，6次索引查询执行的每次结果不同，也返回不同的行。

         把客户ID称作关联参数。如果嵌套循环连接包含有关联参数，则在查询计划中以OUTER REFERENES表示。经常把嵌套循环连接这种有依赖与关联参数的索引查询称作索引连接。索引连接是嵌套循环连接中最常见的类型。

         上一个例子说明了SQL Server对嵌套循环连接提高性能的两个重要技术：关联参数和一个在连接内部中基于关联参数的索引查询。另一个没有提高的性能优化的方法是在连接内部使用存储池。一个存储池能够从连接内部缓存和重新访问结果。存储池在有很多重复值的关联参数和在连接内部估计会有很大消耗时有用。通过使用存储池，SQL Server可以避免对相同关联参数的连接内部重新计算多次。

         不是所有嵌套循环连接都有关联参数。得到一个没有关联参数的简单方法是交叉连接，交叉连接把一个表中所有的行和另一外表中所有的行结合起来。为了用嵌套循环连接实现一个交叉连接，必须搜索和连接内表和外表的每行。内表行的集合不会依赖外表处理的行而改变。因此，对于交叉连接，没有关联参数。

         如果没有合适的索引或适合索引查询的谓词连接，优化器可以使用一个没有连接参数的查询计划。对于决定一个谓词连接是否适合使用一个索引查询的规则，是和决定另外谓词是否适合索引查询一样的。例如：通过以下查询可以查到返回雇佣的员工数：

 

select  E1. [ EmployeeId ] ,  COUNT ( * )

from   [ Employees ]  E1  join   [ Employees ]  E2

    on  E1. [ HireDate ]   <  E2. [ HireDate ]

group   by  E1. [ EmployeeId ]

 

 

在雇佣日期列中没有索引，因此，这个查询产生一个有谓词，但是没有关联参数和索引查询的简单嵌套循环连接：

 

合并连接

合并连接不支持任何连接谓词的嵌套循环连接，合并连接至少需要一个等值连接谓词。而且，合并连接的输入必须存储在连接器上。例如，如果有一个连接谓词[Customers].[CustomerId] = [Orders].[CustomerId] ,客户ID都必须存储在客户的ID列中。

合并连接也是在读的同时对两个存储输入的一行进行比较。在每个步骤中，比较每个输入的下一行。如果两行是相同，输出一个连接后的行并继续。如果行是不同的，舍弃两个输入行中较少的那个并继续。因为输入是存储，连接舍弃的任何行必须比两个输入中任何剩下的行要小，因此可以永不连接。合并连接不需要对两个输入中的每一行扫描。只要到了两个输入中的某一个的末尾，合并连接就会停止扫描。

         嵌套循环连接总的消耗和在输入表中行的乘积成比例，不同于嵌套循环连接，合并连接的表最多读一次，总的消耗和输入行数的总数成正比例，因此何必连接对于大量的输入是较好的选择。

 

排序合并连接和索引合并连接

         对于一个合并连接，SQL Server有两种方法得到排序的输入；直接使用排序操作符对输入排序，或者可能从一个索引中读行。一般来说，一个通过使用索引获得排序次序的计划比直接使用排序的消耗要少。

 

连接谓词和逻辑连接类型

         合并连接支持多等值连接谓词，只要在所有连接键上输入是排序的。只要二者的输入有着一样的排序，特定排序次序就是不匹配。例如，如果有一个连接谓词T1.[Col1] = T2.[Col1] and T1.[Col2] = T2.[Col2]，只要表T1和表T2在（Col1，Col2）上都一样排序，就可以使用合并连接。

 

哈希连接

       哈希连接是第二个物理连接操作符。当提到物理连接操作符时，哈希连接是一个重要的部分。嵌套循环连接对于小的数据集很有用，哈希连接对于大型数据集很有用。哈希连接在并行性和比例行方面优于其他连接，并且对于数据仓库的查询请求反映很快。跟合并连接一样，哈希连接至少需要一个等值连接谓词，支持剩余谓词。不同于合并连接，哈希连接不需要排序的输入集。

         哈希连接在执行时分成两个阶段，即构建阶段和探索阶段。在构建阶段，哈希连接从一个输入（通常称之为左输入或构建输入）中读入所有的行，对等值连接键上的列哈希，然后创建或构建一个内存哈希表。在探索阶段，哈希连接从第二个输入（通常称之为右输入）中读入所有的行，在相同的等值连接键上对行哈希，然后在哈希表中查找或探索匹配的行。由于哈希函数可能导致冲突，哈希连接必须对潜在的匹配进行检查来确定已连接。

         注意，不同于输入行立即开始的嵌套循环连接和合并连接，哈希连接在构建输入时被阻塞。也就是说，哈希连接必须读入和处理所有的构建输入，之后才能返回行。不同于其他连接方法，哈希连接需要一个内存区域存储哈希表。因此，SQL Server在任何时候，对于并发的哈希连接数据有限制。这些特性和约束通常对于数据仓库不是什么问题，但是他们可能为大多数联机事务处理带来不必要的麻烦。

 

 

 

 

转载于:https://www.cnblogs.com/fengxiang/archive/2010/08/29/1811922.html