SQL Query performance optimization Resolution Bookmark Lookup _mssql

Let's look at what bookmarks look for:

When the optimizer chooses a nonclustered index that contains only part of the query request, a lookup (lookup) is required to retrieve other fields to satisfy the request. A key lookup for a table with a clustered index (key lookup) is a RID lookup (RID lookup) for a heap table. This lookup is--bookmark lookup.

The bookmark lookup reads the data from the table based on the row locator of the index. Therefore, in addition to the logical reading of index pages, the logical reading of data pages is required.

This cost is addressed by the additional overhead of reading data from an indexed row locator to a table.

Let's take a look at my test table structure:

It can be seen that there is a clustered index Pk_userid and a nonclustered index ix_username.

Look at the effect of creating a bookmark lookup:

Select Username,gender from dbo. UserInfo where username= ' userN600 '

As you can see from the above SQL generation execution plan, a bookmark lookup (Key lookup) is generated, as shown below



If you rewrite the above SQL to

Select UserName from dbo. UserInfo where username= ' userN600 '


You can see that the bookmark lookup is gone.

The main reason for this SQL-generated bookmark lookup is that the SQL Optimizer chooses nonclustered index ix_username to hold the SQL. The Ix_username index does not contain Gender This field then produces a lookup that is a bookmark lookup from the index to the datasheet.
Resolve Bookmark Lookup:

method One, using a clustered index

For clustered indexes, the leaf page of the index is the same as the data page of the table, so when reading the value of the clustered index key column, the data engine can read the values of the other columns without any row positioning, which resolves the bookmark lookup.
For this SQL (select Username,gender from dbo. UserInfo where Username= ' userN600 ' solves the bookmark lookup by building a clustered index on the UserName because one table has only one clustered index, which means that deleting an existing clustered index (PK_USERID) will result in other forms The foreign key constraint in is to be changed, which requires some work and may seriously affect other queries that depend on the existing clustered index.

method Two, using an overlay index

The overlay index is a nonclustered index that is established in all the columns required to satisfy the SQL query without reaching the base table. If a query encounters an index and does not need to refer to the underlying datasheet at all, the index can be considered an overlay index.
For this SQL (select Username,gender from dbo. UserInfo where Username= ' userN600 ' solves the bookmark lookup by including the Gender field in the nonclustered index ix_username.
That is, use the Include statement when the index is being built, as follows

Use include best in the following situations:
1, do not want to increase the size of the index key, but still can build a coverage index;
2. To index a type of data that cannot be indexed (except text, ntext and images);
3, the maximum number of keyword columns that have exceeded an index

method Three, using an index connection

An index connection uses an index cross between multiple indexes to completely overwrite a query. If the overlay index becomes very wide, you can consider an index connection.
For this SQL (select Username,gender from dbo. UserInfo where username= ' userN600 ' and gender=1 can build a nonclustered index on the Gender.
For this example, it is possible that the SQL optimizer did not choose the nonclustered index ix_username and our newly built index on gender, when we could tell the SQL optimizer to use both indexes at the same time, as follows
Select Gender,username from UserInfo with (index (ix_gender,ix_username)) where username= ' Jins ' and gender=0

All right, just write that much.