WEB开发网
开发学院数据库MSSQL Server SQL Server 2005合并联接算法 阅读

SQL Server 2005合并联接算法

 2008-08-26 09:56:04 来源:WEB开发网   
核心提示:简介: 如果两个联接输入并不小但已在二者联接列上排序(例如,如果它们是通过扫描已排序的索引获得的),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.*

重启数据库服务,查看成本:

SQL Server 2005合并联接算法

执行结果: 

(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 *, 看看成本

SQL Server 2005合并联接算法

这里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连接是最优的操作。

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

Tags:SQL Server 合并

编辑录入:爽爽 [复制链接] [打 印]
赞助商链接