数据库数据归档：冷热数据分离、归档策略、归档查询、存储优化
数据归档这事儿，说白了就是给数据库"瘦身"。我做了十几年嵌入式数据库，见过太多系统因为数据膨胀导致性能雪崩。你想想看，一个跑了几年的设备，表里堆了几千万条历史记录，查询一条最新状态都要等好几秒——这谁受得了？
冷热数据分离，就是把经常访问的"热数据"和很少碰的"冷数据"分开存放。热数据留在主表里，保证读写性能；冷数据挪到归档区，节省存储空间。嗯，这里要注意：分离不是简单粗暴地DELETE，而是要有策略地迁移。
冷热数据的判定标准
我个人习惯用三个维度来判定数据冷热：
- 时间维度：最近7天、30天、90天的数据，按业务场景划分
- 访问频率：统计每条记录被查询的次数，低频的自动标记为冷数据
- 业务价值：比如订单状态为"已完成"且超过半年的，基本可以归档
我在项目中遇到过一种情况：某设备日志表每天增长50万条，但实际查询只关注最近3天的数据。更早的数据纯粹是"占着茅坑不拉屎"。当时我设计了一套分级归档策略，效果立竿见影。
归档策略设计
归档策略不能一刀切。我建议采用"三级归档"模型：
| 级别 | 数据年龄 | 存储介质 | 查询方式 |
|---|---|---|---|
| 热数据 | 0-30天 | SSD主存储 | 直接SQL查询 |
| 温数据 | 31-180天 | HDD归档表 | 归档视图查询 |
| 冷数据 | 181天以上 | 压缩文件/外部存储 | 离线导入查询 |
为什么会这样设计？因为不同年龄的数据，访问模式和性能要求完全不同。热数据要毫秒级响应，冷数据能接受秒级甚至分钟级延迟。
核心原则：归档不是删除，而是"降级存储"。数据还在，只是换了个更便宜的地方待着。
归档查询的实现
归档后怎么查？总不能每次都手动导入吧。我常用的做法是创建"统一查询视图"：
-- 创建归档表（与主表结构一致）
CREATE TABLE sensor_data_archive (
id INTEGER PRIMARY KEY,
device_id INTEGER,
timestamp INTEGER,
value REAL,
status INTEGER
);
-- 创建统一查询视图
CREATE VIEW sensor_data_all AS
SELECT * FROM sensor_data WHERE timestamp > strftime('%s','now','-30 days')
UNION ALL
SELECT * FROM sensor_data_archive;
-- 归档迁移（定时任务执行）
INSERT INTO sensor_data_archive
SELECT * FROM sensor_data
WHERE timestamp < strftime('%s','now','-30 days');
DELETE FROM sensor_data
WHERE timestamp < strftime('%s','now','-30 days');你看，应用层完全不需要改代码，查视图就行。底层数据在哪，视图帮你搞定。我曾经在一个工业物联网项目里用这招，把主表从800万行降到了50万行，查询速度从3秒降到了20毫秒。
小技巧：归档操作建议放在业务低峰期执行，比如凌晨2点。另外，DELETE之后记得执行VACUUM回收空间，否则SQLite不会自动释放磁盘。
存储优化三板斧
归档不只是搬数据，存储优化才是重头戏。我总结了三招：
- 压缩存储：冷数据可以用zlib或lz4压缩，压缩比通常能达到5:1到10:1。我见过一个极端案例，文本型日志压缩后只有原来的3%。
- 列式存储：如果归档数据只查某些字段，考虑用列式存储格式（比如Parquet）。查询特定列时，不用读整行数据。
- 分区存储：按时间分区，每个分区独立文件。查询时只扫描相关分区，IO量大幅减少。
嗯，这里要特别提醒：压缩和分区会带来额外的CPU开销。你得在存储节省和查询性能之间找平衡。我一般建议：超过1年的数据才做压缩，半年内的数据保持原样。
避坑指南：我曾经在一个项目里把归档数据直接压缩成gzip文件，结果业务方要查半年前的数据，解压花了5分钟...后来我改成"按天压缩，按需解压"，问题才解决。归档策略一定要考虑查询场景，别光顾着省空间。
知识体系总览
下面这张图把整个归档流程串起来了，你可以对照着理解：
从图上你能看到，整个流程是：业务数据 → 冷热判定 → 分流存储 → 归档优化 → 统一查询。每个环节都有对应的技术选型，不是随便搬个数据就完事了。
实战中的几个坑
最后分享几个我踩过的坑，你遇到了可以少走弯路：
- 归档事务太大：一次归档几十万条，事务日志撑爆了。后来改成"分批归档，每批1000条"，稳得很。
- 索引失效：归档表忘了建索引，查询时全表扫描。记住：归档表也要建索引，只是可以比主表少一些。
- 时间戳精度：不同设备的时间戳格式不统一，归档条件写错了。统一用UTC时间戳，别用本地时间。
数据归档这事，说白了就是"用空间换时间，再用时间换空间"的博弈。你设计得好，系统能多跑好几年不用大动干戈。设计得不好，归档本身就成了新的性能瓶颈。嗯，多测试、多观察，慢慢就能找到最适合你业务的节奏。