软件开发培训班 >> 编程开发 >> SQL


SQL Server 2005合并联接最佳使用情况

　　本文标签：SQL Server 2005合并联接

　　以下的文章主要描述的是SQL Server 2005合并联接的正确算法，在实际操作中如果遇到两个联接输入而且不小但已在二者，其联接列上排序(例如，如果它们是通过扫描已排序的索引获得的)，则合并联接是最快的联接操作  。

　　如果两个联接输入都很大，而且这两个输入的大小差不多，则预先排序的SQL Server 2005合并联接提供的性能与哈希

　　

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

　　

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

　　最佳使用:

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

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

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

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

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

　　测试语句：

　　合并算法

　　

  1. select a.* from workflowbase1 a inner merge join dbo.workflowinfo1 b   
  2. on a.id=b.workflowid and a.creater=4028814110830a1e01108fe379e60061 

　　

　　

　　hash算法

　　

  1. select a.* from workflowbase1 a inner hash join dbo.workflowinfo1 b   
  2. on a.id=b.workflowid and a.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 树索引中获得预排序的所需数据，则SQL Server 2005合并联接通常是最快的可用联接算法

　　如果我们换一下，将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&,的确验证了上面的一句话：

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

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

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