C Clara轻量论坛系统
登录 注册

ClickHouse 列式数据库入门：大数据量实时分析方案

jaydd
jaydd 正式会员正式会员认证极客认证极客
发布于 2026-10-23 11:43 ·5 浏览 ·7 回复
# ClickHouse # 列式数据库 # 实时分析 # MergeTree
内容摘要

介绍如何用 Docker 部署 ClickHouse，并以 MergeTree 建表、灌入测试数据完成百万行实时聚合查询，强调其适合列式统计聚合、不适合高频单行更新与事务，同时提醒文件句柄限制、排序键分区和 TTL 配置会影响查询性能。

学完这篇，你能在一台普通机器上跑起 ClickHouse，独立完成建表、灌数据和一条百万行级别的实时聚合查询，并知道哪些坑会让查询从毫秒变分钟。

第一步：先弄清它适合干什么

ClickHouse 是列式存储的分析型数据库。数据按列而不是按行存放，同一列数据类型一致、压缩率高，聚合扫描时只读需要的列，所以 SUM、COUNT、GROUP BY 这类查询比 MySQL 快一到三个数量级。反过来，它不擅长高频单行更新和删除、不支持完整事务、点查单行也不比 MySQL 强。

典型用法：把 MySQL 里的业务数据同步一份过来，专做统计。比如论坛的浏览日志、发帖记录、签到流水，原始表继续留在 MySQL 跑业务，ClickHouse 只负责"昨天哪个版块最热""近 7 天新增用户趋势"这类问题。

注意：不要一上来就把主业务库整体迁到 ClickHouse，它没有事务和行级更新，改一条数据要靠 ALTER TABLE ... UPDATE 的异步 mutation，代价很高。

第二步：用 Docker 起一个实例

docker run -d --name clickhouse \
  --ulimit nofile=262144:262144 \
  -p 8123:8123 -p 9000:9000 \
  -v $(pwd)/ch-data:/var/lib/clickhouse \
  clickhouse/clickhouse-server:24.8

两个端口分工明确：8123 是 HTTP 接口，浏览器和 curl 都能直接打；9000 是原生 TCP 协议，官方客户端走这个。启动后执行 docker exec -it clickhouse clickhouse-client 进入命令行。

注意：如果容器起不来，九成是 --ulimit nofile 没加或者宿主机文件句柄上限太低，ClickHouse 对打开文件数很敏感。

第三步：建表，核心是 MergeTree

ClickHouse 的表引擎里，MergeTree 家族是绝对主力，其余大多是它的变体。语法关键在三个子句：

CREATE TABLE bbs.page_views (
    event_date Date,
    event_time DateTime,
    post_id    UInt32,
    board_id   UInt16,
    user_id    UInt32,
    is_guest   UInt8,
    stay_ms    UInt32
) ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (board_id, event_date, post_id)
TTL event_date + INTERVAL 90 DAY;
  • ORDER BY 是排序键，也是稀疏索引，决定了数据在磁盘上的物理顺序。查询里带排序键前缀的条件才能高效裁剪数据。
  • PARTITION BY 控制分区，按月就够，别按天分太细。
  • TTL 让过期数据自动清理，日志表几乎必配。

第四步：灌数据

小批量测试直接用 INSERT ... SELECT 造数据最快：

INSERT INTO bbs.page_views
SELECT
    today() - rand() % 30,
    now() - rand() % 86400,
    rand() % 10000,
    rand() % 50,
    rand() % 5000,
    rand() % 2,
    rand() % 60000
FROM numbers(5000000);

numbers() 是 ClickHouse 内置的数列函数，造几百万行测试数据一条语句搞定。真实数据用 clickhouse-client --query "INSERT INTO ... FORMAT CSV" < data.csv 批量导入，每批几十万行最合适。

注意：绝对不要一行一条 INSERT 往 ClickHouse 里写，每次插入都会生成一个新的数据片段，碎片多了后台合并跟不上，性能和磁盘都会出问题。批量写、每批至少几千行。

第五步：写分析查询

SELECT
    board_id,
    count() AS pv,
    uniqExact(user_id) AS uv,
    round(avg(stay_ms)) AS avg_stay_ms
FROM bbs.page_views
WHERE event_date >= today() - 7
GROUP BY board_id
ORDER BY pv DESC
LIMIT 10;

count()、uniqExact() 是 ClickHouse 的原生聚合函数，百万级数据通常几十毫秒出结果。如果要长期跑同一套统计，建个物化视图让写入时顺带算好，查询时直接读结果表。

第六步：几个必知优化点

  1. 查询条件尽量命中 ORDER BY 的前缀字段，WHERE board_id = 3 比 WHERE user_id = 123 快得多。
  2. 低基数字符串列（比如版块名、状态）用 LowCardinality(String)，能显著降低存储和扫描量。
  3. 永远别写 SELECT *，列式存储的优势就是只读必要的列。
  4. 时间字段用 Date/DateTime 而不是字符串，否则分区和 TTL 都没法用。
  5. 表结构建好后改字段类型代价很大，建模时想清楚字段粒度。

注意：uniq() 是近似去重，快但结果有小误差；要精确 UV 用 uniqExact()，慢一些。选择哪个取决于你的报表能不能接受 1% 左右的偏差。

小结

  • ClickHouse 是列式分析库，专治大表聚合，不替代 MySQL 跑业务。
  • 部署用 Docker 最快，记住 8123（HTTP）和 9000（原生）两个端口。
  • 建表一律 MergeTree，ORDER BY 决定查询快慢，PARTITION BY 按月，日志表配 TTL。
  • 写入必须批量，单行插入会产生大量碎片。
  • 查询只取需要的列，时间字段用日期类型，低基数列加 LowCardinality。
  • 精确去重用 uniqExact，能接受误差用 uniq。
本文转载自 Clara轻量论坛系统，原文地址：https://www.leleweb.cn/thread-951.html
转载请注明出处，版权归原作者所有。
帖子评分全站评分榜 →
—/ 5暂无评分
还没有评分，来打第一分吧
上一篇Elasticsearch 搜索引擎实战：索引 / …

全部回复 7

wbcm
wbcm 见习用户见习用户 1楼 2026-10-23 11:48

结尾那段 INSERT ... SELECT 正好截断了，先补上，因为灌数据这块很多人卡的地方都在这里：

INSERT INTO bbs.page_views
SELECT
    today() - number % 90          AS event_date,
    now() - number * 30            AS event_time,
    number % 50000                 AS post_id,
    number % 60                    AS board_id,
    toUInt8(number % 5 = 0)        AS is_guest,
    number % 5000                  AS user_id,
    rand() % 120000                AS stay_ms
FROM numbers(5000000);

numbers(N) 是 ClickHouse 自带的一张虚拟表，一行代码就能造出千万级数据，做压测非常好用。上面这一条数据量大约 500 万行、每条 3 秒左右，足够做压测。

补几个实践中的点：

1. board_id 建议用 LowCardinality(UInt16)。版块数量就几十个，字典编码后压缩率和扫描都更好；同理帖子状态、用户组这类也用得上。基数超过几万的列不要硬套 LowCardinality，反而会变慢。

2. 千万别循环单行 INSERT。这是新手最容易踩的坑：每次 INSERT 都会在磁盘上生成一个 part，攒到几百个就触发 Too many parts 报错，写入直接卡住。要么用 INSERT ... SELECT 批量灌，要么在应用侧攒够 1 万到 10 万行再批量提交一次；官方的 clickhouse-client --query "INSERT ... FORMAT JSONEachRow" 配合 stdin 也行。

3. 排序键顺序有讲究。你写的 (board_id, event_date, post_id) 是对的——把查询中最常用的等值过滤列放最前面，才能让稀疏索引起作用；如果查询常按时间范围扫描，把 event_date 往前挪。只有带上排序键前缀的条件，才能真正裁剪数据、跳过大量数据块。

4. 预聚合用物化视图，不要每次都做全表扫描。“近 7 天新增用户趋势”这类固定报表，建一张 SummingMergeTree 加物化视图，写入时自动按天聚合，查询就从秒级变成毫秒级。论坛场景尤其推荐这么做。

回到论坛这个例子：把 MySQL 的业务数据同步一份到 ClickHouse，统计完全不用实时同步——每天凌晨用计划任务跑一次增量同步就够了。Clara 本身就有计划任务体系（Cron::register 注册，前台访问懒触发，也可以配 crontab 打 /cron 入口），加个插件每天把浏览和签到流水搬过去，比在 MySQL 上硬扛统计查询划算得多。

最后一个坑：TTL 和分区别一起设得太细。按月分区配 90 天 TTL 是合理的；如果按天分区再配 90 天 TTL，就会产生 90 个活跃分区，后台合并线程的压力会明显变大。

东来东往
东来东往 正式会员正式会员认证极客认证极客 #1283 2楼 2026-10-23 11:54
wbcm：结尾那段 `INSERT ... SELECT` 正好截断了，先补上，因为灌数据这块很多人卡的地方都在这里： ```sql INSERT INTO bbs.p…

补得挺全，numbers() 造数和 Too many parts 这两个确实是新手最先遇到的问题，多数人卡在灌数据阶段，也正因为这两点。顺着物化视图那段，我再补三个容易出问题的地方。

SummingMergeTree 只对 ORDER BY 之外的数值列求和，非数值列和维度列在合并时只会取任意一行的值。因此，版块名、日期这类维度字段必须全部放进 ORDER BY，否则合并后数据会和原表对不上；聚合列最好显式声明 SummingMergeTree((pv, uv, stay_ms))，不要依赖默认行为，否则哪天新增一个数值列，它会被悄悄当成求和列。

物化视图是写入触发器，不是自动回填。建完 MV 后，历史数据一条都不会进去，得手动 INSERT INTO agg SELECT ... FROM page_views 回填一次；同时聚合表本身也要配 TTL，很多人只给源表配了 TTL，结果明细过期清干净了，聚合表越积越大。另外，大批量 INSERT 时 MV 是同步计算的，会拖慢写入，所以灌历史数据前先临时禁用 MV 会快不少。

如果应用侧确实攒不了批，比如实时打点，可以在连接串加 async_insert=1&wait_for_async_insert=1，让服务端自己合并小写入，能避免 part 数量失控，代价是延迟会略有增加。

顺带说一句，造数用 numbers_mt 多线程会更快；另外 rand() 造出来的时间分布太均匀，用来测「近 7 天趋势」这类带时间过滤的查询，结果和真实数据差得比较多，建议 event_time 用 now() - rand() % (86400*90) 制造出倾斜。

Lypeeres
Lypeeres 见习用户见习用户 #1284 3楼 2026-10-23 11:59
东来东往：补得挺全，`numbers()` 造数和 `Too many parts` 这两个确实是新手最先遇到的问题，多数人卡在灌数据阶段，也正因为这两点。顺着物化视图那…

补的这几点都是实战里真会疼的地方，我只在东来那句基础上补两个更"要命"的细节：uv 那列其实不能求和，TTL 也别用默认的 DELETE 方式。

Summing 只做加法，UV 去重得换 AggregatingMergeTree。 你写的 SummingMergeTree((pv, uv, stay_ms)) 里，uv 如果按求和算，结果就是"每天访客数累加"，数值只会越来越高。去重列要存中间态：

CREATE TABLE bbs.board_daily (
    event_date Date,
    board_id   LowCardinality(UInt16),
    pv         UInt64,
    uv         AggregateFunction(uniq, UInt32),
    stay_ms    UInt64
) ENGINE = AggregatingMergeTree
ORDER BY (event_date, board_id);

-- 写入
SELECT ..., uniqState(user_id) FROM page_views GROUP BY ...
-- 查询必须 uniqMerge，直接 select uv 读到的是二进制中间态
SELECT event_date, board_id, sum(pv), uniqMerge(uv) FROM board_daily GROUP BY event_date, board_id;

这是"数字对不上"最常见的来源，比维度列没进 ORDER BY 还隐蔽，因为不报错、只是值离谱。另外维度列你说的没错，但更保险的做法是所有非聚合列一律进 ORDER BY，别指望合并时"取任意一行"。

TTL 建议加 ttl_only_drop_parts=1。 默认 TTL 是在合并时逐行过滤重写 part，日志表数据量大时会持续吃掉磁盘 IO，表现为查询周期性抖动。加上这个设置后，整个 part 全过期就直接删文件，几乎零开销——前提是分区粒度和 TTL 对齐，按月分区 + 90 天保留刚好是三整月，很干净。同理聚合表那条 TTL 也照这个配。

再补两个小的：建 MV 一定显式写 TO bbs.board_daily，不写会落到 .inner.xxx 隐藏表，回填时没法直接 INSERT，只能 drop 重建；async_insert 的 wait_for_async_insert 默认就是 1，压测时别为了吞吐改成 0，那样写入报错会完全静默。

你说的 rand() 时间分布太均匀这点很关键，实际还有个连带问题：event_time 不在排序键里，"近 7 天"这类过滤只能靠分区裁剪加跳数索引，如果这是高频查询，与其纠结索引，不如加个 projection（ALTER TABLE ... ADD PROJECTION p_time (SELECT * ORDER BY event_time)），查询会自动选路。

runyu
runyu 正式会员正式会员认证极客认证极客 #1285 4楼 2026-10-23 12:02
Lypeeres：补的这几点都是实战里真会疼的地方，我只在东来那句基础上补两个更"要命"的细节：uv 那列其实不能求和，TTL 也别用默认的 DELETE 方式。 **Summ…

uniqMerge 那段最值得先说清楚的是精度：uniq 是 HyperLogLog 近似算法，标准误差约 0.5%，百万级 uv 差几千是常态、不是 bug。如果 uv 要跟业务库对数（比如「昨天新增用户数」），要么换 uniqExact（内存随基数线性涨，版块级维度一般还能扛），要么用 uniqCombined(17) 把误差压到 0.1% 左右。这一点没有提前说明，后面核对数据时就会以为 MV 写错了。

再就是 AggregatingMergeTree 有个隐含限制：聚合表只能数据上卷，不能重新下钻。按 (event_date, board_id) 建的 MV，uniqMerge 能出到版块级、全站级，但拿不到单帖 uv，也拆不出小时粒度——维度在建表时就固定了。所以建之前先想清楚报表要出到哪一层，不确定就按最细的（带上 post_id）存，行数多一点，比以后重建 MV 回填要省事。

event_time 不在排序键的连带问题补两条解法：短期的可以加 projection（ALTER TABLE ... ADD PROJECTION p_time (SELECT * ORDER BY (event_date, event_time))），查询命中就自动改走它；更省事的是把「近 7 天趋势」这类查询直接交给按天聚合表，绕开明细扫描。

另外 TTL 支持列级，可以让 stay_ms 30 天过期、pv 留一年，列值重置为默认值而行还在，比整行删更省空间。回填完记着 OPTIMIZE ... FINAL 跑一遍再对数，否则合并还没发生时容易看到中间态。

一只肉包
一只肉包 正式会员正式会员认证极客认证极客 #1286 5楼 2026-10-23 12:12
runyu：`uniqMerge` 那段最值得先说清楚的是精度：`uniq` 是 HyperLogLog 近似算法，标准误差约 0.5%，百万级 uv 差几千是常态、不是 …

**uniq 精度这条最实用，但我要补一句：跟业务库对数时，差异多半不是 HLL 误差，先查去重口径。**

游客行、is_guest=1、爬虫流量都存在 page_views 里，uniqMerge 会把它们一起算进去，而业务库的“新增用户数”只算注册用户，这样对数时差几个百分点很正常。稳妥做法是在写入 MV 时就用组合器把口径固定住：uniqStateIf(user_id, is_guest = 0)，查询端对应 uniqMergeIf(uv, 1)，比事后在查询里 WHERE 过滤可靠得多。另外 uniqExact 在百万级、版块维度下内存大概几十到一百多 MB，真的能扛，别一上来就用近似值。

关于下钻限制，再补一个有利点：从 AggregatingMergeTree 往上卷是合法的：state 可以二次 merge，所以按最细粒度（带 post_id）建表后，版块级、全站级都靠 GROUP BY 直接出，不用另建 MV。“建细一点”的成本只在磁盘和写入，查询侧几乎不额外付出。

projection 那一条我想打个折：projection 会额外写一份数据，写入量增大、磁盘接近翻倍，日志表 90 天 TTL 还好，量大的站可能不划算。更轻的做法是跳数索引：INDEX idx_time event_time TYPE minmax GRANULARITY 1，每个 granule 几十字节，对时间范围裁剪足够用，先试它，不够再上 projection。

结尾提醒：OPTIMIZE TABLE ... FINAL 不要对整张表执行，大表会重写全部 part，磁盘 IO 会被占满。按分区跑，比如 OPTIMIZE TABLE bbs.board_daily PARTITION '202510' FINAL，一次一个分区。列级 TTL 也是合并时才重写列文件，改完配置别马上查，等下一轮 merge。

东来东往
东来东往 正式会员正式会员认证极客认证极客 #1287 6楼 2026-10-23 12:15
一只肉包：**uniq 精度这条最实用，但我要补一句：跟业务库对数时，差异多半不是 HLL 误差，先查去重口径。** 游客行、`is_guest=1`、爬虫流量都存在 …

口径这条我同意一半——必须写入时固定，但「查询端用 uniqMergeIf 兜底」要纠正：state 是不可分割的。

uniqMergeIf(uv, 1) 只能整体保留或整体跳过某个 state，没法从已经混入游客的 HLL 里把元素剔除出来。所以一旦写入时没过滤，查询端任何 -If 组合器都救不回来，只能回填重建。更稳妥的做法是写入侧同时存两列：uv_all AggregateFunction(uniq, UInt32) 和 uv_user AggregateFunction(uniqIf, UInt32) 对应 uniqStateIf(user_id, is_guest = 0)。按天聚合行数本来就少，多一列 state 的存储开销几乎可以忽略，两套口径随时对数，比事后推倒重建 MV 划算得多。

上卷合法这条同意，但「几乎不额外付出」要看基数：GROUP BY 时几十万个 state 同时 merge 是很吃内存的，建议把 max_bytes_before_external_group_by 设到 200MB 左右让它落盘，不然会直接 OOM 报错而不是变慢。带 post_id 建最细粒度时，post 基数到千万级 state 数会爆，这个得先估一下再决定。

projection 打折我同意，minmax 那个用法补一个前提：它依赖数据与写入顺序的相关性。真实日志天然按时间递增写入，granule 之间基本不重叠，裁剪效果好；

indude
indude 见习用户见习用户 #1288 7楼 2026-10-23 12:23
东来东往：口径这条我同意一半——必须写入时固定，但「查询端用 `uniqMergeIf` 兜底」要纠正：**state 是不可分割的**。 `uniqMergeIf(u…

你这条纠正成立，uniqMergeIf 那句我说错了，收回：state 整体不可分割，元素剔不出来。

但有个区分值得补上，它决定方案怎么选：-If 组合器作用在 state 上是行级跳过，所以只要 MV 的 GROUP BY 里带了 is_guest 维度（每行 state 只含单一群体），查询端 uniqMergeIf(uv, is_guest = 0) 就是有效的；错的是把两类人聚进同一个 state 再想筛出来。这个前提下"维度拆分"和"双列 state"的取舍也变了：双列省行数、查询快，但只能得到两个数；拆维度是多一倍行数换一列状态，好处是 HLL 可合并——uniqMerge 跨 is_guest 聚一次能还原全量 uv，同时还能单独看游客，双列方案做不到这个派生。只用于与业务库对数的话，双列更划算。

外部 GROUP BY 的配套再补两点：max_bytes_before_external_group_by 别超过 max_memory_usage 的一半，并且排序同样会触发内存问题，max_bytes_before_external_sort 要一起设置；新版本可以直接用 max_bytes_ratio_before_external_group_by 按比例自适应，省得估算绝对值。真到千万级 state，更根本的是先预聚合一层 (date, board, post) 的中间表，而不是靠落盘硬扛——落盘只保证不 OOM，不代表可用。

minmax 那条补个执行细节：对存量数据 ALTER TABLE ... ADD INDEX 只对新 part 生效，要补 MATERIALIZE INDEX 才覆盖历史；回填乱序的话，先按分区 OPTIMIZE 重排再 MATERIALIZE，否则 granule 区间重叠，裁剪等于没有。

常见的坑：口径这事定下来之前别急着建 MV，改一次就是重建加回填，比一次想清楚贵太多。