ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 翻译:SQL Server 2005中的覆盖索引

翻译:SQL Server 2005中的覆盖索引

原创 Linux操作系统 作者:bigholy 时间:2008-12-05 10:37:31 0 删除 编辑

原文地址:http://www.sql-server-performance.com/articles/per/benchmark_covering_indexes_p1.aspx
覆盖索引是非聚集索引的一种,它包括了
SELECTJOINWHERE查询语句中引用的所有列的数据,由于SQL Server不需要查询表中的实际数据,因而减少了逻辑或物理I/O操作,从而提高了查询性能。

判断覆盖索引是否有助于提高查询性能的一种方法是在SQL Server 2005 管理器中创建一个图形查询执行计划,来看看正在执行的计划中是否存在书签查找。简单地说,书签查找通知查询处理器需要从表或聚集索引中查询所需要的行列信息,而不是直接从非聚集索引中读取数据,虽然它减少了查询性能,但是也产生了额外的磁盘I/O操作。

书签查找是这样一种机制:它将非聚集索引行导航到包含聚集索引的基表的实际数据行中去,当处理大数据量的行时,其操作将是非常巨大的。当请求数目较小的行时,SQL Server优化器试图使用位于该列上的非聚集索引或在WHERE语句中指定的列来获取请求的数据;如果从不包括有非聚集索引列请求数据,SQL Server必须从包含这些列的数据页读取,它并不关系该表是否存在聚集索引与否,而查询仍将从表或聚集索引的表来读取数据。

避免书签查找的一种方法是创建覆盖索引,该方法使得来自包含非聚集索引查询中的所有列均可以直接使用,意味着书签查询操作是不必要的,减少了磁盘I/O操作,有助于提高查询的性能。

影响

由于索引包括了查询中引用的所有列,因而覆盖索引提高了查询性能,另外,SQL Server可以利用位于索引页级的数据项来执行聚合运算,这就是说SQL Server不需要去找到实际的数据执行该运算,显然提高了性能。

虽然覆盖索引提高了查询性能,但也降低了插入、删除、更新的速度,这是因为对于这些操作来说需要额外的工作来维护覆盖索引,显然这并不是个问题,除非数据库对于插入、删除、更新这些操作很频繁。

上面介绍了关于覆盖索引的性能的利与弊之后,下面通过一些方法来逐一讲解:

1.  没有索引的性能测试

2.  非聚集索引的性能测试

3.  覆盖索引的性能测试

方法

本节我们来对上面三种索引条件来进行测试,首先执行以下脚本:

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查询时所带来的表扫描、索引扫描或书签查询.因此我们选择测试表的基数为200万。

在执行以下查询时,需要注意查询执行计划、执行时间、CPU开销和I/O开销。查询执行计划说明了SQL Server查询优化器运行一个某个查询的机制,它为找出查询比较慢的SQL提供了依据。稍后我们对每一种情况的执行计划进行分析,并找出它们的性能是怎样的。执行时间是用于度量执行一条查询所需要的时间,其时间越小代表性能最好。当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)

最后,这里应用了覆盖索引。正如覆盖索引的定义,我们需要对查询中的所有列应用索引,因此要使索引覆盖到OrderNo,ItemCode,QtyPrice列,如下所示的:

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

重新执行上面的查询,再次注意其性能参数。对于以上情况执行下面的INSERT语句:

INSERT INTO OrderDetails
VALUES (
     3124567,
     '123456',
     1,
     0.35,
     'N')

注意:在每次操作后执行CHECKPOINTDBCC DROPCLEANBUFFERSDBCC DROPCLEANBUFFERS命令用于将SQL Server数据缓冲区的数据清空,需要提醒的是这个命令仅清理缓冲区,而不是“脏”页。正因为这一点,在运行这条命令之前,需要首先运行CHECKPOINT命令,运行此命令将数据缓冲区中的所有“脏”数据页写入磁盘。

测试结果

以下是对每一个查询进行测试的执行计划数据:执行时间单位是秒,CPU开销和I/O开销是需根据执行计划来计算和度量。

测试分析

Case 1:没有索引

由于表中没有索引,显而易见获取数据的方式只能是执行“表扫描”(扫描整张表,逐行获取匹配查询条件的记录)

Case 2: 非覆盖索引

现在添在两个非聚集索引:一个是ItemCode,一个是OrderNo。这一次,查询优化器使用一个叫“RID查询”来获取数据。“RID查询”是在堆表中使用“行标识符”的一种书签查询。参数列包含了表中用来查询行的书签名称。正如下图所示的,RID查询在整个开销中占据了51%

不过,由于使用了索引,此次脚本执行花费了4秒,相比之前的提高了300%,CPU的开销也降低了81%,I/O开销降低了64%。

但是,INSERT语句则需要更多的时间和资源。

Case 3: 覆盖索引

由于覆盖索引包含了查询中的所有信息,SQL Server获取数据的速度显然更快,需要较少的资源,另外,执行计划也变得相对简单。

采用了覆盖索引,SELECT查询的执行计间减少到3秒,当与Case 1的结果相比而言,性能提升了400%,而非聚集索引提升了75%。

总结:

  从以上的测试可以知道,覆盖索引有利也有弊,因此需要根据情况进行选择。

 

1.JPG

2.JPG

3.JPG

4.JPG

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/355374/viewspace-506235/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-11-08

  • 博文量
    43
  • 访问量
    84451