[技术交流] GaussDB 100 分区表管理

操作场景

GaussDB 100分区表支持范围分区(Range Partition)、列表分区(List Partition)、哈希分区(Hash Partition)与间隔分区(INTERVAL Partition)。表分区功能可以改善数据库的查询性能,增强可用性,方便维护,以及均衡I/O等。

相关概念

GaussDB 100数据库支持的分区表为范围分区表、列表分区表、哈希分区表和间隔分区表。

  • 范围分区表:将数据基于范围映射到每一个分区,这个范围是由创建分区表时指定的分区键决定的。这种分区方式是最为常用的,并且分区键经常采用日期,例如将销售数据按照月份进行分区。

  • 间隔分区表:间隔分区表是一种特殊的范围分区表。对于普通的范围分区,用户预先创建了多少个分区,如果添加不在该分区的数据时,数据库会报错,这种情况下,用户可以手动添加分区,也可以使用间隔分区。比如用户会按照每天一个分区的方式使用range分区表,在业务部署时会创建一批分区(如3个月)以备后续使用,但是3个月后需要再次创建,不然后续的业务数据入库会报错。range分区的这种方式增加了维护成本,需要内核支持分区的自动创建功能。如果使用间隔分区,用户可以不必关心后续分区的创建,既减少分区的设计成本和维护成本。

  • 列表分区表:将庞大的表分割成小的、易于管理的小块。

  • 哈希分区表:在很多情况下,用户无法预测某个列上的数据变化范围,因而无法实现创建固定数量的范围分区或列表分区。在这种情况下,哈希分区提供了一种在指定数量的分区中均等地划分数据的方法,写入表中的数据均匀地分布在各个分区中,用户无法预测数据将被写入哪个分区中。例如,如果销售城市不是相对固定的,而是遍布全国各地,很难对表进行列表分区,此时可以对该表进行哈希分区。

分区表和普通表相比具有以下优点:

  • 改善查询性能:对分区对象的查询可以仅搜索自己关心的分区,提高检索效率。

  • 增强可用性:如果分区表的某个分区出现故障,表在其他分区的数据仍然可用。

  • 方便维护:如果分区表的某个分区出现故障,需要修复数据,只修复该分区即可。

  • 均衡I/O:可以把不同的分区映射到不同的磁盘以平衡I/O,改善整个系统性能。

操作步骤

按照以下方式对范围分区表进行操作。
  • 删除已存在的重名表,以一个已存在的表“staffs_p”为例。

    DROP TABLE IF EXISTS staffs_p;
  • 创建分区表staffs_p。

    CREATE TABLE staffs_p(  staff_ID    NUMBER(6) not null,  FIRST_NAME     VARCHAR2(20),  LAST_NAME      VARCHAR2(25),  EMAIL          VARCHAR2(25),  PHONE_NUMBER   VARCHAR2(20),  HIRE_DATE      DATE,  employment_ID         VARCHAR2(10),  SALARY         NUMBER(8,2),  COMMISSION_PCT NUMBER(2,2),  MANAGER_ID     NUMBER(6),  section_ID  NUMBER(4))PARTITION BY RANGE (staff_ID)(  partition P_050_BEFORE values less than (50),  partition P_100 values less than (100) ,  partition P_150 values less than (150) ,  partition P_200 values less than (200));
  • 删除分区P_200_AFTER。

    ALTER TABLE staffs_p DROP PARTITION P_200_AFTER;
  • 增加分区P_250,范围为 200 <= P_250 <=250。

    ALTER TABLE staffs_p ADD PARTITION P_250 VALUES LESS THAN (250);
  • 查询分区P_150。

    SELECT * FROM staffs_p PARTITION (P_150);
  • 创建分区表索引

    • 创建分区表索引HR_staffS_p1_index1,不指定索引分区的名字。

      CREATE INDEX HR_staffS_p1_index1 ON staffs_p (staff_ID) LOCAL;
    • 创建分区表索引HR_staffS_p1_index2,并指定索引分区的名字。

      CREATE INDEX HR_staffS_p1_index2 ON staffs_p (section_ID) LOCAL(    PARTITION section_ID1_index,    PARTITION section_ID2_index TABLESPACE USERS,    PARTITION section_ID3_index TABLESPACE USERS,    PARTITION section_ID4_index,    PARTITION staff_ID5_index) TABLESPACE USERS;
按照以下方式对哈希分区表进行操作。
  • 删除已存在的重名表,以一个已存在的表“staffs_p2017和staffs_p2018”为例。

    DROP TABLE IF EXISTS staffs_p2017;
    DROP TABLE IF EXISTS staffs_p2018;
  • 创建分区表

    • 创建指定分区个数,但不指定分区名的哈希分区表。

      CREATE TABLE staffs_p2018(  staff_ID    NUMBER(6) not null,  FIRST_NAME     VARCHAR2(20),  LAST_NAME      VARCHAR2(25),  EMAIL          VARCHAR2(25),  PHONE_NUMBER   VARCHAR2(20),  HIRE_DATE      DATE,  employment_ID         VARCHAR2(10),  SALARY         NUMBER(8,2),  COMMISSION_PCT NUMBER(2,2),  MANAGER_ID     NUMBER(6),  section_ID  NUMBER(4))PARTITION BY HASH (staff_ID) PARTITIONS 5 STORE IN(USERS,USERS);
    • 创建指定分区名,但不指定分区个数的哈希分区表。

      CREATE TABLE staffs_p2017(  staff_ID    NUMBER(6) not null,  FIRST_NAME     VARCHAR2(20),  LAST_NAME      VARCHAR2(25),  EMAIL          VARCHAR2(25),  PHONE_NUMBER   VARCHAR2(20),  HIRE_DATE      DATE,  employment_ID         VARCHAR2(10),  SALARY         NUMBER(8,2),  COMMISSION_PCT NUMBER(2,2),  MANAGER_ID     NUMBER(6),  section_ID  NUMBER(4))PARTITION BY HASH(staff_ID)(  PARTITION P_01 tablespace USERS,  PARTITION P_02 tablespace USERS,  PARTITION P_03 tablespace USERS,  PARTITION P_04 tablespace USERS,  PARTITION P_05 tablespace USERS);
  • 删除分区

    执行删除操作时,最后一个分区的数据添加到前边的某个分区里,然后将最后一个分区删掉。如果只剩下一个分区,删除时会报错。

    ALTER TABLE staffs_p2017 COALESCE PARTITION;ALTER TABLE staffs_p2018 COALESCE PARTITION;
  • 增加分区part_06。

    ALTER TABLE staffs_p2017 ADD PARTITION part_06;
  • 查询分区

    • 查询指定分区名的哈希分区表的分区。

      查询分区P_04。
      SELECT * FROM staffs_p2017 PARTITION(P_04);
    • 查询指定分区个数的哈希分区表的分区。

      --通过联合查询系统表SYS.SYS_TABLE_PARTS和SYS.SYS_TABLES查询出分区名。SELECT * FROM SYS.SYS_TABLE_PARTS WHERE TABLE# = (SELECT ID FROM SYS.SYS_TABLES WHERE NAME='STAFFS_P2018');USER#        TABLE#       PART#        NAME                                                             HIBOUNDLEN   HIBOUNDVAL                                                       SPACE#       ORG_SCN              ENTRY                INITRANS     PCTFREE      FLAGS        BHIBOUNDVAL                                                      ROWCNT       BLKCNT       EMPCNT       AVGRLN       SAMPLESIZE   ANALYZETIME
      ------------ ------------ ------------ ---------------------------------------------------------------- ------------ ---------------------------------------------------------------- ------------ -------------------- -------------------- ------------ ------------ ------------ ---------------------------------------------------------------- ------------ ------------ ------------ ------------ ------------ ----------------------
      2            71           10           SYS_P1023                                                        0                                                                             3            2189599603179522     4393751543808        2            8            0                                                                                                                                             
      2            71           20           SYS_P1024                                                        0                                                                             3            2189599603179523     4393751543808        2            8            0                                                                                                                                             
      2            71           30           SYS_P1025                                                        0                                                                             3            2189599603179524     4393751543808        2            8            0                                                                                                                                             
      2            71           40           SYS_P1026                                                        0                                                                             3            2189599603179525     4393751543808        2            8            0
      
      4 rows fetched.
      --查询名为SYS_P1024的分区的数据。SELECT * FROM STAFFS_P2018 PARTITION(SYS_P1024);
  • 创建分区表索引

    • 创建分区表索引HR_staffS_p1_index_2017,不指定索引分区的名字。

      CREATE INDEX HR_staffS_p1_index_2017 ON staffs_p2017 (staff_ID) LOCAL;
    • 创建分区表索引HR_staffS_p1_index_2017_1,并指定索引分区的名字。

      CREATE INDEX HR_staffS_p1_index_2017_1 ON staffs_p2017 (employment_ID) LOCAL(    PARTITION employment_ID1_index,    PARTITION employment_ID2_index TABLESPACE USERS,    PARTITION employment_ID3_index TABLESPACE USERS,    PARTITION employment_ID4_index,    PARTITION employment_ID5_index) TABLESPACE USERS;
按照以下方式对间隔分区表进行操作。
  • 删除已存在的重名表,以一个已存在的表“staffs_p2016”为例。

    DROP TABLE IF EXISTS staffs_p2016;
  • 创建分区表staffs_p2016。

    CREATE TABLE staffs_p2016(  staff_ID    NUMBER(6) not null,  FIRST_NAME     VARCHAR2(20),  LAST_NAME      VARCHAR2(25),  EMAIL          VARCHAR2(25),  PHONE_NUMBER   VARCHAR2(20),  HIRE_DATE      DATE,  employment_ID         VARCHAR2(10),  SALARY         NUMBER(8,2),  COMMISSION_PCT NUMBER(2,2),  MANAGER_ID     NUMBER(6),  section_ID  NUMBER(4))PARTITION BY RANGE (staff_ID)INTERVAL (2)(  partition P_50 values less than (50),  partition P_100 values less than (100),  partition P_150 values less than (150));
  • 删除分区

    --通过联合查询系统表SYS.SYS_TABLE_PARTS和SYS.SYS_TABLES查询出分区名。SELECT * FROM SYS.SYS_TABLE_PARTS WHERE TABLE# = (SELECT ID FROM SYS.SYS_TABLES WHERE NAME='STAFFS_P2016');USER#        TABLE#       PART#        NAME                                                             HIBOUNDLEN   HIBOUNDVAL                                                       SPACE#       ORG_SCN              ENTRY                INITRANS     PCTFREE      FLAGS        BHIBOUNDVAL                                                      ROWCNT       BLKCNT       EMPCNT       AVGRLN       SAMPLESIZE   ANALYZETIME
    ------------ ------------ ------------ ---------------------------------------------------------------- ------------ ---------------------------------------------------------------- ------------ -------------------- -------------------- ------------ ------------ ------------ ---------------------------------------------------------------- ------------ ------------ ------------ ------------ ------------ ----------------------
    2            70           10           P_50                                                             2            50                                                               3            47529132593153       4393751543808        2            8            0                                    
    2            70           20           P_100                                                            3            100                                                              3            47529132687361       4393751543808        2            8            0                                    
    2            70           30           P_150                                                            3            150                                                              3            47529132744705       4393751543808        2            8            0                                    
    
    3 rows fetched.
    --删除名为P_50的分区。ALTER TABLE staffs_p2016 DROP PARTITION P_50;
  • 增加分区

    间隔分区表会随着用户添加的数据动态扩展,无需手动增加。自动生成的分区名可以从SYS.SYS_TABLE_PARTS表查询出来。

  • 查询分区

    --通过联合查询系统表SYS.SYS_TABLE_PARTS和SYS.SYS_TABLES查询出分区名。SELECT * FROM SYS.SYS_TABLE_PARTS WHERE TABLE# = (SELECT ID FROM SYS.SYS_TABLES WHERE NAME='STAFFS_P2016');USER#        TABLE#       PART#        NAME                                                             HIBOUNDLEN   HIBOUNDVAL                                                       SPACE#       ORG_SCN              ENTRY                INITRANS     PCTFREE      FLAGS        BHIBOUNDVAL                                                      ROWCNT       BLKCNT       EMPCNT       AVGRLN       SAMPLESIZE   ANALYZETIME
    ------------ ------------ ------------ ---------------------------------------------------------------- ------------ ---------------------------------------------------------------- ------------ -------------------- -------------------- ------------ ------------ ------------ ---------------------------------------------------------------- ------------ ------------ ------------ ------------ ------------ ----------------------
    0            242          20           P_100                                                            3            100                                                              0            6386343436210177     4393751543808        2            8            0                                          
    0            242          30           P_150                                                            3            150                                                              0            6386343436247041     4393751543808        2            8            0                                          
    
    2 rows fetched.
    --查询名为P_150的分区的数据。SELECT * FROM STAFFS_P2016 PARTITION(P_150);
  • 创建分区表索引

    • 创建分区表索引HR_staffS_p1_index_20162,并指定索引分区的名字。

      CREATE INDEX HR_staffS_p1_index_20162 ON staffs_p2016 (section_ID) LOCAL(    PARTITION section_ID2_index,    PARTITION section_ID3_index) TABLESPACE USERS;
    • 创建分区表索引HR_staffS_p1_index_2016,不指定索引分区的名字。

      CREATE INDEX HR_staffS_p1_index_2016 ON staffs_p2016 (staff_ID) LOCAL;
按照以下方式对列表分区表进行操作。
  • 删除已存在的重名表,以一个已存在的表“education”为例。

    DROP TABLE IF EXISTS education;
  • 创建分区表education。

    CREATE TABLE education(staff_id INT NOT NULL, higest_degree CHAR(12), graduate_school VARCHAR(64), graduate_date DATETIME, education_note VARCHAR(70))PARTITION BY LIST(higest_degree)(PARTITION doctor VALUES ('doctor') TABLESPACE USERS,PARTITION master VALUES ('master') TABLESPACE USERS,PARTITION undergraduate VALUES ('undergraduate') TABLESPACE USERS);
  • 删除分区undergraduate。

    ALTER TABLE education DROP PARTITION undergraduate;
  • 增加分区postdoctor。

    ALTER TABLE education ADD PARTITION postdoctor VALUES('postdoctor');
  • 查询分区postdoctor。

    SELECT * FROM education PARTITION (postdoctor);
  • 创建分区表索引

    • 创建分区表索引idx_education1,不指定索引分区的名字。

      CREATE INDEX idx_education1 ON education(staff_id) LOCAL;
    • 创建分区表索引idx_education2,并指定索引分区的名字。

      CREATE INDEX idx_education2 ON education(higest_degree) LOCAL(    PARTITION higest_degree1_index,    PARTITION higest_degree4_index,    PARTITION higest_degree5_index) TABLESPACE USERS;