索引管理

注意事项

• Serverless模式实例仅支持创建B-Tree类型索引。

• Serverless模式实例如果创建了索引，会影响系统扩缩容的性能。扩缩容完成时间与索引的数据量成正比关系。

索引类型

云原生数据仓库AnalyticDB PostgreSQL版支持如下索引类型：

• B-Tree索引（默认索引类型）

• 位图索引

  说明

  位图索引（Bitmap Index）为每一个键值都存储一个位图，位图索引提供了和常规索引相同的功能且减少索引空间。对于拥有100至100000个可区分值的列并且当被索引列经常与其他被索引列联合查询时，位图索引表现最佳。

• BRIN索引（仅AnalyticDB PostgreSQL 6.0版支持）

• GIN索引（仅AnalyticDB PostgreSQL 6.0版支持）

• GiST索引（仅AnalyticDB PostgreSQL 6.0版支持）

索引的选择原则

什么情况下需要创建索引：

• 查询返回的结果集小

  查询返回单一记录或非常小的数据集时（例如OLTP类型查询），使用索引可以优化查询性能。

• 压缩表

  在压缩过的追加优化表上使用索引，系统只会解压必要的行，从而提升查询性能。

如何选择索引类型：

• 选择性高的列使用B-tree索引

  例如，如果一个表有1000行数据并且一个列中有800个不同的值，则该索引的选择度为0.8，索引的选择性会比较高。唯一索引的选择度总是1.0。

• 选择度低的列使用位图索引

  例如区分值区间在100至100000之间的列，位图索引表现最好。

• 表数据量大，数据物理分布有一定有序性，查询条件为<、<=、=、>=或>，过滤效果较好的情况下，使用BRIN索引

  在大数据集的情况下，BRIN索引与B-Tree索引相比，占用的空间极小，性能相同。

如何选择合适的列创建索引：

• 索引在连接中用到的列

  频繁连接的列（例如外键列）上的索引能够提升连接性能，这将让查询优化器有更多可以使用的连接方式。

• 索引在谓词中频繁使用的列

  频繁地在WHERE子句中被引用的列是索引的首选。

• 避免在频繁更新的列上建立索引

  在一个频繁更新的列上建立索引会增加该列被更新时所需要的写操作数据量。

如何更好的使用索引：

• 避免创建重叠的索引

  在多列索引中，具有相同前导列的索引冗余。

• 批量载入前删除索引

  当载入大量数据到一个表中，建议先删除索引并且在数据装载完成后重建这些索引，将会比更新索引更快。

• 测试并且比较使用索引和不使用索引的查询性能

  只有被索引列的查询性能有提升时才增加索引。

• 创建完索引，建议对表执行ANALYZE。

创建索引

您可以使用CREATE INDEX命令在表上创建索引，创建索引示例如下：

• B-Tree索引

  在employee表的gender列上创建一个B-Tree索引。

  CREATE INDEX gender_idx ON employee (gender);

• 位图索引

  在films表中的title列上创建一个位图索引。

  CREATE INDEX title_bmp_idx ON films USING bitmap (title);

• BRIN索引

  在customer表的c_custkey列上创建BRIN索引。

  CREATE INDEX c_custkey_brin_idx ON customer USING brin(c_custkey) with(pages_per_range=2);

• GIN索引

  在lineitem表的l_comment列上创建一个GIN索引，支持全文搜索（仅AnalyticDB PostgreSQL 6.0版支持）。

  CREATE INDEX lineitem_idx ON lineitem USING gin(to_tsvector('english', l_comment));

• GIN索引

  在arrayt表的intarray数组类型列上创建一个GIN索引（仅AnalyticDB PostgreSQL 6.0版支持）。

  CREATE INDEX arrayt_idx ON arrayt USING gin(intarray);

• GiST索引

  在customer表的c_comment列上创建一个GiST索引支持全文搜索（仅AnalyticDB PostgreSQL 6.0版支持）。

  CREATE INDEX customer_idx ON customer USING gist(to_tsvector('english', c_comment));

重建索引

您可以使用REINDEX INDEX命令重建索引，重建索引示例如下：

• 重建索引my_index。

  REINDEX INDEX my_index;

• 重建my_table表上的所有索引。

  REINDEX TABLE my_table;

删除索引

您可以使用DROP INDEX命令删除一个索引，删除索引示例如下：

DROP INDEX title_idx;

索引数据收集

您可以使用VACUUM命令收集索引数据，收集索引数据示例如下：

VACUUM customer;

更多信息

更多关于索引的信息，请参见Pivotal Greenplum 官方文档。