Remove key lookup and rid lookup Part1: use include Index

In the Execution Plan, we often see keylookup and ridlookup operations, and the cost is very large. What are key lookup and rid lookup:

 

Ridlookup is a bookmarked query on the stack using the provided row identifier (RID ).

 

KeylookupThe operator is used to search for bookmarks on tables with clustered indexes.

 

The difference is thatKey LookupYou can use the clustered index key value for search. The RID lookup is based on the heap row identifier (fileid: pageid: slotnumber), because additional Io is required to complete the query, therefore, these two operations are resource-consuming.

 

Sqlserver 2005 provides the include index to help eliminate rid lookup andKey lookup.

 

Let's perform a test:

 

Useadventureworks

Go

Select [sod]. [productid],

[Sod]. [orderqty],

[Sod]. [unitprice]

From [sales]. [salesorderdetail] sod

Where [sod]. [productid] = 897

 

Execution Plan:

 

Because the index [ix_salesorderdetail_productid] only contains [productid] columns, you cannot directly obtain [orderqty] and [unitprice]. Therefore, you need to use clusterindex to find the data of these two columns, the key lookup operation (98% cost) is generated ).

 

Next I will modify [ix_salesorderdetail_productid] and add the include [orderqty] and [unitprice] columns.

 

Create nonclusteredindex [ix_salesorderdetail_productid] on [sales]. [salesorderdetail]

(

[Productid] ASC

)

Include ([orderqty],

[Unitprice])

 

Execute again to generate a new execution plan. We only see the indexseek operation:

 

Using include index has the following advantages:

· Re-design non-clustered indexes with a large index key size, so that only the columns used for search and search are key columns. Make all other columns that overwrite the query into non-key columns. In this way, all the columns required for the query will be overwritten, but the index key itself is small and efficient.

· Include non-key columns in non-clustered indexes to avoid exceeding the current index size limit (the maximum number of key columns is 16 and the maximum index key size is 900 bytes ). The Database Engine calculates the number of index key columns or index key size hours, regardless of Non-key columns.

 

Because the inculde field is not treated as the index key, you canDecreaseIndex level,Query Io is also reduced accordingly(It has a great impact on performance) and can also reduce the storage space. Next, let's take a test to see the impact of the index key value on the index level.

 

1. Create two tables first. The indexlevel_small ID int type is the primary key (the key value is very small), and The indexlevel primary key ID is the bytes type (the length is 900, the maximum number of bytes for index operation ).

 

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. Insert 100000 data entries to the distribution of the two tables:

 

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. Check the index level:

Select object_name (object_id) as tablename, index_depth, page_count, values (db_id ('fndblogtest'), object_id ('dbo. indexlevel'), null)

Union

Select object_name (object_id) as tablename, index_depth, page_count, struct (db_id ('fndblogtest'), object_id ('dbo. indexlevel_small'), null, null)

Tablenameindex_depthpage_count fragment_count

Certificate -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Indexlevel 8 54999 28607

Indexlevel_small 3 50000 195

(2 row (s) affected)

4. query records in two tables to check the IO status:

 

Set statisticsioon

Go

Select *
From DBO. indexlevel
Where id = 'zookeeper without zookeeper regions'

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.

We can see that it takes 8 logical Io operations to search for a record in a table with an index key value of 900, while a table with the primary key type needs only three logical Io operations, if the query data volume is large, the performance gap is obvious. SQL Server 2000 can only use compositeindex (all columns must be used as index pages) to solve this problem.
The 2005 include index has much better performance than compositeindex.

 

For how to create an include index, see create indexes withincluded columns.

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

 

There is another way to achieve the same effect, refer:Eliminate key lookup and rid lookup Part2: Index intersection and index join

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