Difference between 2 indexes with columns defined

问题:

Are there any differences between following two indexes?

• IDX_IndexTables_1
• IDX_IndexTables_2

If there are any, what are the differences?

create table IndexTables (
    id int identity(1, 1) primary key,
    val1 nvarchar(100),
    val2 nvarchar(100),
)

create index IDX_IndexTables_1 on IndexTables (val1, val2)
GO

create index IDX_IndexTables_2 on IndexTables (val2, val1)
GO

回答1:

Yes. There is a difference.

The composite index IDX_IndexTables_1 can be used for any query where the val1 column is used in the where clause.

The composite index IDX_IndexTables_2 can be used for any query where the val2 column is used in the where clause.

So, for instance IDX_IndexTables_2 cannot be used for this query (but IDX_IndexTables_1 can be used):

SELECT val1, val2 FROM IndexTables
WHERE val1 = some_value

but can be used for this query:

SELECT val1, val2 FROM IndexTables
WHERE val2 = some_value AND val1 = some_other-value

The way to think about a composite index is think about a paper telephone directory; It is indexed by the surname column, and then the firstname column: you can look up by surname but not by firstname on its own.

回答2:

A multi-column index is conceptually no different than taking all the columns fields and concatinating them together -- indexing the result as a single field.

Since indexes are b-trees they are always searched left to right. You have to begin your search from the left to pair down results as you move to the right for the index to do its job and provide useful results.

With only a single field indexed:

WHERE val1 LIKE 'myvalue%' (uses index)
WHERE val1 LIKE '%myvalue' (cannot use index)

The same concept is applied for multi-column indexes:

When order is val1,val2

WHERE val1='value1' (uses index)
WHERE val2='value2' (cannot use index)

When order is val2,val1

WHERE val1='value1' (cannot use index)
WHERE val2='value2' (uses index)

If both fields are matched exactly order of indexes does not matter in that case.

WHERE val1='value1' AND val2='value2' (uses index in any order)

回答3:

What you have is a composite index. The order is important when your WHERE clause is not using all columns in the composite index.

Consider this query:

SELECT val1
FROM IndexTables
WHERE val1 = 'MyValue'

In order to know what index might be considered read from left to right the columns in your composite indexes. If the column doesn't exist in your query before you read all the columns in your query then the index won't be used.

IDX_IndexTables_1 (val1, val2): Reading from left to right val1 exists and it is our only column so this index would be considered

IDX_IndexTables_2 (val2, val1): Reading from left to right val2 doesn't exist in this query so it won't be used.

回答4:

The previous answers describe how to use the first column of each index. (in the where clause).

I think it's also important to point out that the second column is useful because it potentially increases performance of queries that involve the second column.

The following query will be completed with JUST an index seek on IDX_1, saving valuable lookups to the base table (since val2 is already part of the index).

SELECT val2 from IndexTables where val1 = @someVal1

Likewise, the reversed index will optimize this query:

SELECT val1 from IndexTables where val2 = @someVal2

However, only one (it doesn't matter which) of the two indexes is need to optimize the following query:

SELECT val1, val2 from IndexTables where val1 = @someVal1 and val2 = @someVal2

This shows that, depending on the queries your table receives, there may be a legitimate reason to have both indexes.

回答5:

Other folk have answered that they are different, and I agree.

I'll add some other thoughts though...

• the (col1, col2) index means you don't need an index on col1 alone
• the (col2, col1) index means you don't need an index on col2 alone
• the order matters if this is covering (eg WHERE on col1, SELECT col2)
• the direction (ASC/DESC) also matters (Other question 1, Other question 2)