ClickHouse 入门实战:MergeTree 引擎与聚合查询优化

举报
yd_277255811 发表于 2026/11/17 08:45:02 2026/11/17
【摘要】 一、为什么需要列式数据库同样的千万级明细表,MySQL 做一次全表分组聚合往往要几十秒,因为行存引擎会把整行读出来再挑字段。ClickHouse 按列存储,聚合只需要扫描用到的那几列,再配合向量化执行和数据压缩,常见分析查询能快一到两个数量级。它擅长的是"一次写入、多次聚合"的日志、埋点、指标场景,不擅长高频单行更新和事务,别拿它当业务库用。二、核心概念表引擎:MergeTree 家族是主力...

一、为什么需要列式数据库

同样的千万级明细表,MySQL 做一次全表分组聚合往往要几十秒,因为行存引擎会把整行读出来再挑字段。ClickHouse 按列存储,聚合只需要扫描用到的那几列,再配合向量化执行和数据压缩,常见分析查询能快一到两个数量级。

它擅长的是"一次写入、多次聚合"的日志、埋点、指标场景,不擅长高频单行更新和事务,别拿它当业务库用。

二、核心概念

表引擎:MergeTree 家族是主力,决定数据如何存储、合并与去重。
分区 PARTITION BY:按天或按月把数据切成目录,查询能直接跳过无关分区。
排序键 ORDER BY:数据在分区内按排序键有序存放,决定稀疏索引的粒度。
稀疏索引:默认每 8192 行记一个标记,不是每行都有索引,所以排序键的选择极其关键。
数据片段 part:每次写入生成一个 part,后台异步合并成更大的 part。
向量化执行:按列批量处理数据,充分利用 CPU 缓存与 SIMD。
主键:不保证唯一,只用于加速查找,允许重复。

关键心智模型:先想清楚"最常按什么维度过滤和分组",再决定 ORDER BY;顺序写反,性能会差几十倍。

三、安装与建表

本地起服务:

docker run -d --name ch --ulimit nofile=262144:262144 \
  -p 8123:8123 -p 9000:9000 clickhouse/clickhouse-server
clickhouse-client --host 127.0.0.1 --query "SELECT version()"

建一张访问日志表:

CREATE TABLE access_log
(
    event_time DateTime,
    user_id UInt64,
    path String,
    status UInt16,
    duration_ms UInt32,
    province LowCardinality(String)
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (province, event_time, user_id)
TTL event_time + INTERVAL 90 DAY DELETE;

要点:分区键别用高基数字段(如 user_id),否则会产生成千上万个 part;低基数字符串用 LowCardinality 省空间;ORDER BY 把最常过滤的字段放前面。

四、写入与分区管理

批量导入:

clickhouse-client --query "INSERT INTO access_log FORMAT CSV" < access.csv
INSERT INTO access_log SELECT * FROM access_log_staging WHERE event_time >= '2026-11-01';

写入最重要的规矩是**攒批**:每次 INSERT 都会生成一个 part,一秒一条的小批量写入会让后台合并追不上,触发 "Too many parts" 报错。建议单批 1 万到 10 万行,或走 Kafka 表引擎 / Buffer 表缓冲。

常用运维语句:

SELECT partition, name, rows, bytes_on_disk FROM system.parts
WHERE table = 'access_log' AND active ORDER BY partition;
ALTER TABLE access_log DROP PARTITION '202609';
OPTIMIZE TABLE access_log FINAL;    # 手动触发合并,生产环境慎用

五、聚合查询优化

先看几条实用写法:

SELECT province, count(), quantile(0.99)(duration_ms)
FROM access_log
PREWHERE event_time >= '2026-11-01' AND status = 200
GROUP BY province ORDER BY count() DESC LIMIT 10;

SELECT uniq(user_id) FROM access_log WHERE event_time >= today() - 7;
SELECT sum(duration_ms) / count() FROM access_log WHERE path = '/api/order';

优化要点:

· PREWHERE 优先于 WHERE:过滤条件放 PREWHERE,先只读过滤列,再回表读其余列,能显著减少 IO。
· 只 SELECT 需要的列,别写 SELECT *;列存下多一列就是多一份扫描。
· 去重估算用 uniq / uniqCombined,只有要求精确时才用 uniqExact,后者要存全量状态。
· 高频聚合下沉到物化视图:建 AggregatingMergeTree 物化视图,写入时预聚合,查询时只查汇总表。
· 大表 JOIN 前先用子查询把右表缩小,并合理设置 join_algorithm;ClickHouse 的 JOIN 不走索引,右表默认全量载入内存。
· 用 EXPLAIN PIPELINE 或 system.query_log 看真实扫描行数和耗时,别凭感觉调参。

六、五个常见坑

· Nullable 滥用:可空列会带来额外标记文件并拖慢聚合,能用默认值(0、空串)表示就不用 Nullable。
· 分区键选错:按 user_id 分区会让 part 爆炸,按时间分区最稳。
· ORDER BY 与查询条件不匹配:过滤字段没进排序键,稀疏索引基本失效。
· 频繁小批量写入:触发 Too many parts,合并线程被打满,写入报错。
· 拿 ClickHouse 做点查:按主键查单行并不快,它没有行存那种 B+ 树点查能力。

七、小结

落地顺序建议:先按时间分区、按高频过滤维度定排序键建表;再把写入改成攒批或接入 Kafka 引擎;然后用 PREWHERE、物化视图把常用看板的查询压到秒级;最后配上 TTL 和分区清理策略控制存储成本。走完这四步,一张上亿行的日志表就能稳定支撑日常分析,而不必每次都去加 MySQL 索引救火。

【声明】本内容来自华为云开发者社区博主,不代表华为云及华为云开发者社区的观点和立场。转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息,否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0)

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。