我们用ClickHouse做数据分析。最开始查询很慢。有些查询要几十秒。经过一系列优化之后。大部分查询降到了一秒以内。本文是我们的性能优化实战记录。

一、项目背景

先说说项目背景。

我们公司有一个数据分析平台。每天要处理几千万条数据。用户可以在平台上自定义查询。生成报表和图表。最开始我们用的是MySQL。但是数据量大了之后。MySQL查询越来越慢。有些报表要等几分钟才能出来。

后来我们调研了几个OLAP数据库。最后选了ClickHouse。因为它的查询性能很出色。而且支持SQL。学习成本低。

最开始把数据导入ClickHouse之后。查询确实比MySQL快了很多。但是还是有些查询很慢。特别是多维度聚合的查询。有时候要几十秒。用户体验不好。

于是我们开始做性能优化。经过一个多月的优化。大部分查询从几十秒降到了一秒以内。本文就是我们优化过程的记录。

二、表结构优化

表结构是性能的基础。表结构设计得不好。后面再怎么优化都有限。

优化1：选择合适的表引擎

ClickHouse有很多表引擎。最常用的是MergeTree系列。我们最开始用的是普通的MergeTree。后来发现对于我们的场景。ReplacingMergeTree更合适。

因为我们的数据有更新的需求。有些数据需要去重。ReplacingMergeTree可以根据主键去重。保留最新的版本。这样就不需要额外做去重的逻辑了。

但是要注意。ReplacingMergeTree的去重不是实时的。是在合并的时候才去重。所以查询的时候可能会看到重复的数据。需要用FINAL关键字。或者在业务层做处理。

我们的场景对实时性要求不高。所以用ReplacingMergeTree没问题。

优化2：选择合适的排序键

排序键是ClickHouse性能的关键。ClickHouse是按排序键有序存储的。查询的时候如果过滤条件包含排序键。就可以利用索引快速定位。

我们最开始的排序键是(id)。因为id是主键。但是我们的查询大部分是按时间和维度过滤的。id几乎用不到。所以索引没有发挥作用。

后来我们把排序键改成了(eventdate, category, userid)。这样按时间和类别查询的时候。就能利用索引了。查询速度提升了好几倍。

排序键的设计原则是。把最常用的过滤字段放在前面。把基数低的字段放在前面。把基数高的字段放在后面。而且排序键不宜过长。一般3到5个字段就够了。

优化3：分区设计

分区也是很重要的。我们最开始是按天分区的。每天一个分区。数据量不大的时候没问题。但是数据量越来越大之后。分区数太多了。有几千个分区。导致元数据很多。查询变慢。

后来我们改成了按月分区。每个月一个分区。分区数减少了很多。查询速度也提升了。

分区的设计原则是。分区不要太细。也不要太粗。太细了分区数太多。太粗了分区裁剪效果不好。一般按天或者按月分区。根据数据量来定。

优化4：字段类型优化

字段类型对性能也有影响。我们最开始有些字段用了String类型。但是实际上是数字或者枚举。

比如状态字段。只有几个固定的值。我们用了String。后来改成了LowCardinality(String)。或者Enum。这样存储更小。查询更快。

还有时间字段。我们最开始用了String存储时间字符串。后来改成了DateTime或者Date类型。这样不仅存储更小。而且可以用时间函数。查询也更快。

还有数字字段。能用小类型就用小类型。比如年龄用UInt8就够了。不要用Int32。这样能减少存储。提升查询速度。

三、索引优化

ClickHouse的索引和传统数据库不同。它用的是稀疏索引。每一定数量行存一个索引值。所以索引的效果取决于排序键的设计。

优化5：利用跳数索引

除了主索引。ClickHouse还支持跳数索引。可以在某些字段上建立跳数索引。加速这些字段的过滤。

我们在一些常用的过滤字段上建了跳数索引。比如状态字段。用的是set_v2类型的跳数索引。这样查询的时候可以快速跳过不满足条件的数据块。

跳数索引的类型有很多。minmax、set、bloomfilter等。要根据字段的特点选择合适的类型。比如低基数的字段用set。高基数的字段用bloomfilter。

但是跳数索引不是越多越好。索引会增加写入的开销。也会增加存储。只在常用的过滤字段上建就够了。

优化6：利用主键索引

前面提到排序键的设计。这里再强调一下。主键索引是ClickHouse最重要的索引。一定要设计好。

我们的经验是。把查询中最常用的WHERE条件字段。按使用频率和基数排序。放在排序键的前面。这样大部分查询都能利用主键索引。

我们优化了排序键之后。很多查询从全表扫描变成了索引扫描。速度提升了十倍以上。

四、查询优化

表结构和索引优化好了之后。还要优化查询本身。

优化7：只查需要的列

ClickHouse是列式存储的。查询的时候只读取需要的列。所以不要用SELECT *。只查你需要的列。

我们最开始有些查询用了SELECT *。结果查询很慢。改成只查需要的列之后。速度提升了很多。因为读取的数据量少了。

特别是有些大字段。比如内容字段。一个字段就占了大部分存储空间。如果不需要就不要查。

优化8：用PREWHERE代替WHERE

ClickHouse有一个PREWHERE关键字。比WHERE更高效。PREWHERE会先只读取排序键和过滤字段。过滤之后再读取其他字段。这样可以减少IO。

我们把一些查询的WHERE改成了PREWHERE。速度有提升。特别是过滤后结果集很小的查询。提升更明显。

但是要注意。PREWHERE只适用于过滤条件不包含所有查询列的情况。如果过滤条件已经包含了所有查询列。用WHERE和PREWHERE效果一样。

优化9：避免不必要的排序

ORDER BY是很耗资源的操作。如果不是必须。就不要排序。或者用LIMIT配合ORDER BY。只排序需要的行数。

我们有些查询是ORDER BY之后取前N条。这种情况下ClickHouse会做全量排序。很慢。后来我们用了TopN函数。或者限制排序的行数。速度提升了很多。

还有就是如果排序的字段就是排序键的前缀。那么数据本身就是有序的。不需要额外排序。这种情况下ORDER BY很快。

优化10：用合适的聚合函数

ClickHouse有很多聚合函数。选择合适的聚合函数也能提升性能。

比如统计不重复值。用uniqExact比uniq慢。但是更精确。如果不需要精确值。用uniq就够了。速度快很多。

比如统计分位数。用quantileTDigest比quantile快。精度也够用。

我们把一些精确的聚合函数改成了近似的。速度提升了很多。用户也感知不到差异。

五、参数调优

ClickHouse有很多参数可以调优。默认值不一定适合你的场景。

优化11：调整内存限制

ClickHouse默认的内存限制是10GB。如果你的查询需要更多内存。就会报错。我们把maxmemoryusage调大了。调到了32GB。

但是也不能太大。太大了可能导致OOM。要根据服务器的内存来设置。一般设置为总内存的一半到三分之二。

还有maxbytesbeforeexternalgroupby和maxbytesbeforeexternal_sort。这两个参数控制什么时候用外部排序和外部聚合。如果内存不够。就会写到磁盘上。虽然慢。但是不会报错。

优化12：调整并发数

ClickHouse默认的并发数是根据CPU核数来的。但是有时候默认的并发数不是最优的。

我们调整了max_threads。根据查询的类型设置不同的并发数。大查询用更多的线程。小查询用更少的线程。避免线程切换的开销。

还有maxconcurrentqueries。控制同时运行的查询数。避免太多查询同时运行导致资源争抢。

优化13：调整合并参数

MergeTree的合并对性能也有影响。合并太频繁会影响写入。合并太少会导致查询慢。因为要读很多part。

我们调整了numberoffreeentriesinpooltoexecutemutation等参数。让合并更合理。

还有就是定期做optimize。手动触发合并。把小的part合并成大的part。提升查询性能。

六、硬件优化

软件优化到一定程度之后。就要考虑硬件了。

优化14：用SSD

ClickHouse是IO密集型的。磁盘速度对性能影响很大。我们最开始用的是机械硬盘。查询很慢。后来换成了SSD。速度提升了好几倍。

如果预算够。尽量用SSD。特别是NVMe SSD。速度更快。

优化15：足够的内存

ClickHouse会把热数据放在内存里。内存越大。缓存的热数据越多。查询越快。

我们的服务器从64GB内存升级到了128GB。常用的查询都能命中缓存。速度提升了很多。

优化16：CPU主频要高

ClickHouse对CPU主频很敏感。因为很多操作是单线程的。主频高的CPU查询更快。

我们选服务器的时候。优先选了主频高的CPU。而不是核数多的。实际效果确实更好。

七、优化效果

经过这些优化之后。我们的查询性能提升了很多。

  • 平均查询时间从15秒降到了0.8秒。
  • 最慢的查询从60秒降到了3秒。
  • 并发查询能力从10个提升到了50个。
  • 服务器的CPU和IO使用率都下降了。

用户体验好了很多。报表基本上是秒出。不需要等待了。

八、给用ClickHouse的人的建议

如果你也在用ClickHouse。或者准备用。我有几个建议。

1. 表结构设计是最重要的

在最开始设计表结构的时候。就要考虑查询模式。设计好排序键、分区、字段类型。这些是性能的基础。后面再改成本很高。

2. 不要用SELECT *

只查需要的列。这是最简单也最有效的优化。

3. 善用系统表

ClickHouse有很多系统表。比如system.query_log。可以查看慢查询。分析查询的性能。用这些表来定位瓶颈。

4. 逐步优化

性能优化不是一步到位的。要先找到瓶颈。然后针对性地优化。优化一个就测试一个。看效果。不要一次性改很多东西。出了问题不知道是哪个改的。

5. 不要过度优化

优化到一定程度就够了。不要为了提升零点几秒而花很多时间。要考虑投入产出比。

九、写在最后

ClickHouse是一个很强大的OLAP数据库。但是要发挥它的性能。需要做很多优化。

我们从最开始的几十秒查询。优化到一秒以内。花了一个多月的时间。踩了很多坑。但是最终的效果是值得的。

希望这篇文章能帮到正在用ClickHouse的你。让你的查询也能从慢变快。

最后用一句话结束本文："性能优化没有银弹。只有不断地测量和改进。"愿每一个用ClickHouse的人都能跑出最快的查询。