百分点大数据技术团队：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 比较彻底，测试表的分区数也一致。

CREATE TABLElog_local2(    `found_time` UInt32,         `s_ip` String,    `s_city` String,    `s_long` Float32,`s_lat`Float32,INDEXs_ip_idx1 s_ip TYPE bloom_filter() GRANULARITY 4)ENGINE = MergeTreePARTITION BYtoRelativeDayNum(toDateTime(found_time))ORDER BYfound_time;

CREATE TABLElog_local3(    `found_time` UInt32,         `s_ip` String,    `s_city` String,    `s_long` Float32,`s_lat`Float32,INDEXs_ip_idx1 s_ip TYPE bloom_filter() GRANULARITY 4)ENGINE = MergeTreePARTITION BYtoRelativeDayNum(toDateTime(found_time))ORDER BY(found_time, s_ip, s_city, s_long, s_lat);

CREATE TABLElog_local28(`found_time` UInt32,`d_ip` IPv6CODEC(LZ4HC(9)), `s_ip` IPv6 CODEC(LZ4HC(9)), `d_port` UInt16 CODEC(NONE),`s_port` UInt16 CODEC(NONE), `s_country` LowCardinality(String),`s_city` StringCODEC(ZSTD(9)), s_geo String, s_long Float32 , s_lat Float32, d_countryLowCardinality(String), d_city String CODEC(ZSTD(9)),d_geo String,d_long Float32 , d_lat Float32)ENGINE =MergeTree() PARTITION BYtoRelativeHourNum(toDateTime(found_time)) ORDER BY(found_time,s_city,s_long,s_lat)SETTINGSindex_granularity = 8192;

CREATE TABLE log_local29(`found_time`UInt32,`d_ip` IPv6CODEC(LZ4HC(9)), `s_ip` IPv6 CODEC(LZ4HC(9)), `d_port` UInt16 CODEC(NONE),`s_port` UInt16 CODEC(NONE), `s_country` LowCardinality(String),`s_city` StringCODEC(ZSTD(9)), s_geo String, s_long Float32 , s_lat Float32, d_countryLowCardinality(String), d_city String CODEC(ZSTD(9)),d_geo String,d_long Float32 , d_lat Float32)ENGINE =MergeTree() PARTITION BYtoRelativeHourNum(toDateTime(found_time)) ORDER BY(s_city,s_long,s_lat,found_time)SETTINGSindex_granularity = 8192;

1.3 log_local2 和 log_local3 两张测试表压缩效果对比

从表 2 和表 3 各列数据大小可看出，s_ip 加入到主键后压缩得到一定的优化。

1.4 log_local2 和 log_local3 两张测试表 s_ip 分页查询效果对比

可以看出 s_ip 加入主键后查询性能有一定的提升。

1.5 found_time 和 s_city 开头主键压缩效果对比

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 索引。

2.2 如何创建

为了演示，此处创建一张测试表，只有两个字段，found_time 和 ip，为了下文描述方便，类型都采用 String，并给 ip 字段建了 5 种索引以展示索引用法。

CREATE TABLE  ip_test(found_timeString,ip String,INDEX  ip_idx1(ip) TYPE minmax GRANULARITY 1,INDEX  ip_idx2 ip TYPE bloom_filter(0.025) GRANULARITY 1,INDEX  ip_idx3 ip TYPE ngrambf_v1(3, 512, 5, 0) GRANULARITY 1,INDEX  ip_idx4 ip TYPE tokenbf_v1(512, 5, 0) GRANULARITY 1,INDEX  ip_idx5 ip TYPEset(0) GRANULARITY 4)ENGINE= MergeTree() order by found_timepartitionby toYYYYMMDD(toDateTime(found_time)) SETTINGS index_granularity = 4;

• minmax—存储相应索引区间的大小极值；

• bloom_filter(误差概率)—布隆过滤器，里面的参数为自己设定的误差概率，区间为(0,1),默认为 0.025；

• ngrambf_v1(n,布隆过滤器字节大小，hash 函数个数，随机种子)—；

• tokenbf_v1(布隆过滤器字节大小，hash 函数个数，随机种子)—；

• set(max_rows)—存储相应索引区间的唯一值并不超过 max_rows 的个数，默认值 0 意味着没有限制。

样例数据如下：

insertinto ip_test values('1620756379','178.237.20.123');insertinto ip_test values('1620756380','204.79.197.200');insertinto ip_test values('1620756445','180.163.151.161');insertinto ip_test values('1620756449','72.247.116.168');insertinto ip_test values('1620759973','172.24.20.17');insertinto ip_test values('1620759977','101.89.39.120');insertinto ip_test values('1620760033','192.168.162.96');insertinto ip_test values('1620760035','123.6.2.171');insertinto ip_test values('1620760036','172.217.24.74');insertinto ip_test values('1620760036','101.89.39.120');insertinto ip_test values('1620760047','192.168.162.96');insertinto ip_test values('1620760138','123.6.2.171');insertinto ip_test values('1620760239','172.217.24.74');

2.3 index_granularity 和 GRANULARITY

index_granularity：指的是 ClickHouse 的索引粒度，多少行数据产生一条索引，默认是 8192 行数据产生一条索引；

GRANULARITY：跳数数据根据指的表达式聚合数据块上的信息，聚合信息的粒度是由创建索引的时候指定 GRANULARITY 的值决定的。

如下图所示，index_granularity = 4 意味着每 4 条数据产生一条索引，GRANULARITY1 则意味着 minmax 索引聚合信息的粒度是 1 个 index_granularity,也是 4 条数据，minmax 索引会从每 4 条数据中心聚合出最大最小的值来。GRANULARITY n=n*index_granularity。

再如下图所示，set 索引聚合的信息是整个样例数据的枚举值。

2.4 如何对历史数据建索引

添加索引：

ALTER TABLE ip_test add INDEXip_idx1(ip) TYPE minmax GRANULARITY 1;ALTER TABLE ip_test add INDEXip_idx2 ip TYPE bloom_filter(0.025) GRANULARITY 1;ALTER TABLE ip_test add INDEXip_idx3 ip TYPE ngrambf_v1(3, 512, 5, 0) GRANULARITY 1;ALTER TABLE ip_test add INDEXip_idx4 ip TYPE tokenbf_v1(512, 5, 0) GRANULARITY 1;ALTER TABLE ip_test add INDEXip_idx5 ip TYPE set(0) GRANULARITY 4;

删除索引：

ALTER TABLE ip_test DROP INDEXip_idx1;

重建索引(对历史数据 add 索引的时候，只是改变了表的 schema，实际的索引文件并没有生成，需要再使用重建索引的语句对历史数据建立索引)：

ALTER TABLE ip_test MATERIALIZEINDEX name[ IN PARTITION partition_name]

2.5 bloom_filter 查询效果

黄线为没有建 bloom_filter 索引的查询数据，蓝色为建了 bloom_filter 索引的查询数据，二者数据内容一致。

2.6 跳数索引需要额外存储

下面表格是对 70 亿 ip 字段建立索引后，原始数据和索引数据大小的对比，ip 枚举值在 3 亿以上，对于这类数据建索引，索引文件会非常大。

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

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

3. 压缩和编码

3.1 通用编码

3.2 特殊编码类型

特殊编码与通用的压缩算法相比，区别在于，通用的 LZ4 和 ZSTD 压缩算法是普适行的，不关心数据的分布特点，而特殊编码类型对于特定场景下的数据会有更好的压缩效果。

3.3 如何使用

CREATE TABLE k19_ods.test8(   `found_time` UInt32,   `recv_time1` UInt32 CODEC(NONE),   `recv_time2` UInt32,   `recv_time3` UInt32 CODEC(LZ4),   `recv_time4` UInt32 CODEC(LZ4HC(9)),   `recv_time5` UInt32 CODEC(ZSTD(9)),   `recv_time6` UInt32 CODEC(T64()),   `name0` String CODEC(Delta()，LZ4),   `name1` String CODEC(DoubleDelta()),   `name2` String CODEC(Gorilla()),   `name4` String CODEC(Gorilla(), LZ4))ENGINE = MergeTree()PARTITION BYtoYYYYMMDD(toDateTime(found_time))ORDER BY found_time

压缩算法和特殊编码两者可以结合起来使用。

3.4 压缩效果举例对比

需要注意一点就是，对于 LZ4HC 和 ZSTD 选择的压缩 level 越高，压缩效果越好，但是 CPU 的使用率也会相应的越高。如果插入的数据量很大，会明显看到较高的 CPU 使用率。

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

4. 数据分区

4.1 数据分区规则

不指定分区键

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

如下图：

使用整型

如果分区键取值属于整型且无法转换为日期类型 YYYYMMDD 格式，则直接按照该整型的字符形式输出作为分区 ID 的取值。 

如下图：

使用日期类型

如果分区键取值属于日期类型，或者是能够转换为 YYYYMMDD 日期格式的整型，则按照分区表达式逻辑格式化后作为分区 ID 的取值。

使用其它类型

如果分区键取值既不属于整型，也不属于日期类型，例如 String、Float 等，则通过 128 位 Hash 算法取其 Hash 值作为分区 ID 的取值。

 4.2 分区目录命名规则

• partition_id：20210524:，见上页的生成规则；

• min_block_number:1,,最小块编号，MergeTree 引擎从 1 开始计数，每次+1；

• max_block_number:1，最大块编号，新插入的数据，最小与最大编号一致；

• level:0，这个可以理解为合并的次数，新插入的数据都是 0，每合并 1 次+1。

4.3 分区目录合并过程

4.4 分区设计

分区选择考虑 merge (写入放大)、查询(slelect part 数量)、启动等方面考虑。在实际生产中选择时间作为分区键，根据表数据的大小按天或者按周进行了分区。

这是之前做过的一个 part 数量与启动速度测试，从中可以明显的看出 part 数量很影响启动速度。

5. 配置参数调优

5.1 现场使用的配置参数

关于配置参数，大部分在之前的分享中都描述过，这里着重讲一个新加的配置：distributed_group_by_no_merge。

下面是这个参数的解释，从官方给出的说明中，有两个关键点：一是分布式查询的时候，不会从不同服务器 merge 聚合状态；二是不同分片上有不同的 key 是起比较好的应用场景。为什么不同的分片上要有不同的 key，下文中将做出解释。

5.2 不开启 distributed_group_by_no_merge

以 1 台分布式节点、4 个数据节点的集群来做举例，建表语句和样例数据如下（表里只有两个字段：id 和其对应的数量）：

create table local.server(id Int32,cntInt32)ENGINE=Memory();create table local.server(id Int32,cntInt32)ENGINE=Memory();create table local.server(id Int32,cntInt32)ENGINE=Memory();create table local.server(id Int32,cntInt32)ENGINE=Memory();createtable dis.server(id Int32,cntInt32)ENGINE=Distributed('test',local,'server');insert into server values(1,10) ,(2,9),(3,8) ,(4,5) ,(5,4) ,(6,3);     insert into server values (1,11), (3,12),(5,13), (7,14), (9,10);                     insert into server values (2,15 ), (4,16 ),(6,17 ), (8,18 ), (10,19); insert into server values (3,14 ), (6,15 ),(9,16 ), (11,17), (12,18), (13,20);

正常情况下，要统计整个集群的 id 机器数量并取 Top3，sql 如下：

select id,sum(cnt)counts from server groupby id order by counts desc limit 3 ;

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

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

5.3 开启 distributed_group_by_no_merge

select id,sum(counts)counts from(          select id,sum(cnt)counts from servergroup by id order by counts desc limit 3         settingsdistributed_group_by_no_merge=1)  group by id order by counts desc limit 3;

这条 SQL 在聚合的时候每个节点只只会返回各自节点的 Top3 给分布式节点，分布式节点最终聚合 3*4=12 条数据。不过可以看到数据有一定误差，在生产中对于比较靠前的 topN 来说，实际统计的误差较小，如果不是要求百分百精确的话，完全可以接受。

5.4 如果数据 ip hash 存储

对于上面的统计误差，如果数据是按照一定规则一类数据存储到一个分片上，则可以避免这个问题，如下图所示。所以上文里提到官方说要不同的 key 分布在不同的 shard 上面，不过，这同时会引进一个新的问题——数据倾斜问题，这里就不做讨论了。

6. 物化视图

6.1 如何创建

CREATE MATERIALIZED VIEW db.viewname(   field TYPE...)[ENGINE = engine] [POPULATE] AS SELECT ...

POPULATE 关键字可以创建物化视图的时候对历史数据起作用，否则只会对新插入的数据生效。

6.2 物化视图与普通视图的区别

普通视图不保存数据，保存的仅仅是查询语句，查询的时候还是从原表读取数据，可以将普通视图理解为是个子查询。物化视图则是把查询的结果根据相应的引擎存入到了磁盘或内存中，对数据重新进行了组织，你可以理解物化视图是完全的一张新表。

物化视图示例

我们准备了一份 1547145102 数据量的 ClickHouse 的 text_log 数据，表结构如下：

CREATE TABLE system.text_log(`event_date` Date, `event_time` DateTime, `microseconds` UInt32, `thread_name`LowCardinality(String), `thread_number` UInt32, `os_thread_id` UInt32, `level`Enum8('Fatal' = 1, 'Critical' = 2, 'Error' = 3, 'Warning' = 4, 'Notice' = 5,'Information' = 6, 'Debug' = 7, 'Trace' = 8), `query_id` String, `logger_name`LowCardinality(String), `message` String, `revision` UInt32, `source_file`LowCardinality(String), `source_line` UInt64) ENGINE = MergeTree PARTITION BYtoYYYYMM(event_date) ORDER BY (event_date, event_time) SETTINGSindex_granularity = 8192。

如果要统计每天各种日志级别的 log 数量，查询语句：

selectevent_date,level,countMerge(counts)counts from test.log_level_count group byevent_date,level order by event_date,level;

查询耗时：

现在对该查询建一个物化视图看看效果：

CREATE MATERIALIZED VIEW test.log_level_countENGINE = AggregatingMergeTree() PARTITION BY toYYYYMMDD(event_date)ORDER BY (event_date, level)POPULATE ASselect event_date,level,countState(level)counts from system.text_loggroup by event_date,level order by event_date,level;

再次查询：

selectevent_date,level,countMerge(counts)counts fromtest.log_level_count group by event_date,level order by event_date,level;

可以明显的看出两者查询速度的巨大差异，相差 100 多倍，看到这里有没有两眼发光，很兴奋，觉得自己学到了一个屠龙技！

那两者为什么同样的结果使用物化视图会快这么多呢？

再看两个查询的不同之处，能明显看出查询处理的数据量不一样，原查询直接查询原表 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 底层存储进行存算分离等，需要学习和实践的还有很多。

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