什么是涵盖索引? [英] What is a Covered Index?

问题描述

我刚刚在一些数据库讨论中听说过涵盖索引"这个词 - 它是什么意思?

I've just heard the term covered index in some database discussion - what does it mean?

推荐答案

覆盖索引 是一种索引，它包含查询所需的所有列，可能还有更多列.

A covering index is an index that contains all of, and possibly more, the columns you need for your query.

例如:

SELECT *
FROM tablename
WHERE criteria

通常会使用索引来加快解析使用标准检索哪些行的速度，但随后它将转到完整表以检索行.

will typically use indexes to speed up the resolution of which rows to retrieve using criteria, but then it will go to the full table to retrieve the rows.

但是，如果索引包含列column1, column2 和column3，那么这个sql:

However, if the index contained the columns column1, column2 and column3, then this sql:

SELECT column1, column2
FROM tablename
WHERE criteria

而且，如果可以使用特定索引来加快检索哪些行的分辨率，则该索引已经包含您感兴趣的列的值，因此不必转到表中检索行，但可以直接从索引生成结果.

and, provided that particular index could be used to speed up the resolution of which rows to retrieve, the index already contains the values of the columns you're interested in, so it won't have to go to the table to retrieve the rows, but can produce the results directly from the index.

如果您看到一个典型的查询使用 1-2 列来解析哪些行，然后通常会添加另外 1-2 列，那么也可以使用此方法，附加这些额外的列(如果它们是全部相同)到索引，以便查询处理器可以从索引本身获取所有内容.

This can also be used if you see that a typical query uses 1-2 columns to resolve which rows, and then typically adds another 1-2 columns, it could be beneficial to append those extra columns (if they're the same all over) to the index, so that the query processor can get everything from the index itself.

这是关于该主题的文章:索引覆盖提高 SQL Server 查询性能.

这篇关于什么是涵盖索引?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！