MySQL双列分区表实现咨询：events表多规则分区需求

一、明确MySQL分区限制 #

1. 分区键不能依赖动态函数（如NOW()），因为数据的分区归属在插入时就已确定，不会随时间自动变更。
2. 分区必须满足排他性：每条记录只能属于一个分区，因此原需求中“同时按status和日期划分6个分区”的逻辑存在重叠冲突，需调整优先级或分区规则。
3. 若表存在主键/唯一键，分区键必须包含该键的所有字段。

二、方案1：LIST-RANGE复合分区（子分区，推荐） #

该方案通过先按status做LIST分区，再在每个LIST分区下按start_date做RANGE子分区，既满足按status过滤的需求，又实现了历史/未来数据的分离，虽然分区总数为8，但能高效支持两类查询。

示例代码 #

-- 创建带复合分区的表（若已有表，需先备份数据后重建）
CREATE TABLE events (
    id INT AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    status INT NOT NULL CHECK (status BETWEEN 1 AND 4),
    start_date DATETIME NOT NULL,
    -- 调整主键以满足分区键规则（分区键需包含主键字段）
    PRIMARY KEY (id, status)
)
PARTITION BY LIST(status)
SUBPARTITION BY RANGE (TO_DAYS(start_date)) (
    -- status=1的分区，下分历史/未来子分区
    PARTITION p_status_1 VALUES IN (1) (
        SUBPARTITION p1_old VALUES LESS THAN (TO_DAYS(CURDATE())),
        SUBPARTITION p1_future VALUES GREATER THAN OR EQUAL TO (TO_DAYS(CURDATE()))
    ),
    -- status=2的分区
    PARTITION p_status_2 VALUES IN (2) (
        SUBPARTITION p2_old VALUES LESS THAN (TO_DAYS(CURDATE())),
        SUBPARTITION p2_future VALUES GREATER THAN OR EQUAL TO (TO_DAYS(CURDATE()))
    ),
    -- status=3的分区
    PARTITION p_status_3 VALUES IN (3) (
        SUBPARTITION p3_old VALUES LESS THAN (TO_DAYS(CURDATE())),
        SUBPARTITION p3_future VALUES GREATER THAN OR EQUAL TO (TO_DAYS(CURDATE()))
    ),
    -- status=4的分区
    PARTITION p_status_4 VALUES IN (4) (
        SUBPARTITION p4_old VALUES LESS THAN (TO_DAYS(CURDATE())),
        SUBPARTITION p4_future VALUES GREATER THAN OR EQUAL TO (TO_DAYS(CURDATE()))
    )
);

维护说明 #

• 由于TO_DAYS(CURDATE())在分区创建时是固定值，随着时间推移，未来子分区的记录会变成历史数据，需定期手动调整分区：
  1. 创建新的历史子分区（如每月1日）
  2. 将未来子分区中已过期的数据迁移到历史子分区
  3. 移除旧的未来子分区

三、方案2：计算列+LIST分区（严格匹配6个分区需求） #

若必须按6个分区划分，可通过计算列将status和日期条件合并为单一分区键，需接受手动迁移数据的成本。

步骤1：添加计算列（或创建表时定义） #

-- 若已有表，添加存储型计算列
ALTER TABLE events ADD COLUMN partition_key INT AS (
    CASE
        WHEN start_date < CURDATE() THEN status
        WHEN start_date > CURDATE() THEN 5
        ELSE 6 -- 处理start_date等于当前日期的情况
    END
) STORED;

-- 调整主键，确保包含分区键
ALTER TABLE events DROP PRIMARY KEY;
ALTER TABLE events ADD PRIMARY KEY (id, partition_key);

步骤2：创建LIST分区 #

ALTER TABLE events
PARTITION BY LIST(partition_key) (
    PARTITION p0 VALUES IN (1), -- status=1且start_date<当前日期
    PARTITION p1 VALUES IN (2), -- status=2且start_date<当前日期
    PARTITION p2 VALUES IN (3), -- status=3且start_date<当前日期
    PARTITION p3 VALUES IN (4), -- status=4且start_date<当前日期
    PARTITION p4 VALUES IN (5), -- 所有status且start_date>当前日期
    PARTITION p5 VALUES IN (6)  -- 所有status且start_date=当前日期
);

维护说明 #

• 随着日期推移，p4中的记录会逐渐变为历史数据，需定期执行数据迁移（分批处理避免锁表）：

  -- 分批迁移p4中已过期的数据
  INSERT INTO events SELECT * FROM events PARTITION (p4) WHERE start_date < CURDATE() LIMIT 1000;
  DELETE FROM events PARTITION (p4) WHERE start_date < CURDATE() LIMIT 1000;
  

四、注意事项 #

• 对于2200万条的大表，分区操作前务必备份数据，避免数据丢失。
• 分区后需验证查询性能，确保分区键与常用查询条件匹配，避免全分区扫描。
• 若使用计算列，需确保MySQL版本支持（5.7及以上支持存储型计算列作为分区键）。

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