索引统计(Index Statistics)

正如前面提到的，键的选择性是决定当执行一个查询时是否使用索引的重要因素。SQL Server在系统表sysindexs的statblob字段中存储了键的选择性和样本直方图的值。查询优化器正是基于索引键对应于该列中的值和查询中的SARG，来决定使用哪个索引。

Statblob列是一个image类型列，为了看到存储在该列中的统计信息，可使用DBCC SHOW_STATISTICS命令，该命令返回下列信息：

[list]
[*]一个直方图。它包含了索引键的第一列的偶数个样本值。SQL Server在直方图中至多存储200个样本值。
[*]索引中的组合列的索引密度。索引密度表明了索引键的唯一性，本节随后将讨论。
[*]计算统计信息时表中行数。
[*]用于抽样生成统计信息的行数。
[*]直方图中存储的样本值的个数。
[*]键的平均长度值。
[*]统计计算的日期和时间。
[/list]

DBCC SHOW_STATISTICS语法如下：

DBCC SHOW_STATISTICS (tablename, index)

Listing 34.1显示了authors表中的在au_lname和au_fname列的aunmind非聚集索引的统计信息。

Dbcc show_statistics (authors, aunmind)
Go

Statistics for INDEX 'aunmind'.
Updated Rows Rows Sampled Steps Density Average key length
-------------------- ------ ------------ ----- ---------- ------------------
Aug 6 2001 1:34AM 23 23 22 0.0 24.52174

All density Average Length Columns
------------------------ ------------------------ --------------------------
4.5454547E-2 7.3913045 au_lname
4.3478262E-2 13.52174 au_lname, au_fname
4.3478262E-2 24.52174 au_lname, au_fname, au_id

(3 row(s) affected)

RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS
----------------- ------------ ------------ -------------------- --------------
Bennet 0.0 1.0 0 0.0
Blotchet-Halls 0.0 1.0 0 0.0
Carson 0.0 1.0 0 0.0
DeFrance 0.0 1.0 0 0.0
del Castillo 0.0 1.0 0 0.0
Dull 0.0 1.0 0 0.0
Green 0.0 1.0 0 0.0
Greene 0.0 1.0 0 0.0
Gringlesby 0.0 1.0 0 0.0
Hunter 0.0 1.0 0 0.0
Karsen 0.0 1.0 0 0.0
Locksley 0.0 1.0 0 0.0
MacFeather 0.0 1.0 0 0.0
McBadden 0.0 1.0 0 0.0
O'Leary 0.0 1.0 0 0.0
Panteley 0.0 1.0 0 0.0
Ringer 0.0 2.0 0 0.0
Smith 0.0 1.0 0 0.0
Straight 0.0 1.0 0 0.0
Stringer 0.0 1.0 0 0.0
White 0.0 1.0 0 0.0
Yokomoto 0.0 1.0 0 0.0

分析上面的输出，你能推算出统计最后的修改时间是2001年8月6日。当生成计信息时该表共有23行（Rows），所有23行都用来抽样生成统计信息(Rows Sampled)。键值的平均长度为24.52174字节（Average Key Length）。根据密度信息(Density)，你能看到该索引具有高选择性（低密度意味着高选择性——索引密度后面将涉及到）。表中23行数据，其中22行具有唯一值。
在概述信息和索引密度之后，显示了索引直方图。

[size=medium][b]索引直方图（The Statistics Histogram）[/b][/size]

直方图中至多可存储200个样本值。每个样本值称为一个step。保存在每个step中样本值是值的范围的端点。每个step保存了3个值，分别描述为：

[list]
[*]EQ_ROWS——与样本值相同的行数。换句话就是该step中重复值的个数。
[*]RANG_ROWS——表示除了当前值外，介于当前step和前一个step之间其他值的行数。
[*]Rang Density——表示在该范围内有多少个不同的值。范围密度信息实际上有两个单独的列组成，分别为：DISTINCT_RANGE_ROWS 和AVG_RANG_ROWS。
[*]DISTINCT_RANGE_ROWS表示除了当前值外，当前step与前一个step之间具有多少个不同值的个数。
[*]AVG_RANGE_ROWS在该step范围内，每个不同值的平均行数。
[*]
[/list]

在listing34.1的输出中，索引中第一列的所有不同键值的值作为样本值存储在直方图中，所以，直方图中的样本值之间没有值(RANG_ROWS)，其后所有的范围值为0。你可能注意到在last name 为Ringer的索引键值上有一个重复值（EQ_ROWS = 2）。为了更好比较，Listing34.2显示了bigpubs2000数据库中的sales表的DBCC SHOW_STATISTICS信息片段。

Listing 34.2 DBCC SHOW_STATISTICS Output for the titleidind Index on the sales Table in the bigpubs2000 Database
Statistics for INDEX 'titleidind'.
Updated Rows Rows Sampled Steps Density Average key length
-------------------- ------ ------------ ----- ------------ ------------------
Aug 21 2001 11:18PM 168725 168725 200 1.8955356E-3 26.405577

(1 row(s) affected)

All density Average Length Columns
------------------------ ------------------------ -----------------------------
1.8621974E-3 6.0 title_id
5.997505E-6 10.0 title_id, stor_id
5.9268041E-6 26.405577 title_id, stor_id, ord_num

(3 row(s) affected)

RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS
------------ ---------- ------- ------------------- -------------------
BI0194 0.0 314.0 0 0.0
BI2184 613.0 343.0 2 306.5
BI2574 270.0 277.0 1 270.0
BI3224 618.0 286.0 2 309.0
BI3976 311.0 293.0 1 311.0
BI6450 673.0 300.0 2 336.5
BI9506 947.0 292.0 3 315.66666
BU1111 296.0 299.0 1 296.0
BU7832 349.0 334.0 1 349.0
CH0249 1011.0 311.0 3 337.0
CH0639 984.0 307.0 3 328.0

...

TC4203 0.0 321.0 0 0.0
TC7777 0.0 297.0 0 0.0
(200 row(s) affected)

从这个例子你可以看出，每个范围内有更多的值（RANG_ROWS），并且每个step中包含了更多的重复值（EQ_ROWS）。另外，直方图中的所有200行都被使用了，表中的168,725行记录分布在这些200行中。所有的168,725行都被用来生成统计信息(Rows Sampled)。
只有当一个常量表达式与索引列进行比较时，并且常量表达式的值在查询编译时是已知时，SARG的计算才能使用直方图的step值。直方图中的step可以为SARG使用的的例子包括：
Where col_a = getddate()
Where cust_id = 12345
Where monthly_sales < 10000 /12
Where l_name like "Smith" + "%"

有些常量表达式的直到查询运行时才能计算出来。这些查询参数中包含了局部变量或者子查询：

Where price = @avg_price
Where total_sales > (select sum(qty) from sales)
Where titles.pub_id = publishers_id

对于这些类型的表达式，你需要其他方法来估计匹配的行数。另外，因为直方图steps只记录了索引中第一列的值，当需要评估组合索引的多列的SARG匹配的行数时，SQL Server必须使用不同方法来决定，例如下面：

Select * from sales
Where title_id = 'BI3976'
And stor_id = 'p648'

当直方图没有被使用或者不能使用时，SQL Server使用索引密度值来估计匹配的行数。

[b][size=medium]索引密度（Index Densities）[/size][/b]

当一个查询的SARG 的值直到查询运行时才已知，或者SARG是关于一个索引的多列时，SQL Server才使用为索引中每列存储的密度值。对于组合键值，SQL Server为第一列的组合键存储了密度值；为第一列和第二列；为第一、二、三列；等等。这些信息可以从Listing34.1的DBCC SHOW_STATISTICS 输出信息的All density区域看到。
索引密度表示为键的唯一键值的倒数。每个键的密度可以按照下面的公式进行计算：

[quote]Key density = 1.00/ ( Count of distinct key values in the table)
键密度 = 1.00 / （表中的不同键值数）[/quote]

所以，pubs数据库的author表中state列的密度计算公式如下：

Select Density = 1.00/ (select count (distinct state) from authors)
Go

Density
----------------
.1250000000000

State和zip的组合列密度计算如下：

Select density = 1.00/( select count (distinct  state + zip) from authors)
Go

Density
----------------
.0555555555555

注意，不像选择率，越小的索引密度意味着具有更高的索引选择性。当密度趋近于1，索引就变得有更少的选择性，基本上没有用处了。当索引的选择性低的时候，优化器可能会选择一个表扫描（table scan），或者叶子级的索引扫描（Index scan），而不会进行索引查找（index seek），因为这样会付出更多的代价。

[quote][size=medium]提示：[/size]
当心你的数据库中低选择性的索引。这样的索引通常是对系统的性能是一个损害。它们通常不仅不会用来进行数据的检索，而且也会使得数据修改语句变得缓慢，因为需要额外的索引维护。识别这些索引，考虑删除掉它们。
通常，当你给键中添加更多的列时，密度值应该变得更小。例如，在Listing 34.2，密度值逐渐变小。[/quote]

Key Column Index Density
title_id 1.8621974E-3
title_id, stor_id 5.997505E-6
title_id, stor_id, ord_num 5.9268041E-6

[b][size=medium]使用索引密度评估行数（Estimating Rows Using the Index Statistics）[/size][/b]

那么优化器是如何使用索引密度来决定一个索引的效果呢？
当在一个范围内查找一个索引值或者键中存在重复值时，SQL Server会使用直方图信息。考虑下面关于bigpubs2000数据库中的sales表中查询：

Select * from sales
Where title_id = 'BI2184'

因为在表中title_id中存在重复值，SQL Server使用关于title_id的直方图（参考Listing34.2）来估计匹配的行数。对于BI2184值，它将查看EQ_ROWS值，值为343.0。这表示在表中title_id值为BI2184的记录共有343行。

当一个查询参数（search argument）的精确匹配（exact match 即等号计算）在直方图中step没有发现时，SQL Server使用比查找值（search value）大的下一个step中的AVG_RANG_ROWS值。例如，SQL Server对查找值为‘BI2187’进行评估，它将会发现匹配值为270.0行。

对一个范围检索，SQL Server把检范围两端的RANG_ROW和EQ_ROWS相加。例如，利用Listing34.2中的直方图，如果查找参数为 where title_id <= 'BI2574',行数估计将是：
314 + 613 + 343 + 270 + 277，或者为1817。

当直方图不能使用时，SQL Server就使用索引密度来估计匹配行数。对于等值查找的计算公式是直截了当的，例如：

Declare @tid varchar(6)
Select @tid = 'BI2574'
Select  count(*) from sales where title_id = @tid

行估计值等于指定键值的索引密度（1.8621974E-3）乘以表中行数：

Select  count(*) * 1.8621974E-3
From sales
Go

-------------------
314.19925631500001

如果一个查询的SARG为title_id 和stor_id，并且假如title_id的SARG是一个可在优化期间可评价的常量表达式，SQL Server会用title_id stor_id的索引密度和title_id的直方图来估计匹配的行数（对某些值来说，索引密度估计的值可能会大学直方图估计出来的值）。SQL Server 将会用二者中较小的值作为匹配的行数。
根据title_id stor_id的索引密度，你能看到：

Select coun(*) * 5.997505E-6
From sales

-----------------------------------------------------
1.011929031125
在这个例子中，SQL Server将用title_id 和stor_id的索引密度来估计匹配的值。在此情况下，它估计查询将返回一条匹配的行。

[b][size=medium]生成和维护索引统计[/size][/b]

现在，你也许会问“入户创建索引统计，并且如何维护他们？”当你在一个表上创建一个索引时候索引统计就会第一次被创建，或者当你运行UPDATE STATISTICS 命令时。在7.0以前的版本中，索引统计信息不会自动更新。如果当索引创建之后，你插入许多行，那么反映索引统计的直方图信息不会反映实际的键值分布。结果，优化器有时选择了一个低效率的执行计划。作为一个常规的日常委会工作，DBA只好创建一个schedule运行UPDATE STATISTICS来保持索引统计的及时更新。7.0以后的版本，索引统计会由SQL Server来自动更新。SQL Server不断监视有关索引键的更新活动，并在合适的情况下通过内部的进程来更新统计信息。