SQL Server 统计信息

Lokie.Wang
2020-03-30 23:04:46
数据库
MSSQL

SQL Server统计信息是区别于Mysql的一个重要特性，MySQL数据库中并不存在统计信息这一个东西，可能对于一部分开发来说从来没有听说过统计信息这一东西，本文的将来介绍统计信息的一些知识。

什么是统计信息？统计信息的用途是什么？

SQL Server统计信息SQL Server statistics，主要是用来优化SQL语句的执行速度的。其原理是查询优化器采用统计信息来产生执行计划来改善查询的性能。对于多数的查询而言查询优化器会产生足够的统计信息来预估出比较好的执行计划。

统计信息是二进制大型对象 (BLOB)，这些对象包含与值在表或索引视图的一列或多列中的分布有关的统计信息。查询优化器使用这些统计信息来估计查询结果中的基数或行数。通过这些基数估计，查询优化器可以创建高质量的查询计划。比如当查询优化器觉得通过根据谓词使用基数估计确定选择索引查找运算符而不是更耗资源的索引扫描运算符性能更高。对于统计信息相关关联的两个重要概念是直方图和密度向量。

1）直方图
直方图度量数据集中每个非重复值的出现频率。查询优化器根据统计信息对象第一个键列中的列值来计算直方图，它选择列值的方法是以统计方式对行进行抽样或对表或视图中的所有行执行完全扫描。如果直方图是根据一组抽样行创建的，存储的总行数和非重复值总数则为估计值，且不必为整数。

若要创建直方图，查询优化器将对列值进行排序，计算与每个非重复列值匹配的值数，然后将列值聚合到最多 200 个连续直方图梯级中。每个直方图梯级都包含一个列值范围，后跟上限列值。该范围包括介于两个边界值之间的所有可能列值，但不包括边界值自身。最小排序列值是第一个直方图梯级的上限值。

SQL Server 通过以下三个步骤从已排序的列值集创建直方图

直方图初始化：

在第一步中，处理始于排序集开始处的一个值序列，并收集 range_high_key、equal_rows、range_rows 和 distinct_range_rows 的最多 200 个值（在此步骤中，range_rows 和 distinct_range_rows 始终为零）。已用尽所有输入或已找到 200 个值时，结束第一步。

使用 Bucket 合并进行扫描：

第二步中，按排序顺序处理从统计信息键前导列算起的每个额外值；将每个相继值添加到最后一个范围或在末尾创建一个新范围（这可能是因输入值已排序所致）。如果创建了一个新范围，则一对现有相邻范围折叠为单个范围。选择这对范围以最大限度减少信息丢失。此方法使用最大差异算法使直方图中的梯级数减至最少，并同时最大化边界值之间的差异。折叠后，梯级数在整个步骤中保持为 200。

直方图合并

第三步中，如果未丢失大量信息，可能折叠更多范围。直方图梯级数可以少于非重复值的数目，即使对于边界点少于 200 的列也是如此。因此，即使列包含超过 200 个唯一值，直方图具有的梯级数可能少于 200。对于仅包含唯一值的列，合并的直方图具有三个梯级（最小梯级数）。

2）密度向量

密度是有关给定列或列组合中重复项数目的信息，其计算公式为 1/（非重复值数目）。查询优化器使用密度提高根据相同表或索引视图返回多个列的查询的基数估计。密度与值的选择性成反比，密度越小，值的选择性越大。例如，在一个代表汽车的表中，很多汽车出自同一制造商，但每辆车都有唯一的车牌号 (VIN)。因为 VIN 的密度比制造商低，所以 VIN 索引比制造商索引更具选择性。

那么SQL Server何时创建统计信息？
查询优化器已通过以下方式创建统计信息：
在创建索引时，查询优化器为表或视图上的索引创建统计信息。这些统计信息将创建在索引的键列上。如果索引是一个筛选索引，则查询优化器将在为该筛选索引指定的行的同一子集上创建筛选统计信息。

对于大多数查询，用于创建统计信息的这两个方法就可以确保高质量的查询计划；但在很少的情况下，可以通过使用 CREATE STATISTICS 语句创建附加的统计信息来改进查询计划。这些附加的统计信息可以捕获查询优化器在为索引或单列创建统计信息时并未考虑的统计关联。应用程序可能在表数据中具有附加的统计关联，如果在统计信息对象中计入这些关联，可能会令查询优化器改进查询计划。例如，针对数据行子集的筛选统计信息或针对查询谓词列的多列统计信息可改进查询计划。

说了这么多，我们看下一个实例，Sql Server究竟是怎么用统计信息的，以及怎么样才可以看到统计信息和直方图。首先统计信息一般伴随索引一起存在，我们来看一个统计信息。
首先来看下表和它的结构：

可以看到表存在一个负荷的主键和若干个字段
再来看下他的索引信息：

存在一个聚合索引和两个非聚合的索引，通过SSMS我们首先来查看下该表的统计信息

可以看的_WA开头的一些统计信息是系统自己生成的，而和索引同名的几个就是索引的统计信息。通过如下语句我们来看下他的具体信息

    
    DBCC SHOW_STATISTICS ('dbo.business_RebidData', idx_business_RebidData_BidSectionDataKey_ReBidOrReBulletin)
    GO

结果如下图：

三个结果分别表示：数据显示标题、密度向量、直方图
我们来阅读下这三方信息。

第一个结果分别显示了该统计信息的更新的时间、行数、采样函数、密度向量等

第二过结果密是有关给定列或列组合中重复项数目的信息，那为什么选取这几个呢？来看下这个索引的情况就知道了

可以看成包含的两列都存在在密度向量中，同时由于该两列中的BidSectionDataKey是主键索引的一部分，因此这两列和主键的组合也存在密度向量中，因为主键不重复的特性使得计算密度向量比较容易，SQL Server选择了这种方式进行密度的分布。

第三个结果集主要是显示直方图信息，看到主要通过是对BidSectionDataKey对象的采样进行的，EQ_ROWS就是数据中是这个值的数据行数，当然不是真实的数量，而是在直方图生成是的统计数量。由于这种表数量的关系，收集值不满200行。

RANGE_HI_KEY 直方图梯级的上限列值。列值也称为键值。
RANGE_ROWS 其列值位于直方图梯级内（不包括上限）的行的估算数目。
EQ_ROWS 其列值等于直方图梯级的上限的行的估算数目。
DISTINCT_RANGE_ROWS 非重复列值位于直方图梯级内（不包括上限）的行的估算数目。
AVG_RANGE_ROWS 重复列值位于直方图梯级内（不包括上限）的平均行数（如果 DISTINCT_RANGE_ROWS > 0，则为 RANGE_ROWS / DISTINCT_RANGE_ROWS）。

下面看看它们是如何被用来做参数预估（Cardinality Estimation）（估计行数的计算）执行如下SQL

    select * FROM dbo.business_RebidData where BidSectionDataKey = '3210001010222220160329000002'

返回了7数据，看下执行计划吧

可以看到估计行数和实际行数是一样的，这是因为就采用了这个DataKey查询，所以这是时候用了EQ_ROWS做预估，并且由于刚好是主键索引，因此执行计划只需要到主键索引去做索引的查询然后join起来就可以了。由于这表的直方图不满200，步长信息只有167，所有BidSectionDataKey信息都在这个这个范围内，素有一直会用EQ_Rows去预估。那其他的的呢？

在直方图里没有完全匹配值时，SQL Server也能进行基数计算。因此在直方图里会有RANGE_ROWS列和DISTINCT_RANGE_ROWS列。从上述解释可以看出，直方图并不难理解。直方图里很重要的一点是，SQL Server只为索引中第1个键列中的列值创建直方图。索引中的所有后续列，SQL Server在密度向量里存储。因此，在组合索引键里，第1列应该是选择性最高的那列（查询经常用到的）

先写到这里，下篇关于统计信息的维护

本文为Lokie.Wang原创文章,转载无需和我联系,但请注明来自lokie博客http://lokie.wang