首页 / 知识

关于sql：什么是涵盖索引？

2023-04-15 07:34:00

What is a Covered Index?

我刚刚在一些数据库讨论中听到了术语"涵盖索引"-这是什么意思？

覆盖索引是包含查询所需要的所有列的索引，并且可能包含更多的列。

例如，这：

1
2
3

SELECT *
FROM tablename
WHERE criteria

通常将使用索引来加快使用条件检索哪些行的分辨率，但是随后它将转到完整表以检索行。

但是，如果索引包含列column1，column2和column3，则此sql：

1
2
3

SELECT column1, column2
FROM tablename
WHERE criteria

并且，如果可以使用特定的索引来加快要检索的行的解析度，那么该索引已经包含了您感兴趣的列的值，因此它不必转到表来检索行，但可以直接从索引产生结果。

如果您看到典型查询使用1-2列来解析哪些行，然后通常添加另外1-2列，则也可以使用此方法，最好追加那些额外的列(如果它们在整个过程中都是相同的)添加到索引，以便查询处理器可以从索引本身获取所有内容。

这是一篇文章：索引覆盖可提高该主题上的SQL Server查询性能。

覆盖指数只是普通指数。如果它可以满足查询而无需分析数据，则称为"覆盖"。

例：

1
2
3
4
5
6
7
8
9

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

这是从SQL Server检索数据的最快方法之一。

假设您有一个包含以下各列的简单表，但这里仅索引了ID：

1	Id (INT), Telephone_Number (INT), Name (VARCHAR), Address (VARCHAR)

想象一下，您必须运行以下查询，并检查其查询是否使用索引，以及是否在没有I / O调用的情况下有效执行。请记住，您仅在Id上创建了索引。

1	SELECT Id FROM mytable WHERE Telephone_Number = '55442233';

当您检查此查询的性能时，您会感到失望，因为未索引Telephone_Number，因此需要使用I / O调用从表中获取行。因此，这不是覆盖索引，因为查询中有些列没有索引，这导致频繁的I / O调用。

要使其成为覆盖索引，您需要在(Id, Telephone_Number)上创建一个复合索引。

有关更多详细信息，请参阅此博客：
https://www.percona.com/blog/2006/11/23/covering-index-and-prefix-indexes/

覆盖索引是指"覆盖"特定表所需的所有列，从而完全无需为给定查询/操作访问物理表的索引。

由于索引包含所需的列(或它们的超集)，因此可以使用索引查找或扫描来替换表访问，这通常要快得多。

涵盖的列：

参数化或静态条件；受参数化或恒定条件限制的列。
连接列动态用于联接的列
选定的列；回答选定的值。

尽管覆盖索引通常可以为检索提供很好的好处，但是它们确实增加了一些插入/更新开销。由于需要在每次更新时写入额外或较大的索引行。

联接查询的覆盖索引

覆盖索引作为连接查询的一种性能技术可能最有价值。这是因为联接查询比单表检索更昂贵且更有可能遭受高成本性能问题。

在联合查询中，应按表考虑覆盖索引。
每个"覆盖索引"都会从计划中删除对物理表的访问，并将其替换为仅索引访问。
调查计划成本并进行实验，最值得用覆盖表替换哪些表。
通过这种方式，可以大大降低大型联接计划的乘法成本。

例如：

1
2
3
4
5
6
7

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);

看到：