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 索引救火。
- 点赞
- 收藏
- 关注作者
评论(0)