如何按日期动态分区数据表并自动归档历史数据？

针对你400万行数据表的动态分区需求，我整理了一套完整的实现方案，涵盖表结构改造、初始数据分区和定时维护脚本，确保自动将超过91天的数据归档到历史分区。

1. 先改造原表为分区表 #

首先，你的原表没有分区，而且InnoDB分区表要求分区键必须是主键的一部分，所以我们需要先调整主键结构，再添加分区。操作前一定要备份数据！

-- 第一步：备份原表（关键！防止数据丢失）
CREATE TABLE LargeTable_backup LIKE LargeTable;
INSERT INTO LargeTable_backup SELECT * FROM LargeTable;

-- 第二步：修改表结构，添加分区支持
ALTER TABLE LargeTable 
DROP PRIMARY KEY,
ADD PRIMARY KEY (id, DateOrder), -- 将DateOrder加入主键，满足分区要求
PARTITION BY RANGE (TO_DAYS(DateOrder)) (
    PARTITION p_current VALUES LESS THAN (TO_DAYS(NOW())), -- 临时存当前所有数据
    PARTITION p_history VALUES LESS THAN MAXVALUE -- 临时存历史数据（初始为空）
);

2. 初始化分区：把现有数据分到正确的分区 #

接下来我们需要把已有的数据按照“最近3个月/更早”的规则拆分到对应分区：

-- 计算3个月前的日期对应的TO_DAYS值
SET @cutoff = TO_DAYS(NOW() - INTERVAL 3 MONTH);

-- 拆分当前分区，把早于3个月的数据分离到临时分区
ALTER TABLE LargeTable
REORGANIZE PARTITION p_current INTO (
    PARTITION p_temp_history VALUES LESS THAN (@cutoff),
    PARTITION p_current VALUES LESS THAN (TO_DAYS(NOW()))
);

-- 把临时历史分区合并到正式历史分区
ALTER TABLE LargeTable
REORGANIZE PARTITION p_temp_history, p_history INTO (
    PARTITION p_history VALUES LESS THAN MAXVALUE
);

执行完后，你可以用下面的SQL验证分区数据是否正确：

SELECT PARTITION_NAME, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'LargeTable' AND TABLE_SCHEMA = DATABASE();

3. 定时维护脚本：自动归档超过91天的数据 #

要实现“数据满91天自动移到历史分区”，我们可以创建一个定时任务，每天执行一次分区维护。这里提供两种方式：

方式一：MySQL内置事件（推荐） #

-- 先确保MySQL事件调度器已开启
SET GLOBAL event_scheduler = ON;

-- 创建每天凌晨1点执行的维护事件
CREATE EVENT e_maintain_largetable_partitions
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_DATE + INTERVAL 1 HOUR
DO
BEGIN
    SET @cutoff = TO_DAYS(NOW() - INTERVAL 3 MONTH);
    
    -- 拆分当前分区，将超过3个月的数据移到临时分区
    ALTER TABLE LargeTable
    REORGANIZE PARTITION p_current INTO (
        PARTITION p_temp VALUES LESS THAN (@cutoff),
        PARTITION p_current VALUES LESS THAN (TO_DAYS(NOW() + INTERVAL 1 DAY)) -- 扩展当前分区到明天，避免新插入数据报错
    );
    
    -- 合并临时分区到历史分区
    ALTER TABLE LargeTable
    REORGANIZE PARTITION p_temp, p_history INTO (
        PARTITION p_history VALUES LESS THAN MAXVALUE
    );
END;

方式二：Linux Crontab定时执行脚本 #

如果你的MySQL服务器禁用了事件调度器，可以写一个shell脚本，用crontab每天执行：

#!/bin/bash
MYSQL_USER="your_username"
MYSQL_PASS="your_password"
MYSQL_DB="your_database"

mysql -u$MYSQL_USER -p$MYSQL_PASS $MYSQL_DB << EOF
SET @cutoff = TO_DAYS(NOW() - INTERVAL 3 MONTH);
ALTER TABLE LargeTable REORGANIZE PARTITION p_current INTO (PARTITION p_temp VALUES LESS THAN (@cutoff), PARTITION p_current VALUES LESS THAN (TO_DAYS(NOW() + INTERVAL 1 DAY)));
ALTER TABLE LargeTable REORGANIZE PARTITION p_temp, p_history INTO (PARTITION p_history VALUES LESS THAN MAXVALUE);
EOF

然后给脚本加执行权限，添加到crontab：

chmod +x /path/to/maintain_partitions.sh
crontab -e
# 添加一行：每天凌晨1点执行
0 1 * * * /path/to/maintain_partitions.sh

4. 关键注意事项 #

• 主键调整说明：我们把DateOrder加入了主键，这是因为InnoDB的硬性要求。不过不用担心，id是自增的，所以(id, DateOrder)依然能保证唯一标识每一行数据，不影响你的业务查询。
• 锁表问题：分区重组操作会锁表，所以建议在业务低峰期执行定时任务。
• 数据验证：定期执行分区查询SQL，确保数据分区符合预期。
• 扩展建议：如果历史数据量持续增长，可以考虑把p_history再拆分为按年/月的分区，进一步提升查询性能。

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