Multiple Indexes vs Multi-Column Indexes

问题

What is the difference between creating one index across multiple columns versus creating multiple indexes, one per column?

Are there reasons why one should be used over the other?

For example:

Create NonClustered Index IX_IndexName On TableName
(Column1 Asc, Column2 Asc, Column3 Asc)

Versus:

Create NonClustered Index IX_IndexName1 On TableName
(Column1 Asc)

Create NonClustered Index IX_IndexName2 On TableName
(Column2 Asc)

Create NonClustered Index IX_IndexName3 On TableName
(Column3 Asc)

回答1

I agree with Cade Roux.

This article should get you on the right track:

One thing to note, clustered indexes should have a unique key (an identity column I would recommend) as the first column. Basically it helps your data insert at the end of the index and not cause lots of disk IO and Page splits.

Secondly, if you are creating other indexes on your data and they are constructed cleverly they will be reused.

e.g. imagine you search a table on three columns

state, county, zip.

you sometimes search by state only.
you sometimes search by state and county.
you frequently search by state, county, zip.

Then an index with state, county, zip. will be used in all three of these searches.

If you search by zip alone quite a lot then the above index will not be used (by SQL Server anyway) as zip is the third part of that index and the query optimiser will not see that index as helpful.

You could then create an index on Zip alone that would be used in this instance.

By the way We can take advantage of the fact that with Multi-Column indexing the first index column is always usable for searching and when you search only by 'state' it is efficient but yet not as efficient as Single-Column index on 'state'

I guess the answer you are looking for is that it depends on your where clauses of your frequently used queries and also your group by's.

The article will help a lot. :-)

评论:

@jball Am I missing something here? It looks like the article is mostly about the differences between SQL Server version limitations. Could the article have been moved?

– Ian R. O'Brien

Jan 29, 2013 at 14:40

回答2

Yes. I recommend you check out Kimberly Tripp's articles on indexing.

If an index is "covering", then there is no need to use anything but the index. In SQL Server 2005, you can also add additional columns to the index that are not part of the key which can eliminate trips to the rest of the row.

Having multiple indexes, each on a single column may mean that only one index gets used at all - you will have to refer to the execution plan to see what effects different indexing schemes offer.

You can also use the tuning wizard to help determine what indexes would make a given query or workload perform the best.

Two single-column indexes vs one two-column index in MySQL?

问题

I'm faced with the following and I'm not sure what's best practice.

Consider the following table (which will get large):

id PK | giver_id FK | recipient_id FK | date

I'm using InnoDB and from what I understand, it creates indices automatically for the two foreign key columns. However, I'll also be doing lots of queries where I need to match a particular combination of:

SELECT...WHERE giver_id = x AND recipient_id = t.

Each such combination will be unique in the table.

Is there any benefit from adding an two-column index over these columns, or would the two individual indexes in theory be sufficient / the same?

回答1

If you have two single column indexes, only one of them will be used in your example.

If you have an index with two columns, the query might be faster (you should measure). A two column index can also be used as a single column index, but only for the column listed first.

Sometimes it can be useful to have an index on (A,B) and another index on (B). This makes queries using either or both of the columns fast, but of course uses also more disk space.

When choosing the indexes, you also need to consider the effect on inserting, deleting and updating. More indexes = slower updates.

回答2

A covering index like:

ALTER TABLE your_table ADD INDEX (giver_id, recipient_id);

...would mean that the index could be used if a query referred to giver_id, or a combination of giver_id and recipient_id. Mind that index criteria is leftmost based - a query referring to only recipient_id would not be able to use the covering index in the statement I provided.

Please note that some older MySQL versions can only use one index per SELECT so a covering index would be the best means of optimizing your queries.