2007-05-24 11:44 | 只看楼主
树型| 收藏| 小 中 大 1

并非所有的索引天生平等

并非所有的索引天生平等

--王成辉翻译整理，转贴请注明出自微软BI开拓者www.windbi.com
--原帖地址

如果你对索引有很多的经验，那么你可能已经熟悉聚集索引和非聚集索引的区别。但本篇文章不讲这个。本篇文章是关于SQLServer查询优化器是否会使用你精心创建的索引。也许你不了解这个，但是只是因为一列上有索引并不意味着查询优化器会使用它。你可以想像，索引不被使用是浪费时间的，甚至在最糟的情况下，它会降低你应用程序的性能。让我们来看看这是为什么。

现在开始，让我们看看一个简单的例子。假定我们有一个accounting数据库。里面有一个表Orders，该表有很多不同的列，而我们只关心两列：Orderid和employeeid。该表有150000行并在employeeid列（原文里这里是表，可能是笔误）上有一个非聚集索引。现在让我们运行下面的查询：

SELECT orderid FROM orders WHERE employeeid = 5

对于查询注意到的第一件事就是查询的WHERE子句里用到的列employeeid上有一个非聚集索引。因此，当通过查询优化器查询运行查询时，你最可能会认为查询优化器将使用这个索引来产生所请求的结果。

不幸的是，你不能自动的作出这个假设。仅仅因为有一个可用的索引并不意味着查询优化器会使用它。这是因为查询分析器在使用一个索引前总是评估它是否有用。如果查询分析器检查一个索引发现它没有用，它会忽略这个索引。如果需要，它会执行一个表扫描来产生所请求的结果。

那么什么是有用的索引呢？为了回答这个问题，我们需要明白，查询优化器最大的一个潜在目的就是减少I/O数量，以及执行查询所花费的相应时间。换句话说，查询优化器评估查询能够执行的很多不同方法，然后找出它认为会产生最少I/O数量的一个。但是令人惊异的是，使用一个可用的索引并不总是意味着使用最少的I/O数量。在大多数情况下，特别是有一个非聚集索引，表扫描比可用的索引会产生更少的I/O。

在查询优化器使用一个索引之前，查询优化器评估索引看其是否有足够的可选择性。这是什么意思？可选择性是指查询返回的行数和表的行数的一个百分比。如果返回的行数很少，查询被认为具有高可选择性。通常说来，如果查询返回的行数少于表行数的5％，则具有高可选择性，索引更可能会被使用。如果查询返回表行数的5％－10％，索引也许会、也许不会被使用。如果查询返回表行数的10％以上，索引更可能不会被使用。如果对于查询没有其他有用的索引，将执行一个表扫描。

让我们回头看看这个查询例子：

SELECT orderid FROM orders WHERE employeeid = 5

仅仅通过查看这个查询我们并不能知道employeeid上的索引是否可用。话说回来，我们知道表有150000行，“employeeid = 5”的行有5000。如果用5000除以150000得到3.3％。由于3.3％少于5％，查询优化器将更可能使用这个索引。但如果“employeeid = 5”的行有25000行，这样25000除以150000得到16.6％。由于16.6％大于5％，甚至大于10％，查询优化器将更可能使用表扫描来代替索引的使用。

那么一个表扫描怎么能使用比索引（比如例子中的非聚集索引）更少的I/O呢？如果索引具有高可选择性，特别是如果只返回1行，那么非聚集索引作用很大。但如果要返回很多记录，那么索引不具有可选择性，返回数据的I/O会很高。这是因为查询优化器不得不先根据索引定位数据（用尽I/O），然后根据表返回数据（更多的I/O）。在某些时候，查询优化器确定扫描整个表所花费的I/O比在索引和表之间往返以返回请求的行所花费的I/O要少。

上面给出的例子适用于大部分非聚集索引。如果可用的索引是聚集索引，那么索引会被使用，即使它的可选择性很低，因为索引就是表并且I/O操作很有效。

那么查询优化器怎么知道一个可用的索引选择性足够有用呢？这通过维护每个表上的每个索引的统计来完成。索引统计是存储在sysindexes表里的一个柱状图的值。这些统计是可用行的一个抽样，以近似的告诉查询优化器一个特定索引的可选择性是多少。

每当索引创建、重建、运行UPDATE STATISTICS命令或查询优化器需要时自动创建创建索引统计。索引统计不会实时的维护，因为那样会带给服务器很多的负荷。但因为索引统计不是实时的，它们可能会过期，所以有时候查询优化器会因为索引统计不是最新的而作出差的选择。

但仅仅因为统计是最新的也不意味着查询优化器会使用一个可用的索引。记住，查询优化器基于索引的可选择性作出决定，而它使用索引统计来决定可选择性。


所以如果查询优化器能检查看看一个特定索引是否有用，我们怎么能做相同的事情呢？幸运的是，有一个命令能让我们检查一个索引，并发现这个特定的索引是否有足够的可选择性去使用。

我们要知道一个索引是否有足够的可选择性是因为如果它没有，那么它不会被使用。如果一个索引不被使用，那么它的存在就没有意义了。删除不必要的索引最有可能提升你应用程序的性能，因为你可能知道，由于维护索引的开销会降低对表的INSERT、UPDATE、DELETE的性能。如果正被讨论的表在一个频繁更高的数据库里，那么索引维护会引起一些瓶颈。所以我们的目的是确保如果有一个索引，它就要有足够的可选择性以便使用。我们不要维护不被使用的索引。

我们将使用下面的命令来找出一个索引的可选择性：

DBCC SHOW_STATISTICS (table_name, index_name)

当运行这个命令时，它输出类似下面的结果。这是我维护的一个数据库的真实结果。
索引in_tran_idx的统计：

Updated              Rows    Rows Sampled  Steps  Density      Average key length
-------------------- ------- ------------  ------ ------------  ------------------
Feb 24  2001 3:36AM  7380901 7163688      300    2.2528611E-5  0.0

(1 row(s) affected)

All density Columns
------------------------
2.2528611E-5 in_tran_key

Steps
-----------
1
283
301
340
371
403
456
…
44510

(300 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

这个结果包含了大量的信息，大多数超过了本文的范围。我们关注的一个焦点是列“All density”的密度值“2.2528611E-5”。

密度是指一个索引重复行的平均百分比。如果一个索引列，如employeeid，有很多重复数据，那么称索引有很高的密度。但如果索引列有很多唯一的数据，那么称索引有很低的密度。

密度跟可选择性是相反的关系。如果密度是一个很高的数值，那么可选择性就很低，这意味着索引不会被使用。如果密度是一个很低的数值，那么可选择性就很高，索引更可能被使用。

在上面例子的输出结果里，索引的密度小于1％。反过来，这意味着可选择性高于99％，即索引可能对于查询优化器来说很有用。

如果你是一个高级DBA，你可能已经注意到我过分简单了。虽然那样，我在本文里的观点仍然很正确，我的观点是，并非所有的索引都是平等的。因为索引对查询优化器可用并不意味着会被使用。

对DBA来说，这意味着你要了解你表的索引。你可以运行DBCC SHOW_STATISTICS命令一段时间，看看你索引的实际可选择性如何。你也许会发现你的一些索引没被使用。如果有这种情况，你要考虑将它们删除，这反过来会加速你的应用程序。

对于新的DBA来说，移除而不是添加索引似乎对于调优数据库性能是后退了一步。但你对SQLServer内部运行了解得越多，你会更理解使用索引调优你的应用程序的局限性。
虽有智慧,不如乘势;虽有鎡基,不如待时。
君子学以聚之,问以辨之,宽以居之,仁以行之。
独学而无友，则孤陋而寡闻。