SQL Server nested loop join 效率试验
来源:互联网 发布:四川大学网络教育吧 编辑:程序博客网 时间:2024/06/05 23:04
从很多网页上都看到,SQL Server有三种Join的算法, nested loop join, merge join, hash join. 其中最常用的就是nested loop join.
在介绍nested loop join的很多文章里,都提到如果两个表做nested loop join,取行数较小的表作为外循环表,行数较多的表作为内循环表, join的效率会比较高.
其中之一的原因是如果内循环表做join的列上有合适的索引的话,那么外循环的每一条输入数据可以做索引的seek,这样就不会把整个的内循环表读一遍,尤其是内循环表比较大的话,节省的成本更高. 但是如果内外循环表都没有合适的索引,这样做join,为什么效率也比较高呢?
举个例子,外循环表有10行数据,内循环表有1000行数据,按照nested loop join 的算法,外循环表中取一条,和内循环表的所有数据匹配一遍,输出匹配的数据行. 这样就是要进行10*1000=10000次的匹配; 如果反过来,外循环1000行,内循环10行,那么外循环表中取一条数据,内循环表中遍历10行数据,总计也是1000*10=10000次. 粗看来都一样啊..为什么都说外循环表小的话,效率高呢? 做个试验看看吧.
use tempdbgo--创建两个表,测试nested loop join的效率CREATE TABLE TempA (string VARCHAR(1000))goCREATE TABLE TempB (string VARCHAR(1000))go--插入数据, 让表TempA中的数据刚好存在1页里INSERT INTO TempA SELECT REPLICATE('a' , 1000)INSERT INTO TempA SELECT REPLICATE('b' , 1000)INSERT INTO TempA SELECT REPLICATE('c' , 1000)INSERT INTO TempA SELECT REPLICATE('d' , 1000)INSERT INTO TempA SELECT REPLICATE('e' , 1000)INSERT INTO TempA SELECT REPLICATE('f' , 1000)INSERT INTO TempA SELECT REPLICATE('g' , 1000)--往TempB中插入数据,让TempB的数据是TempA的100倍insert into TempB select * from TempAgo 100--检验一下表TempA 和 TempB的大小set statistics io onselect * from TempAselect * from TempB--返回的结果如下:/*Table'TempA'. Scan count 1, logical reads 1Table'TempB'. Scan count 1, logical reads 100*/--由此可以看出表TempA有7行,存储在1个页; TempB有700行,存储在100个页里.--执行以下查询,将TempA作为外循环表,TempB作为内循环表,看看执行的成本如何SELECT *FROM TempA a INNER LOOP JOIN TempB bONa.string = b.string OPTION (FORCE order)/*Table'TempB'. Scan count 1, logical reads 700Table'TempA'. Scan count 1, logical reads 1*/从结果可以看出从TempA读了1个页,从TempB读了700个页,合计701个逻辑读, 也就是说外循环的表,做一次全表读,有多少页就有多少逻辑读; 内循环的表,对应外循环表的每1条记录,都要读一次全表读,即7乘以100,700个逻辑读.
如果按照这个规律,调换内外循环表的位置,得到的逻辑读应该是 TempB的一次全表读, 100个逻辑读加上700行乘以TempA的全表读(1页),就是700个逻辑读,合计是800个逻辑读.
试验一下看看结果:
--对调一下join的顺序,再看看执行成本:SELECT *FROM tempb b INNER LOOP JOIN tempa aONa.string = b.string OPTION(FORCE ORDER) /*Table'TempA'. Scan count 1, logical reads 700Table'TempB'. Scan count 1, logical reads 100*/
果不其然,和预计的一样.
所以在这种假定的情况下,外循环表较小的话,join的成本更低.
实验的表结构比较特殊,如果往一般情况推演一下,可以做出这样的假设:假设表X有a页,平均每页有b行,表Y有c页,平均每页有d行.
则以表X为外循环,表Y为内循环,则nested loop join的成本是 a+(a*b*c), 而已表Y为外循环,表X为内循环,则nested loop join的成本是 c+(c*d*a)
比较两种方式的成本大小可以将两个代数式相减,再根据abcd不同的情况的出相应的结论.希望各位可以自行推理一番..
- SQL Server nested loop join 效率试验
- SQL Server nested loop join 效率试验
- 浅谈SQL Server中的三种物理连接操作(Nested Loop Join、Merge Join、Hash Join)
- 浅谈SQL Server中的三种物理连接操作(HASH JOIN MERGE JOIN NESTED LOOP)
- 浅谈SQL Server中的三种物理连接操作(HASH JOIN MERGE JOIN NESTED LOOP)
- HASH JOIN ,MERGE JOIN ,NESTED LOOP用法效率比较
- sql hash join VS nested loop
- SQL server 内部实三种类型的物理连接运算:Merge join、Hash join、Nested loop join对比分析
- Nested-Loop Join Algorithms
- 【sql调优之执行计划】nested loops join and nested loop join outer
- mysql Index Nested-Loop Join
- SQL三种物理连接操作Nested Loop Join,Merge Join,Hash Join
- HASH JOIN ,SORT MERGE JOIN ,NESTED LOOP
- HASH JOIN/MERGE JOIN/NESTED LOOP
- 描述HASH JOIN ,MERGE JOIN ,NESTED LOOP
- Hash Join, Nested Loop, Sort-Merge Join
- Hash Join 与 Nested-Loop Join
- HASH JOIN ,MERGE JOIN ,NESTED LOOP 比较
- linux expect 用法小记
- C++常用库
- iBATIS 2.0 开发指南配置文件说明
- DB2 export
- 开关电源测试步骤(图文解说)
- SQL Server nested loop join 效率试验
- 在ubuntu上手工安装tomcat6
- DB2 import
- Mongodb 语法入门手册
- DB2 load
- Compare the function: SQL server vs ORACLE
- 将request对象map中的值封装成数据实体对象
- 设置浏览器禁止访问某网站
- myEclipse 快捷键设置详解