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

对 2 来说， MergeJoin 时，表 Charge 并不是从头到尾扫描，而是从 A 表的最大最小值圈定的范围之内进行扫描，所以实际上它只读取了 6 张数据页。

OK , 为了验证对 2 的解释，我们在表 A 中插入一条 col1 > max(charge.charge_no) 的记录，然后执行：

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

    on A. col1 = charge. charge_no

option ( merge join)-- 执行 MergeJoin 。

 

（图 2 ）

现在 charge 逻辑读成了 582 + 2 = 584 ，验证了我们的想法。

那么如果 min(A.col1) > min(charge.charge_no) ， max(A.col1) = max(charge.charge_no) 时 SQLServer 会不会聪明到再次选择一个较小的扫描范围呢？很遗憾，不会 -_-…. 不知道 MS 这里基于什么考虑。

========================================

我们现在回到图 1 ，实际上我们从图 1 中还能发现 SQL 的分析编译占用时间相对执行占用时间不仅不能忽略，还占了很大比重，所以能避免编译、重编译，还是要尽可能的避免。

========================================

 

OK ，现在我们开始分析分析执行计划，看看 SQLServer 如何在不同的执行计划之间做选择。

我们首先把 A 表 truncate 掉，然后里面就填充一条数据， update statistics A 之后，看看执行计划：



 

（图 3 ： NestedLoop 的执行计划）

（图 4 ： MergeJoin 的执行计划）

我们把鼠标分别移到图 3 和图 4 中 A 表的 Clustered Index Scan 上，会看到完全一样的 tip ：



 

这个“ I/O 开销”就是两个逻辑 IO 的开销（就一条记录，自然是一个聚集索引根节点页，一个数据页，所以是 2 ）；估计行数为 1 ，很准确，我们就 1 行记录。

现在我们把鼠标分别移动到图 3 、图 4 中 charge 表的 Clustered Index Scan 上，看到的则略有不同

 

（图 5 ： NestedLoop ）                 （图 6 ： Merge Join ）

Nested Loop 中的开销评估看起来还算正常，运算符开销 = （估计 IO 开销 + 估计 CPU 开销）×估计行数。（注意， NestedLoop 中，大表是作为内存循环存在的，计算运算符开销别忘了乘上估计行数）。

但是 Merge Join 中我们发现“估计行数”很不正常，居然是总行数（相应的，估计 IO 开销和估计 CPU 开销自然都是全表扫描的开销，这个可以跟 select * from charge 的执行计划做个对比）。显然，执行计划中显示的和实际执行情况非常不同，实际情况按照我们上面的分析，应该就读取 3 张数据页，估计行数应该为 1 。误差是非常巨大的， 3IO 直接给估算成了 584IO 。翻了翻在 pk_charge 上的统计信息，采样行数 10w ，和总行数相同，再加上第二个结果集提供的信息，已经足够采取优化算法去评估查询计划。不知道 MS 为什么没有做。

好吧，我们假设执行计划的评估总是估算最坏的情况。由于 Merge Join 算法比较简单，后面我们只关注 NestedLoop.

我们首先给 A 表增加一行 ( 值为 2) ，然后再来分析执行计划。


 

（图 7 ： A 表NestedLoop）                                       ( 图 8 ： charge 表NestedLoop )

我们从图 7 上可以看到， IO 开销没有增加， CPU 开销略微增加，这很容易理解， A 表只增加了一行，其占用索引页和数据页和原来一样。但是由于行数略有增加， cpu 消耗一定会略有增加。

奇怪的是图 8 显示的 charge 表上的 seek. 对比图 5 ，运算符开销并没有像我们预料的那样增加一倍，而是增加了 0.003412 – 0.003283 = 0.000129. 这个数值远小于 IO 开销。为了多对比一次，这次我们再往 A 表里面插入一条记录（值为 3 ），再来看看 charge 表上的运算：

（图 9 ， charge 表NestedLoop）

这次我们又发现，这次增加的消耗是 0.0035993 – 0.003412 = 0.0001873 ，仍然远远小于一次的 IO 开销。

好吧，那么我们假设执行计划估算算法认为，如果某一页缓存被读到 SQL Engine 中之后就不会再被重复读取。为了验证它，我们试试把 A 表连续地增加到 1000 行，然后看看执行计划：

（图 10 ， charge 表NestedLoop）

我们假设每次进行 clustered index seek 消耗的 cpu 是相同的，那么我们可以计算出来查询计划认为的 IO 共有：（运算符开销 – cpu 开销 *1000 ） / IO 开销 = 5.81984 。要知道 charge 表数据页总数为 582 ， 1000 行恰好是 100000 的百分之一， 1000 行恰好占用了 5.82 页……（提醒一把，这 1000 行是连续值）

OMG… 这次执行计划算法明显的比实际算法聪明。看上去像是， NestedLoop 在每次 Loop 时都会缓存本次 Loop 中读取的数据页，这样当下次 Loop 时，如果目标数据页已经读取过，就不再读取，而直接从 Engine 内存中取。

 

=========================================================

从上面的讨论可以看出，有时候执行计划挺聪明，有时候实际的执行又很聪明，总之，咱是不知道为啥微软不让执行计划和实际的执行一样聪明，或者一样愚蠢。这样，至少 SQL 引擎在评估查询计划的时候可以比较准确。

 

btw: 接着图 10 的例子，各位安达还可以自己去试试 insert 一条大于 max(charge.charge_no) 的记录到表 A 里，然后试试看看 charge 表运算符上有什么变化。

==================================================

 

回到最初的主题，根据我们看到的SQL引擎实际执行看，只有 A 表行集远远小于 charge_no 的时候， SQLServer 为我们选择的 NestedLoop 才是非常高效的；为了保证更小的IO，当(B表索引树深度*A表行数>B表数据页+B表索引树深度）的时候，就可以考虑是否要指定MergeJoin。

值得一提的是，经过多次的实验， SQL 这样评估 MergeJoin 和 NestedLoop ，最后选择它认为更优的查询计划，居然多数情况下都是正确的……我是晕了，不知道你晕了没有。

==================

刚才（22:00)本子待机了一次，然后再开机的时候我没办法重现SQLServer自己选择NestedLoop总是比MergeJoin的cpu占用时间短了。现在的情况是：SQLServer每次都错误的选择了NestedLoop，导致的结果是IO相差20 ～ 30倍，执行时间多了百分之50。  
============================