你的位置:首页 > 数据库

[数据库]理解统计信息(2/6):直方图


在理解统计信息(1/6):密度里,我们讨论了在统计里存储的密度信息。这篇文章会讨论下直方图。我们再次创建SalesOrderDetail表的副本,并在上面建立2个索引。

1 USE StatisticsDB2 GO3 SELECT * INTO SalesOrderDetail FROM AdventureWorks2008r2.Sales.SalesOrderDetail4 CREATE UNIQUE CLUSTERED INDEX ix_SalesOrderDetailID ON SalesOrderDetail(SalesOrderDetailID)5 CREATE NONCLUSTERED INDEX ix_productid ON SalesOrderDetail(productid)

我们来看看非聚集索引的直方图信息。

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

你会在输出里看到200条记录,上图只显示了前18条记录。为了创建直方图,SQL Server基于索引的第一列值把数据分成不同的桶(步长)。输出结果里每条记录被称为步长。基于数据分布情况,步长的最大个数是200。直方图是你数据的统计表述(statistical representation)。换句话说,它是基于索引的第一列值的数据分布情况。直方图总是基于索引的第一列,即使索引是组合列。这也是为什么建议把经常被选到的列(most selective column)作为索引的第一列的原因,但也有例外。
我们来看直方图的输出结果。它是基于productid值把表里121317条记录分成200个桶(步长)。

RANGE_HI_KEY列表示每桶的上边界值。每桶的下边界值是上一桶的RANGE_HI_KEY+1。对于第一桶,下边界值是生成直方图列的最小值。

RANGE_ROWS列表示在那桶范围内的记录数,但不等于RANGE_HI_KEY值。第一条记录的0值标识,在整个表里没有一条记录productid值是小于707的。我们来看第11条记录,它的RANGE_HI_KEY值是718,RANGE_ROWS 列值是218。这就是说,有218条记录的productid是大于716(上一条RANGE_HI_KEY值)且小于718的。我们可以去验证下。

1 SELECT COUNT(*) FROM SalesOrderDetail WHERE productid>716 AND productid<718

EQ_ROWS列表示表里与RANGE_HI_KEY值匹配的记录数。对于第一条记录,EQ_ROWS值为3083表示表里productid值为707的记录有3083条。我们可以验证下。

1 SELECT COUNT(*) FROM SalesOrderDetail WHERE productid=707

DISTINCT_RANGE_ROWS 列表示在2个RANGE_HI_KEY值之间的不同记录数(不同productid值)。我们来看第11条的RANGE_HI_KEY值是718,DISTINCT_RANGE_ROWS列值为1,这就是说productid值大于716(上一个RANGE_HI_KEY值)且小于718只有1条不同记录。我们可以验证下。

1 SELECT COUNT(distinct productid) FROM SalesOrderDetail WHERE productid>716 AND productid<718

 AVG_RANGE_ROWS列表示每个不同值的平均记录数。如果AVG_RANGE_ROWS值大于0的话,和RANGE_ROWS / DISTINCT_RANGE_ROWS相等,不然AVG_RANGE_ROWS的值为1。

SQL Server优化器如何使用直方图来做基数预估(cardinality estimation )呢?我们看下下面查询的执行计划,点击工具栏的显示包含实际的执行计划。

1 SELECT productid FROM SalesOrderDetail WHERE productid>=716 AND productid<=718 

估计行数1513是怎么计算来的呢?我们看下直方图。

红色方框里的数字合计(1076+218+219=1513)就是执行计划里估计行数值。

1076是productid值为716的记录数。

218是productid值大于716且小于718的记录数。

219是productid值等于718的记录数。

三个值的汇总就是上述执行计划里的估计行数。

当where条件复杂时,优化器会创建称为列统计的统计信息,并在直方图数据上使用复合算法(complex algorithm)进行基数预估(cardinality estimation)。我们在下篇文章会谈论这个的更多细节。