亿级数据迁移实战：MySQL 到 ClickHouse

1.5亿条数据从 MySQL 迁移到 ClickHouse，踩过的坑和经验总结

背景#

业务需求：将订单历史数据从 MySQL 迁移到 ClickHouse，用于实时分析。

数据量：1.5 亿+
每日增量：50 万条
查询需求：多维度聚合分析

方案对比#

方案	优点	缺点
阿里巴巴 DataX	成熟稳定	需要额外服务
Canal + Flink	实时同步	复杂度高
定时导出脚本	简单可控	需要停机窗口
ClickHouse JDBC	实现简单	大数据量慢

TIP
最终选择：先全量迁移，再通过 Binlog 实时同步增量

1. 表结构设计#

1
-- MySQL 表结构
2
CREATE TABLE orders (
3
  id BIGINT PRIMARY KEY,
4
  user_id BIGINT NOT NULL,
5
  amount DECIMAL(10,2),
6
  status VARCHAR(20),
7
  created_at DATETIME,
8
  updated_at DATETIME
9
);
10

11
-- ClickHouse 表结构（物化视图 + MergeTree）
12
CREATE TABLE orders (
13
  id UInt64,
14
  user_id UInt64,
15
  amount Decimal(10,2),
16
  status String,
17
  created_at DateTime,
18
  updated_at DateTime,
19
  _sign Int8 DEFAULT 1
20
) ENGINE = MergeTree()
21
PARTITION BY toYYYYMM(created_at)
22
ORDER BY (user_id, created_at);

2. 全量迁移#

1
public void fullMigration() {
2
    // 1. 导出 MySQL 数据
3
    List<Orders> batch;
4
    while ((batch = queryOrders(offset, batchSize)) != null) {
5
        // 2. 写入 ClickHouse
6
        clickHouseClient.insert("orders", batch);
7
        offset += batchSize;
8

9
        // 3. 每 10 万条提交一次
10
        if (offset % 100000 == 0) {
11
            log.info("已迁移 {} 条", offset);
12
        }
13
    }
14
}

3. 增量同步#

1
// 使用 Canal 监听 Binlog
2
public class BinlogListener implements EventListener {
3
    @Override
4
    public void onEvent(Event event) {
5
        EventType type = event.getHeader().getEventType();
6

7
        if (type == EventType.WRITE || type == EventType.UPDATE) {
8
            // 写入 ClickHouse
9
            Order order = parseOrder(event);
10
            clickHouseClient.insert("orders", order);
11
        } else if (type == EventType.DELETE) {
12
            // 软删除（ClickHouse 不支持真正删除）
13
            // 通过 _sign = -1 标记
14
        }
15
    }
16
}

4. 踩过的坑#

坑1：写入速度太慢#

1
// ❌ 单条写入，1秒只能写入几百条
2
for (Order order : orders) {
3
    clickHouseClient.insert("orders", order);
4
}
5

6
// ✅ 批量写入，速度提升 100 倍
7
clickHouseClient.insertBatch("orders", orders);

坑2：分区规划不合理#

1
-- ❌ 按 ID 分区，数据不均匀
2
PARTITION BY id % 100
3

4
-- ✅ 按时间分区，查询友好
5
PARTITION BY toYYYYMM(created_at)

坑3：数据重复#

1
-- ✅ 使用 ReplacingMergeTree 自动去重
2
CREATE TABLE orders (
3
  id UInt64,
4
  ...
5
) ENGINE = ReplacingMergeTree(updated_at)
6
ORDER BY id;
7

8
-- ⚠️ 定期手动去重
9
OPTIMIZE TABLE orders FINAL;

性能对比#

指标	MySQL	ClickHouse
单表数据量	1.5 亿	1.5 亿
COUNT 查询	30s	0.5s
聚合查询	5-10s	<1s
存储空间	200GB	40GB

总结#

提前规划分区 - 按时间分区对分析查询友好
批量写入 - 减少网络开销
做好监控 - 同步延迟、数据完整性
灰度迁移 - 先迁移冷数据，再迁移热数据