個(gè)人中心
退出
PostgreSQL 11 新特性之覆蓋索引（Covering Index）
作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗(yàn)，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設(shè)計(jì)。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net
文章目錄
通常來說，索引可以用于提高查詢的速度。通過索引，可以快速訪問表中的指定數(shù)據(jù)，避免了表上的掃描。
有時(shí)候，索引不僅僅能夠用于定位表中的數(shù)據(jù)。某些查詢可能只需要訪問索引的數(shù)據(jù)，就能夠獲取所需要的結(jié)果，而不需要再次訪問表中的數(shù)據(jù)。這種訪問數(shù)據(jù)的方法叫做 Index-Only 掃描。
要想通過索引直接返回查詢的數(shù)據(jù)，創(chuàng)建的索引需要包含 SELECT 列表中的所有字段：
CREATE TABLE t1 (a int, b int, c int);
INSERT INTO t1
SELECT val, val + 1, val * 2
FROM generate_series(1, 100000) as val;
CREATE UNIQUE INDEX idx_t1_ab ON t1(a, b);
ANALYZE;
以下查詢使用字段 a 作為條件，并返回 a 和 b：
EXPLAIN SELECT a, b FROM t1 WHERE a BETWEEN 100 AND 200;
QUERY PLAN |
--------------------------------------------------------------------------|
Index Only Scan using idx_t1_ab on t1 (cost=0.29..166.00 rows=98 width=8)|
Index Cond: ((a >= 100) AND (a <= 200)) |
通過查詢計(jì)劃可以看出，以上查詢使用了 Index-Only Scan，直接通過索引掃描就可以返回查詢的結(jié)果。
許多數(shù)據(jù)庫產(chǎn)品對(duì)于這種包含了查詢結(jié)果的索引稱為覆蓋索引（covering index），不過更準(zhǔn)確的說法應(yīng)該是 Index-Only 掃描。它只是執(zhí)行計(jì)劃訪問數(shù)據(jù)的一種方式，而不是一種新的索引。
我們修改一下查詢，仍然以字段 a 作為查詢條件，但是要求返回 a 和 c：
EXPLAIN SELECT a, c FROM t1 WHERE a BETWEEN 100 AND 200;
QUERY PLAN |
---------------------------------------------------------------------|
Index Scan using idx_t1_ab on t1 (cost=0.29..166.00 rows=98 width=8)|
Index Cond: ((a >= 100) AND (a <= 200)) |
由于字段 c 不在索引 idx_t1_ab 中，查詢雖然使用了索引掃描，但是仍然需要通過索引二次查詢表中的數(shù)據(jù)。如果想要使用 Index-Only 掃描，需要再基于字段 a, c 創(chuàng)建一個(gè)新的索引。
如果查詢需要返回 a, b, c，還需要第 3 個(gè)索引。如果使用 a, b, c 上的索引替代 idx_t1_ab，又無法保證 a, b 上的唯一性。
為此，PostreSQL 11 提供了一個(gè)新的索引子句，即 INCLUDE 子句：
DROP INDEX idx_t1_ab;
CREATE UNIQUE INDEX idx_t1_ab ON t1 USING btree (a, b) INCLUDE (c);
ANALYZE;
Db2 和 SQL Server 也有類似 INCLUDE 子句。
以上唯一索引仍然基于字段 a, b 創(chuàng)建，同時(shí)使用 INCLUDE 子句在索引的葉子節(jié)點(diǎn)存儲(chǔ)字段 c 的值。因此，以下查詢也能夠使用 Index-Only 掃描：
EXPLAIN SELECT a, c FROM t1 WHERE a BETWEEN 100 AND 200;
QUERY PLAN |
---------------------------------------------------------------------------|
Index Only Scan using idx_t1_ab on t1 (cost=0.42..176.77 rows=105 width=8)|
Index Cond: ((a >= 100) AND (a <= 200)) |
EXPLAIN SELECT a, b, c FROM t1 WHERE a=100 and b BETWEEN 100 AND 200;
QUERY PLAN |
------------------------------------------------------------------------|
Index Only Scan using idx_t1_ab on t1 (cost=0.42..8.44 rows=1 width=12)|
Index Cond: ((a = 100) AND (b >= 100) AND (b <= 200)) |
接下來看一看官方文檔中的介紹：
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON [ ONLY ] table_name [ USING method ]
( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
[ INCLUDE ( column_name [, ...] ) ]
[ WITH ( storage_parameter = value [, ... ] ) ]
[ TABLESPACE tablespace_name ]
[ WHERE predicate ]
INCLUDE 子句可以為索引增加一些非鍵值的字段。這些非鍵字段不能用于索引的掃描條件，并且也不會(huì)參與索引的唯一性約束和排除約束。 不過，Index-Only 掃描方式可以返回這些非鍵字段的值，而不需要訪問索引所在的表，因?yàn)榭梢灾苯訌乃饕?jié)點(diǎn)中直接返回它們的值。因此，在索引中包含非索引鍵的字段擴(kuò)展了 Index-Only 掃描的使用場(chǎng)景。
使用上面的示例表：
EXPLAIN SELECT a, b, b FROM t1 WHERE a=100 and b =100 and c=100;
QUERY PLAN |
------------------------------------------------------------------------|
Index Only Scan using idx_t1_ab on t1 (cost=0.42..8.44 rows=1 width=12)|
Index Cond: ((a = 100) AND (b = 100)) |
Filter: (c = 100) |
查詢計(jì)劃中的索引掃描條件為 ‘((a = 100) AND (b = 100))’，而 ‘(c = 100)’ 只是作為過濾條件。
另外，索引 idx_t1_ab 的唯一性只能確保字段 a, b 的組合唯一，不包括字段 c 的值。
為索引添加非鍵字段時(shí)需要謹(jǐn)慎考慮，特別是寬列。如果一個(gè)索引記錄超過了索引類型允許的最大值，數(shù)據(jù)操作將會(huì)失敗。此外，非鍵字段重復(fù)存儲(chǔ)了表中的數(shù)據(jù)，并且增加了索引的大小，可能會(huì)導(dǎo)致查詢變慢。
INCLUDE 子句中的列不需要相應(yīng)的操作符類；該子句可以包含沒有為特定訪問方式定義操作符的數(shù)據(jù)類型。因?yàn)檫@些字段僅僅用于返回?cái)?shù)據(jù)，而不參與索引的掃描。
表達(dá)式（函數(shù)）不能作為 INCLUDE 字段，因?yàn)?Index-Only 掃描不支持表達(dá)式。
CREATE UNIQUE INDEX idx_t1_exp ON t1(a, b) INCLUDE ((c+1));
SQL Error [0A000]: ERROR: expressions are not supported in included columns
目前只有 B-tree 索引支持 INCLUDE 子句。在 B-tree 索引中 ，INCLUDE 子句中的字段值只存儲(chǔ)在葉子節(jié)點(diǎn)中，而不會(huì)包含在上層的導(dǎo)航節(jié)點(diǎn)中。
覆蓋索引還是優(yōu)化連接查詢的一個(gè)非常好的方法，參考：Covering Indexes for Query Optimization
參考文章：Postgres 11 highlight - Covering Indexes
分類導(dǎo)航