Optimize SQL query performance to solve bookmarks

Let's take a look at what bookmarks are:

When the non-clustered index selected by the optimizer only contains a part of the query request fields, a lookup is required to retrieve other fields to meet the request. For a table with clustered indexes, it is a key lookup, and for a heap table it is an RID lookup ). This kind of search is-bookmarks search.

Bookmarksearch reads data from the table based on the index row locator. Therefore, in addition to the logic reading of the index page, the logic reading of the data page is also required.

There will be some additional overhead between the row Locator of the index and reading data from the table. This article will solve this overhead.

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

It can be seen that there is a clustered index PK_UserID and a non-clustered index IX_UserName.

Look at the effect of creating a bookmarked search:

Select UserName, Gender from dbo. UserInfo where UserName = 'usern600'

According to the preceding SQL Execution Plan, a Key Lookup is generated, as shown in figure



If you rewrite the preceding SQL statement

Select UserName from dbo. UserInfo where UserName = 'usern600'


We can see that the bookmarks are missing.

The main reason why this SQL statement generates a bookmarked query is that the SQL optimizer selects the non-clustered index IX_UserName to execute the SQL statement. The IX_UserName index does not contain the Gender field, so a query from the index to the data table is generated, that is, a bookmarkup query.
Solve bookmarks:

Method 1: Use a clustered Index

For clustered indexes, the leaf page of the index is the same as the data page of the table. Therefore, when you read the value of the clustered index key column, the data engine can read the values of other columns without any row location, which solves the bookmarked search.
For this SQL statement (select UserName, Gender from dbo. userInfo where UserName = 'usern600 ') to solve the problem of bookmarking, you can create a clustered index on UserName, because a table has only one clustered index, this means that deleting the existing clustered index (PK_UserID) will cause other foreign key constraints in the table to be changed. This requires some relevant work, it may seriously affect other queries dependent on existing clustered indexes.

Method 2: Use a covered Index

Covering indexes is a non-clustered index created for all columns that meet SQL queries and do not need to reach the basic table. If you encounter an index and do not need to reference the underlying data table at all, the index can be considered as a overwriting index.
For this SQL statement (select UserName, Gender from dbo. UserInfo where UserName = 'usern600 '), the solution for bookmarking is to include the Gender field in the IX_UserName of the non-clustered index.
That is, use the INCLUDE statement when creating the index. The specific operations are as follows:

It is best to use INCLUDE in the following situations:
1. You do not want to increase the size of the index key, but you can still create a Covering Index;
2. It is intended to index a data type that cannot be indexed (except text, NTEXT, and image );
3. the maximum number of keyword columns for an index has been exceeded.

Method 3: Connect using Indexes

An index connection uses an index crossover between Multiple indexes to completely overwrite a query. If the covered index becomes very wide, you can consider the index connection.
For this SQL statement (select UserName, Gender from dbo. UserInfo where UserName = 'usern600 'and Gender = 1), you can create a non-clustered index on Gender.
In this example, the SQL Optimizer may not select the non-clustered index IX_UserName and the index we created on the Gender at the same time, in this case, we can tell the SQL optimizer to use these two indexes at the same time. The operation is as follows:
Select Gender, UserName from UserInfo with (index (IX_Gender, IX_UserName) where UserName = 'jins' and Gender = 0

Well, write so much.