SQL Server: multiple index for the same columns pe

问题:

I have a table Person with columns personId, teamId, departmentId, among others. Most of the query use a combination of these columns on the where.

Example

Select * from .. where personId = 2 and departementId = 1
Select * from .. where personId = 2 and teamId = 1   
Select * from .. where departmentId = 2 and teamId = 1   

My question is, should I create an index for each of these column individually?

回答1:

The quick answer is yes - just add an index for each column. Its not likely to be the most optimal solution but in most cases it won't be that far off and it probably won't cause any harm unless you already have many indexes on that table.

The only slightly longer answer is that you should test your query against representative data - The SQL Server Database Engine Tuning Advisor can suggest indexes for you, but only you can check to make sure that these indexes are suitable for other all queries (including inserts / updates) - you need to balance the performance of reads against the cost of maintaining those indexes when writing to the database (as well as any storage / space constraints).

回答2:

Either one per column: SQL Server will use Index Intersection

Or, try something like this: three composite indexes. The first column of each is useful as a "single column index" too.

• departmentId, teamId, personId
• personId, departmentId, teamId
• teamId, personId, departmentId

Notes:

• WHERE clause order doesn't matter
• SELECT * is bad

Also, it's a good idea to have foreign key columns indexed and either strategy will work

回答3:

I would not, as a rule, create 3 indexes on the variations of field usage, but that is just a general rule.

As far as the "how do I do this as a newbie" answer, I would create a workload and use the tuning advisor. It is not an end all solution, and as someone learns more, they get beyond the wizard, but it is a good place to start with. Make sure you have a decent representative sample, as indexes can destroy performance on other queries, if done incorrectly.