Greenplum性能优化之路 --（一）分区表

原创

lambgong

修改于 2020-07-27 21:20:27

22.3K0

修改于 2020-07-27 21:20:27

文章被收录于专栏：云数据仓库 PostgreSQL

说明

本文描述问题及解决方法同样适用于 腾讯云云数据仓库 CDW。

什么是分区表

分区表就是将一个大表在物理上分割成若干小表，并且整个过程对用户是透明的，也就是用户的所有操作仍然是作用在大表上，不需要关心数据实际上落在哪张小表里面。Greenplum中分区表的原理和PostgreSQL一样，都是通过表继承和约束实现的。

Greenplum官方给出的分区表示例如下：

与分布的区别

分布：DISTRIBUTED

分区：PARTITION

Greenplum中每个表都需要有一个分布键，如果你建表的时候没有显示使用语法DISTRIBUTED BY (column) 指定一个分布键，系统也会默认为你指定一个。分布目的是把数据打散到每个节点，打散的规则是hash或者randomly。这样在计算时可以充分利用每个节点的资源进行并行计算。

分区特性在本文会详细介绍，两者比较如下：

数据分布是在物理上拆分表数据，将数据打散到各个节点，使数据可以并行计算，这在Greenplum中是必须的。
表分区是在逻辑上拆分大表的数据提高查询性能，也有利于数据生命周期的管理，这在Greenplum中是可选的。
无论是分区表还是非分区表，在Greenplum中，数据都是分散到各个节点上的。
分区不会影响数据在各个节点上的分布情况。

什么时候使用分区表

是否使用分区表，可以通过以下几个方面进行考虑：

表数据量是否足够大：通常对于大的事实表，比如数据量有几千万或者过亿，我们可以考虑使用分区表，但数据量大小并没有一个绝对的标准可以使用，一般是根据经验，以及对目前性能是否满意。
表是否有合适的分区字段：如果数据量足够大了，这个时候我们就需要看下是否有合适的字段能够用来分区，通常如果数据有时间维度，比如按天，按月等，是比较理想的分区字段。
表内数据是否具有生命周期：通常数仓中的数据不可能一直存放，一般都会有一定的生命周期，比如最近一年等，这里就涉及到对旧数据的管理，如果有分区表，就很容易删除旧的数据，或者将旧的数据归档到对象存储等更为廉价的存储介质上。
查询语句中是否含有分区字段：如果你对一个表做了分区，但是所有的查询都不带分区字段，这不仅无法提高性能反而会使性能下降，因为所有的查询都会扫描所有的分区表。

创建分区表

Greenplum支持三种分区类型：

范围分区（Range Partition）
列表分区（List Partition）
组合分区（A combination of both types）

范围分区例子：

CREATE TABLE test_range_partition
(
    uid int, 
    fdate character varying(32)
)
PARTITION BY RANGE(fdate) 
(
        PARTITION p1 START ('2018-11-01') INCLUSIVE END ('2018-11-02') EXCLUSIVE, 
        PARTITION p2 START ('2018-11-02') INCLUSIVE END ('2018-11-03') EXCLUSIVE, 
        DEFAULT PARTITION pdefault
);

以上例子是按天建表，如果时间跨度比较大，会导致建表语句很长，书写起来也不方便，这时候可以使用以下语法：

CREATE TABLE test_range_partition_every_1 
(
    uid int, 
    fdate date
) 
partition by range (fdate) 
(
    PARTITION pn START ('2018-11-01'::date) END ('2018-12-01'::date) EVERY ('1 day'::interval), 
    DEFAULT PARTITION pdefault
);

列表分区例子：

CREATE TABLE test_list_partition
(
	uid int, 
	gender char(1)
)
PARTITION BY LIST (gender)
(
	PARTITION girls VALUES ('F'),
	PARTITION boys VALUES ('M'),
	DEFAULT PARTITION pdefault
);

多级分区例子：

CREATE TABLE test_muti_level_partition
(
	uid int, 
	gender char(1),
	fdate character varying(32)
)
PARTITION BY RANGE(fdate) 
SUBPARTITION BY LIST(gender)
SUBPARTITION template
(
	SUBPARTITION s1 VALUES ('F'),
	SUBPARTITION s2 VALUES ('M')
)
(
	PARTITION p1 START ('2018-11-01') INCLUSIVE END ('2018-11-02') EXCLUSIVE,
	PARTITION p2 START ('2018-11-02') INCLUSIVE END ('2018-11-03') EXCLUSIVE,
	DEFAULT PARTITION pdefault
)

管理分区表

分区表也是一张表，所以对于表的很多操作也可以作用于分区表上，这里列举了常用的一些操作：

清空子分区

ALTER TABLE test_range_partition TRUNCATE PARTITION p1;

删除子分区

ALTER TABLE test_range_partition DROP PARTITION p1;

注：DROP PARTITION 之后跟的是partition name，而不是partition table name，这两者之间是有区别的，如果是使用EVERY语法创建的分区表，你需要通过pg_partitions表查询到对应分区的partition name。

新增子分区

ALTER TABLE test_range_partition ADD PARTITION p3 START ('2018-11-03') INCLUSIVE END ('2018-11-04') EXCLUSIVE;

注：如果分区表中含有DEFAULT分区，会出现如下错误，解决办法可以参见滚动分区：

ERROR: cannot add RANGE partition "p3" to relation "test_range_partition" with DEFAULT partition "pdefault"

滚动分区

通常按时间分区的表，都有一个特性，就是分区会不断往前滚动，比如一个按天分区，保存最近10天的分区表，每到新一天，就会要删除10天前的分表表，并且创建一个新的分区表容纳最新的数据。

如果是含有默认分区的，可以使用分区Split

ALTER TABLE test_range_partition SPLIT DEFAULT PARTITION START ('2018-11-03') INCLUSIVE END ('2018-11-04') EXCLUSIVE INTO (PARTITION p3, DEFAULT partition);

这样新分区就被添加，同时保留了默认分区，然后在删除老的分区就完成新老分区的更替。

交换分区

交换分区就是将一张普通的表和某张分区表进行交换，这个功能在数据分层存储十分有用。

比如我们会需要根据对象存储的不同目录设置分区，这个需求就可以使用交换分区完成，这样对于一张大表，他的较少查询的历史数据就可以放在对象存储上，语法如下：

ALTER TABLE {table_name} EXCHANGE PARTITION {partition_name|FOR (RANK(number))|FOR (value)} WITH TABLE {cos_table_name} WITHOUT VALIDATION;

查询分区

与分区相关的系统表或者视图如下：

pg_partition

pg_partition_columns

pg_partition_encoding

pg_partition_rule

pg_partition_templates

pg_partitions

查看分区基本信息：

t2=# select * from pg_partitions where partitiontablename = 'test_range_partition_1_prt_p1';
-[ RECORD 1 ]------------+---------------------------------------------------------------------------------------------------
schemaname               | public
tablename                | test_range_partition
partitionschemaname      | public
partitiontablename       | test_range_partition_1_prt_p1
partitionname            | p1
parentpartitiontablename | 
parentpartitionname      | 
partitiontype            | range
partitionlevel           | 0
partitionrank            | 1
partitionposition        | 2
partitionlistvalues      | 
partitionrangestart      | '2018-11-01'::character varying(32)
partitionstartinclusive  | t
partitionrangeend        | '2018-11-02'::character varying(32)
partitionendinclusive    | f
partitioneveryclause     | 
partitionisdefault       | f
partitionboundary        | PARTITION p1 START ('2018-11-01'::character varying(32)) END ('2018-11-02'::character varying(32))
parenttablespace         | pg_default
partitiontablespace      | pg_default

查看分区定义

t2=# select pg_get_partition_def('test_range_partition'::regclass,true);
-[ RECORD 1 ]--------+---------------------------------------------------------------------------------------------------------------
pg_get_partition_def | PARTITION BY RANGE(fdate) 
                     |           (
                     |           PARTITION p1 START ('2018-11-01'::character varying(32)) END ('2018-11-02'::character varying(32)), 
                     |           PARTITION p2 START ('2018-11-03'::character varying(32)) END ('2018-11-04'::character varying(32)), 
                     |           DEFAULT PARTITION pdefault 
                     |           )

查询优化

分区表很大的用途在于提升分析性能，但并不是对大表进行分区就能简单的提升性能，也不是分区越多性能越好。

分区的粒度

通常像范围分区的表都涉及到粒度问题，比如按时间分表，究竟是按天，按周，按月等。粒度越细，每张表的数据就越少，但是分区表的数量就会越多，反之亦然。

关于分区表的数量，这里没有绝对的标准，一般来说分区表的数量在100左右已经算是比较多了。

分区表数目过多，会有多方面的影响，比如查询优化器生成执行计划较慢，同时很多维护工作也都会变慢，比如vacuum，recovering segment，expanding the cluster, checking disk usage等。

查询语句

为了充分利用分区表的优势，需要在查询语句中尽量带上分区条件。最终目的是扫描尽量少的分区表。

如下是一个静态分区消除的例子，可以看出Partitions selected: 11 (out of 15)，这里在15张分区表中选择了其中11张

t2=# explain select * from test_range_partition_every_1 where fdate >= '2018-11-05';
                                                         QUERY PLAN                                     
                    
--------------------------------------------------------------------------------------------------------
--------------------
 Gather Motion 2:1  (slice1; segments: 2)  (cost=0.00..431.00 rows=1 width=8)
   ->  Sequence  (cost=0.00..431.00 rows=1 width=8)
         ->  Partition Selector for test_range_partition_every_1 (dynamic scan id: 1)  (cost=10.00..100.
00 rows=50 width=4)
               Filter: fdate >= '2018-11-05'::date
               Partitions selected: 11 (out of 15)
         ->  Dynamic Table Scan on test_range_partition_every_1 (dynamic scan id: 1)  (cost=0.00..431.00
 rows=1 width=8)
               Filter: fdate >= '2018-11-05'::date
 Optimizer status: PQO version 2.55.13
(8 rows)

注：Greenplum最新一代的解析引擎ORCA是支持动态分区消除的，但是分区的选择并不会打印在执行计划中。

以下是官网的说明：

For queries that involve dynamic partition selection where the partitioning key is compared to a variable, the number of partitions that are scanned will be known only during query execution. The partitions selected are not shown in the EXPLAIN output.

从Redshift迁移到Snova

使用过Redshift的朋友都知道，Redshift是不支持分区表的，AWS官方建议使用sort key和distribution key来优化并行处理，官方建议如下：

CREATE TABLE Amazon Redshift does not support tablespaces, table partitioning, inheritance, and certain constraints. The Amazon Redshift implementation of CREATE TABLE enables you to define the sort and distribution algorithms for tables to optimize parallel processing. Amazon Redshift Spectrum supports table partitioning using the CREATE EXTERNAL TABLE command.

但是涉及到数据生命周期管理，Redshift通常的做法是每个分区创建不同的表，而在所有表的基础上创建一个视图来管理这些表，仿造出一个分区的特性，这无疑是低效的。因此从Redshift迁移过来的用户建议在合适的场景下使用分区特性。

欢迎阅读GP性能优化系列，下一篇Greenplum性能优化之路 --（二）存储格式

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

TDSQL MySQL 版