索引中列的顺序有多重要？ [英] How important is the order of columns in indexes?

问题描述

我听说你应该在索引声明的开头处添加最具选择性的列。

I've heard that you should put columns that will be the most selective at the beginning of the index declaration.

例如：

CREATE NONCLUSTERED INDEX MyINDX on Table1
(
   MostSelective,
   SecondMost,
   Least
)

首先，是谣言正确？

如果是这样，我可能会通过重新排列索引中列的顺序看到性能差异，或者是更多的是很好做的做法？

If so, am i likely to see large differences in performance by rearranging the order of the columns in my index, or is it more of a "nice to do" practice?

我问的原因是因为在通过DTA查询之后，它建议我创建一个索引几乎所有相同的列都作为现有索引，只是以不同的顺序。

The reason I'm asking is because after putting a query through the DTA, it recommended that I create an index that had almost all of the same columns in it as an existing index, just in a different order.

我正在考虑将缺少的列添加到现有索引并将其称为好。想法？

I was considering just adding the missing columns to the existing index and calling it good. Thoughts?

推荐答案

看一下这样的索引：

Cols
  1   2   3
-------------
|   | 1 |   |
| A |---|   |
|   | 2 |   |
|---|---|   |
|   |   |   |
|   | 1 | 9 |
| B |   |   |
|   |---|   |
|   | 2 |   |
|   |---|   |
|   | 3 |   |
|---|---|   |

首先查看对A的限制，因为您的第一列消除了比首先限制第二列更多的结果？如果您想象索引必须如何遍历，第1列，第2列等等，这会更容易...您会看到在第一次通过中删除大部分结果会使第2步更快。

See how restricting on A first, as your first column eliminates more results than restricting on your second column first? It's easier if you picture how the index must be traversed across, column 1, then column 2, etc...you see that lopping off most of the results in the fist pass makes the 2nd step that much faster.

另一种情况，如果您在第3列上查询，优化器甚至不会使用索引，因为它在缩小结果集方面没有任何帮助。 只要您在查询中，在下一步之前缩小要处理的结果数量就意味着更好的效果。

Another case, if you queried on column 3, the optimizer wouldn't even use the index, because it's not helpful at all in narrowing down the result sets. Anytime you're in a query, narrowing down the number of results to deal with before the next step means better performance.

自索引以来也是以这种方式存储的，当你查询它时，索引中没有回溯来找到第一列。

Since the index is also stored this way, there's no backtracking across the index to find the first column when you're querying on it.

简而言之：不，它不是为了节目，有真正的性能优势。

In short: No, it's not for show, there are real performance benefits.

这篇关于索引中列的顺序有多重要？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！