写有效率的SQL查询(VI).docVIP

我们先看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，所以多了两个非页节点的IO。不是3×999是因为有些记录（设为n）直接从根节点就能找到，也就是说有些是2×n + （999-n)* 3 对2来说，MergeJo