SQLServer统计信息理解 - 数据库编程

前言

Sqlserver 查询是基于开销查询的，在首次生成执行计划时，是基于多阶段的分析优化才确定出较好的执行计划。而这些开销的基数估计，是根据统计信息来确定的。统计信息其实就是对表的各个字段的总体数据进行分段分布，数据库默认都会自动维护。

表和视图都有统计信息，统计信息对象是根据索引或表列的列表创建的。当某列第一次最为条件查询时，将创建单列的统计信息。当创建索引时，将创建同名的统计信息。索引中，统计信息只统计首列，因此索引除了按首列排序存储数据外，其统计信息也是按首列计算统计的，所以索引设置时定义的第一列非常重要。每个统计信息对象都在包含一个或多个表列的列表上创建，并且包括显示值在第一列中的分布的直方图。

接下来了解统计信息吧~~ ^ ^

统计信息的查看：

可以看到，统计信息也是表的一种对象。

--列出表中的所有统计信息
select * from sys.stats where object_id=OBJECT_ID(N'[Sales].[SalesOrderDetail]')

--查看统计信息及其列
SELECT s.name AS statistics_name ,c.name AS column_name ,sc.stats_column_id
FROM sys.stats AS s
INNER JOIN sys.stats_columns AS sc ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id
INNER JOIN sys.columns AS c  ON sc.object_id = c.object_id AND c.column_id = sc.column_id
WHERE s.object_id = OBJECT_ID(N'[Sales].[SalesOrderDetail]');

--查看所有统计信息更新时间
exec sp_helpstats N'[Sales].[SalesOrderDetail]', 'ALL'

统计信息的属性：

右键统计信息，选择“属性”，可看到统计信息的设置和分布。

还可以使用命令DBCC SHOW_STATISTICS查看，以下为列。
DBCC SHOW_STATISTICS('[Sales].[SalesOrderDetail]','IX_SalesOrderDetail_ProductID')

主要分为三部分，分别为“统计信息头部”，“密度向量”，“直方图”

1 统计信息头信息

列名	说明
Name	统计信息对象的名称（IX_SalesOrderDetail_ProductID）
Updated	上一次更新统计信息的日期和时间（Mar 14 2012 1:14PM）
Rows	上次更新统计信息时表或索引视图中的总行数（121317）。如果筛选统计信息或者统计信息与筛选索引对应，该行数可能小于表中的行数
Rows Sampled	用于统计信息计算的抽样总行数（121317）。如果 Rows Sampled < Rows，显示的直方图和密度结果则是根据抽样行估计的。
Steps	直方图中的梯级数(200)。每个梯级都跨越一个列值范围，后跟上限列值。直方图梯级是根据统计信息中的第一个键列定义的。最大梯级数为 200。
Density	计算公式为：1/统计信息对象第一个键列中的所有值（不包括直方图边界值）的 distinct values。(0.0078125) 查询优化器不使用此 Density 值，显示此值的目的是为了与 SQL Server 2008 之前的版本实现向后兼容。
Average Key Length	统计信息对象中所有键列的每个值的平均字节数 (12 :3个int类型。 ProductID, SalesOrderID, SalesOrderDetailID)
String Index	（NO）Yes 指示统计信息对象包含字符串摘要统计信息，以改进对使用 LIKE 运算符的查询谓词的基数估计；例如 WHERE ProductName LIKE '%Bike'。字符串摘要统计信息与直方图分开存储，如果统计信息对象为char、varchar、nchar、nvarchar、varchar(max)、nvarchar(max)、text 或 ntext. 类型，则基于其第一个键列创建字符串摘要统计信息。
Filter Expression	包含在统计信息对象中的表行子集的谓词。 NULL = 未筛选的统计信息。
Unfiltered Rows	应用筛选表达式前表中的总行数（121317）。如果 Filter Expression 为 NULL，则 Unfiltered Rows 等于 Rows。

2 密度信息

列名	说明
All Density	Density 为 1/distinct values。结果显示统计信息对象中各列的每个前缀的密度，每个密度显示一行。非重复值是每个行前缀和列前缀的列值的非重复列表。反映索引列的选择性（selectivity） "选择性"反映数据集里重复的数据量是多少，或者反过来说，值唯一的数据量有多少。如果一个字段的数据很少有重复，那么他的可选择性就比较高。比如身份证号，是不可重复的。哪怕对整个中国的身份记录做查询，代入一个身份证号码最多只会有一条记录返回，在这样的字段上的过滤条件，能够有效地过滤掉大量数据返回的结果集会比较小举个相反的例子：性别。所有人只有两种，非男即女。这个字段上的重复性就很高选择性就很低。一个过滤条件，最多只能过滤掉一半的记录SQL通过计算“选择性”，使得自己能够预测一个过滤条件做完后，大概能有多少记录返回 Density的定义是： density = 1/cardinality of index keys如果这个值小于0.1，一般讲这个索引的选择性比较高，如果大于0.1，他的选择性就不高了。（参考《Microsoft sqlserver 企业级平台管理实践》）
Average Length	存储列前缀的列值列表的平均长度（以字节为单位）。
Columns	为其显示 All density 和 Average length 的前缀中的列的名称

这里至于为什么会有3行，是因为【ProductID】为非聚集索引，【SalesOrderID,SalesOrderDetailID】为聚集索引，而每个非聚集索引中都包含有聚集索引的键值，所以这里的统计信息也出现了3个可选项。

当前统计信息 [All Density] 计算方法：

select count(*) from (select count(*) a from [Sales].[SalesOrderDetail]group by ProductID ) as T
select count(*) from (select count(*) a from [Sales].[SalesOrderDetail] group by ProductID,SalesOrderID) as T
select count(*) from (select count(*) a from [Sales].[SalesOrderDetail] group by ProductID,SalesOrderID,SalesOrder

SQLServer统计信息理解(一)