是否需要使用与声明相同的顺序使用多个列SQL Server索引?

When I declare a clustered index, specifying: column1, column2 and column3 in this order - do I need to use the columns in that same order?

当我声明一个群集索引时，以这个顺序指定:column1、column2和column3——我需要以同样的顺序使用这些列吗?

For example, will this use the clustered index mentioned earlier to update multiple rows:

例如，这是否将使用前面提到的聚集索引来更新多个行:

 UPDATE Table1     
   WHERE column3 = 1 
      AND column2 = 1 
      AND column1 = 1

2 个解决方案

#1

The order you use declare the items in the Where clause, as you have stated, should not make a difference as to whether the database server is able to use an index which covers those columns.

您使用的顺序声明Where子句中的项，如您所声明的，不应该影响数据库服务器是否能够使用包含这些列的索引。

#2

It's true that when you're checking for exact equality, that order does not matter.

确实，当你检查完全相等时，这个顺序不重要。

But that's not to say that the order in the index does not matter -- perhaps this is what your co-worker was trying to say. For example, if I have a table:

但这并不是说索引中的顺序不重要——也许这就是你的同事想要说的。例如，如果我有一张桌子:

PersonID  FName    LName
--------  -------  -----
1         John     Smith
2         Bill     Jones
3         Frank    Smith
4         Jane     Jackson
...
(assume a significantly large table)

and I define an index on it in the order (LName, FName), that index will necessarily perform differently than an index defined in the order (FName, LName), depending on what the query is.

我在它上按顺序(LName, FName)定义一个索引，该索引的性能必然与按顺序(FName, LName)定义的索引不同，这取决于查询是什么。

For example, for the query: SELECT * FROM People WHERE LName = 'Smith', you will most likely get a better plan for the first type of index than for the second type.

例如，对于查询:从LName = 'Smith'的用户中选择*，您很可能会得到第一种索引类型比第二种索引类型更好的计划。

Likewise, SELECT * FROM People WHERE FName = 'John' will perform better with the second index structure over the first.

同样，从FName = 'John'使用第二个索引结构优于第一个索引结构的人员中选择*。

And SELECT * FROM People WHERE FName = 'John' AND LName = 'Smith' will perform identically no matter what order the index is created.

并且从FName = 'John'和LName = 'Smith'的人员中选择*，无论创建索引的顺序是什么。

#1