消除Key Lookup和RID Lookup Part1:使用Include Index

在执行计划中我们经常会看到KeyLookup和RIDLookup操作，而且Cost很大，具体什么是Key Lookup和RID Lookup：

 

RIDLookup是在使用提供的行标识符(RID) 在堆上进行的书签查找

 

KeyLookup运算符是在具有聚集索引的表上进行的书签查找

 

区别是 Key Lookup通过聚集索引键值进行查找，RID Lookup是通过堆的行标识符（FileID:PageID:SlotNumber）查找,由于都需要额外的IO完成查询，所以这两个操作都是很耗费资源的。

 

SQLServer 2005提供了Include索引可以帮助消除RID Lookup和Key Lookup。

 

下面我们做个测试：

 

useAdventureWorks

go

SELECT [sod].[ProductID],

[sod].[OrderQty],

[sod].[UnitPrice]

FROM [Sales].[SalesOrderDetail] sod

WHERE [sod].[ProductID]= 897

 

执行计划：

 

因为索引[IX_SalesOrderDetail_ProductID]只包含了[ProductID]列，无法直接获得[OrderQty]和[UnitPrice]，所以需通过Clusterindex找到这两列数据，就会产生Key Lookup的操作（98% cost）.

 

下面我修改[IX_SalesOrderDetail_ProductID]，增加Include [OrderQty]和[UnitPrice]列。

 

CREATE NONCLUSTEREDINDEX[IX_SalesOrderDetail_ProductID]ON [Sales].[SalesOrderDetail]

(

[ProductID] ASC

)

INCLUDE( [OrderQty],

[UnitPrice])

 

重新执行，产生新的执行计划，我们只看到IndexSeek操作：

 

使用Include Index有以下优点：

·重新设计索引键大小较大的非聚集索引，以便只有用于搜索和查找的列为键列。使覆盖查询的所有其他列成为非键列。这样，将具有覆盖查询所需的所有列，但索引键本身较小，而且效率高。

· 将非键列包含在非聚集索引中，以避免超过当前索引大小的限制（最大键列数为 16，最大索引键大小为 900字节）。数据库引擎计算索引键列数或索引键大小时，不考虑非键列。

 

由于不将Inculde字段当做索引键处理，可以减少Index的层级，查询IO也相应减少（对性能影响很大），同时也可以降低存储空间。 下面我们通过一个测试来看一下索引键值对索引层级的影响。

 

1.首先创建两张表，IndexLevel_Small ID Int型为主键（键值很小），IndexLevel主键ID为字符型（长度900，索引运行的最大字节数）。

 

CREATE TABLE [dbo].[IndexLevel_Small](

[ID] [int] NOT NULL,--Primary Key

[Name][varchar](3000)NULL)

CREATE TABLE [dbo].[IndexLevel](

[ID][varchar](900)NOTNULL,--PrimaryKey

[Name][varchar](3000)NULL,

) on primary

 

2.对两张表分布插入100000条数据：

 

DECLARE @ID ASVARCHAR(900)

DECLARE @NAME ASVARCHAR(3000)

DECLARE @INT ASINT

set @INT = 1

WHILE @INT <100000

BEGIN

SET @ID = REPLICATE('A',880)+CONVERT(varchar(10),@INT)

set @INT+=1

INSERT INTO dbo.IndexLevelvalues(@ID,REPLICATE('a',3000))

END

DECLARE @ID ASVARCHAR(900)

DECLARE @NAME ASVARCHAR(3000)

DECLARE @INT ASINT

set @INT = 1

WHILE @INT <100000

BEGIN

SET @ID = REPLICATE('A',880)+CONVERT(varchar(10),@INT)

set @INT+=1

INSERT INTO dbo.IndexLevelvalues(@ID,REPLICATE('a',3000))

END

 

3.查一下索引级别：

SELECT OBJECT_NAME(object_id)as TableName,index_depth,page_count,fragment_countFROMsys.dm_db_index_physical_stats(DB_ID('FNDBLogTest'),OBJECT_ID('dbo.IndexLevel'),null,null,null)

union

SELECT OBJECT_NAME(object_id)as TableName,index_depth,page_count,fragment_countFROMsys.dm_db_index_physical_stats(DB_ID('FNDBLogTest'),OBJECT_ID('dbo.IndexLevel_Small'),null,null,null)

TableNameindex_depthpage_count fragment_count

--------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------

IndexLevel 8 54999 28607

IndexLevel_Small 3 50000 195

(2 row(s)affected)

4. 在两张表中查询记录，看IO状况：

 

set statisticsioon

go

select * from dbo.IndexLevel where ID = 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA1'

go

set statisticsiooff

go

(1 row(s)affected)

Table'IndexLevel'. Scancount 0, logical reads 8,physicalreads 0, read-ahead reads 0, lob logical reads 0, lob physical reads0, lobread-ahead reads 0.

set statisticsioon

go

select * from dbo.IndexLevel_Small whereID= 2

go

set statisticsiooff

go

 

(1 row(s)affected)

Table'IndexLevel_Small'.Scan count 0, logicalreads 3,physical reads 0, read-ahead reads 0, lob logical reads 0, lobphysical reads 0,lob read-ahead reads 0.

  

我们看到索引键值为900的表查找一条记录需要8次逻辑IO，而字符型为主键的表只需要3次逻辑IO，如果查询数据量大的话性能差距就很明显了。由于SQL Server 2000中只能用compositeindex（所有的列都需要作为索引页）解决这个问题，所以SQLServer 2005 的Include index相比于compositeindex性能要好很多。

 

如何创建Include Index参考：Create Indexes withIncluded Columns

http://msdn.microsoft.com/en-us/library/ms190806.aspx

 

还有另外一种办法也可以达到相同的效果，参考:消除Key Lookup和RID Lookup Part2:索引交集和索引Join

http://blog.csdn.net/smithliu328/article/details/7835497