Eliminate KeyLookup and RIDLookupPart2: Index intersection and index Join

The following message is displayed in the MSDN query optimization suggestion today: SQLServer automatically considers the index intersection and can use multiple indexes for the same table in the same query (which may be different from everyone's understanding ). Before explaining this, let's take an example: useAdventureWorksgoselectsoh. * fromsales. SalesOrderHeaderAS

The following message is displayed in the MSDN query optimization suggestion today: SQL Server automatically considers the index intersection and can use multiple indexes for the same table in the same query (which may be different from everyone's understanding ). Before explaining this, let's take a look at an example: use AdventureWorks go select soh. * from sales. SalesOrderHeader

The following message is displayed in the MSDN query optimization suggestion today: SQL Server automatically considers the index intersection and can use multiple indexes for the same table in the same query (which may be different from everyone's understanding ).

Let's look at an example before explaining it:

UseAdventureWorks

Go

Select soh .*

From sales. SalesOrderHeaderASsoh

WHERE soh. SalesPersonID = 276

And soh. OrderDatebetween '2014/1/0/2/0/2'

View the execution plan:

Although we have created a non-clustered index for SalesPersonID, SQL Server is not used because OrderDate is not included in the index. I believe that at this time, most people will add an OrderDate column on the index. In fact, there can be another way to add a new index without changing the current index. In this way, SQL Server can use multiple indexes to complete this query. This process is the index intersection.

Now we create an index on OrderDate:

Create nonclusteredindex [ix_orderdate] ON [Sales]. [SalesOrderHeader] ([OrderDate] ASC)

After adding an index, we will see the following execution plan:

This time, SQL Server uses two Non-clustered index seek, obtains the index intersection of the two subsets, and obtains all output fields through Keylookup. we usually think that a query for a table does not use multiple indexes. In fact, the SQL Server Optimization engine can use multiple indexes.

The above test provides us with an inspiration that sometimes you do not have to use one wide index column (multiple index key values) to improve performance, or you can use multiple narrow index keys to improve performance. In addition, if you find that the index does not cover the query conditions, but you cannot directly change the index, adding another index can also meet your requirements.

Index join is a variant of the Index intersection. If a query can directly obtain the required data from the Index, it is called Index join (we can see that the data still needs to be obtained from Key Lookup, index cannot provide all data ).

For more information, see:

Http://msdn.microsoft.com/zh-cn/library/ms188722 (v = SQL .105). aspx

Http://msdn.microsoft.com/zh-cn/library/aa226170 (v = SQL .70). aspx