火山引擎-你的AI云

MySQL 5.7复合分区创建方法及parcels表分区优化咨询

阿华AIGC实验室

2026-4-17

MySQL 5.7复合分区创建方法及parcels表分区优化咨询

嗨，我看了你关于3000万行parcels表分区优化的问题——要按CREATED_AT和PAYMENT_STATUS做分区来逐步清理旧数据，之前试了复合分区没成功，我来帮你梳理下MySQL 5.7里的正确做法，结合你的需求给出两种可行方案。

首先要明确：你的核心需求是按时间归档旧数据，同时结合支付状态做细分，所以要么用RANGE COLUMNS复合分区，要么用RANGE主分区+LIST子分区，这两种都能满足你的需求，下面分别说明：

方案一：RANGE COLUMNS复合分区（按时间+状态组合范围）

这种方式直接把CREATED_AT和PAYMENT_STATUS作为复合分区键，MySQL会按列顺序依次比较（先比时间，时间相同再比状态），适合你需要按「时间+状态」的组合范围来归档的场景。

完整创建SQL

CREATE TABLE parcels_partitioned (
    `ID` int(11) NOT NULL AUTO_INCREMENT,
    `PAYMENT_STATUS` varchar(32) NOT NULL DEFAULT 'unpaid',
    `CREATED_AT` datetime NOT NULL,
    -- 分区键必须包含在主键中，这是MySQL分区表的硬性要求
    PRIMARY KEY (`ID`, `CREATED_AT`, `PAYMENT_STATUS`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
PARTITION BY RANGE COLUMNS(CREATED_AT, PAYMENT_STATUS) (
    -- 2024年1月及之前的unpaid数据（时间到2024-02-01，状态小于'unpaid'）
    PARTITION p_202401_unpaid VALUES LESS THAN ('2024-02-01 00:00:00', 'unpaid'),
    -- 2024年1月及之前的所有非unpaid数据（用'zzzzz'覆盖所有字典序大于'unpaid'的状态）
    PARTITION p_202401_paid VALUES LESS THAN ('2024-02-01 00:00:00', 'zzzzz'),
    -- 2024年2月的unpaid数据
    PARTITION p_202402_unpaid VALUES LESS THAN ('2024-03-01 00:00:00', 'unpaid'),
    -- 2024年2月的所有非unpaid数据
    PARTITION p_202402_paid VALUES LESS THAN ('2024-03-01 00:00:00', 'zzzzz'),
    -- 未来数据的默认分区，避免插入数据时出错
    PARTITION p_future VALUES LESS THAN (MAXVALUE, MAXVALUE)
);

清理旧数据的方式

删除2024年1月的unpaid数据：ALTER TABLE parcels_partitioned DROP PARTITION p_202401_unpaid;
删除2024年1月的所有数据：同时DROP p_202401_unpaid和p_202401_paid分区

方案二：RANGE主分区+LIST子分区（更直观的分层管理）

这种方案先按CREATED_AT做RANGE主分区（按时间归档的核心），每个主分区下再按PAYMENT_STATUS做LIST子分区，适合你主要按时间批量归档，偶尔需要在特定时间范围内删除特定状态数据的场景。

完整创建SQL

CREATE TABLE parcels_subpartitioned (
    `ID` int(11) NOT NULL AUTO_INCREMENT,
    `PAYMENT_STATUS` varchar(32) NOT NULL DEFAULT 'unpaid',
    `CREATED_AT` datetime NOT NULL,
    -- 同样要把分区键（CREATED_AT）和子分区键（PAYMENT_STATUS）加入主键
    PRIMARY KEY (`ID`, `CREATED_AT`, `PAYMENT_STATUS`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
-- 用TO_DAYS把日期转成天数，方便RANGE分区的范围判断
PARTITION BY RANGE (TO_DAYS(CREATED_AT))
-- 子分区用LIST COLUMNS匹配具体的支付状态值
SUBPARTITION BY LIST COLUMNS(PAYMENT_STATUS) (
    -- 2024年1月的主分区
    PARTITION p_202401 VALUES LESS THAN (TO_DAYS('2024-02-01')) (
        SUBPARTITION sp_202401_unpaid VALUES IN ('unpaid'),
        SUBPARTITION sp_202401_paid VALUES IN ('paid'),
        SUBPARTITION sp_202401_other VALUES IN ('refunded', 'cancelled')
    ),
    -- 2024年2月的主分区
    PARTITION p_202402 VALUES LESS THAN (TO_DAYS('2024-03-01')) (
        SUBPARTITION sp_202402_unpaid VALUES IN ('unpaid'),
        SUBPARTITION sp_202402_paid VALUES IN ('paid'),
        SUBPARTITION sp_202402_other VALUES IN ('refunded', 'cancelled')
    ),
    -- 未来数据的主分区
    PARTITION p_future VALUES LESS THAN MAXVALUE (
        SUBPARTITION sp_future_unpaid VALUES IN ('unpaid'),
        SUBPARTITION sp_future_paid VALUES IN ('paid'),
        SUBPARTITION sp_future_other VALUES IN ('refunded', 'cancelled')
    )
);

清理旧数据的方式

删除2024年1月的所有数据：ALTER TABLE parcels_subpartitioned DROP PARTITION p_202401;
删除2024年1月的unpaid数据：ALTER TABLE parcels_subpartitioned DROP SUBPARTITION sp_202401_unpaid;

大表迁移的注意事项（必看！）

你的原表有3000万行，绝对不要直接在原表上ALTER加分区——这会锁表很久，影响业务。正确的做法是：

先创建上述的分区表
分批导入数据：比如按月份用INSERT INTO parcels_partitioned SELECT * FROM parcels WHERE CREATED_AT BETWEEN '2024-01-01' AND '2024-01-31';，避免一次性全表导入导致性能问题
数据校验无误后，切换表名：RENAME TABLE parcels TO parcels_old, parcels_partitioned TO parcels;
确认业务正常后，再删除旧表parcels_old