MySQL冷热分离与存算分层方案的核心是将不同访问频率的数据拆分到不同的存储层级，热数据放在高性能存储层保障查询效率，冷数据转移到低成本存储层降低整体成本，同时存算分层将计算资源和存储资源解耦，提升架构的扩展性。
冷热数据的判断标准
首先需要明确冷热数据的划分规则，常见的判断维度有以下几点：
- 时间维度：比如近3个月产生的订单数据为热数据，3个月之前的数据为冷数据
- 访问频率维度：日均访问次数超过100次的数据为热数据，低于该阈值的为冷数据
- 业务维度：未完成、进行中的业务数据为热数据，已完成归档的历史数据为冷数据
MySQL冷热分离的实现方式
1. 基于分区表的冷热分离
利用MySQL的分区表功能，按时间维度对表进行分区，热数据放在高性能分区，冷数据分区可以迁移到低成本的存储上。以下是创建按时间分区的订单表示例：
-- 创建按时间分区的订单表，按月分区
CREATE TABLE `order_info` (
`id` bigint NOT NULL AUTO_INCREMENT,
`order_no` varchar(64) NOT NULL,
`user_id` bigint NOT NULL,
`create_time` datetime NOT NULL,
`status` tinyint NOT NULL,
PRIMARY KEY (`id`, `create_time`)
) ENGINE=InnoDB
PARTITION BY RANGE (TO_DAYS(create_time)) (
PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
PARTITION p202403 VALUES LESS THAN (TO_DAYS('2024-04-01')),
PARTITION p_history VALUES LESS THAN MAXVALUE
);
可以将p_history分区的存储引擎修改为支持低成本存储的引擎，或者将该分区的数据迁移到归档库。
2. 基于归档表的冷热分离
创建结构相同的热表与冷表，热表存储近期数据，冷表存储历史数据，通过定时任务将热表中的冷数据迁移到冷表。以下是数据迁移的存储过程示例：
DELIMITER // CREATE PROCEDURE `archive_cold_order`() BEGIN -- 将3个月前的订单数据迁移到冷表 INSERT INTO order_info_cold (id, order_no, user_id, create_time, status) SELECT id, order_no, user_id, create_time, status FROM order_info_hot WHERE create_time < DATE_SUB(NOW(), INTERVAL 3 MONTH); -- 删除热表中已迁移的数据 DELETE FROM order_info_hot WHERE create_time < DATE_SUB(NOW(), INTERVAL 3 MONTH); END // DELIMITER ;
可以设置每天凌晨执行该存储过程，完成冷数据的自动归档。
3. 基于分布式存储的冷热分离
使用MySQL搭配分布式对象存储或者归档数据库，热数据存储在MySQL本地高性能磁盘，冷数据通过工具同步到对象存储或者TiDB等支持分层存储的数据库中，查询时通过路由规则判断数据位置。
MySQL存算分层方案设计
架构分层说明
存算分层将架构分为计算层和存储层：
- 计算层：由多个MySQL计算节点组成，负责SQL解析、查询优化、事务处理等计算逻辑，不存储全量数据，只缓存部分热数据
- 存储层：由高性能存储节点和低成本存储节点组成，高性能节点存储热数据，低成本节点存储冷数据，负责数据的持久化存储
核心实现逻辑
数据路由规则
在应用层或者中间件层维护数据路由表，记录数据的时间范围和对应的存储位置，查询时先查询路由表，再到对应的存储节点查询数据。以下是简单的路由逻辑示例：
public class DataRouter {
// 判断数据所在存储节点
public String getStorageNode(Date createTime) {
Date coldThreshold = DateUtils.addMonths(new Date(), -3);
if (createTime.after(coldThreshold)) {
return "hot_storage_node";
} else {
return "cold_storage_node";
}
}
// 查询订单数据
public OrderInfo queryOrder(long orderId, Date createTime) {
String node = getStorageNode(createTime);
if ("hot_storage_node".equals(node)) {
return hotOrderMapper.selectById(orderId);
} else {
return coldOrderMapper.selectById(orderId);
}
}
}
数据迁移逻辑
定时扫描热存储节点中的数据，将符合冷数据标准的数据迁移到冷存储节点，同时更新路由表信息，保证数据查询的准确性。迁移过程需要保证事务一致性，避免数据丢失。
方案注意事项
- 冷热分离后需要保证查询的兼容性，避免业务层需要感知数据所在层级，尽量通过中间件或者路由层屏蔽差异
- 存算分层需要做好计算节点和存储节点之间的网络优化，避免网络延迟影响查询性能
- 冷数据迁移过程需要做好监控，出现异常时及时回滚，保证数据一致性
- 定期校验冷热数据的划分规则，根据业务访问变化调整阈值，保证方案的有效性