SQL Server 聚集索引是否替换了 RID 查找“索引"? [英] Does the SQL Server clustered index replace the RID lookup "index"

问题描述

当一个表在 SQL Server 中具有聚集索引时，是否意味着所有索引查询都将通过聚集索引?

When a table has a clustered index in SQL Server does that mean that all indexed queries will go via the clustered index?

例如，如果我有一个带有单个非聚集索引(索引一列)的表并通过该列搜索一行，它将执行 Index Seek ->RID ->数据行查找 ->结果

For example if I have a table with a single non-clustered index (indexing one column) and search for a row via that column it will do Index Seek -> RID -> Data row lookup -> Result

但是如果我在不同的列上添加聚集索引，那么相同的查询将执行以下 Index Seek ->提取聚类键 ->聚集索引查找 ->结果

But if I add a clustered index on a different column then the same query will do the following Index Seek -> Extract clustering key -> Clustered index seek -> Results

这对我来说意味着非聚集索引不再以叶处的 RID终止"，而是使用聚集索引的聚集键?是吗?

This implies to me that the non-clustered index no longer 'terminates' with a RID at the leaf but with a clustering key of the clustered index? Is that right?

推荐答案

是的，你已经很清楚了.

Yes, you got it pretty much figured out.

当您有聚集索引时，任何非聚集索引也将包括聚集索引中的列作为它们对实际数据的查找".

When you have a clustered index, then any non-clustered index will also include the column(s) from the clustered index as their "lookup" into the actual data.

如果您在非聚集索引中搜索某个值，并且需要访问基础数据的其余列，则 SQL Server 会从该非聚集索引中执行书签查找"(或键查找")索引到聚集索引(它包含数据本身，在叶级节点中).使用聚集索引，您不再需要 RID - 因此，如果 RID 更改(当数据从一个页面移动到另一个页面时)，您不必更新所有索引页面.

If you search for a value in a non-clustered index, and you need to access the remaining columns of the underlying data, then SQL Server does a "bookmark lookup" (or "key lookup") from that non-clustered index into the clustered index (which contains the data itself, in the leaf-level nodes). With a clustered index, you don't need the RID's anymore - and thus you don't have to update all your index pages if a RID changes (when data gets moved from one page to another).

书签查找是相当昂贵的操作，因此您可以通过 INCLUDE 语句向非聚集索引添加额外的列.有了这个，您的非聚集索引将在其叶级页面上包含那些额外的列，并且如果您只需要包含在该数据集中的列，则可以从非聚集索引本身(在那个在这种情况下，它被称为覆盖索引")，您可以为自己节省一系列书签查找.

Bookmark lookups are rather expensive operations, so you can add additional columns to your non-clustered indices via the INCLUDE statement. With this, your non-clustered index will contain those additional columns on its leaf-level pages, and if you only need columns that are contained in that set of data, your query can be satisfied from the non-clustered index itself (in that case, it's called a "covering index") and you can save yourself a series of bookmark lookups.

这篇关于SQL Server 聚集索引是否替换了 RID 查找“索引"?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！