人大金仓数据库KingbaseES 分区表创建介绍

金仓数据库KingbaseES 分区表创建介绍

关键字：

KingbaseES、分区表、人大金仓、KingbaseES

分区表介绍

在当前主流数据库中都有分区表这个功能。分区表是在一个节点内部对数据按照用户指定策略做进一步的水平分区，将表中数据按照指定方式划分为多个互不重叠的部分。

分区表相较与普通表具有以下优点：

• 改善查询性能：对分区表的查询可以只查找自己关心的目标分区，提升查询效率。
• 增强可用性：如果某个分区表的某个分区出现故障，不影响其他分区的数据。
• 均衡I/O：可以把分区表的不同分区映射到不同磁盘平衡I/O，改善系统性能。

分区类型

在KingbaseES数据库中支持范围分区、列表分区、哈希分区及子分区：

范围分区：根据表中的一列或多列数据，将要插入表中数据分为若干个范围，并为每个范围都创建分区，存储相应的数据。

列表分区：根据表中的一列数据，将要插入表中数据的分区键的键值分为若干个列表，并为每个列表都创建分区，存储相应数据。

哈希分区：根据表中的一列或多列数据，通过哈希映射将要插入表中的数据划分到对应分区中。

子分区：在已经存在分区的基础上建立二级分区，插入数据时将根据子分区约束放到对应子分区中。

需要注意的是范围分区和哈希分区支持多列数据进行分区操作，而列表分区只支持单列分区。

创建分区表

KingbaseES建立分区表主要有两种方式。第一种方式是在一条CREATE TABLE命令中直接建立分区表。第二种方式是使用多条CREATE TABLE命令增加分区表的分区。

在KingbaseES的手册中分区表建立的语法如下：

使用单条命令可以使用如下语法

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [ { column_name data_type [ INVISIBLE | VISIBLE ] [ COLLATE collation ] [ column_constraint [ ... ] ] | table_constraint | LIKE source_table [ like_option ... ] } [, ... ]] ) [ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [COLLATE collation ] [ opclass ] [, ... ] ) [ partition_extented_spec ] ]

使用多条命令时可以使用下面的语法增加parent_table的分区，需要注意的是parent_table中需要指定分区键：

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name PARTITION OF parent_table [ ( { column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ] | table_constraint } [, ... ] ) ] { FOR VALUES partition_bound_spec | DEFAULT } [ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]

测试用例

1. 单条语句建立分区表

范围分区的分区表：

create table t5( order_id number(7) primary key, order_date date, info1 varchar(10), info2 varchar(10)) partition by range(order_date)( partition P2020 values less than (to_date('01-01-2021', 'dd-mm-yyyy')), partition P2021 values less than (to_date('01-01-2022','dd-mm-yyyy'))); \d+ t5 --多列范围分区 CREATE TABLE t6 ( order_date date not null, info1 varchar(10), info2 varchar(10)) PARTITION BY RANGE (EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date))( Partition P202001 values less than (2020, 2), Partition P202002 values less than (2020, 3)); \d+ t6

列表分区：

create table t7( YYYYMMDD varchar(12 BYTE) primary key, band_code VARCHAR(6 byte), info1 text, info2 text) partition by List(band_code)( partition P1 values('p1'), partition P2 values('p2')); --尝试多列的列表分区,可以看到list分区不允许多列 create table t8( YYYYMMDD varchar(12 BYTE) primary key, band_code VARCHAR(6 byte), info1 text, info2 text) partition by List(info1, info2)( partition P1 values('p1', 'p1'), partition P2 values('p2', 'p1'));

哈希分区：

--使用多个partition+分区名指定分区个数 CREATE TABLE t9 ( order_id bigint not null, cust_id bigint not null, status text ) PARTITION BY HASH (order_id)( partition P1, partition P2); \d+ t9 --使用partitons+数字指明分区个数 CREATE TABLE t10 ( order_id bigint not null, cust_id bigint not null, status text ) PARTITION BY HASH (order_id) Partitions 5; \d+ t10 --建立多列的hash索引 CREATE TABLE t11 ( order_id bigint not null, cust_id bigint not null, status text ) PARTITION BY HASH (order_id，status) Partitions 5;

子分区：

CREATE TABLE t12( YYYYMMDD VARCHAR(12 BYTE), BAND_CODE VARCHAR(6 BYTE), text_str VARCHAR(120 BYTE) ) PARTITION by LIST(YYYYMMDD) SUBPARTITION BY LIST(BAND_CODE) (PARTITION P20190722 VALUES('20190722') (SUBPARTITION P20190722_YD VALUES('YD'), SUBPARTITION P20190722_XD VALUES('XD'), SUBPARTITION P20190722_DD VALUES('DD'))); \d+ t12 --使用\d+命令只能看到分区，无法看到子分区，查看子分区可以使用\d+ 分区名 \d+ t12_p20190722

1. 多条语句建立分区表

范围分区：

--使用多条语句建立分区表时分区的表名尽量使用'被分区表的名称_分区名'作为表名，使用其他形式表名分区名字将不确定。 CREATE TABLE t13( id serial, user_id int, create_time timestamp(0)) PARTITION BY RANGE(create_time); CREATE TABLE t13_2020 PARTITION OF t13 FOR VALUES FROM ('2020-01-01') TO ('2021-01-01'); CREATE TABLE t13_2021 PARTITION OF t13 FOR VALUES FROM ('2021-01-01') TO ('2022-01-01'); --多列范围分区 CREATE TABLE t14 ( order_date date not null, info1 varchar(10), info2 varchar(10)) PARTITION BY RANGE (EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date)); CREATE TABLE t14_202001 PARTITION OF t14 FOR VALUES FROM (2020,1) TO (2020,2); CREATE TABLE t14_202002 PARTITION OF t14 FOR VALUES FROM (2020,2) TO (2020,3); --需要注意是使用from（多列） to （多列）时判断条件基于行比较的规则，比如PARTITION BY RANGE (x,y) , FROM (1, 2) TO (3, 4) 的分区允许 x=1 绑定任意 y>=2 ， x=2 绑定任意非空 y ， x=3 绑定任意 y<4 .

列表分区：

create table t15( YYYYMMDD varchar(12 BYTE) primary key, band_code VARCHAR(6 byte), info1 text, info2 text) partition by List(band_code); CREATE TABLE t15_p1 PARTITION OF t15 FOR VALUES in ('p1'); CREATE TABLE t15_p2 PARTITION OF t15 FOR VALUES in ('p2');

哈希分区：

CREATE TABLE t16 ( order_id bigint not null, cust_id bigint not null, status text ) PARTITION BY HASH (order_id); CREATE TABLE t16_p1 PARTITION OF t16 FOR VALUES WITH (MODULUS 2, REMAINDER 0); CREATE TABLE t16_p2 PARTITION OF t16 FOR VALUES WITH (MODULUS 2, REMAINDER 1); --多列哈希分区 CREATE TABLE t17 ( order_id bigint not null, cust_id bigint not null, status text ) PARTITION BY HASH (order_id, status); CREATE TABLE t17_p1 PARTITION OF t17 FOR VALUES WITH (MODULUS 2, REMAINDER 0); CREATE TABLE t17_p2 PARTITION OF t17 FOR VALUES WITH (MODULUS 2, REMAINDER 1);

子分区：

--使用增加分区的分区方式建立子分区，这种方式可以建立多级分区 CREATE TABLE t18( YYYYMMDD VARCHAR(12 BYTE), BAND_CODE VARCHAR(6 BYTE), text_str VARCHAR(120 BYTE) ) PARTITION by LIST(YYYYMMDD); CREATE TABLE t18_p1 PARTITION OF t18 FOR VALUES in ('20200101') partition by list(band_code); CREATE TABLE t18_p1_XD PARTITION OF t18_p1 FOR VALUES in ('xd'); \d+ t18 \d+ t18_p1 CREATE TABLE t18_p1_YD PARTITION OF t18_p1 FOR VALUES in ('YD') partition by list(text_str); CREATE TABLE t18_p1_YD_pp PARTITION OF t18_p1_YD FOR VALUES in ('4'); \d+ t18_p1_YD --这里可以看到第三级分区 --使用多条语句创建分区表时，也可以一条语句创建两级分区,但无法使用subpartition关键字 CREATE TABLE t19( YYYYMMDD VARCHAR(12 BYTE), BAND_CODE VARCHAR(6 BYTE), text_str VARCHAR(120 BYTE) ) PARTITION by LIST(YYYYMMDD); CREATE TABLE t19_p1 PARTITION OF t19 FOR VALUES in ('20200101') partition by list(BAND_CODE) (PARTITION YD VALUES('YD')); CREATE TABLE t19_p2 PARTITION OF t19 FOR VALUES in ('20200102') partition by list(BAND_CODE) subpartition by list(text_str) (PARTITION sD VALUES('sD')(subpartition x1 values('sda')));

参考资料

《KingbaseES产品手册》