循序渐进丨MogDB 数据库分区表的静态数据分区删除测试

MogDB 2024-06-25

测试说明

本测试用于当分区表包含全局索引或是分区索引时，是否会影响删除静态数据分区操作（DDL）。

测试流程

建立测试表
测试4组分区表分区裁剪功能
测试4组分区表静态数据分区删除功能

测试结论

序号	测试大项	测试结论
1	无索引表分区裁剪功能	通过，可以正常使用分区裁剪
2	全局索引表分区裁剪功能	通过，可以正常使用分区裁剪
3	本地普通索引表分区裁剪功能	通过，可以正常使用分区裁剪
4	本地主键索引表分区裁剪功能	通过，可以正常使用分区裁剪
5	删除无索引静态分区是否上锁	通过，无论分区表存在DDL还是DML，都可以删除静态分区
6	删除全局索引静态分区是否上锁	通过，无论分区表存在DDL还是DML，都可以删除静态分区
7	删除本地索引静态分区是否上锁	通过，无论分区表存在DDL还是DML，都可以删除静态分区
8	删除本地主键静态分区是否上锁	通过，无论分区表存在DDL还是DML，都可以删除静态分区

版本信息

数据库版本：MogDB v3.0.3

数据库兼容模式：Oracle

测试步骤

1、建立无索引分区表全局索引分区表本地索引分区本地主键分区表

创建全局索引分区表：

CREATE TABLE t_part_noindex (
    id int not null,
    remark varchar,
    ctime date
) PARTITION BY RANGE (ctime)
(
    partition p1 values less than('2023-04-01'),
    partition p2 values less than('2023-07-01'),
    partition p3 values less than('2023-10-01'),
    partition p4 values less than(maxvalue)
)
ENABLE ROW MOVEMENT;

插入数据：

insert into t_part_noindex select generate_series(1,364),'global test',generate_series('2023-01-01'::date,'2023-12-30'::date,'1 day');

查看表结构：

\d+ t_part_noindex

创建全局索引分区表：

CREATE TABLE t_part_noindex (
    id int not null,
    remark varchar,
    ctime date
) PARTITION BY RANGE (ctime)
(
    partition p1 values less than('2023-04-01'),
    partition p2 values less than('2023-07-01'),
    partition p3 values less than('2023-10-01'),
    partition p4 values less than(maxvalue)
)
ENABLE ROW MOVEMENT;

插入数据：

insert into t_part_global select generate_series(1,364),'global test',generate_series('2023-01-01'::date,'2023-12-30'::date,'1 day');

创建全局索引：

create index ind_global on t_part_global (ctime) GLOBAL;

查看表结构：

\d+ t_part_global

创建本地索引分区表：

CREATE TABLE t_part_local (
    id int not null,
    remark varchar,
    ctime date
) PARTITION BY RANGE (ctime)
(
    partition p1 values less than('2023-04-01'),
    partition p2 values less than('2023-07-01'),
    partition p3 values less than('2023-10-01'),
    partition p4 values less than(maxvalue)
)
ENABLE ROW MOVEMENT;

插入数据：

insert into t_part_local select generate_series(1,364),'global test',generate_series('2023-01-01'::date,'2023-12-30'::date,'1 day');

创建本地索引（注意如果建立带有unique属性的本地索引，字段需要引用partition key）：

create index ind_local on t_part_local (ctime) LOCAL;

查看表结构：

\d+ t_part_local

创建本地索引分区表：

CREATE TABLE t_part_primary (
    id int not null,
    remark varchar,
    ctime date
) PARTITION BY RANGE (ctime)
(
    partition p1 values less than('2023-04-01'),
    partition p2 values less than('2023-07-01'),
    partition p3 values less than('2023-10-01'),
    partition p4 values less than(maxvalue)
)
ENABLE ROW MOVEMENT;

插入数据：

insert into t_part_primary select generate_series(1,364),'global test',generate_series('2023-01-01'::date,'2023-12-30'::date,'1 day');

创建主键（注意当主键索引列带partition key，会建立本地索引；如果不带partition key，则建立全局索引，本次测试建立带有local属性的主键）：

alter table t_part_primary add constraint pk_primary primary key(ctime);

查看表结构：

\d+ t_part_primary

查看分区索引：

select c.schemaname||'.'||b.parentid::regclass    table_name,
       a.parentid::regclass                       index_name,
       b.relname                                  table_partition_name,
       a.relname                                  index_partition_name,     
       a.indisusable                              index_partition_status
  from pg_partition a,pg_partition b,pg_stat_user_tables c
  where a.indextblid = b.oid(+)
  and b.parentid=c.relid
  and a.parttype='x'
  and a.parentid in (select indexrelid index_name from pg_index where indrelid in ('t_part_local'::regclass,'t_part_global'::regclass,'t_part_primary'::regclass))
  order by 1,2,3,4;

2、分区裁剪测试

收集统计信息：

analyze t_part_noindex;
analyze t_part_global;
analyze t_part_local;
analyze t_part_primary；

无索引表测试：

explain (analyze,verbose) select * from t_part_noindex where ctime='2023-01-15'::date;

全局索引表测试：

explain (analyze,verbose) select * from t_part_global where ctime='2023-01-15'::date;

本地分区表测试：

explain (analyze,verbose) select * from t_part_local where ctime='2023-04-15'::date;

本地主键表测试：

explain (analyze,verbose) select * from t_part_primary where ctime='2023-08-15'::date;

3、在线删除分区测试

模拟DQL在线事务：

begin;
explain (analyze,verbose) select * from t_part_noindx where ctime = '2023-04-01 00:00:00'::timestamp;
explain (analyze,verbose) select /*+ indexscan(t_part_global ind_global) */ * from t_part_global where ctime = '2023-04-01 00:00:00'::timestamp;
explain (analyze,verbose) select /*+ indexscan(t_part_local ind_local) */ * from t_part_local where ctime = '2023-04-01 00:00:00'::timestamp;
explain (analyze,verbose) select /*+ indexscan(t_part_local pk_primary) */ * from t_part_primary where ctime = '2023-04-01 00:00:00'::timestamp;

查询锁信息：

SELECT pid,database,locktype, relation::regclass,mode,granted FROM pg_locks where granted='t' and database is not null;

在线删除分区：

alter table t_part_noindex drop partition p1;
alter table t_part_global drop partition p1;
alter table t_part_local drop partition p1;
alter table t_part_primary drop partition p1;

模拟DML在线事务：

begin;
explain (analyze,verbose) update t_part_noindex set id = 1000 where ctime = '2023-04-01 00:00:00'::timestamp;
explain (analyze,verbose) update t_part_global set id = 1000 where ctime = '2023-04-01 00:00:00'::timestamp;
explain (analyze,verbose) update t_part_local set id = 1000 where ctime = '2023-04-01 00:00:00'::timestamp;
explain (analyze,verbose) update t_part_primary set id = 1000 where ctime = '2023-04-01 00:00:00'::timestamp;

查询锁信息：

SELECT pid,database,locktype, relation::regclass,mode,granted FROM pg_locks where granted='t' and database is not null;

在线删除分区：

alter table t_part_noindex drop partition p3;
alter table t_part_global drop partition p3;
alter table t_part_local drop partition p3;
alter table t_part_primary drop partition p3;

检查分区删除结果：

\d t_part_noindex
\d t_part_global
\d t_part_local
\d t_part_primary

注意事项

1、为分区表建立primary key时，如果索引字段有partition key，建立本地索引；如果没有partition key，则建立全局索引：

==>global primary key
alter table xx add constraint yy primary(普通列);
==>local primary key
alter table xx add constraint yy primary(普通列,分区列);

2、删除分区操作会使全局分区索引失效。

3、使用分区表+全局索引，在执行计划使用indexscan时，不进行分区裁剪。

关于作者

赵安琪，云和恩墨技术顾问，熟悉 openGauss MogDB / Oracle / MySQL，10年左右的金融、通信等行业关键业务系统的运维经验，目前主要负责 MogDB 数据库的国产化替代和信创改造工作。

END

MogDB 是云和恩墨基于 openGauss 开源内核进行增强提升，推出的一款安稳易用的企业级关系型数据库。其具备金融级高可用和全密态计算的极致安全、面向多核处理器的极致性能、AI自诊断调优的极致智能能力，能够满足从核心交易到复杂计算的企业级业务需求。

访问官网了解更多：www.mogdb.io

产品兼容适配申请：partner@enmotech.com

进入交流群：Roger_database

mogdb 分区表数据库分区 mysql索引 oracle分区表

文章转载自MogDB，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

循序渐进丨MogDB 数据库分区表的静态数据分区删除测试

评论