Sql Server 2005 嵌套循环算法

284354749

　　 前段时间看了一篇关于算法的blog，地址如下：
　　http://www.iyunv.com/perfectdesign/archive/2008/04/24/sql_tuning.html
　　
　　不少人也给了解决方法，以前也研究过（嵌套，合并，hash）算法，但没有真正的用到优化中，这个例子给了我很大启示。
　　  现在就讨论一下这三个算法的使用。
　　嵌套循环：
　　算法：
　　for each row R1 in the outer table     
　　for each row R2 in the inner table
　　if R1 joins with R2
　　return (R1, R2)
　　嵌套循环：适合小输入的表，输出也是小输出的数据量
　　外部循环逐行处理外部输入表。内部循环会针对每个外部行执行，在内部输入表中搜索匹配行。
　　SQL Server 帮助文档中介绍：

最佳使用: 如果外部输入较小而内部输入较大且预先创建了索引，则嵌套循环联接尤其有效。在许多小事务中（如那些只影响较小的一组行的事务），索引嵌套循环联接优于合并联接和哈希联接。但在大型查询中，嵌套循环联接通常不是最佳选择。

　　例子：表：workflowinfo1 约45万条 表workflowbase1 约4.5万条
　　    条件：workflowbase1中列id，creater都建立索引，workflowinfo1中workflowid建立了索引。

测试SQL语句：

注意

1，测试条件：（creater='402881411023dcc001102e3bbbc505c7'的workflowbase1表只有2条数据）

嵌套循环：

select * from workflowbase1 a inner loop join dbo.workflowinfo1 b

on a.id=b.workflowid and a.creater='402881411023dcc001102e3bbbc505c7'

合并连接

select * from workflowbase1 a inner merge join dbo.workflowinfo1 b

on a.id=b.workflowid and a.creater='402881411023dcc001102e3bbbc505c7'

hash连接

select * from workflowbase1 a inner hash join dbo.workflowinfo1 b

　　on a.id=b.workflowid and a.creater='402881411023dcc001102e3bbbc505c7'
　　
　　 对比SQL语句成本

　　
　　
　　这里返回的结果

(5 行受影响)

表'workflowinfo1'。扫描计数2，逻辑读取11 次，物理读取2 次，预读16 次，lob 逻辑读取0 次，lob 物理读取0 次，lob 预读0 次。

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

这里返回了5条数据。

这里在看一下输入很多条数据时：

1，测试条件：（creater=4028814110830a1e01108fe379e60061’的workflowbase1表有1023条数据）

   为避免上一次绑定变量的影响，重启数据库服务。对比成本

  

这时发现嵌套循环不是最优的算法：hash连接才是。

我们看看具体的执行结果：

(10468 行受影响)

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

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

(10468 行受影响)

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

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

(10468 行受影响)

表'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 次。

嵌套循环：IO次数：13843+1571=15414次

    合并，Hash连接：IO次数：9604+1571=11175次

这里的嵌套循环计数为1023次，从上面的嵌套循环算法可以看出：从表'workflowbase1'取出1023条，再逐条循环去对比'workflowinfo1'里的数据，有的话返回行。 由于合并算法也排序，这里的成本，hash连接要比合并要高效。

从这里可以看出嵌套循环适合适合小输入的表，输出也是小输出的数据量的情况，不适合查询出大数据量

同时输出的数据也是排过序的（通过索引（排序）去找数据）

如果一个联接输入很小（不到 10 行），而另一个联接输入很大而且已在其联接列上创建了索引，则索引 Nested Loops 连接是最快的联接操作。

由于嵌套循环相对其他两个算法。合并算法（必须按相等列分别排序），hash（必须列相等连接，而不是类似left(1,3)函数相等）是有条件限制的，一般嵌套循环在实际使用中会比较多。