greenplum 分区表管理 greenplum 分区表和分布键

继上一篇GreenPlum的文章之后，过了挺长的一段时间（快1年了）

上一篇文章其实有点像杂记，有点散，没什么主题，都是一些测试和工作中的实际运用的记录

后面打算，稍微整理下第一篇文章，把一些基础知识和性能优化剥离开来，专门写一篇（工作中用到的）性能优化

 

这篇文章来说说两个东西，一个是分区表，一个是外部表

分区表：

分区其实是很多数据库的一种数据放置的优化。

就好像你将一个张桌子上的文件按类别堆叠起来一样，分区就是将数据按照某个字段的值，放到不同的子表里面（或者放到不同的路径下），这样你要查询的时候，就可以加快速度，比如分区按照月份分割，那要查12月份数据的时候，只要到12月份的那个子表（或者路径）上去查询就可以，并不需要全表的扫描，这在大数据领域是一个非常好的优化方式。

GreenPlum的分区，其实就是自动创建相关联的字表！

 

1、范围分区（range）

根据分区字段的值范围区间来分区，每一个分区就是一个子表。

一般情况下，我们会按照月份来做一个分区，但是还是要根据自己每个分区中的数据量来决定！（我们的集群）大概五千万以内的数据量，查询起来是比较快的，如果大于这个值，上到了亿级别，查询就很影响性能了。所以每个分区的数据要在千万级别。

使用方式：

用户可以通过给出一个START值、一个END值以及一个定义分区增量值的子句让Greenplum数据库自动产生分区。

默认情况下，START值总是被包括在内而END值总是被排除在外。

例如（结果如图）：
 

--创建月分区表，date_id格式为201901,201902
CREATE TABLE "数据库"."table_test" (
  "user_id" VARCHAR(52),
	"key" VARCHAR(52),
  "date_id" int4,
  "gmt_modified" timestamptz(6) DEFAULT now()
)WITH (APPENDONLY=true, COMPRESSLEVEL=1, ORIENTATION=column, COMPRESSTYPE=rle_type)
DISTRIBUTED by(user_id)
partition by range (date_id) 
(
    partition "2019_" start (201901) end (201913) every (1),
    default partition def
)

--如下是运行成功的返回信息
> NOTICE:  CREATE TABLE will create partition "table_test_1_prt_def" for table "table_test"
  
  NOTICE:  CREATE TABLE will create partition "table_test_1_prt_2019__1" for table "table_test"
  
  NOTICE:  CREATE TABLE will create partition "table_test_1_prt_2019__2" for table "table_test"
  
  NOTICE:  CREATE TABLE will create partition "table_test_1_prt_2019__3" for table "table_test"
  
  NOTICE:  CREATE TABLE will create partition "table_test_1_prt_2019__4" for table "table_test"
  
  NOTICE:  CREATE TABLE will create partition "table_test_1_prt_2019__5" for table "table_test"
  
  NOTICE:  CREATE TABLE will create partition "table_test_1_prt_2019__6" for table "table_test"
  
  NOTICE:  CREATE TABLE will create partition "table_test_1_prt_2019__7" for table "table_test"
  
  NOTICE:  CREATE TABLE will create partition "table_test_1_prt_2019__8" for table "table_test"
  
  NOTICE:  CREATE TABLE will create partition "table_test_1_prt_2019__9" for table "table_test"
  
  NOTICE:  CREATE TABLE will create partition "table_test_1_prt_2019__10" for table "table_test"
  
  NOTICE:  CREATE TABLE will create partition "table_test_1_prt_2019__11" for table "table_test"
  
  NOTICE:  CREATE TABLE will create partition "table_test_1_prt_2019__12" for table "table_test"
  

--创建月分区表，date_id格式为20190101--20190131会放到同一个分区里，以此类推
--注意我的end写的是20191299，
--如果写的是20191231，那么20191231的数据就会被放到def的分区里面

CREATE TABLE "数据库"."table_test" (
  "user_id" VARCHAR(52),
	"key" VARCHAR(52),
  "date_id" int4,
  "gmt_modified" timestamptz(6) DEFAULT now()
)WITH (APPENDONLY=true, COMPRESSLEVEL=1, ORIENTATION=column, COMPRESSTYPE=rle_type)
DISTRIBUTED by(user_id)
partition by range (date_id) 
(
    partition "2019_" start (20190101) end (20191299) every (100),
    default partition def
)

也可以将 every(1) 改成every(2) 来实现将2个月份放入同一个分区

 

2、列表分区（list）

根据值的分组，相同的数据归类到一组，也就一个分区中。
例如：

CREATE TABLE rank (id int, rank int, year int, gender 
char(1), count int ) 
DISTRIBUTED BY (id)
PARTITION BY LIST (gender)
( PARTITION girls VALUES ('F','D'), 
  PARTITION boys VALUES ('M'), 
  DEFAULT PARTITION other );

 

3、对分区的增删（一般不会修改分区，但是其实好像是有split的操作的，大家自行百度）

 

-1、split我百度了下，好像用法如下：

如下这段sql皆为引用，具体我还未测试，因为这种操作我们好像基本不用

原文地址：https://blog.51cto.com/13126942/2053712

create table test_partition_list 
 (
     id int, 
     name varchar(64), 
     fdate varchar(10)
 ) 
 distributed by (id) 
 partition by list (fdate) 
 (
     partition p1 values ('2017-01-01', '2017-01-02'), 
     partition p2 values ('2017-01-03'), 
     default partition pd
 );

将分区p2 在 '2017-02-20' 左右切分成两块
 alter table test_partition_range split partition p2 at ('2017-02-20') into (partition p2, partition p3); 

切割默认分区：
alter table test_partition_range split default partition start ('2017-03-01')  end ('2017-03-31')  into (partition p4, default partition);
 

-2、增加分区

如果存在default partition，则不能add分区，否则会报错

ALTER TABLE "rank" ADD PARTITION aa VALUES('B')
> ERROR:  cannot add LIST partition "aa" to relation "rank" with DEFAULT partition "other"
  HINT:  need to SPLIT partition "default"

只能split default partition

比如2020年了，我想要增加2020年的月份的分区

alter table 表名 split default partition start (202001) end (202002) into (partition "2020_1", default partition);

 

-3、删除分区

alter table 表名 DROP partition 分区名称;
 

-4、清空分区数据

清空分区表数据，相当于删除分区，然后再新建一个

alter table 表名 truncate partition 分区名称;

 

4、多级分区

-1、创建

例子：在date的基础上，再多做一级分区是归属地（region），并且设置了开始1月，结束4月，every跨度为1个月

CREATE TABLE sales (trans_id int, date date, amount decimal(9,2), region text)
  DISTRIBUTED BY (trans_id)
  PARTITION BY RANGE (date)
  SUBPARTITION BY LIST (region)
  SUBPARTITION TEMPLATE
    ( SUBPARTITION usa VALUES ('usa'),
      SUBPARTITION asia VALUES ('asia'),
      SUBPARTITION europe VALUES ('europe'),
      DEFAULT SUBPARTITION other_regions )
  ( START (date '2014-01-01') INCLUSIVE
    END (date '2014-04-01') EXCLUSIVE
    EVERY (INTERVAL '1 month') );

 

还有查询该数据库中的所有的分区表的信息：

select * from pg_partitions

总结：

前面讲了好几种方式来处理数据的分区，其实最经常用的还是按照时间分区（因为更多业务需求也是按照天或者月来查询数据）。

1、如果是月表，一般就按照月份分区

表名类似   aggr_XXXX_2019，然后后面的分区就是1，2，3（类似本文的第一个例子）

2、如果是日表，一般按照日分区（做二级分区的操作比较少见，就是先做一级月份，再做一级日期）

表名类似  aggr_XXXX_2019，然后后面的分区就是0611,0612

但是一般情况下，在gp中的日表不需要分区，当然还是要视情况而定，如果查询的性能无法满足业务需求的时候，那么就需要考虑做一些优化，分区是比较常见的手段之一。

 这篇文章其实挺早就开始编辑了，但是最近公司比较忙，一直拖到现在才完成（以后还是要多敦促自己），好了还是老话，菜鸡一个，如果有什么疑问或者误导了新人，还请大家批评指出，本人坚决改正~~谢谢