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

测试说明

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

测试流程

1. 建立测试表

2. 测试4组分区表分区裁剪功能

3. 测试4组分区表静态数据分区删除功能

测试结论

版本信息

数据库版本：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 数据库的国产化替代和信创改造工作。

数据驱动，成就未来，云和恩墨，不负所托！

---

云和恩墨创立于2011年，是业界领先的“智能的数据技术提供商”。公司总部位于北京，在国内外35个地区设有本地办公室并开展业务。

云和恩墨以“数据驱动，成就未来”为使命，致力于将创新的数据技术产品和解决方案带给全球的企业和组织，帮助客户构建安全、高效、敏捷且经济的数据环境，持续增强客户在数据洞察和决策上的竞争优势，实现数据驱动的业务创新和升级发展。

自成立以来，云和恩墨专注于数据技术领域，根据不断变化的市场需求，创新研发了系列软件产品，涵盖数据库、数据库存储、数据库云管和数据智能分析等领域。这些产品已经在集团型、大中型、高成长型客户以及行业云场景中得到广泛应用，证明了我们的技术和商业竞争力，展现了公司在数据技术端到端解决方案方面的优势。

在云化、数字化和智能化的时代背景下，云和恩墨始终以正和多赢为目标，感恩每一位客户和合作伙伴的信任与支持，“利他先行”，坚持投入于数据技术核心能力，为构建数据驱动的智能未来而不懈努力。

我们期待与您携手，共同探索数据力量，迎接智能未来。