本篇主题：基于3W1H原则，讲解ClickHouse，以便于后续开发实践。

中文文档：https://clickhouse.com/docs/zh/operations/performance-test/

1 clickhouse是什么？

ClickHouse 是俄罗斯的Yandex于2016年开源的列式存储数据库（DBMS），使用C++语言编写，主要用于在线分析处理查询（OLAP），能够使用SQL查询实时生成分析数据报告。

tips:

OLTP（on-line transaction processing）翻译为联机事务处理， OLAP（On-Line Analytical Processing）翻译为联机分析处理，从字面上来看OLTP是做事务处理，OLAP是做分析处理。从对数据库操作来看，OLTP主要是对数据的增删改，OLAP是对数据的查询。

1.1 Why we choose it

1.1.1 经典架构及分析

架构分析

数据的实效性，中间过程经过Kafka、ETL、调度处理，报表的实效性不理想

即席分析性能，Hive存储是hdfs文件系统，查询效率不高，不适合即席查询

涉及Hadoop组件多

数据链路长，数据链路处理流程长，繁琐容错也不好

1.1.2 为啥选择ClickHouse

ClickHouse独立于hadoop生态之外，开源的OLAP数据库

支持海量数据，实时导入，适合在线查询，多维 聚合分析

真正的面向列的DBMS，SQL语法支持

简单、开箱即用

主主架构，支持线性扩展

其他原因，请看下方特性

1.2 clickhouse特点

1、完备的DBMS功能

ClickHouse拥有完备的管理功能，所以它称得上是一个DBMS（Database Management System，数据库管理系统），而不仅是一个数据库。作为一个DBMS，它具备了一些基本功能，如下所示。

DDL（数据定义语言）：可以动态地创建、修改或删除数据库、表和视图，而无须重启服务。

DML（数据操作语言）：可以动态查询、插入、修改或删除数据。

权限控制：可以按照用户粒度设置数据库或者表的操作权限，保障数据的安全性。

数据备份与恢复：提供了数据备份导出与导入恢复机制，满足生产环境的要求。

分布式管理：提供集群模式，能够自动管理多个数据库节点。

这里只列举了一些最具代表性的功能，但已然足以表明为什么Click House称得上是DBMS了。

2、列式存储与数据压缩

列式存储和数据压缩，对于一款高性能数据库来说是必不可少的特性。一个非常流行的观点认为，如果你想让查询变得更快，最简单且有效的方法是减少数据扫描范围和数据传输时的大小，而列式存储和数据压缩就可以帮助我们实现上述两点。列式存储和数据压缩通常是伴生的，因为一般来说列式存储是数据压缩的前提。

列式存储相比于行式存储，列式存储在分析场景下有着许多优良的特性。

分析场景中往往需要读大量行但是少数几个列。 

在行存模式下，数据按行连续存储，所有列的数据都存储在一个block中，不参与计算的列在IO时也要全部读出，读取操作被严重放大。

列存模式下，只需要读取参与计算的列即可，极大的减低了IO cost，加速了查询。

同一列中的数据属于同一类型，压缩效果显著，压缩比高。 

列存往往有着高达十倍甚至更高的压缩比，节省了大量的存储空间，降低了存储成本。

更高的压缩比意味着更小的data size，从磁盘中读取相应数据耗时更短。

自由的压缩算法选择。clickhouse默认使用LZ4算法压缩，数据总体的压缩比可以达到8:1。

不同列的数据具有不同的数据类型，适用的压缩算法也就不尽相同。可以针对不同列类型，选择最合适的压缩算法。

高压缩比，意味着同等大小的内存能够存放更多数据，系统cache效果更好。

官方显示，通过列存储，在某些分析场景下，能获得100倍甚至更高的加速效果。

3、向量化执行引擎

clickhouse不仅将数据按列存储，而且按列进行计算。传统的OLTP数据库通常采用按行计算，原因是事务处理中以点查为主，SQL计算量小，实现这些技术的收益不够明显，单个SQL所涉及计算量可能极大，将每行作为一个基本单元进行处理会带来严重的性能损耗：

1）对每一行数据都要调用相应的函数，函数调用开销占比高；

2）存储层按列存储数据，在内存中按列组织，但是计算层按行处理，无法充分利用CPU Cache的预读能力，造成CPU Cache miss严重；

3）按行处理，无法利用高效的SIMD指令

ClickHouse实现了向量化执行引擎（Vectorized execution engine），对内存中的列式数据，一个batch调用一次SIMD指令（而非每一行调用一次），不仅减少函数调用次数，降低Cache miss，而且可以充分发挥SIMD指令的并行能力，大幅度缩短了计算耗时。向量执行引擎，通常能够带来数倍的性能提升。

SIMD全称Sigle Instuction Multiple Data，单指令多数据流，能够赋值多个操作数，并把它们打包在大型寄存器的一组指令集。以同步方式，在同一时刻执行同一条指令。

4、关系模型与SQL查询

相比HBase和Redis这类NoSQL数据库，ClickHouse使用关系模型描述数据并提供了传统数据库的概念（数据库、表、视图和函数等）。

与此同时，ClickHouse完全使用SQL作为查询语言（支持GROUP BY、ORDER BY、JOIN、IN等大部分标准SQL）。

5、多样化的表引擎

ClickHouse和MySQL类似，把表级的存储引擎插件化，根据表的不同需求可以设定不同的存储引擎。目前包括合并树、日志、接口和其他四大类20多种引擎。

不同的引擎决定了表数据的存储特点和表数据的操作行为：

1）决定表存储在哪里以及以何种方式存储

2）支持哪些查询以及如何支持

3）并发数据访问

4）索引的使用

5）是否可以执行多线程请求

6）数据复制参数

表引擎决定了数据在文件系统的存储方式，常用的也是官方推荐的MergeTree系列，如果需要数据副本的话可以使用ReplicateMergeTree系列，相当于MergeTree的副本版本。读取集群数据需要使用分布式表引擎Distribute。

6、多线程与分布式

如果说向量化执行是通过数据级并行的方式提升了性能，那么多线程处理就是通过线程级并行的方式实现了性能的提升。相比基于底层硬件实现的向量化执行SIMD，线程级并行通常由更高层次的软件层面控制。由于SIMD不适合用于带有较多分支判断的场景，ClickHouse也大量使用了多线程技术以实现提速，以此和向量化执行形成互补。

ClickHouse使用分布式分而治之的思想，利用多台服务的资源协同处理。在数据层面预先将数据分布到各太服务器，在计算层面，将数据的计算查询直接下推到数据所在服务器。在数据存取方面，既支持分区（纵向扩展，利用多线程原理），也支持分片（横向扩展，利用分布式原理），可以说是将多线程和分布式的技术应用到了极致。

7、多主架构

HDFS、Spark、HBase和Elasticsearch这类分布式系统，都采用了Master-Slave主从架构，由一个管控节点作为Leader统筹全局。而ClickHouse则采用Multi-Master多主架构，集群中的每个节点角色对等，客户端访问任意一个节点都能得到相同的效果。这种多主的架构有许多优势，例如对等的角色使系统架构变得更加简单，不用再区分主控节点、数据节点和计算节点，集群中的所有节点功能相同。所以它天然规避了单点故障的问题，非常适合用于多数据中心、异地多活的场景。

8、数据分片与分布式查询

数据分片是将数据进行横向切分，这是一种在面对海量数据的场景下，解决存储和查询瓶颈的有效手段，是一种分治思想的体现。

ClickHouse支持分片，而分片则依赖集群。每个集群由1到多个分片组成，而每个分片则对应了ClickHouse的1个服务节点。分片的数量上限取决于节点数量（1个分片只能对应1个服务节点）。

ClickHouse并不像其他分布式系统那样，拥有高度自动化的分片功能。ClickHouse提供了本地表（Local Table）与分布式表（Distributed Table）的概念。一张本地表等同于一份数据的分片。而分布式表本身不存储任何数据，它是本地表的访问代理，其作用类似分库中间件。借助分布式表，能够代理访问多个数据分片，从而实现分布式查询。

这种设计类似数据库的分库和分表，十分灵活。例如在业务系统上线的初期，数据体量并不高，此时数据表并不需要多个分片。所以使用单个节点的本地表（单个数据分片）即可满足业务需求，待到业务增长、数据量增大的时候，再通过新增数据分片的方式分流数据，并通过分布式表实现分布式查询。

1.3 clickhouse性能测试

官网提供的测试：https://clickhouse.com/benchmark/dbms/

专家测试：http://www.clickhouse.com.cn/topic/5c453371389ad55f127768ea

1.4 clickhouse适用和不适用场景

官网文档：OLAP使用场景

总结：

数据只是添加到数据库，没有必要修改

读取数据时，会从数据库中提取出大量的行，但只用到一小部分列

大宽表，读大量行但是少量列，结果集较小，列的值是比较小的数值和短字符串

数据需要以大批次（大于1000行）进行更新，而不是单行更新；或者根本没有更新操作

无需事务，数据一致性要求低

每次查询中只会查询一个大表。除了一个大表，其余都是小表

查询结果显著小于数据源。即数据有过滤或聚合。返回结果不超过单个服务器内存大小

2 clickhouse的使用

官网在线测试链接：https://play.clickhouse.tech/?file=welcome

2.1 数据类型

基础类型只有数值、字符串和时间三种类型，没有 Boolean 类型，但可以使用整型的 0 或 1 替代。

ClickHouse 的数据类型和常见的其他存储系统的数据类型对比：

看官网：https://clickhouse.tech/docs/zh/sql-reference/data-types/

说明：Clickhouse的字段默认是不允许为NULL的，如果数据有可能为NULL，需要将字段定义为类似Nullable(Int64)的类型。

2.2 存储引擎

ClickHouse 提供了大约 28 种表引擎，各有各的用途，比如有 Log 系列用来做小表数据分析，MergeTree 系列用来做大数据量分析，而 Integration 系列则多用于外表数据集成。再考虑复制表Replicated 系列，分布式表 Distributed 等，纷繁复杂，新用户上手选择时常常感到迷惑。

ClickHouse表引擎一共分为四个系列，分别是Log、MergeTree、Integration、Special。其中包含了两种特殊的表引擎Replicated、Distributed，功能上与其他表引擎正交，根据场景组合使用。

最强大的表引擎当属 MergeTree （合并树）引擎及该系列（*MergeTree）中的其他引擎。对于大多数正式的任务，推荐使用MergeTree 族中的引擎。因为只有合并树系列的表引擎才支持主键索引、数据分区、数据副本和数据采样这些特性，同时也只有此系列的表引擎支持ALTER相关操作。

Log、Special、Integration 主要用于特殊用途，场景相对有限。MergeTree 系列才是官方主推的存储引擎，支持几乎所有 ClickHouse 核心功能。

存储引擎，去查阅官方文档

2.2.1 表引擎概览

一共分为四个系列，分别是Log、MergeTree、Integration、Special。其中包含了两种特殊的表引擎Replicated、Distributed，功能上与其他表引擎正交。表引擎（即表的类型）决定了：

（1）数据的存储方式和位置，写到哪里以及从哪里读取数据

（2）支持哪些查询以及如何支持。

（3）并发数据访问。

（4）索引的使用（如果存在）。

（5）是否可以执行多线程请求。

（6）数据复制参数。

2.2.2 翻牌子存储引擎如何选

1、Log系列

Log系列表引擎功能相对简单，主要用于快速写入小表（1百万行左右的表），然后全部读出的场景。

几种Log表引擎的共性是：

数据被顺序append写到磁盘上；

不支持delete、update；

不支持index；

不支持原子性写；

insert会阻塞select操作。

它们彼此之间的区别是：

TinyLog：不支持并发读取数据文件，查询性能较差；格式简单，适合用来暂存中间数据；

StripLog：支持并发读取数据文件，查询性能比TinyLog好；将所有列存储在同一个大文件中，减少了文件个数；

Log：支持并发读取数据文件，查询性能比TinyLog好；每个列会单独存储在一个独立文件中。

2、Integration系列

该系统表引擎主要用于将外部数据导入到ClickHouse中，或者在ClickHouse中直接操作外部数据源。

Kafka：将Kafka Topic中的数据直接导入到ClickHouse；

MySQL：将Mysql作为存储引擎，直接在ClickHouse中对MySQL表进行select等操作；

JDBC/ODBC：通过指定jdbc、odbc连接串读取数据源；

HDFS：直接读取HDFS上的特定格式的数据文件；

3、Special系列

Special系列的表引擎，大多是为了特定场景而定制的。这里也挑选几个简单介绍，不做详述。

Memory：将数据存储在内存中，重启后会导致数据丢失。查询性能极好，适合于对于数据持久性没有要求的1亿一下的小表。在ClickHouse中，通常用来做临时表。

Buffer：为目标表设置一个内存buffer，当buffer达到了一定条件之后会flush到磁盘。

File：直接将本地文件作为数据存储；

Null：写入数据被丢弃、读取数据为空；

4、MergeTree系列

Log、Special、Integration主要用于特殊用途，场景相对有限。MergeTree系列才是官方主推的存储引擎，支持几乎所有ClickHouse核心功能。

1、一般情况下使用MergeTree引擎即可。

2、子类引擎使用

特殊功能：发生在分区合并时

ReplacingMergeTree：解决数据去重问题，有相同主键无法去重的问题，主要基于order by后的字段进行去重。

CollapsingMergeTree：数据删除问题，以增代删。缺陷：多线程情况下，乱序写入，无法删除。

VersionedCollapsingMergeTree：数据删除问题，以增代删。解决CollapsingMergeTree缺陷

SummingMergeTree：特定数据合并问题，主要基于order by后的字段进行sum聚合。

AggregatingMergeTree：特定数据合并问题，主要基于order by后的字段进行AggregateFunction 类型的聚合

5、Distributed 分布式引擎

Distributed 分布式引擎本身不存储数据, 但可以在多个服务器上进行分布式查询。

2.3 clickhouse的SQL特性

参见知识库其他文档：clickhouse的语法特性 vs maxcomputer

2.4 ClickHouse的函数

ClickHouse中至少存在两种类型的函数

常规函数：每一行执行一次函数计算一样（对于每一行，函数的结果不依赖于其他行）

聚合函数。 合函数则从各行累积一组值

arrayJoin函数 与 表函数 均属于第三种类型的函数

官网资料：https://clickhouse.com/docs/en/sql-reference/functions/

自定义函数实现，知识库《ClickHouse源码：函数&自定义函数》

3 实践与应用

3.1 基于ClickHouse数仓分层

ck适合的数据模型

ck数仓数据模型采用星型模型搭建，星型架构是一种非正规化的结构，多维数据集的每一个维度都直接与事实表相连，不存在渐变维度，所以数据有一定冗余。因为有冗余，所以很多统计不需要做外部的关联查询，因此一般情况下效率比雪花模型高。

说明：使用星型模型搭建即最大限度的允许数据冗余，减少必要的外表连接，提高sql效率

3.2 电信用户画像案例分析

基于clickHouse数据架构

1、数据流程处理

2、任务调度

3、数据导出实现

定义引擎类别MySQL，直接进行操作。

clear_dx_user_group_001 数据源是mysql；export_dx_user_group_001数据源是clickhouse。

4、bitmap函数在用户画像中实现

核心表：

CREATE TABLE IF NOT EXISTS ctuserbasecoredb.app_dx_portrait_tag_umap_1d

(

    dw_gmt_create DateTime COMMENT '日期',

    ds FixedString(8) COMMENT '数据分区 yyyymmdd',

    tag_id        String  COMMENT '标签id',

    tag_value    String  COMMENT '标签值',

    us AggregateFunction(groupBitmap,UInt64) COMMENT '用户uid集合形成bitmapping'

)engine=AggregatingMergeTree()

partition by  ds

order by (tag_id,tag_value)

-- 指定数据生命周期为7天,数据备份，到指定卷或者磁盘

TTL dw_gmt_create  + INTERVAL 7 DAY

-- 索引粒度，默认8192

SETTINGS index_granularity = 8192

COMMENT '标签-标签聚合用户表-全量表(tag->uidbitmap)';

聚集：

INSERT INTO TABLE ctuserbasecoredb.app_dx_portrait_tag_umap_1d

select

    now() AS dw_gmt_create

    ,'${before_date_str}' AS ds

    ,t.tag_id

    ,t.tag_value

    ,groupBitmapState(t.bit_map_id) AS us

from (

    select

        s1.tag_id

          ,s1.out_user_id

          ,s1.tag_value

          ,s2.bit_map_id

    from(

        SELECT

            tag_id

              ,out_user_id

              ,tag_value

        FROM ctuserbasecoredb.dwb_dx_portrait_user_lni_act_1d

        where ds = '${before_date_str}'

        UNION ALL

        select

            tag_id

              ,out_user_id

              ,tag_value

        FROM ctuserbasecoredb.dwb_dx_portrait_user_attr_1d

        where ds = '${before_date_str}'

    )s1

    join (

    select out_user_id,bit_map_id

    from ctuserbasecoredb.dim_dx_user_bitmap_id_df

    where ds = '${before_date_str}'

    )s2

    on s1.out_user_id = s2.out_user_id

)t

group by t.tag_id,t.tag_value;

关键SQL:

  insert into app_dx_portrait_group_tag_aggregation_1d

  select now() as dw_gmt_create

  ,'{self.ds_partition}' as ds

  ,'{group_id}' as group_id

  ,s2.out_user_id as out_user_id

  ,s2.tag_detail

  ,now() as base_time

  from ( select arrayJoin(bitmapToArray(t.us)) as id from ({group_tag_conditions_sql})t) s1

  join (

    select out_user_id,bit_map_id,tag_detail

    from dim_dx_user_bitmap_id_df

    where ds = '20211013'

  )s2

  on s1.id = s2.bit_map_id

group_tag_conditions_sql：

select arrayJoin(bitmapToArray(t.us)) as id

from (

  select groupBitmapOrState(us) as us from (

    select groupBitmapAndState(us) as us from (

      select us from app_dx_portrait_tag_umap_1d where ds='20211013' and tag_id='DX_01_AB_0001' and tag_value = 'true'

      UNION ALL 

      select us from app_dx_portrait_tag_umap_1d where ds='20211013' and tag_id='DX_01_AA_0001' and tag_value = 'true'

      UNION ALL 

      select us from app_dx_portrait_tag_umap_1d where ds='20211013' and tag_id='DX_01_AA_0002' and tag_value = '电信'

      UNION ALL 

      select groupBitmapOrState(us) as us from app_dx_portrait_tag_umap_1d where ds='20211013' and tag_id='DX_01_AA_0003' and tag_value in ('未知', '浙江', '上海', '北京', '湖北')

    )

)

)

3.3 ClickHouse监控

知识库文档：ClickHouse 监控平台

https://clickhouse.com/docs/zh/operations/monitoring/

这是官网给出的方案：基于prometheus + grafana生态。

测试环境：http://172.31.28.31:3000/d/10Bw94W7z/clickhousece-shi-huan-jing?orgId=1

4 clickhouse优化

4.1 Clickhouse SQL常规优化

单表查询：

Prewhere替代where

列裁剪与分区裁剪，代替使用select *

orderby 结合where、limit

避免构建虚拟列（as xx）,不要在结果集上构建虚拟列，虚拟列非常消耗资源浪费性能，可以考虑在前

uniqCombined替代distinct ，uniqCombined底层采用类似HyperLogLog算法实现，能接收2%左右的数据误差，可直接使用这种去重方式提升查询性能。

多表查询：

用IN 代替JOIN 

insert into hits_v2

select a.* from hits_v1 a where a. CounterID in (select CounterID from

visits _v1

大小表JOIN ，小表在右 

insert into table hits_v2

select a.* from hits_v1 a left join visits_v 2 b on a. CounterID=b.

CounterID;

注意谓词下推（版本差异）,每个子查询提前完成过滤操作

分布式表使用GLOBAL

使用字典表，字典表会常驻内存，在需要进行关联分析的业务中使用

4.2 clickhouse explain

基本语法：

EXPLAIN [AST | SYNTAX | PLAN | PIPELINE] [setting = value, ...]

SELECT ... [FORMAT ...]

➢ PLAN：用于查看执行计划，默认值。

header 打印计划中各个步骤的head 说明，默认关闭，默认值0;

description 打印计划中各个步骤的描述，默认开启，默认值1；

actions 打印计划中各个步骤的详细信息，默认关闭，默认值0。

➢ AST ：用于查看语法树;

➢ SYNTAX：用于优化语法;

➢ PIPELINE：用于查看PIPELINE 计划。

header 打印计划中各个步骤的head 说明，默认关闭;

graph 用DOT图形语言描述管道图，默认关闭，需要查看相关的图形需要配合graphviz查看；

actions 如果开启了graph，紧凑打印打，默认开启。

注：PLAN 和PIPELINE 还可以进行额外的显示设置，如上参数所示。

-- 先做一次查询

SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'hello') FROM numbers(10);

-- 查看语法优化

EXPLAIN SYNTAX SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'hello') FROM numbers(10);

-- 开启三元运算符优化

SET optimize_if_chain_to_multiif = 1;

-- 再次查看语法优化

EXPLAIN SYNTAX SELECT number = 1 ? 'hello' : (number = 2 ? 'world' : 'hello') FROM numbers(10);

-- 返回优化后的语句

SELECT if(number = 1, 'hello', if(number = 2, 'world', 'hello')) FROM numbers(10);