Greenplum介绍 - Partitioned Table

我会带着你远行 2022-05-27 05:35 192阅读 0赞

GP支持分区表，主要用来存放大表，如fact table  
目的：  
1. 把大数据切片,便于查询  
2. 便于数据库维护

分区创建时，每个分区会自带一个Check约束，来限定数据的范围。Check约束也用于 执行查询时定位分区。

**支持分区类型**：  
1. 范围分区 range partition  
2. 列表分区 list partition  
3. 组合分区

[![Greenplum介绍 <wbr>- <wbr>Partitioned <wbr>Table][Greenplum_wbr_-_wbr_Partitioned_wbr_Table]][Greenplum_wbr_-_wbr_Partitioned_wbr_Table_Greenplum_wbr_-_wbr_Partitioned_wbr_Table]  
                                      (多级分区表)

\-------partition 和 distribution的区别------  
distribution -- 物理上拆分表数据、能并行执行查询  
partition -- 逻辑上拆分大表数据提高查询性能、利于数据仓库维护工作

\------表分区策略------  
@ 表是否足够大？  
  大事实表可以选择分区。如果一个表有几百万或10亿数据量，你可以看到性能上的优势。  
  如果只是几千行或者更小的表的话，dba将在维护分区花费大量的精力，小表不要建分区，直接full table scan。  
@ 是否对现有性能不满意？  
  当表的查询响应时间比预期要慢的话，可以考虑用分区表  
@ 是否能够判断查询可以使用固定范围或固定值来做限定条件？  
  where子句中是否使用分区字段。  
  例如：经常按日志来查询，可以按月或周来范围分区。或按地区来查的话，可以按地区来做列表分区。  
@ 是否需要维护你数据仓库中的大量历史记录？  
  分区设计另外一个考量是根据业务要求来决定历史数据的保存。  
  例如：数据仓库只要保留过去12个月的数据。如果是按月分区的话，你很容易删掉历史分区并载入数据到当前几个月份中。  
@ 是否能基于一些定义规则将数据分成等份？  
  你应该选择尽可能的将数据等分。如果分区中包含等数量记录，则查询性能提升基于已创建的分区数量。  
  例如：一个大表被分成10个分区，执行一个查询将比非分区表快10倍。

\------创建分区表------  
\[时间范围分区\]  
2中创建方式：  
==》CREATE TABLE sales (id int, date date, amt decimal(10,2))  
    DISTRIBUTED BY (id)  
    PARTITION BY RANGE (date)  
   ( START (date '2008-01-01') INCLUSIVE  
     END (date '2009-01-01') EXCLUSIVE  
     EVERY (INTERVAL '1 day') ); --时间间隔  
==> CREATE TABLE sales (id int, date date, amt decimal(10,2))  
    DISTRIBUTED BY (id)  
    PARTITION BY RANGE (date)  
    ( PARTITION Jan08 START (date '2008-01-01') INCLUSIVE ,  
    PARTITION Feb08 START (date '2008-02-01') INCLUSIVE ,  
    PARTITION Mar08 START (date '2008-03-01') INCLUSIVE ,  
    PARTITION Apr08 START (date '2008-04-01') INCLUSIVE ,  
    PARTITION May08 START (date '2008-05-01') INCLUSIVE ,  
    PARTITION Jun08 START (date '2008-06-01') INCLUSIVE ,  
    PARTITION Jul08 START (date '2008-07-01') INCLUSIVE ,  
    PARTITION Aug08 START (date '2008-08-01') INCLUSIVE ,  
    PARTITION Sep08 START (date '2008-09-01') INCLUSIVE ,  
    PARTITION Oct08 START (date '2008-10-01') INCLUSIVE ,  
    PARTITION Nov08 START (date '2008-11-01') INCLUSIVE ,  
    PARTITION Dec08 START (date '2008-12-01') INCLUSIVE  
    END (date '2009-01-01') EXCLUSIVE ); --在最后要写个end  
   
\[数字范围分区\]  
==>CREATE TABLE rank (id int, rank int, year int, gender  
    char(1), count int)  
    DISTRIBUTED BY (id)  
    PARTITION BY RANGE (year)  
    ( START (2001) END (2008) EVERY (1),  
    DEFAULT PARTITION extra );

\[列表分区\]  
==>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 (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 (2002) END (2010) EVERY (1),  
        DEFAULT PARTITION outlying\_years );

\------普通表 转 分区表 -------  
==>CREATE TABLE sales2 (LIKE sales)  
   PARTITION BY RANGE (date)  
   ( START (date '2008-01-01') INCLUSIVE  
     END (date '2009-01-01') EXCLUSIVE  
     EVERY (INTERVAL '1 month') );  
==>INSERT INTO sales2 SELECT \* FROM sales;  
==>DROP TABLE sales;  
==>ALTER TABLE sales2 RENAME TO sales;  
==>GRANT ALL PRIVILEGES ON sales TO admin;  
==>GRANT SELECT ON sales TO guest;

\------**选择性分区扫描的一些限制**\-----  
限制条件：  
1. 条件中使用简单直接的限制操作符(如：=  <  <=  >  >=  <>)，可以选择性扫描分区  
2. 不能识别变化性函数，只能是固定值  
   例如：where语句中 date > current\_date可以做范围扫描。 但是time > timeofday则不行。  
3. 不确定的值是无法做runtime评估，必须要指定明确的值  
   例如：SELECT \* from partition\_table PT, other\_table OT WHERE  
         PT.id=OT.id and OT.some\_column = 'value';  
   --value必须指定值  
    
\------查看表分区-------  
pg\_partitions  - 查看创建分区信息  
pg\_partition\_templates - 查看是用subpartition模板创建的子分区  
pg\_partition\_column - 查看分区字段

关于修改表分区的语法，参考官方文档。

原文网址：http://blog.sina.com.cn/s/blog\_90474a9201013k74.html

[Greenplum_wbr_-_wbr_Partitioned_wbr_Table]: http://s14.sinaimg.cn/middle/90474a92gbea60414516e&690
[Greenplum_wbr_-_wbr_Partitioned_wbr_Table_Greenplum_wbr_-_wbr_Partitioned_wbr_Table]: /images/20220527/73d0c0feafde4b9cab34914855d32fb0.png