SQL Server 2005合并联接算法
2008-08-26 09:56:04 来源:WEB开发网简介:
如果两个联接输入并不小但已在二者联接列上排序(例如,如果它们是通过扫描已排序的索引获得的),则合并联接是最快的联接操作。如果两个联接输入都很大,而且这两个输入的大小差不多,则预先排序的合并联接提供的性能与哈希联接相近。
从上次我们分析来看,嵌套循环适合输入和输出都小的情况,那如果输入和输入都比较大情况下,使用合并算法什么情况下最优。
最佳使用:
合并联接本身的速度很快,但如果需要排序操作,选择合并联接就会非常费时。然而,如果数据量很大且能够从现有 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连接是最优的操作。
注意:这里的排序指两个输入集合必须按相等列进行分别排序。而不是按其他列排序。
- ››SQL Server 2008 R2 下如何清理数据库日志文件
- ››sqlite 存取中文的解决方法
- ››SQL2005、2008、2000 清空删除日志
- ››SQL Server 2005和SQL Server 2000数据的相互导入...
- ››sql server 2008 在安装了活动目录以后无法启动服...
- ››sqlserver 每30分自动生成一次
- ››sqlite 数据库 对 BOOL型 数据的插入处理正确用法...
- ››sql server自动生成批量执行SQL脚本的批处理
- ››sql server 2008亿万数据性能优化
- ››SQL Server 2008清空数据库日志方法
- ››sqlserver安装和简单的使用
- ››SQL Sever 2008 R2 数据库管理
更多精彩
赞助商链接