评估索引的有效性（Evaluating Index Usefulness）

评价索引的有效性（Evaluating Index Usefulness）
SQL Server提供索引主要有两个原因：其一是作为一种保证数据库表中数据唯一性的方法；其二，提供了一种快速访问表中数据的方法。创建合适的索引是数据库物理设计时最为重要的方面之一。因为你不能在一个表上无限制地创建索引，而且不管怎么说，它也是不可行的。所以，你将想在一些具有高选择性 （high Selectivity ）的列上创建索引，这样，查询时系统将会利用这些索引。一个索引的选择性定义如下：


select count(distinct au_lname) as '# unique', count(*) as '# rows', str(count(distinct au_lname) / cast (count(*) as real),4,2) as 'selectivity' from authors go
 
# unique    # rows      selectivity 
----------- ----------- ------------------------------
22        23 0.96 


author表的au_lname列的有效率计算值为0.96，表明在au_lname创建的索引将具有高选择性，也是一个好的候选索引。除了一行外，其余所有行的last name值都唯一。 现在，来分析state列的选择性：

select count(distinct state) as '# unique',       count(*) '# rows',      str(count(distinct state) / cast (count(*) as real),4,2) as 'selectivity'  from authors  go  

 
# unique    # rows      selectivity 
----------- ----------- ------------------- ----------- 
  8        23     0.35 

正如你所看到的，state列的索引选择率（0.35）比au_lname索引选择率要低很多，将不太有用。


对于这一点，你可能会问，是否因为state列中的一些值具有较高的重复性而导致了选择性的下降，或者说仅仅只有一些值具有唯一性。你可以用下面的查询来确定：

select state, count(*)   from authors  group by state  order by 2 desc  go  

 
state 
----- ----------- 
CA             15 
UT              2 
TN              1 
MI              1 
OR              1 
IN              1 
KS              1 
MD              1 



正如你所预料到的，state值，除了一个外，其余值都相对唯一。因为表中有多一半的state值都为‘CA’。所以state可能不是一个好的候选索引列，特别是假如大部分时间你都以CA来进行查询，此时，SQL Server将发现扫描整个表将比借助索引进行查询数据更有效。

一般来说，如果一个键值的选择率低于0.85，那么优化器通常会选择表扫描来处理查询。在这种情况下，使用表扫描来获取所有满足条件的数据行将比通过B-Tree来定位大量数据行来查找更有效率。

如果有更多的索引可以选择，那么SQL Server将怎样来确定每个索引是否具有选择性和到底选择哪一个索引对用户来说更有效呢？例如，SQL Server怎么知道下面的索引能够返回多少行？ select * from table  where key between 1000000 and 2000000

如果该表在0到20,000,000之间有10,000,000行记录，优化器如何知道是使用一个索引还是进行表扫描呢？如果在该范围内有10行记录，或者900,000，又如何选择？SQL Server如何来估计在1,000,000 至2,000,000之间有多少行？等等诸如此类的问题，优化器是从索引统计（Index Statistics）中获得这些信息的。

查看更多 下一篇