使用REORGANIZE PARTITION合并拆分或修改分区-云原生数据库 PolarDB-阿里云

REORGANIZE PARTITION用于对LIST或RANGE分区表的分区进行结构重组（如合并、拆分或修改分区），同时自动重新分布数据且不丢失数据。

效果展示

拆分分区

-- 将 p2019_2020 拆分为 2019 和 2020 两个父分区
ALTER TABLE sales_data
REORGANIZE PARTITION p2019_2020 INTO (
    PARTITION p2019 VALUES LESS THAN (2020) (
        SUBPARTITION s2019_q1,
        SUBPARTITION s2019_q2,
        SUBPARTITION s2019_q3,
        SUBPARTITION s2019_q4
    ),
    PARTITION p2020 VALUES LESS THAN (2021) (
        SUBPARTITION s2020_q1,
        SUBPARTITION s2020_q2,
        SUBPARTITION s2020_q3,
        SUBPARTITION s2020_q4
    )
);

合并分区

-- 合并 p2021 和 p2022 分区
ALTER TABLE sales_data
REORGANIZE PARTITION p2021, p2022 INTO (
    PARTITION p2021_2022 VALUES LESS THAN (2023) (
        SUBPARTITION s2021_2022_q1,
        SUBPARTITION s2021_2022_q2,
        SUBPARTITION s2021_2022_q3,
        SUBPARTITION s2021_2022_q4
    )
);

合并分区时，需为连续相邻的分区。

修改分区

-- 修改分区为季度划分
ALTER TABLE logs
REORGANIZE PARTITION p0, p1 INTO (
    PARTITION q1 VALUES LESS THAN (TO_DAYS('2023-04-01')),
    PARTITION q2 VALUES LESS THAN (TO_DAYS('2023-07-01')),
    PARTITION q3 VALUES LESS THAN (TO_DAYS('2023-10-01')),
    PARTITION q4 VALUES LESS THAN (TO_DAYS('2024-01-01'))
);

语法

ALTER TABLE table_name
    REORGANIZE PARTITION partition_names INTO (partition_definitions)

partition_definitions: {list_partition | range_partition}

subpartition_definition: {list_subpartition | range_subpartition ｜ hash_subpartition | key_subpartition}

partition_definitions分区说明

list_partition

PARTITION partition_name VALUES IN (value[, value]...) 
(subpartition, ...)

range_partition

PARTITION partition_name VALUES LESS THAN (value[, value]...) 
(subpartition, ...)

subpartition_definition子分区说明

list_subpartition

SUBPARTITION [subpartition_name] VALUES IN (value[, value]...) 
[TABLESPACE tablespace_name]

range_subpartition

SUBPARTITION [subpartition_name] VALUES LESS THAN (value[, value]...) 
[TABLESPACE tablespace_name]

hash_subpartition/key_subpartition为：

SUBPARTITION [subpartition_name ]
[TABLESPACE tablespace_name]

参数	说明
table_name	表名
partition_names	需要合并或拆分的现有分区名列表，以英文逗号分隔。
partition_definitions	新分区定义列表，以英文逗号分隔。
partition_name	需要创建的分区名称。说明分区名称在所有分区和子分区中必须是唯一的，且必须遵循给对象标识符命名的惯例。
subpartition_name	需要创建的子分区名称。说明子分区名称在所有分区和子分区中必须是唯一的，且必须遵循给对象标识符命名的惯例。

示例

数据准备

-- 创建按年分区 + 按季度哈希子分区的销售表
CREATE TABLE sales_data (
    order_id INT AUTO_INCREMENT,
    order_date DATE NOT NULL,
    amount DECIMAL(10,2),
    PRIMARY KEY (order_id, order_date)  -- 主键必须包含分区键
)
PARTITION BY RANGE (YEAR(order_date))
SUBPARTITION BY HASH( QUARTER(order_date) )
SUBPARTITIONS 4 (  -- 每个父分区默认4个子分区
    PARTITION p2019_2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 插入测试数据
INSERT INTO sales_data (order_date, amount) VALUES
('2020-03-15', 100.00),  -- p2019_2020
('2021-06-20', 200.00),  -- p2021
('2022-09-10', 300.00),  -- p2022
('2023-12-25', 400.00);  -- p_future

拆分分区

将p2019_2020拆分为p2019和p2020两个分区。

ALTER TABLE sales_data
REORGANIZE PARTITION p2019_2020 INTO (
    PARTITION p2019 VALUES LESS THAN (2020) (
        SUBPARTITION s2019_q1,
        SUBPARTITION s2019_q2,
        SUBPARTITION s2019_q3,
        SUBPARTITION s2019_q4
    ),
    PARTITION p2020 VALUES LESS THAN (2021) (
        SUBPARTITION s2020_q1,
        SUBPARTITION s2020_q2,
        SUBPARTITION s2020_q3,
        SUBPARTITION s2020_q4
    )
);

合并分区

将p2021和p2022合并为一个p2021_2022分区。

ALTER TABLE sales_data
REORGANIZE PARTITION p2021, p2022 INTO (
    PARTITION p2021_2022 VALUES LESS THAN (2023) (
        SUBPARTITION s2021_2022_q1,
        SUBPARTITION s2021_2022_q2,
        SUBPARTITION s2021_2022_q3,
        SUBPARTITION s2021_2022_q4
    )
);

修改分区

将p2019、p2020、p2021_2022和p_future四个分区修改为p2019、p2020_2021和p2022_future三个分区。

ALTER TABLE sales_data
REORGANIZE PARTITION p2019, p2020, p2021_2022, p_future INTO (
    PARTITION p2019 VALUES LESS THAN (2020) (
        SUBPARTITION s2019_q1,
        SUBPARTITION s2019_q2,
        SUBPARTITION s2019_q3,
        SUBPARTITION s2019_q4
    ),
    PARTITION p2020_2021 VALUES LESS THAN (2022) (
        SUBPARTITION s2020_2021_q1,
        SUBPARTITION s2020_2021_q2,
        SUBPARTITION s2020_2021_q3,
        SUBPARTITION s2020_2021_q4
    ),
    PARTITION p2022_future VALUES LESS THAN MAXVALUE (
        SUBPARTITION s2022_future_1,
        SUBPARTITION s2022_future_2,
        SUBPARTITION s2022_future_3,
        SUBPARTITION s2022_future_4
    )
);