这是一个非常经典且务实的MySQL数据治理方案。只保留近6个月热数据、将旧数据按年归档备份，在运维上被称作“滚动切分”与“冷热分离”。

具体的操作，不能简单地用DELETE（会锁表且性能极差），而应该采用“搬移-归档-清理”的标准化流程。下面是一套可直接落地的操作指南：

1. 核心策略：采用“表交换”而非“删数据”

最稳妥的方式是创建归档表，将旧数据从主表“剥离”出去，而不是逐行删除。

假设你的主表叫orders，时间字段是create_time。

第一步：创建当年（或某一年）的归档表

结构必须与主表完全一致，但可以去掉一些不必要的二级索引以提升写入速度（主键保留）。

-- 例如归档 2025 年的数据
CREATE TABLE orders_archive_2025 LIKE orders;
-- 删除归档表上的非必要索引（可选，提升插入速度）
ALTER TABLE orders_archive_2025 DROP INDEX idx_user_id, DROP INDEX idx_status;

第二步：将旧数据“搬移”至归档表（原子操作）

使用RENAME TABLE实现表交换，这是MySQL中唯一能做到“瞬间挪移”且不阻塞业务的方法。但注意，这一步要求时间分界线恰好落在某个整点或整日，且业务上允许短暂的表不存在（通常在凌晨做）。

如果你需要严格按create_time < '2026-01-01'搬移，更安全的方式是分批次插入+删除，但在MySQL 5.6+，推荐使用pt-archiver工具，如果没有，就用纯SQL事务批量搬移：

-- 1. 先将旧数据插入归档表（分批执行，每批1000条）
INSERT INTO orders_archive_2025 SELECT * FROM orders 
WHERE create_time < '2026-01-01' AND create_time >= '2025-01-01' LIMIT 1000;

-- 2. 确认插入成功后，再从主表删除这批数据（务必带相同条件）
DELETE FROM orders 
WHERE create_time < '2026-01-01' AND create_time >= '2025-01-01' LIMIT 1000;

重要：务必在create_time字段上建立索引，否则删除和查询会全表扫描，拖垮数据库。建议写脚本循环执行（如每2秒跑一次），避开业务高峰期。

---

2. 进阶方案：使用 pt-archiver（DBA首选）

如果你有Percona Toolkit，这是最安全、最优雅的方式。它会自动将数据从主库拷贝到归档库，并以小块（chunk）的形式删除，彻底避免锁表。

pt-archiver \
--source h=主库IP,D=数据库名,t=orders \
--dest h=备份库IP,D=数据库名,t=orders_archive_2025 \
--where "create_time < '2026-01-01'" \
--limit 1000 \
--txn-size 1000 \
--purge \
--progress 10000 \
--statistics

• --purge：表示从源表删除已归档的数据。

• --txn-size：每1000条提交一次事务，对业务影响极小。

---

3. 最后一步：释放物理磁盘空间（关键）

注意：即使你删除了主表里的几百万条数据，MySQL的ibd数据文件并不会自动缩小，磁盘空间不会释放给操作系统。

你需要执行表空间碎片整理：

-- 在业务低峰期执行（会锁表）
OPTIMIZE TABLE orders;

或者使用ALTER TABLE orders ENGINE=InnoDB;，效果相同。执行完后，磁盘空间才会真正空出来。

---

4. 按年备份的落地建议

你提到的“按年备份”，不建议只放在MySQL里，因为归档表在MySQL里依然占用昂贵的SSD存储。

更合理的流程是：

1. 主库：只留近6个月（表 orders）。

2. 归档库（同一台机器或另一台低配机器）：存放近3-5年的归档表（orders_archive_2024， orders_archive_2025）。

3. 对象存储/冷存储：将超过3年的数据，用 mysqldump 或 mydumper 导出为 SQL/CSV 文件，使用 gzip 压缩后（压缩率可达90%），上传至阿里云OSS或AWS S3的“归档存储”类型，成本极低。

导出冷数据的示例命令：

# 导出2024年数据并压缩
mysqldump -u用户名 -p 数据库名 orders_archive_2024 | gzip > /backup/orders_2024.sql.gz

# 确认导出无误后，删除该归档表
DROP TABLE orders_archive_2024;

5. 可能遇到的“坑”与对策

常见问题	解决方案
主从延迟	批量删除数据会产生大量binlog，导致从库延迟。建议将LIMIT设小（如500），并增加--sleep参数（如休眠1秒）。
查询必须带时间	分表后，应用代码必须改写SQL，WHERE条件必须包含 create_time，否则查询会扫描全表（因为数据只留6个月，不带时间查会扫大量空页）。
6个月零1天的数据怎么办	建议写一个定时任务（如每月1号凌晨），执行 CALL archive_procedure();，将 create_time < DATE_SUB(NOW(), INTERVAL 6 MONTH) 的数据自动搬移。
自增ID断层	归档后主表ID会断层，这不影响业务。如果使用分布式ID（雪花算法），完全没影响。

---

6、演示示例 

6.1 复制表

CREATE TABLE t_order_package_2024 LIKE t_order_package;//复制表结构并创建新表

6.2 获取数据段的最小ID，最大ID

SELECT min(id),max(id) FROM t_order_package WHERE date < '2025-01-01' AND date >= '2024-01-01';//记录最小ID，最大ID

6.3 分段复制数据

INSERT INTO t_order_package_2024 SELECT * FROM t_order_package WHERE  (id >=1 and id < 10) and (date < '2025-01-01' AND date >= '2024-01-01');//分批复制数据，大批量容易锁表

6.4 分段删除数据

DELETE FROM t_order_package WHERE id <= 2236979 LIMIT 1000;//不要大批量删除，容易锁表

6.5 释放物理磁盘空间

OPTIMIZE TABLE t_order_package;

7. 总结执行路径

1. 凌晨2点，业务低峰期。

2. 执行 pt-archiver 或脚本，将半年前的数据搬移到 orders_archive_2025。

3. 执行 OPTIMIZE TABLE orders 释放空间。

4. 将 orders_archive_2025 迁移至低配归档数据库。

5. 将2年前的归档表导出压缩，上传至云冷存储，并删除库内对应表。

如果你的数据量极大（上百亿），单纯靠脚本删除可能依然很慢，需要引入分区表（Partitioning）配合“交换分区”来瞬间剥离数据。你想了解一下基于时间分区的“秒级剥离”方案吗？我可以为你展开讲讲。