Greenplum性能优化之路 --(一)分区表

说明

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

什么是分区表

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

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

partitions.jpg

与分布的区别

分布:DISTRIBUTED

分区:PARTITION

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

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

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

什么时候使用分区表

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

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

创建分区表

Greenplum支持三种分区类型:

  1. 范围分区(Range Partition)
  2. 列表分区(List Partition)
  3. 组合分区(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);

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

查询分区

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

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性能优化之路 --(二)存储格式

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

编辑于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏北京马哥教育

五分钟 SQL Server 学习入门——基本篇

? 作者:My_heart_ 来源:http://blog.csdn.net/my_heart_/article/details/62425140 首先相信...

41340
来自专栏智能合约

数据插入失败引发的主键auto_increment问题

36130
来自专栏程序你好

MySQL中MyISAM与InnoDB存储的区别

12910
来自专栏技术博文

从MyISAM转到InnoDB需要注意什么

转自 MySql中文网 http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ==&mid=200910426&idx=1...

374140
来自专栏北京马哥教育

数据库基础知识:数据库中的约束和三大范式

? 一.数据库中的范式: 范式, 英文名称是 Normal Form,它是英国人 E.F.Codd(关系数据库的老祖宗)在上个世纪70年代提出关系数据库模型后...

32770
来自专栏逸鹏说道

维护索引(1)——索引碎片

前言: DBA的日常任务并不仅仅是创建需要的索引在对应的列上,实际上,DBA还要保持索引创建的高标准。 周而复始,DBA必须盯着一些非常重要的信息: 1、 索引...

35870
来自专栏大数据钻研

MySQL分页性能优化指南

一道面试的问题,当MySQL表中有数据量很大的时候如何做分页。。。。当时只知道在数据量很大的时候可以分表,但不知道不分表时可以怎么做。。。。唉,谁让代理商就那么...

36080
来自专栏xingoo, 一个梦想做发明家的程序员

oracle数据结构

 数据类型: 1 字符数据:CHAR VARCHAR NCHAR NVARCHAR2 LONG CLOB NCLOB  2 数字数据类型:NUMBER 唯一用来...

22160
来自专栏java一日一条

MySQL分页性能优化指南

很多应用往往只展示最新或最热门的几条记录,但为了旧记录仍然可访问,所以就需要个分页的导航栏。然而,如何通过MySQL更好的实现分页,始终是比较令人头疼的问题。虽...

19830
来自专栏java学习

oracle数据库的常用的命令操作!

Java基础 | 数据库 | Android | 学习视频 | 学习资料下载 最新通知 数据库技术是计算机科学技术的一个重要分支。从20世纪50年代中期开始,...

33070

扫码关注云+社区

领取腾讯云代金券