MySQL数据冷热分离是指依据数据的访问频次与业务价值，将系统中频繁读写的热数据同极少访问的冷数据拆分到不同存储结构或实例中的设计方法。在交易、日志、物联网等场景中，数据随时间推移呈明显冷热分层：近期数据被反复查询更新，历史数据仅在审计或统计时偶尔调取。若不加以区分，单一大表会导致索引膨胀、缓存命中率下降、备份恢复缓慢。通过合理的冷热分离，可以把SSD资源留给热数据，把冷数据放进压缩表或廉价存储，从而在成本可控的前提下保障核心链路性能。
基于时间范围的冷热归档方案
最直观的冷热分离方式是按时间字段（如create_time）划分界限，例如将三个月内的订单视为热数据，更早的转入order_archive表。该方案实施简单，业务改造成本低，适合多数中小规模系统。归档任务可通过定时脚本或事件调度器执行，在从库上跑批以避免影响主库写入。
具体实现时，应先在归档表上建立与源表兼容的索引，并使用游标或分页方式批量迁移，每次处理一万行左右以控制事务大小。迁移完成后，通过双写校验或行数比对确认一致性，再于源表删除已归档记录。需要注意的是，若业务存在跨时间段查询（如查一年前某用户全部订单），应在服务层做统一路由，先查热表再查冷表并合并结果。
下面是一个简单的归档存储过程示例，演示如何把过期数据搬走：
DELIMITER $$
CREATE PROCEDURE archive_old_orders(IN cutoff DATE)
BEGIN
INSERT INTO order_archive
SELECT * FROM orders
WHERE create_time < cutoff
AND NOT EXISTS (
SELECT 1 FROM order_archive a WHERE a.id = orders.id
);
DELETE FROM orders WHERE create_time < cutoff;
END$$
DELIMITER ;
这种方式的优势是逻辑清晰、易于排查，但当单表数据量极大时，删除操作可能触发长事务和锁等待。因此建议搭配分区表使用，或直接用RENAME TABLE将整月分区剥离，替代逐行删除。
利用MySQL分区表实现透明冷热分层
MySQL原生分区功能可按范围、列表、哈希等规则将数据物理拆分，但对应用而言仍是一张表。借助PARTITION BY RANGE按时间分区，可将旧分区直接关联到慢速磁盘，新分区放在高速盘。查询时优化器会自动裁剪分区，仅扫描命中部分，从而降低冷数据带来的IO开销。
例如对日志表按月份建范围分区，超过两年的分区可定时ALTER TABLE ... EXCHANGE PARTITION与归档表互换，实现近乎零成本的冷数据迁出。相比手动归档，分区方案减少了应用层感知，也避免了跨表查询的合并逻辑。不过分区表存在限制：主键必须包含分区键，且某些版本对外键支持不完善，设计时需权衡。
以下示例展示创建按时间分区的表结构：
CREATE TABLE access_log (
id BIGINT PRIMARY KEY,
user_id INT,
create_time DATE
)
PARTITION BY RANGE (TO_DAYS(create_time)) (
PARTITION p2023 VALUES LESS THAN (TO_DAYS('2024-01-01')),
PARTITION p2024 VALUES LESS THAN (TO_DAYS('2025-01-01')),
PARTITION pmax VALUES LESS THAN MAXVALUE
);
在运维层面，可将pmax之外的旧分区通过CHECK TABLE和OPTIMIZE PARTITION定期维护，并把冷分区文件放到挂载的低速存储上。这样既保留数据可查性，又释放了热分区的缓存空间。
冷热分离中的一致性与查询兼容设计
无论采用归档表还是分区，都要解决数据迁移期间的读写一致性。若在迁移过程中源表仍有更新，冷表就会缺失最新变更。常用做法是引入软删除标记与最终一致性校验：源表打标后异步同步至冷表，待核对无误再物理清除。也可以借助binlog订阅（如Canal）将变更实时投递到归档库，保证冷端近实时。
查询兼容方面，应在数据访问层封装统一接口，对调用方屏蔽底层是多表还是多实例。例如用MyBatis的@Interceptor或自研路由组件，根据时间参数决定走热库还是冷库。对于必须跨冷热聚合的统计需求，可提前在离线数仓计算好结果，避免在线服务直接UNION ALL大表。
此外还要考虑回灌成本：如果合规要求冷数据偶尔回到热环境，应保留反向同步脚本并定期演练。下面给出一个简单的服务层路由伪代码，说明如何按时间分发：
public Order queryOrder(long id, Date createTime) {
if (createTime.after(HOT_CUTOFF)) {
return hotMapper.selectById(id);
} else {
return coldMapper.selectById(id);
}
}
综上，冷热分离不是单纯挪数据，而是涉及存储选型、迁移机制、查询路由和运维监控的系统性设计。团队应结合数据增长速率、访问画像和硬件预算，选择最贴合业务的策略，并在上线后持续观测热区命中率与冷区查询延迟，动态调整边界。