深入SQLServer连接（JOIN)系列–part5连接小结

PART 1
PART 2
PART 3
PART 4


下表总结了前面所提到三个物理连接操作符：


 

例子

剩余的内容中，我们会通过例子来看一个查询是如何微妙的选择不同的连接类型的。

让我们先建立两个有1000行的表：

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

 

create table T2 (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)

 

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

 

    set @i = @i + 1

 

  end

 

因为我们没有索引，这两个表的简单连接会生成一个hash连接：

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

  |--Hash Match(Inner Join, HASH:([T1].[a])=([T2].[a]), RESIDUAL:([T2].[a]=[T1].[a]))

       |--Table Scan(OBJECT:([T1]))

       |--Table Scan(OBJECT:([T2]))

 

但是，hash连接有一个不好的地方是会快速的返回查询的前几行。假设我们只想要前10行：

select top 10 * from T1 join T2 on T1.a = T2.a

  |--Top(TOP EXPRESSION:((10)))

       |--Nested Loops(Inner Join, WHERE:([T2].[a]=[T1].[a]))

            |--Table Scan(OBJECT:([T1]))

            |--Table Spool

                 |--Table Scan(OBJECT:([T2]))

 

现在我们得到了一个嵌套循环连接（nested loop join)。我们没有索引，所以该计划会为T1的每一行来扫描T2的所有行，直到我们找到了想要的10行。如果找到这10行需要很多对T2的扫描，这个执行计划的成本就很高。幸运的是，如果你执行该查询，并打开statistics profile，我们可以看到只需要对T2扫描28次。

注意：

可能正如你所料，top运算符返回的前N行后，立即终止它的输入子树。在本例中，N的值为10.
Table spool是一个优化  。在这个例子中，它其实没起多大作用。但是，假设我们用”select T2.a“ 取代”select *”。T2包含了3列，其中还有一个char(200)的列，但我们只需要其中的一个整数列T2.a。因此，我们可以将T2.a进行table spool(假脱机）.T2可能只会在每一个page里面存储大概40行，因为每行的大小可能在200bytes左右。扫描T2的所有1000行，可能需要扫描25个page。而spool可以将1000行的列T2.a保存在1到2个page里面。
回到例子中，正如前面提到的，如果我们请求了太多的行，嵌套循环连接成本会变得很高。这时，一个merge join就是更好的选择了。

select top 100 * from T1 join T2 on T1.a = T2.a

  |--Top(TOP EXPRESSION:((100)))

       |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([T2].[a])=([T1].[a]), RESIDUAL:([T2].[a]=[T1].[a]))

            |--Sort(ORDER BY:([T2].[a] ASC))

            |    |--Table Scan(OBJECT:([T2]))

            |--Sort(ORDER BY:([T1].[a] ASC))

                 |--Table Scan(OBJECT:([T1]))

 

相似的，如果我们无论如何需要排序，一个merge join也是很好的选择，因为我们已经需要对至少一个表进行排序了。

select top 10 * from T1 join T2 on T1.a = T2.a

 

order by T1.a

 

该查询的执行计划基本与上面top 100的查询语句一样。

现在，让我们添加一个聚集索引：

create unique clustered index T1a on T1(a)

 

回到最初的连接，我们现在得到了一个多对一的合并连接：

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

  |--Merge Join(Inner Join, MERGE:([T1].[a])=([T2].[a]), RESIDUAL:([T2].[a]=[T1].[a]))

       |--Clustered Index Scan(OBJECT:([T1].[T1a]), ORDERED FORWARD)

       |--Sort(ORDER BY:([T2].[a] ASC))

            |--Table Scan(OBJECT:([T2]))

 

优化器发现我们已经有一个表进行排序了，可能会决定也对另外一个表也进行排序，然后使用merge join 而不是hash join：

但如果我们在T1的表扫描上添加了一个谓词，我们可以得到hash join：

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

 

where T1.b < 100

  |--Hash Match(Inner Join, HASH:([T1].[a])=([T2].[a]), RESIDUAL:([T2].[a]=[T1].[a]))

       |--Clustered Index Scan(OBJECT:([T1].[T1a]), WHERE:([T1].<(100)))

       |--Table Scan(OBJECT:([T2]))

 

merge join需要对T1的所有1000行进行排序，而hash join只需要为T1表上满足T1.b < 100的20左右的行构建一个hash表。因此，hash join的执行计划需要更少的内存，也不太可能会溢出。

但如果我们也在查询上添加一个排序，我们又得到了merge join：

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

 

where T1.b < 100

 

order by T1.a

  |--Merge Join(Inner Join, MERGE:([T1].[a])=([T2].[a]), RESIDUAL:([T2].[a]=[T1].[a]))

       |--Clustered Index Scan(OBJECT:([T1].[T1a]),  WHERE:([T1].[b]<(100)) ORDERED FORWARD)

       |--Sort(ORDER BY:([T2].[a] ASC))

            |--Table Scan(OBJECT:([T2]))

 

回顾下，merge join会保留顺序，所以我们不再需要再进行一个最终排序，来满足查询语句中的排序子语句。hash join不会保留顺序，所以我们可能需要一个额外的排序。如果使用hash join，我们可能会需要两个消耗内存的运算符。（注意到一个消耗内存的运算符不一定就比两个消耗内存的运算符来得好，关键是要看运算符所需要的内存量）。

接着，我们在T2的表扫描上添加一个谓词：

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

 where T2.b < 100

 

  |--Nested Loops(Inner Join, OUTER REFERENCES:([T2].[a]))

       |--Table Scan(OBJECT:([T2]), WHERE:([T2].[b]<(100)))

       |--Clustered Index Seek(OBJECT:([T1].[T1a]), SEEK:([T1].[a]=[T2].[a]) ORDERED FORWARD)

 

通过减少T2的基数，一个基于索引的嵌套循环连接限制是最好的选择了。

最后，让我们添加一个有10万行的大表T3：

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

 

declare @i int

 

set @i = 0

 

while @i < 100000

 

  begin

 

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

 

    set @i = @i + 1

 

  end

 

考虑一个简单的T1和T3的连接：

select * from T1 join T3 on T1.a = T3.a

  |--Hash Match(Inner Join, HASH:([T1].[a])=([T3].[a]), RESIDUAL:([T3].[a]=[T1].[a]))

       |--Clustered Index Scan(OBJECT:([T1].[T1a]))

       |--Table Scan(OBJECT:([T3]))

 

即使我们在T1上有一个索引，我们仍然选择了hash join。回顾下，T1和T2这两个大小相近的表是做merge join。要做一个merge join，我们需要在T1和T3上都进行排序。hash join需要足够的内存在T1（1000行）上构建一个hash表，而merge join 可能需要足够的内存来排序T3（10万行）。可见，hash join需要了更少的内存。

 

[b]Hints

虽然我们建议尽量少用hint，除非特别需要的情况下。如果你想要通过实验来查看不同的连接顺序和连接类型是如何影响执行计划和性能的，你可以使用hint来强制SQL Server来生成不同的执行计划。这些hint都在BOL里面有记录。需要注意的是不可能强迫所有的执行计划，而且使用错误的hint，可能导致SQL Server没法找到一个合法的执行计划，并产生如下错误：

Msg 8622, Level 16, State 1, Line 1

Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

 

你可以使用查询hint来告诉SQL Server使用一个特殊的连接类型或者强迫连接的顺序。查询hint一般是出现在OPTION子句后且在每个语句的最后面。前面的例子中，我们已经使用了一些hint了：LOOP JOIN, MERGE JOIN , HASH JOIN , FORCE ORDER。前3个hint告诉优化器只能使用特定的连接类型。如果你指定了两个连接hint，优化器将会只使用其中的一个连接类型，这其实也是提供了一种不使用某个特定连接类型的方式（不在 OPTION 后面指定）。而FORCE ORDER告诉优化器根据FROM子句后面的顺序来连接两个表。

 

你也可以在FROM子句后面使用连接hint，来强制连接的类型和顺序。这些hint的功能是非常强大的。你可以为每个连接指定确定的连接类型。通过使用括号，你可以强制任何连接顺序。假设有下面这样一个例子（纯属假设），下面是使用hint来强制一棵bushy treee使用所有的3中连接类型。

select *

 

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

 

    inner hash join

 

     (T3 inner loop join T4 on T3.a = T4.a)

 

    on T1.b = T3.b

 

接下来

后面，我们可能还会介绍关于连接的一些高级话题。

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

5 个解决方案