openGauss
开源数据库
openGauss社区官网
开源社区
MogDB 分区表全操作速查/使用脚本实现RANGE分区定时自动删除
2022-04-14MogDB 分区表全操作速查/使用脚本实现RANGE分区定时自动删除
MogDB 分区表全操作速查/使用脚本实现 RANGE 分区定时自动删除
本文出处：https://www.modb.pro/db/237966
本文所有操作基于 Mogdb 版本 2.0.1
分区类型
- RANGE 分区 按照范围值进行分区，生产环境最常用到的分区类型，特别适用于按照日期进行业务查询的系统。
- INTERVAL 分区 可以理解为 RANGE 分区的加强版，在新数据插入时，按照 partition key 的取值，实时自动建立新分区，非常实用。
- LIST 分区 按照枚举值进行分区，目前 mogdb 版本（2.0.1）不支持 default 分区，如果插入数据时，出现 partition key 中没有枚举到的值的话，SQL 会执行失败。
- HASH 分区 一般作为子分区存在，很少单独使用。
- SUB-PARTITION 子分区 在分区的基础上，为分区添加下一级子分区，一般用于优化 SQL 语句的执行速度，如 RANGE-HASH 子分区。本文不对子分区做介绍。
分区相关操作操作
创建分区表
*RANGE 分区*
- partition key 支持多列分区
- partition key 为 maxvalue 的分区可以存放不符合其他分区的数值
- 注意 partition key 分区范围为[ )，分区不存储 partition key 的边界值，如 partition p3 values less than('2021-10-01')，P3 分区不存储'2021-10-01'的数据，该数据存放在下个分区中
--SQL语句
CREATE TABLE part_range (
id int not null,
remark varchar(8),
db_insert_time date
) PARTITION BY RANGE (db_insert_time)
(
partition p1 values less than('2021-04-01'),
partition p2 values less than('2021-07-01'),
partition p3 values less than('2021-10-01'),
partition p4 values less than(maxvalue)
);*INTERVAL 分区*
- 分隔间隔可以为，日，周，月，年，最小分隔单位为日。
--SQL语句
create table part_interval(
id serial,
remark varchar(16),
db_insert_time date
) partition by range(db_insert_time) interval('1 day')
(
partition p1 values less than('2020-12-08'),
partition p2 values less than('2020-12-09')
);*LIST 分区*
- 暂不支持 default 分区 如果插入的新数据不在所有分区取值范围内，SQL 将会报错退出
- 分区个数不能超过 64 个
- 每个分区键键值不能超过 1048575
--SQL语句
CREATE TABLE part_list (
id serial not null,
remark varchar(16),
province_name varchar(16)
) PARTITION BY LIST(province_name)
(
PARTITION p1 values('harbin','shenyang','changchun'),
PARTITION p2 values('beijing','shenzhen','tianjing')
);*HASH 分区*
- 分区个数建议为 power(2,N)个，分区个数在 create table 创建完毕之后，不再允许修改
- 分区个数不能超过 1048575
--SQL语句
create table part_hash(
id int not null,
name varchar(16)
) partition by hash(id)
(
partition p1,
partition p2,
partition p3,
partition p4
);查询分区表分区信息
--SQL语句
select rel_table.relname table_name, rel_partition.*
from (select relname, parentid from pg_partition where parttype = 'r') rel_table,
(select relname,
parentid,
relfilenode,
parttype,
partstrategy,
boundaries,
interval
from pg_partition
where parttype = 'p') rel_partition
where rel_table.parentid = rel_partition.parentid
and rel_table.relname in
('part_range', 'part_list', 'part_hash', 'part_interval')
order by 1, 2;重命名分区
*RANGE 分区*
--SQL语句
alter table part_range rename partition p1 to p_1;*INTERVAL 分区*
--SQL语句
alter table part_interval rename partition p1 to p_1;*LIST 分区*
--SQL语句
alter table part_list rename partition p1 to p_1;*HASH 分区*
--SQL语句
alter table part_hash rename partition p1 to p_1;添加分区
*RANGE 分区*
- 如果创建了 maxvalue 分区，需要先删除 maxvalues 的分区/或直接使用 split 命令增加分区
- 支持一次增加多个分区
--SQL语句
alter table part_range drop partition p4;
alter table part_range add partition p5 values less than('2022-02-01'),add partition p6 values less than('2022-02-02');*INTERVAL 分区*
- 无需手动添加分区，数据插入时自动增加分区
--SQL语句
insert into part_interval values(1,null,now());--插入数据前
--插入数据后
*LIST 分区*
- 支持一次增加多个分区
--SQL语句
alter table part_list add partition p3 values('taiwan'),add partition p4 values('aomen'),add partition p5 values('xiangguang');*HASH 分区*
- 暂不支持添加分区
删除分区
*RANGE 分区*
- 支持一次删除多个分区
--SQL语句
alter table part_range drop partition p5,drop partition p6;*INTERVAL 分区*
- 参考分区自动删除脚本
*LIST 分区*
- 支持一次删除多个分区
--SQL语句
alter table part_list drop partition p3,drop partition p4,drop partition p5;*HASH 分区*
- 暂不支持删除分区
切割分区
*RANGE 分区*
split_point（分割点）需要位于前分区和分隔分区之间 ，且不能跨越多个分区
分区名不能重用
--SQL语句
alter table part_range split partition p3 at ('2021-08-01') into (partition p_3,partition p_4);*INTERVAL 分区*
- 同 RANGE 分区
*LIST 分区*
- 暂不支持切割分区
*HASH 分区*
- 暂不支持切割分区
清空分区
*RANGE 分区*
--SQL语句
alter table part_range truncate partition p_1;*INTERVAL 分区*
- 同 RANGE 分区
*LIST 分区*
- 暂不支持清空分区
*HASH 分区*
- 暂不支持清空
交换分区
(普通表转换为表分区，分区表位普通表)
- 需要列结构一致，索引/约束一致
- 普通表数据要在将替换的分区 partition key 范围之内 适用在变更时间较短，并且数据量较多的场合（考虑全局索引重建的因素）
*RANGE 分区*
--创建普通表
CREATE TABLE t_exchange_range (
id int not null,
remark varchar(8),
db_insert_time date
);
--普通表插入数据
insert into t_exchange_range values(3,null,date '2021-08-01'),(3,null,date '2021-08-15'),(3,null,date '2021-09-01'),(3,null,date '2021-09-15');
--交换分区
alter table part_range EXCHANGE partition (p_4) with table t_exchange_range;
--查询分区
select * from part_range partition(p_4);
--输出结果*INTERVAL 分区*
- 同 RANGE 分区
*LIST 分区*
- 暂不支持交换分区
*HASH 分区*
- 暂不支持交换分区
自动删除分区脚本：
名称类型描述
oidoid行标识符（隐藏属性，必须明确选择）。
relnamename分区表、分区、分区上toast表和分区索引的名称。
parttype"char"对象类型:
- 'r': partitioned table
- 'p': table partition
- 'x': index partition
- 't': toast table
parentidoid当对象为分区表或分区时，此字段表示分区表在PG_CLASS中的OID。当对象为index partition时，此字段表示所属分区表索引（partitioned index）的OID。
rangenuminteger保留字段。
intervalnuminteger保留字段。
partstrategy"char"分区表分区策略，现在仅支持:
- 'r': 范围分区。
- 'v': 数值分区。
- 'i': 间隔分区。
- 'l'：list分区。
- 'h'：hash分区。
- 'n'：无效分区。
relfilenodeoidtable partition、index partition、分区上toast表的物理存储位置。
reltablespaceoidtable partition、index partition、分区上toast表所属表空间的OID。
relpagesdouble precision统计信息: table partition、index partition的数据页数量。
reltuplesdouble precision统计信息: table partition、index partition的元组数。
relallvisibleinteger统计信息: table partition、index partition的可见数据页数。
reltoastrelidoidtable partition所对应toast表的OID。
reltoastidxidoidtable partition所对应toast表的索引的OID。
indextblidoidindex partition对应table partition的OID。
indisusableBoolean分区索引是否可用。
reldeltarelidoidDelta表的OID。
reldeltaidxoidDelta表的索引表的OID。
relcudescrelidoidCU描述表的OID。
relcudescidxoidCU描述表的索引表的OID。
relfrozenxidxid32冻结事务ID号。
为保持前向兼容，保留此字段，新增relfrozenxid64用于记录此信息。
intspnuminteger间隔分区所属表空间的个数。
partkeyint2vector分区键的列号。
intervaltablespaceoidvector间隔分区所属的表空间，间隔分区以round-robin方式落在这些表空间内。
intervaltext[]间隔分区的间隔值。
boundariestext[]范围分区和间隔分区的上边界。
transittext[]间隔分区的跳转点。
reloptionstext[]设置partition的存储属性，与pg_class.reloptions的形态一样，用"keyword=value"格式的字符串来表示 ，目前用于在线扩容的信息搜集。
relfrozenxid64xid冻结事务ID号。