MySQL 数据归档实战指南从中小表到大表的全场景方案线上表越来越大查询变慢、备份变久、磁盘告急——归档几乎是每个跑过 3 年以上的业务都会遇到的问题。本文按数据量级、业务约束、表结构逐步展开给出可落地的归档方案并澄清常见误区DELETE 不缩表、何时需要触发器、如何做部分保留等。文章目录MySQL 数据归档实战指南从中小表到大表的全场景方案1. 归档要解决什么问题2. 先定策略四类决策2.1 归档边界什么数据算「冷」2.2 归档去向放哪里2.3 在线表是否还要保留冷数据2.4 可接受的切换窗口3. 数据量维度四档场景与选型3.1 渐进式演进推荐路径4. 方案一分批 DELETE 归档库中小表4.1 适用4.2 基本流程4.3 核心 SQL同事务、同条件4.4 持续写入时为何不需要触发器4.5 注意5. 方案二分区表 EXCHANGE / DROP PARTITION大表首选5.1 适用5.2 分区设计示例5.3 归档方式 A交换分区冷数据进归档库可查询5.4 归档方式 B直接 DROP不需再查5.5 正确性要点5.6 与持续写入6. 方案三热数据重建 双表 RENAME超大单表瘦身6.1 适用6.2 思路你描述的方案6.3 阶段一不停机复制热数据6.4 阶段二追增量 原子切换6.5 切换后6.6 与 gh-ost / pt-osc7. 方案四整表 RENAME 轮换按月一张表7.1 适用7.2 流程7.3 注意8. 部分保留同一条件下只归档一部分8.1 条件拆分8.2 实现方式8.3 不要用裸 LIMIT 表示「留一部分」8.4 对账9. 数据正确性保障体系9.1 四个维度9.2 每批门控脚本内9.3 全局收尾对账9.4 并发 UPDATE10. 迁移窗口内的增量同步何时需要触发器10.1 结论一览10.2 临时触发器示例仅迁移期10.3 「复制到 staging 后长期双存在」才需要持续同步11. 空间回收DELETE 之后还要做什么11.1 InnoDB 行为11.2 各方案的空间效果11.3 非分区表 DELETE 后的回收12. 运维配套任务表、监控、回滚12.1 任务日志表12.2 监控告警12.3 回滚思路12.4 从库策略13. 方案选型速查表14. 总结附录伪代码 — 分批归档任务1. 归档要解决什么问题归档不是简单的「把旧数据挪走」通常要同时满足目标说明在线表变小热查询只扫近期数据索引更小、缓存命中率更高冷数据可查客服、审计、纠纷仍能通过 ID / 时间查到历史可恢复归档失败可重跑误删可找回对业务影响小尽量不停写锁表窗口可控磁盘真释放逻辑删行 ≠ 文件缩小后文详述常见误区❌ 以为DELETE旧数据后在线表文件会变小❌ 表还在写就要给热表挂永久触发器做归档❌ 用裸LIMIT表示「部分归档」❌ 先删源表再插归档库2. 先定策略四类决策动手写脚本前先回答四个问题2.1 归档边界什么数据算「冷」-- 典型组合WHEREstatusIN(CLOSED,CANCELLED)ANDcreate_timecutoff-- 如 90 天前ANDkeep_flag0任务开始时固定cutoff整次任务不变优先归档业务上已封闭的数据已关单、已结算避免冷数据仍被 UPDATE2.2 归档去向放哪里去向适用归档库同结构表仍需 SQL 查询历史备份表RENAME 留下的整表表级瘦身、少查冷数据文件CSV / Parquet 对象存储极少查询、成本优先按月分表天然按时间隔离2.3 在线表是否还要保留冷数据全量迁出冷数据只存在于归档侧部分保留满足冷条件但仍留在线VIP、大额、keep_flag——见 第 8 节2.4 可接受的切换窗口窗口可选方案无停写分批 DELETE、分区 DROP、gh-ost 类在线重建秒级分钟级停写热数据重建 追增量 RENAME可维护窗口OPTIMIZE、整表导出导入3. 数据量维度四档场景与选型数据量 / 特征 推荐路径 ───────────────────────────────────────────────────────── S 500 万、非分区、日增可控 → 分批 DELETE 归档库 M 500 万5000 万、可改表结构 → 时间分区 EXCHANGE/DROP L 单表亿级、短期难分区 → 热数据重建 双表 RENAME XL 按月暴涨、整月可封闭 → 整表 RENAME 轮换3.1 渐进式演进推荐路径很多团队不是一步到位而是阶段 1S 档 — 脚本分批 DELETE 归档库快速上线 阶段 2M 档 — 在线表加分区新方案切分区归档 阶段 3L 档 — 历史膨胀后做一次 RENAME 瘦身之后走分区不必等「完美架构」才做归档先止住在线表增长再优化手段。4. 方案一分批 DELETE 归档库中小表4.1 适用表 500 万行或 DELETE 一批在可接受时间内完成尚未分区改造成本可接受需要冷数据在归档库可查4.2 基本流程定 cutoff → 分批选取 → INSERT 归档库 → 校验 → DELETE 源表 → 记日志 → 全局对账4.3 核心 SQL同事务、同条件STARTTRANSACTION;INSERTINTOarchive_db.orders_archive(id,user_id,amount,status,create_time,archive_batch_id)SELECTid,user_id,amount,status,create_time,20240818_001FROMprod_db.ordersWHEREstatusCLOSEDANDcreate_time2024-05-01 00:00:00ANDkeep_flag0ANDid1000000ANDid1010000;DELETEFROMprod_db.ordersWHEREstatusCLOSEDANDcreate_time2024-05-01 00:00:00ANDkeep_flag0ANDid1000000ANDid1010000;COMMIT;4.4 持续写入时为何不需要触发器新数据create_time cutoff→ 不在 WHERE 内不会被碰本批 INSERT 与 DELETE 条件一致同事务内 InnoDB 行锁保证一致不需要给热表挂永久触发器4.5 注意单批不宜过大建议 5k5w 行避免长事务、大 binlogDELETE 后文件未必缩小——见 第 11 节归档表对id建唯一键任务可幂等重跑5. 方案二分区表 EXCHANGE / DROP PARTITION大表首选5.1 适用按时间访问明显订单、日志、流水可接受一次 DDL 加分区或新建分区表迁移希望删冷数据 真释放空间5.2 分区设计示例CREATETABLEorders(idBIGINTNOTNULL,create_timeDATETIMENOTNULL,...PRIMARYKEY(id,create_time)-- 分区键必须进主键/唯一键)PARTITIONBYRANGE(TO_DAYS(create_time))(PARTITIONp202401VALUESLESS THAN(TO_DAYS(2024-02-01)),PARTITIONp202402VALUESLESS THAN(TO_DAYS(2024-03-01)),...PARTITIONp_futureVALUESLESS THAN MAXVALUE);5.3 归档方式 A交换分区冷数据进归档库可查询CREATETABLEorders_archive_202401LIKEorders;ALTERTABLEorders EXCHANGEPARTITIONp202401WITHTABLEorders_archive_202401;元数据级交换秒级orders_archive_202401获得整月数据在线表该分区为空5.4 归档方式 B直接 DROP不需再查ALTERTABLEordersDROPPARTITIONp202401;空间回收最彻底不可逆务必先备份或先 EXCHANGE 到归档表5.5 正确性要点只 DROP已封闭月份如 2 月 1 日后才 DROP 1 月分区EXCHANGE 前校验分区行数交换后归档表行数应一致分区表与归档表结构、索引、约束一致5.6 与持续写入当月分区持续写入历史分区只读封闭 →天然无 UPDATE 同步问题无需触发器。6. 方案三热数据重建 双表 RENAME超大单表瘦身6.1 适用单表亿级历史上未分区DELETE 无法让文件变小在线表只需保留热数据冷数据整表留存即可可接受一次短切换窗口或迁移期临时触发器6.2 思路你描述的方案1. 把「要保留的热数据」复制到 orders_temp 2. RENAME orders → orders_backup_20240818 整表变备份含全量快照 3. RENAME orders_temp → orders 新活动表仅热数据结果表内容orders新仅热数据文件小orders_backup_20240818切换时刻全量热冷冷数据在备份表中新活动表不再承载冷行。这是表级归档比 DELETE 更利于在线表物理变小。6.3 阶段一不停机复制热数据CREATETABLEorders_tempLIKEorders;-- 分批复制避免长事务INSERTINTOorders_tempSELECT*FROMordersWHEREcreate_timeDATE_SUB(NOW(),INTERVAL90DAY)ORstatusNOTIN(CLOSED,CANCELLED)ORkeep_flag1;-- 按 id 分批 sleep记录copy_start_time和已复制max_id。6.4 阶段二追增量 原子切换选项 A短暂停写推荐简单可靠-- 应用停写或只读LOCKTABLESordersWRITE;INSERTINTOorders_tempSELECT*FROMordersWHERE(create_time...ORstatusNOTIN(...)ORkeep_flag1)AND(idmax_copied_idORupdated_atcopy_start_time)ONDUPLICATEKEYUPDATEuser_idVALUES(user_id),amountVALUES(amount),statusVALUES(status),updated_atVALUES(updated_at);UNLOCKTABLES;RENAMETABLEordersTOorders_backup_20240818,orders_tempTOorders;-- 校正自增避免新 id 与备份表冲突SETnext_ai(SELECTMAX(id)1FROMorders_backup_20240818);SETsqlCONCAT(ALTER TABLE orders AUTO_INCREMENT ,next_ai);PREPAREstmtFROMsql;EXECUTEstmt;DEALLOCATEPREPAREstmt;选项 B迁移期临时触发器不能停写时在阶段一复制期间对orders挂临时触发器把 INSERT/UPDATE/DELETE 同步到orders_temp切换前校验一致、删除触发器、再 RENAME。详见 第 10 节。6.5 切换后不需要永久触发器——只有一个活动表ordersorders_backup_*可迁归档库、压缩存储或确认无查询后 DROP备份表里热数据有冗余副本全量快照属正常6.6 与 gh-ost / pt-osc在线改表工具本质也是建新表 → 复制 追增量触发器或 binlog→ RENAME 切换。本方案是同一模式的手动版。7. 方案四整表 RENAME 轮换按月一张表7.1 适用业务天然按月分表或表名带月份orders_202401每月整表「下线」无部分行归档7.2 流程-- 月初新表已建好 orders_202402RENAMETABLEorders_202401TOorders_archive_202401,orders_202402TOorders;-- 或应用改指向新表名元数据操作极快旧表整表保留或 DROP7.3 注意应用路由或表名策略要统一跨月查询需扫多表或汇总视图8. 部分保留同一条件下只归档一部分定义满足冷数据条件如 90 天前且已关单的行里只搬走一部分其余继续留在线表。8.1 条件拆分归档集合 A statusCLOSED AND create_time cutoff 实际搬走 B A AND NOT 保留规则 R 仍留在线 A AND RWHEREstatusCLOSEDANDcreate_timecutoffANDkeep_flag0ANDuser_idNOTIN(SELECTuser_idFROMvip_users)-- 示例ANDamount1000008.2 实现方式方式说明keep_flag关单时按 VIP/金额写入脚本只动keep_flag0白名单表NOT EXISTS (archive_keep_orders)稳定比例MOD(CRC32(CONCAT(id,salt)),100) 80约 80% 归档salt 固定8.3 不要用裸 LIMIT 表示「留一部分」-- ❌ 危险每批删哪些行不确定无法对账重跑DELETEFROMordersWHERE...LIMIT5000;限量应用主键游标id last_id ORDER BY id LIMIT N保留语义仍由keep_flag等表达。8.4 对账-- 应归档且未保留任务结束后源表应为 0SELECTCOUNT(*)FROMordersWHEREstatusCLOSEDANDcreate_timecutoffANDkeep_flag0;-- 应保留仍在源表SELECTCOUNT(*)FROMordersWHEREstatusCLOSEDANDcreate_timecutoffANDkeep_flag1;-- 主键无交集SELECTCOUNT(*)FROMorders oINNERJOINorders_archive aONo.ida.id;9. 数据正确性保障体系9.1 四个维度维度手段不丢先 INSERT 归档再 DELETE禁止先删后插不重归档表id唯一幂等INSERT IGNORE/ 按 batch 校验不错行数 校验和COUNT/SUM或关键字段 CRC可恢复archive_job_log 备份表/分区可回导9.2 每批门控脚本内1. 计算本批 source_count、checksum_source 2. INSERT 归档 3. 计算 archive_count、checksum_archive 4. 不一致 → ROLLBACK / 不 DELETE、告警 5. 一致 → DELETE 或 COMMIT 6. 写入 job_log9.3 全局收尾对账源表应归档条件行数 0归档表同条件行数 历史累计源表与归档表主键交集 0子表订单明细按order_id同步归档或对账9.4 并发 UPDATE场景处理分批 DELETE 归档同事务 同 WHERE 可选FOR UPDATE热数据 RENAME 切换仅迁移窗口追增量切换后单表分区封闭后归档历史分区无写无问题冷数据业务禁止修改最强约束优先采用10. 迁移窗口内的增量同步何时需要触发器10.1 结论一览阶段是否需要触发器分批 INSERT 归档 DELETE 源表否分区 EXCHANGE / DROP否RENAME 切换完成之后否热数据复制到orders_temp期间长耗时视情况停写追增量优先不能停写可用临时触发器或 gh-ost10.2 临时触发器示例仅迁移期CREATETRIGGERtr_orders_ins_syncAFTERINSERTONordersFOR EACH ROWBEGINIFNEW.create_timeDATE_SUB(NOW(),INTERVAL90DAY)ORNEW.statusNOTIN(CLOSED,CANCELLED)ORNEW.keep_flag1THENINSERTINTOorders_tempVALUES(...);ENDIF;END;-- UPDATE / DELETE 同理切换前 DROP TRIGGER再 RENAME缺点热表每笔写放大与批量复制叠加时负载高。替代短停写 ON DUPLICATE KEY UPDATE追增量或 binlog CDC长期双写镜像场景。10.3 「复制到 staging 后长期双存在」才需要持续同步若流程是「复制 → RENAME staging →很久以后才删源表」则存在双份且源表仍 UPDATE——这是镜像问题应用 CDC 优于永久触发器。推荐改流程复制 → 校验 → 删源或整表 RENAME 一切换→ 结束双存在。11. 空间回收DELETE 之后还要做什么11.1 InnoDB 行为DELETE逻辑删除表文件通常不缩小空洞可给同表 INSERT 复用还给操作系统需DROP 分区/表或重建表11.2 各方案的空间效果方案在线表文件分批 DELETE往往不变需 OPTIMIZE / 换表DROP PARTITION明显缩小热数据 RENAME 换新表新表小文件整表 RENAME 轮换在线表始终新文件11.3 非分区表 DELETE 后的回收-- 低峰执行大表会锁表或耗时很长OPTIMIZETABLEorders;-- 或 ALTER TABLE orders ENGINEInnoDB;大表可用 gh-ost 做在线重建。分区表优先DROP PARTITION避免全表 OPTIMIZE。12. 运维配套任务表、监控、回滚12.1 任务日志表CREATETABLEarchive_job_log(idBIGINTAUTO_INCREMENTPRIMARYKEY,job_nameVARCHAR(64)NOTNULL,batch_idVARCHAR(32)NOTNULL,cutoff_timeDATETIMENOTNULL,id_range_startBIGINT,id_range_endBIGINT,source_countINT,archive_countINT,checksum_sourceVARCHAR(64),checksum_archiveVARCHAR(64),statusENUM(running,success,failed)NOTNULL,error_msgTEXT,started_atDATETIMENOTNULL,finished_atDATETIME,UNIQUEKEYuk_batch(job_name,batch_id));12.2 监控告警单批source_count ! archive_count全局对账源表遗漏、主键交集 0任务超时、连续 0 行条件错误主从延迟超阈值时暂停 DELETE12.3 回滚思路方案回滚归档库 DELETE从归档表 INSERT 回源表注意幂等EXCHANGE再 EXCHANGE 回去RENAME 切换再 RENAME 换回需未对新表写入或先冻结DROP PARTITION不可回滚必须先备份或 EXCHANGE 到归档表12.4 从库策略读压力大的校验可在从库做COUNT/CHECKSUMDELETE 仍在主库执行关注 binlog 与复制延迟13. 方案选型速查表场景数据量推荐方案停写空间回收触发器历史可查、表不大 500 万分批 DELETE 归档库否需 OPTIMIZE否时间明显、可分区500 万EXCHANGE / DROP PARTITION否好否亿级单表、DELETE 不缩表亿级热数据 RENAME 切换短窗口很好仅迁移期可选按月整表封闭任意整表 RENAME 轮换否很好否冷数据极少查大DROP PARTITION 或导出 OSS否最好否部分保留VIP 等任意在上述方案上加 keep 条件同左同左同左14. 总结归档边界先于脚本cutoff、状态、keep_flag任务内固定 cutoff。按数据量选型中小表分批搬迁大表分区超大单表热数据 RENAME按月整表轮换。正确性靠同事务同条件、每批门控、全局对账、幂等设计而不是永久触发器。DELETE 不缩表要真释放空间用 DROP PARTITION、新表 RENAME 或 OPTIMIZE。热数据重建 双表 RENAME是亿级单表瘦身的利器触发器只用于迁移窗口追增量切换完即删。部分保留用keep_flag/ 白名单表达不用裸 LIMIT。归档没有银弹但路径清晰先止住在线表膨胀再按量级升级到分区或 RENAME。把边界、校验、日志做扎实比追求一次性完美架构更重要。附录伪代码 — 分批归档任务defarchive_orders(cutoff:str,batch_size:int5000):ifjob_already_running(orders_archive):returnlast_id0batch_no0whileTrue:batch_no1batch_idf{date.today()}_{batch_no:03d}withdb.transaction():rowsdb.query(SELECT id, ... FROM orders WHERE ... AND id %s ORDER BY id LIMIT %s,(last_id,batch_size))ifnotrows:breakids[r.idforrinrows]checksum_srcchecksum(rows)db.execute(INSERT INTO archive.orders_archive ...,rows)checksum_arcdb.query_one(SELECT COUNT(*), SUM(...) FROM archive.orders_archive WHERE batch_id %s,(batch_id,))ifnotverify(len(rows),checksum_src,checksum_arc):raiseArchiveError(batch verify failed)db.execute(DELETE FROM orders WHERE id IN (%s),(ids,))log_success(batch_id,len(rows),checksum_src)last_idmax(ids)sleep(0.1)# 降低主库压力reconcile_global(cutoff) MySQL 数据归档实战指南：从中小表到大表的全场景方案
MySQL 数据归档实战指南从中小表到大表的全场景方案线上表越来越大查询变慢、备份变久、磁盘告急——归档几乎是每个跑过 3 年以上的业务都会遇到的问题。本文按数据量级、业务约束、表结构逐步展开给出可落地的归档方案并澄清常见误区DELETE 不缩表、何时需要触发器、如何做部分保留等。文章目录MySQL 数据归档实战指南从中小表到大表的全场景方案1. 归档要解决什么问题2. 先定策略四类决策2.1 归档边界什么数据算「冷」2.2 归档去向放哪里2.3 在线表是否还要保留冷数据2.4 可接受的切换窗口3. 数据量维度四档场景与选型3.1 渐进式演进推荐路径4. 方案一分批 DELETE 归档库中小表4.1 适用4.2 基本流程4.3 核心 SQL同事务、同条件4.4 持续写入时为何不需要触发器4.5 注意5. 方案二分区表 EXCHANGE / DROP PARTITION大表首选5.1 适用5.2 分区设计示例5.3 归档方式 A交换分区冷数据进归档库可查询5.4 归档方式 B直接 DROP不需再查5.5 正确性要点5.6 与持续写入6. 方案三热数据重建 双表 RENAME超大单表瘦身6.1 适用6.2 思路你描述的方案6.3 阶段一不停机复制热数据6.4 阶段二追增量 原子切换6.5 切换后6.6 与 gh-ost / pt-osc7. 方案四整表 RENAME 轮换按月一张表7.1 适用7.2 流程7.3 注意8. 部分保留同一条件下只归档一部分8.1 条件拆分8.2 实现方式8.3 不要用裸 LIMIT 表示「留一部分」8.4 对账9. 数据正确性保障体系9.1 四个维度9.2 每批门控脚本内9.3 全局收尾对账9.4 并发 UPDATE10. 迁移窗口内的增量同步何时需要触发器10.1 结论一览10.2 临时触发器示例仅迁移期10.3 「复制到 staging 后长期双存在」才需要持续同步11. 空间回收DELETE 之后还要做什么11.1 InnoDB 行为11.2 各方案的空间效果11.3 非分区表 DELETE 后的回收12. 运维配套任务表、监控、回滚12.1 任务日志表12.2 监控告警12.3 回滚思路12.4 从库策略13. 方案选型速查表14. 总结附录伪代码 — 分批归档任务1. 归档要解决什么问题归档不是简单的「把旧数据挪走」通常要同时满足目标说明在线表变小热查询只扫近期数据索引更小、缓存命中率更高冷数据可查客服、审计、纠纷仍能通过 ID / 时间查到历史可恢复归档失败可重跑误删可找回对业务影响小尽量不停写锁表窗口可控磁盘真释放逻辑删行 ≠ 文件缩小后文详述常见误区❌ 以为DELETE旧数据后在线表文件会变小❌ 表还在写就要给热表挂永久触发器做归档❌ 用裸LIMIT表示「部分归档」❌ 先删源表再插归档库2. 先定策略四类决策动手写脚本前先回答四个问题2.1 归档边界什么数据算「冷」-- 典型组合WHEREstatusIN(CLOSED,CANCELLED)ANDcreate_timecutoff-- 如 90 天前ANDkeep_flag0任务开始时固定cutoff整次任务不变优先归档业务上已封闭的数据已关单、已结算避免冷数据仍被 UPDATE2.2 归档去向放哪里去向适用归档库同结构表仍需 SQL 查询历史备份表RENAME 留下的整表表级瘦身、少查冷数据文件CSV / Parquet 对象存储极少查询、成本优先按月分表天然按时间隔离2.3 在线表是否还要保留冷数据全量迁出冷数据只存在于归档侧部分保留满足冷条件但仍留在线VIP、大额、keep_flag——见 第 8 节2.4 可接受的切换窗口窗口可选方案无停写分批 DELETE、分区 DROP、gh-ost 类在线重建秒级分钟级停写热数据重建 追增量 RENAME可维护窗口OPTIMIZE、整表导出导入3. 数据量维度四档场景与选型数据量 / 特征 推荐路径 ───────────────────────────────────────────────────────── S 500 万、非分区、日增可控 → 分批 DELETE 归档库 M 500 万5000 万、可改表结构 → 时间分区 EXCHANGE/DROP L 单表亿级、短期难分区 → 热数据重建 双表 RENAME XL 按月暴涨、整月可封闭 → 整表 RENAME 轮换3.1 渐进式演进推荐路径很多团队不是一步到位而是阶段 1S 档 — 脚本分批 DELETE 归档库快速上线 阶段 2M 档 — 在线表加分区新方案切分区归档 阶段 3L 档 — 历史膨胀后做一次 RENAME 瘦身之后走分区不必等「完美架构」才做归档先止住在线表增长再优化手段。4. 方案一分批 DELETE 归档库中小表4.1 适用表 500 万行或 DELETE 一批在可接受时间内完成尚未分区改造成本可接受需要冷数据在归档库可查4.2 基本流程定 cutoff → 分批选取 → INSERT 归档库 → 校验 → DELETE 源表 → 记日志 → 全局对账4.3 核心 SQL同事务、同条件STARTTRANSACTION;INSERTINTOarchive_db.orders_archive(id,user_id,amount,status,create_time,archive_batch_id)SELECTid,user_id,amount,status,create_time,20240818_001FROMprod_db.ordersWHEREstatusCLOSEDANDcreate_time2024-05-01 00:00:00ANDkeep_flag0ANDid1000000ANDid1010000;DELETEFROMprod_db.ordersWHEREstatusCLOSEDANDcreate_time2024-05-01 00:00:00ANDkeep_flag0ANDid1000000ANDid1010000;COMMIT;4.4 持续写入时为何不需要触发器新数据create_time cutoff→ 不在 WHERE 内不会被碰本批 INSERT 与 DELETE 条件一致同事务内 InnoDB 行锁保证一致不需要给热表挂永久触发器4.5 注意单批不宜过大建议 5k5w 行避免长事务、大 binlogDELETE 后文件未必缩小——见 第 11 节归档表对id建唯一键任务可幂等重跑5. 方案二分区表 EXCHANGE / DROP PARTITION大表首选5.1 适用按时间访问明显订单、日志、流水可接受一次 DDL 加分区或新建分区表迁移希望删冷数据 真释放空间5.2 分区设计示例CREATETABLEorders(idBIGINTNOTNULL,create_timeDATETIMENOTNULL,...PRIMARYKEY(id,create_time)-- 分区键必须进主键/唯一键)PARTITIONBYRANGE(TO_DAYS(create_time))(PARTITIONp202401VALUESLESS THAN(TO_DAYS(2024-02-01)),PARTITIONp202402VALUESLESS THAN(TO_DAYS(2024-03-01)),...PARTITIONp_futureVALUESLESS THAN MAXVALUE);5.3 归档方式 A交换分区冷数据进归档库可查询CREATETABLEorders_archive_202401LIKEorders;ALTERTABLEorders EXCHANGEPARTITIONp202401WITHTABLEorders_archive_202401;元数据级交换秒级orders_archive_202401获得整月数据在线表该分区为空5.4 归档方式 B直接 DROP不需再查ALTERTABLEordersDROPPARTITIONp202401;空间回收最彻底不可逆务必先备份或先 EXCHANGE 到归档表5.5 正确性要点只 DROP已封闭月份如 2 月 1 日后才 DROP 1 月分区EXCHANGE 前校验分区行数交换后归档表行数应一致分区表与归档表结构、索引、约束一致5.6 与持续写入当月分区持续写入历史分区只读封闭 →天然无 UPDATE 同步问题无需触发器。6. 方案三热数据重建 双表 RENAME超大单表瘦身6.1 适用单表亿级历史上未分区DELETE 无法让文件变小在线表只需保留热数据冷数据整表留存即可可接受一次短切换窗口或迁移期临时触发器6.2 思路你描述的方案1. 把「要保留的热数据」复制到 orders_temp 2. RENAME orders → orders_backup_20240818 整表变备份含全量快照 3. RENAME orders_temp → orders 新活动表仅热数据结果表内容orders新仅热数据文件小orders_backup_20240818切换时刻全量热冷冷数据在备份表中新活动表不再承载冷行。这是表级归档比 DELETE 更利于在线表物理变小。6.3 阶段一不停机复制热数据CREATETABLEorders_tempLIKEorders;-- 分批复制避免长事务INSERTINTOorders_tempSELECT*FROMordersWHEREcreate_timeDATE_SUB(NOW(),INTERVAL90DAY)ORstatusNOTIN(CLOSED,CANCELLED)ORkeep_flag1;-- 按 id 分批 sleep记录copy_start_time和已复制max_id。6.4 阶段二追增量 原子切换选项 A短暂停写推荐简单可靠-- 应用停写或只读LOCKTABLESordersWRITE;INSERTINTOorders_tempSELECT*FROMordersWHERE(create_time...ORstatusNOTIN(...)ORkeep_flag1)AND(idmax_copied_idORupdated_atcopy_start_time)ONDUPLICATEKEYUPDATEuser_idVALUES(user_id),amountVALUES(amount),statusVALUES(status),updated_atVALUES(updated_at);UNLOCKTABLES;RENAMETABLEordersTOorders_backup_20240818,orders_tempTOorders;-- 校正自增避免新 id 与备份表冲突SETnext_ai(SELECTMAX(id)1FROMorders_backup_20240818);SETsqlCONCAT(ALTER TABLE orders AUTO_INCREMENT ,next_ai);PREPAREstmtFROMsql;EXECUTEstmt;DEALLOCATEPREPAREstmt;选项 B迁移期临时触发器不能停写时在阶段一复制期间对orders挂临时触发器把 INSERT/UPDATE/DELETE 同步到orders_temp切换前校验一致、删除触发器、再 RENAME。详见 第 10 节。6.5 切换后不需要永久触发器——只有一个活动表ordersorders_backup_*可迁归档库、压缩存储或确认无查询后 DROP备份表里热数据有冗余副本全量快照属正常6.6 与 gh-ost / pt-osc在线改表工具本质也是建新表 → 复制 追增量触发器或 binlog→ RENAME 切换。本方案是同一模式的手动版。7. 方案四整表 RENAME 轮换按月一张表7.1 适用业务天然按月分表或表名带月份orders_202401每月整表「下线」无部分行归档7.2 流程-- 月初新表已建好 orders_202402RENAMETABLEorders_202401TOorders_archive_202401,orders_202402TOorders;-- 或应用改指向新表名元数据操作极快旧表整表保留或 DROP7.3 注意应用路由或表名策略要统一跨月查询需扫多表或汇总视图8. 部分保留同一条件下只归档一部分定义满足冷数据条件如 90 天前且已关单的行里只搬走一部分其余继续留在线表。8.1 条件拆分归档集合 A statusCLOSED AND create_time cutoff 实际搬走 B A AND NOT 保留规则 R 仍留在线 A AND RWHEREstatusCLOSEDANDcreate_timecutoffANDkeep_flag0ANDuser_idNOTIN(SELECTuser_idFROMvip_users)-- 示例ANDamount1000008.2 实现方式方式说明keep_flag关单时按 VIP/金额写入脚本只动keep_flag0白名单表NOT EXISTS (archive_keep_orders)稳定比例MOD(CRC32(CONCAT(id,salt)),100) 80约 80% 归档salt 固定8.3 不要用裸 LIMIT 表示「留一部分」-- ❌ 危险每批删哪些行不确定无法对账重跑DELETEFROMordersWHERE...LIMIT5000;限量应用主键游标id last_id ORDER BY id LIMIT N保留语义仍由keep_flag等表达。8.4 对账-- 应归档且未保留任务结束后源表应为 0SELECTCOUNT(*)FROMordersWHEREstatusCLOSEDANDcreate_timecutoffANDkeep_flag0;-- 应保留仍在源表SELECTCOUNT(*)FROMordersWHEREstatusCLOSEDANDcreate_timecutoffANDkeep_flag1;-- 主键无交集SELECTCOUNT(*)FROMorders oINNERJOINorders_archive aONo.ida.id;9. 数据正确性保障体系9.1 四个维度维度手段不丢先 INSERT 归档再 DELETE禁止先删后插不重归档表id唯一幂等INSERT IGNORE/ 按 batch 校验不错行数 校验和COUNT/SUM或关键字段 CRC可恢复archive_job_log 备份表/分区可回导9.2 每批门控脚本内1. 计算本批 source_count、checksum_source 2. INSERT 归档 3. 计算 archive_count、checksum_archive 4. 不一致 → ROLLBACK / 不 DELETE、告警 5. 一致 → DELETE 或 COMMIT 6. 写入 job_log9.3 全局收尾对账源表应归档条件行数 0归档表同条件行数 历史累计源表与归档表主键交集 0子表订单明细按order_id同步归档或对账9.4 并发 UPDATE场景处理分批 DELETE 归档同事务 同 WHERE 可选FOR UPDATE热数据 RENAME 切换仅迁移窗口追增量切换后单表分区封闭后归档历史分区无写无问题冷数据业务禁止修改最强约束优先采用10. 迁移窗口内的增量同步何时需要触发器10.1 结论一览阶段是否需要触发器分批 INSERT 归档 DELETE 源表否分区 EXCHANGE / DROP否RENAME 切换完成之后否热数据复制到orders_temp期间长耗时视情况停写追增量优先不能停写可用临时触发器或 gh-ost10.2 临时触发器示例仅迁移期CREATETRIGGERtr_orders_ins_syncAFTERINSERTONordersFOR EACH ROWBEGINIFNEW.create_timeDATE_SUB(NOW(),INTERVAL90DAY)ORNEW.statusNOTIN(CLOSED,CANCELLED)ORNEW.keep_flag1THENINSERTINTOorders_tempVALUES(...);ENDIF;END;-- UPDATE / DELETE 同理切换前 DROP TRIGGER再 RENAME缺点热表每笔写放大与批量复制叠加时负载高。替代短停写 ON DUPLICATE KEY UPDATE追增量或 binlog CDC长期双写镜像场景。10.3 「复制到 staging 后长期双存在」才需要持续同步若流程是「复制 → RENAME staging →很久以后才删源表」则存在双份且源表仍 UPDATE——这是镜像问题应用 CDC 优于永久触发器。推荐改流程复制 → 校验 → 删源或整表 RENAME 一切换→ 结束双存在。11. 空间回收DELETE 之后还要做什么11.1 InnoDB 行为DELETE逻辑删除表文件通常不缩小空洞可给同表 INSERT 复用还给操作系统需DROP 分区/表或重建表11.2 各方案的空间效果方案在线表文件分批 DELETE往往不变需 OPTIMIZE / 换表DROP PARTITION明显缩小热数据 RENAME 换新表新表小文件整表 RENAME 轮换在线表始终新文件11.3 非分区表 DELETE 后的回收-- 低峰执行大表会锁表或耗时很长OPTIMIZETABLEorders;-- 或 ALTER TABLE orders ENGINEInnoDB;大表可用 gh-ost 做在线重建。分区表优先DROP PARTITION避免全表 OPTIMIZE。12. 运维配套任务表、监控、回滚12.1 任务日志表CREATETABLEarchive_job_log(idBIGINTAUTO_INCREMENTPRIMARYKEY,job_nameVARCHAR(64)NOTNULL,batch_idVARCHAR(32)NOTNULL,cutoff_timeDATETIMENOTNULL,id_range_startBIGINT,id_range_endBIGINT,source_countINT,archive_countINT,checksum_sourceVARCHAR(64),checksum_archiveVARCHAR(64),statusENUM(running,success,failed)NOTNULL,error_msgTEXT,started_atDATETIMENOTNULL,finished_atDATETIME,UNIQUEKEYuk_batch(job_name,batch_id));12.2 监控告警单批source_count ! archive_count全局对账源表遗漏、主键交集 0任务超时、连续 0 行条件错误主从延迟超阈值时暂停 DELETE12.3 回滚思路方案回滚归档库 DELETE从归档表 INSERT 回源表注意幂等EXCHANGE再 EXCHANGE 回去RENAME 切换再 RENAME 换回需未对新表写入或先冻结DROP PARTITION不可回滚必须先备份或 EXCHANGE 到归档表12.4 从库策略读压力大的校验可在从库做COUNT/CHECKSUMDELETE 仍在主库执行关注 binlog 与复制延迟13. 方案选型速查表场景数据量推荐方案停写空间回收触发器历史可查、表不大 500 万分批 DELETE 归档库否需 OPTIMIZE否时间明显、可分区500 万EXCHANGE / DROP PARTITION否好否亿级单表、DELETE 不缩表亿级热数据 RENAME 切换短窗口很好仅迁移期可选按月整表封闭任意整表 RENAME 轮换否很好否冷数据极少查大DROP PARTITION 或导出 OSS否最好否部分保留VIP 等任意在上述方案上加 keep 条件同左同左同左14. 总结归档边界先于脚本cutoff、状态、keep_flag任务内固定 cutoff。按数据量选型中小表分批搬迁大表分区超大单表热数据 RENAME按月整表轮换。正确性靠同事务同条件、每批门控、全局对账、幂等设计而不是永久触发器。DELETE 不缩表要真释放空间用 DROP PARTITION、新表 RENAME 或 OPTIMIZE。热数据重建 双表 RENAME是亿级单表瘦身的利器触发器只用于迁移窗口追增量切换完即删。部分保留用keep_flag/ 白名单表达不用裸 LIMIT。归档没有银弹但路径清晰先止住在线表膨胀再按量级升级到分区或 RENAME。把边界、校验、日志做扎实比追求一次性完美架构更重要。附录伪代码 — 分批归档任务defarchive_orders(cutoff:str,batch_size:int5000):ifjob_already_running(orders_archive):returnlast_id0batch_no0whileTrue:batch_no1batch_idf{date.today()}_{batch_no:03d}withdb.transaction():rowsdb.query(SELECT id, ... FROM orders WHERE ... AND id %s ORDER BY id LIMIT %s,(last_id,batch_size))ifnotrows:breakids[r.idforrinrows]checksum_srcchecksum(rows)db.execute(INSERT INTO archive.orders_archive ...,rows)checksum_arcdb.query_one(SELECT COUNT(*), SUM(...) FROM archive.orders_archive WHERE batch_id %s,(batch_id,))ifnotverify(len(rows),checksum_src,checksum_arc):raiseArchiveError(batch verify failed)db.execute(DELETE FROM orders WHERE id IN (%s),(ids,))log_success(batch_id,len(rows),checksum_src)last_idmax(ids)sleep(0.1)# 降低主库压力reconcile_global(cutoff)