我们的订单数据库每天产生数十万条记录，上周遇到一次严重的查询超时事故。当打开慢查询日志时，发现一个简单的订单统计SQL竟然扫描了上亿条历史数据。这让我意识到：不做数据归档的数据库就像装满旧报纸的仓库，看似保存完好，实则隐患重重。
一、为什么需要数据归档？
某电商平台的订单表现存1.2亿条记录，其中过去6个月内的活跃订单仅占15%。每当财务部门生成年度报表时，系统就会像老牛拉破车一样缓慢。这种情况可以通过三个关键指标判断是否该做归档：
- 单表数据量超过5000万行
- 历史数据查询频率低于1次/周
- OLTP业务响应时间波动超过30%
1.1 典型应用场景
物流公司的运单数据在签收6个月后，仅需保留基本信息；银行交易记录根据监管要求需长期保存但无需实时访问。这些场景的共同特征是：数据价值随时间递减，访问频率呈指数级下降。
二、主流归档方案对比
2.1 水平分表方案
-- 创建2023年度归档表
CREATE TABLE orders_archive_2023 LIKE orders;
-- 迁移2023年数据
INSERT INTO orders_archive_2023
SELECT * FROM orders
WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';
-- 验证数据一致性
SELECT COUNT(*) FROM orders_archive_2023; -- 返回4567890
SELECT COUNT(*) FROM orders
WHERE create_time <= '2023-12-31'; -- 应等于归档表总数
这种方法的缺点是会产生表碎片，需要定期执行OPTIMIZE TABLE。当需要查询跨年数据时，必须使用UNION ALL拼接多表。
2.2 分区表方案
-- 创建分区表
ALTER TABLE orders
PARTITION BY RANGE (YEAR(create_time)) (
PARTITION p2022 VALUES LESS THAN (2023),
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025)
);
-- 归档2022年分区到独立表空间
ALTER TABLE orders
REORGANIZE PARTITION p2022 INTO (
PARTITION p2022_archived DATA DIRECTORY '/archive'
);
通过EXPLAIN PARTITIONS可以看到查询命中的分区数。需要特别注意：分区键必须是主键的一部分，否则会破坏主键唯一性。
三、生产级归档系统搭建
3.1 归档数据表设计
CREATE TABLE archive_meta (
id INT PRIMARY KEY AUTO_INCREMENT,
source_table VARCHAR(64) NOT NULL,
archive_table VARCHAR(64) NOT NULL,
condition_expr TEXT,
archived_count INT DEFAULT 0,
begin_time DATETIME NOT NULL,
end_time DATETIME,
INDEX idx_table(source_table)
) ENGINE=InnoDB COMMENT='归档元数据表';
元数据表记录每次归档的上下文信息，当需要数据回溯时，可通过此表快速定位归档位置。建议添加操作人字段实现审计追踪。
3.2 存储过程示例
DELIMITER $$
CREATE PROCEDURE archive_orders_data(
IN source_db VARCHAR(64),
IN source_table VARCHAR(64),
IN archive_db VARCHAR(64),
IN cutoff_date DATE
)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE batch_size INT DEFAULT 5000;
DECLARE total_rows INT DEFAULT 0;
-- 创建影子归档表
SET @create_stmt = CONCAT(
'CREATE TABLE IF NOT EXISTS ', archive_db,'.',source_table,'_archive ',
'LIKE ', source_db,'.',source_table
);
PREPARE stmt FROM @create_stmt;
EXECUTE stmt;
-- 分页迁移数据
archive_loop: LOOP
START TRANSACTION;
-- 移动符合条件的记录
SET @move_stmt = CONCAT(
'INSERT INTO ', archive_db,'.',source_table,'_archive ',
'SELECT * FROM ', source_db,'.',source_table,
' WHERE create_date < ? LIMIT ?'
);
PREPARE move_stmt FROM @move_stmt;
EXECUTE move_stmt USING cutoff_date, batch_size;
SET @rows_moved = ROW_COUNT();
-- 删除已迁移记录
SET @delete_stmt = CONCAT(
'DELETE FROM ', source_db,'.',source_table,
' WHERE create_date < ? LIMIT ?'
);
PREPARE delete_stmt FROM @delete_stmt;
EXECUTE delete_stmt USING cutoff_date, batch_size;
SET @rows_deleted = ROW_COUNT();
COMMIT;
-- 判断是否继续循环
IF @rows_moved = 0 THEN
LEAVE archive_loop;
END IF;
SET total_rows = total_rows + @rows_moved;
END LOOP;
-- 记录归档元数据
INSERT INTO archive_meta(source_table, archive_table, archived_count)
VALUES (source_table, CONCAT(source_table,'_archive'), total_rows);
END$$
DELIMITER ;
关键点解析：采用分页处理避免大事务锁表，每个批次完成后立即提交。建议根据服务器配置调整batch_size参数，物理机可设置为10000，虚拟机建议5000。
四、事件调度配置
-- 开启事件调度器
SET GLOBAL event_scheduler = ON;
-- 创建每日归档任务
CREATE EVENT auto_archive_orders
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_DATE + INTERVAL 1 DAY
DO
BEGIN
CALL archive_orders_data(
'prod_db',
'orders',
'archive_db',
DATE_SUB(CURRENT_DATE(), INTERVAL 180 DAY)
);
-- 碎片整理
OPTIMIZE LOCAL TABLE prod_db.orders;
END;
使用SHOW PROCESSLIST可以监控事件执行状态。建议在业务低峰期执行OPTIMIZE操作，该操作会重建表并更新索引统计信息。
五、索引设计的陷阱
归档后的查询通常需要跨多个时间段，某物流系统曾因为缺少组合索引导致性能回退：
-- 不良设计
ALTER TABLE waybills ADD INDEX idx_created (create_time);
-- 优化后的设计
ALTER TABLE waybills
ADD INDEX idx_status_created (ship_status, create_time);
在where条件中同时出现状态和时间的查询，组合索引可将查询速度提升3-5倍。但要注意索引维护成本，超过5个索引就需要评估必要性。
六、最佳实践总结
应用场景匹配
- 金融交易系统：采用双重归档（在线库+备份磁带）
- 物联网设备：冷热分层存储（InnoDB + MyISAM）
- 社交平台：异步归档队列（Kafka + Spark）
技术优缺点分析
| 优势项 | 隐患点 |
|---|---|
| 查询性能提升30%-70% | 归档期间产生5%-10%的额外I/O |
| 存储成本降低40%-60% | 需要维护两套数据结构 |
| 索引维护时间缩短一半 | 跨表查询需要应用层适配 |
注意事项
- 使用
pt-archiver工具时要禁用foreign_key_checks - 归档后的数据压缩建议使用
zlib算法 - 定期执行
CHECKSUM TABLE验证数据完整性 - 归档表建议保留主库binlog的1.5倍时长
在实施归档方案后的运维数据显示：某银行核心系统的批量作业时间从47分钟缩短到12分钟，每月节约云存储费用约$12,000。这证明合理的归档策略不仅能优化性能，更是实现成本控制的有效手段。
评论