同一列上的非聚集索引和聚簇索引 [英] Non-clustered index and clustered index on the same column

问题描述

我遇到了这个帖子在Stackoverflow中。第一个答案提到像聚簇索引具有表的所有数据，而非聚簇索引只有列+聚簇索引的位置或行（如果它在堆上）（没有聚簇的表） index）。非聚集索引如何具有聚簇索引的位置？它只包含作为B树中节点排序的列值，每个节点都固定到列具有该节点值的行，对吧？

I came across this post in Stackoverflow. The first answer mentions something like A clustered index has all the data for the table while a non clustered index only has the column + the location of the clustered index or the row if it is on a heap (a table without a clustered index). How can a non-clustered index have the location of the clustered index? It only contains the column values sorted as nodes in a B-treee with each node pinting to the row where the column has that node-value, right?

推荐答案

假设您正在谈论 SQL Server ，并假设您在您的桌面上拥有聚集索引（正如您所希望的那样）。

Assuming you're talking about SQL Server and also assuming that you have a clustered index on your table (as you should).

然后非聚集索引具有您在 CREATE INDEX 语句中定义的列，加它具有构成聚簇索引的列（如果存在）。

Then the nonclustered index has the columns that you define in your CREATE INDEX statement, plus it has the column(s) that make up your clustered index (if present).

该聚类键值是实际数据所在位置的指针。

That clustering key value is the "pointer" to where the actual data is located.

如果查询执行者通过您的非聚集索引寻找值并找到匹配，然后

If the query executor seeks through your nonclustered index for a value and find a match, then

• 这个价值就是你所关心的 - 那么你只需要回到那个价值

• either that value is all you care about - then you just get back that value

或 非聚集索引可能还有一些包含列（在叶级页面中），并且可以满足查询（请求的所有列都存在），这样您就可以获得所要求的值对于

or the nonclustered index might also have some included columns (in the leaf level page) and with those, the query can be satisfied (all columns requested are present) so you get back the values you asked for

或 ，那么您想要的值并非都在非聚集索引叶级页面中（如果您始终执行 SELECT * ，则尤其如此）然后查询执行程序必须从非聚簇索引中获取聚类键值，然后返回到聚簇索引，做所谓的键查找，寻求通过t他聚集索引，并找到存储完整行的关联数据页面 - >和现在查询执行者可以返回您要求的值

or then the values you want are not all in the nonclustered index leaf-level page (that's especially true if you do SELECT * all the time) and then the query executor has to take the clustering key value from the nonclustered index, and go back to the clustering index, do what's called a key lookup, seek through the clustering index, and find the associated data page where the complete row is stored -> and now the query executor can return the values you've asked for

有一个很好的解释 - 请参阅此博客文章。它说：

For a pretty good explanation - see this blog post here. It says:

在NonClustered Index中：

....

2.b.如果表具有Clustered索引，或索引位于Indexed视图上，则行定位器是该行的Clustered index Key。 SQL
服务器通过使用
搜索Clustered索引来检索数据行，该存储在NonClustered
索引的Leaf行中的Clustered index Key。

或参见此博文在SQL Server索引的整个系列中，它解释了存储在非聚集索引叶级页面中的书签。

Or see this blog post in a whole series on SQL Server indexes which explains the "bookmarks" stored in a nonclustered index leaf-level page, too.

这篇关于同一列上的非聚集索引和聚簇索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！