blocks|key|1158406|text|包含的列不构成索引的键的一部分，但它们确实存在于索引中。从本质上讲，这些值将是重复的，因此存在存储开销，但是您的索引覆盖(即被查询优化器选择)更多查询的可能性更大。这种复制还可以提高查询时的性能，因为数据库引擎可以返回值，而不必查看表本身。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1158407|只有非聚集索引才能包含列，因为在聚集索引中，实际上包含了每一列。|1158408|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

Included columns don't form part of the key for the index, but they do exist on the index. Essentially the values will be duplicated, so there is a storage overhead, but there is a greater chance that your index will cover (i.e. be selected by the query optimizer for) more queries. This duplication also improves performance when querying, since the database engine can return the value without having to look at the table itself.

Only nonclustered indexes can have included columns, because in a clustered index, every column is effectively included.

blocks|key|944454|text|索引键列是索引的b树的一部分。包含的列不是。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|944455|取两个索引：|944456|CREATE+INDEX+index1+ON+table1+(col1,+col2,+col3)
CREATE+INDEX+index2+ON+table1+(col1)+INCLUDE+(col2,+col3)|code-block|syntax|javascript|944457|index1更适合于这种查询：|offset|length|style|CODE|944458|SELECT+*+FROM+table1+WHERE+col1+=+x+AND+col2+=+y+AND+col3+=+z|944459|而index2更适合这种查询：|944460|SELECT+col2,+col3+FROM+table1+WHERE+col1+=+x|944461|在第一个查询中，index1提供了一种快速识别感兴趣的行的机制。查询将(可能)作为索引查找执行，然后执行书签查找以检索整行。|944462|在第二个查询中，index2充当覆盖索引。SQL+Server根本不需要命中基表，因为索引提供了满足查询所需的所有数据。在这种情况下，index1也可以作为覆盖索引。|944463|如果您想要一个覆盖索引，但又不想将所有列都添加到b-tree中，因为您不在这些列上进行查找，或者因为它们不是允许的数据类型(例如XML)而无法添加，那么可以使用INCLUDE子句。|944464|entityMap^0|0|0|0|0|6|0|0|1|6|0|0|8|6|0|8|6|1V|6|0|0^^$0|@$1|2|3|4|5|6|7|12|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|13|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|14|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|15|8|@$K|16|L|17|M|N]]|9|@]|A|$]]|$1|O|3|P|5|F|7|18|8|@]|9|@]|A|$G|H]]|$1|Q|3|R|5|6|7|19|8|@$K|1A|L|1B|M|N]]|9|@]|A|$]]|$1|S|3|T|5|F|7|1C|8|@]|9|@]|A|$G|H]]|$1|U|3|V|5|6|7|1D|8|@$K|1E|L|1F|M|N]]|9|@]|A|$]]|$1|W|3|X|5|6|7|1G|8|@$K|1H|L|1I|M|N]|$K|1J|L|1K|M|N]]|9|@]|A|$]]|$1|Y|3|Z|5|6|7|1L|8|@]|9|@]|A|$]]|$1|10|3|-4|5|6|7|1M|8|@]|9|@]|A|$]]]|11|$]]

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

Take two indexes:

<pre><code>CREATE INDEX index1 ON table1 (col1, col2, col3)
CREATE INDEX index2 ON table1 (col1) INCLUDE (col2, col3)
</code></pre>

<code>index1</code> is better suited for this kind of query:

<pre><code>SELECT * FROM table1 WHERE col1 = x AND col2 = y AND col3 = z
</code></pre>

Whereas <code>index2</code> is better suited for this kind of query:

<pre><code>SELECT col2, col3 FROM table1 WHERE col1 = x
</code></pre>

In the first query, <code>index1</code> 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).

In the second query, <code>index2</code> 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. <code>index1</code> could also act as a covering index in this case. 

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.

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

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

Thanks

Index Key Column VS Index Included Column

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

有人能解释一下这个双索引键列和索引包含列吗？目前，我有一个索引，它有4个索引键列和0个包含列。谢谢

问索引键列与索引包含列
EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问索引键列与索引包含列EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问索引键列与索引包含列
EN