MS SQL 查询联接运算系列----嵌套循环联接

以下内容节选自Inside SQL Server 2005--Query Tuning and Opti

SQL Server支持三种物理联接运算：嵌套循环联接，合并联接，哈希联接．在先前的例子中我们已经看到了嵌套循环联接，在以下部分，我们将详细地介绍每一种联接运算的工作原理，另外也对每一个运算支持的逻辑联接类型作一解释，最后讨论每一种联接类型的性能．

嵌套循环联接(Nested Loops Join)

嵌套循环联接是最简单的，最基本的联接算法．它将从一个表（外表）与另一个表（内表）的每一行进行比较，查找满足联接谓词的记录行．

         下面我们使用伪代码来描述该算法：

                    for each row R1 in the outer table

   for each row R2 in the inner table

      if R1 joins with R2

         return (R1, R2)

         嵌套循环联接的查询开销＝outer表＊inner表，由于其查询开销随着input表的增加而增加，实际上，查询优化器试图通过减少要处理的inner行来降低开销．

         考虑以下的查询：

                     SELECT O.[OrderId]

                     FROM [Customers] C JOIN [Orders] O ON C.[CustomerId] = O.[CustomerId]

WHERE C.[City] = N'London'

         当执行该查询时，我们得到以下查询计划：

                                         Rows Executes

46  1       |--Nested Loops(Inner Join, OUTER REFERENCES:([C].[CustomerID]))

6   1              |--Index Seek(OBJECT:([Customers].[City] AS [C]),

                      SEEK:([C].[City]=N'London') ORDERED FORWARD)

46  6              |--Index Seek(OBJECT:([Orders].[CustomerID] AS [O]),

                      SEEK:([O].[CustomerID]=[C].[CustomerID]) ORDERED FORWARD)

         与其他计划不同，该计划使用了SET STATISTICS PROFILE ON开关，这样可以看到每一个运算执行的行数．在本计划中，outer表是Customers，inner表是Orders，因而，依据嵌套循环联接算法，SQL Server首先从Customers表开始查找，每次读取一个Customer，然后在Orders表上执行索引查找．由于Customers表有６行，在Orders表上执行的索引查找次数为６次．注意到Orders表上的索引查找取决于CustomerID（源自Customers表）．SQL Server需要在Orders表上重复执行６次索引查找．而CustomerID具有不同的值，这样，执行６次索引查找将返回不同的记录行．

         我们把CustomerID称为相关参数，若嵌套循环联接具有相关参数，可以在其计划中的OUTER REFERENCES找到．通常我们参考具有索引查找的嵌套循环联接类型，该联接类型取决于索引联接的相关参数．索引联接可能是嵌套循环联接中最常见的类型．其实，我们在SQL Server 2005中所见到的书签查询就是这样一种非聚集索引与基表间的索引联接．

         上面提到的示例均是SQL Server提高嵌套循环联接性能的两个重要技术：相关参数和基于联接的innter内相关参数的索引查找．而另外一个性能优化是在联接的inner内使用了lazy spool，Lazy spool将联接的inner内的结果进行缓存，以便可以重用．在和具有许多重复值的相关参数上Lazy spool尤其很有用．通过使用了lazy spool，SQL Server无需对联接的inner内的表进行重新计算．

         并非所有的嵌套循环联接都有关联参数，不使用相关参数的嵌套循环联接的简单方法是使用cross联接．Cross联接将一个表的所有行与另一个表的所有行进行匹配，要实现cross联接，需要将inner表的每一行与outer表的每一行进行扫描和联接．Inner表的行集并不会发生变化，这主要取决于我们处理的outer表的行数．

         在一些情况下，可能没有合适的索引或满足索引查找的联接谓词，查询优化器可能使用无相关参数生成一个查询计划．判断满足索引查找的联接谓词与判断满足索引查找的其他谓词的规则是相同的，例如，在以下的查询中：

SELECT E1.[EmployeeId], COUNT(*)

FROM [Employees] E1 JOIN [Employees] E2

  ON E1.[HireDate] < E2.[HireDate]

GROUP BY E1.[EmployeeId]

         在HireDate列上并没有索引存在，这样该查询将产生一个简单的嵌套循环联接（无任何相关的参数和索引查找）

                |--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1007],0)))

     |--Stream Aggregate(GROUP BY:([E1].[EmployeeID]) DEFINE:([Expr1007]=Count(*)))

          |--Nested Loops(Inner Join, WHERE:([E1].[HireDate]<[E2].[HireDate]))

             |--Clustered Index Scan(OBJECT:([Employees].[PK_Employees] AS [E1]))

             |--Clustered Index Scan(OBJECT:([Employees].[PK_Employees] AS [E2]))

         下面我来使用CROSS APPLY来重写上面的查询：

SELECT E1.[EmployeeId], ECnt.Cnt

FROM [Employees] E1 CROSS APPLY

(

   SELECT COUNT(*) Cnt

   FROM [Employees] E2

   WHERE E1.[HireDate] < E2.[HireDate]

) ECnt

虽然这两个查询结果是相同的，对于使用CROSS APPLY的查询计划使用相关能数的嵌套循环联接．

|--Nested Loops(Inner Join, OUTER REFERENCES:([E1].[HireDate]))

     |--Clustered Index Scan(OBJECT:([Employees].[PK_Employees] AS [E1]))

     |--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int,[Expr1007],0)))

          |--Stream Aggregate(DEFINE:([Expr1007]=Count(*)))

               |--Clustered Index Scan (OBJECT:([Employees].[PK_Employees] AS [E2]),

                    WHERE:([E1].[HireDate]<[E2].[HireDate]))