我们用ClickHouse做数据分析已经有一年多了，从最开始的几台机器，到现在的几十台集群，踩了很多坑，也积累了不少经验。本文是我总结的ClickHouse实战最佳实践，包括表设计、数据导入、查询优化、集群部署、运维监控等方面。如果你在用ClickHouse或者打算用，希望这篇文章能帮到你。

一、为什么选择ClickHouse

先说说我们为什么选择ClickHouse。

我们的业务需要对大量的日志数据和业务数据进行实时分析，数据量在百亿级别，查询要求秒级响应。最开始用的是MySQL，但是数据量大了之后，查询越来越慢，加索引、分库分表都解决不了根本问题。

后来试了几个OLAP数据库，包括Druid、Kylin、ClickHouse。对比下来，ClickHouse的性能最好，查询速度最快，而且部署简单，运维成本低，SQL支持也比较完善。所以最终选择了ClickHouse。

ClickHouse是俄罗斯Yandex公司开源的列式存储数据库，专门用于在线分析处理（OLAP）。它的核心特点是：

  • 列式存储，查询速度快
  • 向量化执行，CPU利用率高
  • 支持SQL，学习成本低
  • 线性扩展，可以水平扩展集群
  • 数据压缩率高，节省存储空间

用了一年多，我们对ClickHouse的表现很满意。大部分查询都能在秒级返回，复杂的聚合查询也能在十几秒内完成。

二、表设计最佳实践

表设计是ClickHouse性能的基础，设计得好，查询就快；设计得不好，再怎么优化都没用。

1. 选择合适的表引擎

ClickHouse有很多种表引擎，最常用的是MergeTree系列。对于大多数场景，推荐用ReplacingMergeTree或者CollapsingMergeTree。

  • MergeTree：最基础的引擎，支持主键索引和分区，适合只追加不修改的场景
  • ReplacingMergeTree：在MergeTree的基础上，支持去重，相同主键的数据会被替换，适合需要更新的场景
  • CollapsingMergeTree：通过标记列来折叠数据，适合需要删除和更新的场景
  • SummingMergeTree：自动聚合相同主键的数据，适合预聚合场景

我们大部分表用的是ReplacingMergeTree，因为业务数据经常需要更新，用ReplacingMergeTree可以自动去重，保证数据的唯一性。

2. 合理设计分区键

分区是ClickHouse的一个重要特性，合理的分区可以大大提升查询性能。

分区键的选择原则是：选择查询中经常用来过滤的列，而且分区的数量不要太多。一般来说，按日期分区是最常见的，比如按天、按月分区。

我们的日志表是按天分区的，因为大部分查询都会指定时间范围。按天分区之后，查询的时候只会扫描相关的分区，不会扫描全表，性能提升很明显。

要注意的是，分区不要太细，比如按小时分区，会导致分区数量太多，文件数量爆炸，影响性能。也不要太粗，比如按年分区，查询的时候还是要扫描大量数据。

3. 主键和排序键的设计

ClickHouse的主键不是唯一约束，而是用来排序和建立稀疏索引的。主键的设计对查询性能影响很大。

主键的选择原则是：把查询中经常用来过滤和分组的列放在前面。比如我们的日志表，查询经常按用户ID和时间来过滤，所以主键设计成了(userid, eventtime)。

要注意的是，主键的列不要太多，一般3到5列就够了。列太多会导致索引变大，影响写入性能和查询性能。

排序键默认和主键一样，也可以单独指定。排序键决定了数据在磁盘上的存储顺序，排序键设计得好，查询的时候可以利用数据的局部性，提升查询速度。

4. 合理选择数据类型

数据类型的选择也很重要，不同的数据类型占用的存储空间和计算性能不一样。

  • 时间类型用DateTime或者Date，不要用字符串
  • 整数类型用合适的范围，比如能用Int32就不要用Int64
  • 字符串类型用String，不要用FixedString，除非长度固定
  • 低基数字符串用LowCardinality，可以大大压缩存储空间
  • 浮点数用Float64，精度要求不高可以用Float32

我们有一个维度列，值只有十几个，用了LowCardinality之后，存储空间减少了70%，查询速度也提升了不少。

5. 适当使用物化视图

物化视图是ClickHouse的一个强大功能，可以预先计算和存储查询结果，提升查询速度。

对于一些经常查询的聚合指标，我们建了物化视图，提前把数据聚合好。查询的时候直接查物化视图，速度比查原表快几十倍。

比如我们有一个实时的销售统计，按天、按地区、按品类统计销售额。如果每次都从明细表查询，需要扫描大量数据。建了物化视图之后，数据写入的时候自动聚合，查询的时候直接读聚合结果，毫秒级返回。

要注意的是，物化视图会增加写入的开销，不要建太多。只对那些查询频率高、计算量大的查询建物化视图。

三、数据导入最佳实践

数据导入是ClickHouse使用中的一个重要环节，导入的方式和效率直接影响系统的性能。

1. 批量导入，不要单条插入

ClickHouse适合批量导入，不适合单条插入。单条插入的性能很差，而且会产生大量的小文件，影响查询性能。

我们的做法是：数据先写到Kafka，然后用ClickHouse的Kafka引擎消费，批量写入。每次写入的数据量在一万到十万条之间，这样写入性能最好。

如果是从文件导入，用clickhouse-client的INSERT语句，一次导入一个文件或者一批数据。不要一条一条地插。

2. 控制写入频率

写入频率不要太高，否则会导致part数量太多，ClickHouse需要频繁合并，影响性能。

一般来说，每个表每秒写入1到2次就够了。如果数据量很大，可以增加每次写入的数据量，而不是增加写入频率。

我们遇到过一个问题，最开始的时候写入频率太高，每秒写入十几次，导致part数量爆炸，查询越来越慢。后来调整成批量写入，每秒1到2次，问题就解决了。

3. 使用合适的导入工具

ClickHouse提供了多种导入方式：

  • clickhouse-client：适合从文件导入
  • Kafka引擎：适合实时数据流
  • HTTP接口：适合程序写入
  • 第三方工具：比如DataX、Flink Connector等

我们实时数据用Kafka引擎，离线数据用DataX。两种方式都很稳定，性能也不错。

4. 数据去重

如果数据有重复，需要在导入的时候去重，或者用ReplacingMergeTree引擎自动去重。

ReplacingMergeTree会在后台合并的时候去重，但是在合并之前，查询的时候可能会读到重复数据。如果需要严格去重，可以在查询的时候加FINAL关键字，但是会影响性能。

我们的做法是：在导入端做去重，保证写入的数据不重复。同时用ReplacingMergeTree作为兜底，双重保证。

四、查询优化最佳实践

查询优化是ClickHouse使用中最有技术含量的部分，也是最能体现经验的地方。

1. 利用分区和主键过滤

查询的时候，尽量带上分区键和主键的过滤条件。这样ClickHouse可以只扫描相关的分区和数据范围，不会全表扫描。

比如我们的查询都会指定时间范围，而且尽量带上用户ID。这样查询的速度会快很多。

要避免的是：不带任何过滤条件的全表扫描，或者对分区键用函数（比如toDate(event_time) = '2021-01-01'），这样会导致分区裁剪失效。

2. 只查需要的列

ClickHouse是列式存储，查询的时候只读取需要的列，列越少，读取的数据量越小，查询越快。

所以不要用SELECT ，只查你需要的列。我们有一个查询，最开始用了SELECT ，查了30多列，要十几秒。后来改成只查5列，两秒就返回了。

3. 合理使用聚合函数

ClickHouse的聚合函数很强大，但是不同的聚合函数性能差别很大。

  • count()比count(distinct col)快很多，如果不需要精确去重，可以用uniq()近似去重
  • sum()、avg()这些基本聚合函数很快
  • 用group by的时候，分组的列尽量少，而且尽量用主键列

我们有一个查询，用了count(distinct userid)，数据量大的时候很慢。后来改成了uniq(userid)，速度提升了十倍，误差在1%以内，业务可以接受。

4. 避免大结果集

查询返回的结果集不要太大，最好控制在一百万行以内。如果结果集很大，建议把结果写入一张新表，或者用LIMIT分页。

我们遇到过一个问题，有个查询返回了几千万行结果，导致内存溢出，服务挂了。后来加了LIMIT，并且建议用户把结果导出到文件，问题就解决了。

5. 使用预聚合

对于复杂的查询，可以用物化视图或者中间表做预聚合。查询的时候直接查预聚合的结果，速度会快很多。

我们的很多报表查询，都是先聚合到小时级或者天级的中间表，然后报表查询直接查中间表。这样既保证了查询速度，又减轻了原表的查询压力。

6. 查询监控和慢查询分析

开启ClickHouse的查询日志，定期分析慢查询。找到慢的查询，针对性地优化。

我们每天都会分析前一天的慢查询，找到执行时间最长的几个，然后看是表设计的问题，还是查询写法的问题，或者是数据倾斜的问题。持续优化，系统的整体性能才会不断提升。

四、集群部署最佳实践

ClickHouse支持分布式集群，合理的集群部署可以提升系统的可用性和扩展性。

1. 分片和副本

ClickHouse的集群由分片和副本组成。分片用来水平扩展，把数据分散到多个节点；副本用来高可用，每个分片有多个副本，一个节点挂了不影响服务。

我们的集群是3分片2副本，一共6个节点。每个分片有2个副本，数据写入的时候同时写两个副本，保证数据不丢失。

分片的数量根据数据量和查询性能来定，数据量大、查询多，就多分几片。副本的数量一般2个就够了，对可用性要求高可以用3个。

2. 分布式表和本地表

ClickHouse的集群中，每张表都有本地表和分布式表。本地表存在每个节点上，分布式表是一个逻辑视图，查询的时候会自动路由到各个节点。

我们的做法是：数据写入本地表，查询用分布式表。这样写入的时候不需要分布式协调，性能更好；查询的时候用分布式表，自动并行查询各个节点。

要注意的是，分布式表的查询会把各个节点的结果汇总到一个节点，如果结果集很大，会导致汇总节点内存压力大。所以分布式查询也要控制结果集大小。

3. 硬件配置

ClickHouse对硬件的要求比较高，特别是CPU和内存。

  • CPU：越多核越好，ClickHouse是向量化执行，多核并行，CPU核数直接影响查询速度
  • 内存：越大越好，ClickHouse会利用内存做缓存和计算，建议至少64GB
  • 磁盘：SSD，最好是NVMe SSD，ClickHouse是IO密集型的，磁盘速度很重要
  • 网络：万兆网卡，集群内部数据传输量大，网络带宽很重要

我们的节点配置是：32核CPU，128GB内存，2TB NVMe SSD，万兆网卡。这个配置对于百亿级数据量来说，性能很不错。

4. 负载均衡

查询请求要做负载均衡，不要都打到一个节点上。可以用Nginx、HAProxy或者ClickHouse自带的负载均衡功能。

我们用的是HAProxy，把查询请求均匀分发到各个节点。同时配置了健康检查，节点挂了自动剔除，保证服务可用。

五、运维监控最佳实践

好的运维监控是系统稳定运行的保障。

1. 监控指标

我们监控的ClickHouse指标包括：

  • 系统指标：CPU、内存、磁盘、网络
  • ClickHouse指标：查询数、查询延迟、写入数、写入延迟、part数量、合并队列、复制队列
  • 业务指标：各表的数据量、查询频率、慢查询数量

用Prometheus采集指标，Grafana做可视化大盘。有异常的时候自动告警。

2. 备份和恢复

数据一定要有备份。我们用的是ClickHouse的ALTER TABLE ... FREEZE命令，每天做一次全量备份，备份到对象存储。

恢复的时候，用ATTACH PARTITION把备份的数据挂载回去。我们做过几次恢复演练，确认备份是可用的。

不要等数据丢了才想起来备份，那时候就晚了。

3. 定期维护

定期做一些维护操作：

  • 检查part数量，如果太多，手动触发合并
  • 检查过期数据，及时删除或者归档
  • 检查磁盘空间，提前扩容
  • 升级版本，跟进社区的bug修复和性能优化

我们每周做一次例行维护，确保系统健康运行。

4. 容量规划

提前做好容量规划，不要等磁盘满了或者性能不够了才扩容。根据数据增长速度和查询量增长，提前规划扩容。

我们每个月都会评估一次容量，预测未来三个月的资源需求，提前准备好扩容方案。

六、常见的坑

最后说说我们踩过的一些坑。

坑1：part数量太多

最开始写入频率太高，导致part数量爆炸，查询越来越慢，甚至服务不可用。

解决方法：批量写入，控制写入频率。如果part已经很多了，可以用OPTIMIZE TABLE手动合并。

坑2：数据倾斜

某个分片的数据量特别大，导致查询的时候这个分片很慢，拖慢了整个查询。

解决方法：选择合适的分片键，让数据均匀分布。如果已经倾斜了，可以重新分片或者调整数据分布。

坑3：内存溢出

复杂查询或者大结果集查询，导致内存溢出，服务挂掉。

解决方法：限制查询的内存使用，设置maxmemoryusage参数。查询的时候控制结果集大小，避免大结果集。

坑4：副本同步延迟

网络波动或者节点负载高，导致副本同步延迟，查询的时候读到旧数据。

解决方法：监控复制队列，延迟高的时候及时处理。查询的时候可以设置一致性级别，保证读到最新数据。

坑5：版本升级出问题

有一次升级版本，因为配置不兼容，导致集群启动失败。

解决方法：升级之前先看更新日志，在测试环境验证。升级的时候先升级一个节点，没问题再滚动升级其他节点。

七、写在最后

ClickHouse是一个非常优秀的OLAP数据库，性能强劲，功能丰富，运维简单。但是要用好它，还是需要不少经验和技巧的。

本文总结了我们在表设计、数据导入、查询优化、集群部署、运维监控等方面的最佳实践，以及踩过的一些坑。希望能帮到正在用或者打算用ClickHouse的同学。

技术是不断发展的，ClickHouse也在不断更新，新的功能和优化不断出现。我们要保持学习，跟进社区的发展，不断优化自己的系统。

最后用一句话结束本文："工欲善其事，必先利其器。"ClickHouse是一把利器，但是只有掌握了正确的使用方法，才能发挥它的最大威力。愿每一个用ClickHouse的人，都能让自己的数据分析又快又稳。