SQL Server为一列创建多个非聚集索引，而在一个索引中只有多个列 [英] SQL Server creating multiple nonclustered indexes for one column vs having multiple columns in just one index

问题描述

假设我有下表

• UserID(身份)PK
• UserName-唯一非空
• UserEmail-唯一非空

• UserID (Identity) PK
• UserName - unique non null
• UserEmail - unique non null

推荐什么以获得最佳性能?

What is recommended for the best performance?

• 分别为UserName和UserEmail创建非聚集索引

• creating non clustered index for UserName and UserEmail separately

OR

• 只需包含两个列

请分享您的想法，为什么一个人比另一个人更可取.

Please do share you thoughts why one is preferable over other.

推荐答案

要考虑的另一个重要点是:只有在引用了最左边的n列时，才会使用复合索引(由多列组成)(例如在WHERE子句中).

Another important point to consider is this: a compound index (made up of multiple columns) will only be used if the n left-most columns are being referenced (e.g. in a WHERE clause).

因此，如果您有一个复合索引

So if you have a single compound index on

(UserID, UserName, UserEmail)

然后在以下情况下可能使用此索引 :

then this index might be used in the following scenarios:

• 单独搜索UserID时(仅使用最左边的1列-UserID)
• 搜索UserID和UserName时(使用最左边的2列)
• 搜索所有三列时

• when you're searching for UserID alone (using just the 1 left-most column - UserID)
• when you're searching for UserID and UserName (using the 2 left-most columns)
• when you're searching for all three columns

但是，该单一复合索引将永远不会用于搜索

But this single compound index will never be able to be used for searches on

• 正好UserName-它是索引中的第二列，因此该索引无法曾经被使用
• 只是UserEmail-它是索引中的第三列，因此该索引无法曾经被使用

• just UserName - it's the second column in the index and thus this index cannot ever be used
• just UserEmail - it's the third column in the index and thus this index cannot ever be used

请记住这一点-仅仅因为一列是索引的一部分并不一定意味着该索引将支持并加快对单个列的搜索！

Just remember this - just because a column is part in an index doesn't necessarily mean that searching on that single column alone will be supported and sped up by that index!

因此，如果您的使用模式和应用程序确实确实需要单独在UserName和/或UserEmail上进行搜索(不提供其他搜索值)，那么您必须在这些列上创建单独的索引-仅一个化合物就不会完全没有任何好处.

So if your usage patterns and your application really need to search on UserName and/or UserEmail alone (without providing other search values), then you must create separate indices on these columns - just a single compound one will not have any benefit at all.

这篇关于SQL Server为一列创建多个非聚集索引，而在一个索引中只有多个列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！