索引键列 VS 索引包含列 [英] Index Key Column VS Index Included Column

问题描述

谁能解释一下这两个 - 索引键列 VS 索引包含列?

Can someone explain this two - Index Key Column VS Index Included Column?

目前，我有一个索引，其中包含 4 个索引键列和 0 个包含列.

Currently, I have an index that has 4 Index Key Column and 0 Included Column.

谢谢

推荐答案

索引键列是索引的 b 树的一部分.包含的列不是.

Index key columns are part of the b-tree of the index. Included columns are not.

取两个索引:

CREATE INDEX index1 ON table1 (col1, col2, col3)
CREATE INDEX index2 ON table1 (col1) INCLUDE (col2, col3)

index1 更适合这种查询:

SELECT * FROM table1 WHERE col1 = x AND col2 = y AND col3 = z

而 index2 更适合这种查询:

Whereas index2 is better suited for this kind of query:

SELECT col2, col3 FROM table1 WHERE col1 = x

在第一个查询中，index1 提供了一种快速识别感兴趣的行的机制.查询将(可能)作为索引查找执行，然后是书签查找以检索完整行.

In the first query, index1 provides a mechanism for quickly identifying the rows of interest. The query will (probably) execute as an index seek, followed by a bookmark lookup to retrieve the full row(s).

在第二个查询中，index2 充当覆盖索引.SQL Server 根本不需要访问基表，因为索引提供了满足查询所需的所有数据.在这种情况下，index1 也可以充当覆盖索引.

In the second query, index2 acts as a covering index. SQL Server doesn't have to hit the base table at all, since the index provides all the data it needs to satisfy the query. index1 could also act as a covering index in this case.

如果您想要一个覆盖索引，但不想将所有列添加到 b 树中，因为您不查找它们，或者不能因为它们不是允许的数据类型(例如，XML), 使用 INCLUDE 子句.

If you want a covering index, but don't want to add all columns to the b-tree because you don't seek on them, or can't because they aren't an allowed datatype (eg, XML), use the INCLUDE clause.

这篇关于索引键列 VS 索引包含列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！