When SQL Server creates a composite index, the composite index column order affects the performance of the query

Talk about composite indexes

Write index blog Too much, has not wanted to write, there are two reasons:
One is the suspicion that there are fried leftovers, brothers have said: Index, as long as the query criteria to build the index on the line, really can be so violent?
Second, the index is a very big topic, it is difficult to summarize all the situation, you do not make a point of novelty, but there is a copy of the suspicion of copying

Since it's written, write something a little bit different,
All right, stop the crap.
/*

20160814 Note: A similar article was found today: http://www.cnblogs.com/fly_zj/archive/2012/08/11/2633629.html;
Can be understood as: when you add a composite index, the equality operation field should be placed at the front
But that is not entirely accurate, it should be said that the high selectivity of the field, put in the front, popularly said is, will be the most effective filter conditions, the way composite index of the first bit

*/

Build a test environment:

Create a table that simulates a table in a real business, fills it with data, fills the time field evenly, and populates the other fields with GUIDs

Create table businessinfotable (    BuniessCode1 varchar),    BuniessCode2 varchar,    BuniessCode3 varchar (+),    BuniessCode4 varchar (+),    BuniessStatus1 tinyint,    BuniessStatus2 tinyint,    BuniessDateTime1 datetime,    BuniessDateTime2 datetime,    OtherColumn1 varchar (),    OtherColumn2 varchar ( ),    OtherColumn3 varchar (DECLARE) @i int=0while @i<1000000begin    insert INTO businessinfotable     Values     (        NEWID (), NEWID (), NEWID (), NEWID (), RAND () *100,rand () *100,        DATEADD (Mi,@i,getdate ()), DATEADD (Mi,@i,getdate ()), NEWID (), NEWID (), NEWID ()    )    set @[email protected]+1end

Now there is a query (in fact the query is far more complicated than this, simplify a little, do not deliberately create the environment)

    Select OtherColumn2,           BuniessStatus1,           BuniessStatus2,           BuniessDateTime1,           BuniessDateTime2    From Businessinfotable     where  BuniessDateTime1 between ' 2016-6-21 ' and ' 2016-6-28 ' and        BuniessDateTime2 Between ' 2016-6-21 ' and ' 2016-6-28 ' and        BuniessStatus1    =    and        BuniessStatus2    =    66

Note one point:

The clustered index is not considered for the time being, after all, there can be only one clustered index on a table.
No one else is a fool, not easy to build a clustered index, and the clustered index is already occupied
Since is occupied, the principle is generally not to move other people's existing things, such as others built a clustered index, you have to delete, according to their own situation to build a clustered index, this is not to find *

Experienced you must consider qualifying the index, taking into account the search for bookmarks to avoid key lookup, we have asked for the OtherColumn2 column include in the
Like this.

CREATE nonclustered INDEX idx_1 on businessinfotable (buniessstatus1,buniessstatus2,buniessdatetime1, BuniessDateTime2) INCLUDE (OTHERCOLUMN2)

Or so, just the index leading column order is different

CREATE nonclustered INDEX idx_2 on businessinfotable (BUNIESSDATETIME1,BUNIESSDATETIME2,BUNIESSSTATUS1, BUNIESSSTATUS2) INCLUDE (OTHERCOLUMN2)

Of course you can adjust the order of four columns, I do not too much to do the demonstration, interested in their own try
The order of the leading columns here does not affect the use of the index, when the query is non-clustered index seek, absolute
So the question is, exactly the same query condition, the same as the result, using a different index, the difference between the index is only the column order is not the same, the cost of the same, there is a difference?

The same query, using the results of different indexes (Idx_1 and Idx_2, respectively):
Look at the picture below.

Look at the IO situation

Cause Analysis:

Seems to be a little different, good seems that the difference is really not small.
What is the reason?
The index is present in the form of a balanced tree (b-tree), and the order of the columns of the composite index determines the order in which the information of the B-tree is stored
If BuniessStatus1 is listed as the leading column, because the range of the BUNIESSSTATUS1 distribution (relative) is smaller,
This can filter out a relatively small result set by buniessstatus1=55 at the time of query, then filter with other conditions in order is relatively fast.
For example, buniessstatus1=55 filtered out to meet the conditions of the data there are 5, plus BuniessStatus2 BuniessDateTime1 BuniessDateTime2 These three conditions to filter again, come out a piece of data.
If BUNIESSDATETIME1 is the leading column of the index, filter with BuniessDateTime1 between ' 2016-6-21 ' and ' 2016-6-28 ', there may be 10,000 data,
Then use BUNIESSDATETIME2,BUNIESSSTATUS1, BuniessStatus2 filter, and finally, there is only one qualifying data.
The difference is that: at the beginning of the filter conditions, determine how many page queries to determine the initial criteria to meet the data, and then further filtering
If the data range that satisfies the query condition is determined relatively accurately at the very beginning, the data that satisfies the condition can be finally confirmed by a relatively small price.
If the data range that satisfies the query condition is determined relatively vaguely at first, the cost of the process is relatively large, although the result is the same as the one behind each condition.
Of course, the establishment of this index is related to the distribution of data, but there is no conclusion that the composite index must be in what order to be the best
Or that sentence: concrete analysis of specific problems, to avoid empiricism, there is no one-cut means to solve all the problems.

Summarize:

In this paper, a simple example is given to analyze the effect of the order of columns on the query when creating a conforming index.
Note that when creating an index, you should not only consider which columns to create indexes on, but also whether the order of indexed columns will affect the query.
Avoid talking about indexing, which is the "Index on query criteria" approach to violence.

When SQL Server creates a composite index, the composite index column order affects the performance of the query