SQL Server统计信息偏差影响表联结方式案例浅析

 

我们知道数据库中的统计信息的准确性是非常重要的。它会影响执行计划。一直想写一篇关于统计信息影响执行计划的相关博客，但是都卡在如何构造一个合适的例子上，所以一直拖着没有写。巧合，最近在生产环境中遇到这么一个案例，下面对案例中的相关信息做了脱敏处理，有些中间步骤也省略了，只关注核心部分sql。如下所示，同事反馈一个sql语句执行很慢。

 

 

update  b

set     b.[status] = '已扫描，未签收' ,

        b.[time] = pr.createddate

from    #batch b

        join wdpm.pdarecords pr with ( nolock ) on b.batch_no = pr.orderno

                                                              and pr.functionname = '[wdpm].[usp_saveoutorder]'

where   b.[status] = '已打单，未扫描'

        and pr.createddate > b.[time];

 

 

 

如下截图所示，这个sql语句基本上耗时271秒。一个临时表与一个表做嵌套循环连接（nested loops）。 因为表wdpm.pdarecords只有一个聚集索引，所以执行计划中，这个表走聚集索引扫描。

 

 

 

 

 

 

注意：这里表wdpm.pdarecords本身缺少合适的索引，只有一个聚集索引。后面展开讲述这个问题.这里先围绕统计信息的准确性对执行计划的影响来展开讲述。

 

 

 

物理表wdpm.pdarecords的数据量为2505369（当然这个是一直在变化的。这个数值仅仅是实验前的检测记录，一直有会话对其进行dml操作，所以数据会变化，所以这里没有列出统计信息截图）。

 

 

我们看到table scan部分，预估行数（estimated number of rows）为1， 实际行数为150。 这个偏差已经比较大了。

 

 

 

对于物理表wdpm.pdarecords而言，基数估计的预估行数（estimated number of rows）为921771， 但是由于嵌套循环连接，所以累加起来的实际行数（actual number of rows）为： 921771*150  =138265650  。

 

 

 

 

我们知道嵌套循环（nested loops）算法的时间复杂度为n*m， n的预估值从1变成了150 ，这里面的偏差就大了（因为每次聚集索引扫描的开销也很大）。所以导致优化器在表的物理连接方式上选择了嵌套循环（nested loops), 因为预估的代价是很小的。但是实际因为统计信息的误差，导致这个代价放大了150倍。那么如果我们更新临时表的统计信息呢？然后执行这个sql，会有什么变化呢？ 

 

如下所示，我们在执行sql语句前，更新一下临时表的统计信息。发现优化器在获取了准确的统计信息后，在表的物理连接上选择了hash join方式。而且sql语句耗时变成了1秒多。为什么呢？ 因为优化器发现选择nested loops的代价远远高于 hash join。所以它在获取了准确的信息后，作出了最优选择。之前之所以生成了一个错误的执行计划，就是因为它得到的“信息”不准确，导致它作出了错误的抉择。这个就好比你获取了错误的信息，作出了错误的选择，购买了一只错误的股票，而巴菲特由于掌握了准确的行业信息，作出了正确的选择。 购买了几只购票都大涨了。

 

update statistics #batch with fullscan;

update  b

set     b.[status] = '已扫描，未签收' ,

        b.[time] = pr.createddate

from    #batch b

        join wdpm.pdarecords pr with ( nolock ) on b.batch_no = pr.orderno

                                                              and pr.functionname = '[wdpm].[usp_saveoutorder]'

where   b.[status] = '已打单，未扫描'

        and pr.createddate > b.[time];

 

 

 

 

 

 

当然，了解到这里，还远远没有结束。我们发现表wdpm.pdarecords 只有一个聚集索引，而且聚集索引位于iden自增字段上，从另外一个角度来看，这个表其实是缺少合适的索引的。那么我们可以创建一个索引。

 

create index ix_pdarecords_n1 on wdpm.pdarecords(orderno,functionname)

 

 

创建索引后，即使不更新临时表#batch的统计信息，我们发现执行计划也会走嵌套循环（nested loops)，而不会走hash join了。这个又是什么原因呢？

 

 

此处截图，是第二次执行sql，临时表的数据变化了（生成临时表的数据的sql有好几个，每次执行获取的数据都会有部分变化）

 

 

因为有了合适的索引，趋近准确的统计信息，以及谓词下推（predicate push down），基数(cardinality)的预估行数（esitmted row size）为35.0545 与实际行数（actual number of rows）为666， 这样即使循环次数为140.  总的访问记录数为140*666=93240 ， 这个是远远小于之前错误执行计划的138265650 。所以即使临时表的#batch的统计信息有误，但是优化器还是生成了一个不错的执行计划。这样sql的执行时间也就缩短到了1秒内.

 

 

 

这个案例仅仅是为了展示：统计信息的准确与否，会导致优化器生成的执行计划选择不同的表连接方式， 例如从嵌套循环（nested loops)变成hash join。 仅仅是为了说明统计信息准确的重要性。