ClickHouse实战：十亿级数据OLAP查询优化

前言

去年公司要搞一个数据分析平台，日志量大概一天3亿条，保留90天，总共约300亿条数据。之前用MySQL分库分表勉强扛着，但复杂查询动不动就几十秒，业务那边已经骂了好几次。

调研了一圈，Druid、Doris、ClickHouse，最后选了ClickHouse。原因很简单：单机性能强悍，运维相对简单，社区活跃。

用了一年多，踩了不少坑，也总结了一些优化经验。这篇文章分享出来，希望能帮到同样在做数据分析的朋友。

ClickHouse是什么

一句话：列式存储的OLAP数据库，专门用来做数据分析，写进去快，查得更快。

和MySQL这类行存数据库的核心区别：

• 行存：一行数据存在一起，适合CRUD单条记录
• 列存：同一列的数据存在一起，适合聚合统计

举个例子，有张100列的表，查询只涉及3列，MySQL要把整行读出来再挑3列，ClickHouse直接只读3列的数据，IO少一个数量级。

环境搭建

单机版很简单：

# Ubuntu/Debianapt-getinstall-y apt-transport-https ca-certificates apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv 8919F6BD2B48D754echo"deb https://packages.clickhouse.com/deb stable main">/etc/apt/sources.list.d/clickhouse.listapt-getupdateapt-getinstall-y clickhouse-server clickhouse-client# 启动systemctl start clickhouse-server# 连接clickhouse-client

默认配置文件在/etc/clickhouse-server/，主要改这几个：

<!-- config.xml --><max_memory_usage>10000000000</max_memory_usage><!-- 单查询内存限制10G --><max_threads>8</max_threads><!-- 查询并行度 -->

建表：选对引擎很重要

ClickHouse有几十种引擎，生产环境90%用MergeTree家族。

基础MergeTree

CREATETABLElogs(event_dateDate,event_timeDateTime,user_id UInt64,actionString,duration UInt32,city String,device String)ENGINE=MergeTree()PARTITIONBYtoYYYYMM(event_date)ORDERBY(user_id,event_time)SETTINGS index_granularity=8192;

几个关键点：

1. PARTITION BY：分区键，按月分区是常见做法。查询时指定月份可以跳过无关分区。
2. ORDER BY：排序键，决定数据物理存储顺序。查询最常用的筛选条件要放进去。
3. index_granularity：索引粒度，默认8192行一个索引项。

选错ORDER BY的代价

第一版我把ORDER BY写成了(event_time, user_id)，结果按user_id查询特别慢。

原因：ORDER BY决定数据排列顺序，也决定稀疏索引的结构。user_id放第二位，按它查需要扫描大量数据块。

改成(user_id, event_time)后，按user_id查询速度提升10倍以上。

原则：把等值查询最常用的列放前面，范围查询的列放后面。

写入优化

批量写入是基本

ClickHouse的写入不是针对小事务设计的。每次INSERT都会生成一个数据part，小批量频繁写入会产生大量parts，影响查询性能。

# 错误方式：一条条插forrowindata: insert(row)# 正确方式：批量插# 至少1000条起批，建议1万~10万条一批insert_batch(data[:10000])

官方建议每批次不少于1000行，每秒写入不超过1次。

用Buffer引擎缓冲

如果必须高频小批量写入，用Buffer引擎：

CREATETABLElogs_bufferASlogsENGINE=Buffer(currentDatabase(),logs,16,-- num_layers10,100,-- min_time, max_time10000,1000000,-- min_rows, max_rows10000000,100000000-- min_bytes, max_bytes);

数据先写Buffer表，积攒到一定量或时间后自动flush到主表。

实际写入架构

我们的架构是 Kafka -> Flink -> ClickHouse：

Kafka (原始日志) | v Flink (聚合、清洗) | v ClickHouse (每10秒一批，每批约50万条)

Flink里做时间窗口聚合，积攒10秒的数据一次写入，避免小批量问题。

查询优化

看执行计划

EXPLAINSELECTuser_id,count()FROMlogsWHEREevent_date='2024-12-25'ANDcity='Beijing'GROUPBYuser_id;

关注点：

• 有没有用到分区裁剪
• 有没有用到主键索引
• 扫描了多少行

更详细的：

EXPLAINPIPELINESELECT...

能看到每个处理阶段的执行计划。

分区裁剪

-- 好：指定分区SELECT*FROMlogsWHEREevent_date>='2024-12-01'ANDevent_date<'2024-12-31'-- 差：分区列加函数SELECT*FROMlogsWHEREtoYYYYMM(event_date)=202412

第二种写法分区裁剪失效，会扫全表。

利用主键索引

ORDER BY的列才有稀疏索引：

-- 表定义 ORDER BY (user_id, event_time)-- 快：user_id等值查询，精准定位SELECT*FROMlogsWHEREuser_id=12345-- 较快：user_id + event_time范围SELECT*FROMlogsWHEREuser_id=12345ANDevent_time>'2024-12-01'-- 慢：跳过user_id直接查event_timeSELECT*FROMlogsWHEREevent_time>'2024-12-01'

避免SELECT *

列存数据库的优势就是只读需要的列：

-- 差：读所有列SELECT*FROMlogsWHEREuser_id=12345-- 好：只读需要的列SELECTuser_id,action,durationFROMlogsWHEREuser_id=12345

100列的表，读3列和读100列，IO差距巨大。

prewhere代替where

SELECT*FROMlogs PREWHERE event_date='2024-12-25'-- 先过滤WHEREcity='Beijing'-- 再过滤

prewhere在读取其他列之前先过滤，减少需要读取的数据量。过滤性强的条件用prewhere。

物化视图预聚合

高频查询的聚合结果提前算好：

-- 创建物化视图，按天按城市预聚合CREATEMATERIALIZEDVIEWlogs_daily_cityENGINE=SummingMergeTree()PARTITIONBYtoYYYYMM(event_date)ORDERBY(event_date,city)ASSELECTevent_date,city,count()ascnt,sum(duration)astotal_durationFROMlogsGROUPBYevent_date,city;

新数据写入logs时自动聚合到物化视图。查询日/城市维度的汇总直接查物化视图，速度快几十倍。

跳数索引

对于不在ORDER BY里的列，可以加跳数索引：

-- 给city列加布隆过滤器索引ALTERTABLElogsADDINDEXidx_city cityTYPEbloom_filter GRANULARITY4;-- 给duration加minmax索引ALTERTABLElogsADDINDEXidx_duration durationTYPEminmax GRANULARITY4;

bloom_filter适合等值查询，minmax适合范围查询。

几个踩过的坑

坑1：String类型太慢

最开始action、city这些列都用String，后来发现GROUP BY特别慢。

改用LowCardinality：

ALTERTABLElogsMODIFYCOLUMNcity LowCardinality(String);ALTERTABLElogsMODIFYCOLUMNactionLowCardinality(String);

LowCardinality内部是字典编码，对于基数低（不同值少）的列，存储和计算都更高效。我们改完后相关查询快了3倍。

坑2：不小心全表扫描

线上出过一次事故，有人写了个没有where条件的count查询，直接把CPU打满了。

解决：设置查询限制

-- 用户级别限制CREATEUSERanalyst SETTINGS max_rows_to_read=100000000;-- 查询级别限制SETmax_rows_to_read=100000000;SELECT...

坑3：JOIN性能

ClickHouse的JOIN不是强项，大表JOIN特别慢。

优化方向：

1. 小表放右边（会被广播到所有节点）
2. 用字典代替维度表JOIN
3. 数据预先宽表化，避免JOIN

-- 创建字典CREATEDICTIONARY city_dict(city_id UInt32,city_name String,province String)PRIMARYKEYcity_id SOURCE(CLICKHOUSE(HOST'localhost'PORT9000USER'default'TABLE'city_dim'DB'default'))LIFETIME(MIN300MAX600)LAYOUT(HASHED());-- 用字典代替JOINSELECTdictGet('city_dict','city_name',city_id)ascity_name,count()FROMlogsGROUPBYcity_id;

坑4：磁盘打满

日志数据量太大，磁盘很快就满了。

配置TTL自动清理：

ALTERTABLElogsMODIFYTTL event_date+INTERVAL90DAY;

90天前的数据自动删除。

也可以配置冷热分层，旧数据迁移到便宜的存储：

<storage_configuration><disks><hot><path>/data/clickhouse/hot/</path></hot><cold><path>/data/clickhouse/cold/</path></cold></disks><policies><tiered><volumes><hot><disk>hot</disk></hot><cold><disk>cold</disk></cold></volumes><move_factor>0.1</move_factor></tiered></policies></storage_configuration>

监控与运维

系统表查性能

-- 查询日志SELECTquery,read_rows,read_bytes,result_rows,memory_usage,query_duration_msFROMsystem.query_logWHEREtype='QueryFinish'ORDERBYquery_start_timeDESCLIMIT20;-- 慢查询SELECT*FROMsystem.query_logWHEREquery_duration_ms>10000ORDERBYquery_start_timeDESC;-- 各表大小SELECTtable,formatReadableSize(sum(bytes_on_disk))assize,sum(rows)asrowsFROMsystem.partsWHEREactiveGROUPBYtableORDERBYsum(bytes_on_disk)DESC;

常规运维操作

-- 手动合并parts（一般不需要，后台自动做）OPTIMIZETABLElogs FINAL;-- 查看partsSELECTtable,partition,name,rows,bytes_on_diskFROMsystem.partsWHEREtable='logs';-- 删除分区ALTERTABLElogsDROPPARTITION'202401';

多节点管理

多个ClickHouse节点时，我会用星空组网把它们串到一个虚拟网络里，维护的时候不用一台台跳。特别是跨机房部署的时候，组到一起后用Ansible批量执行命令很方便。

性能数据参考

分享一下我们的实际数据：

• 单表300亿行，约8TB存储（压缩后）
• 8核32G的机器
• 简单聚合查询（单分区）：100ms以内
• 复杂聚合（跨月、多维度）：3-10秒
• 物化视图查询：50ms以内

相比之前MySQL分库分表的方案，复杂查询从几十秒降到几秒，业务那边再也没骂过。

总结

ClickHouse的使用核心就几点：

1. 建表：ORDER BY要根据查询模式设计
2. 写入：批量写，别小批量频繁写
3. 查询：利用好分区裁剪和主键索引，只读需要的列
4. 预计算：高频查询用物化视图

它不是万能的：OLTP场景别用，JOIN重的场景谨慎用。但在OLAP场景，特别是日志分析、用户行为分析、实时报表这些方向，确实是目前性价比最高的选择之一。

刚开始用的时候可能觉得概念多，用熟了会发现套路就那些。希望这篇文章能帮你少踩点坑。