写有效率的SQL查询（VI）

我们先看NestedLoop和MergeJoin的算法（以下为引用，见RicCC的《通往性能优化的天堂-地狱 JOIN方法说明》):
==================================
NestedLoop:
   foreach rowA in tableA where tableA.col2=?
    {
    search rowsB from tableB where tableB.col1=rowA.col1 and tableB.col2=? ;
    if(rowsB.Count<=0)
        discard rowA ;
    else
        output rowA and rowsB ;
    }
MergeJoin:
两个表都按照关联字段排序好之后，merge join操作从每个表取一条记录开始匹配，如果符合关联条件，则放入结果集中；否则，将关联字段值较小的记录抛弃，从这条记录对应的表中取下一条记录继续进行匹配，直到整个循环结束。
==================================

 

我们通过最简单的情况来计算NestedLoop和MergeJoin的消耗：
两张表A、B，分别有m、n行数据（m < n），占用基础表物理存储空间分别为a、b页，聚集索引树非叶节点都是两层（一层根节点，一层中间级节点），A、B的聚集索引建在A.col1、B.col1上。一条查询语句：
select A.col1, B.col2 from A inner join B where A.col1 = B.col1。

 

执行NestedLoop操作：
A作为outer input，B作为inner input时：A带来的IO为a；每次通过clustered index seek执行内部循环，花费3(一个根节点、一个中间集结点、一个叶节点。当然也可能直接从根节点就拿到要的数据，我们只考虑最坏的情况），这样执行整个嵌套循环过程消耗IO为a + 3*m。如果B作为inner input，A作为outer input分析类似。

执行MergeJoin:
MergeJoin要把A、B两张表做个Scan，然后进行Merge操作。所以A、B分别带来IO为a + b就是总的逻辑IO开销。

 

从上述分析来看，若a + 3*m << a + b，即3*m << b，那么NestedLoop性能是极佳的。当然，我们比较A表的行和B表所占数据页大小看上去有点夸张，但是量化分析确实如此。在这里，我们没有计算NestedLoop和MergeJoin本身的cpu计算开销，特别是后者，这部分并不能完全忽略，但是也来得有限。

 

OK，现在我们试图执行实际的语句验证我们的观点，看看能发现什么。

我有两张表，一张表charge，聚集索引在charge_no上，它是个int identity(1,1)，共10万行，数据页582张，聚集索引非叶节点2层。一张表A，聚集索引在col1上（唯一），共999行，数据页2张，聚集索引两层。min(A.col1) = min(charge.charge_no)、Max(A.col1) < max(charge.charge_no)。

我们在set statistics io on和set statistics time on之后，执行语句：

select A.col1, charge.member_no from A inner join charge

    on A.col1 = charge.charge_no

option(loop join) -–执行NestedLoop

go

select A.col1, charge.member_no from A inner join charge

    on A.col1 = charge.charge_no

option(merge join)--执行MergeJoin。

结果集都是999行，而且我们看到消息窗口中输出为：



 

（图1）

从上图中我们注意到几点比较和最初分析不同的地方：

1.      Nested Loop时，表A的逻辑读是4，而不是预计中的表A数据页大小2；charge逻辑读2096，而不是预计中的3×999。

2.      Merge Join时，表Charge的逻辑读只有8。

对1来说，表A的逻辑读是4是因为clustered index scan需要从聚集索引树根节点开始去找最开始的那张数据页，表A的聚集索引树深度为2，所以多了两个非页节点的

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16998571/viewspace-567163/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16998571/viewspace-567163/