PHP项目历史数据归档：大表分离实战指南
目录导读
- 为什么需要归档大表？ —— 性能瓶颈与业务痛点分析
- 归档前的准备工作 —— 数据评估、存储与工具选型
- 三种主流归档方案对比 —— 分表、分区、冷热分离
- 实战：PHP + MySQL 冷热分离流程 —— 步骤详解与代码示例
- 常见问题与问答（FAQ） —— 你可能会踩的坑
- 监控与维护 —— 确保归档后系统稳定运行
为什么需要归档大表？
在PHP项目中，日志、订单、用户行为等数据表动辄上亿行，例如一个电商系统的orders表,5年内可能达到10亿条记录。
- 查询缓慢：全表扫描IO开销巨大，
SELECT COUNT(*)都可能超时。 - 索引膨胀：B+树深度增加,写入和更新性能下降。
- 备份困难：
mysqldump一个100GB的表可能需要数小时。 - 存储成本高：热数据（近3个月）和冷数据（历史数年）混存,浪费SSD资源。
一个真实案例：某SaaS平台的 logs 表达200GB，归档后，普通查询从8秒降到0.2秒。
归档前的准备工作
1 数据评估
- 识别冷数据：通常按时间（如>6个月）、状态（如已完结订单）区分。
- 计算数据量：
SELECT COUNT(*) FROM big_table WHERE create_time < '2023-01-01'; - 确定保留周期：业务规定至少保留3年,则每次归档2年前的数据。
2 存储选型
| 方案 | 场景 | 优点 | 缺点 |
|---|---|---|---|
| 同一实例不同表（归档库） | 中等规模 | 管理简单 | 仍占主库存储 |
| 低成本云存储（如OSS） | 海量日志 | 极低成本 | 无法实时查询 |
| ClickHouse | 分析型查询 | 列式压缩，查询快 | 运维复杂 |
3 工具推荐
- PHP脚本 + cron：灵活,适合小团队。
- pt-archiver（Percona Toolkit）：成熟稳定,支持批处理。
- MySQL事件调度器：适合定时任务,但需注意死锁。
三种主流归档方案对比
方案A：分表（水平拆分）
原理：如orders_2023、orders_2024。
- 优点：查询时直接命中对应表,性能好。
- 缺点：业务代码需修改路由（如按年份取模）,维护成本高。
方案B：分区表（MySQL分区）
原理：PARTITION BY RANGE (TO_DAYS(create_time))。
- 优点：对应用透明,可直接删除旧分区。
- 缺点：分区数过多会降低性能（建议<1024个）；跨分区查询仍需扫描所有分区。
方案C：冷热分离（推荐）
原理：主表保留近期数据（如3个月）,历史数据迁移到归档表或另一数据库。
- 优点：主表大小可控，备份快；归档数据可压缩存储。
- 缺点：需要数据搬运的逻辑,可能涉及停机窗口。
我的选择：冷热分离 + 定期合并分区，热表用分区表,冷表用归档库或CSV文件导出到OSS。
实战：PHP + MySQL 冷热分离流程
步骤1：创建归档表结构
-- 假设原始表：orders (id, order_no, user_id, amount, create_time) CREATE TABLE orders_archive LIKE orders; -- 可选：在归档表上设置只读或添加归档时间戳 ALTER TABLE orders_archive ADD COLUMN archive_time DATETIME DEFAULT CURRENT_TIMESTAMP;
步骤2：编写PHP归档脚本
<?php
// archive.php - 每天凌晨执行，迁移180天前的订单
$cutoff = date('Y-m-d', strtotime('-180 days'));
$limit = 1000; // 每批处理1000条，避免长事务
try {
$pdo = new PDO('mysql:host=127.0.0.1;dbname=php_project;charset=utf8mb4', 'root', '');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->beginTransaction();
// 1. 将数据插入归档表
$stmt = $pdo->prepare("
INSERT INTO orders_archive (id, order_no, user_id, amount, create_time)
SELECT id, order_no, user_id, amount, create_time
FROM orders
WHERE create_time < ?
ORDER BY id
LIMIT ?
");
$stmt->execute([$cutoff, $limit]);
$inserted = $stmt->rowCount();
// 2. 从主表删除已归档数据（用JOIN确保一致性）
if ($inserted > 0) {
$pdo->exec("
DELETE o FROM orders o
JOIN orders_archive a ON o.id = a.id
WHERE o.create_time < '$cutoff'
LIMIT $limit
");
}
$pdo->commit();
echo "归档完成，本次迁移 $inserted 条记录\n";
} catch (Exception $e) {
$pdo->rollBack();
echo "错误: " . $e->getMessage();
}
?>
步骤3：设置cron定时任务
# 每天凌晨2点执行 0 2 * * * /usr/bin/php /path/to/archive.php >> /var/log/archive.log 2>&1
步骤4：查询时合并数据（可选）
若需要同时查询热数据和归档数据,可用视图或联合查询：
SELECT * FROM orders WHERE create_time >= '2023-01-01' UNION ALL SELECT * FROM orders_archive WHERE create_time >= '2023-01-01';
注意：此方法会降低性能,建议只给后台查询使用。
步骤5：空间回收
归档后，主表可能存在碎片（尤其DELETE后）：
OPTIMIZE TABLE orders; -- 会锁表，建议业务低峰执行
常见问题与问答（FAQ）
Q1：归档过程中如何处理新写入的数据？
A：先锁定原表（LOCK TABLES orders WRITE），但会影响业务，推荐使用双写策略：业务写入主表的同时，插入一个标记（如is_archived=0）,归档时只处理标记为0的旧数据。
Q2：pt-archiver和PHP脚本哪个更推荐？
A：pt-archiver是C语言编写的，性能更高，但依赖Percona Toolkit环境，PHP脚本适合自定义逻辑（如同步到ES/Redis），适合小规模数据（<1000万行）。
Q3：归档后查询历史数据怎么办？
A：提供独立的后台查询入口，如“历史订单查询”页面，专门访问归档表，前端建议不展示超过2年的数据,避免性能开销。
Q4：数据一致性如何保证？
A：使用事务+LIMIT分批处理，每次确保“插入归档表”和“删除主表”在同一次事务中，若中途失败，事务回滚,不会产生数据丢失。
Q5：归档数据是否需要索引？
A：需要！但可以缩减索引数量，例如归档表只保留(user_id, create_time)复合索引,满足主要查询场景。
监控与维护
- 监控指标：主表大小（建议<50GB）、归档延迟（比计划晚几天）、错误日志数量。
- 异常处理：如果
INSERT ... SELECT超时，可增加max_execution_time或改用mysqldump导出再导入。 - 定期测试：每季度从归档表随机恢复几条数据到主表,验证数据完整性。
建议工具链：
- PHP + cURL 发送告警到企业微信/钉钉
- Prometheus + node_exporter 监控MySQL表大小
- 定期执行
CHECK TABLE orders_archive;
PHP项目大表归档的核心在于最小化对在线业务的影响，推荐使用冷热分离方案，结合脚本定时迁移，并配合分区表降低主表膨胀速度，记住三点：分批处理避免长事务、保留最近N个月热数据、归档后及时回收空间，按照本文的步骤，你的项目也可以轻松承载亿级数据,同时保持查询毫秒级的响应速度。
