将大表定义为分区表,从而将其分成较小的存储单元,根据查询条件,会只扫描满足条件的分区而避免全表扫描,从而显著提升查询性能。
支持的表分区类型
- 范围(RANGE)分区:基于一个数值型范围划分数据,例如按着日期区间定义。
- 值(LIST)分区:基于一个值列表划分数据,例如按着 城市属性定义。
- 多级分区表:上述两种类型的多级组合。

上图为一个多级分区表设计实例,一级分区采用按月的区间(Range)分区,二级分区采用按地区的值(List)分区设计。
创建范围(RANGE)分区表
用户可以通过给出一个START值、一个END值以及一个定义分区增量值的子句让数据库自动产生分区。默认情况下,START值总是被包括在内而END值总是被排除在外,例如:
CREATE TABLE sales (id int, date date, amt decimal(10,2))
DISTRIBUTED BY (id)
PARTITION BY RANGE (date)
( START (date '2016-01-01') INCLUSIVE
END (date '2017-01-01') EXCLUSIVE
EVERY (INTERVAL '1 day') );
也可以创建一个按数字范围分区的表,使用单个数字数据类型列作为分区键列,例如:
CREATE TABLE rank (id int, rank int, year int, gender char(1), count int)
DISTRIBUTED BY (id)
PARTITION BY RANGE (year)
( START (2006) END (2016) EVERY (1),
DEFAULT PARTITION extra );
创建值(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'),
PARTITION boys VALUES ('M'),
DEFAULT PARTITION other );
创建多级分区表
CREATE TABLE sales
(id int, year int, month int, day int, region text)
DISTRIBUTED BY (id)
PARTITION BY RANGE (month)
SUBPARTITION BY LIST (region)
SUBPARTITION TEMPLATE (
SUBPARTITION usa VALUES ('usa'),
SUBPARTITION europe VALUES ('europe'),
SUBPARTITION asia VALUES ('asia'),
DEFAULT SUBPARTITION other_regions)
(START (1) END (13) EVERY (1),
DEFAULT PARTITION other_months );
分区定义的粒度
通常分区表的定义都涉及到粒度问题,比如按时间分区,究竟是按天,按周,按月等。粒度越细,每张表的数据就越少,但是分区的数量就越多,反之亦然。关于分区的数量,没有绝对的标准,一般分区的数量在 200 左右已经算是比较多了。分区表数目过多,会有多方面的影响,比如查询优化器生成执行计划较慢,同时很多维护工作也会变慢,比如VACUUM等。
请对多级分区格外谨慎,因为分区文件的数量可能会增长得非常快。例如,如果一个表被按照月和城市划分并且有24个月以及1,00个城市,那么表分区的总数就是2400。特别对于列存表,会把每一列存在一个物理表中,因此如果这个表有100个列,系统就需要为该表管理十多万个文件。
分区表查询优化
explain
select * from sales
where year = 2008
and month = 1
and day = 3
and region = 'usa';
由于查询条件落在一级分区2008的二级子分区1的三级子分区 'usa' 上,查询只会扫描读取这一个三级子分区数据。如下其查询计划显示,总计468个三级子分区中,只有一个分区被读取。
Gather Motion 4:1 (slice1; segments: 4) (cost=0.00..431.00 rows=1 width=24)
-> Sequence (cost=0.00..431.00 rows=1 width=24)
-> Partition Selector for sales (dynamic scan id: 1) (cost=10.00..100.00 rows=25 width=4)
Filter: year = 2008 AND month = 1 AND region = 'usa'::text
Partitions selected: 1 (out of 468)
-> Dynamic Table Scan on sales (dynamic scan id: 1) (cost=0.00..431.00 rows=1 width=24)
Filter: year = 2008 AND month = 1 AND day = 3 AND region = 'usa'::text
查询分区表定义
SELECT
partitionboundary,
partitiontablename,
partitionname,
partitionlevel,
partitionrank
FROM pg_partitions
WHERE tablename='sales';
分区表维护
分区表支持多种分区管理操作,包括新增分区,删除分区,重命名分区,清空截断分区,交换分区,分裂分区等,详情请参考Greenplum 官方文档。
在文档使用中是否遇到以下问题
更多建议
匿名提交