ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 写有效率的SQL查询(VI)

写有效率的SQL查询(VI)

原创 Linux操作系统 作者:sunger_chen 时间:2009-03-13 13:32:58 0 删除 编辑

我们先看NestedLoopMergeJoin的算法(以下为引用,见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操作从每个表取一条记录开始匹配,如果符合关联条件,则放入结果集中;否则,将关联字段值较小的记录抛弃,从这条记录对应的表中取下一条记录继续进行匹配,直到整个循环结束。
==================================

 

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

 

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

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

 

从上述分析来看,若a + 3*m << a + b,即3*m << b,那么NestedLoop性能是极佳的。当然,我们比较A表的行和B表所占数据页大小看上去有点夸张,但是量化分析确实如此。在这里,我们没有计算NestedLoopMergeJoin本身的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 onset 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数据页大小2charge逻辑读2096,而不是预计中的3×999

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

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

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

下一篇: SQL常用的语法
请登录后发表评论 登录
全部评论

注册时间:2009-01-09

  • 博文量
    28
  • 访问量
    23963