SQL Server 中的覆盖索引和覆盖查询是什么？

What are Covering Indexes and Covered Queries in SQL Server?

提问by TheVillageIdiot

Can you explain the concepts of, and relationship between, Covering Indexes and Covered Queries in Microsoft's SQL Server?

你能解释一下微软 SQL Server 中覆盖索引和覆盖查询的概念和关系吗？

采纳答案by Mitch Wheat

A covering index is one which can satisfy all requested columns in a query without performing a further lookup into the clustered index.

覆盖索引是一种可以满足查询中所有请求列的索引，而无需对聚集索引执行进一步查找。

There is no such thing as a covering query.

没有覆盖查询这样的东西。

Have a look at this Simple-Talk article: Using Covering Indexes to Improve Query Performance.

看看这篇 Simple-Talk 文章：使用覆盖索引来提高查询性能。

回答by Suresh

If all the columnsrequested in the selectlist of query, are available in the index, then the query engine doesn't have to lookup the table again which can significantly increase the performance of the query. Since all the requested columns are available with in the index, the index is covering the query. So, the query is called a covering query and the index is a covering index.

如果查询列表中请求的所有列select都在索引中可用，则查询引擎不必再次查找表，这可以显着提高查询的性能。由于所有请求的列都在索引中可用，因此索引覆盖了查询。因此，查询称为覆盖查询，索引称为覆盖索引。

A clustered index can always cover a query, if the columns in the select list are from the same table.

如果选择列表中的列来自同一个表，则聚集索引始终可以覆盖查询。

The following links can be helpful, if you are new to index concepts:

如果您不熟悉索引概念，以下链接可能会有所帮助：

• Excellent video on advantages and disadvantages of index and covering queries and indexes.
• Indexes in SQL Server

• 关于索引的优缺点以及涵盖查询和索引的优秀视频。
• SQL Server 中的索引

回答by TheEsnSiavashi

A Covering Index is a Non-Clusteredindex. Both Clustered and Non-Clustered indexes use B-Tree data structure to improve the search for data, the difference is that in the leaves of a Clustered Index a whole record (i.e. row) is stored physically right there!, but this is not the case for Non-Clustered indexes. The following examples illustrate it:

覆盖索引是一个Non-Clustered索引。聚集索引和非聚集索引都使用 B 树数据结构来改进数据搜索，区别在于聚集索引的叶子中，整个记录（即行）物理存储在那里！，但这不是非聚集索引的情况。下面的例子说明了它：

Example: I have a table with three columns: ID, Fname and Lname.

示例：我有一个包含三列的表：ID、Fname 和 Lname。

However, for a Non-Clustered index, there are two possibilities: either the table already has a Clustered index or it doesn't:

但是，对于非聚集索引，有两种可能性：要么表已经有聚集索引，要么没有：

As the two diagrams show, such Non-Clustered indexes do not provide a good performance, because they cannot find the favorite value (i.e. Lname) solely from the B-Tree. Instead they have to do an extra Look Up step (either Key or RID look up) to find the value of Lname. And, this is where covered index comes to the screen.Here, the Non-Clustered index on ID coveres the value of Lname right next to it in the leaves of the B-Tree and there is no need for any type of look up anymore.

如两个图所示，这种非聚集索引不能提供良好的性能，因为它们无法仅从 B 树中找到最喜欢的值（即 Lname）。相反，他们必须执行额外的查找步骤（Key 或 RID 查找）才能找到 Lname 的值。而且，这就是覆盖索引出现在屏幕上的地方。在这里，ID 上的非聚集索引覆盖了 B 树叶子中紧邻它的 Lname 的值，并且不再需要任何类型的查找。

回答by yfeldblum

A covered queryis a query where all the columns in the query's result set are pulled from non-clustered indexes.

一个覆盖查询是其中在查询的结果集中的所有列从非聚集索引拉查询。

A query is made into a covered query by the judicious arrangement of indexes.

通过索引的明智安排，查询变成了覆盖查询。

A covered query is often more performant than a non-covered query in part because non-clustered indexes have more rows per page than clustered indexes or heap indexes, so fewer pages need to be brought into memory in order to satisfy the query. They have more rows per page because only part of the table row is part of the index row.

覆盖查询通常比非覆盖查询性能更高，部分原因是非聚集索引每页的行数比聚集索引或堆索引多，因此需要将更少的页放入内存以满足查询。它们每页有更多行，因为只有表行的一部分是索引行的一部分。

A covering indexis an index which is used in a covered query. There is no such thing as an index which, in and of itself, is a covering index. An index may be a covering index with respect to query A, while at the same time not being a covering index with respect to query B.

甲覆盖索引是其在有盖查询中使用的索引。没有索引这样的东西，它本身就是一个覆盖索引。一个索引可能是关于查询 A 的覆盖索引，同时不是关于查询 B 的覆盖索引。

回答by Shalom Craimer

Here's an article in devx.comthat says:

这是devx.com上的一篇文章，它说：

Creating a non-clustered index that contains all the columns used in a SQL query, a technique called index covering

创建包含 SQL 查询中使用的所有列的非聚集索引，这种技术称为索引覆盖

I can only suppose that a covered queryis a query that has an index that covers all the columns in its returned recordset. One caveat - the index and query would have to be built as to allow the SQL server to actually infer from the query that the index is useful.

我只能假设一个覆盖查询是一个查询，它有一个覆盖其返回记录集中所有列的索引。一个警告 - 必须构建索引和查询，以允许 SQL 服务器从查询中实际推断出索引是有用的。

For example, a join of a table on itself might not benefit from such an index (depending on the intelligence of the SQL query execution planner):

例如，一个表本身的连接可能不会从这样的索引中受益（取决于 SQL 查询执行计划器的智能）：

PersonID ParentID Name
1        NULL     Abe
2        NULL     Bob
3        1        Carl
4        2        Dave

Let's assume there's an index on PersonID,ParentID,Name- this would be a covering index for a query like:

让我们假设有一个索引PersonID,ParentID,Name- 这将是一个查询的覆盖索引，例如：

SELECT PersonID, ParentID, Name FROM MyTable

But a query like this:

但是这样的查询：

SELECT PersonID, Name FROM MyTable LEFT JOIN MyTable T ON T.PersonID=MyTable.ParentID

Probably wouldn't benifit so much, even though all of the columns are in the index. Why? Because you're not really telling it that you want to use the triple index of PersonID,ParentID,Name.

即使所有列都在索引中，可能不会有太大好处。为什么？因为您并没有真正告诉它您想要使用PersonID,ParentID,Name.

Instead, you're building a condition based on two columns - PersonIDand ParentID(which leaves out Name) and then you're asking for all the records, with the columns PersonID, Name. Actually, depending on implementation, the index might help the latter part. But for the first part, you're better off having other indexes.

相反，您正在构建基于两列的条件 - PersonIDand ParentID（省略Name），然后您要求所有记录，以及 columns PersonID, Name。实际上，根据实施情况，索引可能对后半部分有所帮助。但是对于第一部分，最好使用其他索引。

回答by Learning

A covering query is on where all the predicates can be matched using the indices on the underlying tables.

覆盖查询是关于可以使用基础表上的索引匹配所有谓词的地方。

This is the first step towards improving the performance of the sql under consideration.

这是提高所考虑的 sql 性能的第一步。

回答by Sadia Aziz

a covering index is the one which gives every required column and in which SQL server don't have hop back to the clustered index to find any column. This is achieved using non-clustered index and using INCLUDE option to cover columns. Non-key columns can be included only in non-clustered indexes. Columns can't be defined in both the key column and the INCLUDE list. Column names can't be repeated in the INCLUDE list. Non-key columns can be dropped from a table only after the non-key index is dropped first. Please see details here

覆盖索引是提供每个必需列的索引，其中 SQL Server 没有跳回聚集索引来查找任何列。这是通过使用非聚集索引和使用 INCLUDE 选项来覆盖列来实现的。非键列只能包含在非聚集索引中。不能同时在键列和 INCLUDE 列表中定义列。列名不能在 INCLUDE 列表中重复。只有先删除非键索引后，才能从表中删除非键列。请在此处查看详细信息

回答by Bob Letts

When I simply recalled that a Clustered Index consists of a key-ordered non-heap list of ALL the columns in the defined table, the lights went on for me. The word "cluster", then, refers to the fact that there is a "cluster" of all the columns, like a cluster of fish in that "hot spot". If there is no index covering the column containing the sought value (the right side of the equation), then the execution plan uses a Clustered Index Seek into the Clustered Index's representation of the requested column because it does not find the requested column in any other "covering" index. The missing will cause a Clustered Index Seek operator in the proposed Execution Plan, where the sought value is within a column inside the ordered list represented by the Clustered Index.

当我简单地回忆起聚集索引由已定义表中所有列的键排序非堆列表组成时，我的灯亮了。那么，“集群”一词指的是所有列都有一个“集群”，就像那个“热点”中的鱼群一样。如果没有索引覆盖包含所寻求值的列（等式的右侧），则执行计划使用聚集索引查找到聚集索引对请求列的表示，因为它没有在任何其他列中找到请求的列“覆盖”指数。缺失将导致提议的执行计划中出现聚集索引查找运算符，其中查找的值位于由聚集索引表示的有序列表内的列内。

So, one solution is to create a non-clustered index that has the column containing the requested value inside the index. In this way, there is no need to reference the Clustered Index, and the Optimizer should be able to hook that index in the Execution Plan with no hint. If, however, there is a Predicate naming the single column clustering key and an argument to a scalar value on the clustering key, the Clustered Index Seek Operator will still be used, even if there is already a covering index on a second column in the table without an index.

因此，一种解决方案是创建一个非聚集索引，该索引在索引中包含包含请求值的列。这样，就不需要引用聚集索引，优化器应该能够在没有提示的情况下在执行计划中挂钩该索引。但是，如果有一个 Predicate 命名单列聚集键和一个参数到聚集键上的标量值，则仍将使用聚集索引查找运算符，即使在第二列上已经有一个覆盖索引没有索引的表。

回答by Bruce

Page 178, High Performance MySQL, 3rd Edition

第 178 页，高性能 MySQL，第 3 版

An index that contains (or "covers") all the data needed to satisfy a query is called a covering index.

When you issue a query that is covered by an index (an indexed-covered query), you'll see "Using Index" in the Extra column in EXPLAIN.

包含（或“覆盖”）满足查询所需的所有数据的索引称为覆盖索引。

当您发出由索引覆盖的查询（索引覆盖查询）时，您将在 EXPLAIN 的 Extra 列中看到“Using Index”。