使用复合聚簇索引时，SQL Server是否会跳槽？ [英] Does SQL Server jump leaves when using a composite clustered index?

问题描述

考虑以下复合聚簇索引：

Consider the following composite clustered index:

CREATE UNIQUE CLUSTERED INDEX ix_mytable ON mytable(a, b)

显然， b 上的单独索引将搜索的特定值b 更快。

Obviously, a separate index on b will make searching for a particular value of b faster.

但是，如果 b 上的单独索引不，则似乎对我来说，复合索引仍可用于通过遍历 a 的离散值树来查找具有 b 特定值的元组而不是表扫描本地搜索 b ，跳转到 a 的下一个值，等等。

However, if a separate index on b is not employed, it seems to me that the composite index can still be used to find tuples with a particular value for b instead of a table scan, by traversing the tree of discrete values of a and do a local search for b, jump to the next value of a, etc.

这是SQL Server的方式作品？ （例如，如果MSSQL对具有多列的索引使用单个哈希值，则不会这样。）

Is this how SQL Server works? (It would not be, for instance, if MSSQL uses a single hash value for indexes with multiple columns.)

它是，并且已经需要复合索引其他原因，以及 a 的离散值的数量足够小，性能/空间权衡可能会摆脱 b 的单独索引。

It it is, and the composite index is needed already for other reasons, and the number of discrete values of a is small enough, the performance/space trade-off may swing away from having a separate index for b.

（上面的UNIQUE和CLUSTERED约束对于这个例子并不是真的需要，但它们代表了 b 的最快检索，它不涉及单独的 b 的索引 - 前者为 a 的每个循环提供快捷方式，后者在查找中删除了一个间接度。）

(The UNIQUE and CLUSTERED constraints above aren't really required for this example, but they would represent the fastest retrieval of b that did not involve a separate index for b--the former providing a shortcut for each loop of a, the latter removing one degree of indirection in the lookup).

推荐答案

不，没有跳过'a'的集群。只有在指定了最左边的列时才能使用索引，否则需要使用完整扫描。

No, there is no jumping over a clusters of 'a'. An index can be used only if the leftmost column is specified, otherwise a full scan needs to be employed.

Oracle有所谓的索引跳过扫描操作员。

Oracle has the so called 'Index Skip Scan' operator.

这篇关于使用复合聚簇索引时，SQL Server是否会跳槽？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！