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

最新推荐文章于 2024-04-01 00:00:00 发布

最新推荐文章于 2024-04-01 00:00:00 发布 · 446 阅读

标签

#SQL Server #SQL #Go

数据库技术专栏收录该内容

26 篇文章

订阅专栏

本文探讨了SQL Server中索引的重要性及如何选择具有高选择性的索引。通过对作者表的lastname和state列进行分析，解释了选择率的概念及其对查询性能的影响。

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

[quote]选择率 = （唯一索引值的个数）/ (表中所有行数)
Selectivity ratio = (Number of unique index values)/ (Total number of rows in the table)[/quote]

如果选择率高，——也就是说，大量行都可以用索引键值来唯一标识——那么该索引就具有高选择性，即对优化器来说也是有用的。最佳的选择性是1，即每一行都有一个唯一的索引键值。低选择性意味着表中有许多重复的键值，这样的索引将很少有用。SQL Server优化器基于索引的选择性来决定对一个查询是否使用索引。越高的选择性，SQL Server检索结果集（Result set）就越快和越有效。

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

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）中获得这些信息的。

欲知后事如何，且听下回分解，Index Statistics。