前言

  在生产业务环境中,经常由于设计不规范,导致在数据库中存储的数据体量越来越庞大,那么带来的问题就是查询效率的低下和维护任务的增加。

  在 Greenplum 数据库中,通过使用分区,可以实现大规模并行处理,并且子分区也可以拥有自己的分区,比如数据按照年份分区,再按照月份分区。在内部处理表分区的过程中,

  Greenplum 在父级表和子表之间是通过被继承实现,类似于 PostgreSQL 中的继承。

如何决定分区策略

· 表是否足够大

  通常在数据仓库中,会区分维度表和事实表,而事实表通常需要存储大量的数据,数据体量可能是百万或者数十亿条记录,对于这样的表,应该选择分区表,通过分区带来更高的性能优势。

· 查询是否满足预期

  查询数据时返回慢,那么如果在一般的优化下无法提升查询性能,那么分区是优化的良好选择。

· 查询谓词筛选

  在 WHERE 条件中,例如,倾向于按照日期筛选结果的表,建议使用分区表。

对未分区的表进行分区

--定义未分区表 tab_sales

create table tab_sales(id bigserial,gen_date date,amt decimal(10,2));

假设该表按照 gen_date 进行分区

--插入模拟数据数据(我这里使用 shell插入的模拟数据,略过)

#!/bin/bash
CONNINFO="psql -U gpadmin -d postgres -Atq -c"


for m in {1..12};do
for d in {1..28};do
$CONNINFO "insert into tab_sales(gen_date,amt) values('2020-$m-$d',10.00)"
done
done

-查询数据


postgres=# select count(*) from tab_sales;
count 
-------
   336
(1 row)

在进行分区表创建之前,备份原表

create table tab_salesbak as select * from tab_sales;

--删除原表

drop table tab_sales;

创建分区表


CREATE TABLE tab_sales (id bigserial, gen_date date, amt decimal(10,2))
DISTRIBUTED BY (id)
PARTITION BY RANGE (gen_date)
( PARTITION Jan16 START (date '2020-01-01') INCLUSIVE , 
  PARTITION Feb16 START (date '2020-02-01') INCLUSIVE ,
  PARTITION Mar16 START (date '2020-03-01') INCLUSIVE ,
  PARTITION Apr16 START (date '2020-04-01') INCLUSIVE ,
  PARTITION May16 START (date '2020-05-01') INCLUSIVE ,
  PARTITION Jun16 START (date '2020-06-01') INCLUSIVE ,
  PARTITION Jul16 START (date '2020-07-01') INCLUSIVE ,
  PARTITION Aug16 START (date '2020-08-01') INCLUSIVE ,
  PARTITION Sep16 START (date '2020-09-01') INCLUSIVE ,
  PARTITION Oct16 START (date '2020-10-01') INCLUSIVE ,
  PARTITION Nov16 START (date '2020-11-01') INCLUSIVE ,
  PARTITION Dec16 START (date '2020-12-01') INCLUSIVE 
  END (date '2021-01-01') EXCLUSIVE );

将备份表中的数据加载到新的分区表


postgres=# insert into tab_sales select * from tab_salesbak ;
INSERT 0 336

--验证分区表中是否包含数据

postgres=# select * from tab_sales_1_prt_dec16;
id  |  gen_date  |  amt  
-----+------------+-------
324 | 2020-12-16 | 10.00
318 | 2020-12-10 | 10.00
334 | 2020-12-26 | 10.00
321 | 2020-12-13 | 10.00

--验证结果,分区已经包含数据

除此之外,还有两种方式

第一种

--使用 pg_dump

备份单个表数据,如果表非常大,那么使用 pg_dump 中的 -Fc 或者 -Ft 参数进行压缩备份,还原时使用 pg_restore

[gpadmin@mdw ~]$ pg_dump -U gpadmin -t tab_sales -a postgres -f tab_sales

备份原来的表

postgres=# create table tab_salesbak as select * from tab_sales;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause. Creating a NULL policy entry.
SELECT 336

创建分区表

CREATE TABLE tab_sales (id bigserial, gen_date date, amt decimal(10,2))
DISTRIBUTED BY (id)
PARTITION BY RANGE (gen_date)
( PARTITION Jan16 START (date '2020-01-01') INCLUSIVE , 
PARTITION Feb16 START (date '2020-02-01') INCLUSIVE ,
PARTITION Mar16 START (date '2020-03-01') INCLUSIVE ,
PARTITION Apr16 START (date '2020-04-01') INCLUSIVE ,
PARTITION May16 START (date '2020-05-01') INCLUSIVE ,
PARTITION Jun16 START (date '2020-06-01') INCLUSIVE ,
PARTITION Jul16 START (date '2020-07-01') INCLUSIVE ,
PARTITION Aug16 START (date '2020-08-01') INCLUSIVE ,
PARTITION Sep16 START (date '2020-09-01') INCLUSIVE ,
PARTITION Oct16 START (date '2020-10-01') INCLUSIVE ,
PARTITION Nov16 START (date '2020-11-01') INCLUSIVE ,
PARTITION Dec16 START (date '2020-12-01') INCLUSIVE 
  END (date '2021-01-01') EXCLUSIVE );

-使用 psql 恢复数据,如果是使用 -Ft 或者 -Fc 备份的表数据,那么使用 pg_restore

[gpadmin@mdw ~]$ psql -U gpadmin -d postgres<tab_sales< div="">

-验证数据


postgres=# select * from tab_sales_1_prt_nov16;
id  |  gen_date  |  amt  
-----+------------+-------
308 | 2020-11-28 | 10.00
292 | 2020-11-12 | 10.00
295 | 2020-11-15 | 10.00

第二种

--使用 COPY

登录到数据库,COPY 表到外部文件

postgres=# COPY tab_sales  TO '/home/gpadmin/tab_sales' WITH DELIMITER '|' NULL AS '';
COPY 336

-备份原来的表

postgres=# CREATE TABLE tab_salesbak AS SELECT * FROM tab_sales;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause. Creating a NULL policy entry.
SELECT 336

删除原来的表,并创建分区表

DROP TABLE tab_sales;
CREATE TABLE tab_sales (id bigserial, gen_date date, amt decimal(10,2))
DISTRIBUTED BY (id)
PARTITION BY RANGE (gen_date)
( PARTITION Jan16 START (date '2020-01-01') INCLUSIVE , 
PARTITION Feb16 START (date '2020-02-01') INCLUSIVE ,
PARTITION Mar16 START (date '2020-03-01') INCLUSIVE ,
PARTITION Apr16 START (date '2020-04-01') INCLUSIVE ,
PARTITION May16 START (date '2020-05-01') INCLUSIVE ,
PARTITION Jun16 START (date '2020-06-01') INCLUSIVE ,
PARTITION Jul16 START (date '2020-07-01') INCLUSIVE ,
PARTITION Aug16 START (date '2020-08-01') INCLUSIVE ,
PARTITION Sep16 START (date '2020-09-01') INCLUSIVE ,
PARTITION Oct16 START (date '2020-10-01') INCLUSIVE ,
PARTITION Nov16 START (date '2020-11-01') INCLUSIVE ,
PARTITION Dec16 START (date '2020-12-01') INCLUSIVE 
  END (date '2021-01-01') EXCLUSIVE );

--加载数据到新的分区表


postgres=# COPY tab_sales FROM '/home/gpadmin/tab_sales' WITH DELIMITER '|' NULL AS '';
COPY 336

-验证数据

postgres=# select * from tab_sales_1_prt_jul16 limit 5;
id  |  gen_date  |  amt  
-----+------------+-------
175 | 2020-07-07 | 10.00
191 | 2020-07-23 | 10.00
172 | 2020-07-04 | 10.00
188 | 2020-07-20 | 10.00
169 | 2020-07-01 | 10.00

结语

  以上就是普通表拆分为分区表的方式,建议在设计表时,对存储的数据进行了解。