百分点大数据技术团队：ClickHouse 国家级项目性能优化实践

编者按

ClickHouse 自从 2016 年开源以来便备受关注，主要应用于数据分析（OLAP）领域，各个大厂纷纷跟进大规模使用。百分点科技在某国家级项目建设中完成了多数据中心的 ClickHouse 集群建设，日增千亿数据量，在此基础上进行优化与性能调优，能够更好地解决部署规模扩大和数据量扩容等问题。本文结合项目的数据规模及业务场景，重点介绍了百分点大数据技术团队在 ClickHouse 国家级项目建设中的性能优化实践。

一、概览

2020 年 4 月，百分点大数据技术团队结合某国家级多数据中心的 Clickhouse 集群建设，发表了“ClickHouse国家级项目最佳实践”，该文介绍了 Clickhouse 的特点、适用场景与核心概念，以及百分点科技在大规模 Clickhouse 集群建设与运营方面的实践经验。为部署到更多的城市，解决当前城市数据量扩容问题，Clickhouse 集群的设计优化与性能调优成为重点工作：

数据量增加了 5 倍多：数据量达到每天千亿的级别；

查询周期更长：即时查询周期从 7 天增加到 30 天；

数据种类更多：业务数据类型增加了 1 倍；

集群规模增加了 400 多台，聚合节点压力变大；

聚合查询值枚举空间大：部分字段的值枚举空间在几百万和几十亿之间，聚合可能内存溢出。

在做了大规模的测试后，我们发现沿用集群版本和规划不能满足需求。Clickhouse 的很多新特性 V1.1.5 版本都不支持。经过一系列的版本对比测试，集群规划变更及参数调优，成功将版本升级到 V20.1.16，同时也升级了 Clickhouse 指标采集与监控系统，满足了数据规模的增长和业务需求的变化。整个升级涉及很多内容，本文仅就 Clickhouse 性能调优实践进行重点介绍。

二、调优思路

Clickhouse 性能主要由 CPU、IO、Memory 驱动，无论是索引优化、列编码与压缩算法，还是配置的调优，主要是围绕着这三个方面进行。索引改变了数据存储和读取的顺序，编码和压缩改变数据存储与传输的大小，服务配置影响着 ClickHouse 的资源分配和执行行为。

首先通过版本升级，利用 ClickHouse 的新特性来提升单条 SQL 的查询速度，如采用跳数索引、列编码及新的压缩算法，这些都是在新版本中出的新特性。

其次，改变了整个集群的写入方式，在数据写入的时候对集群按照业务概念进入逻辑划分，减少了集群写入压力。

三、实践经验

1. 主键的选择

1.1 生产中如何建主键索引

实践中，时间对于业务是必查字段，因此选用时间字段作为主键，同时将几个重要字段也加入了主键。总体来说，ClickHouse 索引的长度没有明确的限制，需要根据实际业务和数据的结构来综合考虑。

提升查询性能

加在索引中的列如果能跳过比较长的一段数据，则能很好的提升查询性能。

提高数据压缩率

根据主键排序的时候，数据一致性越高，压缩比越高，这两点在下面的测试用例中会很明显的看到。另外，较长的索引会影响索引的内存使用量，不过对于目前普遍的大数据机器配置来说，以及 ClickHouse 稀疏索引的特性，四五个字段的长索引，对内存的占用还是很有限的。

1.2 测试表结构

在项目建设前期，表的主键基本上只有业务时间一个字段，于是我们做了下面的测试，使用其他比较重要的字段作为主键开头。

下面四张测试表字段一致，只有主键不同。其中表 2 和表 3 数据一致，各有 70 亿数据；表 28 和表 29 数据一致，各有 643575000 数据，为了避免分区 part 数量的影响，所有表的分区都 merge 比较彻底，测试表的分区数也一致。

1.6 看上去 log_local29 的压缩效果更好，但是为什么不用这种组合索引呢？

实际业务中是多字段开放式查询，found_time 是必查字段，无论查什么字段，都要带上 found_time 字段，使用 s_city 开头的主键看上去这几个字段得到极大的优化效果，但是在不查询这几个字段的时候，会因为 found_time 没有得到较好的优化而查询效果变差。

如果是要对每天的数据做类似于数仓的 ETL，原始数据层使用这种索引组合则会因为降低比较多的存储而好很多。

2. 跳数索引

2.1 ClickHouse 跳数索引类型

生产中，主要是使用 MergeTree 系列的引擎，目前，MergeTree 系列引擎共支持 5 种跳数索引，分别是 minmax、set 和 ngrambf_v1 和 tokenbf_v1、bloom_filter。实际生产中我们只选用了 bloom_filter 跳数索引，这个是由百分点科技实际业务的数据分布所决定的，后面会分析为什么只用了 bloom_filter 索引。

在生产中只对枚举值比较多的字段用了 bloom_filter 跳数索引，其他索引没有使用，因为 bloom_filter 的索引文件不至于太大，同时对于值比较多的列又能起到比较好的过滤效果。因为经过测试发现对于枚举值较少的字段，不建索引查询速度就已经很快，因为枚举少，本身在压缩的时候压缩比很高，读取速度很快，但是建了索引之后，要么不能过滤掉数据，要么确实过滤掉一部分数据，但是因为读取的数据块不一致又会导致从原先的大块读取的顺序 IO 退化为随机 IO，反而得不偿失。而枚举值较多的情况比如 ip 字段，在使用 ngrambf_v1 索引的时候，ngram size 和 bloom filter 大小的选择对索引的大小和效果影响很大。

另外，通过上面的表格也能看出来，有些字段的 bloom_filter 跳数索引还是非常占用存储的，查询的时候跳数索引的 IO 也需要考虑。因此，实际中才会出现日志中确实过滤掉了数据，但是查询速度反而慢了的情况。最后，选择什么索引以及索引的参数怎么设置，都要结合业务数据的分布特点也进行，数据的排序方式、散列程度都会影响索引效果。

在生产中，我们调整了一些字段的类型，有的从 String 变为 Int，有的从较大取值范围的类型调整为较小范围的类型；还有一些不常用和压缩后 size 非常大的字段采取了较高的压缩算法；对于一些枚举值较少的 String 使用了 LowCardinality(String)，但由于这个特性在所用版本中有 bug，因此在生产中改成了 String。

4. 数据分区

4.1 数据分区规则

不指定分区键

如果建表时不指定分区键，则数据默认不分区，所有数据写到一个默认分区 all 里面。

ClickHouse 的聚合过程大致如下图(这里只画了数据返回流程)。在 ClickHouse 的聚合查询中，每个机器都会把自己的聚合的中间状态返回给分布式节点，也就是说，即使你只是想要 Top100，每台机器也会把自己所拥有的所有枚举值都返回给分布式节点进行进一步的聚合。

返回给分布式节点 22 条数据，分布式节点对着 22 条数据进行进一步的聚合，最后得出想要的结果。

再看两个查询的不同之处，能明显看出查询处理的数据量不一样，原查询直接查询原表 15.5 亿的数据，使用物化视图后，物化视图查询的是存在自己视图表里的数据，数据重组后的数据量差异是两者查询速度差异的根本原因。

6.4 物化视图在实践中的使用

因为业务查询条件的不固定，所以在实际业务中没有使用物化视图，不过在对 ClickHouse 的监控中使用到了，比如每天新增、每小时新增数据量，每天的流量情况以及慢 SQL 的查询等，这里就不详细展开了。

总结与展望

通过调优，在生产环境中，95%的查询小于 1S 响应、98%查询小于 3 秒响应、99%查询小于 5 秒响应。

文中的优化思路和方法总体上非常的通用，但具体怎么设计和使用需要结合实际的业务场景来进行。比如，在百分点科技项目中数据的存储是随机写到一定数量机器上的，在了解的不少公司实践中，比较推荐的都是对数据按照一定规则进行 hash 进行让相同的数据落到同一台机器上，我们没有这样做，主要有三点原因：一是数据从源头进来的时候就没有进行 hash，二是如果在数据流中进行 hash 会大大的降低数据流的处理速度，三是数据倾斜问题。

另外，对于项目中存在的一些问题我们也进行了反思，比如选用的版本，还不是很稳定和比较优的版本，选择一个版本需要花费时间和精力进行各种测试，这个版本也只是在当时匆忙的时间与多个版本中综合妥协的选择。现在，ClickHouse 的大版本已经更新到 21.X，在此版本后，又出现了很多新功能，比如对于小表的 wide part 功能、SQL 的 explain 功能、mysql 同步数据到 ClickHouse、开窗函数，以及使用 S3 等作为 ClickHouse 底层存储进行存算分离等，需要学习和实践的还有很多。

文章的最后，就以开窗函数作为彩蛋吧！

开窗函数

开窗函数这个功能在 21.3 中出现实验性功能，使用这个功能需要设置 allow_experimental_window_functions

= 1。

实例：求过去 10 分钟每分钟都出现并连续出现 3 次的活跃 ip。

可以看出，使用开窗函数后不但 SQL 简洁不少，执行速度也快很多。开窗函数对于数仓的朋友来说，简直就是福音。

说明：文中所做测试用的均是测试数据，与生产会有一定偏差，但总体不影响本文中的相关结论；测试机器使用的 RAID5，具体 IO 行为与 SSD 会有差别。