什么是冷热分离？归档表的常见方案？

考察要点

这道题考察的是候选人对大型数据库系统架构设计和数据生命周期管理的理解。面试官想看你是否懂：

1. 为什么要进行数据分离：即冷热分离带来的核心价值，如提升核心业务性能、降低存储成本、提高系统可维护性。
2. 如何实施数据归档：掌握至少两种主流的数据归档方案（如定时任务、分区表），并能分析其优缺点和适用场景。
3. 工程实践中的考量：是否具备处理大规模数据操作的经验，比如如何安全、高效地在线删除大量数据，以及如何处理归档后的跨库查询问题。

核心概念

冷热分离是一种数据库优化策略，其核心思想是根据数据的访问频率和业务价值，将数据划分为“热数据”和“冷数据”，并采用不同的存储和管理策略。

• 热数据 (Hot Data)：访问频繁、经常修改、对实时性要求高的数据。例如，电商网站近三个月的订单、活跃用户的个人信息。热数据通常存储在高性能的存储介质上（如 SSD），并保持较小的体量以确保核心业务的查询性能。
• 冷数据 (Cold Data)：访问频率低、几乎不修改、多为历史归档性质的数据。例如，一年前的订单、已注销用户的日志。冷数据可以迁移到成本更低的存储介-质（如 HDD），甚至迁移出关系型数据库，存入数据仓库或对象存储。

数据识别依据: 通常使用时间维度作为划分标准，例如 create_time 或 update_time。

归档方案详解

方案一：定时任务 + 脚本归档

这是最常见和灵活的方案。通过一个定时执行的脚本（如 Shell + SQL，或使用 Go/Java 编写的程序）来迁移数据。

执行流程:

1. 锁定批次: SELECT id FROM hot_table WHERE create_time < '...' LIMIT 1000; 选出一批要归档的数据。
2. 迁移数据: INSERT INTO cold_table SELECT * FROM hot_table WHERE id IN (...); 将数据插入到归档表。
3. 删除数据: DELETE FROM hot_table WHERE id IN (...); 从原表删除已成功迁移的数据。
4. 循环执行: 重复以上步骤，直到没有数据需要归档。

SQL 示例:

 1-- 假设 hot_orders 是热表, cold_orders 是归档表
 2-- 伪代码，实际应在程序中循环执行
 3BEGIN;
 4-- 步骤1 & 2: 将 90 天前的数据迁移到归档表
 5INSERT INTO cold_orders (id, user_id, amount, create_time)
 6SELECT id, user_id, amount, create_time
 7FROM hot_orders
 8WHERE create_time < DATE_SUB(NOW(), INTERVAL 90 DAY)
 9LIMIT 1000;
10
11-- 步骤3: 从热表删除已迁移的数据 (使用 JOIN 更安全)
12DELETE h
13FROM hot_orders h
14JOIN cold_orders c ON h.id = c.id
15WHERE h.create_time < DATE_SUB(NOW(), INTERVAL 90 DAY);
16COMMIT;

• 优点:
  • 实现简单，逻辑灵活，不依赖特定的数据库功能。
  • 冷热表可以异构（不同索引、不同存储引擎，甚至不同数据库实例）。
• 缺点:
  • 存在数据不一致的风险（INSERT 成功但 DELETE 失败）。需要强事务保证和重试机制。
  • 大批量 DELETE 会产生表锁、行锁，消耗大量 IO 和 CPU，可能导致主从延迟。
  • 对线上业务有一定性能冲击，必须在业务低峰期执行。

方案二：MySQL 分区表 (Partitioning)

利用 MySQL 5.6+ 的分区功能，特别是 RANGE 分区，将一个大表在物理上分割成多个小文件。归档操作可以转化为一个近乎瞬时的元数据操作。

实现方式:

1. 创建分区表: 按时间范围（如按月）创建分区。
   sql

   ⧉⛶

    1CREATE TABLE hot_orders (
    2    id INT NOT NULL AUTO_INCREMENT,
    3    user_id INT,
    4    create_time DATETIME NOT NULL,
    5    PRIMARY KEY (id, create_time) -- 分区键必须是主键或唯一索引的一部分
    6)
    7PARTITION BY RANGE (TO_DAYS(create_time)) (
    8    PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')),
    9    PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')),
   10    ...
   11    PARTITION p_future VALUES LESS THAN MAXVALUE
   12);

2. 归档分区: 使用 ALTER TABLE ... EXCHANGE PARTITION 将一个旧分区与一个普通的归档表进行交换。
   sql

   ⧉⛶

    1-- 1. 创建一个与 hot_orders 结构完全相同的归档表
    2CREATE TABLE cold_orders_202301 LIKE hot_orders;
    3ALTER TABLE cold_orders_202301 REMOVE PARTITIONING; -- 归档表不能是分区表
    4
    5-- 2. 交换分区，这是一个原子且极快的元数据操作
    6ALTER TABLE hot_orders EXCHANGE PARTITION p202301 WITH TABLE cold_orders_202301;

   执行后，p202301 分区的数据瞬间进入 cold_orders_202301 表，而 hot_orders 表中不再包含这部分数据。

• 优点:
  • 归档过程极快，对业务几乎无感知，因为它不涉及数据行移动。
  • 管理清晰，删除历史数据只需 DROP PARTITION，同样是秒级操作。
• 缺点:
  • 分区表有较多限制：分区键必须包含在主键或唯一索引中、外键不支持、打开文件句柄数增多等。
  • 一旦使用分区，所有查询都必须考虑分区键，否则可能导致分区剪枝（Partition Pruning）失效，性能下降。
  • 分区维护（增加新分区、删除旧分区）需要额外的定时任务。

常见低分回答

1. “直接 DELETE 就行了”: DELETE FROM hot_table WHERE create_time < '...'。这种回答忽略了归档（保留数据）的需求，并且单次大批量 DELETE 会锁表，产生大量 binlog，可能拖垮线上服务。
2. 混淆归档与备份: “冷数据就是数据库备份”。这是概念性错误。备份是为了灾难恢复，恢复的是某个时间点的全量或增量数据。归档是为了管理数据生命周期，分离出的冷数据仍可能被查询。
3. 描述脚本方案时忽略事务和批量处理: “先 INSERT 再 DELETE”。没有提及 BEGIN/COMMIT 保证原子性，也没说要分批处理以避免长时间锁和资源消耗，这是缺乏生产经验的表现。
4. 滥用触发器: “在热表上建一个 AFTER DELETE 触发器，把删掉的数据写到冷表”。触发器会增加每次 DELETE 操作的开销，在高并发场景下会严重影响写入性能，通常不推荐用于大批量归档。
5. 对查询透明性考虑不周: 只说了如何分离，但没说分离后如果业务要同时查冷热数据怎么办，缺乏对业务影响的完整思考。

工程实践与优化

• 首选工具 pt-archiver: 对于脚本归档方案，不要自己造轮子。Percona Toolkit 中的 pt-archiver 是业界标准工具，它内置了批量处理、事务、防止主从延迟、错误重试等高级功能，安全且高效。
  bash

  ⧉⛶

   1pt-archiver --source h=host,D=db,t=hot_table \
   2            --dest h=host,D=db,t=cold_table \
   3            --where "create_time < '2023-01-01'" \
   4            --limit 1000 --commit-each --progress 10000 \
   5            --run-time 4h --statistics

• 归档表的设计:
  • 索引优化: 冷表查询场景单一，可以去掉不必要的索引，只保留查询必须的索引，以节省空间和 INSERT 开销。
  • 存储引擎: 可以考虑使用压缩行格式 ROW_FORMAT=COMPRESSED 来节省磁盘空间。对于极冷数据，MySQL 的 ARCHIVE 引擎提供高压缩比但不支持索引和事务。
  • 异构存储: 最优实践是将冷数据归档到更廉价的系统，如阿里云的 OSS、AWS S3，或数据仓库如 ClickHouse、BigQuery，以实现极致的成本优化和强大的分析能力。
• 跨冷热数据查询:
  • UNION ALL 视图: 可以创建一个视图来联接冷热表，对应用层透明。
    sql

    ⧉⛶

     1CREATE VIEW v_orders AS
     2SELECT * FROM hot_orders
     3UNION ALL
     4SELECT * FROM cold_orders;

    注意：这种方式在有 ORDER BY 和 LIMIT 时性能可能不佳，需要谨慎评估。
  • 应用层聚合: 由应用代码分别查询热表和冷表，然后合并结果。逻辑复杂，但控制力更强。
• 监控: 必须建立监控告警，跟踪热表的大小、归档任务的执行时长和成功率，确保数据量维持在健康水平。

常见追问与延伸题目

1. 追问：如果使用 pt-archiver，它的工作原理是什么？相比自己写脚本有什么优势？

   • 回答要点 1 (原理): pt-archiver 本质上也是一个循环执行“查询-插入-删除”的程序。它会以小批量（chunk）的方式从源表（--source）中 SELECT 数据，然后 INSERT 到目标表（--dest），最后 DELETE 源表中的对应数据。整个过程包裹在事务中。
   • 回答要点 2 (优势):
     • 安全性: 内置事务，保证数据一致性。支持 --dry-run 预演。
     • 性能控制: 可通过 --limit 控制批次大小，通过 --sleep 控制批次间隔，避免冲击线上服务。它还会监控主从复制延迟（--check-replication-lag），当延迟超过阈值时会自动暂停。
     • 健壮性: 强大的错误处理和重试机制，支持断点续传。
     • 灵活性: 支持归档到不同服务器、不同数据库，甚至归档到文件 (--file)。逻辑非常成熟，是经过成千上万生产环境验证的。

2. 追问：如果用分区表方案，EXCHANGE PARTITION 是如何工作的？有什么核心限制？

   • 回答要点 1 (工作原理): EXCHANGE PARTITION 是一个 DDL 操作，它交换的是元数据指针。MySQL 只是修改了内部的数据字典，将分区 p 指向的物理文件（.ibd 文件）与表 t 指向的物理文件进行互换。这个过程不涉及任何数据行的复制、移动或删除，因此速度极快，接近于 0 开销。
   • 回答要点 2 (核心限制):
     • 结构一致: 用于交换的非分区表必须与分区表有完全相同的表结构（列名、类型、顺序、字符集等），但不能有分区定义。
     • 外键限制: 两个表都不能有外键引用，也不能被其他表外键引用。
     • 触发器限制: 表上不能有触发器。
     • 临时表限制: 不能与临时表进行交换。
     • 数据清空: 在将一个普通表的数据换入分区时，该普通表的数据会被清空（因为它的数据指针指向了原分区的空数据文件）。

3. 追问：冷热分离后，如果有一个业务需求需要跨冷热库查询，并按时间排序分页，该如何设计？

   • 回答要点 1 (简单方案 - 数据库层): 使用 UNION ALL 视图。优点是简单透明，但性能瓶颈明显。当 ORDER BY create_time DESC LIMIT 10 OFFSET 10000 时，数据库可能需要对冷热两个表的结果集做巨大的合并排序，然后才能取出一页数据，非常低效。
   • 回答要点 2 (改进方案 - 应用层): 应用层做融合。例如，查询第一页时，SELECT ... FROM hot_table ORDER BY create_time DESC LIMIT 10。如果要翻页到第 N 页，应用层可以根据分页参数和热数据的时间范围，判断是否需要开始查询冷表。例如，当请求的页码已经超出了热表能提供的数据范围时，再向冷表发起查询。这种方式逻辑复杂，尤其在处理跨冷热表的边界页时。
   • 回答要点 3 (最佳实践 - 架构层): 将数据同步到专门的查询分析系统。将冷热数据通过 CDC (Change Data Capture) 工具（如 Canal、Debezium）实时或准实时地同步到一个统一的搜索引擎（如 Elasticsearch）或 OLAP 数据库（如 ClickHouse, Doris）。这些系统天生为海量数据的聚合、排序和查询而设计。这样，OLTP 库（MySQL）只负责高并发的事务处理，复杂的查询分析任务则交由专业系统完成，实现了读写分离和负载隔离，是扩展性最好的方案。