MySQL 分区表怎么按日期清理历史数据

MySQL 分区表按日期清理历史数据，关键不是每天执行一条大范围 DELETE，而是让“保留周期”与分区边界完全对齐。对按月留存的订单、日志或事件表，通常使用 RANGE COLUMNS (created_at)，每个月一个分区；确认某个月已经越过保留线后，直接 ALTER TABLE ... DROP PARTITION。这样删除的是完整分区，不需要逐行扫描，但分区内的数据也会随之被永久丢弃。

用 RANGE COLUMNS 把日期边界定义成完整月份

如果清理单位是月份，就不要用一个笼统的年份分区来承载精确的月度保留。RANGE COLUMNS 可以直接使用 DATE 或 DATETIME 列，分区上界按时间顺序排列。下面的示例把上界写成下个月第一天，实际范围是左闭右开：[2024-01-01, 2024-02-01)。

CREATE TABLE access_event (
    id BIGINT NOT NULL,
    created_at DATETIME NOT NULL,
    payload JSON NOT NULL,
    PRIMARY KEY (id, created_at)
)
PARTITION BY RANGE COLUMNS (created_at) (
    -- 每个分区只负责一个自然月，便于按月淘汰
    PARTITION p202401 VALUES LESS THAN ('2024-02-01'),
    PARTITION p202402 VALUES LESS THAN ('2024-03-01'),
    PARTITION p202403 VALUES LESS THAN ('2024-04-01'),
    -- 给未来数据留一个兜底边界，不能把它当作普通月份删除
    PARTITION pmax VALUES LESS THAN (MAXVALUE)
);

分区键还必须满足表的主键和唯一键约束：示例把 created_at 放进主键，避免建表时遇到分区键未覆盖唯一键的限制。查询按时间过滤时，优化器可以通过分区裁剪减少需要检查的分区，但分区本身不是索引替代品。

用元数据确认待清理分区覆盖的日期

自动任务不要根据分区名猜日期，更不要直接拼接一个未经核对的分区名。先看表结构，再从元数据中确认分区的上界。PARTITION_DESCRIPTION 对范围分区保存的是上界描述，TABLE_ROWS 是估算值，适合做容量观察，不应被当成精确计数。

SELECT
    PARTITION_NAME,
    PARTITION_DESCRIPTION,
    TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'access_event'
  AND PARTITION_NAME IS NOT NULL
ORDER BY PARTITION_ORDINAL_POSITION;

-- 清理前只检查已越过保留线的整月分区
SELECT PARTITION_NAME, PARTITION_DESCRIPTION
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'access_event'
  AND PARTITION_NAME = 'p202401'
  AND PARTITION_DESCRIPTION 

生产环境里，检查结果应该被记录到运维日志或审批单：表名、分区名、保留截止日、操作者和备份位置都要能对应上。若分区里混入了迟到数据，先判断它是否仍在业务保留范围内；不要因为“月份已经旧了”就跳过业务确认。

用 DROP PARTITION 一次清理完整历史月份

确认 p202401 已完全越过保留线后，可以执行：

ALTER TABLE access_event
    DROP PARTITION p202401;
-- DROP PARTITION 会同时丢弃该分区中的数据，执行前必须完成备份或审批

这是分区级删除，不等价于带条件的行级 DELETE。MySQL 文档明确说明，DROP PARTITION 可用于 RANGE 或 LIST 分区，不能用于 HASH 或 KEY 分区，而且语句不支持 IF EXISTS。因此脚本应先查询分区是否存在，并把“已不存在”作为可识别的幂等结果处理，而不是把名字直接写死后无限重试。

用新分区和 MAXVALUE 处理滚动保留

滚动保留不只包含“删旧分区”，还要保证下一个月份有位置可写。若表已经有 pmax，不能直接在它后面 ADD PARTITION；应先把 pmax 拆成新月份和新的兜底分区：

ALTER TABLE access_event
    REORGANIZE PARTITION pmax INTO (
        -- 新月份接住边界内的数据
        PARTITION p202404 VALUES LESS THAN ('2024-05-01'),
        -- 继续保留未来数据的兜底范围
        PARTITION pmax VALUES LESS THAN (MAXVALUE)
    );

建议把“创建下月分区”和“清理过期分区”拆成两个有明确顺序的任务：先保证未来写入有合法落点，再执行删除；失败时保留元数据和实际执行结果，避免调度器重复发出同一条 DDL。上线前用低流量窗口、备份策略和权限核对保护生产表，尤其要确认回收数据是否还承担审计或对账职责。

常见问题

按日期删除历史数据一定要分区吗？

不一定。数据量小或删除条件零散时，普通 DELETE 更直接；只有当数据天然按时间批次到达、删除单位也是完整时间片时，分区级清理才更有价值。

DROP PARTITION 后还能恢复吗？

不能把它当作可回滚的普通删除。应依赖执行前的备份、归档或可验证的导出；如果数据还可能被审计使用，先归档再淘汰。

为什么分区表仍然查询很慢？

分区裁剪只减少要访问的分区，不能替代合适索引。检查查询是否对分区键使用了可裁剪的范围条件，并分别评估索引和单分区内的数据量。