SQL Server：具有相同列性能的多个索引 [英] SQL Server: multiple index for the same columns performance

问题描述

我有一个人表，列 personId，teamId，departmentId 等等。大多数查询都使用这些列的组合。

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.

示例

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?

推荐答案

快速回答是肯定的 - 只需为每列添加一个索引。它不太可能是最理想的解决方案，但在大多数情况下它不会那么遥远，它可能不会造成任何伤害，除非你已经在该表上有很多索引。

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.

唯一稍微长一点的答案是您应测试您的查询代表性数据 - SQL Server数据库引擎优化顾问可以为您建议索引，但只有您可以检查以确保这些索引适合其他所有查询（包括插入/更新） - 在写入数据库时​​（以及任何存储/空间限制），您需要平衡读取的性能与维护这些索引的成本。

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).

这篇关于SQL Server：具有相同列性能的多个索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！