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