如何为已分区表新增分区且无需重组现有p_max分区数据

注意：本方案仅适用于使用RANGE类型分区、且末分区为VALUES LESS THAN MAXVALUE定义的p_max分区的表

当前典型的分区结构示意如下：

前置校验 #

你需要新增的分区对应范围的数据完全不存在于当前p_max分区中，如果存在该范围数据，你始终需要对这部分数据进行迁移，无法完全跳过数据处理步骤。

具体操作步骤 #

该方案通过分区交换的方式绕开对海量数据p_max分区的扫描和重组，所有核心操作均为元数据操作，可秒级完成：

• 创建一张和原分区表结构完全一致的空普通表（非分区表），命名为tmp_p_max

CREATE TABLE tmp_p_max LIKE original_table;
ALTER TABLE tmp_p_max REMOVE PARTITIONING;

• 将现有的p_max分区和tmp_p_max进行分区交换，该操作仅修改元数据，不会实际移动数据，瞬间完成

ALTER TABLE original_table EXCHANGE PARTITION p_max WITH TABLE tmp_p_max;

• 此时原表的p_max分区为空，直接对空p_max执行重组操作，拆分出需要的新分区和新的p_max分区，空分区重组操作无数据处理，秒级完成（以下为新增2024年1月、2月分区的示例，可按需调整分区规则）

ALTER TABLE original_table REORGANIZE PARTITION p_max INTO (
    PARTITION p202401 VALUES LESS THAN ('2024-02-01'),
    PARTITION p202402 VALUES LESS THAN ('2024-03-01'),
    PARTITION p_max VALUES LESS THAN MAXVALUE
);

• 将存储了原p_max数据的tmp_p_max和新的空p_max分区交换回去，同样为元数据操作，瞬间完成

ALTER TABLE original_table EXCHANGE PARTITION p_max WITH TABLE tmp_p_max;

• 操作验证无误后，删除临时表即可

DROP TABLE tmp_p_max;

注意事项 #

• 操作前务必校验新增分区范围内无数据存在于原p_max分区，否则会出现数据归属分区错误，影响后续查询和写入
• 建议在业务低峰期执行操作，避免短暂的元数据锁影响线上业务
• 操作前请做好数据和结构备份，避免操作失误引发数据丢失

内容的提问来源于stack exchange，提问作者Manu