作者简介：董森涛，瑞蓝创数据库专家
原创内容未经授权不得随意使用，转载请联系小编并注明来源
一、 问题描述
4.4.2版本mysql租户下普通分区表(非动态维护分区)转换为动态分区表的时候，个别老旧分区不会自动删除维护？
二、 问题复现
针对该问题构建模拟测试表，复现和分析问题，具体环境和步骤如下：
2.1 复现环境信息
MySQL [test_cnt]> select version();
+---------------------------+
| version() |
+---------------------------+
| 5.7.25-OceanBase-v4.4.2.2 |
+---------------------------+
2.2 基础准备构建测试表
构建test_dyn_part为测试对象，该表为普通分区表
-- 0. 删除测试表test_dyn_part
droptableifexists test_dyn_part;
-- 1. 创建测试test_dyn_part，RANGE COLUMNS 分区表
CREATETABLE test_dyn_part (
idINTNOTNULL,
create_time DATETIME NOTNULL,
nameVARCHAR(100),
PRIMARY KEY(id, create_time)
) PARTITIONBYRANGECOLUMNS(create_time) (
PARTITION p202401 VALUESLESSTHAN ('2024-04-01'),
PARTITION p202402 VALUESLESSTHAN ('2024-07-01'),
PARTITION p202403 VALUESLESSTHAN ('2024-10-01'),
PARTITION p202404 VALUESLESSTHAN ('2025-01-01')
);
-- 2.插入模拟数据
insertinto test_dyn_part(id,create_time,name)values(1,'2024-03-28','name_240328');
insertinto test_dyn_part(id,create_time,name)values(2,'2024-04-01','name_240401');
insertinto test_dyn_part(id,create_time,name)values(3,'2024-05-28','name_240528');
insertinto test_dyn_part(id,create_time,name)values(4,'2024-07-02','name_240702');
insertinto test_dyn_part(id,create_time,name)values(5,'2024-10-12','name_241012');
insertinto test_dyn_part(id,create_time,name)values(6,'2024-11-19','name_241119');
-- 3.查看当前表的数据
select * from test_dyn_part;
select'p202401'as pname,p.* from test_dyn_part PARTITION(p202401) p unionall
select'p202402'as pname,p.* from test_dyn_part PARTITION(p202402) p unionall
select'p202403'as pname,p.* from test_dyn_part PARTITION(p202403) p unionall
select'p202404'as pname,p.* from test_dyn_part PARTITION(p202404) p;
查看表和分区的TABLE_ID信息
SELECT tl.TABLE_ID,tl.TABLET_ID,tl.TABLE_TYPE,
tl.PARTITION_NAME,tl.LS_ID,HIGH_VALUE, PARTITION_POSITION
FROM oceanbase.DBA_TAB_PARTITIONS tp
left join (select * from oceanbase.DBA_OB_TABLE_LOCATIONS where ROLE='LEADER') tl
on tp.PARTITION_NAME=tl.PARTITION_NAME and
tp.TABLE_OWNER=tl.DATABASE_NAME and tp.TABLE_NAME=tl.TABLE_NAME
WHERE TABLE_OWNER = DATABASE() AND tp.TABLE_NAME = 'test_dyn_part'
ORDER BY PARTITION_POSITION;
执行结果如下
MySQL [test_cnt]> SELECT tl.TABLE_ID,tl.TABLET_ID,tl.TABLE_TYPE,
-> tl.PARTITION_NAME,tl.LS_ID,HIGH_VALUE, PARTITION_POSITION
-> FROM oceanbase.DBA_TAB_PARTITIONS tp
-> left join (select * from oceanbase.DBA_OB_TABLE_LOCATIONS whereROLE='LEADER') tl
-> on tp.PARTITION_NAME=tl.PARTITION_NAME and
-> tp.TABLE_OWNER=tl.DATABASE_NAME and tp.TABLE_NAME=tl.TABLE_NAME
-> WHERE TABLE_OWNER = DATABASE() AND tp.TABLE_NAME = 'test_dyn_part'
-> ORDER BY PARTITION_POSITION;
+----------+-----------+------------+----------------+-------+-----------------------+--------------------+
| TABLE_ID | TABLET_ID | TABLE_TYPE | PARTITION_NAME | LS_ID | HIGH_VALUE | PARTITION_POSITION |
+----------+-----------+------------+----------------+-------+-----------------------+--------------------+
| 522910 | 210205 | USER TABLE | p202401 | 1002 | '2024-04-01 00:00:00' | 1 |
| 522910 | 210206 | USER TABLE | p202402 | 1001 | '2024-07-01 00:00:00' | 2 |
| 522910 | 210207 | USER TABLE | p202403 | 1002 | '2024-10-01 00:00:00' | 3 |
| 522910 | 210208 | USER TABLE | p202404 | 1001 | '2025-01-01 00:00:00' | 4 |
+----------+-----------+------------+----------------+-------+-----------------------+--------------------+
4 rows in set (0.05 sec)
2.4 非动态分区表转换为动态分区表
将test_dyn_part转为动态分区表
ALTER TABLE test_dyn_part DYNAMIC_PARTITION_POLICY = (
ENABLE = TRUE,
TIME_UNIT = 'MONTH',
PRECREATE_TIME = '3MONTH',
EXPIRE_TIME = '1YEAR'
);
查看test_dyn_part动态分区表的策略
SHOW CREATE TABLE test_dyn_part;
执行结果如下：
MySQL [test_cnt]> SHOW CREATETABLE test_dyn_part;
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | CreateTable |
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_dyn_part | CREATETABLE`test_dyn_part` (
`id`int(11) NOTNULL,
`create_time` datetime NOTNULL,
`name`varchar(100) DEFAULTNULL,
PRIMARY KEY (`id`, `create_time`)
) ORGANIZATIONHEAPDEFAULTCHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 DYNAMIC_PARTITION_POLICY = (ENABLE = TRUE, TIME_UNIT = 'MONTH', PRECREATE_TIME = '3MONTH', EXPIRE_TIME = '1YEAR', TIME_ZONE = 'DEFAULT', BIGINT_PRECISION = 'NONE')
partitionbyrangecolumns(`create_time`)
(partition`p202401`valueslessthan ('2024-04-01 00:00:00'),
partition`p202402`valueslessthan ('2024-07-01 00:00:00'),
partition`p202403`valueslessthan ('2024-10-01 00:00:00'),
partition`p202404`valueslessthan ('2025-01-01 00:00:00')) |
+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1rowinset (0.02 sec)
查看表和分区的TABLE_ID信息
SELECT tl.TABLE_ID,tl.TABLET_ID,tl.TABLE_TYPE,
tl.PARTITION_NAME,tl.LS_ID,HIGH_VALUE, PARTITION_POSITION
FROM oceanbase.DBA_TAB_PARTITIONS tp
left join (select * from oceanbase.DBA_OB_TABLE_LOCATIONS where ROLE='LEADER') tl
on tp.PARTITION_NAME=tl.PARTITION_NAME and
tp.TABLE_OWNER=tl.DATABASE_NAME and tp.TABLE_NAME=tl.TABLE_NAME
WHERE TABLE_OWNER = DATABASE() AND tp.TABLE_NAME = 'test_dyn_part'
ORDER BY PARTITION_POSITION;
执行结果如下: TABLET_ID 没有发生变化，分区还是一开始的4个，相关动态维护的策略还没生效，因此此时该动态分区管理定时调度任务还没有触发，设置的过期策略是year，需要等到每天到00:00才触发调度进行分区动态维护。
MySQL [test_cnt]> SELECT tl.TABLE_ID,tl.TABLET_ID,tl.TABLE_TYPE,
-> tl.PARTITION_NAME,tl.LS_ID,HIGH_VALUE, PARTITION_POSITION
-> FROM oceanbase.DBA_TAB_PARTITIONS tp
-> left join (select * from oceanbase.DBA_OB_TABLE_LOCATIONS whereROLE='LEADER') tl
-> on tp.PARTITION_NAME=tl.PARTITION_NAME and
-> tp.TABLE_OWNER=tl.DATABASE_NAME and tp.TABLE_NAME=tl.TABLE_NAME
-> WHERE TABLE_OWNER = DATABASE() AND tp.TABLE_NAME = 'test_dyn_part'
-> ORDER BY PARTITION_POSITION;
+----------+-----------+------------+----------------+-------+-----------------------+--------------------+
| TABLE_ID | TABLET_ID | TABLE_TYPE | PARTITION_NAME | LS_ID | HIGH_VALUE | PARTITION_POSITION |
+----------+-----------+------------+----------------+-------+-----------------------+--------------------+
| 522910 | 210205 | USER TABLE | p202401 | 1002 | '2024-04-01 00:00:00' | 1 |
| 522910 | 210206 | USER TABLE | p202402 | 1001 | '2024-07-01 00:00:00' | 2 |
| 522910 | 210207 | USER TABLE | p202403 | 1002 | '2024-10-01 00:00:00' | 3 |
| 522910 | 210208 | USER TABLE | p202404 | 1001 | '2025-01-01 00:00:00' | 4 |
+----------+-----------+------------+----------------+-------+-----------------------+--------------------+
4 rows in set (0.02 sec)
2.5. 第1次 手动触发动态分区维护-出现不符合预期情况
手动触发动态分区维护管理
-- 手动触发动态分区管理（不等待后台任务）
CALL DBMS_PARTITION.MANAGE_DYNAMIC_PARTITION('3MONTH', 'MONTH');
查看test_dyn_part表转为动态分区表后的分区情况：此时动态分区创建出来，但是P202508之前的分区存在。
SELECT tl.TABLE_ID,tl.TABLET_ID,tl.TABLE_TYPE,
tl.PARTITION_NAME,tl.LS_ID,HIGH_VALUE, PARTITION_POSITION
FROM oceanbase.DBA_TAB_PARTITIONS tp
left join (select * from oceanbase.DBA_OB_TABLE_LOCATIONS where ROLE='LEADER') tl
on tp.PARTITION_NAME=tl.PARTITION_NAME and
tp.TABLE_OWNER=tl.DATABASE_NAME and tp.TABLE_NAME=tl.TABLE_NAME
WHERE TABLE_OWNER = DATABASE() AND tp.TABLE_NAME = 'test_dyn_part'
ORDER BY PARTITION_POSITION;
执行结果如下:当前时间为26-08-17，设置的策略是1年，但P202508之前的分区还存在 不符合预期。
MySQL [test_cnt]> SELECT tl.TABLE_ID,tl.TABLET_ID,tl.TABLE_TYPE,
-> tl.PARTITION_NAME,tl.LS_ID,HIGH_VALUE, PARTITION_POSITION
-> FROM oceanbase.DBA_TAB_PARTITIONS tp
-> left join (select * from oceanbase.DBA_OB_TABLE_LOCATIONS whereROLE='LEADER') tl
-> on tp.PARTITION_NAME=tl.PARTITION_NAME and
-> tp.TABLE_OWNER=tl.DATABASE_NAME and tp.TABLE_NAME=tl.TABLE_NAME
-> WHERE TABLE_OWNER = DATABASE() AND tp.TABLE_NAME = 'test_dyn_part'
-> ORDER BY PARTITION_POSITION;
+----------+-----------+------------+----------------+-------+-----------------------+--------------------+
| TABLE_ID | TABLET_ID | TABLE_TYPE | PARTITION_NAME | LS_ID | HIGH_VALUE | PARTITION_POSITION |
+----------+-----------+------------+----------------+-------+-----------------------+--------------------+
| 522910 | 210208 | USER TABLE | p202404 | 1001 | '2025-01-01 00:00:00' | 1 |
| 522910 | 210209 | USER TABLE | P202501 | 1001 | '2025-02-01 00:00:00' | 2 |
| 522910 | 210210 | USER TABLE | P202502 | 1002 | '2025-03-01 00:00:00' | 3 |
| 522910 | 210211 | USER TABLE | P202503 | 1001 | '2025-04-01 00:00:00' | 4 |
| 522910 | 210212 | USER TABLE | P202504 | 1002 | '2025-05-01 00:00:00' | 5 |
| 522910 | 210213 | USER TABLE | P202505 | 1001 | '2025-06-01 00:00:00' | 6 |
| 522910 | 210214 | USER TABLE | P202506 | 1002 | '2025-07-01 00:00:00' | 7 |
| 522910 | 210215 | USER TABLE | P202507 | 1001 | '2025-08-01 00:00:00' | 8 |
| 522910 | 210216 | USER TABLE | P202508 | 1002 | '2025-09-01 00:00:00' | 9 |
| 522910 | 210217 | USER TABLE | P202509 | 1001 | '2025-10-01 00:00:00' | 10 |
| 522910 | 210218 | USER TABLE | P202510 | 1002 | '2025-11-01 00:00:00' | 11 |
| 522910 | 210219 | USER TABLE | P202511 | 1001 | '2025-12-01 00:00:00' | 12 |
| 522910 | 210220 | USER TABLE | P202512 | 1002 | '2026-01-01 00:00:00' | 13 |
| 522910 | 210221 | USER TABLE | P202601 | 1001 | '2026-02-01 00:00:00' | 14 |
| 522910 | 210222 | USER TABLE | P202602 | 1002 | '2026-03-01 00:00:00' | 15 |
| 522910 | 210223 | USER TABLE | P202603 | 1001 | '2026-04-01 00:00:00' | 16 |
| 522910 | 210224 | USER TABLE | P202604 | 1002 | '2026-05-01 00:00:00' | 17 |
| 522910 | 210225 | USER TABLE | P202605 | 1001 | '2026-06-01 00:00:00' | 18 |
| 522910 | 210226 | USER TABLE | P202606 | 1002 | '2026-07-01 00:00:00' | 19 |
| 522910 | 210227 | USER TABLE | P202607 | 1001 | '2026-08-01 00:00:00' | 20 |
| 522910 | 210228 | USER TABLE | P202608 | 1002 | '2026-09-01 00:00:00' | 21 |
| 522910 | 210229 | USER TABLE | P202609 | 1001 | '2026-10-01 00:00:00' | 22 |
| 522910 | 210230 | USER TABLE | P202610 | 1002 | '2026-11-01 00:00:00' | 23 |
| 522910 | 210231 | USER TABLE | P202611 | 1001 | '2026-12-01 00:00:00' | 24 |
+----------+-----------+------------+----------------+-------+-----------------------+--------------------+
24 rows in set (0.08 sec)
2.6. 第2次 手动触发动态分区维护-符合预期
再次手动触发动态分区管理
CALL DBMS_PARTITION.MANAGE_DYNAMIC_PARTITION('3MONTH', 'MONTH');
查看test_dyn_part表转为动态分区表后的分区情况
SELECT tl.TABLE_ID,tl.TABLET_ID,tl.TABLE_TYPE,
tl.PARTITION_NAME,tl.LS_ID,HIGH_VALUE, PARTITION_POSITION
FROM oceanbase.DBA_TAB_PARTITIONS tp
left join (select * from oceanbase.DBA_OB_TABLE_LOCATIONS where ROLE='LEADER') tl
on tp.PARTITION_NAME=tl.PARTITION_NAME and
tp.TABLE_OWNER=tl.DATABASE_NAME and tp.TABLE_NAME=tl.TABLE_NAME
WHERE TABLE_OWNER = DATABASE() AND tp.TABLE_NAME = 'test_dyn_part'
ORDER BY PARTITION_POSITION;
执行结果: 此时保留的分区个数 符合预期，为当前时间的往前保留1年（最小的分区为P202508）
MySQL [test_cnt]> SELECT tl.TABLE_ID,tl.TABLET_ID,tl.TABLE_TYPE,
-> tl.PARTITION_NAME,tl.LS_ID,HIGH_VALUE, PARTITION_POSITION
-> FROM oceanbase.DBA_TAB_PARTITIONS tp
-> left join (select * from oceanbase.DBA_OB_TABLE_LOCATIONS whereROLE='LEADER') tl
-> on tp.PARTITION_NAME=tl.PARTITION_NAME and
-> tp.TABLE_OWNER=tl.DATABASE_NAME and tp.TABLE_NAME=tl.TABLE_NAME
-> WHERE TABLE_OWNER = DATABASE() AND tp.TABLE_NAME = 'test_dyn_part'
-> ORDER BY PARTITION_POSITION;
+----------+-----------+------------+----------------+-------+-----------------------+--------------------+
| TABLE_ID | TABLET_ID | TABLE_TYPE | PARTITION_NAME | LS_ID | HIGH_VALUE | PARTITION_POSITION |
+----------+-----------+------------+----------------+-------+-----------------------+--------------------+
| 522910 | 210216 | USER TABLE | P202508 | 1002 | '2025-09-01 00:00:00' | 1 |
| 522910 | 210217 | USER TABLE | P202509 | 1001 | '2025-10-01 00:00:00' | 2 |
| 522910 | 210218 | USER TABLE | P202510 | 1002 | '2025-11-01 00:00:00' | 3 |
| 522910 | 210219 | USER TABLE | P202511 | 1001 | '2025-12-01 00:00:00' | 4 |
| 522910 | 210220 | USER TABLE | P202512 | 1002 | '2026-01-01 00:00:00' | 5 |
| 522910 | 210221 | USER TABLE | P202601 | 1001 | '2026-02-01 00:00:00' | 6 |
| 522910 | 210222 | USER TABLE | P202602 | 1002 | '2026-03-01 00:00:00' | 7 |
| 522910 | 210223 | USER TABLE | P202603 | 1001 | '2026-04-01 00:00:00' | 8 |
| 522910 | 210224 | USER TABLE | P202604 | 1002 | '2026-05-01 00:00:00' | 9 |
| 522910 | 210225 | USER TABLE | P202605 | 1001 | '2026-06-01 00:00:00' | 10 |
| 522910 | 210226 | USER TABLE | P202606 | 1002 | '2026-07-01 00:00:00' | 11 |
| 522910 | 210227 | USER TABLE | P202607 | 1001 | '2026-08-01 00:00:00' | 12 |
| 522910 | 210228 | USER TABLE | P202608 | 1002 | '2026-09-01 00:00:00' | 13 |
| 522910 | 210229 | USER TABLE | P202609 | 1001 | '2026-10-01 00:00:00' | 14 |
| 522910 | 210230 | USER TABLE | P202610 | 1002 | '2026-11-01 00:00:00' | 15 |
| 522910 | 210231 | USER TABLE | P202611 | 1001 | '2026-12-01 00:00:00' | 16 |
+----------+-----------+------------+----------------+-------+-----------------------+--------------------+
16 rows in set (0.09 sec)
三、 原因分析
下面语句可以查到test_dyn_part表转为动态分区表后的策略是保留1年的分区(即1年前的分区自动动态删除)；
SELECT DATABASE_NAME, TABLE_NAME, ENABLE, TIME_UNIT,
PRECREATE_TIME, EXPIRE_TIME
FROM oceanbase.DBA_OB_DYNAMIC_PARTITION_TABLES
WHERE TABLE_NAME = 'test_dyn_part';
执行结果
MySQL [test_cnt]> SELECT DATABASE_NAME, TABLE_NAME, ENABLE, TIME_UNIT,
-> PRECREATE_TIME, EXPIRE_TIME
-> FROM oceanbase.DBA_OB_DYNAMIC_PARTITION_TABLES
-> WHERE TABLE_NAME = 'test_dyn_part';
+---------------+---------------+--------+-----------+----------------+-------------+
| DATABASE_NAME | TABLE_NAME | ENABLE | TIME_UNIT | PRECREATE_TIME | EXPIRE_TIME |
+---------------+---------------+--------+-----------+----------------+-------------+
| test_cnt | test_dyn_part | TRUE | MONTH | 3MONTH | 1YEAR |
+---------------+---------------+--------+-----------+----------------+-------------+
1 row in set (0.02 sec)
第1次手动触发 CALL DBMS_PARTITION.MANAGE_DYNAMIC_PARTITION('3MONTH', 'MONTH');预创建3个月表，当前时间是26-08-17，也就是逻辑上25-08-01之前的分区应该删除。但P202508之前甚至p202404的分区也存在；ob底层的动态分区维护的逻辑顺序：
先执行是否需要添加分区
-
起点 = 最后一个分区 p202404 的 high_bound(2025-01-01) 向下取整到 MONTH则为2025-01-01 -
终点 = max(表的PRECREATE_TIME=3MONTH, 指定值=3MONTH)，当前时间为2026-08 + 3MONTH ≈ 2026-11-17 -
创建分区：自动补分区创建 P202501, P202502, ..., P202610, P202611（共23个）
在添加分区之后再删除过期分区
-
每次在删除的时候最后一个分区会跳过，第一次出现过期未删除主要的时候获取table_schema是旧快照信息导致；
init() → table_schema_ 当前的schema快照
↓
execute()
├─ add_dynamic_partition_() → write_ddl_("ALTER TABLE ... ADD PARTITION ...")
└─ drop_dynamic_partition_() → build_expired_partition_name_list_()
→ for i get_partition_num() - 1
→ table_schema_只有在init的时候被初始化，相当于此时读取的是旧快照，因此只遍历 p202401, p202402, p202403
→ p202404 是 i=3，被跳过,分区表至少得存在1个分区
第2次手动触发 CALL DBMS_PARTITION.MANAGE_DYNAMIC_PARTITION('3MONTH', 'MONTH');
先执行是否需要添加分区
-
此时p202404已经不是最后一个分区，最后一个分区为P202611，此时不需预先创建3个月的表，前面第一次已经创建；
在添加分区之后再删除过期分区
-
此时的表有p202404到P202611，发现p202404到P202507均符合过期分区，执行删除，最后剩下P202508到P202611的分区，此时符合预期；
四、 问题结论
-
mysql租户下普通分区表(非动态维护分区)转换为动态分区表的时候，个别老旧分区不会自动删除维护(不完全准确)，该现象主要原因是：第1次触发动态分区管理的时候，在单次判断删除过期分区的时候读取的是旧快照schema导致；但在下一个调度触发周是会自动删除之前的过期分区。
-
前面的“不符合预期”，功能层面实际不会有大影响，效果上表现为"首次多保留一轮"；从测试从普通分区表(非动态维护分区)转换为动态分区表的时候是onlien ddl ，非动态分区转换为动态分区还是一个相对优化的功能。
-
需要注意的是：在该版本中动态分区的TIME_UNIT是不支持修改的，如下ddl执行的时候会报错，因此在设计动态分区表的策略的时候，对于TIME_UNIT建议不要随意设置，以免后期修改需要重建；
ALTER TABLE test_dyn_part DYNAMIC_PARTITION_POLICY = (
TIME_UNIT = 'DAY',
PRECREATE_TIME = '7DAY',
EXPIRE_TIME = '90DAY'
);
-- 会提示错误：Not supported feature or function
END
瑞蓝创 OceanBase OBCP V4 精英训练营
点击下方图片立即了解详情
▼ 点击「阅读原文」，了解更多产品技术文章