【架构实战】ClickHouse实时分析：海量日志的秒级查询

一、查个UV，等了3小时

2020年做运营分析平台，日活数据从Hive里跑。运营想查"昨天各渠道的UV和订单转化率"，我在Hive上跑了3个小时才出结果。运营等不及，自己用Excel手算去了。

后来接了一个实时大屏需求，要求5秒刷新，展示当天实时GMV、订单量、TOP10商品——Hive彻底歇菜了。

这就是OLAP场景的经典矛盾：数据量大（每天数十亿条日志），查询要求快（秒级响应），传统方案做不到。

ClickHouse就是为这个场景而生的。

二、ClickHouse为什么这么快

2.1 列式存储——按列读写

【行存储（MySQL/InnoDB）】
Row1: |id|name|age|city|amount|time|
Row2: |id|name|age|city|amount|time|
Row3: |id|name|age|city|amount|time|

SELECT SUM(amount) FROM orders WHERE city = '北京';
→ 需要读取所有列，但只用到了city和amount

【列存储（ClickHouse）】
Col1(id): |1|2|3|...
Col2(name): |张三|李四|王五|...
Col3(age): |25|30|28|...
Col4(city): |北京|上海|北京|...
Col5(amount): |100|200|150|...
Col6(time): |t1|t2|t3|...

SELECT SUM(amount) FROM orders WHERE city = '北京';
→ 只读取city和amount两列，IO降低90%

2.2 向量化执行——批次处理

-- 传统数据库：逐行处理
-- 1000万行 = 1000万次函数调用

-- ClickHouse：按批次处理
-- 一批8192行，1000万行 ≈ 1220次函数调用
-- 配合SIMD指令，CPU效率极高

2.3 数据压缩

-- 由于同列数据相似度高，压缩率极高
CREATE TABLE events (
    event_date Date,
    event_time DateTime,
    user_id UInt64,
    event_type String,
    platform String
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_type, event_time)
SETTINGS index_granularity = 8192;

-- 原始数据 1TB → 压缩后 ≈ 100GB（压缩比10:1）

三、ClickHouse表引擎选择

引擎	适用场景	特点	注意事项
MergeTree	通用OLAP	按主键排序，自动合并	最常用，首选
ReplacingMergeTree	去重场景	自动去重	非实时去重
SummingMergeTree	预聚合	自动求和	只支持SUM
AggregatingMergeTree	复杂聚合	配合物化视图	需要理解聚合函数
CollapsingMergeTree	更新删除	CDC场景	使用复杂
Distributed	分布式表	分片查询	配合MergeTree使用

3.1 MergeTree建表实战

-- 用户行为日志表
CREATE TABLE events (
    event_date Date,
    event_time DateTime,
    user_id UInt64,
    session_id String,
    event_type LowCardinality(String),  -- 低基数字段优化
    page_url String,
    referrer String,
    device_type LowCardinality(String),
    os LowCardinality(String),
    duration UInt32,
    properties String                   -- JSON格式扩展字段
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)       -- 按月分区
ORDER BY (event_type, event_time, user_id)  -- 排序键（核心）
TTL event_time + INTERVAL 90 DAY        -- 90天过期自动删除
SETTINGS index_granularity = 8192;

3.2 ORDER BY设计——查询性能的命脉

-- 假设80%的查询都是按 event_type + 时间范围
-- 好的ORDER BY：
ORDER BY (event_type, event_time)

SELECT * FROM events 
WHERE event_type = 'click' 
  AND event_time >= '2025-06-01' 
  AND event_time < '2025-06-02';
-- 索引命中，扫描数据量极小

-- 差的ORDER BY：
ORDER BY (event_time, event_type)
-- 同样查询需要扫描整个时间范围，过滤效率低

四、实时数据写入架构

4.1 整体架构

业务服务 → Kafka → ClickHouse（实时）
                → HDFS（离线备份）
                
详细流程：
┌──────────┐    ┌──────────┐    ┌───────────────┐
│  Nginx    │───>│  Kafka    │───>│  Flink 清洗   │
│  日志采集  │    │  缓冲层    │    │  格式统一      │
└──────────┘    └──────────┘    └───────┬───────┘
                                        │
                               ┌────────▼───────┐
                               │  ClickHouse     │
                               │  物化视图预聚合  │
                               └────────────────┘

4.2 Kafka引擎——零代码接入

-- 创建Kafka消费表
CREATE TABLE events_kafka_queue (
    event_time DateTime,
    user_id UInt64,
    event_type String,
    page_url String,
    device_type String,
    duration UInt32
) ENGINE = Kafka()
SETTINGS
    kafka_broker_list = 'kafka1:9092,kafka2:9092',
    kafka_topic_list = 'user_events',
    kafka_group_name = 'clickhouse_consumer',
    kafka_format = 'JSONEachRow',
    kafka_num_consumers = 4;

-- 创建物化视图，自动将Kafka数据写入MergeTree
CREATE MATERIALIZED VIEW events_mv TO events AS
SELECT 
    toDate(event_time) AS event_date,
    event_time,
    user_id,
    event_type,
    page_url,
    device_type,
    duration
FROM events_kafka_queue;

五、实战查询场景

5.1 实时大屏——秒级聚合

-- 实时GMV（当天累计，5秒刷新）
SELECT 
    sum(multiIf(event_type = 'order', amount, 0)) AS gmv,
    countIf(event_type = 'order') AS order_cnt,
    countIf(event_type = 'pv') AS pv,
    uniqExactIf(user_id, event_type = 'pv') AS uv
FROM events
WHERE event_date = today()
  AND event_time >= now() - INTERVAL 5 SECOND;

-- 执行时间：50ms（数据量：最近5秒约100万行）

5.2 漏斗分析

-- 用户行为漏斗：首页→商品详情→加入购物车→下单
SELECT 
    windowFunnel(300)(event_time, 
        event_type = 'home_page',
        event_type = 'product_detail',
        event_type = 'add_cart',
        event_type = 'place_order'
    ) AS level,
    count() AS user_count
FROM events
WHERE event_date >= today() - 7
  AND event_type IN ('home_page', 'product_detail', 'add_cart', 'place_order')
GROUP BY level
ORDER BY level;

-- 结果：
-- Level 1: 100万（访问首页）
-- Level 2: 40万（查看详情）
-- Level 3: 8万（加购物车）
-- Level 4: 2万（下单）
-- 转化率：2万/100万 = 2%

5.3 留存分析

-- 次日留存率
SELECT 
    first_day,
    uniqExact(user_id) AS new_users,
    uniqExact(retention_day_2) AS day2_retention,
    round(day2_retention / new_users * 100, 2) AS retention_pct
FROM (
    SELECT 
        user_id,
        min(event_date) AS first_day,
        -- 次日是否活跃
        maxIf(event_date, event_date = first_day + 1) AS retention_day_2
    FROM events
    WHERE event_date >= today() - 30
      AND event_type = 'app_launch'
    GROUP BY user_id
)
WHERE first_day >= today() - 30
GROUP BY first_day
ORDER BY first_day;

六、踩过的坑

坑1：单条INSERT灾难

-- 错误：逐条插入
INSERT INTO events VALUES (...);  -- 1000次/秒 → ClickHouse崩溃

-- 正确：批量插入
INSERT INTO events VALUES (...), (...), (...);  -- 每次5000-10000条

坑2：大量的ALTER DELETE

-- 错误：频繁删除数据（Mutation操作）
ALTER TABLE events DELETE WHERE event_type = 'spam';

-- 正确：使用TTL或分区DROP
ALTER TABLE events DROP PARTITION '202501';

坑3：JOIN大表

ClickHouse的JOIN性能不如MySQL，尽量用物化视图预计算替代JOIN查询。

七、总结

ClickHouse解决的核心问题：海量数据下的秒级分析查询。

选型决策：

• 数据量小于1亿条 → MySQL/PostgreSQL足够
• 数据量1亿~100亿，OLAP场景 → ClickHouse
• 需要OLTP+OLAP → 考虑TiDB HTAP

核心实践：

1. ORDER BY设计决定查询性能，高频过滤字段放前面
2. 写入要批量，单条INSERT会拖垮整个集群
3. 物化视图做预聚合是提升查询速度的王牌
4. 用好TTL自动清理，避免磁盘爆满
5. 监控 system.merges 和 system.mutations，确保后台合并正常

ClickHouse上线后，运营大屏从3小时等待变成了3秒刷新，再也不用来催IT了。

---

个人观点，仅供参考