分区表定义_定义数据库对象_用户指南_分析型数据库PostgreSQL版-阿里云

支持的表分区类型

范围（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 );

创建多级分区表

支持创建多级的分区表。下述建表语句创建了具有三级表分区的表。一级分区在year字段上使用RANGE分区，二级分区在month字段上做RANGE分区，三级分区在region上做了LIST分区。

CREATE TABLE sales (id int, year int, month int, day int, 
region text)
DISTRIBUTED BY (id)
PARTITION BY RANGE (year)            
  SUBPARTITION BY RANGE (month)     
  SUBPARTITION TEMPLATE (
       START (1) END (13) EVERY (1), 
       DEFAULT SUBPARTITION other_months )
  SUBPARTITION BY LIST (region)    
    SUBPARTITION TEMPLATE (
       SUBPARTITION usa VALUES ('usa'),
       SUBPARTITION europe VALUES ('europe'),
       SUBPARTITION asia VALUES ('asia'),
       DEFAULT SUBPARTITION other_regions)
( START (2008) END (2016) EVERY (1),
  DEFAULT PARTITION outlying_years);

分区定义的粒度

通常分区表的定义都涉及到粒度问题，比如按时间分区，究竟是按天，按周，按月等。粒度越细，每张表的数据就越少，但是分区的数量就越多，反之亦然。关于分区的数量，没有绝对的标准，一般分区的数量在 200 左右已经算是比较多了。分区表数目过多，会有多方面的影响，比如查询优化器生成执行计划较慢，同时很多维护工作也会变慢，比如VACUUM等。

注意

请对多级分区格外谨慎，因为分区文件的数量可能会增长得非常快。例如，如果一个表被按照月和城市划分并且有24个月以及1,00个城市，那么表分区的总数就是2400。特别对于列存表，会把每一列存在一个物理表中，因此如果这个表有100个列，系统就需要为该表管理十多万个文件。

分区表查询优化

AnalyticDB for PostgreSQL 支持分区表的分区裁剪功能，根据查询条件会只扫描所需的数据分区而避免扫描整个表的全部内容，提升查询性能。例如对于如下查询：

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

查询分析表定义

可以通过如下 SQL 语句查询一个分区表的所有分区定义信息：

SELECT 
  partitionboundary, 
  partitiontablename, 
  partitionname,
  partitionlevel, 
  partitionrank
FROM pg_partitions 
WHERE tablename='sales';