代码改变世界

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

2019-03-31 19:54  潇湘隐者  阅读(...)  评论(... 编辑 收藏

 

我们知道数据库中的统计信息的准确性是非常重要的。它会影响执行计划。一直想写一篇关于统计信息影响执行计划的相关博客,但是都卡在如何构造一个合适的例子上,所以一直拖着没有写。巧合,最近在生产环境中遇到这么一个案例,下面对案例中的相关信息做了脱敏处理,有些中间步骤也省略了,只关注核心部分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只有一个聚集索引,所以执行计划中,这个表走聚集索引扫描。

 

 

 

 

clip_image001

 

 

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

 

clip_image002

 

 

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

 

clip_image003

 

我们看到Table Scan部分,预估行数(Estimated Number of Rows)为1, 实际行数为150。 这个偏差已经比较大了。

 

 

clip_image004

 

对于物理表WDPM.PdaRecords而言,基数估计的预估行数(Estimated Number of Rows)为921771, 但是由于嵌套循环连接,所以累加起来的实际行数(Actual Number of Rows)为: 921771*150  =138265650  。

 

clip_image005

 

clip_image006

 

 

我们知道嵌套循环(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];

 

 

 

clip_image007

 

 

clip_image008

 

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

 

CREATE INDEX IX_PdaRecords_N1 ON wdpm.PdaRecords(OrderNo,FunctionName)

 

 

创建索引后,即使不更新临时表#Batch的统计信息,我们发现执行计划也会走嵌套循环(Nested Loops),而不会走Hash Join了。这个又是什么原因呢?

 

clip_image009

 

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

 

clip_image010

 

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

 

 

clip_image011

 

这个案例仅仅是为了展示:统计信息的准确与否,会导致优化器生成的执行计划选择不同的表连接方式, 例如从嵌套循环(Nested Loops)变成Hash Join。 仅仅是为了说明统计信息准确的重要性。