MS SQL索引列的排序顺序是否重要？

Question

我有一个数据库，我运行分析器和数据库顾问来提高数据库的性能。到目前为止，效果很不错，顾问向我展示了许多可以创建的索引和统计信息，以获得更好的性能。 但是，在阅读语句时，我遇到了这个：

CREATE NONCLUSTERED INDEX [_dta_index_xinfobaseacl_11_397426127__K3_K7_K1_K5] ON [dbo].[xinfobaseacl] 
(
    [lviewid] ASC,
    [lparentid] ASC,
    [lid] ASC,
    [xlactor] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

CREATE NONCLUSTERED INDEX [_dta_index_xinfobaseacl_11_397426127__K7_K5_K1_K3] ON [dbo].[xinfobaseacl] 
(
    [lparentid] ASC,
    [xlactor] ASC,
    [lid] ASC,
    [lviewid] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]
go

同一张表，同样的列和参数，只是列的排序方式不同...

所以我很困惑，创建索引时排序顺序真的很重要吗？

- YvesR

Answer 1

是的，复合索引中列的顺序很重要。

对于所有4个列的等值谓词，它们都能够很好地发挥作用。

但第一个还将支持查询，如下：

WHERE lviewid =  1 AND lparentid 1 AND lid = 1
ORDER BY xlactor

第二个将支持查询，例如

WHERE parentid =  1 AND xlactor = 1
ORDER BY lid, xlactor

是否应该创建两个索引取决于您的工作负载。您是否向 DTA 提供了包括数据修改语句在内的代表性工作负载？

- Martin Smith

---

这实际上意味着，我们必须查找所有源代码，以确定WHERE语句中的使用是否按照索引的正确排序顺序，以防止相同内容的多个索引，我是对的吗？由于此项工作是由顾问创建的，因此我们可能会在WHERE语句中使用不同排序顺序的相同内容进行查询。 - YvesR

如果错误排序使用的索引不存在，他不会使用其他现有的索引，对吗？ - YvesR

@YvesR - 如果你的第一个评论是询问WHERE语句中谓词的顺序是否需要与索引定义中的顺序相同，那么答案是“不需要”。因为lviewid是第一个索引中的初始列，而在第二个索引中是最后一列，所以没有三列的子集可以使得这两个索引都能够查找。因此，除非在4个列上使用等值谓词，否则它们通常会支持不同的查询。 - Martin Smith

那我就在想，为什么顾问会提供两个索引呢？因为我们的源代码肯定不会对这个表使用ORDER BY查询，只是类似于“EXISTS（SELECT * FROM xinfobaseacl WHERE ...”这样的东西。 - YvesR

“ORDER BY”只是一个例子。它建议将其作为关键列而不是包含列，因此我猜测DTA并不仅仅是为了覆盖目的而将其包含在内。例如，第一个索引还支持“MAX（xlactor）... WHERE lviewid = 1 AND lparentid 1 AND lid = 1”。或者也许这只是DTA的冗余建议。如果我不知道您的查询，那么我无法判断。 - Martin Smith

好的，对我来说最重要的是如果使用不同的排序顺序，则WHERE语句使用索引，所有其他内容似乎都是微调。 - YvesR

Answer 2

您混淆了术语。排序不会产生太大的影响，但索引中列的顺序可能很重要。

复合索引的正确列顺序取决于您的查询，但一般来说，具有最少不同值的列应该放在第一位。

- podiluska

---

虽然复合索引中列的顺序确实会影响索引的整体排序，也许这就是他们的意思。 - Martin Smith

@MartinSmith 可能是这样。不过，在给出的例子中，所有列的排序顺序都是相同的。 - podiluska

第一个按 [lviewid] ASC,[lparentid] ASC, [lid] ASC, [xlactor] ASC 排序，第二个按 [lparentid] ASC, [xlactor] ASC, [lid] ASC, [lviewid] ASC 排序。 - Martin Smith

在这个例子中，我们没有使用任何ORDER BY，只是使用WHERE语句来检查记录的存在，因为这个表代表了访问控制列表功能。所以顾问发现在同一列上使用了两个不同的WHERE语句，只是排序方式不同，看起来... - YvesR