MySQL 千万级表冷热数据处理方案

MySQL 千万级表冷热数据处理方案

对于千万级数据表的冷热数据处理，时间维度分区是一个可行的方案，但需要结合其他优化手段来确保业务连续性。以下是详细分析和多种解决方案：

分区方案分析

时间分区方案

-- 创建按月分区表
CREATE TABLE large_table (
    id BIGINT,
    data VARCHAR(255),
    created_at DATETIME,
    -- 其他字段...
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (TO_DAYS(created_at)) (
    PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')),
    PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')),
    -- 更多分区...
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

优点：

• 删除旧数据只需ALTER TABLE DROP PARTITION，操作瞬间完成
• 查询可以分区裁剪(partition pruning)提高性能

局限性：

• 分区键必须包含在主键中
• 跨分区查询可能性能下降
• 分区数量不宜过多(通常不超过1024个)

更完善的解决方案

1. 分区表+定期维护

-- 每月执行一次维护
ALTER TABLE large_table DROP PARTITION p_oldest;
ALTER TABLE large_table REORGANIZE PARTITION pmax INTO (
    PARTITION p_newmonth VALUES LESS THAN (TO_DAYS('2023-12-01')),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

最佳实践：

• 配合事件调度器自动执行
• 在业务低峰期执行
• 保留1-2个空分区备用

2. 分表+视图方案

-- 创建月度分表
CREATE TABLE large_table_202311 (LIKE large_table);
CREATE TABLE large_table_202312 (LIKE large_table);

-- 创建联合视图
CREATE VIEW large_table_view AS
SELECT * FROM large_table_202311 UNION ALL
SELECT * FROM large_table_202312;

优势：

• 删除旧表完全不影响其他表
• 可以针对热表单独优化

3. 归档表+触发器

-- 创建归档表
CREATE TABLE large_table_archive LIKE large_table;

-- 设置触发器或定期任务转移数据
INSERT INTO large_table_archive 
SELECT * FROM large_table 
WHERE created_at < DATE_SUB(NOW(), INTERVAL 3 MONTH);

DELETE FROM large_table 
WHERE created_at < DATE_SUB(NOW(), INTERVAL 3 MONTH);

优化技巧：

• 分批处理(每次1000-10000条)
• 在从库上执行归档
• 使用pt-archiver工具

4. 使用MySQL 8.0的不可见列

-- 标记而非删除数据
ALTER TABLE large_table 
ADD COLUMN is_active TINYINT(1) INVISIBLE DEFAULT 1;

-- "删除"旧数据(实际是标记)
UPDATE large_table 
SET is_active = 0 
WHERE created_at < DATE_SUB(NOW(), INTERVAL 3 MONTH);

-- 查询时自动过滤
CREATE VIEW current_data AS 
SELECT * FROM large_table WHERE is_active = 1;

生产环境建议方案

1. 分区表方案（适合大多数场景）

   • 按周/月分区
   • 配合事件定期维护分区
   • 保留最近4-5个分区(比要求的3个月多留缓冲)

2. 分表+中间件（超大规模数据）

   • 使用ShardingSphere或MyCat管理分表
   • 应用层无需关心分表逻辑

3. 混合方案（关键业务系统）

   -- 热数据: 分区表(最近3个月)
   -- 温数据: 压缩表(3-12个月)
   -- 冷数据: 归档到对象存储
   

性能对比

方案	锁时间	业务影响	实现复杂度	查询性能
时间分区	秒级	低	中	高
分表+视图	无	最低	高	中
归档删除	分钟级	高	低	中
不可见列	无	最低	中	中

最终建议：对于千万级表，优先考虑时间分区方案，配合定期维护任务，可以做到几乎不影响业务的情况下高效管理冷热数据。