我们知道数据库中的统计信息的准确性是非常重要的. 它会影响执行计划. 一直想写一篇关于统计信息影响执行计划的相关博客, 但是都卡在如何构造一个合适的例子上, 所以一直拖着没有写. 巧合, 最近在生产环境中遇到这么一个案例, 下面对案例中的相关信息做了脱敏处理, 有些中间步骤也省略了, 只关注核心部分 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 自增字段上, 从另外一个角度来看, 这个表其实是缺少合适的索引的. 那么我们可以创建一个索引.
CREATEINDEXIX_PdaRecords_N1ONwdpm.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. 仅仅是为了说明统计信息准确的重要性.
来源: https://www.cnblogs.com/kerrycode/p/10632813.html