磐维数据库分区表使用

原创飞天 2024-12-21

411

分区表概述

当一张表内的数据过多时，就会严重影响到数据的查询和操作效率。磐维数据库支持把一张表从逻辑上分成多个小的分片，从而避免一次处理大量数据，提高处理效率。

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

改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索效率。
增强可用性：如果分区表的某个分区出现故障，表在其他分区的数据仍然可用。
方便维护：如果分区表的某个分区出现故障，需要修复数据，只修复该分区即可。
均衡 I/O：可以把不同的分区映射到不同的磁盘以平衡 I/O，改善整个系统性能。

分区表类型

PanWeiDB 数据库支持的分区表：Range 分区表、List 分区表、Hash 分区表、system 分区表和Interval 分区表。

Range 分区表：范围分区根据您为每个分区建立的分区键值的范围将数据映射到分区。这种分区方式是最常见的，并且分区键值经常采用日期，例如将销售数据按照月份进行分区。
List 分区表：将数据中包含的键值分别存储在不同的分区中，依次将数据映射到每一个分区，分区中包含的键值由创建分区表时指定，键值最多不超过 127 个。
Hash 分区表：将数据根据内部哈希算法依次映射到每一个分区中，包含的分区个数由创建分区表时指定。
system 分区表：系统分区可以对没有分区键的表进行分区。创建分区时可以指定分区名称，也可以不指定分区名称，由系统自动分配。
Interval 分区表：interval 分区是 range 分区的扩展。对于连续数据类型的 Range 分区，如果插入的新数据值与当前分区均不匹配，自动interval 分区可以自动创建分区。

注意事项：

普通表若要转成分区表，需要新建分区表，然后把普通表中的数据导入到新建的分区表中。因此在初始设计表时，请根据业务提前规划是否使用分
区表。
Hash 分区只支持 1 个分区键，分区键值能用表的普通字段来指定，分区范围的值只支持常量表达式、数值或字符串常量，不支持新增和删除分区。
system 分区表仅在数据库兼容模式为 Oracle 时有效（即初始化数据库时 DBCOMPATIBILITY=‘A’），在其他数据库兼容模式下不能使用该特性。
system 分区表暂不支持二级分区、组合分区。
暂不支持对列存表、MOT 表进行分区操作。
交换分区的普通表，必须与分区表的字段个数、字段类型都完全一致时才可以进行 exchange 操作，且普通表不能是临时表。（支持增删列之后的表进行分区交换。）

Range 分区表

1、范围分区表按照划分范围的方式，分为以下类别：

VALUES LESS THAN：通过给出每个分区的上限来确定分区范围。上个分区的上限<=分区的范围<本分区的上限。
START END：通过以下方式划分：
（1）分区的起点和终点；
（2）仅给出分区起点；
（3）仅给出分区终点；
（4）给出分区起点和终点后，再给出该范围内的间隔值。
（5）以上这些方式的综合应用。

2、创建VALUES LESS THAN范围分区表
语法格式：

CREATE TABLE partition_table_name
( [column_name data_type ]
  [, ... ]
)
     PARTITION BY RANGE (partition_key) 
         (
         PARTITION partition_name VALUES LESS THAN (partition_value | MAXVALUE})
         [, ... ]
         );

示例：创建范围分区表order_table，分区字段create_date，按月分区

CREATE TABLE order_table
(
order_no              INTEGER          NOT NULL,
create_date           DATETIME         NOT NULL
)
PARTITION BY RANGE(create_date)
(
PARTITION p202410 VALUES LESS THAN('2024-11-01 00:00:00'),
PARTITION p202411 VALUES LESS THAN('2024-12-01 00:00:00'),
PARTITION p202412 VALUES LESS THAN('2025-01-01 00:00:00'),
PARTITION p202501 VALUES LESS THAN('2025-02-01 00:00:00'),
PARTITION p202502 VALUES LESS THAN('2025-03-01 00:00:00'),
PARTITION p202503 VALUES LESS THAN('2025-04-01 00:00:00'),
PARTITION p202504 VALUES LESS THAN('2025-05-01 00:00:00'),
PARTITION pmax VALUES LESS THAN(MAXVALUE)
);

#分区表中插入数据
insert into order_table  values(1,now());
insert into order_table  values(2,'2025-01-23 10:15:03');
insert into order_table  values(3,'2025-03-01 05:15:03');

#查询分区表中所有数据
sales=# select * from order_table ;
 order_no |     create_date     
----------+---------------------
        1 | 2024-12-21 20:04:02
        2 | 2025-01-23 10:15:03
        3 | 2025-03-01 05:15:03
(3 rows)
#查询分区表中指定分区的数据
sales=# select * from order_table partition (p202503) ;
 order_no |     create_date     
----------+---------------------
        3 | 2025-03-01 05:15:03

3、创建START END范围分区表
START END范围分区表有多种表达方式，而且这些方式可以在一个分区表内组合使用。

方式一：START(partition_value) END (partition_value | MAXVALUE)方式

语法格式：

CREATE TABLE partition_table_name
( [column_name data_type ]
  [, ... ]
)
     PARTITION BY RANGE (partition_key) 
         (
         PARTITION partition_name START(partition_value) END (partition_value | MAXVALUE)
         [, ... ]
         );

示例：创建范围分区表graderecord，分区字段grade

CREATE TABLE graderecord  
(  
  number INTEGER,  
  name CHAR(20),  
  class CHAR(20),  
  grade INTEGER
)  
PARTITION BY RANGE(grade)  
(  
  PARTITION pass START(60) END(90),  
  PARTITION excellent START(90) END(MAXVALUE)
);

#插入数据
insert into graderecord values('210101','Alan','21.01',92);  
insert into graderecord values('210102','Ben','21.01',62);  
insert into graderecord values('210103','Brain','21.01',26);  
insert into graderecord values('210204','Carl','21.02',77);  
insert into graderecord values('210205','David','21.02',47);  
insert into graderecord values('210206','Eric','21.02',97); 

#查询分区表中所有数据
student=# SELECT * FROM graderecord;
 number |         name         |        class         | grade 
--------+----------------------+----------------------+-------
 210103 | Brain                | 21.01                |    26
 210205 | David                | 21.02                |    47
 210102 | Ben                  | 21.01                |    62
 210204 | Carl                 | 21.02                |    77
 210101 | Alan                 | 21.01                |    92
 210206 | Eric                 | 21.02                |    97
(6 rows)

#查询分区表中指定分区的数据
student=# SELECT * FROM graderecord PARTITION (pass);
ERROR:  partition "pass" of relation "graderecord" does not exist

#查看graderecord表中现有的分区
student=# select relname, parttype, partstrategy, boundaries from pg_partition where parentid=(select oid from pg_class where relname='graderecord');
   relname   | parttype | partstrategy | boundaries 
-------------+----------+--------------+------------
 graderecord | r        | r            | 
 pass_0      | p        | r            | {60}
 pass_1      | p        | r            | {90}
 excellent   | p        | r            | {NULL}
(4 rows)

可以看出，针对于START END类型的范围分区，在创建分区的时候自动把pass分区分成了两个分区pass_0、pass_1，pass_0的grade范围是（MINVALUE, 60），pass_1的范围是60<= grade<90

#查询分区表中指定分区的数据
student=#  SELECT * FROM graderecord PARTITION (pass_0);
 number |         name         |        class         | grade 
--------+----------------------+----------------------+-------
 210103 | Brain                | 21.01                |    26
 210205 | David                | 21.02                |    47
(2 rows)

student=#  SELECT * FROM graderecord PARTITION (pass_1);
 number |         name         |        class         | grade 
--------+----------------------+----------------------+-------
 210102 | Ben                  | 21.01                |    62
 210204 | Carl                 | 21.02                |    77
(2 rows)

student=# SELECT * FROM graderecord PARTITION (excellent);
 number |         name         |        class         | grade 
--------+----------------------+----------------------+-------
 210101 | Alan                 | 21.01                |    92
 210206 | Eric                 | 21.02                |    97
(2 rows)

方式二：START(partition_value)方式

语法格式：

CREATE TABLE partition_table_name
( [column_name data_type ]
    [, ... ]
 )
     PARTITION BY RANGE (partition_key)  
         (
         PARTITION partition_name START(partition_value) 
         [, ... ]  
         );

示例：创建范围分区表graderecord，分区字段grade

CREATE TABLE graderecord2  
(  
  number INTEGER,  
  name CHAR(20),  
  class CHAR(20),  
  grade INTEGER
)  
PARTITION BY RANGE(grade)  
(  
  PARTITION pass START(60) ,  
  PARTITION excellent START(90) 
);

#查看graderecord2表中现有的分区
student=# select relname, parttype, partstrategy, boundaries from pg_partition where parentid=(select oid from pg_class where relname='graderecord2');
   relname    | parttype | partstrategy | boundaries 
--------------+----------+--------------+------------
 graderecord2 | r        | r            | 
 pass_0       | p        | r            | {60}
 pass_1       | p        | r            | {90}
 excellent    | p        | r            | {NULL}
(4 rows)


(4 rows)

可以看出，针对于START 类型的范围分区，在创建分区的时候自动把pass分区分成了两个分区pass_0、pass_1，pass_0的grade范围是（MINVALUE, 60），pass_1的范围是60<= grade<90

方式三：END(partition_value | MAXVALUE)方式

语法格式：

CREATE TABLE partition_table_name
( [column_name data_type ]
    [, ... ]
 )
     PARTITION BY RANGE (partition_key)  
         (
         PARTITION partition_name END(partition_value | MAXVALUE) 
         [, ... ]  
         );

示例：

CREATE TABLE graderecord3  
  (  
  number INTEGER,  
  name CHAR(20),  
  class CHAR(20),  
  grade INTEGER
  )  
  PARTITION BY RANGE(grade)  
  (  
  PARTITION no_pass END(60),  
  PARTITION pass END(90),
  PARTITION excellent END(MAXVALUE)
  );

#查看graderecord3表中现有的分区
student=# select relname, parttype, partstrategy, boundaries from pg_partition where parentid=(select oid from pg_class where relname='graderecord3');
   relname    | parttype | partstrategy | boundaries 
--------------+----------+--------------+------------
 graderecord3 | r        | r            | 
 no_pass      | p        | r            | {60}
 pass         | p        | r            | {90}
 excellent    | p        | r            | {NULL}

方式四：START(partition_value) END (partition_value) EVERY (interval_value)方式

语法格式：

CREATE TABLE partition_table_name
( [column_name data_type ]
    [, ... ]
 )
     PARTITION BY RANGE (partition_key)  
         (
         PARTITION partition_name START(partition_value) END (partition_value) EVERY (interval_value)
         [, ... ]  
         );

示例：

CREATE TABLE test_startevery_end
(  
col1 INTEGER,  
col2 INTEGER,
name CHAR(20)
)  
PARTITION BY RANGE(col1)  
(  
partition p1  start(0) end(3), 
partition p2  start(3) end(12) every(3), 
partition p3  start(12) end(MAXVALUE)
);

#查看graderecord3表中现有的分区
student=# select relname, parttype, partstrategy, boundaries from pg_partition where parentid=(select oid from pg_class where relname='test_startevery_end');
       relname       | parttype | partstrategy | boundaries 
---------------------+----------+--------------+------------
 test_startevery_end | r        | r            | 
 p1_0                | p        | r            | {0}
 p1_1                | p        | r            | {3}
 p2_1                | p        | r            | {6}
 p2_2                | p        | r            | {9}
 p2_3                | p        | r            | {12}
 p3                  | p        | r            | {NULL}
(7 rows)

可以看到，p2分区总范围是[3,12)，间隔为3，因此p2分区被分成了3个,分别是 p2_1：[3,6), p2_2：[6,9), p2_3：[9,12)。

List 分区表

语法格式：

CREATE TABLE partition_table_name
( [column_name data_type ]
  [, ... ]
)
     PARTITION BY LIST (partition_key) 
         (
         PARTITION partition_name VALUES (list_values_clause)
         [, ... ]
         );

示例：

CREATE TABLE emp(
    employee_id NUMBER,
    department_id NUMBER,
    employee_name VARCHAR2(50),
    hire_date DATE
)
PARTITION BY LIST (department_id)
(
    PARTITION sales_partition VALUES (1, 2, 3),   
    PARTITION development_partition VALUES (4, 5),  
    PARTITION hr_partition VALUES (6)
);	

#查看emp表中现有的分区
postgres=# select relname, parttype, partstrategy, boundaries from pg_partition where parentid=(select oid from pg_class where relname='emp');
        relname        | parttype | partstrategy | boundaries 
-----------------------+----------+--------------+------------
 emp                   | r        | l            | 
 sales_partition       | p        | l            | {1,2,3}
 development_partition | p        | l            | {4,5}
 hr_partition          | p        | l            | {6}
(4 rows)

Hash 分区表

语法格式：

CREATE TABLE partition_table_name
( [column_name data_type ]
  [, ... ]
)
     PARTITION BY HASH (partition_key) 
        (PARTITION partition_name )
        [, ... ]
    ;

示例：

create table hash_partition_table (
col1 int, 
col2 int)
partition by hash(col1)
(
partition p1,
partition p2
);

#查看hash_partition_table表中现有的分区
postgres=# select relname, parttype, partstrategy, boundaries from pg_partition where parentid=(select oid from pg_class where relname='hash_partition_table') order by parttype desc, relname asc;
       relname        | parttype | partstrategy | boundaries 
----------------------+----------+--------------+------------
 hash_partition_table | r        | h            | 
 p1                   | p        | h            | {0}
 p2                   | p        | h            | {1}
(3 rows)

hash分区不用看boundaries列，会根据内部的算法把数据放到对应的分区内。

system 分区表

语法格式：

CREATE TABLE partition_table_name
( [column_name data_type ]
  [, ... ]
)
     PARTITION BY system 
        [(PARTITION partition_name )]
        [, ... ]
    ;

示例：

# 创建分区表（指定分区名称）
create table system_par_tab(
c1 integer, 
c2 date, 
c3 text
)
partition by system
(
partition part_1, 
partition part_2, 
partition part_3
);

#创建分区表（系统自动分配分区名称）
create table system_par_tab2(
id number, code varchar2(10), description varchar2(50)
)
partition by system;

#向分区表插入数据
向一个 system 分区表中插入数据时必须指定其分区名称。
insert into system_par_tab partition(part_1) values(1,'2022-01-01','p1');
insert into system_par_tab partition(part_2) values(2,'2022-02-01','p2');
insert into system_par_tab partition(part_3) values(3,'2022-03-01','p3');

#查看system_par_tab表中现有的分区
postgres=# select relname, parttype, partstrategy, boundaries from pg_partition where parentid=(select oid from pg_class where relname='system_par_tab');
    relname     | parttype | partstrategy | boundaries 
----------------+----------+--------------+------------
 system_par_tab | r        | s            | 
 part_1         | p        | s            | 
 part_2         | p        | s            | 
 part_3         | p        | s            | 
(4 rows)

#查看system_par_tab2表中现有的分区
postgres=# select relname, parttype, partstrategy, boundaries from pg_partition where parentid=(select oid from pg_class where relname='system_par_tab2');
     relname     | parttype | partstrategy | boundaries 
-----------------+----------+--------------+------------
 system_par_tab2 | r        | s            | 
 system_par_tab2 | p        | s            | 
(2 rows)

Interval 分区表

间隔分区是在范围分区的基础上，增加了间隔值“PARTITION BY RANGE (partition_key)”的定义。
语法格式：

CREATE TABLE partition_table_name
( [column_name data_type ]
  [, ... ]
)
     PARTITION BY RANGE (partition_key) 
         INTERVAL ('interval_expr') 
         (
         PARTITION partition_name VALUES LESS THAN (partition_value | MAXVALUE})
         [, ... ]
         );

示例：

CREATE TABLE interval_partition_table
(
order_no              INTEGER          NOT NULL,
create_date           DATETIME         NOT NULL
)
PARTITION BY RANGE(create_date)
INTERVAL ('1 month')
(
PARTITION p202410 VALUES LESS THAN('2024-11-01 00:00:00'),
PARTITION p202411 VALUES LESS THAN('2024-12-01 00:00:00'),
PARTITION p202412 VALUES LESS THAN('2025-01-01 00:00:00')
);


#分区表中插入数据
insert into interval_partition_table  values(1,'2024-12-22 00:00:00');
#不在已有分区的数据插入，系统会新建分区sys_p1。
insert into interval_partition_table  values(2,'2025-05-04 00:00:00');

postgres=# select relname, parttype, partstrategy, boundaries from pg_partition where parentid=(select oid from pg_class where relname='interval_partition_table');
         relname          | parttype | partstrategy |       boundaries        
--------------------------+----------+--------------+-------------------------
 interval_partition_table | r        | i            | 
 p202410                  | p        | r            | {"2024-11-01 00:00:00"}
 p202411                  | p        | r            | {"2024-12-01 00:00:00"}
 p202412                  | p        | r            | {"2025-01-01 00:00:00"}
 sys_p1                   | p        | i            | {"2025-06-01 00:00:00"}

START END语法格式请参考range分区表语法。

分区表操作

删除分区：

ALTER TABLE partition_table_name DROP PARTITION partition_name [ UPDATE GLOBAL INDEX ];

增加分区：

ALTER TABLE partition_table_name ADD {partition_less_than_item | partition_start_end_item| partition_list_item };

重命名分区：

ALTER TABLE partition_table_name RENAME PARTITION partition_name TO partition_new_name;

重建分区:

ALTER TABLE partition_table_name REBUILD PARTITION partition_name;
ALTER TABLE partition_table_name REBUILD PARTITION ALL;

分裂分区（指定切割点split_partition_value的语法）

ALTER TABLE partition_table_name SPLIT PARTITION partition_name AT ( split_partition_value ) INTO ( PARTITION partition_new_name1, PARTITION partition_new_name2);

分裂分区（指定分区范围的语法）

ALTER TABLE partition_table_name SPLIT PARTITION partition_name INTO { ( partition_less_than_item [, ...] ) | ( partition_start_end_item [, ...] ) };

合并分区：

ALTER TABLE partition_table_name MERGE PARTITIONS { partition_name } [, ...] INTO PARTITION partition_name;

交换分区:

ALTER TABLE partition_table_name exchange partition partition_name with table table_name (without/with validation);

二级分区

磐维数据库支持二级分区。二级分区即在原有的 range 分区、list 分区、hash 分区、interval 分区的基础上再次进行分区。
二级分区可以对表中的每个分区再次进行分区。分区类型有 range、list、hash 三种，一级与二级分区的分区类型可以任意组合。二级分区支持使用CREATE/ALTER/SELECT 语法，用于二级分区的创建与增删改查。二级分区的相关信息可在系统表 PG_PARTITION 中获取。
注意事项
interval 分区不能作为二级分区。
二级分区的语法格式：
创建、修改和删除二级分区的语法格式请参考：开发者指南->SQL 语法参考- >SQL 语法->CREATE TABLE SUBPARTITION、ALTER TABLE SUBPARTITION。
用户也可以使用如下命令在 gsql 客户端中查询相关 SQL 语法的使用帮助信息。

\h create table subpartition
\h alter table subpartition

总结

针对于大表，要选择合适的分区策略和分区键，合理利用分区表来提高查询性能和维护操作的效率。

关于作者：
专注于Oracle、MySQL、PG、OpenGauss和国产数据库的研究，热爱生活，热衷于分享数据库技术。
微信公众号：飞天online
墨天轮：https://www.modb.pro/u/15197
如有任何疑问，欢迎大家留言，共同进步~~~

墨力计划

最后修改时间：2024-12-23 10:44:08

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

磐维数据库（共53篇）

磐维数据库，简称"PanWeiDB"。是中国移动信息技术中心首个基于中国本土开源数据库打造的面向ICT基础设施的自研数据库产品。其产品内核能力基于华为openGauss开源软件，并进一步提升了系统稳定性。磐维数据库具有高性能、高可靠、高安全、高兼容等特点，能够满足复杂多变的业务需求。磐维数据库提供了自动化、流程化的解决方案，实现了一键式数据迁移。这种高效的数据迁移方式不仅提高了迁移数据的效率，也降低了操作难度，为用户带来了极大的便利。

磐维数据库分区表使用

分区表概述

分区表类型

Range 分区表

List 分区表

Hash 分区表

system 分区表

Interval 分区表

分区表操作

二级分区

总结

文章被以下合辑收录

评论