GP--大表分区管理(一)

表分区使用背景:

表分区用以解决特别大的表的问题。比如事实表,解决办法就是将表分成很多小且更容易管理的部分。通过让GreenPlum数据库查询优化器只扫描满足给定查询所需的数据而避免扫描大表的全部内容,分区表能够提升查询性能。

表分布与表分区的区别:

Greenplum中每个表都需要有一个分布键,如果你建表的时候没有显示使用语法DISTRIBUTED BY (column) 指定一个分布键,系统也会默认为你指定一个。分布目的是把数据打散到每个节点,打散的规则是hash或者randomly。这样在计算时可以充分利用每个节点的资源进行并行计算。

分布:DISTRIBUTED
分区:PARTITION
分区并不会改变表数据在Segment之间的物理分布。
表分布是物理的:Greenplum数据库会在物理上把分区表和未分区表划分到多个Segment上来启用并行查询处理。
表分区是逻辑的:Greenplum数据库在逻辑上划分大表来提升查询性能并且有利于数据仓库维护任务,例如把旧数据滚出数据仓库。
无论是分区表还是非分区表,在Greenplum中,数据都是分散到各个节点上的。

GreenPlum与postgresql 分区对比

分区表意思是将一个大表在物理上分割成几块,GPDB中的分区表和PostgreSQL中实现原理一样,都是用过表继承、约束来实现。
1、在PostgreSQL中,一个父表,多个子表来实现分区表,需要手动向子表插入数据,如果向父表插入数据,则直接会被插入到父表中。
2、在GPDB中,可以直接想父表插入数据,便可以根据约束直接自动向对应的子表插入数据,当分区子表不存在时,插入失败。

Greenplum官方给出的分区表示例如下:
![在这里插入图片描述](https://img-blog.csdnimg.cn/20200402145418175.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzEyMDU1OQ==,size_16,color_FFFFFF,t_70

语法

在CREATE TABLE时使用PARTITION BY(以及可选的SUBPARTITION BY)子句来做分区。

理解

在GPDB中对一张表做分区,实际上是创建了一张顶层(父级)表和多个低层(子级)表。 在内部, GPDB在顶级表与低级表之间创建了继承关系(类似于PostgreSQL中的继承/INHERIT功能)。

Greenplum数据库把表划分成部分(也称为分区)来启用大规模并行处理。

[一个萝卜一个坑]:
Greenplum使用表创建时定义的分区标准来创建每一个分区及其上一个可区分的CHECK约束,这个约束限制了该表能含有的数据。查询优化器使用CHECK约束来决定要扫描哪些表分区来满足一个给定的查询谓词。

Greenplum系统目录存储了分区层次信息,这样插入到顶层父表的行会被正确地传播到子表分区。

要更改分区设计或者表结构,可使用带有PARTITION子句的ALTER TABLE修改父表。

要把数据插入到一个分过区的表中,用户需要指定根分区表,也就是用CREATE TABLE命令创建的那个表。用户也可以在INSERT命令中指定分区表的一个叶子子表。如果该数据对于指定的叶子子表不合法,则会返回一个错误。不支持在DML命令中指定一个非叶子或者非根分区表。

分区方式

Greenplum数据库支持:

·范围分区(Range Partition):基于一个数字型范围划分数据,例如按照日期或价格划分。
·列表分区(List Partition):基于一个值列表划分数据,例如按照销售范围或产品线划分。
·组合分区(A conbination of both types):两种类型的组合。

决定表分区的策略:

1、表是否足够大?
通常对于大的事实表,比如数据量有几千万或者过亿,我们可以考虑使用分区表,但数据量大小并没有一个绝对的标准可以使用,一般是根据经验,以及对目前性能是否满意。
3、查询条件是否能匹配分区条件?
如果数据量足够大了,这个时候我们就需要看下是否有合适的字段能够用来分区,通常如果数据有时间维度,比如按天,按月等,是比较理想的分区字段。
4、数据仓库是否需要滚动历史数据?
通常数仓中的数据不可能一直存放,一般都会有一定的生命周期,比如最近一年等,这里就涉及到对旧数据的管理,如果有分区表,就很容易删除旧的数据,或者将旧的数据归档到对象存储等更为廉价的存储介质上。
5、按照某个规则数据是否可以被均匀的分拆?
尽可能的均分。

不要创建超过所需数量的分区。创建过多的分区可能会拖慢管理和维护工作,例如清理、恢复Segment、扩展集群、检查磁盘用量等等。

除非查询优化器能基于查询谓词排除一些分区,分区技术就不能改进查询性能。每个分区都扫描的查询运行起来会比表没有分区时还慢,因此如果用户的查询中很少能实现分区排除,请避免进行分区。请检查查询的解释计划来确认分区被排除。

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

在选定一种多级分区策略之前,可以考虑一种带有位图索引的单级分区。索引会降低数据装载的速度,因此推荐用用户的数据和模式进行性能测试以决定最佳的策略。

  • 1
    点赞
  • 5
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
支持自动ORACLE大表分区: 版本进度: 31. 20110420 V2.2 支持任意表任意时间字段分区 以下为安装部署部分: 1.分区相关脚本部署执行顺序,安装前请确保该用户拥有管理员权限, 同时请执行GRANT CREATE ANY TABLE TO DBUSER, 因为使用到了动态的CREATE TABLE语句; 01) >AGGRE_ERROR_INFO_DDL.SQL 如果日志表AGGRE_ERROR_INFO已经存在,该步骤跳过。 02) >GET_MILLISECOND.SQL 如果函数GET_MILLISECOND已经存在,该步骤跳过。 03) >GET_DATE_FROM_MILLISECOND.SQL 如果函数GET_DATE_FROM_MILLISECOND已经存在,该步骤跳过。 04) >AGGRE_PM_PARTITIONF.SQL 2.注意事项: 01) >部署完后注意检查分区维护JOB[对应存储过程为AGGRE_PM_PARTITIONM], 如果有多个相同的分区维护JOB,则请删除后面创建的JOB,只保留一个分区维护JOB。 检查脚本如下:select t.what,t.* from user_jobs t 02) >本产品中使用的分区调度表名称为AGGRE_PARTITION_TASK,可以根据该表中信息观察分区情况。 以下为说明解释部分: 2.分区改造主过程:AGGRE_PM_PARTITIONF.SQL, 意思是PARTITIONING THE FIRST TIME; 参数解释如下: -- @PARAM VARCHAR2 PARTTABLENAME---可以指定对某个表大小大于等于TABLEONSIZE_M(单位为MB)的表进行按指定时间字段的自动分区; -- @PARAM NUMBER TABLEONSIZE_M---大表自动分区起始大小,单位为兆字节(MB),如不想指定具体大小则置0即可; -- @PARAM NUMBER PARTINTERVAL----取值范围为[1/24,365],表的分区时长,单位为天,默认为1,采用一天一分区; -- @PARAM 若为7,则采用一周一分区,若为30,则采用一月一分区; -- @PARAM NUMBER PARTRESERVED----表数据保留时长,单位为天; -- @PARAM NUMBER BACKINTERVAL----取值范围为[3600,7*86400],表数据回迁时的循环步长,即一次回迁多长时间的数据,单位为秒; -- @PARAM VARCHAR2 PARTWEEKDAY-----取值范围为(SUN,MON),PARTINTERVAL为7时起作用,指定一周的起始天为星期日还是星期一; -- @PARAM VARCHAR2 PARTFIELD-------指定的分区时间字段名称 -- @PARAM VARCHAR2 FIELDFORMAT-----指定的分区时间字段的格式 -- @PARAM VARCHAR2 TISPARTITIONED--取值范围为(TRUE,FALSE),指定PARTTABLENAME参数所指定的表是否是分区表,默认为FALSE -- @PARAM VARCHAR2 PARTEXCHANGE----取值范围为(TRUE,FALSE),是否使用交换分区方法实现非分区表的分区化改造,默认为FALSE -- @PARAM 注意:当PARTEXCHANGE参数为TRUE时,TISPARTITIONED参数只能为FALSE, -- @PARAM 即已经分好区的分区表是不能够使用交换分区的方法转换为另一种分区表的; -- @PARAM VARCHAR2 DROPPABLE-------取值范围为(TRUE,FALSE),指定分区完后是否DROP掉分区备份表; 其中参数FIELDFORMAT的取值范围如下: /** * FIELDFORMAT * 0 NUMBER/CHAR MILLISECOND 1300200064000 13BITS * 1 NUMBER/CHAR SECOND 1300200064 10BITS * 2 NUMBER/CHAR YYYYMMDDHH24MISS 20110315224030 * 3 NUMBER/CHAR YYYYMMDDHH24MI 201103152240 * 4 NUMBER/CHAR YYYYMMDDHH24 2011031522 * 5 NUMBER/CHAR YYYYMMDD 20110315 * 6 NUMBER/CHAR YYYYMM 201103 * 7 NUMBER/CHAR YYYY 2011 * 8 CHAR YYYY-MM 2011-03 * 9 CHAR YYYY-MM-DD 2011-03-15 * 10 CHAR YYYY-MM-DD HH24 2011-03-15 22 * 11 CHAR YYYY-MM-DD HH24:MI 2011-03-15 22:40 * 12 CHAR YYYY-MM-DD HH24:MI:SS 2011-03-15 22:40:30 * 13 CHAR YYYY-MM-DD HH24:MI:SSXFF 2011-03-15 22:40:30.765000 * 14 CHAR YYYY"年" 2011年 * 15 CHAR YYYY"年"MM"月" 2011年03月 * 16 CHAR YYYY"年"MM"月"DD"日" 2011年03月15日 * 17 CHAR YYYY"年"MM"月"DD"日" HH24"时" 2011年03月15日 22时 * 18 CHAR YYYY"年"MM"月"DD"日" HH24"时"MI"分" 2011年03月15日 22时40分 * 19 CHAR YYYY"年"MM"月"DD"日" HH24"时"MI"分"SS"秒" 2011年03月15日 22时40分30秒 * 100 DATE 2011-3-15 23:00:01 * 101 TIMESTAMP 15-3月 -11 10.59.30.953000 下午 +08:00 */ -- 第一次分区尽量在数据库闲时操作,这样更能保证分区表的数据一致性; -- 通常使用的现有大表分区方法:A.使用RENAME分区 B.使用交换分区 C.使用联机重定义 只有C方案才能保证数据的完全一致性; -- 但是经过测试发现方案B和C都存在分区过程的不透明性,对EXCEPTION不好控制,另外C方案比较适合手工操作,不适合自动运行; -- B方案比较适合将非分区表中的数据放到分区表中的一个分区中,不符合要求,所以本分区存储过程默认采用A方案; -- 当然,也支持通过新增参数PARTEXCHANGE来控制是否使用B方案;PARTEXCHANGE为TRUE,使用B方案,为FALSE,使用A方案; -- 手动运行示例: 自动对800M以上的非分区大表PM_RAW_B_RESTEST进行分区,一天一分区; -- 手动运行示例: SQL> EXEC AGGRE_PM_PARTITIONF('PM_RAW_B_RESTEST',800,1,10,3600,'SUN','DCTIME','0','FALSE','FALSE','FALSE'); -- 推荐以以上这种方式对单个表进行分区,并将DROPPABLE参数设为'FALSE', -- 这样有什么问题可以跟踪,等完后再可手动将分区备份表DROP掉; -- 注意:分区之前请确保相关表空间足够大。 -- 注意:如果在分区化改造过程中数据回迁之前抛出异常,则手动数据回迁前注意检查分区表有无主键索引。 3.分区维护主过程:AGGRE_PM_PARTITIONM.SQL, 意思是PARTITION MANAGEMENT; 4.创建分区维护JOB -- 对在分区调度表中的已经分区的表进行分区清理以及分区追加等 -- 分区维护操作由该JOB自动完成,该过程不用手动干预。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值