Greenplum索引管理

Greenplum 索引

PostgreSQL 目前支持8种索引接口，包括B-Tree, hash, gin, gist, sp-gist, brin, rum, bloom。

Greenplum 目前支持B-Tree, GiST, bitmap三种索引接口。

用户可以根据不同的数据类型，不同的请求类型，使用不同的索引接口建立相应的索引。例如对于数组，全文检索类型，可以使用GIN索引，对于地理位置数据，范围数据类型，图像特征值数据，几何类数据等，可以选择GiST索引。

1 概述

1.1 索引不是万能的，无副作用的

在pg中，我们会经常使用索引来加速查询，但是在Greenplum中对于索引的依赖却比较低，应该合理的使用。因为在gp中顺序扫描会很快，而索引扫描则是一种随即搜索的模式。Greenplum的数据分布在Segment上，因此每个Segment会扫描全体数据的一小部分来得到结果。通过表分区，要扫描的数据量可能会更少，因此索引使用的场景可能会更少。
并且索引通常会增加一些数据库负担，它们使用存储空间并且在表被更新时需要被维护。

首先在不加索引时尝试用户的查询负载。索引更有可能为OLTP负载改进性能，在那种场景中查询会返回一个单一记录或者数据的一个小的子集。 在被压缩过的追加优化表上，索引也可以提高返回一个目标行集合的查询的性能，因为优化器在适当的时候可以使用一种索引访问方法而不是全表扫描。 对于压缩过的数据，使用一种索引访问方法意味着只有必要的行会被解压。

1.2 主键自动创建索引，分布表索引见到主表

Greenplum数据库会自动为带有主键的表创建PRIMARY KEY约束。要在一个被分区的表上创建索引，就在用户创建的分区表上创建一个索引。该索引会被传播到Greenplum数据库所创建的所有子表上。不支持在Greenplum数据库为分区表创建的子表上创建索引。

注意一个UNIQUE CONSTRAINT（例如PRIMARY KEY CONSTRAINT）会隐式地创建一个UNIQUE INDEX，它必须包括分布键中所有的列以及任何分区键。UNIQUE CONSTRAINT会在整个表上被强制要求，包括所有的表分区（如果有）。

2 索引的命名规范

索引命名规范

索引以IDX作为前缀标识，不能与表同名。在长度允许的情况下，最好能标识出该索引对应的表名和字段名。

建立索引的名称的原则是简洁和易于理解，索引命名采用如下的规范：

1. 普通索引：IDX_<简要表名>_<索引首字段名称>

例如在CS_REC_RECPTION表的INT_SERVERVER 字段上建立普通索引命名如下IDX_RECEPTION_SERVNUMBER

2. 唯一索引：UID_<简要表名>_<索引首字段名称>

例如在CS_REC_RECPTION表的INT_SERVERVER 字段上建立唯一索引命名如下UID_RECEPTION_SERVNUMBER

3. 主键：PK_<表名>

例如在CS_REC_RECPTION表的INT_SERVERVER 字段上建立主键索引命名如下PK_CS_REC_RECEPTION

在长度允许的情况下，最好能标识出该索引对应的表名和字段名。

3 索引使用规范

3.1 索引需要考虑的因素

• 用户的查询负载。 索引能改进查询返回单一记录或者非常小的数据集的性能，例如OLTP负载。
• 压缩表。在被压缩过的追加优化表上，索引也可以提高返回一个目标行集合的查询的性能。对于压缩过的数据，一种索引访问方法意味着只有必要的行会被解压。
• **避免在频繁更新的列上建立索引。**在一个被频繁更新的列上建立索引会增加该列被更新时所要求的写操作数据量。
• **创建选择性的B-树索引。**索引选择度是一个列中具有的可区分值的数量除以表中行数得到的比例。例如，如果一个表有1000行并且一个列中有800个可区分的值，则该索引的选择度为0.8，这还不错。唯一索引的选择度总是1.0，这是最好的选择度。Greenplum数据库只允许在分布键列上的唯一索引。
• 为低选择度的列使用位图索引。 Greenplum数据库的位图索引类型在常规的PostgreSQL中不可用。
• **索引在连接中用到的列。**在被用于频繁连接的一个列（例如一个外键列）上的索引能够提升连接性能，因为这让查询优化器有更多的连接方法可以使用。
• **索引在谓词中频繁使用的列。**频繁地在WHERE子句中被引用的列是索引的首选。
• **避免重叠的索引。**具有相同前导列的索引是冗余的。
• **批量载入前删掉索引。**对于载入大量数据到一个表中，请考虑先删掉索引并且在数据装载完成后重建它们。这常常比更新索引更快。
• 考虑一个聚簇索引。 聚簇一个索引意味着记录会根据索引被物理排序后存储在磁盘上。如果用户需要的数据被随机分布在磁盘上，数据库必须在磁盘上来回寻找以取得所需的记录。如果这些记录被存储得彼此临近，那么取得它们的操作就会更高效。例如，一个在日期列上的聚簇索引中数据会按照日期顺序存放。针对一个指定日期范围的查询将会导致对磁盘的一次有序地读取，这会利用快速的顺序访问。
• 重创建索引执行执行Analyze。

4 Greenplum 支持的索引类型

PostgreSQL 目前支持8种索引接口，包括B-Tree, hash, gin, gist, sp-gist, brin, rum, bloom。

Greenplum 目前支持B-Tree, GiST, bitmap和GIN四种索引接口。可以分区索引和组合索引。

用户可以根据不同的数据类型，不同的请求类型，使用不同的索引接口建立相应的索引。例如对于数组，全文检索类型，可以使用GIN索引，对于地理位置数据，范围数据类型，图像特征值数据，几何类数据等，可以选择GiST索引。

Note: 只有索引键的列与Greenplum分布键相同（或者是其超集）时，Greenplum数据库才允许唯一索引。在追加优化表上不支持唯一索引。在分区表上，唯一索引无法在一个分区表的所有子表分区之间被实施。唯一索引只能在一个分区内实施。

5 Greenplum索引分类及适用场景

(1)btree索引

适合于拥有重复值较少的字段。数据重复值越少，查询数据选择性越 ，使 索引查询数据的效率越 ，反之则越低。
适于对字段所在数据有少 修改的场合，如字段有较少 的插 、修改、删除等情况， 新索引字段键值的代价适中。适于数据变化相对频繁的系统需要占较的存储空间
(2)bitmap索引

拥有较 重复值的字段，数据重复值对索引的查询效率影响 对字段所有数据只读或者极少修改的场合数据变化很少的数据分析系统需要较 的存储空间。

Greenplum数据库提供位图索引类型。位图索引最适合于拥有大量数据、很多临时查询以及少量数据修改（DML）事务的数据仓库应用和决策支持系统。

一个索引提供了指向表中包含一个给定键值的行的指针。常规索引存储了每个键存储了一个元组ID的列表，列表中的元组ID对应于具有那个键值的行。 位图索引为每一个键值都存储一个位图。常规索引可能会比表中的数据大几倍，但位图索引提供了和常规索引相同的功能并且只需要被索引数据尺寸的一小部分。

位图中的每一个位对应于一个可能的元组ID。 如果该位被设置，则具有相应元组ID的行包含该键值。 一个映射函数负责将这个位的位置转换成一个元组ID。位图被压缩存储。如果可区分键值的数量很小，位图索引会小很多同时也会被压缩得更好，并且比常规索引节省可观的空间。 一个位图索引的大小与该表中行数乘以被索引列中不同值数量的结果成比例。

位图索引对于在WHERE子句中包含多个条件的查询最有效。 满足某些但不是全部条件的行在访问表之前就会被过滤掉。这通常会极大地改善响应时间。

(3)hash索引

索引的 涉及到经常使 =操作时候， 不推荐使用

6 索引测试

6.1 bitmap索引

bitmap索引类似GIN倒排，只是bitmap的KEY是列的值，VALUE是BIT（每个BIT对应一行），而不是行号list或tree。
bitmap与btree一样，都支持 等于，大于，小于，大于等于，小于等于的查询。但是要注意：bitmap索引适用于只查询数据而不更新数据的数据仓库应用。

例子：—建表

postgres=# CREATE table t2 (id int,info text)
postgres-# with(appendonly=true,orientation=column)
postgres-# distributed by(id);
CREATE TABLE
postgres=# insert into t2 select generate_series(1,100000),md5(random()::text);
INSERT 0 100000

—不使用索引

postgres=# EXPLAIN select * from t2 where id <10;
                                   QUERY PLAN                                    
---------------------------------------------------------------------------------
 Gather Motion 2:1  (slice1; segments: 2)  (cost=0.00..1364.00 rows=10 width=37)
   ->  Append-only Columnar Scan on t2  (cost=0.00..1364.00 rows=5 width=37)
         Filter: id < 10
 Optimizer status: legacy query optimizer
(4 rows)

—创建bitmap索引

postgres=# CREATE INDEX idx_t2 on t2 using bitmap(id);
CREATE INDEX

—再次查看执行计划发现走bitmap索引扫描了。

postgres=# EXPLAIN select * from t2 where id <10;     
                                         QUERY PLAN                                         
--------------------------------------------------------------------------------------------
 Gather Motion 2:1  (slice1; segments: 2)  (cost=101.98..808.90 rows=10 width=37)
   ->  Bitmap Append-Only Column-Oriented Scan on t2  (cost=101.98..808.90 rows=5 width=37)
         Recheck Cond: id < 10
         ->  Bitmap Index Scan on idx_t2  (cost=0.00..101.98 rows=5 width=0)
               Index Cond: id < 10
 Optimizer status: legacy query optimizer
(6 rows)

重建索引:
—重建指定索引

postgres=# REINDEX INDEX idx_t2;
REINDEX

—重建一张表上所有索引

postgres=# REINDEX TABLE t2;
REINDEX

6.3 B-tree索引

​ CREATE INDEX命令在一个表上定义一个索引。 例如，要在表employee的gender列上创建一个B-树索引：

CREATE INDEX gender_idx ON employee (gender);

6.3 表达式索引

索引列不必只是表的一列，而是可以是从表的一列或多列计算的函数或标量表达式。 此功能对于根据计算结果快速访问表非常有用。

索引表达式的维护成本相对较高，因为必须在插入和每次更新时为每一行计算派生表达式。

第一个示例是使用lower函数进行不区分大小写的比较的常用方法：

SELECT * FROM test1 WHERE lower(col1) = 'value';

如果一个索引已经在lower(col1)函数的结果上被定义，该查询可以使用索引：

CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));

7 检查索引使用

Greenplum数据库的索引并不要求维护和调优。 用户可以检查实际的查询负载使用了哪些索引。 使用EXPLAIN命令可以检查一个查询的索引使用。

查询计划展示了数据库将用来回答一个查询的步骤或者计划节点以及每一个计划节点的时间估计。要检查索引的使用，请在用户的EXPLAIN输出中寻找以下查询计划节点类型：

• 索引扫描 - 一次索引的扫描。
• 位图堆扫描 - 检索所有由BitmapAnd、BitmapOr或者BitmapIndexScan生成的位图并且访问堆以检索相关的行。
• 位图索引扫描 - 计算一个由所有来自底层索引的满足查询谓词的位图通过OR操作形成的位图。
• BitmapAnd或BitmapOr - 取得从多个BitmapIndexScan节点生成的位图，把它们AND或者OR在一起，并且生成一个新的位图作为其输出。

用户必须做实验来确定要创建哪些索引。请考虑以下几点。

• 在创建或者更新一个索引后运行ANALYZE。ANALYZE会收集表统计信息。 查询优化器使用表统计信息来估算一个查询所返回的行数并且为每一种可能的查询计划赋予实际开销。
• 实验中使用真实数据。使用测试数据建立索引会告诉用户该测试数据需要什么样的索引，但也仅此而已。
• 不要使用非常小的测试数据集，因为它们的结果很可能是不真实的或者倾斜的。
• 在开发测试数据时要小心。相似的、完全随机的或者排序后插入的值都将使统计信息偏离真实数据的分布。
• 通过使用运行时参数来关闭特定的计划类型，用户可以强制使用索引来进行测试。 例如，关闭顺序扫描（enable_seqscan）以及嵌套循环连接（enable_nestloop）两种最基本的计划来强制系统使用一种不同的计划。 对用户的查询使用索引和不用索引的执行进行计时，并且使用EXPLAIN ANALYZE命令来比较结果。