非聚集索引如何输出未包含在索引中的列 [英] How can a Non-Clustered index output a column that is not included in the index

问题描述

查看执行计划，我在输出列表中看到A 列".该操作是对非聚集索引的索引扫描:IX_name"

Viewing the Execution plan, i see "column A" in the Output List. The operation is an Index Scan on a Non-Clustered Index : "IX_name"

当我看到这个索引的定义时.我在索引键列或包含列中都没有看到A 列".

When i see the definition of this index. I do not see "column A" in either Index Key columns or Included columns.

非聚集索引如何用于输出索引中不存在的列.它不应该对表或其他包含A 列"的索引使用表扫描.

How is a Non-Clustered index being used to output a column that is not present in the index. Shouldn't it use a Table Scan on the table or some other index which has "column A" present in it.

推荐答案

如果表本身是 clustered¹，那么所有二级索引都包含一个集群键²的副本(一个决定物理顺序的键聚簇表中的行数).

If the table itself is clustered¹, then all secondary indexes contain a copy of the clustering key² (a key that determines the physical order of rows in the clustered table).

原因:聚簇表中的行物理存储在 B 树(不是表堆)中，因此可以在 B 树节点分裂或合并时移动，因此二级索引不能只包含行指针"(因为在行移动后它会有悬空"的危险).

The reason: rows in a clustered table are physically stored within a B-tree (not table heap), and therefore can move when B-tree nodes get split or coalesced, so the secondary index cannot just contain the row "pointer" (since it would be in danger of "dangling" after the row moves).

通常，这会对性能产生不利影响³ - 通过二级索引查询可能需要双重查找:

Often, that has detrimental effect on performance³ - querying through secondary index may require double-lookup:

• 首先，搜索二级索引并获取聚簇键.
• 其次，根据上面检索到的聚类键，搜索聚类表本身(即 B 树).

但是，如果您只需要聚类键的字段，则只需进行第一次查找.

However, if all you want are the fields of the clustering key, only the first lookup is needed.

¹ 又名 MS SQL Server 下的聚集索引".

¹ Aka "clustered index" under MS SQL Server.

² 通常，但不一定是 MS SQL Server 下的 PRIMARY KEY.

² Usually, but not necessarily a PRIMARY KEY under MS SQL Server.

³ 不幸的是，集群在 MS SQL Server 下是默认开启的——人们通常只是保留默认值，而没有充分考虑其影响.当聚类不合适时，您应该明确指定 NONCLUSTERED 关键字以将其关闭.

³ It is unfortunate that clustering is on by default under MS SQL Server - people often just leave the default without fully considering its effects. When clustering is not appropriate, you should specify NONCLUSTERED keyword explicitly to turn it off.

这篇关于非聚集索引如何输出未包含在索引中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！