列以相反顺序定义的 2 个索引之间的差异

Question

以下两个指标有什么区别吗？

• IDX_IndexTables_1
• IDX_IndexTables_2

如果有的话，有什么区别？

create table IndexTables (
    id int identity(1, 1) primary key,
    val1 nvarchar(100),
    val2 nvarchar(100),
)

create index IDX_IndexTables_1 on IndexTables (val1, val2)
GO

create index IDX_IndexTables_2 on IndexTables (val2, val1)
GO

Answer 1

多列索引在概念上与获取所有列字段并将它们连接在一起没有什么不同——将结果索引为单个字段。

由于索引是 B 树，因此它们总是从左到右搜索。您必须从左侧开始搜索，以便在向右移动时将结果配对，以便索引完成其工作并提供有用的结果。

仅对单个字段建立索引：

WHERE val1 LIKE 'myvalue%' (uses index)
WHERE val1 LIKE '%myvalue' (cannot use index)

相同的概念适用于多列索引：

当 order 为 val1,val2

WHERE val1='value1' (uses index)
WHERE val2='value2' (cannot use index)

当 order 为 val2,val1

WHERE val1='value1' (cannot use index)
WHERE val2='value2' (uses index)

如果两个字段完全匹配，则在这种情况下索引的顺序并不重要。

WHERE val1='value1' AND val2='value2' (uses index in any order)

Answer 2

你拥有的是一个综合索引。当 WHERE 子句未使用复合索引中的所有列时，顺序很重要。

考虑这个查询：

SELECT val1
FROM IndexTables
WHERE val1 = 'MyValue'

为了了解可能考虑使用什么索引，请从左到右读取复合索引中的列。如果在读取查询中的所有列之前查询中不存在该列，则不会使用索引。

IDX_IndexTables_1 (val1, val2)：从左到右读取 val1 存在，并且它是我们唯一的列，因此该索引将被视为

IDX_IndexTables_2 (val2, val1)：从左读取右边的 val2 在此查询中不存在，因此不会使用它。

Answer 3

前面的答案描述了如何使用每个索引的第一列。 （在 where 子句中）。

我认为指出第二列很有用也很重要，因为它可能会提高涉及第二列的查询的性能。

以下查询将仅通过 IDX_1 上的索引查找来完成，从而保存对基表的有价值的查找（因为 val2 已经是索引的一部分）。

SELECT val2 from IndexTables where val1 = @someVal1

同样，反向索引将优化此查询：

SELECT val1 from IndexTables where val2 = @someVal2

但是，只需要两个索引中的一个（无论是哪一个）来优化以下查询：

SELECT val1, val2 from IndexTables where val1 = @someVal1 and val2 = @someVal2

这表明，根据您的表收到的查询，可能会出现以下情况：拥有这两个索引的正当理由。

Answer 4

其他人回答说他们是不同的，我同意。

不过，我会添加一些其他想法...

• (col1, col2) 索引意味着您不需要单独在 col1 上建立索引
• (col2, col1) 索引意味着您不需要单独在 col2 上建立索引
• 顺序很重要如果这是覆盖（例如在 col1 上的 WHERE，SELECT col2），
• 则方向（ASC/DESC）也很重要（其他问题 1，其他问题 2)

Answer 5

是的。有一个区别。

复合索引 IDX_IndexTables_1 可用于在 where 子句中使用 val1 列的任何查询。

复合索引 IDX_IndexTables_2 可用于在 where 子句中使用 val2 列的任何查询。

因此，例如，IDX_IndexTables_2 不能用于此查询（但可以使用 IDX_IndexTables_1）：

SELECT val1, val2 FROM IndexTables
WHERE val1 = some_value

但可以用于此查询：

SELECT val1, val2 FROM IndexTables
WHERE val2 = some_value AND val1 = some_other-value

考虑复合索引的方法就像考虑纸质电话簿；它按姓氏列进行索引，然后按名字列进行索引：您可以按姓氏进行查找，但不能按名字本身进行查找。