文章
文章用户商铺文档快讯圈子网址导航供求信息

ClickHouse 生产 3 年实战:日 5 亿日志 + 200ms 聚合 + Doris 对比

ES 跑日志 5 亿/天聚合 20s,换 ClickHouse 后 200ms,资源省一半。本文实录表引擎选型 + ORDER BY/分区设计 + LowCardinality + Buffer 表 + 物化视图 + MaterializedMySQL CDC + 集群配置,对比 Doris 实战经验。

三年前我们用 MySQL + ES 跑数据报表,日均 5 亿日志,聚合查询 20 秒以上。换 ClickHouse 后同样查询 200 毫秒,资源还省一半。本文实录 ClickHouse 从 0 到生产的全过程,讲透表引擎选型、分区键、ORDER BY、物化视图、MaterializedMySQL CDC、对比 Doris 的实战经验。

为什么从 ES 换到 ClickHouse

业务场景:用户行为日志 + 订单数据 OLAP 报表

ES 时期(2020):
  - 单条日志 800B,日均 5 亿,存储 400GB/天
  - 集群:8 节点 × 32C × 128GB
  - 聚合查询(group by day, page):15-30s
  - 写入瓶颈:refresh + merge 抢 CPU
  - 磁盘按月归档,3 个月历史就要 30TB

ClickHouse(2021):
  - 同样数据,压缩比 8-10x,存储 50GB/天
  - 集群:4 节点 × 32C × 64GB
  - 同等聚合:200-500ms
  - 写入:批量 1w 条/批,持续 50w/s 无压力

简单说:OLAP 场景 ClickHouse 性能 + 资源效率甩 ES 几条街

表引擎选型

ClickHouse 表引擎(只列实战常用的):

MergeTree:基础引擎,99% 场景都用它(或者它的变体)
  - 自动按主键排序
  - 数据压缩存储
  - 后台 merge part

ReplacingMergeTree:相同 ORDER BY key 的行,merge 时只保留最新版本
  - 用途:做去重(订单状态最新)
  - 注意:不保证立即去重,要查 FINAL 或 OPTIMIZE

SummingMergeTree:相同 ORDER BY key 的行,数值列自动求和
  - 用途:预聚合表(按小时/天的指标)

AggregatingMergeTree:相同 ORDER BY key 的行,聚合状态列自动合并
  - 用途:物化视图存预聚合状态

CollapsingMergeTree:用 sign 列实现"删除"
  - 用途:订单状态变更场景

ReplicatedMergeTree:上面的 Replicated 版本(多副本)
  - 生产必选,单副本不要碰

Distributed:分布式表(逻辑表,不存数据)
  - 跨 shard 查询入口

建表实战:用户行为日志

-- 本地表(每个分片建一份)
CREATE TABLE user_events_local ON CLUSTER ck_cluster
(
  event_time   DateTime,
  event_date   Date MATERIALIZED toDate(event_time),
  user_id      UInt64,
  event_type   LowCardinality(String),       -- 枚举类型用 LowCardinality
  page         LowCardinality(String),
  device       LowCardinality(String),
  city         LowCardinality(String),
  duration_ms  UInt32,
  payload      String CODEC(ZSTD(3))         -- JSON,用 ZSTD 压缩
)
ENGINE = ReplicatedMergeTree(
  '/clickhouse/tables/{shard}/user_events_local',
  '{replica}'
)
PARTITION BY toYYYYMM(event_date)             -- 按月分区
ORDER BY (event_date, user_id, event_time)    -- 主键 = 排序键
SAMPLE BY user_id                              -- 支持采样查询
TTL event_date + INTERVAL 180 DAY              -- 180 天后自动删
SETTINGS index_granularity = 8192;

-- 分布式表(逻辑表,业务查询入口)
CREATE TABLE user_events ON CLUSTER ck_cluster AS user_events_local
ENGINE = Distributed(ck_cluster, default, user_events_local, rand());

-- 查询时用分布式表
SELECT count() FROM user_events
WHERE event_date = today() AND event_type = 'click';

关键设计要点

1. ORDER BY:决定数据物理顺序,查询必经
   - 按业务查询频率,把过滤性强的列放前面
   - 第一列:时间(几乎所有查询都过滤时间)
   - 第二列:常用 group by 的列(user_id / item_id / ...)

2. PARTITION BY:分区粒度
   - 按月最常见(toYYYYMM)
   - 不要按天分区,part 太多会拖慢 merge
   - 一个分区目标:几十亿行 / 50GB-100GB

3. LowCardinality:枚举类型必用
   - 字符串值集 < 1w 时用,节省 50-90% 存储
   - 不要对高基数字符串用(URL / trace_id)

4. CODEC:压缩选型
   - 时间序列数值用 Delta / DoubleDelta + LZ4
   - JSON / 长文本用 ZSTD(3)
   - 默认 LZ4 已经够好

5. SAMPLE BY:采样查询
   - 适合"看趋势,不要精确数"的场景
   - SELECT count() * 10 FROM t SAMPLE 0.1 WHERE ...

6. TTL:数据生命周期
   - 按天/月自动清理
   - 也可以 TTL 移动到冷盘(分层存储)

批量写入(避坑)

// ClickHouse 不喜欢单条 INSERT
// 错:每条一个 INSERT
for (Event e : events) {
    jdbcTemplate.update("INSERT INTO user_events VALUES (?, ?, ?, ?)",
        e.getTime(), e.getUserId(), e.getType(), e.getPage());
}
// 1000 条 = 1000 个 part = ClickHouse 抓狂

// 对:批量 INSERT,推荐 5000-100000 条一批
String sql = "INSERT INTO user_events (event_time, user_id, event_type, page) VALUES";
List batch = events.stream()
    .map(e -> new Object[]{e.getTime(), e.getUserId(), e.getType(), e.getPage()})
    .collect(Collectors.toList());
jdbcTemplate.batchUpdate(sql + " (?, ?, ?, ?)", batch);

// 进阶:用 Buffer 表缓冲
CREATE TABLE user_events_buffer AS user_events
ENGINE = Buffer(
  default, user_events_local,
  16,                  -- 16 个 buffer
  10, 60,              -- min/max 时间 sec
  10000, 1000000,      -- min/max 行数
  10000000, 100000000  -- min/max 字节
);
// 应用写 buffer 表,后台异步刷到本地表

查询性能对比

-- 同样数据 50 亿行
-- ES 跑:15-30 秒
-- ClickHouse 跑:见下

-- 查询 1:全表 count
SELECT count() FROM user_events WHERE event_date BETWEEN '2024-01-01' AND '2024-12-31';
-- 0.05s

-- 查询 2:按页面分组求 UV
SELECT page, uniqExact(user_id) AS uv
FROM user_events
WHERE event_date = today()
GROUP BY page ORDER BY uv DESC LIMIT 20;
-- 0.8s(全表 5 亿行)

-- 查询 3:同比上周(双时间段)
SELECT
  toStartOfHour(event_time) AS h,
  countIf(event_date = today()) AS today_cnt,
  countIf(event_date = today() - 7) AS last_week_cnt
FROM user_events
WHERE event_date IN (today(), today() - 7)
GROUP BY h ORDER BY h;
-- 1.2s

-- 查询 4:漏斗分析(funnel)
SELECT
  level,
  count() AS users
FROM (
  SELECT
    user_id,
    windowFunnel(86400)(event_time,
      event_type = 'view',
      event_type = 'add_cart',
      event_type = 'checkout',
      event_type = 'pay'
    ) AS level
  FROM user_events
  WHERE event_date = today()
  GROUP BY user_id
)
WHERE level > 0
GROUP BY level;
-- 3.5s(2 亿用户)

物化视图(MV)预聚合

-- 业务高频查询:按小时的活跃 UV
-- 不做 MV:每次都 group by uniqExact,慢
-- 做 MV:写入时增量计算,查询时直接读

-- 1. 创建预聚合表(AggregatingMergeTree)
CREATE TABLE user_events_hourly_local ON CLUSTER ck_cluster
(
  event_hour      DateTime,
  page            LowCardinality(String),
  city            LowCardinality(String),
  uv_state        AggregateFunction(uniqExact, UInt64),
  pv              UInt64
)
ENGINE = ReplicatedAggregatingMergeTree(
  '/clickhouse/tables/{shard}/user_events_hourly',
  '{replica}'
)
PARTITION BY toYYYYMM(event_hour)
ORDER BY (event_hour, page, city);

-- 2. 创建物化视图(数据流向)
CREATE MATERIALIZED VIEW user_events_hourly_mv ON CLUSTER ck_cluster
TO user_events_hourly_local
AS
SELECT
  toStartOfHour(event_time) AS event_hour,
  page,
  city,
  uniqExactState(user_id) AS uv_state,   -- State 类型存中间状态
  count() AS pv
FROM user_events_local
GROUP BY event_hour, page, city;

-- 3. 查询时合并状态
SELECT
  event_hour,
  page,
  uniqExactMerge(uv_state) AS uv,        -- Merge 完成最终聚合
  sum(pv) AS pv
FROM user_events_hourly_local
WHERE event_hour >= today()
GROUP BY event_hour, page;
-- 50ms,1 亿用户的小时聚合

MaterializedMySQL CDC

-- ClickHouse 直接订阅 MySQL binlog,实时同步
SET allow_experimental_database_materialized_mysql = 1;

CREATE DATABASE order_mirror
ENGINE = MaterializedMySQL(
  'mysql-host:3306',
  'orders_db',
  'replicator',          -- MySQL 用户
  'password'
)
SETTINGS
  allow_startup_database_without_connection_to_master = true,
  materialized_mysql_tables_list = 'orders,order_items';

-- 自动创建对应表,持续同步
-- MySQL 写 → binlog → ClickHouse 应用
-- 延迟一般 1-3 秒

-- 业务侧效果:OLTP 写 MySQL,OLAP 查 ClickHouse
SELECT toDate(created_at) AS d, sum(amount), count()
FROM order_mirror.orders
WHERE created_at >= now() - INTERVAL 7 DAY
GROUP BY d ORDER BY d;

集群部署



  
    
      
        true
        
          ck-1-1
          9000
        
        
          ck-1-2
          9000
        
      
      
        true
        
          ck-2-1
          9000
        
        
          ck-2-2
          9000
        
      
      
        true
        
          ck-3-1
          9000
        
        
          ck-3-2
          9000
        
      
    
  

  
    01
    ck-1-1
  

  
    zk-12181
    zk-22181
    zk-32181

ClickHouse vs Doris 对比

维度              ClickHouse        Doris (Apache Doris 2.x)
================================================
查询性能          极强(聚合)      强(MPP + 向量化)
写入吞吐          50w-100w/s        20w-50w/s
更新支持          弱(ReplacingMT)  原生 Unique Key 表
JOIN              一般(分布式 JOIN 弱)强(支持 Colocate JOIN)
SQL 友好性        中(ClickHouse 方言)高(MySQL 兼容)
运维复杂度        中(集群配置繁琐) 低(FE/BE 架构清晰)
社区中文支持      中                高(中国主导)
适合场景          日志 / 时序 / 指标 BI / 报表 / 实时数仓

我们的选择逻辑:
- 日志类只追加场景 → ClickHouse
- 需要 update / upsert / 多表 join 的 BI → Doris
- 极致写入吞吐 / 单表大宽表 → ClickHouse 更香

避坑清单

  1. 不要单条 INSERT,批量 5000-10w 条一批
  2. 枚举类型一律 LowCardinality
  3. 分区不要太细,按月最佳;part 超过 1000 个会变慢
  4. JOIN 是 ClickHouse 的弱项,大表 JOIN 用 dictGet 或预聚合
  5. ZooKeeper / Keeper 必须 HA,挂了写不进
  6. SELECT * 是大忌,只选需要的列(列存优势)
  7. 用 EXPLAIN 看查询计划,关注 read rows / part count
  8. OPTIMIZE TABLE FINAL 慎用,会重写所有 part
  9. backup 用 BACKUP TO 命令,不要手动复制目录
  10. 升级前看 changelog 的 breaking change,21→22→23 都有大变化

生产 3 年总结

ClickHouse 在 OLAP / 日志 / 时序场景几乎无敌,关键是表设计要按查询模式。ORDER BY、分区键、LowCardinality、物化视图,这四样配好了,百亿行查询毫秒返回不是梦。但它不是万能:OLTP 别用、JOIN 重的别用、update 多的别用。这次从 ES 迁过来三年,日均查询量翻了 5 倍,集群规模反而缩小一半。下一步在测 ClickHouse Cloud 和 ClickHouse Keeper(替代 ZK),后者已经稳定可生产。希望这篇能帮到正在做数据平台选型的团队。

—— 别看了 · 2026
声明：本站所有文章，如无特殊说明或标注，均为本站原创发布。任何个人或组织，在未征得本站同意时，禁止复制、盗用、采集、发布本站内容到任何网站、书籍等各类媒体平台。如若本站内容侵犯了原著者的合法权益，可联系我们进行处理 邮箱1846861578@qq.com。
ClickHouseOLAP大数据数据库架构
技术教程

Prometheus 高基数治理实战:1850 万 series 砍到 180 万

2026-5-19 12:06:43

技术教程

Flink CDC 同步 MySQL 到 Doris 半年踩坑实录:8 大坑全解

2026-5-19 12:11:01

0 条回复 A文章作者 M管理员
    暂无讨论，说说你的看法吧
❯

解锁会员权限

开通会员

解锁海量优质VIP资源

立刻开通
个人中心
购物车
优惠劵
今日签到
有新私信 私信列表
搜索
客服

  • 扫码打开当前页

返回顶部