SQL 什么是涵盖索引？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/62137/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is a Covered Index?
提问by Martynnw
I've just heard the term covered index in some database discussion - what does it mean?
我刚刚在一些数据库讨论中听到了术语覆盖索引 - 它是什么意思？
采纳答案by Lasse V. Karlsen
A covering indexis an index that contains all of, and possibly more, the columns you need for your query.
一个覆盖索引是一个包含所有的，甚至更多的指标，您需要为您查询列。
For instance, this:
例如，这个：
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.
通常会使用索引来加快使用标准检索哪些行的分辨率，但随后它将转到完整表以检索行。
However, if the index contained the columns column1, column2and column3, then this sql:
但是，如果索引包含column1、column2和column3列，则此 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.
并且，如果可以使用特定索引来加快要检索哪些行的分辨率，则该索引已经包含您感兴趣的列的值，因此不必转到表来检索行，但可以直接从索引生成结果。
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.
如果您看到典型查询使用 1-2 列来解析哪些行，然后通常添加另外 1-2 列，则也可以使用此方法，附加这些额外的列可能是有益的（如果它们全部相同） ) 到索引，以便查询处理器可以从索引本身获取所有内容。
Here's an article: Index Covering Boosts SQL Server Query Performanceon the subject.
这是一篇文章：Index Covering Boosts SQL Server Query Performance关于该主题。
回答by aku
Covering index is just an ordinary index. It's called "covering" if it can satisfy query without necessity to analyze data.
覆盖索引只是一个普通的索引。如果它可以满足查询而无需分析数据，则称为“覆盖”。
example:
例子：
CREATE TABLE MyTable
(
ID INT IDENTITY PRIMARY KEY,
Foo INT
)
CREATE NONCLUSTERED INDEX index1 ON MyTable(ID, Foo)
SELECT ID, Foo FROM MyTable -- All requested data are covered by index
This is one of the fastest methods to retrieve data from SQL server.
这是从 SQL 服务器检索数据的最快方法之一。
回答by Thomas W
Covering indexes are indexes which "cover" all columns needed from a specific table, removing the need to access the physical table at all for a given query/ operation.
覆盖索引是“覆盖”特定表所需的所有列的索引，完全不需要为给定的查询/操作访问物理表。
Since the index contains the desired columns (or a superset of them), table access can be replaced with an index lookup or scan -- which is generally much faster.
由于索引包含所需的列（或它们的超集），表访问可以替换为索引查找或扫描——这通常要快得多。
Columns to cover:
要涵盖的列：
- parameterized or static conditions; columns restricted by a parameterized or constant condition.
- join columns; columns dynamically used for joining
- selected columns; to answer selected values.
- 参数化或静态条件；受参数化或常量条件限制的列。
- 加入列；动态用于连接的列
- 选定的列；回答选定的值。
While covering indexes can often provide good benefit for retrieval, they do add somewhat to insert/ update overhead; due to the need to write extra or larger index rows on every update.
虽然覆盖索引通常可以为检索提供良好的好处，但它们确实增加了插入/更新开销；由于需要在每次更新时写入额外或更大的索引行。
Covering indexes for Joined Queries
连接查询的覆盖索引
Covering indexes are probably most valuable as a performance technique for joined queries. This is because joined queries are more costly & more likely then single-table retrievals to suffer high cost performance problems.
覆盖索引作为连接查询的性能技术可能是最有价值的。这是因为连接查询的成本更高，而且比单表检索更容易遇到高性价比问题。
- in a joined query, covering indexes should be considered per-table.
- each 'covering index' removes a physical table access from the plan & replaces it with index-only access.
- investigate the plan costs & experiment with which tables are most worthwhile to replace by a covering index.
- by this means, the multiplicative cost of large join plans can be significantly reduced.
- 在连接查询中，覆盖索引应被视为每个表。
- 每个“覆盖索引”从计划中删除一个物理表访问并将其替换为仅索引访问。
- 调查计划成本并试验哪些表最值得用覆盖索引替换。
- 通过这种方式，可以显着降低大型连接计划的乘法成本。
For example:
例如：
select oi.title, c.name, c.address
from porderitem poi
join porder po on po.id = poi.fk_order
join customer c on c.id = po.fk_customer
where po.orderdate > ? and po.status = 'SHIPPING';
create index porder_custitem on porder (orderdate, id, status, fk_customer);
See:
看：
回答by JumpMan
Lets say you have a simple table with the below columns, you have only indexed Id here:
假设您有一个包含以下列的简单表，您在这里只索引了 Id：
Id (Int), Telephone_Number (Int), Name (VARCHAR), Address (VARCHAR)
Imagine you have to run the below query and check whether its using index, and whether performing efficiently without I/O calls or not. Remember, you have only created an index on Id.
想象一下，您必须运行以下查询并检查它是否使用索引，以及是否在没有 I/O 调用的情况下高效执行。请记住，您只在 上创建了索引Id。
SELECT Id FROM mytable WHERE Telephone_Number = '55442233';
When you check for performance on this query you will be dissappointed, since Telephone_Numberis not indexed this needs to fetch rows from table using I/O calls. So, this is not a covering indexed since there is some column in query which is not indexed, which leads to frequent I/O calls.
当您检查此查询的性能时，您会感到失望，因为Telephone_Number没有索引，因此需要使用 I/O 调用从表中获取行。所以，这不是一个覆盖索引，因为查询中有一些列没有被索引，这会导致频繁的 I/O 调用。
To make it a covered index you need to create a composite index on (Id, Telephone_Number).
要使其成为覆盖索引，您需要在 上创建复合索引(Id, Telephone_Number)。
For more details, please refer to this blog: https://www.percona.com/blog/2006/11/23/covering-index-and-prefix-indexes/
更多详情请参考此博客：https: //www.percona.com/blog/2006/11/23/covering-index-and-prefix-indexes/
