你的位置:首页 > 数据库

[数据库]理解统计信息(0/6):密度


在日常生活中,我们用统计信息来采取决定。SQL Server优化器也用同样的方式,使用统计信息来选择正确的执行计划。如果统计信息错误或过期,SQL Server可能就会选择错误的执行计划。在这个文章里,我们换个方式理解下统计信息。

查询优化器使用统计信息来判断每一步返回的行。执行计划里的估计行数信息是基于列的可用统计信息计算而来的。统计信息给我们列里数据分布情况。没有统计信息,查询优化器不呢个判断不同计划的效率。通过使用统计信息,查询优化器在访问数据时可以做出正确的选择。

在我们定义索引时,统计信息会自动创建。除此之外,当列在查询里被引用,作为WHERE条件的一部分,在group by子句里或join条件里,统计信息都会创建。为了自动创建统计信息,在数据库层里的AUTO_CREATE_STATISTICS 设置应该被启用。默认情况下这个设置是被启用的。除此之外,统计信息可以使用CREATE STATISTICS命令创建。

在SQL Server里存储的统计数字是关于密度和直方图(数据分布)的信息。在我们讨论更多细节前,先理解这2个概念。

密度:在给出列或一组列惟一值的比例。统计密度的公式:1/列(或一组列)不同值个数。

密度用来衡量列的唯一性或列的选择性。密度的值在0和1之间。如果这列的密度值为1,表示这列的所有记录值一样,选择性低。更高的密度带来更低的选择性。如果这列的密度值为0.003,表示这列有1/0.003=333个不同值。

我们来看个例子,用下列语句创建表并在上面建立2个索引。 

1 USE StatisticsDB2 GO3 4 SELECT * INTO SalesOrderDetail FROM AdventureWorks2008R2.Sales.SalesOrderDetail5 CREATE UNIQUE CLUSTERED INDEX ix_SalesOrderDetailID ON SalesOrderDetail(SalesOrderDetailID)6 CREATE NONCLUSTERED INDEX ix_productid ON SalesOrderDetail(productid)

我们来看看这2个索引的统计信息。

1 DBCC SHOW_STATISTICS('dbo.SalesOrderDetail', 'ix_SalesOrderDetailID')

输出结果有3个表,分别是:统计信息头,密度和直方图。

在第1部分(统计信息头)

  • Name :索引名称
  • Updated :统计信息更新时间
  • Rows :索引里行数目。不是表里的行数。
  • Rows Sampled :用于生成统计信息的采样行数。创建索引的时候,会进行完全扫描。
  • Steps :直方图里的步长。(第3部分的内容)
  • Density :在SQL Server里不再使用,向老版本兼容。
  • Average key length :索引键平均长度。
  • String Index :用于like时估计统计行数。
  • Filter Expression :过滤索引表达式
  • Unfiltered Rows :未过滤的行数,如果不存在过滤索引,则等同于Row列。

可以使用DBCC SHOW_STATISTICS加WITH STAT_HEADER来只获取统计头信息。

在第2部分的密度表,我们只看到一条记录,因为在我们索引里只有1列。

所有密度列给我们SalesOrderDetailsId 列的密度值(1/列(或一组列)不同值个数)。所有密度列给我们值 8.242868E-06 =0.000008242868。这表示SalesOrderDetailsId 列有 1/0.000008242868=121317个唯一值,我们可以用下列语句验证下。

1 SELECT COUNT(DISTINCT SalesOrderDetailID ) FROM SalesOrderDetail

可以使用DBCC SHOW_STATISTICS加WITH DENSITY_VECTOR来只获取密度信息。

我们来看看非聚集索引ix_productid的密度。

1 DBCC SHOW_STATISTICS('dbo.SalesOrderDetail', 'ix_productid') WITH DENSITY_VECTOR

在密度表里可以看到2行记录,即使我们的非聚集索引是在单列上。这是因为聚集键是非聚集索引的一部分(参见索引深入浅出(4/10):非聚集索引的B树结构在聚集表)。

第一行告诉我们,ProductID 列的密度值为0.003759399,换句话说,在ProductID列有1/0.003759399=226个唯一值。我们可以验证下。

1 SELECT COUNT(DISTINCT ProductID) FROM dbo.SalesOrderDetail

第二行告诉我们,ProductID和SalesOrderDetailID组合列的密度值是0.000008242868,换句话说,ProductID和SalesOrderDetailID组合列有121317个唯一值,这个和表的总记录数是一致的。

密度值会用在哪里呢,我们看下下面查询的执行计划,点击工具栏的显示包含实际的执行计划。

1 SELECT ProductID,COUNT(*) FROM dbo.SalesOrderDetail GROUP BY ProductID

 

在执行计划里,在流聚合运算符的输出里,我们看到估计行数是266。在ProductID列唯一值(个数)可以从密度表里拿到。但我们怎么证明查询优化器是用这个值来计算估计行数。我们创建另外一个没有任何索引的SalesOrderDetail表。

1 SELECT * INTO SalesOrderDetail_NoStats FROM SalesOrderDetail

通过下面语句我们可以看出,这表没有任何的统计信息。

1 EXEC SP_HELPSTATS 'SalesOrderDetail_NoStats', 'ALL'

我们再看下这个表的刚才查询,点击工具栏的显示包含实际的执行计划:

1 SELECT ProductID,COUNT(*) FROM dbo.SalesOrderDetail_NoStats GROUP BY ProductID

在没有任何统计索引和统计信息的情况下,优化器再一次在流聚合运算符的输出里估计行数是266。我们再次检查下这个表的统计信息。

1 EXEC SP_HELPSTATS 'SalesOrderDetail_NoStats', 'ALL'

是的,在估计执行计划是,SQL Server在productID 列创建了统计信息来帮助优化器选择正确的执行计划。我们来看看这个统计信息的详情。

1 DBCC SHOW_STATISTICS('dbo.SalesOrderDetail_NoStats', '_WA_Sys_00000005_7E6CC920') 

在统计信息头,我们注意到Rows Sampled 值比Rows值小。这是因为在自动创建统计信息时,SQL Server没有扫描整个表,只扫描了表的样本。为什么会这样在接下来的文章里会谈到。简而言之,在非聚集索引字段里,统计信息帮助优化器在每一步操作时判断估计行数,什么样的连接是合适的,还有在计划中的执行顺序。