PostgreSQL 11 新特性之覆盖索引（Covering Index）

作者: 不剪发的Tony老师
毕业于北京航空航天大学，十多年数据库管理与开发经验，目前在一家全球性的金融公司从事数据库架构设计。CSDN学院签约讲师以及GitChat专栏作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net

文章目录

通常来说，索引可以用于提高查询的速度。通过索引，可以快速访问表中的指定数据，避免了表上的扫描。

有时候，索引不仅仅能够用于定位表中的数据。某些查询可能只需要访问索引的数据，就能够获取所需要的结果，而不需要再次访问表中的数据。这种访问数据的方法叫做 Index-Only 扫描。

要想通过索引直接返回查询的数据，创建的索引需要包含 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))                                 |

    
通过查询计划可以看出，以上查询使用了 Index-Only Scan，直接通过索引扫描就可以返回查询的结果。

许多数据库产品对于这种包含了查询结果的索引称为覆盖索引（covering index），不过更准确的说法应该是 Index-Only 扫描。它只是执行计划访问数据的一种方式，而不是一种新的索引。

我们修改一下查询，仍然以字段 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 中，查询虽然使用了索引扫描，但是仍然需要通过索引二次查询表中的数据。如果想要使用 Index-Only 扫描，需要再基于字段 a, c 创建一个新的索引。

如果查询需要返回 a, b, c，还需要第 3 个索引。如果使用 a, b, c 上的索引替代 idx_t1_ab，又无法保证 a, b 上的唯一性。

为此，PostreSQL 11 提供了一个新的索引子句，即 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 创建，同时使用 INCLUDE 子句在索引的叶子节点存储字段 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 子句可以为索引增加一些非键值的字段。这些非键字段不能用于索引的扫描条件，并且也不会参与索引的唯一性约束和排除约束。 不过，Index-Only 扫描方式可以返回这些非键字段的值，而不需要访问索引所在的表，因为可以直接从索引节点中直接返回它们的值。因此，在索引中包含非索引键的字段扩展了 Index-Only 扫描的使用场景。

使用上面的示例表：

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

    
查询计划中的索引扫描条件为 ‘((a = 100) AND (b = 100))’，而 ‘(c = 100)’ 只是作为过滤条件。

另外，索引 idx_t1_ab 的唯一性只能确保字段 a, b 的组合唯一，不包括字段 c 的值。

为索引添加非键字段时需要谨慎考虑，特别是宽列。如果一个索引记录超过了索引类型允许的最大值，数据操作将会失败。此外，非键字段重复存储了表中的数据，并且增加了索引的大小，可能会导致查询变慢。

INCLUDE 子句中的列不需要相应的操作符类；该子句可以包含没有为特定访问方式定义操作符的数据类型。因为这些字段仅仅用于返回数据，而不参与索引的扫描。

表达式（函数）不能作为 INCLUDE 字段，因为 Index-Only 扫描不支持表达式。

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 子句中的字段值只存储在叶子节点中，而不会包含在上层的导航节点中。

覆盖索引还是优化连接查询的一个非常好的方法，参考：Covering Indexes for Query Optimization

参考文章：Postgres 11 highlight - Covering Indexes