巧用复合索引，优化查询性能

开源项目

巧用复合索引，优化查询性能

2020-09-23

作者：马洪旭，Greenplum内核研发

复合索引，也称作多字段索引，是指建立在表的多个字段上的索引，它是数据库系统中广泛支持的索引使用方式，Greenplum也不例外。在之前的文章中，我们已经介绍了Greenplum的默认索引——B树索引。本文将对Greenplum中的复合索引以及相关的最佳实践进行简要介绍。

建立测试数据

首先我们建立一张测试表：student，它有4个字段：

id，学生唯一编号
score，考试分数，0~100之间
class，学生班级，1~10之间
comment，学生评价，本示例中插入一个随机字符串

article=# create table student (id int, score int, class int, comment varchar(128)) distributed by (id);CREATE TABLE

随后插入约2千万的测试数据，其中少量学生不及格（第2个insert语句）：

article=# insert into student (id, score, class, comment) SELECT g, random() * 40 + 60, random() * 10 + 1, md5(g::text) FROM generate_series(1,20000000) as g;INSERT 0 20000000article=# insert into student (id, score, class, comment) SELECT g, random() * 60, random() * 10 + 1, md5(g::text) FROM generate_series(20000001,20010000) as g;INSERT 0 10000

然后在(score,class,id)列上建立一个复合索引，由于没有指定索引类型，因此建立的是Greenplum中的默认索引类型，即B-Tree索引。

article=# create index on student(score,class,id);CREATE INDEXarticle=# analyze student;ANALYZE

要重点留意的是复合索引中的数据是按索引字段定义的顺序来排序，在本例子中按(score,class,id)有序排列，即先按第一个字段score进行排序，如果score相同，再按第二个字段class排序，如果再相同，最后按id排序，一个简单示例如下：

score    class    id60       1        3460       4        2060       4        19875       1        23075       8        4575       8        10090       3        20092       1        400

Greenplum中其建立好的B-Tree复合索引逻辑上如下图所示，其中圆角矩形是索引元组，其中存储的键值如前文所述，按(score,class,id)排列；方角矩形是B-Tree节点/页面，每个节点内存储了多个索引元组。

调整代价参数

在进行查询之前，首先要在Greenplum中设置一个和代价估算相关的guc值（可参考文档：http://docs-cn.greenplum.org/v6/ref_guide/config_params/guc-list.html#random_page_cost ）：random_page_cost，它表示随机页面访问的IO代价，默认值为100（针对机械磁盘）；与之相对的是seq_page_cost，它表示顺序页面访问的IO代价，默认为1。

因为笔者的环境是SSD磁盘，而它随机和顺序读取的速度差不多，因此默认random_page_cost值过大，这里将它重新设置为5。一般来说，random_page_cost的值越大越有可能使用顺序扫描，而较小的值使得更有可能使用索引扫描。

article=# show seq_page_cost; seq_page_cost--------------- 1(1 row)article=# show random_page_cost; random_page_cost------------------ 100(1 row)article=# set random_page_cost to 5;SET

查询示例

接下来我们通过几个查询看一下Greenplum中对复合索引的具体使用情况。

查询不及格的总人数

article=# explain (costs off) select count(*) from student where score < 60;                             QUERY PLAN------------------------------------------------------------------------- Aggregate   ->  Gather Motion 2:1  (slice1; segments: 2)       ->  Aggregate             ->  Bitmap Heap Scan on student                   Recheck Cond: (score < 60)                   ->  Bitmap Index Scan on student_score_class_id_idx                         Index Cond: (score < 60) Optimizer: Postgres query optimizer(8 rows)

由查询计划可知，这里使用了索引扫描[1]（实际上是位图索引扫描即Bitmap Index Scan，它也是索引扫描的一种，详见：https://www.cybertec-postgresql.com/en/postgresql-indexing-index-scan-vs-bitmap-scan-vs-sequential-scan-basics/）。根据之前介绍B-Tree的结构，在查询过程中首先在B-Tree中找到第一条score<60的记录，因为叶子节点层有序，因此通过右向指针逐个遍历页面访问，于是可以非常快速的获取全部结果。

查询一班中不及格的总人数

article=# explain (costs off) select count(*) from student where class = 1 and score < 60;                             QUERY PLAN------------------------------------------------------------------------- Aggregate   ->  Gather Motion 2:1  (slice1; segments: 2)       ->  Aggregate             ->  Bitmap Heap Scan on student                   Recheck Cond: ((score < 60) AND (class = 1))                   ->  Bitmap Index Scan on student_score_class_id_idx                         Index Cond: ((score < 60) AND (class = 1)) Optimizer: Postgres query optimizer(8 rows)

由查询计划可知，这里依然使用了索引扫描。结合上例，即一般来说，当查询条件能对应到复合索引的某个前缀时（如本例中的socre+class），都能使用到这个复合索引，同时和它们在where语句中的出现顺序（本例中特地将class放到前边，依然可以使用索引）是无关的。

查询三班的总人数

article=# explain (costs off) select * from student where class = 3;              QUERY PLAN------------------------------------------ Gather Motion 2:1  (slice1; segments: 2)   ->  Seq Scan on student       Filter: (class = 3) Optimizer: Postgres query optimizer(4 rows)

这个查询并没有使用到索引，而只使用了普通的顺序扫描Seq Scan，这是以为使用索引时往往伴随着大量随机IO，同时这个查询的查询结果预期能命中1/10的记录，因此使用顺序扫描的效率更高：顺序IO + cache命中。这再次印证了，一般只有命中某个前缀才能使用到复合索引。

查询id=100的同学的考试信息

尽管之前我们多次提到了复合索引的前缀效应，但并不是非前缀条件下就一定不能使用复合索引，请看如下查询：

article=# explain (costs off) select * from student where id = 100;                        QUERY PLAN-------------------------------------------------------------- Gather Motion 1:1  (slice1; segments: 1)   ->  Index Scan using student_score_class_id_idx on student       Index Cond: (id = 100) Optimizer: Postgres query optimizer(4 rows)

由查询计划可以看到，尽管id并不是索引(score,class,id)的某一个前缀，但是依然可以使用到这个复合索引。笔者早年在Mysql上具有多年工作经验，深知Mysql索引的最左前缀原则，因此对于Greenplum中的这个索引优化效果非常惊奇，那是不是查询计划有误呢？我们来对比一下这个查询分别使用顺序扫描和索引扫描的实际运行时间：

article=# explain analyze select * from student where id = 100;                                  QUERY PLAN-------------------------------------------------------------------------------------[...]   ->  Seq Scan on student  (cost=0.00..302179.47 rows=1 width=45) (actual time=163.513..2282.622 rows=1 loops=1)[...] Execution time: 2304.333 msarticle=# explain analyze select * from student where id = 100;                                  QUERY PLAN-------------------------------------------------------------------------------------[...]   ->  Index Scan using student_score_class_id_idx on student  (cost=0.19..245071.28 rows=1 width=45) (actual time=134.610..636.103 rows=1 loops=1)[...] Execution time: 637.869 ms

即确实是索引扫描更快一些，对比顺序扫描，查询数据提升了4倍左右。那这里的原因是什么呢？对于id=100的查询，由于它不是复合索引的前缀，因此数据中符合id=100的索引元组并不是连续存放的。根据B-Tree查询原理，需要读取全部索引元组来判断是否符合此条件，可以由下图来对比它和顺序扫描的读取过程：

由上图的读取过程可知：

顺序扫描SeqScan，直接读取堆中的数据元组，由于不要求元组有序，因此基本都是顺序IO，但是每个数据元组对比对应的索引元组要大一些（多了comment字段）

索引扫描IndexScan，

a. 需要按序读取每个页面，页面内是顺序IO，而页面之间是随机IO

b. 到heap表中读取符合索引条件的数据元组，需要大量随机IO（如果命中查询条件的元组多的话）

回到我们之前的查询，命中id=100的数据只有1条，因此索引扫描的第2部分（b）基本可以无视，于是查询时间就是读取全部索引页面和全部数据页面的时间对比。但是索引页面由于没有comment字段，因此总大小会更小一些，这就是为什么这个查询使用索引扫描更快的原因。可以通过如下Sql来查询数据页面和索引页面的总大小，可以看出确实是索引页面要更小：

article=# select pg_relation_size('student'); pg_relation_size------------------     1724776448(1 row)article=# select pg_indexes_size('student'); pg_indexes_size-----------------     624852992(1 row)

因此我们还能了解到：Greenplum的查询优化器是非常精细的，可以准备的估算出这种情况，因此没有死板的只使用最左前缀来判断符合索引的使用场景。

总结

由如上介绍可知，如果表中数据经常使用某几个字段进行组合查询的话，那么在Greenplum中建立一个复合索引可以很好加速这些查询。不过注意在建立索引时，要将区分度或者查询频率高的字段放到最前边。

另外由于Greenplum中的索引都是二级索引（即索引存储上是独立于表中数据存放的），而建立一个复合索引(col1,col2,col3)，实际在效果上相当于建立了(col1), (col1,col2), (col1,col2,col3) 三个索引，因此对比建立多个独立索引，复合索引可以大大减少了索引的空间开销。