ClickHouse 作为一款开源列式数据库管理系统，主要用于数据分析（OLAP）领域，近年来备受关注。本文将分享 Clickhouse 应用实践经验和遇到的问题，总结出基本的开发和使用规范，以供参考。

背景

ClickHouse 由俄罗斯第一大搜索引擎 Yandex 于 2016 年 6 月发布，开发语言为 C++，ClickHouse 是一个面向联机分析处理(OLAP)的开源的面向列式存储的 DBMS，与 Hadoop、Spark 这些巨无霸组件相比，ClickHouse 很轻量级，查询性能非常好，号称比事务数据库快 100-1000 倍，最大的特色是高性能的向量化执行引擎，而且功能丰富、可靠性高。

Clickhouse 初识

核心概念

•表引擎（Engine）

表引擎决定了数据的存储方式，常用的存储引擎是 MergeTree 系列，如果需要数据副本的话可以使用 ReplicatedMergeTree 系列，读取集群数据需要使用分布式表引擎 Distribute。MergeTree 作为家族系列最基础的表引擎，主要有以下特点：

1.支持分区，可以通过 PRIMARY KEY 语句指定分区字段 2.存储的数据按照主键排序：允许创建稀疏索引，从而加快数据查询速度

建表语法：

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster](   name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],    ...    INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,    INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2) ENGINE = MergeTree()ORDER BY expr[PARTITION BY expr][PRIMARY KEY expr][SAMPLE BY expr][TTL expr [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'], ...][SETTINGS name=value, ...]

•表分区（Partition）

表中的数据按照指定的字段分区存储，每个分区在文件系统中都是都以目录的形式存在。常用时间字段作为分区字段，比如按照天分区或者月分区。查询时，使用分区字段作为 Where 条件，可以有效的过滤掉大量数据。

•数据分片（Shard）

一个分片本身就是 ClickHouse 一个实例节点，分片的本质就是为了提高查询效率，将一份全量的数据分成多份（片），从而降低单节点的数据扫描数量，提高查询性能。

•功能缺陷

1.没有完整的事务支持

2.缺少高频率低延迟的修改或删除数据的能力

3.不适合通过其检索单行的点查询

4.二进制数据或文件存储

开发规范

数据写入规范

1.禁止单条插入操作，会产生大量小分区文件，必须每个写入批次 5w-10w 条数据。

2.建议表要指定分区键，插入数据以分区为单位，入库性能大大提升。

3.分区键的基数不能太多，通过查询、写入综合考虑选择合适的分区键。

4.数据在写入 ClickHouse 前预先的对数据进行分组，避免一次插入的数据属于多个分区。

查询 SQL 编写规范

1.禁⽌使用 select * ，每减少一个字段会减少大量的数据扫描，提升查询效率。

2.建议使用 limit 限制返回数据条数，限定结果集数量。

3.使用 uniqCombined 替代 distinctuniqCombined 对去重进行了优化，通过近似去重提升十倍查询性能。

4.不建议通过 ClickHouse 做 join 类的操作，可以把打宽操作提升至业务数据输入端来完成。

集群架构

常规架构

•部署架构 对 clickhouse 集群的交付做一些标准，能够提升交付速度。以 8 台机器为例，集群模式固定为 4 分片 2 副本模式，另外需要 3 台 zookeeper 主机，解决 clickhouse 集群高可用性，保证单机器宕机情况不影响集群可用性。业务访问读分布式表，写本地表。

•重点监控

1.ClickHouse 服务的后台合并线程数，按实际情况来优化服务参数

2.慢查询语句，监控不合理查询语句 3.ZNode 的增长趋势，用来判断是否存在不合理写入

实践优化案例

应用实践

目前某项目中已有规模上百台节点集群，支撑每日近千亿数据入库，查询秒级完成，极大提升原有 OLAP 架构的性能，覆盖的业务场景包括：地震平台，精准实时用户位置人流，用户行为日志分析，用户画像，人群圈定和业务日志的分析，监控、查询等。

1、案例需求

为满足运维人员快速定位故障、降低用户投诉，提升客户满意度，需要新建海量数据即席查询的上网日志查询平台，需求如下：

•数据量：预估日均 8PB，查询周期 30 天，总存储量 248PB;

•并发要求：100TPS；

•数据源：2345G DPI 数据、2345G 位置信令、IMS 数据；

•查询方式：通过 IMSI、IMEI、XDR ID 查询，因此需求 MSISDN、IMSI、IMEI、XDR ID 可任选 1 个进行查询。

2、数据特点分析

•单表查询，类似多个字段进行点查

•数据量级特别巨大，按百亿、千亿级计算

•数据本身没有太多规律，很难选择分区键，不能做太多剪枝

3、实现思路

通过数据分布特点以及查询需求，核心的要点是多个字段的点查来捞取所需要的数据条目，我们挑选了三套方案来逐步验证可行性。

•Apache Hbase + Phoenix

•Apache Spark + ORC(Bloom Filter Index）

•Elasticsearch

•ClickHouse

方案一是相对比较传统的解决思路，在具体实践中也会有比较不错的效果，不过涉及的组件会比较多。对于方案二，利用 Spark 的查询优化和 ORC 的索引机制，可以有效的做到数据剪枝，但是很难达到稳定秒级返回。对于方案三，成本太大，投入产出比不成正比。所以我们会进行探索 ClickHouse 是否可以达到我们的需求。

对于这么海量数据，要达到极速返回，我们需要做的就是利用引擎本身的机制来做数据剪枝，比如说利用合理分区键、排序索引等特性。但是针对上网日志数据，常规的剪枝优化思路并不能达到优化效果，因为每个分区下的数据是海量的，同时索引效果也不大。

这里的难点在于多字段捞取数据，一开始会选择直接使用原始字段排序作为 Clickhouse 的主索引，效果不佳。后来通过试验，最终通过数据取模来模拟多级分区，再利用 CK 自身的索引缓存机制来作为最终的实现方案。

4、具体实现

•表设计，为每个查询字段取模，并将其添加至字段列表

CREATE TABLE test.event_replica(    `msisdn` String,    `msisdn_mod` Int32,    `imsi` String,    `imsi_mod` Int32,    `p` Int32)ENGINE = MergeTreePARTITION BY pORDER BY (msisdn_mod, imsi_mod, imei_mod)SETTINGS index_granularity = 8192

•写入约定，将字段取 hash 值，并将其取模

INSERT INTO test.event_replicaSELECT   Msisdn, modulo(sipHash64(Msisdn), 1000),  Msisdn, modulo(sipHash64(Msisdn), 1500),  1FROM crowd.data;

•查询约定，需要对应好每个字段的取模值

SELECT count() FROM test.events WHERE msisdn = '1f560177b8b5bbebfa938' and msisdn_mod = modulo(sipHash64('1f560177b8b5bbebfa938'), 1000) ;

5、性能验证

•5 台普通服务器，SATA 盘，单节点 32core

•数据量级：829 亿

•数据总量

•模拟手机号查询，冷查询 1s 返回

•对比原生查询，提升上百倍

6、查询分析结论

•取模 1000，相当于数据扫描达到减少 1/1000，性能效果不错。

•取模之后，数据扫描量会大大减少，同时利用了 CK 索引机制，相当于有一层 cache

•根据 CK 的索引特性，靠左原则，越处于后面的索引，需要扫描的数据越多，需要的时间越长

结束语

大数据分析技术发展还是挺快的，尤其业内也有比较多的开源技术出现，像 ClickHouse、Doris 这样的技术。ClickHouse 是本身开源版本，内部做了不少工作，比如说 ClickHouse 的云原生改造，本身它只支持 Local 部署的模式，做到了存储计算分离，就能比较容易地基于 K8s 去调动算力等。

另外业内云原生数据分析公司的成功，可以看到越来越多的云原生能力，包括 AI 支持和数据分析、湖仓一体、批流一体等等，技术一直在持续推进。