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

SQLServer2005覆盖索引基准

SQLServer2005覆盖索引基准

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

什么是覆盖索引？

覆盖索引是非聚集复合索引的一种形式，它包括在查询里的Select、Join和Where子句用到的所有列。因此，索引包含了查询正在查找的数据，SQLServer不必查找表的实际数据，减少了逻辑和（或）物理I/O，从而提升性能。

帮助确定一个覆盖索引是否有助于查询性能的一个方法是在SQLServer2005的企业管理器里为讨论的查询创建一个图形化的查询执行计划，看看是否执行了任何书签查找（Bookmark Lookup）。本质上，书签查找告诉你查询处理器不得不从表或者聚集索引里查找需要的行列，而不是能直接从非聚集索引里读取。书签查找能降低查询性能因为它们产生了额外的磁盘I/O来返回列数据。

书签查找是从非聚集索引行到基表（或聚集索引）里的实际数据行的一个导航机制，并且当在处理大量行的时候开销很大。当查询要求很少的行时，SQLServer优化器试图使用列或包含在WHERE子句的列上的非聚集索引来返回查询需要的数据。如果查询从列上要求的数据不包含在非聚集索引里，SQLServer必须返回到包含那些列的数据的数据页。不论表是否有没有聚集索引，查询将仍然不得不返回到表或聚集索引去获取数据。

避免书签查找的一种方法是创建覆盖索引。这种方法查询的所有列都直接在非聚集索引里可用，这意味着书签查找是不必要的，这就减少了磁盘I/O，从而提升了你查询的性能。

更多的信息请看这里。


矛盾

覆盖索引可以用来提升查询性能，因为索引包含了所有查询里的列。非聚集索引为表里的每一行用索引键值来存储一行。另外，SQLServer能使用索引页级里的这些行来执行聚集计算。这意味着SQLServer不必去实际的表执行聚集计算，这样可以提升性能。

虽然覆盖索引能提升获取数据的性能，但它们也能降低INSERT、UPDATE和DELETE查询的性能。这是因为维护覆盖索引要求做一些额外的工作。通常这不是问题，除非你的数据库经常遭受非常高的INSERT、UPDATE和DELETE。你也许不得不在你的产品系统上应用覆盖索引之前去实验看看它们是否在提升性能方面上比影响性能方面更有帮助。

虽然引入覆盖索引对性能问题既有积极也有消极的影响，正如上面讨论的，本文将着重讨论在SQLServer2005下运行查询时在下面的条件下找出将要发生的事：
  • 没有任何索引的性能
  • 有非聚集索引的性能
  • 有覆盖索引的性能
方法

在这一节里，我们看一看怎样测试上面的三个索引条件。首先，创建一个表格式如下：

CREATE TABLE [dbo].[OrderDetails](
    [OrderNo] [int] NOT NULL,
    [ItemCode] [varchar](50) NOT NULL,
    [Qty] [int] NULL,
    [Price] [float] NULL,
    [Status] [char](1) NULL
) ON [PRIMARY]

然后，向表OrderDetails里插入数据。表需要有大量的数据以便SELECT语句没有覆盖索引时强迫使用表扫描、索引扫描或书签查找，这将导致很长的足够的时间以便能更好的比较结果。所以我们测试用多于2000000的数据。

我们将执行下面的查询并注意查询执行计划、执行时间、CPU成本和I/O成本。查询执行计划大概描述了SQLServer查询优化器怎样实际运行（或将要运行）一个特定查询的。当去找出一个特定查询运行为什么慢时这个信息是很有价值的。我们将为每种情形分析执行计划并分析性能是怎样增加或减少的。执行时间是执行查询花费的时间，执行时间越少，性能就越好。当你有很少的I/O或CPU成本，这表明使用了很少的服务器资源，标志这较好的性能。

下面是将用来贯穿整个测试过程的查询。选择这个查询以便它将使用前面所说的书签查找。

SELECT OrderNo,
    ItemCode,
    Qty,
    Price
FROM dbo.OrderDetails
WHERE ItemCode = 'A2-K137-FF1931'
    AND (OrderNo BETWEEN 250000 and 300000)

第一个测试看看表上没有索引时的性能。

然后，接下来创建两个非聚集索引，如下：

CREATE NONCLUSTERED INDEX IX_Order_Details_ItemCode ON OrderDetails (ItemCode)
GO
CREATE NONCLUSTERED INDEX IX_Order_Details_OrderNo ON OrderDetails (OrderNo)

最后，使用覆盖索引。按照覆盖索引的定义，我们不得不为查询里的所有列创建索引。所以要在表OrderDetails的列OrderNo、ItemCode、Qty和Price上创建索引，如下：

CREATE NONCLUSTERED INDEX IX_Order_Details_Coverindex ON OrderDetails (
    OrderNo,
    ItemCode,
    Qty,
    Price)

然后将重新执行查询并再次注意当存在覆盖索引时获取数据上面的提到的性能参数。

对于上面的每一个情形，将执行下面的INSERT语句，并返回同样的数据。

INSERT INTO OrderDetails
VALUES (
    3124567,
    '123456',
    1,
    0.35,
    'N')
注：每个操作之后将执行CHECKPOINT和DBCC DROPCLEANBUFFERS，这将从缓存中清楚数据。DBCC DROPCLEANBUFFERS命令用来在各个测试之间为了公平的测试从SQLServer数据缓存（buffer）清除所有的测试数据。记住这个命令只用来清除干净的缓存，而不清除脏的缓存。因此，在运行DBCC DROPCLEANBUFFERS命令之前，你可以先运行CHECKPOINT命令。运行CHECKPOINT将把所有的脏缓存写入磁盘。然后当你运行DBCC DROPCLEANBUFFERS时，你可以确定所有的数据缓存被清除，而不只是干净的缓存。

结果

下面是从每个查询的执行计划里收集的结果。执行时间单位是秒，CPU成本和I/O成本从执行计划里的计算值除以测量值。对于上面提到的相同的SELECT查询和INSERT查询的3种情形的所有这些参数的值都记录了下来。


SELECT

INSERT

Without any Indexes
With Non-Covering Index
With Covering Index
Without any Indexes
With Non-Covering Index
With Covering Index
Execution Time (Sec.)

12

4

3

1

4

5

CPU Cost

2.339937

0.4381462

0.312734

0.000001

0.000003

0.000004

I/O Cost

7.64016

2.6854338

1.12757

0.01

0.03

0.04



分析情形1：没有任何索引表上没有索引，除了执行表扫描没有其他的办法来返回数据。查询将一行一行的遍历整个表来取得和查询条件匹配的记录。



不用说，这是从表里获取数据最没效率的方法。你可以看到它花了12秒来获取数据。

情形2：有非聚集索引现在给表的两列ItemCode和OrderNumber添加两个非聚集索引。此时，查询优化器使用RID LOOKUP来获得信息。RID LOOKUP是堆上书签查找使用的代理行标识符（RID）。参数列包含用来在表里查找行的书签标签和查找的行所在的表的名字。正如你从下图看到的，RID LOOKUP花了整个成本的50％。



然而，由于使用了索引，这次脚本执行了4秒，比第一种情形快了300％。CPU成本也减少了81％，I/O成本也减少了64％。

然而，INSERT语句现在比表上没有索引的时候花费了更多的时间和资源。

情形3：有覆盖索引接下来是测试有覆盖索引的情形。由于覆盖索引包含了查询里所有的信息，SQLServer将使用更少的资源来更快的返回数据。另外，使用覆盖索引，你不会看到那么复杂的执行计划。



使用覆盖索引，SELECT查询的执行时间已经减少到3秒。当你和不使用任何索引进行比较时，你可以看到它提升了400％，和非聚集索引相比也性能上提升了75％。CPU成本和I/O成本也得到改善，这意味着引入覆盖索引之后，SELECT查询使用了更少的资源。

象前面的情形一样，INSERT花费了更多的时间，还有额外的资源。你可以看到INSERT语句执行时间上升为5秒了，而相比表上没有索引时的时间为1秒。

结论

由于上面的统计建议，覆盖索引既有优势也有劣势。觉得优势是否超过劣势，执行覆盖索引对你特定需求是否是最好的，这是你作为DBA的工作。
虽有智慧,不如乘势;虽有鎡基,不如待时。
君子学以聚之,问以辨之,宽以居之,仁以行之。
独学而无友，则孤陋而寡闻。