什么是涵盖指数？ [英] What is a Covered Index?

问题描述

解决方案

我在一些数据库讨论中刚刚听说过术语覆盖的索引>覆盖索引是一个索引，其中包含您的查询所需的所有列，甚至更多。

例如：

  SELECT * 
 FROM tablename 
 WHERE条件
  

通常会使用索引来加快使用条件检索哪些行的解析度，但是它会转到全表检索行。

但是，如果索引包含 column1，column2 和

  SELECT column1，column2 
 FROM tablename 
 WHERE条件
  

，并且，如果特定索引可用于加快要检索的行的分辨率，则索引已包含您感兴趣的列的值in，所以它不必去表来检索行，但可以直接从索引产生结果。

这也可以使用，如果你看一个典型的查询使用1-2列来解析哪些行，然后通常添加另外1-2列，可能有益的是将这些额外的列（如果他们是相同的）追加到索引，查询处理器可以从索引本身获取所有内容。

这是一个文章：索引覆盖提升SQL Server查询性能。

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, 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, 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.

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.

Here's an article: Index Covering Boosts SQL Server Query Performance on the subject.

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