数据归档策略
随着业务增长,数据库表越来越大。为了防止无限膨胀,我们需要设计数据归档策略。
数据增长趋势:
第 1 个月:10 万条 → 查询正常
第 3 个月:500 万条 → 查询变慢
第 6 个月:2000 万条 → 备份困难
第 12 个月:5000 万条 → 需要改变
归档的好处
| 好处 | 说明 |
|---|---|
| 控制主表大小 | 保持热数据表在合理范围 |
| 提升查询性能 | 减少扫描数据量 |
| 降低备份成本 | 备份时间窗口缩短 |
| 合规要求 | 满足数据保留政策 |
数据不会一下子变冷,它会先从热变温再变冷。所以归档不是「一次性把旧数据搬走」,而是给数据划出温度带:常用的留在热层,不常用的搬进温层或冷层。后面几个方案讨论的,就是这条温度线划在哪里、由谁来执行。
热数据层(Hot)
- MySQL 主库
- 最近 30 天数据
- 频繁访问,需要索引优化
温数据层(Warm)
- MySQL 从库 / 归档表
- 30-90 天数据
- 偶尔访问,查询走从库
冷数据层(Cold)
- 对象存储 / CSV 文件
- 90 天以上数据
- 极少访问,按需查询
方案 A:归档到文件
归档到文件最简单,也最危险:文件一旦写下去就很难改,格式还可能随代码升级而失效。所以方案 A 的关键不是怎么写,而是怎么保证几年后还能读懂——格式要自描述,索引要另存。
方案 A:归档到文件
- 步骤 1:按保留期筛出待归档数据,导出为 CSV 或 Parquet 等自描述格式的文件
- 步骤 2:把文件按日期分目录存到对象存储或归档服务器,并用时间戳命名
- 步骤 3:把表名、时间范围、行数与文件路径写入归档记录表
- 步骤 4:抽样读取归档文件,确认格式仍能被正常解析,再删除主表数据
关注点:归档文件的格式、存放位置,以及将来还能不能被读出来。
方案 B:归档到历史表
归档到历史表的好处是查询方式基本不变,坏处是同一个库里东西越来越多。真正的分界线在于查询路由:查询语句必须知道该去主表还是历史表找,否则索引建得再全也没用。
方案 B:归档到历史表
- 步骤 1:在同一个库里建历史表,保持与主表相同的字段和索引
- 步骤 2:按月或按季度建分区表,保留期内的新分区可以直接淘汰
- 步骤 3:查询时按时间范围路由到主表或历史表,避免退化成全表扫描
- 步骤 4:历史再多时把旧分区导出成文件,衔接方案 A 的归档流程
关注点:历史表与主表如何隔离,以及查询如何被路由到正确的表。
方案 C:使用分区表
| 字段名 | 类型 | 约束 / 说明 |
|---|---|---|
id | BIGINT | PRIMARY KEY, AUTO_INCREMENT |
endpoint | VARCHAR(255) | NOT NULL |
params | TEXT | 请求参数 |
response_status | INT | HTTP 状态码 |
response_time | INT | 响应时间(ms) |
created_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP |
| id | endpoint | params | response_status | response_time | created_at |
|---|---|---|---|---|---|
1 | /api/weather | city=北京 | 200 | 118 | 2026-03-31 14:00:00 |
2 | /api/weather | city=上海 | 200 | 132 | 2026-03-31 14:01:00 |
3 | /api/weather | city=广州 | 500 | 240 | 2026-03-31 14:02:00 |
| 字段名 | 类型 | 约束 / 说明 |
|---|---|---|
id | INT | PRIMARY KEY, AUTO_INCREMENT |
email | VARCHAR(255) | UNIQUE, NOT NULL |
password_hash | VARCHAR(255) | NOT NULL |
api_key | VARCHAR(64) | UNIQUE, NOT NULL |
plan | VARCHAR(50) | DEFAULT 'free' |
daily_limit | INT | DEFAULT 1000 |
created_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP |
| id | plan | daily_limit | created_at | |
|---|---|---|---|---|
1 | alice@demo.com | free | 1000 | 2026-04-08 10:32:11 |
2 | bob@demo.com | pro | 100000 | 2026-04-11 09:15:02 |
3 | ops@demo.com | basic | 10000 | 2026-04-13 21:44:36 |
| 字段名 | 类型 | 约束 / 说明 |
|---|---|---|
id | BIGINT | PRIMARY KEY, AUTO_INCREMENT |
user_id | INT | NOT NULL |
endpoint | VARCHAR(255) | NOT NULL |
hour | TIMESTAMP | 精确到小时 |
total_calls | INT | DEFAULT 0 |
success_calls | INT | DEFAULT 0 |
error_calls | INT | DEFAULT 0 |
avg_response_time | FLOAT | 平均响应时间(ms) |
created_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP |
| user_id | endpoint | hour | total_calls | success_calls | error_calls |
|---|---|---|---|---|---|
1001 | /api/weather | 2026-04-20 10:00 | 132 | 128 | 4 |
1002 | /api/forecast | 2026-04-20 10:00 | 91 | 90 | 1 |
| 字段名 | 类型 | 约束 / 说明 |
|---|---|---|
id | BIGINT | PRIMARY KEY, AUTO_INCREMENT |
user_id | INT | NOT NULL |
endpoint | VARCHAR(255) | NOT NULL |
date | DATE | 统计日期 |
total_calls | INT | DEFAULT 0 |
success_calls | INT | DEFAULT 0 |
error_calls | INT | DEFAULT 0 |
avg_response_time | FLOAT | 平均响应时间(ms) |
p95_response_time | FLOAT | 95分位响应时间(ms) |
p99_response_time | FLOAT | 99分位响应时间(ms) |
created_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP |
| user_id | endpoint | date | total_calls | success_calls | error_calls |
|---|---|---|---|---|---|
1001 | /api/weather | 2026-04-20 | 2410 | 2368 | 42 |
1002 | /api/forecast | 2026-04-20 | 1832 | 1810 | 22 |
| 字段名 | 类型 | 约束 / 说明 |
|---|---|---|
id | BIGINT | PRIMARY KEY, AUTO_INCREMENT |
user_id | INT | NOT NULL |
month | DATE | 每月1号 |
total_calls | INT | DEFAULT 0 |
total_success | INT | DEFAULT 0 |
total_error | INT | DEFAULT 0 |
avg_daily_calls | FLOAT | 日均调用次数 |
peak_hour_calls | INT | 峰值小时调用量 |
most_used_endpoint | VARCHAR(255) | 最常调用接口 |
created_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP |
| user_id | month | total_calls | total_success | total_error | avg_daily_calls |
|---|---|---|---|---|---|
1001 | 2026-04 | 58420 | 57110 | 1310 | 1947 |
1002 | 2026-04 | 40680 | 39920 | 760 | 1356 |
| 字段名 | 类型 | 约束 / 说明 |
|---|---|---|
id | INT | PRIMARY KEY, AUTO_INCREMENT |
name | VARCHAR(100) | UNIQUE, NOT NULL |
display_name | VARCHAR(100) | NOT NULL |
endpoint | VARCHAR(255) | NOT NULL |
method | VARCHAR(10) | DEFAULT 'GET' |
category | VARCHAR(50) | weather/news/finance/tools |
external_api_url | VARCHAR(255) | 上游服务地址 |
requires_auth | BOOLEAN | DEFAULT TRUE |
cache_ttl | INT | 缓存时间(秒) |
rate_limit_factor | INT | DEFAULT 1 |
status | VARCHAR(20) | active/deprecated/maintenance |
is_public | BOOLEAN | DEFAULT TRUE |
created_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP |
updated_at | TIMESTAMP | ON UPDATE CURRENT_TIMESTAMP |
| id | name | display_name | category | method | cache_ttl | status |
|---|---|---|---|---|---|---|
1 | weather | 天气查询 | weather | GET | 3600 | active |
2 | news | 新闻快讯 | news | GET | 300 | active |
3 | stock | 股票行情 | finance | GET | 60 | maintenance |
| 字段名 | 类型 | 约束 / 说明 |
|---|---|---|
id | INT | PRIMARY KEY, AUTO_INCREMENT |
user_id | INT | NOT NULL |
api_id | INT | NOT NULL, FK -> api_definitions.id |
granted_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP |
expires_at | TIMESTAMP | NULL 表示长期有效 |
status | VARCHAR(20) | active/revoked/expired |
| id | user_id | api_id | granted_at | expires_at | status |
|---|---|---|---|---|---|
8001 | 101 | 1 | 2026-04-10 10:00:00 | NULL | active |
8002 | 101 | 2 | 2026-04-10 10:00:00 | 2026-05-10 10:00:00 | active |
8003 | 204 | 3 | 2026-04-01 09:15:00 | 2026-04-30 23:59:59 | expired |
| 字段名 | 类型 | 约束 / 说明 |
|---|---|---|
id | BIGINT | NOT NULL, AUTO_INCREMENT |
user_id | INT | NOT NULL |
endpoint | VARCHAR(255) | NOT NULL |
params | TEXT | 请求参数 |
response_time | INT | 毫秒 |
status | VARCHAR(50) | 调用状态 |
created_at | TIMESTAMP | 分区键 |
pk | PRIMARY KEY(id, created_at) | 支持范围分区 |
| partition | range | records | storage | hotness |
|---|---|---|---|---|
p202401 | < 2024-02-01 | 58,200,000 | 370 GB | cold |
p202402 | < 2024-03-01 | 61,900,000 | 395 GB | warm |
pmax | MAXVALUE | ongoing | rolling | hot |
归档是一个会长期存在的后台任务,它必须能安全地重跑。批次记录就是为此存在的——有了它,任务中途失败后可以从断点继续,而不是从头再来一遍。
定时归档任务:部署操作
- 步骤 1:把归档任务安排在低峰期执行,避开业务高峰和备份窗口
- 步骤 2:用固定的分批大小处理数据,每批提交一次,避免长事务长时间锁表
- 步骤 3:让任务可重入:按时间范围记录已完成的批次,重跑不会重复归档
- 步骤 4:任务结束后核对主表行数与归档行数是否对得上
关注点:归档任务的执行窗口、重跑能力与幂等性。
保留策略配置
保留期是业务决策,不是技术参数。调用日志可能只要三个月,用户数据可能必须留三年,还有些要按合规要求删——所以它必须是配置,而且要能追溯是谁定的。
保留策略配置
- 步骤 1:按数据用途定保留期,调用日志与用户数据采用不同的期限,并写成可配置项
- 步骤 2:明确到期后的动作是归档、清理还是转冷,并把这三者区分开
- 步骤 3:对有合规要求的数据,把保留期与删除动作一并记录成审计日志
关注点:不同类型数据的保留期差异,以及到期后的具体处理动作。
自动化清理
归档和清理必须成对出现:先确认数据已经归档好,再去删除主表里的记录。顺序一旦反了,就不是性能问题,而是实实在在的数据丢失。这也是这一节最想强调的一条红线。
自动化清理
- 步骤 1:用定时任务按分区或按批次清理到期数据,不用一条大 SQL 删全表
- 步骤 2:清理前先确认数据已归档且归档文件可读,归档失败就跳过不删
- 步骤 3:归档确认完成后才删除主表数据,避免同一份数据长期存两遍
- 步骤 4:记录每轮清理的行数、耗时与失败原因,作为容量规划的依据
关注点:清理动作的粒度,以及不可逆操作之前的保险措施。
监控指标
归档策略最怕的不是出错,而是「悄悄失效」——任务还在跑,数据量却压不下来;或者压下来了,查询反而变慢。监控要盯的是这些趋势,而不是某一个点的数值。
监控指标
- 步骤 1:监控主表行数与增长速度,估算多久会重新涨到告警阈值
- 步骤 2:统计归档任务的执行成功率与单轮耗时,识别反复失败的批次
- 步骤 3:跟踪查询的平均扫描行数,判断归档后热数据是否真的变少了
- 步骤 4:记录归档与清理后存储占用的变化,评估长期成本
关注点:哪些数字能提前看出归档策略正在失效。
告警配置
告警的价值在于它指向明确的动作。归档任务延迟了要做什么、主表涨超了要做什么,都应该在设计告警时就想清楚,而不是等告警响了再临时决定。
告警配置
- 步骤 1:为归档任务连续失败、主表行数超过阈值分别配置告警
- 步骤 2:为归档与清理的处理量异常下跌配置告警,排查是否有漏处理
- 步骤 3:做分级:任务延迟先告警,连续失败再升级为需要人工介入
- 步骤 4:把告警指向具体的任务与数据表,收到告警就能直接定位
关注点:哪些信号值得在半夜把人叫醒,哪些只需要记录。
发现内容有问题?
告诉我们哪里需要改进。
