基于开发者空间OpenGauss数据库的分区表项目实践

1 概述

1.1 案例介绍

分区表在数据库中使用广泛，尤其在数据库大表中对数据拆分，分表等领域，利用分区把表数据划分多个不同数据文件，利用表空间可把不同分区的表数据分布在不同磁盘，可以充分利用磁盘I/O和数据文件并行读取，以分区方式优化索引起到查询性能提升(因为不同分区会使优化器对执行计划剪枝)。

通过实际操作，让大家深入了解如何使用分区表，在日常开发中如何在项目中运用分区表的优点，从而掌握分区表在数据分布、索引优化的应用，体验其在应用开发中的优势。

1.2 适用对象

• 企业
• 个人开发者
• 高校学生

1.3 案例时间

本案例总时长预计60分钟。

1.4 案例流程

说明：

1. 领取空间开发桌面，登录云主机；
2. 在云主机终端进入OpenGaussDB；
3. 进入开发者空间部署OpenGaussDB数据库之分区表功能使用；

1.5 资源总览

资源名称	规格	单价(元)	时长(分钟)
开发者空间-云主机	鲲鹏通用计算增强型 kc2 | 4vCPUs | 8G | OpenEuler 22.03 Server定制版 (40GB)	免费	60

最新案例动态，请查阅 《基于开发者空间OpenGauss数据库的分区表项目实践》。小伙伴快来领取华为开发者空间，进入云开发环境桌面版实操吧！

2 分区表讲解

2.1 开发者空间配置

面向广大开发者群体，华为开发者空间提供一个随时访问的“开发桌面云主机”、丰富的“预配置工具集合”和灵活使用的“场景化资源池”，开发者开箱即用，快速体验华为根技术和资源。

领取云主机后可以直接进入 华为开发者空间工作台界面，点击打开云主机 > 进入桌面连接云主机。

2.2 分区表概述

分区表(Partitioned Table)指在单节点内对表数据内容按照分区键以及围绕分区键的分区策略对表进行逻辑切分。在openGauss中，数据分区是在一个节点内部对数据按照用户指定的策略做进一步的水平分表，将表中的数据按照指定方式划分为多个互不重叠的部分。

分区表实际上就是一种数据组织方式，是把逻辑上的一张表根据某种方案分成几张物理块进行存储。这张逻辑上的表称之为分区表，物理块称之为分区。分区表是一张逻辑表，不存储数据，数据实际是存储在分区上的。分区的小表独立管理，可以具有独立的索引和存储空间。分区表增强了数据库应用程序的性能、可管理性和可用性，并有助于降低存储大量数据的总体拥有成本。分区允许将表、索引和索引组织的表细分为更小的部分，使这些数据库对象能够在更精细的粒度级别上进行管理和访问。

GaussDB提供了丰富的分区策略和扩展，以满足不同业务场景的需求。由于分区策略的实现完全由数据库内部实现，对用户是完全透明的，因此它几乎可以在实施分区表优化策略以后做平滑迁移，无需潜在耗费人力物力的应用程序更改。

2.3 OpenGaussDB支持的分区表

OpenGauss数据库支持的分区表为一级分区表和二级分区表，其中一级分区表包括范围分区表、间隔分区表、列表分区表、哈希分区表四种，二级分区表包括范围分区、列表分区、哈希分区两两组合的九种。

一般情况下，我们只会使用到一级分区。常见的分区方案有范围分区(Range Partitioning)、间隔分区(Interval Partitioning)、哈希分区(Hash Partitioning)、列表分区(List Partitioning)、数值分区(Value Partition)等。目前行存表支持范围分区、间隔分区、哈希分区、列表分区。

• 范围分区表：范围分区是根据表的一列或者多列，将要插入表的记录分为若干个范围，这些范围在不同的分区里没有重叠。为每个范围创建一个分区，用来存储相应的数据，将数据基于范围映射到每一个分区。这个范围是由创建分区表时指定的分区键决定的。分区键经常采用日期，例如将销售数据按照月份进行分区。范围分区的分区策略是指记录插入分区的方式。目前范围分区仅支持范围分区策略。
• 范围分区策略：根据分区键值将记录映射到已创建的某个分区上，如果可以映射到已创建的某一分区上，则把记录插入到对应的分区上，否则给出报错和提示信息。这是最常用的分区策略。
• 间隔分区表：是一种特殊的范围分区表，相比范围分区表，新增间隔值定义，当插入记录找不到匹配的分区时，可以根据间隔值自动创建分区。间隔分区只支持基于表的一列分区，并且该列只支持TIMESTAMP[(p)] [WITHOUT TIME ZONE]、TIMESTAMP[(p)] [WITH TIME ZONE]、DATE数据类型。
• 间隔分区策略：根据分区键值将记录映射到已创建的某个分区上，如果可以映射到已创建的某一分区上，则把记录插入到对应的分区上，否则根据分区键值和表定义信息自动创建一个分区，然后将记录插入新分区中，新创建的分区数据范围等于间隔值。
• 哈希分区表：哈希分区是根据表的一列，为每个分区指定模数和余数，将要插入表的记录划分到对应的分区中，每个分区所持有的行都需要满足条件：分区键的值除以为其指定的模数将产生为其指定的余数。包含的分区个数由创建分区表时指定。
• 哈希分区策略：根据分区键值将记录映射到已创建的某个分区上，如果可以映射到已创建的某一分区上，则把记录插入到对应的分区上，否则返回报错和提示信息。
• 列表分区表：列表分区是根据表的一列，将要插入表的记录通过每一个分区中出现的键值划分到对应的分区中，这些键值在不同的分区里没有重叠。为每组键值创建一个分区，用来存储相应的数据。分区中包含的键值由创建分区表时指定。
• 列表分区策略：根据分区键值将记录映射到已创建的某个分区上，如果可以映射到已创建的某一分区上，则把记录插入到对应的分区上，否则给出报错和提示信息。
• 二级分区表：由范围分区、列表分区、哈希分区任意组合得到的分区表，其一级分区和二级分区均可以使用前面三种定义方式。

用户在CREATE TABLE时增加PARTITION参数，即表示针对此表应用数据分区功能。用户可以在实际使用中根据需要调整建表时的分区键，使每次查询结果尽可能存储在相同或者最少的分区内(称为“分区剪枝”)，通过获取连接I/O大幅度提升查询性能。

实际业务中，时间经常被作为查询对象的过滤条件。因此，用户可考虑选择时间列为分区键，键值范围可根据总数据量、一次查询数据量调整。

2.4 分区表优点

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

• 改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索效率。
• 增强可用性：如果分区表的某个分区出现故障，表在其他分区的数据仍然可用。
• 方便维护：对于大型表，数据维护变得非常困难。而分区表可以将数据分散到多个小表中，使得数据维护更加便捷。例如，对某个分区进行删除操作时，只需要删除该分区对应的小表即可。
• 扩展性好 :随着业务的发展，数据量会不断增加。分区表可以根据业务需求动态调整分区数量，以满足更高的查询性能和存储需求。
• 均衡I/O：可以把不同的分区映射到不同的磁盘以平衡I/O，改善整个系统性能。

说明：普通表若要转成分区表，需要新建分区表，然后把普通表中的数据导入到新建的分区表中。因此在初始设计表时，请根据业务提前规划是否使用分区表。

2.5 启动OpenGaussDB实例并登录

本案例中，使用OpenGaussDB开发平台，完成SQL的编程和自定义函数等多种功能。

基于之前案例《 基于开发者空间部署OpenGauss主备集中式数据库系统》。在云主机部署OpenGaussDB实例。并启动数据库服务。

进入OpenGaussDB的安装目录的bin文件，该案例云主机环境中安装目录在环境变量$GAUSSHOME中，读者根据自己云主机安装目录进行操作修改。

登录后复制

cd $GAUSSHOME/bin

• 1.

初始化数据库实例，初始化数据库目录在当前目录下data，设置节点名称和初始化用户密码。如下所示

登录后复制

./gs_initdb -D data --nodename=n1 -w GaussDB@123

• 1.

以单节点模式启动数据库实例，并在当前目录下输出日志文件logfile

登录后复制

./gs_ctl start -D data -Z single_node -l logfile

• 1.

用gsql客户端工具，进入OpenGaussDB数据库。参数 -a表示追加、-r表示使用readline

登录后复制

./gsql -d postgres -ar

• 1.

3 分区表实践

3.1 创建分区表

3.1.1 范围分区表示例

登录后复制

--创建表空间 VALUES LESS THAN
CREATE TABLESPACE tbs_test_range1_p1 RELATIVE LOCATION 'tbs_test_range1/tablespace_1';
CREATE TABLESPACE tbs_test_range1_p2 RELATIVE LOCATION 'tbs_test_range1/tablespace_2';
CREATE TABLESPACE tbs_test_range1_p3 RELATIVE LOCATION 'tbs_test_range1/tablespace_3';
CREATE TABLESPACE tbs_test_range1_p4 RELATIVE LOCATION 'tbs_test_range1/tablespace_4';
--创建分区表test_range1。
CREATE TABLE test_range1(
    id INT, 
    info VARCHAR(20)
) PARTITION BY RANGE (id) (
    PARTITION p1 VALUES LESS THAN (200) TABLESPACE tbs_test_range1_p1,
    PARTITION p2 VALUES LESS THAN (400) TABLESPACE tbs_test_range1_p2,
    PARTITION p3 VALUES LESS THAN (600) TABLESPACE tbs_test_range1_p3,
    PARTITION pmax VALUES LESS THAN (MAXVALUE) TABLESPACE tbs_test_range1_p4);
--插入1000条数据
INSERT INTO test_range1 VALUES(GENERATE_SERIES(1,1000),'abcd');
--查看p1分区的行数199条，[1,200)。
SELECT COUNT(*) FROM test_range1 PARTITION (p1);
--查看p2分区的行数200条，[200,400)。
SELECT COUNT(*) FROM test_range1 PARTITION (p2);
--查看分区信息。
SELECT a.relname, a.boundaries, b.spcname 
FROM pg_partition a, pg_tablespace b 
WHERE a.reltablespace = b.oid AND a.parentid = 'test_range1'::regclass;
--删除
DROP TABLE test_range1;
DROP TABLESPACE tbs_test_range1_p1;
DROP TABLESPACE tbs_test_range1_p2;
DROP TABLESPACE tbs_test_range1_p3;
DROP TABLESPACE tbs_test_range1_p4;
--创建分区表。
CREATE TABLE test_range2(
    id INT, 
    info VARCHAR(20)
) PARTITION BY RANGE (id) (
    PARTITION p1 START(1) END(600) EVERY(200), 
    PARTITION p2 START(600) END(800), 
    PARTITION pmax START(800) END(MAXVALUE)
);
--查看分区信息。
SELECT relname, boundaries FROM pg_partition 
WHERE parentid = 'test_range2'::regclass AND parttype = 'p' ORDER BY 1;
--删除。
DROP TABLE test_range2;

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.
• 19.
• 20.
• 21.
• 22.
• 23.
• 24.
• 25.
• 26.
• 27.
• 28.
• 29.
• 30.
• 31.
• 32.
• 33.
• 34.
• 35.
• 36.
• 37.
• 38.
• 39.
• 40.
• 41.
• 42.
• 43.
• 44.

3.1.2 列表分区示例

登录后复制

--创建列表分区表。
CREATE TABLE test_list ( NAME VARCHAR ( 50 ), area VARCHAR ( 50 ) ) 
PARTITION BY LIST (area) (
    PARTITION p1 VALUES ('Beijing'),
    PARTITION p2 VALUES ('Shanghai'),
    PARTITION p3 VALUES ('Guangzhou'),
    PARTITION p4 VALUES ('Shenzhen'),
    PARTITION pdefault VALUES (DEFAULT)
);
--插入数据。
INSERT INTO test_list VALUES ('bob', 'Shanghai'),('scott', 'Sichuan');
--查询分区数据。
SELECT * FROM test_list PARTITION (p2);
SELECT * FROM test_list PARTITION (pdefault);
--删除。
DROP TABLE test_list;

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.

3.1.3 哈希分区示例

登录后复制

--创建哈希分区表，指定分区数。
CREATE TABLE test_hash1(c1 int) PARTITION BY HASH(c1) PARTITIONS 3;
--创建哈希分区表，并指定分区名。
CREATE TABLE test_hash2(c1 int) PARTITION BY HASH(C1)(
    PARTITION pa,
    PARTITION pb,
    PARTITION pc
);
--查看分区信息。
SELECT b.relname AS table_name, a.relname AS partition_name 
FROM pg_partition a, pg_class b 
WHERE b.relname LIKE 'test_hash%' AND a.parttype = 'p' AND a.parentid = b.oid; 
--删除。
DROP TABLE test_hash1,test_hash2;

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.

3.1.4 间隔分区示例

登录后复制

CREATE TABLE interval_sales
(
    prod_id       NUMBER(6),
    cust_id       NUMBER,
    time_id       DATE,
    channel_id    CHAR(1),
    promo_id      NUMBER(6),
    quantity_sold NUMBER(3),
    amount_sold   NUMBER(10, 2)
)
PARTITION BY RANGE (time_id) INTERVAL ('1 month')
(
    PARTITION date_2015 VALUES LESS THAN ('2016-01-01'),
    PARTITION date_2016 VALUES LESS THAN ('2017-01-01'),
    PARTITION date_2017 VALUES LESS THAN ('2018-01-01'),
    PARTITION date_2018 VALUES LESS THAN ('2019-01-01'),
    PARTITION date_2019 VALUES LESS THAN ('2020-01-01')
);

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.

3.1.5 注意事项

• 唯一约束和主键约束的约束键包含所有分区键将为约束创建LOCAL索引，否则创建GLOBAL索引。
• 目前哈希分区仅支持单列构建分区键，暂不支持多列构建分区键。
• 只需要有间隔分区表的INSERT权限，往该表INSERT数据时就可以自动创建分区。
• 对于分区表PARTITION FOR (values)语法，values只能是常量。
• 对于分区表PARTITION FOR (values)语法，values在需要数据类型转换时，建议使用强制类型转换，以防隐式类型转换结果与预期不符。
• 分区数最大值为1048575个，一般情况下业务不可能创建这么多分区，这样会导致内存不足。应参照参数local_syscache_threshold的值合理创建分区，分区表使用内存大致为(分区数 * 3 / 1024)MB。理论上分区占用内存不允许大于local_syscache_threshold的值，同时还需要预留部分空间以供其他功能使用。
• 考虑性能影响，一般建议单表最大分区数不超过2000，子分区数 *(LOCAL索引个数 + 1) 不超过10000。
• 当分区数太多导致内存不足时，会间接导致性能急剧下降。
• 指定分区语句目前不能走全局索引扫描。
• 不支持XML类型数据作为分区键、二级分区键。
• 在为数据对象增加或者变更ILM策略的时候，如果追加了行级表达式，需要注意行表达式目前只支持白名单中列出的函数。具体白名单函数列表参考行表达式函数白名单。

3.2 修改分区表

3.2.1 修改表分区名称

登录后复制

--创建前置分区表。
CREATE TABLE test_p1 (col1 INT, col2 INT) PARTITION BY RANGE (col1) 
( 
    PARTITION p1 VALUES LESS THAN (10), 
    PARTITION p2 VALUES LESS THAN (20), 
    PARTITION p3 VALUES LESS THAN (MAXVALUE) 
);
--修改分区名称。
ALTER TABLE test_p1 RENAME PARTITION p3 TO pmax;
--查询分区信息。
SELECT relname, boundaries, oid FROM pg_partition WHERE parentid='test_p1'::regclass AND parttype <> 'r';

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.

3.2.2 移动分区表空间

登录后复制

--创建分区。
CREATE TABLESPACE tbs_data1 RELATIVE LOCATION 'tablespace1/tbs_data1';
--移动分区表空间。
ALTER TABLE test_p1 MOVE PARTITION P1 TABLESPACE tbs_data1;
--查看分区表空间。
SELECT relname, spcname FROM pg_partition t1, pg_tablespace t2 WHERE T1.reltablespace=t2.oid and t1.parentid='test_p1'::regclass;

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.

3.2.3 分区交换

登录后复制

--创建普通表，插入数据。
CREATE TABLE test_ep1(col1 INT,col2 INT);
INSERT INTO test_ep1 VALUES (GENERATE_SERIES(1,30), 1000);
--迁移普通表数据到指定分区。
ALTER TABLE test_p1 EXCHANGE PARTITION (p1) WITH TABLE test_ep1 VERBOSE;
--查询。
SELECT COUNT(*) FROM test_p1 PARTITION (p1);
--删除表test_ep1
DROP TABLE test_ep1;

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.

3.2.4 分区合并

登录后复制

--将test_p1表中p2,pmax分区合并到pmax中。
ALTER TABLE test_p1 MERGE PARTITIONS p2,pmax INTO PARTITION pmax;
--查看分区。
SELECT relname, boundaries, oid FROM pg_partition WHERE parentid='test_p1'::regclass AND parttype <> 'r' order by 1;
--删除表和表空间。
DROP TABLE test_p1;
DROP TABLESPACE tbs_data1;

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.

3.2.5 分区切割

登录后复制

--建表
CREATE TABLE test_r1 (col1 INT,col2 INT) PARTITION BY RANGE (col1)(
    PARTITION p1 VALUES LESS THAN (10),
    PARTITION pmax VALUES LESS THAN (MAXVALUE)
); 
--切割分区
ALTER TABLE test_r1 SPLIT PARTITION pmax AT (20) INTO (PARTITION p2, PARTITION pmax1);
ALTER TABLE test_r1 SPLIT PARTITION pmax1 INTO (
    PARTITION p3 VALUES LESS THAN (30),
    PARTITION pmax VALUES LESS THAN (MAXVALUE)
);
--查询
SELECT relname, boundaries, oid FROM pg_partition WHERE parentid='test_r1'::regclass AND parttype <> 'r' order by 1;
--删除表test_r1。
DROP TABLE test_r1;
--建表
CREATE TABLE test_r2(col1 INT, col2 INT) PARTITION BY RANGE (col1)(
    PARTITION p1   START(1) END(10),
    PARTITION p2   START(10) END(20),
    PARTITION pmax START(20) END(MAXVALUE)
);
--切割分区
ALTER TABLE test_r2 SPLIT PARTITION pmax INTO (
    PARTITION p3 START(20) END(30),
    PARTITION pmax1 START(30) END (MAXVALUE)
);
--查看
SELECT relname, boundaries, oid FROM pg_partition WHERE parentid='test_r2'::regclass AND parttype <> 'r' order by 1;
--删除表test_r2
DROP TABLE test_r2;

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.
• 19.
• 20.
• 21.
• 22.
• 23.
• 24.
• 25.
• 26.
• 27.
• 28.
• 29.
• 30.

3.2.6 添加分区

登录后复制

--建表
CREATE TABLE test_p2 (col1 INT, col2 INT) PARTITION BY RANGE (col1)(
    PARTITION p1 VALUES LESS THAN (10),
    PARTITION p2 VALUES LESS THAN (20)
);
--添加分区
ALTER TABLE test_p2 ADD PARTITION p3 VALUES LESS THAN (30);
--删除表test_p2
DROP TABLE test_p2;
--建表
CREATE TABLE test_p3 (col1 INT, col2 INT) PARTITION BY LIST(col1)(
    PARTITION p1 VALUES (1),
    PARTITION p2 VALUES (2)
);
--添加分区
ALTER TABLE test_p3 ADD PARTITION p3 VALUES (3);
--删除表test_p3
DROP TABLE test_p3;

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.

3.2.7 删除分区

登录后复制

--建表
CREATE TABLE test_p4 (col1 INT, col2 INT) PARTITION BY LIST(col1)(PARTITION p1 VALUES (1),PARTITION p2 VALUES (2));
--删除test_p3表的p2分区
ALTER TABLE test_p4 DROP PARTITION p2;
--查看
SELECT relname, boundaries, oid FROM pg_partition WHERE parentid='test_p4'::regclass;
--删除表test_p4
DROP TABLE test_p4;
--指定partition value删除分区
--建表
CREATE TABLE test_p4 (col1 INT, col2 INT) PARTITION BY RANGE(col1)(PARTITION p1 VALUES LESS THAN(1),PARTITION p2 VALUES LESS THAN (2));
--删除test_p3表的分区键为1时所处的分区
ALTER TABLE test_p4 DROP PARTITION FOR (1);
--查看
SELECT relname, boundaries FROM pg_partition WHERE parentid='test_p4'::regclass order by 1 desc;
--删除表test_p4
DROP TABLE test_p4;

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.

3.2.8 清空分区

登录后复制

--建表
CREATE TABLE test_p5 (col1 INT, col2 INT) PARTITION BY RANGE (col1)(
    PARTITION p1 VALUES LESS THAN (5),
    PARTITION p2 VALUES LESS THAN (10)
);
--插入数据
INSERT INTO test_p5 VALUES (GENERATE_SERIES(1,9), 100);
--查看p2分区数据
SELECT * FROM test_p5 PARTITION (p2);
--清空p2分区的数据
ALTER TABLE test_p5 TRUNCATE PARTITION p2;
--查看p2分区数据
SELECT * FROM test_p5 PARTITION (p2);
--删除表test_p5
DROP TABLE test_p5;

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.

3.2.9 开启和关闭列表分区的自动扩展功能

登录后复制

--创建一个列表分区
CREATE TABLE list_int (c1 int, c2 int)
PARTITION BY LIST (c1)
(
    PARTITION p1 VALUES (1, 2, 3),
    PARTITION p2 VALUES (4, 5, 6)
);
--开启列表分区的自动扩展功能
ALTER TABLE list_int SET PARTITIONING AUTOMATIC;
--关闭列表分区的自动扩展功能
ALTER TABLE list_int SET PARTITIONING MANUAL;
--删除列表分区
DROP TABLE list_int;
--开启和关闭范围分区的自动扩展功能
--创建一个范围分区
CREATE TABLE range_int (c1 int, c2 int)
PARTITION BY RANGE (c1)
(
    PARTITION p1 VALUES LESS THAN (5),
    PARTITION p2 VALUES LESS THAN (10),
    PARTITION p3 VALUES LESS THAN (15)
);
--开启范围分区的自动扩展功能
ALTER TABLE range_int SET INTERVAL (5);
--关闭范围分区的自动扩展功能
ALTER TABLE range_int SET INTERVAL ();
--删除范围分区
DROP TABLE range_int;

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.
• 19.
• 20.
• 21.
• 22.
• 23.
• 24.
• 25.
• 26.
• 27.
• 28.

OpenGaussDB不支持该语法，GaussDB支持该语法。

3.2.10 注意事项

• 只有分区表的所有者或者被授予了分区表ALTER权限的用户有权限执行ALTER TABLE PARTITION命令，当三权分立开关关闭时，系统管理员默认拥有此权限。
• 添加分区的表空间不能是PG_GLOBAL。
• 添加分区的名称不能与该分区表已有分区的名称相同。
• 添加分区的分区键值要和分区表的分区键类型一致。
• 若添加RANGE分区，添加分区键值要大于分区表中最后一个范围分区的上边界。
• 若添加LIST分区，添加分区键值不能与现有分区键值重复。
• 不支持添加哈希分区。
• 如果目标分区表中已有分区数达到了最大值1048575，则不能继续添加分区。
• 当分区表只有一个分区时，不能删除该分区。
• 选择分区使用PARTITION FOR()，括号里指定值个数应该与定义分区时使用的列个数相同，并且一一对应。ALTER TABLE操作会处理partition_value落入的分区。
• Value分区表不支持Alter Partition操作。
• 间隔分区表不支持添加分区。
• 哈希分区表不支持切割分区，不支持合并分区，不支持添加/删除分区。
• 删除、切割、合并、清空、交换分区的操作会使Global索引失效，可以申明UPDATE GLOBAL INDEX子句同步更新索引。
• 如果删除、切割、合并、清空、交换分区操作不申明UPDATE GLOBAL INDEX子句，并发的DML业务有可能因为索引不可用而报错。
• 若设置参数enable_gpi_auto_update为on，即使不申明UPDATE GLOBAL INDEX子句，也会自动更新Global索引。
• 在为数据对象增加或者变更ILM策略的时候，如果追加了行级表达式，需要注意行表达式目前只支持白名单中列出的函数。具体白名单函数列表参考行表达式函数白名单。
• 开启范围分区自动扩展要求分区表中不能存在分区键值为MAXVALUE的分区。
• 开启范围分区自动扩展只支持单分区键。
• 开启列表分区自动扩展要求分区表中不能存在分区键值为DEFAULT的分区。

3.3 删除分区表

分区表删除方法与普通表一样，语法如下。

登录后复制

DROP TABLE [ IF EXISTS ] 
    { [schema.]table_name } [, ...] [ CASCADE | RESTRICT ] [ PURGE ];

• 1.
• 2.

注意事项:

• DROP TABLE删除表后，依赖该表的索引会被删除，而使用到该表的函数和存储过程将无法执行。删除分区表，会同时删除分区表中的所有分区。
• 表的所有者、表所在模式的所有者、被授予了表的DROP权限的用户或被授予DROP ANY TABLE权限的用户，有权删除指定表，三权分立关闭时，系统管理员默认拥有该权限。
• DROP TABLE时，如果被指删除的表作为外键表引用了另一张表，会级联删除被引用表上的触发器，此时需要对被引用表加八级锁，可能造成业务的阻塞。

3.4 分区表性能优化

3.4.1 查询优化

• 使用分区键：查询语句中应包含分区键，以便MySQL能够快速定位到相关分区，减少扫描的分区数量。
• 避免跨分区查询：尽量避免跨多个分区的查询，因为跨分区查询会增加扫描的分区数量，影响查询性能。

登录后复制

SELECT * FROM interval_sales WHERE time_id BETWEEN '2021-01-01' AND '2021-12-31';

• 1.

3.4.2 数据导入优化

• 批量插入：使用批量插入语句可以提高数据导入效率，减少分区切换的开销。
• 关闭索引：在大量数据导入前，可以临时关闭索引，以减少索引维护的开销，导入完成后再重新启用索引。

登录后复制

ALTER TABLE interval_sales DISABLE KEYS;
-- 批量插入数据
ALTER TABLE interval_sales ENABLE KEYS;

• 1.
• 2.
• 3.

OpenGaussDB官方链接(此功能在OpenGaussDB环境中可能存在报错，可跳过该部分)：

< https://docs.opengauss.org/zh/docs/latest/docs/ExtensionReference/dolphin-ALTER-TABLE.html>

3.5 分区表相关系统视图和函数

3.5.1 所有分区视图

• ADM_PART_TABLES：所有分区表信息。
• ADM_TAB_PARTITIONS：所有一级分区信息。
• ADM_TAB_SUBPARTITIONS：所有二级分区信息。
• ADM_PART_INDEXES：所有Local索引信息。
• ADM_IND_PARTITIONS：所有一级分区表索引分区信息。
• ADM_IND_SUBPARTITIONS：所有二级分区表索引分区信息。

注意事项：该视图GaussDB支持，OpenGauss不支持

3.5.2 当前用户可访问视图

• DB_PART_TABLES：当前用户可访问的分区表信息。
• DB_TAB_PARTITIONS：当前用户可访问的一级分区信息。
• DB_TAB_SUBPARTITIONS：当前用户可访问的二级分区信息。
• DB_PART_INDEXES：当前用户可访问的Local索引信息。
• DB_IND_PARTITIONS：当前用户可访问的一级分区表索引分区信息。
• DB_IND_SUBPARTITIONS：当前用户可访问的二级分区表索引分区信息。

注意事项：该视图GaussDB支持，OpenGauss不支持

3.5.3 当前用户拥有的视图

• MY_PART_TABLES：当前用户拥有的分区表信息。
• MY_TAB_PARTITIONS：当前用户拥有的一级分区信息。
• MY_TAB_SUBPARTITIONS：当前用户拥有的二级分区信息。
• MY_PART_INDEXES：当前用户拥有的Local索引信息。
• MY_IND_PARTITIONS：当前用户拥有的一级分区表索引分区信息。
• MY_IND_SUBPARTITIONS：当前用户拥有的二级分区表索引分区信息。

注意事项：该视图GaussDB支持，OpenGauss不支持

3.5.4 内置工具函数

• pg_get_tabledef获取分区表的定义，入参可以为表的OID或者表名。
• pg_partition_size(oid,oid)指定OID代表的分区使用的磁盘空间。其中，第一个oid为表的OID，第二个oid为分区的OID。
• pg_partition_size(text, text)指定名称的分区使用的磁盘空间。其中，第一个text为表名，第二个text为分区名。
• pg_partition_indexes_size(oid,oid)指定OID代表的分区索引使用的磁盘空间。其中，第一个oid为表的OID，第二个oid为分区的OID。
• pg_partition_indexes_size(text,text)指定名称的分区索引使用的磁盘空间。其中，第一个text为表名，第二个text为分区名。

3.6 分区表查询性能异常

3.6.1 常见原因

使用分区表进行相关查询业务，SQL性能慢时，常见的原因有以下几种：

• 分区索引失效，顺序扫描导致的SQL性能慢
• 分区表无法进行分区剪枝导致的SQL性能慢
• SQL计划选择非最优导致的SQL性能慢

3.6.2 处理方法

3.6.2.1 判断是否存在索引异常行为

部分分区DDL如果不带UPDATE GLOBAL INDEX子句，会导致分区表Global索引失效。同时用户也可以使用ALTER TABLE或者ALTER INDEX使分区表索引失效。当分区表或者部分分区上索引不可用后，会导致查询SQL无法走索引扫描，从而出现慢SQL场景。

3.6.2.2 查询索引类型和状态

• 分区表索引分为LOCAL索引与GLOBAL索引：LOCAL索引与某个具体分区绑定，而GLOBAL索引则对应整个分区表。
• 唯一约束和主键约束的约束键包含所有分区键则创建LOCAL索引，否则创建GLOBAL索引。当查询语句在查询数据涉及多个目标分区时，建议使用GLOBAL索引，反之建议使用LOCAL索引。但需要注意GLOBAL索引在分区维护语法中存在额外的开销。
• 在创建LOCAL索引时，可以通过FOR { partition_name | ( partition_value [, ...] ) }子句，指定在单个分区上创建LOCAL索引，此类索引在其他分区上不生效，后续新增的分区也不会自动创建该索引。需要注意的是，当前仅静态剪枝到单个分区的计划支持生成分类索引的查询路径。

Local索引会在每个分区创建一个索引，Global索引会在整个分区表上创建一个全局索引。对于Local索引，需要确认整个索引自身状态和目标查询分区的索引状态；对于Global索引，只需要确认整个索引自身状态。

可以通过系统表pg_index查询分区表的所有索引，并获取索引是否可用的状态，其索引类型在pg_class中给出。下面给出了查询分区表t1的全部索引状态及索引类型的命令参考：

登录后复制

CREATE TABLESPACE example1 RELATIVE LOCATION 'tablespace1/tablespace_1';
CREATE TABLESPACE example2 RELATIVE LOCATION 'tablespace2/tablespace_2';
CREATE TABLESPACE example3 RELATIVE LOCATION 'tablespace3/tablespace_3';
CREATE TABLESPACE example4 RELATIVE LOCATION 'tablespace4/tablespace_4';

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;

CREATE INDEX tpcds_web_returns_p2_index1 ON web_returns_p2 (ca_address_id) LOCAL;

CREATE INDEX tpcds_web_returns_p2_index2 ON web_returns_p2 (ca_address_sk) LOCAL
(
    PARTITION web_returns_p2_P1_index,
    PARTITION web_returns_p2_P2_index TABLESPACE example3,
    PARTITION web_returns_p2_P3_index TABLESPACE example4,
    PARTITION web_returns_p2_P4_index,
    PARTITION web_returns_p2_P5_index,
    PARTITION web_returns_p2_P6_index,
    PARTITION web_returns_p2_P7_index,
    PARTITION web_returns_p2_P8_index
) TABLESPACE example2;

CREATE INDEX tpcds_web_returns_p2_global_index ON web_returns_p2 (ca_street_number) GLOBAL;

select c.relname, i.indisusable, c.relkind 
from pg_class c 
join pg_index i on c.oid = i.indexrelid 
join pg_class r on i.indrelid = r.oid 
where r.relname = 'web_returns_p2';

• 1.
• 2.
• 3.
• 4.
• 5.
• 6.
• 7.
• 8.
• 9.
• 10.
• 11.
• 12.
• 13.
• 14.
• 15.
• 16.
• 17.
• 18.
• 19.
• 20.
• 21.
• 22.
• 23.
• 24.
• 25.
• 26.
• 27.
• 28.
• 29.
• 30.
• 31.
• 32.
• 33.
• 34.
• 35.
• 36.
• 37.
• 38.
• 39.
• 40.
• 41.
• 42.
• 43.
• 44.
• 45.
• 46.
• 47.
• 48.
• 49.
• 50.
• 51.
• 52.
• 53.
• 54.

返回结果：其中indisusable字段为't'表示该索引可用，为'f'表示索引已经失效，在查询业务中该索引无法使用;relkind为'i'表示该索引为Local索引，为'I'表示该索引为Global索引。

或者通过元语句\d命令查询分区表的索引信息：

登录后复制

\d+ web_returns_p2

• 1.

可以看到表t1的两条索引t1_c1_idx、t1_c2_tableoid_idx被列出。其中t1_c1_idx带有LOCAL关键字，表示该索引为Local索引，t1_c2_tableoid_idx没有LOCAL关键字，表示该索引为Global索引。同时t1_c2_tableoid_idx标记了UNUSABLE，表示该索引不可用。

3.6.2.3 查询索引分区状态

对于Local索引，还需要确认目标查询分区的索引状态，这个信息可以从系统表pg_partition中查询。

登录后复制

select p.relname,p.indisusable from pg_partition p join pg_class c on p.parentid=c.oid where c.relname='t1_c1_idx';

• 1.

可以看到索引分区p1_c1_idx可用，索引分区p2_c1_idx不可用。在分区p2上的查询无法使用索引t1_c1_idx。

3.6.2.4 重建异常的索引/索引分区

如果分区表的索引/索引分区状态异常，会导致查询SQL无法走索引扫描，需要重建索引/索引分区。重建索引的命令如下：

登录后复制

ALTER INDEX t1_c2_tableoid_idx REBUILD;

• 1.

重建索引分区的命令如下：

登录后复制

ALTER INDEX t1_c1_idx REBUILD PARTITION p2_c1_idx;

• 1.

3.6.2.4.1 分区表索引重建/不可用

用户可以通过命令使得一个分区表索引或者一个索引分区不可用，此时该索引/索引分区不再维护。使用重建索引命令可以重建分区表索引，恢复索引的正常功能。

此外，部分分区级DDL操作也会使得Global索引失效，包括删除drop、交换exchange、清空truncate、分割split、合并merge。如果在DDL操作中带UPDATE GLOBAL INDEX子句，则会同步更新Global索引，否则需要用户自行重建索引。

3.6.2.4.2 local索引分区重建/不可用

• 使用ALTER INDEX PARTITION可以设置Local索引分区是否可用。
• 使用ALTER TABLE MODIFY PARTITION可以设置分区表上指定分区的所有索引分区是否可用。

例如，假设分区表range_sales上存在两张Local索引range_sales_idx1和range_sales_idx2，假设其在分区date_202001上对应的索引分区名分别为range_sales_idx1_part1和range_sales_idx2_part1。

下面给出了维护分区表分区索引的语法：

• 可以通过如下命令设置分区date_202001上的所有索引分区均不可用。
• ALTER TABLE range_sales MODIFY PARTITION date_202001 UNUSABLE LOCAL INDEXES;
• 或者通过如下命令单独设置分区date_202001上的索引分区range_sales_idx1_part1不可用。
• ALTER INDEX range_sales_idx1 MODIFY PARTITION range_sales_idx1_part1 UNUSABLE;
• 可以通过如下命令重建分区date_202001上的所有索引分区。
• ALTER TABLE range_sales MODIFY PARTITION date_202001 REBUILD UNUSABLE LOCAL INDEXES;
• 或者通过如下命令单独重建分区date_202001上的索引分区range_sales_idx1_part1。
• ALTER INDEX range_sales_idx1 REBUILD PARTITION range_sales_idx1_part1;

3.6.2.5 判断分区表是否存在剪枝异常的场景

当分区表的分区键所在列存在条件时，可以触发分区剪枝。数据库会在优化器/执行器阶段识别到需要扫描的分区，而不会扫描全部分区。只有分区表的业务是顺序扫描，或者使用Local索引进行扫描时候，才可能触发分区剪枝；Global索引不会触发分区剪枝，这是因为Global索引是在整个分区表上创建的单个索引，不存在剪枝的概念。

剪枝是被动触发的，当查询业务满足分区剪枝条件时，会自动触发分区剪枝。

3.6.2.5.1 判断是否触发了分区剪枝

可以通过查询计划来判断是否触发了分区剪枝。当扫描分区数少于分区总数时，即触发了分区剪枝。分区剪枝分为静态剪枝和动态剪枝，静态剪枝是指在优化器阶段就能识别到裁剪的分区；动态剪枝是指优化器阶段只能确定可以进行剪枝，但不知道具体裁剪到哪一个分区，在执行器阶段才会确定裁剪的目标分区。

下面的业务触发了分区静态剪枝，计划中的'Iterations: 1'表示扫描了1个分区，'Selected Partitions: 1'表示扫描的目标分区下标是1。

这个下标是一个逻辑数组下标，对于范围分区/间隔分区，按照分区定义上界值升序排列；对于列表分区，按照第一个枚举值升序排列；对于哈希分区，按照计算后的哈希值升序排列。可以通过函数pg_get_tabledef获取分区定义，其列出的分区即为该对应下标。

登录后复制

explain select * from t1 where c1 < 100;

• 1.

下面的业务没有触发分区剪枝，计划中的'Iterations: 2'表示扫描了2个分区，'Selected Partitions: 1..2'表示扫描的目标分区下标是1和2。可以看到扫描的分区数等于分区表的总分区数，表示未进行任何分区剪枝。

登录后复制

explain select * from t1;

• 1.

下面的业务触发了分区动态剪枝，计划中的'Iterations: PART'和'Selected Partitions: PART'表示优化器识别到分区表可以进行分区剪枝。

登录后复制

prepare p1 as select * from t1 where c1 < $1;
explain execute p1(100);

• 1.
• 2.

3.6.2.5.2 支持分区剪枝的场景

当分区键所在列存在条件时，可以进行分区剪枝。条件剪枝支持场景为：比较表达式(<，<=，=，>=，>)、逻辑表达式(AND、OR)、数组表达式。需要注意的是，列表和哈希分区不支持除等号外的其他比较表达式的剪枝。如果条件全为常量，则进行静态剪枝；如果条件带有参数、部分隐式转换、immutable函数，则进行动态剪枝。

当分区表作为参数化路径计划的内表且分区键所在列为索引检索条件时，支持动态剪枝。

3.6.2.5.3 不支持分区剪枝的场景

分区键所在列的条件为子查询表达式、无法直接强转的类型转换、stable/volatile函数时，不支持分区剪枝。

分区表在生成除参数化路径外的其他JOIN计划时，不支持动态剪枝。

3.6.2.5.4 业务改写适配分区剪枝

当业务设计不合理，导致原逻辑上可以走分区剪枝的计划，最终未走分区剪枝时，可以改写业务以适配分区剪枝，从而提升分区表查询业务性能。比如使用HINT固定计划、在客户端修改绑参类型等方法。

3.6.2.5.5 判断分区表是否是因为SQL计划选择非最优

通过执行计划分析判断即可，这里不再阐述。