用HAWQ轻松取代传统数据仓库(八) —— 大表分区

1、用HAWQ轻松取代传统数据仓库（八） 大表分区一、HAWQ中的分区表 与大多数关系数据库一样，HAWQ也支持分区表。这里所说的分区表是指HAWQ的内部分区表，外部分区表在后面“外部数据”篇讨论。在数据仓库应用中，事实表通常有非常多的记录，分区可以将这样的大表在逻辑上分为小的、更易管理的数据片段。HAWQ的优化器支持分区消除以提高查询性能。只要查询条件中可以使用分区键作为过滤条件，那么HAWQ只需要扫描满足查询条件的分区，而不必进行全表扫描。 分区并不改变表数据在segment间的物理分布。表的分布是物理的，无论是分区表还是非分区表，HAWQ都会在segment上物理地分布数据，并且并行处理查询

2、。而表的分区是逻辑上的，HAWQ逻辑分隔大表以提高查询性能和数据仓库应用的可维护性。例如，将老的分区数据从数据仓库转储或移除，并建立新的数据分区等。HAWQ支持以下分区类型：范围分区：基于数字范围分区，如日期、价格等。列表分区：基于列表值分区，如销售区域、产品分类等。两者混合的分区类型。 图1是一个混合类型分区表的例子，sales表以销售日期范围作为主分区，而以销售区域作为一个日期分区中的列表子分区键。注意，HAWQ并没提供类似Oracle的在线重定义功能，它只能使用CREATE TABLE命令创建分区表，而没有简单的命令能够将一个非分区表转化成分区表。最好在建表之前就规划好分区方式和维护方法

3、，因为当一个非分区表已经存在大量数据后再改作分区表的操作，时间和空间消耗上都是很棘手的问题。在CREATE TABLE命令中使用PARTITION BY或可选的SUBPARTITION BY子句建立分区。上级分区可以包含一个或多个下级分区。HAWQ内部创建上下级分区之间的层次关系。分区条件定义一个分区内可以包含的数据。在建立分区表时，HAWQ为每个分区条件创建一个唯一的CHECK约束，限制一个分区所能含有的数据，保证各个分区中数据的互斥性。查询优化器利用该CHECK约束，决定扫描哪些分区以满足查询谓词条件。 HAWQ在系统目录中存储分区的层次信息，因此插入到分区表中的行可以正确传递到子分区中。

4、ALTER TABLE命令的PARTITION子句用于修改分区表结构。 在向分区表插入数据时，可以在INSERT命令中指定表的根分区或叶分区。如果数据对于指定的叶分区无效，将返回错误。INSERT命令不支持向非叶分区的子分区中插入数据。二、确定分区策略 并不是所有表都适合分区，需要进行实测以保证所期望的性能提升。下面是一些通用的分区指南，如果对以下问题的大部分答案是肯定的，分区表对于提高性能是可行的数据库设计。否则，表不适合分区。表是否足够大？按照一般的经验，至少千万记录以上的表才算大表。数据仓库中的事实表适合作为分区表。对于小于这个数量级的表通常不需要分区。因为系统管理与维护分区的开销会抵消

5、掉分区带来的可见的性能优势。性能是否不可接受？只有当实施了其它优化手段后，响应时间仍然不可接受时，再考虑使用分区。查询谓词条件中是否包含适合的分区键？检查查询的WHERE子句中是否包含适合作为分区的条件。例如，如果大部分查询都通过日期检索数据，那么按照月或周做范围分区可能是有益的。是否需要维护一个数据仓库的历史数据窗口？例如，组织中的数据仓库只需要保持过去12个月的数据，那么按月分区，就可以很容易地删除最老月份的分区，并向最新的月分区中装载当前数据。根据分区定义条件，是否每个分区的数据量比较平均？分区条件应尽可能使数据平均划分。如果每个分区包含基本相同的记录数，性能会有所提升。例如，将一个大表

6、分成10个相等的分区，如果查询条件中带有分区键，那么理论上查询应该比非分区表快将近10倍。 使用分区还要注意以下问题。首先，不要创建多余的分区。太多的分区将会减慢管理和维护任务，如检查磁盘使用、集群扩展、释放剩余空间等。其次，只有在查询条件可以利用分区消除时，性能才会得到提升。否则，一个需要扫描所有分区的查询会比非分区表还慢。可以通过查看一个查询的执行计划（explain plan）确认是否用到了分区消除。最后是关于多级分区的问题。多级分区会使分区文件的数量快速增长。例如，如果一个表按日期和城市做分区，1000天的1000个城市的数据，就会形成100万个分区。假设表有100列，并且假设表使用面

7、向列的物理存储格式，那么系统为此表需要管理1亿个文件。三、创建分区表 如前所述，创建分区表需要定义分区键、分区类型、分区层次。下面是几个创建分区表的例子。1. 定义日期范围分区表 在定义日期分区表时，可以考虑以可接受的细节粒度做分区。例如，相对于以月份做主分区，日期做子分区的分区策略，每个日期一个分区，一年365个分区的方案可能更好。多级分区可以降低生成查询计划的时间，但平面化的分区设计运行地更快。sql view plain copy 在CODE上查看代码片派生到我的代码片create table sales (id int, date date, amt decimal(10,2) dis

8、tributed by (id) partition by range (date) ( start (date '2017-01-01') inclusive end (date '2017-02-01') exclusive every (interval '1 day') ); 上面的语句以date列作为分区键，从2017年1月1月到2017年2月1日，每天一个分区，将建立31个分区。分区对应表对象的名称分别是sales_1_prt_1 . sales_1_prt_31。注意inclusive表示分区中包含定义的分区键值，exclusive

9、表示不包含。例如，sales_1_prt_1包含date >= (date '2017-01-01') and date < (date '2017-01-02')的数据，sales_1_prt_31包含date >= (date '2017-01-31') and date < (date '2017-02-01')的数据，即这个语句定义的分区是左闭右开的数据区间。sql view plain copy 在CODE上查看代码片派生到我的代码片db1=# insert into sales values (

10、1, (date '2016-12-31'),100); ERROR: no partition for partitioning key (seg21 hdp4:40000 pid=60186) db1=# insert into sales values (1, (date '2017-01-01'),100); INSERT 0 1 db1=# insert into sales values (1, (date '2017-02-01'),100); ERROR: no partition for partitioning key (se

11、g23 hdp4:40000 pid=60190) db1=# insert into sales values (1, (date '2017-01-31'),100); INSERT 0 1 同样可以定义左开右闭的分区。sql view plain copy 在CODE上查看代码片派生到我的代码片create table sales (id int, date date, amt decimal(10,2) distributed by (id) partition by range (date) ( start (date '2017-01-01') ex

12、clusive end (date '2017-02-01') inclusive every (interval '1 day') ); db1=# insert into sales values (1, (date '2017-01-01'),100); ERROR: no partition for partitioning key (seg19 hdp4:40000 pid=60182) db1=# insert into sales values (1, (date '2017-01-02'),100); INSERT

13、 0 1 db1=# insert into sales values (1, (date '2017-01-31'),100); INSERT 0 1 db1=# insert into sales values (1, (date '2017-02-01'),100); INSERT 0 1 db1=# insert into sales values (1, (date '2017-02-02'),100); ERROR: no partition for partitioning key (seg23 hdp4:40000 pid=602

14、69) 也可以显式定义每个分区。sql view plain copy 在CODE上查看代码片派生到我的代码片create table sales (id int, date date, amt decimal(10,2) distributed by (id) partition by range (date) ( partition p201701 start (date '2017-01-01') inclusive , partition p201702 start (date '2017-02-01') inclusive , partition p2

15、01703 start (date '2017-03-01') inclusive , partition p201704 start (date '2017-04-01') inclusive , partition p201705 start (date '2017-05-01') inclusive , partition p201706 start (date '2017-06-01') inclusive , partition p201707 start (date '2017-07-01') incl

16、usive , partition p201708 start (date '2017-08-01') inclusive , partition p201709 start (date '2017-09-01') inclusive , partition p201710 start (date '2017-10-01') inclusive , partition p201711 start (date '2017-11-01') inclusive , partition p201712 start (date '2

17、017-12-01') inclusive end (date '2018-01-01') exclusive ); 以上语句为2017年每个月建立一个分区。注意，不需要问每个分区指定END值，只要在最后一个分区（本例中的p201712）指定END值即可。2. 定义数字范围分区表plain view plain copy 在CODE上查看代码片派生到我的代码片db1=# create table rank (id int, rank int, year int, gender db1(# char(1), count int) db1-# distributed by

18、(id) db1-# partition by range (year) db1-# ( start (2017) end (2018) every (1), db1(# default partition extra ); NOTICE: CREATE TABLE will create partition "rank_1_prt_extra" for table "rank" NOTICE: CREATE TABLE will create partition "rank_1_prt_2" for table "rank

19、" CREATE TABLE db1=# dt List of relations Schema | Name | Type | Owner | Storage -+-+-+-+- public | rank | table | gpadmin | append only public | rank_1_prt_2 | table | gpadmin | append only public | rank_1_prt_extra | table | gpadmin | append only (3 rows) db1=# insert into rank values (1,1,20

20、16,'M',100); INSERT 0 1 db1=# insert into rank values (1,1,2017,'M',100); INSERT 0 1 db1=# insert into rank values (1,1,2018,'M',100); INSERT 0 1 db1=# insert into rank values (1,1,2019,'M',100); INSERT 0 1 db1=# select * from rank; id | rank | year | gender | count -

21、+-+-+-+- 1 | 1 | 2016 | M | 100 1 | 1 | 2018 | M | 100 1 | 1 | 2019 | M | 100 1 | 1 | 2017 | M | 100 (4 rows) db1=# select * from rank_1_prt_2; id | rank | year | gender | count -+-+-+-+- 1 | 1 | 2017 | M | 100 (1 row) db1=# select * from rank_1_prt_extra; id | rank | year | gender | count -+-+-+-+-

22、 1 | 1 | 2016 | M | 100 1 | 1 | 2018 | M | 100 1 | 1 | 2019 | M | 100 (3 rows) db1=# drop table rank; DROP TABLE db1=# dt No relations found. 从上面的例子看到：HAWQ缺省的分区范围是左闭右开。可以使用default partition子句增加一个缺省分区，当数据不被包含在任何明确定义的分区时，可以被包含在缺省分区中。HAWQ在查询时可以将分区当做表看待，但删除主表后，分区被一并删除。3. 定义列表分区表 列表分区可以使用任何允许等值比较数据类型的列作为

23、分区键。列表分区表必须显式定义每个分区。注意列表中的字符比较区分大小写。plain view plain copy 在CODE上查看代码片派生到我的代码片db1=# create table rank (id int, rank int, year int, gender db1(# char(1), count int ) db1-# distributed by (id) db1-# partition by list (gender) db1-# ( partition girls values ('f'), db1(# partition boys values (&#

24、39;m'), db1(# default partition other ); NOTICE: CREATE TABLE will create partition "rank_1_prt_girls" for table "rank" NOTICE: CREATE TABLE will create partition "rank_1_prt_boys" for table "rank" NOTICE: CREATE TABLE will create partition "rank_1_pr

25、t_other" for table "rank" CREATE TABLE db1=# dt List of relations Schema | Name | Type | Owner | Storage -+-+-+-+- public | rank | table | gpadmin | append only public | rank_1_prt_boys | table | gpadmin | append only public | rank_1_prt_girls | table | gpadmin | append only public |

26、rank_1_prt_other | table | gpadmin | append only (4 rows) db1=# insert into rank values (1,1,2016,'M',100); INSERT 0 1 db1=# insert into rank values (1,1,2016,'m',100); INSERT 0 1 db1=# insert into rank values (1,1,2016,'f',100); INSERT 0 1 db1=# insert into rank values (1,1,

27、2016,'F',100); INSERT 0 1 db1=# insert into rank values (1,1,2016,'A',100); INSERT 0 1 db1=# select * from rank; id | rank | year | gender | count -+-+-+-+- 1 | 1 | 2016 | f | 100 1 | 1 | 2016 | m | 100 1 | 1 | 2016 | M | 100 1 | 1 | 2016 | F | 100 1 | 1 | 2016 | A | 100 (5 rows) db1

28、=# select * from rank_1_prt_boys; id | rank | year | gender | count -+-+-+-+- 1 | 1 | 2016 | m | 100 (1 row) db1=# select * from rank_1_prt_girls; id | rank | year | gender | count -+-+-+-+- 1 | 1 | 2016 | f | 100 (1 row) db1=# select * from rank_1_prt_other; id | rank | year | gender | count -+-+-+

29、-+- 1 | 1 | 2016 | M | 100 1 | 1 | 2016 | F | 100 1 | 1 | 2016 | A | 100 (3 rows) HAWQ不支持多分区键列复合比较，分区键只能是单列。plain view plain copy 在CODE上查看代码片派生到我的代码片db1=# create table rank (id int, rank int, year int, gender db1(# char(1), count int ) db1-# distributed by (id) db1-# partition by list (gender,year)

30、db1-# ( partition girls values ('f',2017), db1(# partition boys values ('m',2018), db1(# default partition other ); ERROR: Composite partition keys are not allowed 4. 定义多级分区 可以在分区中定义子分区。使用subpartition template子句保证每个分区都有相同的子分区定义，包括以后添加的分区。sql view plain copy 在CODE上查看代码片派生到我的代码片create

31、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 ('europ

32、e'), default subpartition other_regions) (start (date '2017-01-01') inclusive end (date '2018-01-01') exclusive every (interval '1 month'), default partition outlying_dates ); 以上语句建立了一共65个分区。一级分区13个，每个一级分区包含4个子分区。 下面的例子显示了一个树形分区设计。sales表按年、月、地区的层级三级分区。SUBPARTITION TEMPLAT

33、E子句保证每个分区都有相同的子分区结构。例子中的每一层级都指定了缺省的分区。sql view plain copy 在CODE上查看代码片派生到我的代码片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 o

34、ther_months ) subpartition by list (region) subpartition template ( subpartition usa values ('usa'), subpartition europe values ('europe'), subpartition asia values ('asia'), default subpartition other_regions ) ( start (2017) end (2018) every (1), default partition outlying_

35、years ); 注意，范围分区上的多级分区很容易建立大量的分区，其中有些分区可能只有很少的数据（甚至没有数据）。随着分区数量的增加，系统表的记录不断增长，查询优化和执行时所需的内存也会增加。加大范围分区的范围或者选择不同的分区策略有助于减少分区的数量。 5. 对一个已经存在的表进行分区 正如开篇提到的，HAWQ只能使用CREATE TABLE命令创建分区表。如果想对一个已经存在的表进行分区，只能这样做：新建分区表->将数据原表导入分区表->删除原表->分区表改名->分析分区表->对新建的分区表重新授权。例如：sql view plain copy 在CODE上查

36、看代码片派生到我的代码片create table sales2 (like sales) partition by range (date) ( start (date '2017-01-01') inclusive end (date '2018-01-01') exclusive every (interval '1 month') ); insert into sales2 select * from sales; drop table sales; alter table sales2 rename to sales; analyze s

37、ales; grant all privileges on sales to admin; grant select on sales to guest; 6. 查看分区表定义 查询pg_partitions视图可以获取分区相关信息。sql view plain copy 在CODE上查看代码片派生到我的代码片select partitionboundary, partitiontablename, partitionname,partitionlevel, partitionrank from pg_partitions where tablename='sales' 以下表

38、和视图提供了分区表的信息。pg_partition：分区表及其层级关系。pg_partition_templates：子分区使用的模板。pg_partition_columns：分区键列。五、分区消除 使用EXPLAIN可以检查查询执行计划，验证查询优化器是否只扫描了相关分区的数据。下面以sales表上的年、月、地区三级分区为例进行说明。sql view plain copy 在CODE上查看代码片派生到我的代码片create table sales (id int, year int, month int, day int, region text) distributed by (id)

39、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 ('北京'), subpartition europe values ('上海'), subpartition asia

40、 values ('广州'), default subpartition other_regions ) ( start (2017) end (2020) every (1), default partition outlying_years ); sales表最底层存储数据的分区共有 4 * 13 * 4 = 208个;1. 插入一条数据，如图2所示。这次只要扫描年份DEFAULT分区下的52个子分区。 分区消除有以下限制：查询优化器只有在查询条件中包含=、<、<=、>、>=、<>等比较运算符是才可能应用分区消除。对于稳定的函数会应用分区

41、消除，对于易变函数不会应用分区消除。例如，WHERE date > CURRENT_DATE会应用分区消除，而time > TIMEOFDAY则不会。六、分区表维护 ALTER TABLE命令维护分区表。尽管可以通过引用分区对应的表对象的名子进行查询和装载数据，但修改分区表结构时，只能使用ALTER TABLE.PARTITION引用分区的名字。也可以使用PARTITION FOR (value)或PARTITION FOR(RANK(number)指示分区。注意，HAWQ不支持在多级分区上的如下操作：增加缺省分区增加分区删除缺省分区删除分区分割分区所有修改子分区的操作1. 增加分

42、区sql view plain copy 在CODE上查看代码片派生到我的代码片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 (r

43、egion) subpartition template ( subpartition usa values ('北京'), subpartition europe values ('上海'), subpartition asia values ('广州'), default subpartition other_regions ) ( start (2017) end (2020) every (1); alter table sales add partition start (2016) inclusive end (2017) exclu

44、sive; 使用add partition增加分区时不能存在DEFAULT分区，否则会报类似以下的错误：plain view plain copy 在CODE上查看代码片派生到我的代码片ERROR: cannot add RANGE partition to relation "sales" with DEFAULT partition "outlying_years" HINT: need to SPLIT partition "outlying_years" 这时需要使用split partition增加分区。 为一个分区表增加子

45、分区时，可以指定需要修改的分区。sql view plain copy 在CODE上查看代码片派生到我的代码片alter table sales alter partition for (rank(12) add partition africa values ('africa'); alter table sales alter partition for (rank(1) add partition africa values ('africa'); 2. 增加缺省分区sql view plain copy 在CODE上查看代码片派生到我的代码片alter

46、table sales add default partition other; 如果没有DEFAULT分区，不能匹配分区CHECK约束的数据行将被拒绝入库，并且数据转载失败。为了避免这种情况，指定DEFAULT分区。任何不能与分区匹配的行都被装载进DEFAULT分区。3. 分区改名 每个子分区对应一个表对象，可以用dt元命令查看到。如果是自动生成的范围分区，在没有指定名称的分区被赋予一个数字。分区对应表对象的命名规则如下：plain view plain copy 在CODE上查看代码片派生到我的代码片<parentname>_<level>_prt_<part

47、ition_name> 例如：plain view plain copy 在CODE上查看代码片派生到我的代码片sales_1_prt_1_2_prt_11_3_prt_other_regions 上面的名称表示该分区名为'other_regions'，是sales表的一个第三级分区，隶属第一级的1号分区下的第二级的11号分区下。 修改顶级父表的名称，会重命名所有分区子表名，例如：sql view plain copy 在CODE上查看代码片派生到我的代码片alter table sales rename to globalsales; 相关的分区子表名变为：plain

48、view plain copy 在CODE上查看代码片派生到我的代码片globalsales_1_prt_1_2_prt_11_3_prt_other_regions 也可以将顶级分区名改为自定义的名称，例如：sql view plain copy 在CODE上查看代码片派生到我的代码片alter table sales rename partition for (2017) to y2017; 表对象名的最大长度为64字节，超长会报错：plain view plain copy 在CODE上查看代码片派生到我的代码片db1=# alter table globalsales rename p

49、artition for (2017) to year2017; ERROR: relation name "globalsales_1_prt_year2017_2_prt_other_months_3_prt_other_regions" for child partition is too long 当使用ALTER TABLE.PARTITION 命令修改分区表时，总是用分区名称（如y2017）而不是分区对应的表对象全名（globalsales_1_prt_y2017）。4. 删除分区 ALTER TABLE命令也可用来删除分区，如果被删除的分区有子分区，则这些子分

50、区及其数据也都被一起删除。sql view plain copy 在CODE上查看代码片派生到我的代码片alter table globalsales drop partition for (2017); alter table globalsales drop partition for (2018); 不能删除最后一个分区：plain view plain copy 在CODE上查看代码片派生到我的代码片db1=# alter table globalsales drop partition for (2019); ERROR: cannot drop partition for valu

51、e (2019) of relation "globalsales" - only one remains HINT: Use DROP TABLE "globalsales" to remove the table and the final partition 5. 清空分区 使用ALTER TABLE命令清空一个分区及其所有子分区的数据。不能单独清空一个子分区。sql view plain copy 在CODE上查看代码片派生到我的代码片alter table globalsales truncate partition for (2018); 6

52、. 分区交换 分区交换指的是用一个表的数据与一个分区的数据交换。HAWQ只支持单级分区表的分区交换。plain view plain copy 在CODE上查看代码片派生到我的代码片db1=# alter table sales exchange partition for (2017) db1-# with table stage_sales; ERROR: cannot EXCHANGE PARTITION for relation "sales" - partition has children 经常使用分区交换向分区表装载数据。当然也能使用COPY或INSERT命令

53、向分区表装载数据，此时数据被自动路由到正确的底层分区，就像普通表一样。但是，这种装载数据的方法会根据数据遍历整个分区层次结构，因此数据装载的性能很差。在前面208个分区的例子中，插入一条记录竟然用时16秒多，如图8所示。 向分区表装载数据的推荐方法创建一个中间过渡表，装载过渡表，然后用过渡表与分区做交换。plain view plain copy 在CODE上查看代码片派生到我的代码片db1=# create table sales (id int, year int, month int, day int, region varchar(10) db1-# distributed by (id) db1-# partition by range (year) db1-# ( start (2017) end (2020) every (1); NOTICE: CREATE TABLE will create partition "sales_1_prt_1" for table "sales" NOTICE: CREATE TA