SQL Server 2005合并联接算法

核心提示：简介： 如果两个联接输入并不小但已在二者联接列上排序(例如，如果它们是通过扫描已排序的索引获得的)，SQL Server 2005合并联接算法，则合并联接是最快的联接操作，如果两个联接输入都很大，如果没有排序hash连接是最优的操作，注意：这里的排序指两个输入集合必须按相等列进行分别排序，而且这两个输入的大小差不多，则

简介：

如果两个联接输入并不小但已在二者联接列上排序(例如，如果它们是通过扫描已排序的索引获得的)，则合并联接是最快的联接操作。如果两个联接输入都很大，而且这两个输入的大小差不多，则预先排序的合并联接提供的性能与哈希联接相近。

从上次我们分析来看，嵌套循环适合输入和输出都小的情况，那如果输入和输入都比较大情况下，使用合并算法什么情况下最优。

最佳使用:

合并联接本身的速度很快，但如果需要排序操作，选择合并联接就会非常费时。然而，如果数据量很大且能够从现有 B 树索引中获得预排序的所需数据，则合并联接通常是最快的可用联接算法。

我们来测试一下，合并连接的最优情况：

测试环境：表：workflowinfo1 约45万条 表workflowbase1 约4.5万条

条件：workflowbase1中列id，creater都建立索引，workflowinfo1中workflowid建立了索引。

如果两个联接输入并不小但已在二者联接列上排序(例如，如果它们是通过扫描已排序的索引获得的)，则合并联接是最快的联接操作。如果两个联接输入都很大，而且这两个输入的大小差不多，则预先排序的合并联接提供的性能与哈希联接相近。~：(creater=4028814110830a1e01108fe379e60061’的workflowbase1表有1023条数据)

测试语句：

合并算法　　

selecta.*fromworkflowbase1ainnermergejoindbo.workflowinfo1b

ona.id=b.workflowidanda.creater='4028814110830a1e01108fe379e60061'

hash算法

selecta.*fromworkflowbase1ainnerhashjoindbo.workflowinfo1b

ona.id=b.workflowidanda.creater='4028814110830a1e01108fe379e60061'

注意:这两条SQL和上一个嵌套循环的例子有区别，一个 select * 和一个是 select a.*

重启数据库服务，查看成本：

执行结果:　

(10468行受影响)

表'workflowinfo1'。扫描计数1，逻辑读取3527次，物理读取1次，预读3528次，lob逻辑读取0次，lob物理读取0次，lob预读0次。

表'workflowbase1'。扫描计数1，逻辑读取1571次，物理读取0次，预读1624次，lob逻辑读取0次，lob物理读取0次，lob预读0次。

(10468行受影响)

表'workflowbase1'。扫描计数3，逻辑读取1571次，物理读取0次，预读0次，lob逻辑读取0次，lob物理读取0次，lob预读0次。

表'workflowinfo1'。扫描计数3，逻辑读取3886次，物理读取0次，预读0次，lob逻辑读取0次，lob物理读取0次，lob预读0次。

表'Worktable'。扫描计数0，逻辑读取0次，物理读取0次，预读0次，lob逻辑读取0次，lob物理读取0次，lob预读0次。

这,时，Merge算法比Hash算法少了357次IO。这时发现，成本对比，合并连接要优于hash连接,排序使用了B-tree索引的排序，大表workflowinfo1就没有排序操作。

这里验证了上面的一句话:

如果数据量很大且能够从现有 B 树索引中获得预排序的所需数据，则合并联接通常是最快的可用联接算法

如果我们换一下，将select a.*换成select *， 看看成本

这里hash连接是最优的算法

执行结果：

(10468行受影响)

表'workflowbase1'。扫描计数3，逻辑读取1571次，物理读取0次，预读0次，lob逻辑读取0次，lob物理读取0次，lob预读0次。

表'workflowinfo1'。扫描计数3，逻辑读取9604次，物理读取0次，预读0次，lob逻辑读取0次，lob物理读取0次，lob预读0次。

(10468行受影响)y

表'Worktable'。扫描计数0，逻辑读取0次，物理读取0次，预读0次，lob逻辑读取0次，lob物理读取0次，lob预读0次。

表'workflowinfo1'。扫描计数1，逻辑读取9604次，物理读取0次，预读0次，lob逻辑读取0次，lob物理读取0次，lob预读0次。

表'workflowbase1'。扫描计数1，逻辑读取1571次，物理读取0次，预读0次，lob逻辑读取0次，lob物理读取0次，lob预读0次。

这里的hash和merge的io次数一样，但merge连接里多了一个排序操作，占到整个成本的60&,的确验证了上面的一句话：

合并联接本身的速度很快，但如果需要排序操作，选择合并联接就会非常费时。

两个联接输入并不小但已在二者联接列上排序，则合并联接是最快的联接操作。如果没有排序hash连接是最优的操作。

注意：这里的排序指两个输入集合必须按相等列进行分别排序。而不是按其他列排序。