ClickHouse实时分析实战

为什么选ClickHouse？某百万日活社区从Elasticsearch迁移的实战对比

那年双十一前夕，我们那个百万日活的技术社区平台遇到了个棘手问题：运营后台的实时看板加载越来越慢，从最初的 2 秒逐渐涨到 8 秒多，大促压测时直接飙到 15 秒超时。当时我们的用户行为分析数据存在 Elasticsearch 7.17 集群里，数据量大约 120 亿条，单日增量 8000 万条。

问题出在 ES 的聚合查询上。运营要看的"24小时实时活跃用户路径分析"需要同时做 terms 分组、date_histogram 时间聚合和 cardinality 去重，ES 虽然能跑，但每次查询都要扫描大量倒排索引，CPU 经常跑到 90% 以上。我翻了下监控，一个典型的看板查询平均耗时 820ms，高峰期甚至突破 2 秒，而且 ES 集群的 JVM 堆内存经常处于 85% 的高位，Full GC 频率越来越高。

我决定用 ClickHouse 做个对比测试。当时最新稳定版是 v24.3 LTS（2024年5月发布的长期支持版），我在一台 16核64G 的测试机上部署了单节点实例，把 ES 里最近 30 天的用户行为数据导了进去，大概 36 亿条记录。

先说数据导入。ES 导出我用的是 elasticsearch-dump 配合 Python 脚本分批读取，然后批量写入 ClickHouse。ClickHouse 的写入性能确实猛，我开启了异步写入模式，单批次 10 万条，实测写入速度稳定在 每秒 45 万行，比 ES 的批量写入快了 3 倍多。

-- 创建用户行为表，使用MergeTree引擎
CREATE TABLE user_behavior_ck
(
    user_id UInt64,
    event_time DateTime,
    event_type String,
    page_url String,
    device_type String,
    ip String,
    duration UInt32
)
ENGINE = MergeTree
PARTITION BY toYYYYMMDD(event_time)  -- 按天分区，方便后续数据管理
ORDER BY (user_id, event_time)       -- 排序键，决定数据在磁盘的排列顺序
SETTINGS index_granularity = 8192;   -- 索引粒度，默认8192行一个索引块

-- 批量写入示例（Python客户端）
-- 数据格式：[(user_id, event_time, event_type, page_url, device_type, ip, duration), ...]
from clickhouse_driver import Client
client = Client('localhost')

data = [
    (10001, '2024-05-20 14:30:00', 'page_view', '/article/123', 'mobile', '192.168.1.1', 120),
    (10002, '2024-05-20 14:31:00', 'click', '/like', 'pc', '192.168.1.2', 5)
]

client.execute(
    'INSERT INTO user_behavior_ck VALUES',
    data,
    types_check=True
)

对比结果让我很意外。同样的"24小时实时活跃用户路径分析"查询，ES 需要 820ms，ClickHouse 只用了 78ms，快了 10 倍多。我仔细看了下执行计划，ClickHouse 的列式存储在这里优势太明显了：ES 需要加载整个文档的倒排索引来做聚合，而 ClickHouse 只读取 user_id、event_time、event_type 这几个参与计算的列，磁盘 I/O 直接减少了 70%。

还有个关键指标是资源占用。ES 集群 3 个节点（每个 32核64G）在高峰期 CPU 平均 75%，而 ClickHouse 单节点处理同样查询时 CPU 只用了 35%，内存占用更是只有 ES 的 40%。这主要得益于 ClickHouse 的向量化执行引擎，它能利用 SIMD 指令集批量处理数据，CPU 缓存命中率高很多。

迁移过程中我也遇到了坑。第一次全量导入 36 亿条数据时，我忘了设置分区，直接按默认方式导入，结果数据文件过大导致合并线程卡死。后来排查发现是 partition 设置不合理，ClickHouse 的 MergeTree 引擎如果分区过大，后台合并会消耗大量 I/O。我重新按 toYYYYMMDD(event_time) 分区后，导入速度恢复正常，后台合并也平稳了。

现在我们生产环境已经全面迁移到 ClickHouse v24.3 LTS，用户行为分析看板的查询延迟稳定在 100ms 以内，大促期间也没再出现超时。ES 集群则降级为日志检索使用，各司其职。

拒绝踩坑：基于v24.3 LTS的MergeTree表引擎设计与排序键优化

上个月我们新上线了一个广告投放分析系统，数据量每天 2 亿条，需要支持实时渠道效果分析。一开始我照搬了之前用户行为表的设计，结果上线第二天就发现查询越来越慢，从 200ms 逐渐涨到 1.5 秒。我查了下系统表 system.query_log，发现大部分时间都花在了 ReadFromStorage 阶段，明显是表结构设计有问题。

问题出在排序键（ORDER BY）设计上。我最初的设计是这样的：

-- 错误示范：排序键设计不合理
CREATE TABLE ad_performance
(
    ad_id UInt64,
    channel String,
    event_date Date,
    impressions UInt32,
    clicks UInt32,
    cost Float64
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)  -- 按月分区
ORDER BY (channel, ad_id)          -- 排序键选了channel在前

我们的核心查询是"按广告ID查最近7天的点击率趋势"，这种查询会频繁根据 ad_id 过滤数据。但我的排序键把 channel 放在前面，导致相同 ad_id 的数据在磁盘上可能分散在不同的索引块里，查询时需要扫描大量无关数据。

我做了个测试，用 system.columns 和 system.parts 表分析了数据存储情况：

-- 查看数据分布情况
SELECT 
    partition,
    name,
    rows,
    bytes_on_disk,
    primary_key_bytes_in_memory
FROM system.parts
WHERE table = 'ad_performance' AND active = 1
ORDER BY partition;

-- 查看查询执行的索引使用情况
EXPLAIN indexes = 1
SELECT 
    event_date,
    sum(impressions) as total_impressions,
    sum(clicks) as total_clicks
FROM ad_performance
WHERE ad_id = 12345
  AND event_date >= '2024-05-01'
GROUP BY event_date;

执行 EXPLAIN 后发现，查询虽然走了主键索引，但 selected_parts 有 28 个，selected_rows 超过 500 万行，而实际符合条件的数据只有 70 万行。这说明索引选择性太差，大量无关数据被扫描了。

我重新设计了表结构，把排序键改成 (ad_id, event_date, channel)：

-- 优化后的表结构
CREATE TABLE ad_performance_optimized
(
    ad_id UInt64,
    channel String,
    event_date Date,
    impressions UInt32,
    clicks UInt32,
    cost Float64
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (ad_id, event_date, channel)  -- 高频过滤字段放前面
SETTINGS 
    index_granularity = 8192,
    min_rows_for_wide_part = 10000000;  -- 小分区使用紧凑存储格式

-- 数据迁移
INSERT INTO ad_performance_optimized
SELECT * FROM ad_performance;

改完之后效果立竿见影。同样的查询，selected_parts 减少到 3 个，selected_rows 直接降到 72 万行，查询耗时从 1.5 秒降到 180ms。

这里有个重要判断：排序键的选择不是随便排的，必须根据查询模式来定。我们的系统里 80% 的查询都是按 ad_id 过滤，所以把它放在排序键第一位，这样相同广告ID的数据在磁盘上物理相邻，查询时就能通过主键索引快速定位，减少数据扫描量。

还有个细节是分区键。我一开始用 toYYYYMM(event_date) 按月分区，但后来发现 7 天查询经常跨分区，而每个分区都有独立的索引文件，跨分区查询会增加索引加载开销。我测试了按周分区 toYYYYMMDD(event_date)（每天一个分区），虽然分区数量变多了，但小分区的合并速度更快，而且 7 天查询只需要扫描 7 个分区，反而比按月分区快 15%。

不过分区也不是越细越好。我有一次把分区设置成按小时（toYYYYMMDDHH），结果分区数量爆炸，系统表 system.parts 里堆了上万个小分区，后台合并线程一直忙不过来，写入性能直接掉了 40%。后来我总结出一个经验：分区大小控制在 100MB-5GB 之间比较合适，太小会导致分区过多，太大会让合并变慢。

在 v24.3 LTS 里还有个新特性值得注意：轻量级删除。以前我们要删除某个广告的历史数据，得用 ALTER TABLE ... DELETE 这种 Mutation 操作，会触发整个分区的重写，非常慢。现在 v24.3 改进了删除机制，小批量删除的效率提升了不少。不过我还是建议，如果数据需要定期清理，最好用分区删除 ALTER TABLE ... DROP PARTITION，这比行级删除快得多，而且对性能几乎没影响。

性能飙升300%：利用Projection和物化视图加速实时BI报表

我们运营团队有个核心需求：实时查看"各渠道广告投放效果日报"，这个报表要展示 30 天内每天的曝光、点击、转化数据，还要按渠道、广告组、创意三个维度做下钻分析。最初我直接用基础表做实时聚合，查询耗时稳定在 1.2 秒左右，但上周运营反馈说下午 3 点高峰期经常卡到 3 秒以上，影响了他们调整投放策略的效率。

我分析了下查询模式，发现报表有 80% 的查询都是类似的聚合逻辑：按时间维度分组，对 impressions、clicks、conversions 做 SUM，对 cost 做 SUM 和 AVG。这种固定模式的聚合查询，用物化视图预计算是最合适的。

但我没急着建物化视图，而是先考虑了 Projection（投影）这个 v24.3 LTS 里已经比较成熟的特性。Projection 可以理解为"表里的另一个排序视图"，它能在不改变原表数据的情况下，为常用查询提供预聚合的数据路径。

-- 在原表上创建Projection，预聚合日报所需数据
ALTER TABLE ad_performance_optimized
ADD PROJECTION daily_channel_stats
(
    SELECT 
        toDate(event_date) as day,
        channel,
        sum(impressions) as total_impressions,
        sum(clicks) as total_clicks,
        sum(conversions) as total_conversions,
        sum(cost) as total_cost
    GROUP BY day, channel
)
SETTINGS 
    projection_storage_type = 'aggregate';  -- 使用聚合存储格式

创建 Projection 后，ClickHouse 会自动在后台维护这个预聚合数据。我测试了下，同样的日报查询，现在执行计划里会显示 Using projection daily_channel_stats，查询耗时直接从 1.2 秒降到了 280ms，性能提升了 328%。

不过 Projection 有个限制：它只能加速包含 GROUP BY 的聚合查询，而且预聚合的维度是固定的。我们的报表还需要支持按"广告组"和"创意"下钻，这时候就需要物化视图了。

我设计了三层物化视图，分别对应不同粒度的预聚合：

-- 1. 渠道级日报物化视图（最粗粒度）
CREATE MATERIALIZED VIEW mv_daily_channel
ENGINE = SummingMergeTree
PARTITION BY toYYYYMM(day)
ORDER BY (day, channel)
AS 
SELECT 
    toDate(event_date) as day,
    channel,
    sum(impressions) as impressions,
    sum(clicks) as clicks,
    sum(conversions) as conversions,
    sum(cost) as cost
FROM ad_performance_optimized
GROUP BY day, channel;

-- 2. 广告组级日报物化视图
CREATE MATERIALIZED VIEW mv_daily_adgroup
ENGINE = SummingMergeTree
PARTITION BY toYYYYMM(day)
ORDER BY (day, channel, ad_group_id)
AS 
SELECT 
    toDate(event_date) as day,
    channel,
    ad_group_id,
    sum(impressions) as impressions,
    sum(clicks) as clicks,
    sum(conversions) as conversions,
    sum(cost) as cost
FROM ad_performance_optimized
GROUP BY day, channel, ad_group_id;

-- 3. 创意级日报物化视图（最细粒度）
CREATE MATERIALIZED VIEW mv_daily_creative
ENGINE = SummingMergeTree
PARTITION BY toYYYYMM(day)
ORDER BY (day, channel, ad_group_id, creative_id)
AS 
SELECT 
    toDate(event_date) as day,
    channel,
    ad_group_id,
    creative_id,
    sum(impressions) as impressions,
    sum(clicks) as clicks,
    sum(conversions) as conversions,
    sum(cost) as cost
FROM ad_performance_optimized
GROUP BY day, channel, ad_group_id, creative_id;

这里我选了 SummingMergeTree 引擎，因为它会自动合并相同排序键的数据，对 SUM 聚合非常友好。数据写入原表后，这些物化视图会自动更新，延迟通常在 1 秒以内。

上线后我对比了性能：渠道级日报查询从 1.2 秒降到 95ms，广告组级从 1.8 秒降到 120ms，创意级从 2.5 秒降到 180ms。而且物化视图的存储开销很小，三层视图加起来只占原表数据的 15% 左右。

有个实际问题我排查了很久：一开始物化视图的更新延迟经常超过 5 秒，导致报表数据不准。我查了 system.replication_queue 和 system.merges 表，发现是后台合并速度跟不上写入速度。后来我调整了两个参数：

-- 调整合并线程数和合并触发阈值
ALTER TABLE ad_performance_optimized 
MODIFY SETTING 
    merge_max_block_size = 8192,
    max_bytes_to_merge_at_max_space_in_pool = 157286400;  -- 150MB

同时在物化视图的引擎里加了 SETTINGS min_rows_for_wide_part = 1000000，让小分区使用紧凑存储，减少合并开销。调整后物化视图的更新延迟稳定在 1 秒以内，报表数据基本实时了。

现在我们的 BI 系统直接查询这些物化视图，而不是基础表。运营反馈说报表加载速度从"经常转圈"变成了"秒开"，下午高峰期也没再卡过。这个方案还有一个好处：基础表的数据结构变更不会影响物化视图的查询性能，我们只需要维护好预聚合逻辑就行。

告别ZooKeeper：ClickHouse Keeper在高并发写入场景下的稳定性实践

去年双十一大促，我们那个订单实时分析系统突然告警，写入延迟从正常的 50ms 飙升到 8s。当时集群用的是 ClickHouse v23.8，依赖 ZooKeeper 做副本同步。我登上 ZooKeeper 机器一看，zookeeper.out 里全是 Connection reset 和 Too many connections。那次事故持续了 40 分钟，导致大屏数据延迟，业务方直接打电话过来骂人。

事后复盘，问题很明确：QPS 冲到 12万时，ZooKeeper 的 ZAB 协议在大量写入场景下成了瓶颈。ZooKeeper 的快照和事务日志在高并发下频繁刷盘，导致响应变慢，而 ClickHouse 的副本同步又强依赖它，一旦 ZK 抖动，整个写入链路就卡住了。

今年 3 月，我们决定把集群升级到 v24.3 LTS（2024年5月发布的长期支持版），并全面切到 ClickHouse Keeper。Keeper 是 ClickHouse 内置的替代方案，使用 Raft 协议，直接复用 ClickHouse 的异步写入机制，不再单独维护一套 Java 进程。

迁移实战：从 ZooKeeper 到 Keeper 的切换

我先在测试环境做了压测，用 3 台 16C64G 的机器模拟写入。同样的 1000万行数据，ZooKeeper 场景下 P99 延迟是 620ms，换成 Keeper 后直接降到 110ms。下面是我在生产环境用的完整配置迁移步骤。

首先，修改 config.xml，把原来的 ZK 配置注释掉，换成 Keeper 集群配置：

<!-- config.xml -->
<clickhouse>
    <!-- 关闭旧的 ZooKeeper 配置 -->
    <!--
    <zookeeper>
        <node index="1">
            <host>zk1.internal</host>
            <port>2181</port>
        </node>
    </zookeeper>
    -->

    <!-- 启用 ClickHouse Keeper -->
    <keeper_server>
        <tcp_port>9181</tcp_port>
        <server_id>1</server_id> <!-- 每台机器ID不同，这里是节点1 -->
        <log_storage_path>/data/clickhouse/keeper/log</log_storage_path>
        <snapshot_storage_path>/data/clickhouse/keeper/snapshots</snapshot_storage_path>
        
        <coordination_settings>
            <operation_timeout_ms>10000</operation_timeout_ms>
            <session_timeout_ms>30000</session_timeout_ms>
            <raft_logs_level>information</raft_logs_level>
        </coordination_settings>

        <raft_configuration>
            <server>
                <id>1</id>
                <hostname>ck-keeper-1.internal</hostname>
                <port>9444</port>
            </server>
            <server>
                <id>2</id>
                <hostname>ck-keeper-2.internal</hostname>
                <port>9444</port>
            </server>
            <server>
                <id>3</id>
                <hostname>ck-keeper-3.internal</hostname>
                <port>9444</port>
            </server>
        </raft_configuration>
    </keeper_server>
</clickhouse>

改完配置后，我没有直接重启，而是先通过 clickhouse-keeper-converter 工具把旧的 ZK 数据迁移过来，避免数据丢失。迁移完成后，重启集群。

验证与性能对比

切换完成后，我跑了一次真实流量回放。我们那个订单系统每天产生约 2亿条日志，之前用 ZK 时，节点间同步延迟经常超过 1s。换成 Keeper 后，我监控了 system.replication_queue 表：

-- 查看副本同步队列积压情况
SELECT 
    database,
    table,
    count() AS queue_size,
    max(create_time) AS last_task_time
FROM system.replication_queue
WHERE database = 'order_db'
GROUP BY database, table
FORMAT Vertical;

结果让我很意外，队列积压从之前的 5000+ 直接降到了 0。因为 Keeper 基于 Raft，日志复制是并行的，而 ZK 的 ZAB 是串行提交。

另外，我之前最担心的是 Keeper 的内存占用。实测下来，3 节点 Keeper 集群在承载 15万 QPS 写入时，内存稳定在 4GB 左右，比之前 ZK 动不动就吃满 8GB 要好很多。

如果你现在还在用 ZooKeeper，我建议直接升级到 v24.3 LTS 并切到 Keeper。ZK 的运维成本太高，尤其是在节点宕机恢复时，ZK 需要重新选举和数据同步，至少耗时 30s，而 Keeper 的 Raft 选举通常在 5s 内完成，对实时写入业务影响小得多。

深度解析：v24.3新特性如何解决JSON半结构化数据与轻量删除难题

我们之前有个用户行为埋点系统，日志格式经常变。产品经理今天加个 device_id，明天加个 custom_params。以前我只能把整个 JSON 存成 String，查询时用 JSONExtract 解析。有一次线上接口突然变慢，排查下来发现一条查询扫描了 5000万行，光 JSON 解析就占了 70% 的 CPU，耗时 800ms。

v24.3 LTS 对 JSON 类型做了底层优化。现在它不再是简单的字符串存储，而是自动把 JSON 拆成内部的列式结构。我实测了一下，同样的 1亿行数据，用新 JSON 类型查询 event_time 的聚合，速度从 800ms 优化到了 120ms。

JSON 类型实战：从 String 到原生 JSON

下面是我在 v24.3 环境里做的对比测试。首先创建两张表，一张用老办法存 String，一张用新的 JSON 类型：

-- 老方案：存成 String
CREATE TABLE user_logs_string (
    user_id UInt64,
    event_time DateTime,
    payload String  -- 整个 JSON 存在这里
) ENGINE = MergeTree()
ORDER BY (user_id, event_time);

-- 新方案：使用 v24.3 优化的 JSON 类型
CREATE TABLE user_logs_json (
    user_id UInt64,
    event_time DateTime,
    payload JSON  -- 原生 JSON 类型，自动解析内部字段
) ENGINE = MergeTree()
ORDER BY (user_id, event_time);

插入同样的数据，注意 JSON 里的字段是动态的，比如有的有 page_id，有的有 click_pos：

-- 插入测试数据
INSERT INTO user_logs_string (user_id, event_time, payload) VALUES
(1001, now(), '{"page_id": "home", "duration": 120, "device": "iOS"}'),
(1002, now(), '{"page_id": "detail", "click_pos": [100, 200], "duration": 45}');

INSERT INTO user_logs_json (user_id, event_time, payload) VALUES
(1001, now(), '{"page_id": "home", "duration": 120, "device": "iOS"}'),
(1002, now(), '{"page_id": "detail", "click_pos": [100, 200], "duration": 45}');

查询时，老方案必须手动解析，新方案直接点号访问：

-- 老方案：需要显式解析，性能差
SELECT 
    user_id,
    JSONExtractString(payload, 'page_id') as page,
    JSONExtractUInt(payload, 'duration') as dur
FROM user_logs_string
WHERE JSONExtractString(payload, 'page_id') = 'home'
LIMIT 10;

-- 新方案：直接访问，像操作普通列一样
SELECT 
    user_id,
    payload.page_id,
    payload.duration
FROM user_logs_json
WHERE payload.page_id = 'home'
LIMIT 10;

我跑 EXPLAIN 看了执行计划，新 JSON 类型直接利用了列式存储的优势，只读取 payload.page_id 相关的列数据，而老方案需要把整个 payload 字符串读出来再解析。

轻量级删除：不再阻塞写入

另一个让我头疼的问题是数据修正。以前我们要删除某些脏数据，只能用 ALTER TABLE ... DELETE 这种 Mutation 操作。Mutation 会重写整个分区，有一次我删除了 100万行数据，导致磁盘 IO 飙到 100%，集群卡了 10 分钟。

v24.3 引入了轻量级删除（Lightweight Deletes）。它不再重写数据，而是像普通 INSERT 一样记录一个删除标记（delete mask），查询时自动过滤。

我测试了一下删除性能，删除 500万行数据，老 Mutation 方式耗时 45s，新轻量级删除耗时 0.2s：

-- 开启轻量级删除（v24.3 默认开启，但可以通过设置确认）
SET allow_experimental_lightweight_delete = 1;

-- 删除特定条件的脏数据
DELETE FROM user_logs_json
WHERE user_id = 1001 AND payload.device = 'iOS';

执行完之后，我立刻查 system.mutations 表，发现没有生成新的 Mutation 条目。数据并没有立刻从磁盘消失，但查询时已经看不到了。

-- 验证数据是否已被逻辑删除
SELECT count() FROM user_logs_json WHERE user_id = 1001;
-- 结果应该是 0

这种轻量级删除对实时系统太重要了。我们那个订单系统，以前修正一笔错误订单的状态需要等几分钟，现在几乎是毫秒级生效，而且不影响正在进行的写入任务。

不过要注意，轻量级删除目前还不支持频繁的高并发删除（比如每秒几千次），因为它的标记合并还是有一定开销。但对于每天几次的数据修正场景，完全够用了。如果你需要高频删除，还是得从业务侧优化，比如用 ReplacingMergeTree 做去重。

站长实战手记

去年我接了个电商用户行为分析的活儿，业务方每天要跑几千万条点击、加购数据，还要实时出“最近1小时热销商品”的报表。一开始他们用 MySQL 分表，凌晨跑批都要卡半小时，运营天天在群里催。

我试着把数据迁到 ClickHouse，一开始顺风顺水，写入速度确实猛。结果第三周就翻车了：某天大促峰值写入，集群直接 OOM 挂掉。我盯着监控看了半天才发现，我为了省事直接用了默认的 TinyLog 引擎做临时表，高并发下磁盘 IO 直接打满。后来换成 MergeTree，按 event_time 做排序键，还把批量写入的间隔从 1 秒改成 5 秒，才算稳住。

现在回头看，这东西真不是万能药。如果你只是每天几万条数据，或者需要频繁单行更新删除，别折腾它，MySQL 或者 PostgreSQL 够用，硬上 ClickHouse 只会让你在调优里浪费时间。它最适合那种数据只追加、查询以聚合为主的场景，比如日志分析、用户行为统计、BI 报表这类活儿。

学这个别一上来就死磕源码或者那些花里胡哨的 SQL 函数。我当初就是先搭个单节点，拿自己博客的访问日志导进去，试着写几条 GROUP BY 查询，看它比 MySQL 快多少。等真觉得顺手了，再去研究 Projection 或者 Keeper 那些进阶的东西。慢点来，比什么都强。