冷热数据分层与归档

访问热度（典型幂律）：
  最近 7 天数据  →  80%+ 的查询流量
  最近 90 天数据 →  15% 的查询流量
  一年前数据     →  5% 的查询流量（多为审计/合规）

存储成本（SSD vs 归档）：
  热层 SSD   ：每 GB 成本高、性能好
  冷层归档/对象存储：每 GB 成本可低一个数量级

-- 用慢日志/审计日志统计：不同时间窗数据的查询占比
-- 例如统计表 orders 中，按 created_at 时间窗的查询命中分布
SELECT DATE_FORMAT(created_at, '%Y-%m') AS month,
       COUNT(*) AS row_count
FROM orders
WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY)
GROUP BY month ORDER BY month;

识别口诀：
  1. 以业务保留期为准（合规要求 3 年，就必须留 3 年）
  2. 以访问统计为准（连续 90 天无查询，进冷层）
  3. 以运维窗口为准（每晚 2 点做归档任务）

冷层该「牺牲什么」：
  ✅ 牺牲随机读性能（老数据访问极少）
  ✅ 牺牲更新能力（冷数据只读）
  ❌ 不能牺牲：可检索、可恢复、可审计

归档三个目标：
  1. 热表瘦身：表变小 → 索引小、锁少、查询快
  2. 成本控制：冷数据进低成本存储
  3. 合规保留：老数据仍可查询/审计

归档五个原则：
  □ 可回溯：归档数据必须可查询、可校验
  □ 可逆：迁移出错的冷数据能回退到热层
  □ 定时：归档任务可编排、可重试、可监控
  □ 无感：归档过程不阻塞线上读写
  □ 一致：归档后主键/约束/统计信息不丢

一个典型策略：
  订单表 orders
    热层保留：最近 90 天（SSD，主库）
    冷层保留：90 天 ~ 5 年（历史库 orders_archive）
    过期清理：超过 5 年 → 导出到对象存储 → 删除

-- 按天分区的订单表
CREATE TABLE orders (
  id BIGINT,
  created_at DATETIME,
  amount DECIMAL(10,2),
  PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (TO_DAYS(created_at)) (
  PARTITION p20260629 VALUES LESS THAN (TO_DAYS('2026-06-30')),
  PARTITION p20260630 VALUES LESS THAN (TO_DAYS('2026-07-01')),
  PARTITION pFuture  VALUES LESS THAN MAXVALUE
);

-- 归档 30 天前的分区：先交换到归档表，再 DROP
ALTER TABLE orders EXCHANGE PARTITION p20260530 WITH TABLE orders_archive;
ALTER TABLE orders DROP PARTITION p20260530;

对比：
  DELETE 1000 万行老数据   → 分钟级，产生大量 redo/binlog
  DROP 一个分区            → 秒级，直接释放数据文件
  所以：能用分区，就绝不用 DELETE 归档。

每日归档流水线：
  1. 识别到期分区（created_at < 今天 - 90 天）
  2. EXCHANGE 到归档表（同库，结构一致）
  3. 校验归档表行数 = 原分区行数
  4. DROP 原分区，回收热层空间
  5. 归档表导出 → 历史库/对象存储 → 本地归档表清空

-- 分批归档：每次取 1 万条老数据搬到归档表
INSERT INTO orders_archive (id, created_at, amount)
SELECT id, created_at, amount FROM orders
WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY)
LIMIT 10000;

-- 确认归档成功后，分批删除原表（加 LIMIT 防大锁）
DELETE FROM orders
WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY)
LIMIT 10000;

分批规则：
  □ 每批 5000~10000 行，批间 sleep 0.1~1s
  □ 跑在业务低峰期，观察主从延迟与磁盘 IO
  □ 用主键排序保证分批可断点续跑

推荐演进：
  初期（量小）  → 同库冷表，SQL 无感
  中期（量大）  → 独立历史库，应用层路由
  远期（海量）  → 对象存储 + 数仓，走分析链路

统一查询入口 QueryRouter：
  SELECT ... FROM orders WHERE created_at >= 90 天内
    → 路由到热表 orders
  SELECT ... FROM orders WHERE created_at < 90 天前
    → 路由到历史库 orders_archive
  SELECT ... 不按时间过滤
    → 先热表，miss 再查冷层（或提示改查历史）

# 应用层按时间路由（伪代码）
def query_orders(start, end):
    if start >= ARCHIVE_CUTOFF:          # 全热窗
        return query_hot(start, end)
    if end < ARCHIVE_CUTOFF:             # 全冷窗
        return query_archive(start, end)
    hot = query_hot(start, ARCHIVE_CUTOFF)     # 跨界：两段都查
    old = query_archive(ARCHIVE_CUTOFF, end)
    return merge(hot, old)

-- 1. 行数校验：归档前后行数必须一致
SELECT COUNT(*) FROM orders
WHERE created_at < '2026-06-30';           -- 迁移前
SELECT COUNT(*) FROM orders_archive;        -- 迁移后（应相等）

-- 2. 主键校验：两边主键集合一致，无缺失无重复
SELECT COUNT(*) FROM (
  SELECT id FROM orders
  WHERE created_at < '2026-06-30'
  EXCEPT
  SELECT id FROM orders_archive
) AS missing;                                 -- 期望 0 行

-- 3. 抽样校验：抽查若干行字段值一致
SELECT * FROM orders_archive
WHERE created_at >= '2026-06-01' AND created_at < '2026-06-30'
LIMIT 100;

可回溯保障：
  □ 每次归档写审计表：归档时间、表名、时间窗、行数、MD5
  □ 归档数据设只读权限，防误改
  □ 冷层保留"回迁脚本"：出错时可反向 EXCHANGE 回热表
  □ 定期做归档恢复演练（从对象存储拉回并校验）

CREATE TABLE archive_audit (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  archive_batch VARCHAR(64) NOT NULL,        -- 批次号
  src_table VARCHAR(128) NOT NULL,           -- 源表
  dst_table VARCHAR(128) NOT NULL,           -- 目标
  time_start DATETIME NOT NULL,              -- 归档时间窗起点
  time_end   DATETIME NOT NULL,              -- 归档时间窗终点
  rows_moved BIGINT NOT NULL,                -- 迁移行数
  checksum_md5 VARCHAR(64),                  -- 全量行指纹
  status ENUM('ok','failed','rollback') NOT NULL,
  finished_at DATETIME NOT NULL,
  KEY idx_batch (archive_batch)
);

-- 对账：本批迁移行数与源表应删行数相等
SELECT batch, rows_moved, time_start, time_end
FROM archive_audit WHERE status = 'ok' ORDER BY id DESC LIMIT 10;

审计的价值：
  1. 归档出错时可定位到具体批次回滚
  2. 审计/合规审查时能证明数据去向
  3. 每月对账：所有已归档批次与历史库总量吻合