创建和管理分区表
分区表概述
分区表通过对分区列的判断，把分区列不同的记录放到不同的分区中。查询时可以通过查询表来访问各个分区中的数据，也可以通过在查询时直接指定分区的方法来进行查询。
本章节主要介绍在 VexDB 数据库中如何创建和管理分区表，指导用户正确创建表和管理分区表。
背景信息
VexDB 数据库支持的分区表：Range分区表、List分区表、Hash分区表、system分区表、Interval分区表和垂直分区。
- Range分区表：范围分区根据用户为每个分区建立的分区键值的范围将数据映射到分区。这种分区方式是最常见的，并且分区键值经常采用日期作为分区键，例如将销售数据按照月份进行分区。Range分区支持使用like进行模糊查询（即like的前缀匹配），参见示例2。当前使用like进行模糊查询支持字符串相关类型的分区键，如name、text、bpchar等。
- List分区表：将数据中包含的键值分别存储在不同的分区中，依次将数据映射到每一个分区，分区中包含的键值由创建分区表时指定，键值最多不超过127个。
- Hash分区表：将数据根据内部哈希算法依次映射到每一个分区中，包含的分区个数由创建分区表时指定。
Hash分区只支持1个分区键，分区键值能用表的普通字段来指定，分区范围的值只支持常量表达式、数值或字符串常量，不支持新增和删除分区。 - system分区表：系统分区可以对没有分区键的表进行分区。创建分区时可以指定分区名称，也可以不指定分区名称，由系统自动分配。
- system分区表暂不支持二级分区、组合分区。
- Interval分区表：interval分区是range分区的扩展。对于连续数据类型的Range分区，如果插入的新数据值与当前分区均不匹配，自动interval分区可以自动创建分区。详细请参考支持INTERVAL分区表。
- 垂直分区表：垂直分区功能可将一个数据表按列进行分区。常用于以下场景：
- 字段冷热分离，将频繁访问字段放在一个分区，不常访问的放在另外分区，从而减少无关的物理I/O。
- 大字段列单独存放，如BLOB列，可将BLOB列单独放在一个分区，其他列放在一个分区，从而减少单独查询其他列的时间。
- 关联关系列放一个分区，例如查询中倾向于一起访问的某些字段，如 a、b、C 列始终一起访问，则将它们放在一个分区，d、e 列总是一起访问，则将它们放在另一个分区。
分区表和普通表相比具有以下优点：
- 改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索效率。
- 增强可用性：如果分区表的某个分区出现故障，表在其他分区的数据仍然可用。
- 方便维护：如果分区表的某个分区出现故障，需要修复数据，只修复该分区即可。
- 均衡I/O：可以把不同的分区映射到不同的磁盘以平衡I/O，改善整个系统性能。
VexDB 支持分区剪枝功能，分区剪枝是指优化器自动提取出需要扫描的分区，减少扫描的数据块，从而避免全表扫描，提高性能。详细内容请参考分区剪枝。
注意事项
- 普通表若要转成分区表，需要新建分区表，然后把普通表中的数据导入到新建的分区表中。因此在初始设计表时，请根据业务提前规划是否使用分区表。
- 列存表仅支持使用垂直分区进行分区操作。
- 暂不支持对MOT表进行分区操作。
- 交换分区的普通表，必须与分区表的字段个数、字段类型都完全一致时才可以进行exchange操作，且普通表不能是临时表（支持增删列之后的表进行分区交换）。
- 不支持macaddr、macaddr8作为分区键。
- 垂直分区表使用限制：
- 不支持二级子分区。
- 存储方式只支持列存。
- 不支持本地分区索引。
- 不支持列存表不支持的特性如trigger、foreign key、alter table、modify partition等。
示例
示例1： 按照以下方式对Range分区表进行操作。
- 创建表空间。
CREATE TABLESPACE example1 RELATIVE LOCATION 'tablespace/tablespace_1'; CREATE TABLESPACE example2 RELATIVE LOCATION 'tablespace/tablespace_2'; CREATE TABLESPACE example3 RELATIVE LOCATION 'tablespace/tablespace_3'; CREATE TABLESPACE example4 RELATIVE LOCATION 'tablespace/tablespace_4'; - 创建分区表并插入数据。
CREATE TABLE customer_address ( ca_address_sk integer NOT NULL , ca_address_id character(16) NOT NULL , ca_street_number character(10) , ca_street_name character varying(60) , ca_street_type character(15) , ca_suite_number character(10) , ca_city character varying(60) , ca_county character varying(30) , ca_state character(2) , ca_zip character(10) , ca_country character varying(20) , ca_gmt_offset numeric(5,2) , ca_location_type character(20) ) TABLESPACE example1 PARTITION BY RANGE (ca_address_sk) ( PARTITION P1 VALUES LESS THAN(5000), PARTITION P2 VALUES LESS THAN(10000), PARTITION P3 VALUES LESS THAN(15000), PARTITION P4 VALUES LESS THAN(20000), PARTITION P5 VALUES LESS THAN(25000), PARTITION P6 VALUES LESS THAN(30000), PARTITION P7 VALUES LESS THAN(40000), PARTITION P8 VALUES LESS THAN(MAXVALUE) TABLESPACE example2 ) ENABLE ROW MOVEMENT; insert into customer_address(ca_address_sk,ca_address_id,ca_street_number) values(12000,'a4','a3'),(20000,'w1','w2'),(50000,'w1','w2'); - 插入数据。
将表customer_address的数据插入到表web_returns_p2中。
例如在数据库中创建了一个表customer_address的备份表web_returns_p2，现在需要将表customer_address中的数据插入到表web_returns_p2中，则可以执行如下命令。
创建备份表web_returns_p2并插入数据。CREATE TABLE web_returns_p2 ( ca_address_sk integer NOT NULL , ca_address_id character(16) NOT NULL , ca_street_number character(10) , ca_street_name character varying(60) , ca_street_type character(15) , ca_suite_number character(10) , ca_city character varying(60) , ca_county character varying(30) , ca_state character(2) , ca_zip character(10) , ca_country character varying(20) , ca_gmt_offset numeric(5,2) , ca_location_type character(20) ) TABLESPACE example1 PARTITION BY RANGE (ca_address_sk) ( PARTITION P1 VALUES LESS THAN(5000), PARTITION P2 VALUES LESS THAN(10000), PARTITION P3 VALUES LESS THAN(15000), PARTITION P4 VALUES LESS THAN(20000), PARTITION P5 VALUES LESS THAN(25000), PARTITION P6 VALUES LESS THAN(30000), PARTITION P7 VALUES LESS THAN(40000), PARTITION P8 VALUES LESS THAN(MAXVALUE) TABLESPACE example2 ) ENABLE ROW MOVEMENT; INSERT INTO web_returns_p2 SELECT * FROM customer_address; - 重命名分区。
ALTER TABLE web_returns_p2 RENAME PARTITION P8 TO P_9; ALTER TABLE web_returns_p2 RENAME PARTITION FOR (40000) TO P8; - 查询分区P8。
SELECT * FROM web_returns_p2 PARTITION (P8);
返回结果如下：ca_address_sk | ca_address_id | ca_street_number | ca_street_name | ca_street_type | ca_suite_number | ca_city | ca_county | c ---------------+------------------+------------------+----------------+----------------+-----------------+---------+-----------+-- 50000 | w1 | w2 | | | | | | (1 row) - 删除分区表。
DROP TABLE web_returns_p2; DROP TABLE customer_address; DROP TABLESPACE example1; DROP TABLESPACE example2; DROP TABLESPACE example3; DROP TABLESPACE example4;
示例2： 按照以下方式对Hash分区表进行操作。
创建分区表（ Hash分区只支持1个分区键，分区键值能用表的普通字段来指定，分区范围的值只支持常量表达式、数值或字符串常量，不支持新增和删除分区。）
语法格式如下：
CREATE TABLE [IF NOT EXISTS] table_name
…
PARTITION BY HASH (columns_name)
hash_partition_desc
…
- 创建分区表。
CREATE TABLE t_hash_1 (c1 integer, c2 date, c3 text) PARTITION BY HASH (c1) ( PARTITION t_hash_p1, PARTITION t_hash_p2 ); - 插入数据并查询结果。
insert into t_hash_1 values(1,'2020-07-29','a'); SELECT * FROM t_hash_1 PARTITION (t_hash_p1);
返回结果如下：c1 | c2 | c3 ----+---------------------+---- 1 | 2020-07-29 00:00:00 | a (1 row) - 更新数据并查询结果。
UPDATE t_hash_1 set c1=3 where c1=1; SELECT * FROM t_hash_1 PARTITION (t_hash_p2);
返回结果如下：c1 | c2 | c3 ----+---------------------+---- 3 | 2020-07-29 00:00:00 | a (1 row) - 删除数据并查询结果。
DELETE t_hash_1 where c1=3; SELECT * FROM t_hash_1;
返回结果如下：c1 | c2 | c3 ----+----+---- (0 rows) - 删除分区表。
DROP TABLE t_hash_1;
示例3： 按照以下方式对List分区表进行操作。
- 创建分区表。
CREATE TABLE t_list (c1 integer, c2 date, c3 text) PARTITION BY LIST (c2) ( PARTITION p1 VALUES ('2019-10-12'), PARTITION p2 VALUES ('2019-10-13'), PARTITION p3 VALUES ('2019-10-14') ); - 修改分区表行迁移属性。
alter table t_list enable row movement; - 新增分区。
ALTER TABLE t_list ADD PARTITION P4 VALUES ('2019-10-15'); - 删除分区。
ALTER TABLE t_list DROP PARTITION p4; - 插入数据并查询结果。
insert into t_list values(1,'2019-10-13','test'); SELECT * FROM t_list PARTITION (p2);
返回结果如下：c1 | c2 | c3 ----+---------------------+------ 1 | 2019-10-13 00:00:00 | test (1 row) - 查看其它的分区：
SELECT * FROM t_list PARTITION (p1);
返回结果如下：c1 | c2 | c3 ----+----+---- (0 rows) - 新数据并查询结果。
update t_list set c2='2019-10-12' where c1=1; SELECT * FROM t_list PARTITION (p2); SELECT * FROM t_list PARTITION (p1);
返回结果如下：c1 | c2 | c3 ----+----+---- (0 rows) c1 | c2 | c3 ----+---------------------+------ 1 | 2019-10-12 00:00:00 | test (1 row) - 删除分区表。
DROP TABLE t_list;
示例4： 按照以下方式对system分区表进行操作。
- 创建分区表（指定分区名称），并向其中插入数据。
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; - 查询系统自动生成的分区名称。
（1）查询系统表pg_partition。\d+ system_par_tab2
返回结果如下：Table "public.system_par_tab2" Column | Type | Modifiers | Storage | Stats target | Description -------------+-------------+-----------+----------+--------------+------------- id | numeric | | main | | code | varchar(10) | | extended | | description | varchar(50) | | extended | | Partition By SYSTEM Number of partitions: 1 (View pg_partition to check each partition range.) Has OIDs: no Options: orientation=row, compression=no, fillfactor=80
（2）查询表记录。select relname,parentid,partstrategy from pg_partition where relname='system_par_tab2';
返回结果如下：relname | parentid | partstrategy -----------------+----------+-------------- system_par_tab2 | 16707 | h system_par_tab2 | 16707 | s (2 rows)
（3）执行\x命令开始列式输出模式并获取上面的parentid进行查询。\x select * from pg_partition where parentid='16707';
返回结果如下，其中system_par_tab2 就是系统自动生成的分区名：-[ RECORD 1 ]------+---------------------------------------------------------------- relname | system_par_tab2 parttype | r parentid | 16707 rangenum | 0 intervalnum | 0 partstrategy | s subpartstrategy | n relfilenode | 0 reltablespace | 0 relpages | 0 reltuples | 0 relallvisible | 0 reltoastrelid | 0 reltoastidxid | 0 indextblid | 0 indisusable | t reldeltarelid | 0 reldeltaidx | 0 relcudescrelid | 0 relcudescidx | 0 relfrozenxid | 0 intspnum | partkey | subpartkey | intervaltablespace | interval | boundaries | transit | reloptions | {orientation=row,compression=no,fillfactor=80,wait_clean_gpi=n} subparttemplate | relfrozenxid64 | 0 relminmxid | 0 partkeyexpr | partitionno | -1 subpartitionno | -[ RECORD 2 ]------+---------------------------------------------------------------- relname | system_par_tab2 parttype | p parentid | 16707 rangenum | 0 intervalnum | 0 partstrategy | h subpartstrategy | n relfilenode | 16711 reltablespace | 0 relpages | 0 reltuples | 0 relallvisible | 0 reltoastrelid | 16712 reltoastidxid | 0 indextblid | 0 indisusable | t reldeltarelid | 0 reldeltaidx | 0 relcudescrelid | 0 relcudescidx | 0 relfrozenxid | 14918 intspnum | partkey | subpartkey | intervaltablespace | interval | boundaries | transit | reloptions | {orientation=row,compression=no,fillfactor=80} subparttemplate | relfrozenxid64 | 14918 relminmxid | 2 partkeyexpr | partitionno | 1 subpartitionno | - 关闭列式输出模式向分区表中插入数据。
\x off 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分区表中插入数据时必须指定其分区名称。 - 查询分区表。
select * from system_par_tab;
返回结果如下：c1 | c2 | c3 ----+---------------------+---- 1 | 2022-01-01 00:00:00 | p1 2 | 2022-02-01 00:00:00 | p2 3 | 2022-03-01 00:00:00 | p3 - 更新数据。
update system_par_tab partition(part_2) set c3='p5' where c1 ='2'; - 新增分区。
alter table system_par_tab add partition part_4; - 删除分区。
alter table system_par_tab drop partition part_4; - 清空分区表。
truncate table system_par_tab2; - 清空指定分区。
alter table system_par_tab truncate partition part_1; - 删除分区表。
DROP TABLE system_par_tab2; DROP TABLE system_par_tab;
default 分区
功能描述
针对Range和List分区，支持定义和使用default分区。创建分区表的时候可以指定创建default分区，对于已经创建的分区表，可以新增和删除default分区。
Range类型的分区表，可以通过指定分区范围值为MAXVALUE来包含所有情况的值。
注意事项
- 已存在default分区的LIST分区不能新增分区。
- 删除分区时不能只剩下一个default分区。
- 在创建分区表时，不能只定义一个default分区，并且default分区必须定义在最后。
- 已经存在default分区的分区表，不能新增分区。
- 删除分区时，不能只剩下一个default分区。
- default分区不能进行SPLIT和MERGE操作。
语法格式
- List分区表创建分区表时指定创建default分区，default分区必须定义为最后一个分区，SQL语法如下：
CREATE TABLE [IF NOT EXISTS]table_name ...... PARTITION BY [LIST ]({column_name|(expression)}) ( PARTITION partition_name..., PARTITION partition_name VALUES(DEFAULT)[TABLESPACE tablespace_name] ) - List分区表新增default分区的SQL语法如下：
ALTER TABLE table_name ADD PARTITION partition_name VALUES(DEFAULT) [TABLESPACE tablespace_name]; - List分区表删除default分区SQL语法如下：
ALTER TABLE partition_name DROP PARTITION partition_name; - Range分区表创建分区表时指定创建default分区，SQL语法如下：
CREATE TABLE [IF NOT EXISTS]table_name ...... PARTITION BY [RANGE ]({column_name|(expression)}) ( PARTITION partition_name..., PARTITION partition_name VALUES LESS THAN (MAXVALUE) [TABLESPACE tablespace_name] ) - Range分区新增default分区的SQL语法如下：
ALTER TABLE table_name ADD PARTITION partition_name VALUES LESS THAN (MAXVALUE) [TABLESPACE tablespace_name]; - Range分区表删除default分区SQL语法如下：
ALTER TABLE partition_name DROP PARTITION partition_name;
示例
- 创建一个分区表。
create table t_partition_list4(col number,name varchar2(20)) partition by list(col)( partition t_list_p1 values(1,3,5,7,9) , partition t_list_p2 values(2,4,6,8,10) ); - 添加一个默认分区并插入数据。
alter table t_partition_list4 add partition t_list_default values(default); insert into t_partition_list4 values(1,'t_list_p1'); insert into t_partition_list4 values(2,'t_list_p2'); insert into t_partition_list4 values(31,'t_list_default'); - 查询分区表中的数据。
- 查询分区t_list_p1的数据。
select * from t_partition_list4 partition(t_list_p1);
返回结果如下：col | name -----+----------- 1 | t_list_p1 (1 row) - 查询分区t_list_p2的数据。
select * from t_partition_list4 partition(t_list_p2);
返回结果如下：col | name -----+----------- 2 | t_list_p2 (1 row) - 查询分区t_list_default的数据。
select * from t_partition_list4 partition(t_list_default);
返回结果如下：col | name -----+---------------- 31 | t_list_default (1 row)
- 查询分区t_list_p1的数据。
- 清理环境。
drop table t_partition_list4;
interval分区表
功能描述
interval分区是range分区的扩展。
在没有自动interval分区特性之前，在创建范围（Range）类型分区表时，用户通常会定义一个maxvalue分区，将没有落在当前关注范围内的数据，都放到该分区中，以免发生插入的元组的分区键值不能映射到任何分区的错误。然而当业务场景发生变化时，如果没有及时根据数据增长创建新的分区，就可能会导致分区表的数据发生倾斜，大多数数据都放到了这个未进行细分的maxvlaue分区中，这就违背了用户对表进行分区的初衷：用户希望各个分区的数据量均衡，这样才能加快查询。
自动interval分区可以改善这个问题，对于连续数据类型的Range分区，如果插入的新数据值与当前分区均不匹配，自动interval分区可以自动创建分区。
注意事项
列存表不支持间隔分区。
语法格式
CREATE TABLE table_name
(
...
)
PARTITION BY RANGE(column1)
INTERVAL ('interval_expr') [ STORE IN (tablespace_name [, … ] ) ]
(
PARTITION partition_name1 VALUESLESS THAN ( { partition_value | MAXVALUE } )
PARTITION partition_name2 VALUES LESS THAN ( { partition_value | MAXVALUE } )
);
参数说明
- PARTITION BY RANGE(column1)
指定一个range分区的列。 - INTERVAL ('interval_expr') STORE IN (tablespace_name , … )
间隔分区定义信息。- interval_expr：自动创建分区的间隔，例如：1 day、1 month。
- STORE IN (tablespace_name , … )：指定存放自动创建分区的表空间列表，如果有指定，则自动创建的分区从表空间列表中循环选择使用，否则使用分区表默认的表空间。
- PARTITION partition_name VALUES LESS THAN ( { partition_value | MAXVALUE } )
指定各分区的信息。partition_name为范围分区的名称。partition_value为范围分区的上边界，取值依赖于partition_key的类型。MAXVALUE表示分区的上边界，它通常用于设置最后一个范围分区的上边界。- 每个分区都需要指定一个上边界。
- 分区上边界的类型应当和分区键的类型一致。
- 分区列表是按照分区上边界升序排列的，值较小的分区位于值较大的分区之前。
示例
- 创建interval分区表。
CREATE TABLE interval_normal_exchange (logdate date not null) PARTITION BY RANGE(logdate) INTERVAL('1 month') ( PARTITION interval_normal_exchange_p1 VALUES LESS THAN('2020-03-01'), PARTITION interval_normal_exchange_p2 VALUES LESS THAN('2020-04-01'), PARTITION interval_normal_exchange_p3 VALUES LESS THAN('2020-05-01') ); - 执行以下语句查询分区情况。
SELECT relname,parttype,partstrategy,boundaries FROM pg_partition WHERE parentid = (SELECT oid FROM pg_class WHERE relname = 'interval_normal_exchange') ORDER BY relname;
返回结果如下：relname | parttype | partstrategy | boundaries -----------------------------+----------+--------------+-------------- interval_normal_exchange | r | i | interval_normal_exchange_p1 | p | r | {2020-03-01} interval_normal_exchange_p2 | p | r | {2020-04-01} interval_normal_exchange_p3 | p | r | {2020-05-01} (4 rows) - 清理环境。
drop table interval_normal_exchange;
二级分区
功能描述
二级分区功能，即在原有的range分区、list分区、hash分区、interval分区的基础上再次进行分区。
二级分区可以对表中的每个分区再次进行分区。分区类型有range、list、hash三种，一级与二级分区的分区类型可以任意组合。二级分区支持使用CREATE/ALTER/SELECT语法，用于二级分区的创建与增删改查。二级分区的相关信息可在系统表PG_PARTITION中获取。
注意事项
interval分区不能作为二级分区。
语法格式
创建、修改和删除二级分区的语法分别是：CREATE TABLE SUBPARTITION、ALTER TABLE SUBPARTITION。用户也可以使用如下命令在vsql客户端中查询相关SQL语法的使用帮助信息。
\h create table subpartition
\h alter table subpartition
二级分区的增删改查
对二级分区表插入数据的语法与普通表的语法没有任何差别。当向二级分区表插入数据时，会根据一级分区和二级分区规则，把数据分布到匹配的二级分区中进行存储。
以下列举了部分与二级分区相关的常用SQL句式。
- 新增一级与二级分区：
ALTER TABLE table_name ADD partition_desc [ ( subpartition_desc [, ... ] ) ] - 删除指定一级分区包括属于它的所有二级分区：
ALTER TABLE table_name DROP PARTITION { partition_name | { FOR (value [ , … ] )} - 为指定一级分区新增二级分区：
ALTER TABLE table_name MODIFY PARTITION partition_name ADD subpartition_desc - 删除指定二级分区：
ALTER TABLE table_name DROP SUBPARTITION { subpartition_name | { FOR (value [ , … ])} - 删除数据：
DELETE FROM table_name [ PARTITION { ( partition_name ) | { FOR ( value [ , ... ] ) } } ] [ WHERE ...] DELETE FROM table_name [ SUBPARTITION { ( partition_name ) | { FOR ( value [ , ... ] ) } } ] [ WHERE ...] - 更新数据：
UPDATE table_name [ PARTITION { ( partition_name ) | { FOR ( value [ , ... ] ) } } ] SET [ WHERE ...] UPDATE table_name [ SUBPARTITION { ( partition_name ) | { FOR ( value [ , ... ] ) } } ] SET [ WHERE ...] - 在指定二级分区查找数据：
SELECT ... FROM table_name SUBPARTITION (subpartition_name) - 单独查询某个分区或子分区数据：
SELECT ... FROM table_name PARTITION { ( partition_name ) | FOR ( value [ , ... ] ) } SELECT ... FROM table_name SUBPARTITION { ( subpartition_name ) | {FOR ( value [, … ] ) } - 当查询二级分区表的数据时（不能指定分区或子分区），如果查询条件中包含有分区键的条件，则在生成执行计划时会根据分区键条件过滤掉不符合条件的分区或子分区：
EXPLAIN SELECT ... FROM table_name WHERE ...
参数说明
- table_name
表名。 - partition_name
分区表的名称。 - subpartition_name
二级分区表的名称。 - SUBPARTITION BY RANGE | LIST | HASH
根据column_name指定的字段进行二级分区，分区类型可以是RANGE、LIST、 HASH其一。 - SUBPARTITION TEMPLATE ( subpartition_desc , …)
常规子分区模板定义语法，适用于Range/List/Hash子分区，当分区定义中没有指定子分区的定义时会根据子分区模板自动生成二级分区。
若没有提供子分区模板，同时partition_desc也不指定subpartition_desc，则将创建一个默认子分区。 - hash_subpartition_by_quantity
Hash分区持有的子分区模板定义语法（也可用与子分区定义语法），指定创建N个Hash子分区，同时可选指定Hash子分区的tablespace名列表。
示例
示例1： 二级分区的创建，修改和删除。
- 创建一个分区表并插入数据。
create table t_part_list_range ( id number not null, partition_key int, subpartition_key int, col2 varchar2(10) ) partition by list(partition_key) subpartition by range(subpartition_key) ( partition t_partition_01 values (100) (subpartition sub_1_1 values less than (10), subpartition sub_1_2 values less than (20) ), partition t_partition_02 values (200) (subpartition sub_2_1 values less than (10), subpartition sub_2_2 values less than (20) ) ); insert into t_part_list_range values(1,100,5,'sub_1_1'); insert into t_part_list_range values(2,100,15,'sub_1_2'); insert into t_part_list_range values(3,200,5,'sub_2_1'); insert into t_part_list_range values(4,200,15,'sub_2_2'); insert into t_part_list_range values(5,200,16,'sub_2_2'); select * from t_part_list_range subpartition for (100,5); - 新增一级与二级分区。
alter table t_part_list_range add partition t_partition_03 values (300) ( subpartition sub_3_1 values less than (10), subpartition sub_3_2 values less than (20) ); - 删除指定一级分区包括属于它的所有二级分区。
alter table t_part_list_range drop partition t_partition_02; - 为指定一级分区新增二级分区。
alter table t_part_list_range modify partition t_partition_01 add subpartition sub_1_3 values less than (30); - 删除指定二级分区。
alter table t_part_list_range drop subpartition sub_1_3; - 清理环境。
drop table t_part_list_range;
示例2： 一级分区为interval分区，二级分区为list分区，分区键类型为字符类型；创建并查看分区结果。
- 创建测试表，包含一级分区和二级分区。
CREATE TABLE t_subpartition_interval_list( partition_key date, subpartition_key varchar(20), test varchar(20) )partition by range(partition_key) interval('12 month') subpartition by list(subpartition_key) (partition partition_p1 VALUES LESS THAN ('2019-01-01') (subpartition sub_1_1 values('test1'), subpartition sub_1_2 values('test2'), subpartition sub_1_3 values (default) ), partition partition_p2 VALUES LESS THAN ('2021-01-01') (subpartition sub_2_1 values('test1'), subpartition sub_2_2 values('test2'), subpartition sub_2_3 values (default) ) ); - 插入测试数据。
INSERT INTO t_subpartition_interval_list VALUES('2018-09-02','test','test1'); INSERT INTO t_subpartition_interval_list VALUES('2020-12-12','test2','test2'); INSERT INTO t_subpartition_interval_list VALUES('2022-02-02','interval','test3'); - 查询已有分区对应数据。
select * from t_subpartition_interval_list subpartition(sub_1_3); select * from t_subpartition_interval_list subpartition(sub_2_2);
返回结果如下：partition_key | subpartition_key | test ---------------------+------------------+------- 2018-09-02 00:00:00 | test | test1 (1 row) partition_key | subpartition_key | test ---------------------+------------------+------- 2020-12-12 00:00:00 | test2 | test2 (1 row) - 查询表新增分区名。
select relname,parttype,partstrategy,boundaries from pg_partition where parentid = (select oid from pg_class where relname = 't_subpartition_interval_list') union all select relname,parttype,partstrategy,boundaries from pg_partition where parentid in ( select oid from pg_partition where parentid = ( select oid from pg_class where relname ='t_subpartition_interval_list' ) order by relname) order by relname;
返回结果如下：relname | parttype | partstrategy | boundaries ------------------------------+----------+--------------+------------------------- partition_p1 | p | r | {2019-01-01} partition_p2 | p | r | {2021-01-01} sub_1_1 | s | l | {test1} sub_1_2 | s | l | {test2} sub_1_3 | s | l | sub_2_1 | s | l | {test1} sub_2_2 | s | l | {test2} sub_2_3 | s | l | sys_p1 | p | i | {"2023-01-01 00:00:00"} sys_p1_subpartdefault1 | s | l | t_subpartition_interval_list | r | i | (11 rows) - 查询新增分区对应数据。
select * from t_subpartition_interval_list subpartition(sys_p1_subpartdefault1);
返回结果如下：partition_key | subpartition_key | test ---------------------+------------------+------- 2022-02-02 00:00:00 | interval | test3 (1 row) - 清理环境。
drop table t_subpartition_interval_list;
分区剪枝
分区剪枝是指优化器自动提取出需要扫描的分区，减少扫描的数据块，从而避免全表扫描，提高性能。VexDB 支持静态分区剪枝和动态分区剪枝。
静态分区剪枝
功能描述
静态分区剪枝是一种优化技术，用于提高查询性能。当在对分区表执行SQL查询时，如果查询条件中包含分区键，优化器会在编译阶段就能够判断出哪些分区是不必要的，并且可以直接排除这些分区的扫描。
表1 支持的分区类型及表达式类型
| 分区类型 | 表达式 | 分区等级 |
|---|---|---|
| 范围(Range)分区 | <,<=,=,>,>=,like,any,all | 一级、二级分区 |
| 列表(List)分区 | <,<=,>,>=,<>(不等于) | 一级、二级分区 |
| 哈希(Hash)分区 | = | 一级、二级分区 |
| 间隔(Interval)分区 | <,<=,=,>,>=,any,all | 一级、二级分区 |
注意事项
- 多个分区键的List分区表，操作符<>存在剪枝不完全的情况，不建议使用。
- List分区表查询中如果有多个<>操作符的查询条件，存在剪枝不完全的情况，不建议使用。
- 二级分区表分区键一致时存在剪枝不完全的情况，不建议使用。
- 表达式like仅支持字符类型。
示例
示例1: 一级分区表静态剪枝。
- 创建测试表并插入数据。
create table par1_1187644(id int,a1 text,a2 date,a3 varchar(30)) partition by list (id) ( partition p1 values(100), partition p2 values(200), partition p3 values(300), partition p4 values(400), partition p5 values(null)); insert into par1_1187644 values(100,chr(65 + (generate_series(1,100)-1)%25),generate_series(DATE '2022-01-01', DATE '2022-4-10', '1 day'),chr(65 + (generate_series(1,100)-1)%25)); insert into par1_1187644 values(200,chr(65 + (generate_series(1,100)-1)%25),generate_series(DATE '2022-01-01', DATE '2022-4-10', '1 day'),chr(65 + (generate_series(1,100)-1)%25)); insert into par1_1187644 values(300,chr(65 + (generate_series(1,100)-1)%25),generate_series(DATE '2022-01-01', DATE '2022-4-10', '1 day'),chr(65 + (generate_series(1,100)-1)%25)); insert into par1_1187644 values(400,chr(65 + (generate_series(1,100)-1)%25),generate_series(DATE '2022-01-01', DATE '2022-4-10', '1 day'),chr(65 + (generate_series(1,100)-1)%25)); insert into par1_1187644 values(null,chr(65 + (generate_series(1,100)-1)%25),generate_series(DATE '2022-01-01', DATE '2022-4-10', '1 day'),chr(65 + (generate_series(1,100)-1)%25)); - 指定条件查询数据。
select * from par1_1187644 where id>212 limit 5;
返回结果如下：id | a1 | a2 | a3 -----+----+---------------------+---- 300 | A | 2022-01-01 00:00:00 | A 300 | B | 2022-01-02 00:00:00 | B 300 | C | 2022-01-03 00:00:00 | C 300 | D | 2022-01-04 00:00:00 | D 300 | E | 2022-01-05 00:00:00 | E (5 rows) - 查看执行计划验证剪枝效果。
explain (costs off) select * from par1_1187644 where id>212 limit 5;
返回结果如下，可以看出只扫描了部分分区：QUERY PLAN -------------------------------------------------- Limit -> Partition Iterator Iterations: 2 -> Partitioned Seq Scan on par1_1187644 Filter: (id > 212) Selected Partitions: 3..4 (6 rows) - 清理环境。
drop table par1_1187644;
示例2: 二级分区表静态剪枝。
- 创建二级分区表。
create table par1_1187650(id int,a1 text,a2 date,a3 varchar(30)) partition by range (id) subpartition by list(a1) ( partition p1 values less than(100)( subpartition p1_1 values('g'), subpartition p1_2 values('n'), subpartition p1_3 values('z'), subpartition p1_4 values(null)), partition p2 values less than(200)( subpartition p2_1 values('g'), subpartition p2_2 values('n'), subpartition p2_3 values('z'), subpartition p2_4 values(null)), partition p3 values less than(300)( subpartition p3_1 values('g'), subpartition p3_2 values('n'), subpartition p3_3 values('z'), subpartition p3_4 values(null)), partition p4 values less than(401)( subpartition p4_1 values('g'), subpartition p4_2 values('n'), subpartition p4_3 values('z'), subpartition p4_4 values(null))); - 插入测试数据。
insert into par1_1187650 values(generate_series(1,100),'g',generate_series(DATE '2022-01-01', DATE '2022-4-10', '1 day'),chr(65 + (generate_series(1,100)-1)%25)); insert into par1_1187650 values(generate_series(101,200),'n',generate_series(DATE '2022-01-01', DATE '2022-4-10', '1 day'),chr(65 + (generate_series(1,100)-1)%25)); insert into par1_1187650 values(generate_series(201,300),'z',generate_series(DATE '2022-01-01', DATE '2022-4-10', '1 day'),chr(65 + (generate_series(1,100)-1)%25)); insert into par1_1187650 values(generate_series(301,400),null,generate_series(DATE '2022-01-01', DATE '2022-4-10', '1 day'),chr(65 + (generate_series(1,100)-1)%25)); - 指定条件查询数据。
select * from par1_1187650 where id>212 and a1>'d' limit 5;
返回结果如下：id | a1 | a2 | a3 -----+----+---------------------+---- 213 | z | 2022-01-13 00:00:00 | M 214 | z | 2022-01-14 00:00:00 | N 215 | z | 2022-01-15 00:00:00 | O 216 | z | 2022-01-16 00:00:00 | P 217 | z | 2022-01-17 00:00:00 | Q (5 rows) - 查看执行计划验证剪枝效果。
explain (costs off) select * from par1_1187650 where id>212 and a1>'d' limit 5;
返回结果如下，可以看出只扫描了部分一级、二级分区，其中Selected Subpartitions字段的值代表二级分区的数量。QUERY PLAN --------------------------------------------------------- Limit -> Partition Iterator Iterations: 2, Sub Iterations: 6 -> Partitioned Seq Scan on par1_1187650 Filter: ((id > 212) AND (a1 > 'd'::text)) Selected Partitions: 3..4 Selected Subpartitions: 3:3, 4:3 (7 rows) - 清理环境。
drop table par1_1187650;
动态分区剪枝
功能描述
动态分区剪枝是与静态分区剪枝相对的一种优化技术，同样用于提高查询性能。在处理分区表时，当SQL查询条件中包含变量或者表达式，它们的值只有在运行时才能确定的情况下，数据库引擎能够在执行阶段根据实际的查询参数值来决定应当访问哪些分区，并排除不必要的分区扫描。
VexDB 支持对Range、List、Hash、Interval分区表进行动态分区剪枝，支持的场景如下：
- 支持PREPARE/EXECUTE语法对一级、二级分区表动态分区剪枝。需注意查询条件需包含分区键，且条件值为参数。
- 支持InitPlan场景的一级、二级分区表动态分区剪枝。需注意分区键的条件为子连接，且为非相关子连接。
- 支持Nested Loop且连接条件可下推至分区表场景的一级、二级分区表动态分区剪枝，查询走nestloop计划，且包含分区键的条件，其中分区键存在本地分区索引。
前置条件
使用动态分区剪枝需设置GUC参数enable_runtime_prune为on，该参数默认值为on，参数类型为USERSET。
示例
- 创建测试表并插入测试数据。
create table par1_1188069(id int,a1 text,a2 date,a3 varchar(30)) partition by range (id) subpartition by list(a1) ( partition p1 values less than(100)( subpartition p1_1 values('d'), subpartition p1_2 values('k'), subpartition p1_3 values('q'), subpartition p1_4 values(null)), partition p2 values less than(200)( subpartition p2_1 values('d'), subpartition p2_2 values('k'), subpartition p2_3 values('q'), subpartition p2_4 values(null)), partition p3 values less than(300)( subpartition p3_1 values('d'), subpartition p3_2 values('k'), subpartition p3_3 values('q'), subpartition p3_4 values(null)), partition p4 values less than(401)( subpartition p4_1 values('d'), subpartition p4_2 values('k'), subpartition p4_3 values('q'), subpartition p4_4 values(null))); create index on par1_1188069(id)local; insert into par1_1188069 values(generate_series(1,100),'d',generate_series(DATE '2022-01-01', DATE '2022-4-10', '1 day'),chr(65 + (generate_series(1,100)-1)%25)); insert into par1_1188069 values(generate_series(101,200),'k',generate_series(DATE '2022-01-01', DATE '2022-4-10', '1 day'),chr(65 + (generate_series(1,100)-1)%25)); insert into par1_1188069 values(generate_series(201,300),'q',generate_series(DATE '2022-01-01', DATE '2022-4-10', '1 day'),chr(65 + (generate_series(1,100)-1)%25)); insert into par1_1188069 values(generate_series(301,400),null,generate_series(DATE '2022-01-01', DATE '2022-4-10', '1 day'),chr(65 + (generate_series(1,100)-1)%25)); - 确认GUC参数enable_runtime_prune的值是否为on。
show enable_runtime_prune;
返回结果如下：enable_runtime_prune ---------------------- on (1 row) - 使用PREPARE语法创建预备查询语句。
prepare i1_1188069(int,int) as select * from par1_1188069 where id>$1 and id<$2 limit 3; - 查看执行计划。
explain (analyze,costs off) execute i1_1188069(130,230);
返回结果如下，可以看出只扫描了部分分区：QUERY PLAN ------------------------------------------------------------------------------------------------------------------------ Limit (actual time=0.107..0.109 rows=3 loops=1) -> Partition Iterator (actual time=0.106..0.108 rows=3 loops=1) Iterations: PART -> Partitioned Index Scan using par1_1188069_id_idx on par1_1188069 (actual time=0.018..0.019 rows=3 loops=2) Index Cond: ((id > $1) AND (id < $2)) Selected Partitions: PART Selected Subpartitions: PART Total runtime: 0.288 ms (8 rows) - 关闭GUC参数enable_runtime_prune对比查询效果。
set enable_runtime_prune off; show enable_runtime_prune;
返回结果如下：enable_runtime_prune ---------------------- off (1 row) - 使用PREPARE语法创建预备查询语句。
prepare i1_1188070(int,int) as select * from par1_1188069 where id>$1 and id<$2 limit 3; - 查看执行计划。
explain (analyze,costs off) execute i1_1188070(130,230);
返回结果如下，可以看出执行了全表扫描。QUERY PLAN ------------------------------------------------------------------------------------------------------------------------ Limit (actual time=0.045..0.047 rows=3 loops=1) -> Partition Iterator (actual time=0.044..0.045 rows=3 loops=1) Iterations: 4, Sub Iterations: 16 -> Partitioned Index Scan using par1_1188069_id_idx on par1_1188069 (actual time=0.025..0.026 rows=3 loops=6) Index Cond: ((id > $1) AND (id < $2)) Selected Partitions: 1..4 Selected Subpartitions: ALL Total runtime: 0.200 ms (8 rows) - 清理环境。
drop table par1_1188069;
