SQL Server评价索引之有效性

以下的文章主要描述的是SQL Server评价索引之有效性（Evaluating Index Usefulness），SQL Server数据库所提供的索引主要有2个原因：***是作为一种保证数据库表中数据唯一性的方法；其二，提供了一种快速访问表中数据的方法。

创建合适的索引是数据库物理设计时最为重要的方面之一。因为你不能在一个表上无限制地创建索引，而且不管怎么说，它也是不可行的。所以，你将想在一些具有高选择性（high Selectivity ）的列上创建索引，这样，查询时系统将会利用这些索引。一个SQL Server评价索引的选择性定义如下：

引用

选择率 = （唯一索引值的个数）/ (表中所有行数)

Selectivity ratio = (Number of unique index values)/ (Total number of rows in the

如果选择率高——也就是说，大量行都可以用索引键值来唯一标识——那么该SQL Server评价索引就具有高选择性，即对优化器来说也是有用的。***的选择性是1，即每一行都有一个唯一的索引键值。低选择性意味着表中有许多重复的键值，这样的索引将很少有用。

SQL Server优化器基于索引的选择性来决定对一个查询是否使用索引。越高的选择性，SQL Server检索结果集（Result set）就越快和越有效。

例如，你正在对authors 表中的索引的有效性进行评估。假如大多数查询是以author’s last name或者state来进行访问的。因为大量的并发用户会修改该表的数据，你只允许一个索引——author’s last name或者state，你将会选择谁？让我们进行一些分析来判断哪个索引更有效些，或者更有选择性。首先，利用一个查询来确定pubs数据库中 author表的last name列的有效性：

Sql代码

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   
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创建的SQL Server评价索引将具有高选择性，也是一个好的候选索引。除了一行外，其余所有行的last name值都唯一。现在，来分析state列的选择性：

Sql代码

select count(distinct state) as '# unique',   
count(*) '# rows',   
str(count(distinct state) / cast (count(*) as real),4,2) as 'selectivity'   
from authors   
go   
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列的SQL Server评价索引选择率（0.35）比au_lname索引选择率要低很多，将不太有用。

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

Sql代码

select state, count(*)   
from authors   
group by state   
order by 2 desc   
go   
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之间有多少行？等等诸如此类的问题，优化器是从SQL Server评价索引统计（Index Statistics）中获得这些信息的。

【编辑推荐】