ClickHouse深度总结

一、简介

1.1ClickHouse是什么

ClickHouse 是一个用于联机分析 (OLAP)的列式数据库管理系统( DBMS)，来自于俄罗斯本土搜索引擎企业 Yandex 公司，是为世界第二大web分析平台（Yandex.Metrica）所开发 2016年开源，开发语言是C++,是一款PB级的交互式分析数据库。ClickHouse的性能超过了目前市场上可比的面向列的DBMS，每秒钟每台服务器每秒处理数亿至十亿多行和数十千兆字节的数据。

1.2CK的热度

与其他开源分析数据库的受欢迎程度对比

olap数据库	开源时间	GitHub start
ClickHouse	2016	22.3k
Kylin	2015	3.3k
Doris	2017	4k

贡献者

1.2.1 头部互联网公司积极使用

• 今日头条内部用ClickHouse来做用户行为分析，内部一共几千个ClickHouse节点，单集群最大1200节点，总数据量几十PB，日增原始数据300TB左右。

• 腾讯内部用ClickHouse做游戏数据分析，并且为之建立了一整套监控运维体系。

• 携程内部从18年7月份开始接入试用，目前80%的业务都跑在ClickHouse上。每天数据增量十多亿，近百万次查询请求。

• 快手内部也在使用ClickHouse，存储总量大约10PB， 每天新增200TB， 90%查询小于3S。

• ....

二、ClickHouse特点

2.1 列式存储

以下表为例

id	name	age
1	张三	12
2	李四	13
3	王五	14

行式存储

列式存储

2.1.1 两者的区别：

行式存储应用场景在查询某个人的所有属性，只需根据索引一次磁盘查找加顺序读取即可。但是当列变多，每次只查询一列或者某几列（统计所有人年龄平均值），如果还是行式存储数据，需要全表扫描，遍历很多数据都是不需要的。

2.1.2 列式存储的特性

1. 对于列的聚合、计数、求和等操作远远优于行式存储。

2. 由于相同列数据类型相同，压缩效果显著，节省存储空间、降低存储成本。

3. 针对不同的列类型，可以选择更适用的压缩算法，优化压缩比。

4. 高压缩比，意味着同等内存大小，能够存放更多数据，系统cache效果更好。同时增加了数据传输io速度。

2.2 DBMS 的功能

几乎覆盖了标准 SQL 的大部分语法，包括 DDL 和 DML，用户管 理及权限管理，数据的备份与恢复。

2.3 多样化引擎

根据表的不同需求可以设定不同的存储引擎。目前包括MergeTree Family、Log Family、Integrations和Special四大类 20 多种引擎。

引擎的名称大小写敏感

2.3.1 MergeTree家族：

Clickhouse 中最强大的表引擎当属 MergeTree （合并树）引擎及该系列（MergeTree）中的其他引擎。 MergeTree 系列的引擎被设计用于插入极大量的数据到一张表当中。数据可以以数据片段的形式一个接着一个的快速写入，数据片段在后台按照一定的规则进行合并。相比在插入时不断修改（重写）已存储的数据，这种策略会高效很多。

2.3.1.1 MergeTree:

• 存储的数据按主键排序。 这使得能够创建一个小型的稀疏索引来加快数据检索。

• 如果指定了 分区键 的话，可以使用分区。 在相同数据集和相同结果集的情况下 ClickHouse 中某些带分区的操作会比普通操作更快。查询中指定了分区键时 ClickHouse 会自动截取分区数据。这也有效增加了查询性能。

• 支持数据副本。 ReplicatedMergeTree 系列的表提供了数据副本功能

• 支持数据采样。需要的话，您可以给表设置一个采样方法。

--MergeTree建表语句
CREATE TABLE [IF NOT EXISTS] [db_name.]table_name (
name1 [type] [DEFAULT|MATERIALIZED|ALIAS expr],
name2 [type] [DEFAUErEMAMLERLALLIZED|ALIAS expr],
省略...
) ENGINE = MergeTree()
[PARTITION BY expr]
[ORDER BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[SETTINGS name=value, 省略...]

order by 设定了分区内的数据按照哪些字段顺序进行有序保存。 order by 是 MergeTree 中唯一一个必填项，甚至比 primary key 还重要，因为当用户不 设置主键的情况，很多处理会依照 order by 的字段进行处理（比如去重和汇总）。 要求：主键必须是 order by 字段的前缀字段。 比如 order by 字段是 (id,sku_id) 那么主键必须是 id 或者(id,sku_id)

2.3.1.2 ReplacingMergeTree:

分区内主键去重 确保数据最终被去重，但是无法保证查询过程中主键不重复。因为相同主键的数据可能被 shard 到不同的节点，但是 compaction 只能在一个节点中进行，而且optimize 的时机也不确定

2.3.1.3 CollapsingMergeTree:

引擎要求在建表语句中指定一个标记列 Sign（插入的时候指定为 1，删除的时候指定为 -1），后台 Compaction 时会将主键相同、Sign 相反的行进行折叠，也即删除。来消除 ReplacingMergeTree 的限制。

2.3.1.4 VersionedCollapsingMergeTree:

允许快速写入不断变化的对象状态。删除后台中的旧对象状态。 这显着降低了存储体积。 为了解决 CollapsingMergeTree 乱序写入情况下无法正常折叠问题，VersionedCollapsingMergeTree 表引擎在建表语句中新增了一列 Version，用于在乱序情况下记录状态行与取消行的对应关系。主键相同，且 Version 相同、Sign 相反的行，在 Compaction 时会被删除。

2.3.1.5 SummingMergeTree：

对主键列进行预先聚合。 在后台 Compaction 时，会将主键相同的多行进行 sum 求和，然后使用一行数据取而代之，从而大幅度降低存储空间占用，提升聚合计算性能。

2.3.1.6 AggregatingMergeTree：

高级预先聚合 SummingMergeTree 对非主键列进行 sum 聚合，而 AggregatingMergeTree 则可以指定各种聚合函数。（如聚合bitmap）

2.3.2 Log家族

这些引擎是为了需要写入许多小数据量（少于一百万行）的表的场景而开发的。 以列文件的形式保存在磁盘上，不支持索引，没有并发控制。一般保存少量数据的小表， 生产环境上作用有限。可以用于平时练习测试用。

2.3.2.1 StripeLog

2.3.2.2 Log

将每一列存储在一个单独的文件中

2.3.2.3 TinyLog

最简单的引擎 TinyLog 引擎是该系列中最简单的引擎并且提供了最少的功能和最低的性能。TinyLog 引擎不支持并行读取和并发数据访问，并将每一列存储在不同的文件中。它比其余两种支持并行读取的引擎的读取速度更慢，并且使用了和 Log 引擎同样多的描述符。你可以在简单的低负载的情景下使用它。

-- 创建TinyLog引擎表
create table t_tinylog ( id String, name String) engine=TinyLog;

2.3.3 Integrations

用于与其他的数据存储与处理系统集成的引擎。 该类型的引擎：

• Kafka

• MySQL

• ODBC

• JDBC

• HDFS

2.2.4 Special

用于其他特定功能的引擎

该类型的引擎：

• Distributed

• MaterializedView

• Dictionary

• Merge

• File

• Null

• Set

• Join

• URL

• View

• Memory 内存引擎，数据以未压缩的原始形式直接保存在内存当中，服务器重启数据就会消失。 读写操作不会相互阻塞，不支持索引。简单查询下有非常非常高的性能表现（超过 10G/s）。 一般用到它的地方不多，除了用来测试，就是在需要非常高的性能，同时数据量又不太 大（上限大概 1 亿行）的场景

• Buffer

2.4 高吞吐写入能力（批量写入）

ClickHouse 采用类 LSM Tree的结构，数据写入后定期在后台 Compaction。通过类 LSM tree的结构，ClickHouse 在数据导入时全部是顺序 append 写，写入后数据段不可更改，在后台compaction 时也是多个段 merge sort 后顺序写回磁盘。顺序写的特性，充分利用了磁盘的吞吐能力，即便在 HDD 上也有着优异的写入性能。官方公开 benchmark 测试显示能够达到 50MB-200MB/s 的写入吞吐能力，按照每行100Byte 估算，大约相当于 50W-200W 条/s 的写入速度。

2.5 数据分区与线程级并行

ClickHouse 将数据划分为多个 partition，每个 partition 再进一步划分为多个index granularity(索引粒度)，然后通过多个 CPU核心分别处理其中的一部分来实现并行数据处理。在这种设计下，单条 Query 就能利用整机所有 CPU。极致的并行处理能力，极大的降低了查询延时。所以，ClickHouse 即使对于大量数据的查询也能够化整为零平行处理。但是有一个弊端就是对于单条查询使用多 cpu，就不利于同时并发多条查询。所以对于高 qps 的查询业务，ClickHouse 并不是强项

三、Sql操作

基本上来说传统关系型数据库（以 MySQL 为例）的 SQL 语句，ClickHouse 基本都支持

3.1 Insert

基本与标准 SQL（MySQL）基本一致

1. 常规插入 insert into [table_name] values(…),(….)

2. 从表到表的插入 insert into [table_name] select a,b,c from [table_name_2]

3.2 Update和Delete

ClickHouse 提供了 Delete 和 Update 的能力，这类操作被称为 Mutation(突变) 查询，它可以看做 Alter 的一种。 虽然可以实现修改和删除，但是和一般的 OLTP 数据库不一样，Mutation 语句是一种很“重”的操作，而且不支持事务。 “重”的原因主要是每次修改或者删除都会导致放弃目标数据的原有分区，重建新分区。 所以尽量做批量的变更，不要进行频繁小数据的操作。

1. 删除操作 alter table t_order_smt delete where sku_id ='sku_001';

2. 修改操作 alter table t_order_smt update total_amount=toDecimal32(2000.00,2) where id =102; 由于操作比较“重”，所以 Mutation 语句分两步执行，同步执行的部分其实只是进行新增数据新增分区和并把旧分区打上逻辑上的失效标记。直到触发分区合并的时候，才会删除旧数据释放磁盘空间，（一般不会开放这样的功能给用户，由管理员完成）。

3.3 Select

ClickHouse 基本上与标准 SQL 差别不大

• 支持子查询

• 支持 CTE(Common Table Expression 公用表表达式 with 子句)

• 支持各种 JOIN，但是 JOIN 操作无法使用缓存，所以即使是两次相同的 JOIN 语句， ClickHouse 也会视为两条新 SQL

• 窗口函数

• 不支持自定义函数

• GROUP BY 操作增加了 with rollup\with cube\with total

3.4 Alter

同关系型数据库类似,语法如下 ALTER TABLE [db].name [ON CLUSTER cluster] ADD|DROP|CLEAR|COMMENT|MODIFY COLUMN ...

• 新增字段 alter table tablename add column newcolname String after col1 '';

• 修改字段类型 alter modify

• 删除字段 alter drop

四、副本

1. 副本存在是为了保证数据的高可用，单台服务器宕机，如果设置了副本，依然可以从其他服务器获取数据。

2. 副本只能同步数据，不能同步表结构，所以需要在每台机器上自己手动建表

3. Replication是只支持MergeTree family的表引擎 建表语句： CREATE TABLE table_name ( ... ) ENGINE = ReplicatedMergeTree('zookeeper_name_configured_in_auxiliary_zookeepers:path', 'replica_name') ...

五、分片集群

• 分片集群实现了在多个服务器上进行分布式查询的功能。

• 读是自动并行的。读取时，远程服务器表的索引（如果有的话）会被使用。

• 分布式表引擎本身不存储数据，通过分布式逻辑表来写入、分发、路由来操作多台节点不同分片的分布式数据。

建表语句： CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]( name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1], name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2], ...) ENGINE = Distributed(cluster, database, table[, sharding_key[, policy_name]])[SETTINGS name=value, ...] 如果表已经存在 CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster] AS [db2.]name2 ENGINE = Distributed(cluster, database, table[, sharding_key[, policy_name]]) [SETTINGS name=value, ...]

分布式引擎参数：

1. cluster - 服务为配置中的集群名

2. database - 远程数据库名

3. table - 远程数据表名

4. sharding_key - (可选) 分片

5. keypolicy_name - (可选) 规则名，它会被用作存储临时文件以便异步发送数据

六、使用场景--用户画像

6.1 传统方案：Elasticsearch+Hbase

 

方案劣势：

• ES全索引,消耗大量内存资源，硬件成本高

• ES数据写入构建索引，性能随写入量衰减明显，大数据量写入会有瓶颈，造成系统不稳定

• 标签更新需要重建索引，成本高效率低

• ES缺少SQL支持

6.2 基于ClickHouse构建标签画像

方案优势：

• ClickHouse 实时数据落盘，具备高吞吐写入能力

• ClickHouse 索引为稀疏索引，内存消耗少，硬件成本低

• ClickHouse 数据有序存储，配合稀疏索引，查询效率高

• ClickHouse 根据引擎和数据类型，优化选择压缩算法，压缩比高，存储成本低，查询效率高

• ClickHouse 支持SQL，更简单

6.2.1标签表结构演进（一）

表结构：

tag_id	user_id
tagnum1	101
tagnum1	102
tagnum2	101
tagnum3	101

#####DDL
CREATE TABLE ......ORDER BY (tag_id，user_id)
engine=MergeTree()
###用户圈选，交集
SELECT user_id FROM xxx WHERE tag_id=1 AND
user_id IN （select user_id from xxx
where tag_id =2
）
where tagID= 1
###用户画像，基于人群进行画像分析
SELECT tag_id ,count(1) FROM xxx AND
uid IN （select user_id from xxx
where tag_id =2
）and tag_id in (4,5,6,7)
GROUP BY tag_id

优势：

• 表结构简单，标签扩展简单

• 同一个uid 数据只在shard，增加并行查询效率 劣势：

• 标签和用户量增加，数据行数膨胀迅速，查询效率衰减明显

• 多条件嵌套查询SQL 复杂

6.2.2 标签表结构演进（二）

user_id	tag1	tag2	tagN
101	F	10	20
101	M	20	30
102	M	10	30
...	...	...	...

#####DDL:
CREATE TABLE（ ......
user_id UInt64,
tag1 SimpleAggregateFunction(anyLast, Nullable(Enum(‘女’ = F, '男’ = M)
.........
)
ORDER BY uid ENGINE= AggregatingMergeTree()
###用户圈选，交集
SELECT uid FROM table_users WHERE anyLastMerge(tag1)=‘F ’ AND
anyLast(tag2)= 10
### 用户标签详情
SELECT uid FROM table_users WHERE anyLastMerge（tagID）=‘F ’
AND anylastMerge（tag2）= 10
###用户画像，基于已有人群进行用户画像分析
SELECT tag1 ,count(1) FROM table_users AND
user_id IN （select user from xxx
where tag2 =10
）GROUP BY tag

优缺点：

• 数据行数减少，SQL 语句简单

• SimpleAggregateFunction （anylast,...）支持标签值更新

• 交，并，差，SQL表达复杂（在线分析效率低）

6.2.3 标签表结构演进（三）

tag	tag_value	user_ids(bitmap)
tag1	F	(101,102,103,,,)
tag1	M	(104,105,,,)
tag3	20	(101,,,)

优点：

• 存储空间：bit位存储，极大降低存储空间

• 效率高：bitmap进行与、或、差、非等操作比join、in指数级提升效率

• 易扩展、松耦合：增减标签、只需操作一行数据

• sql简洁: ck自带大量bitmap操作函数，轻松实现各种需求

6.2.3.1 数据存储与同步：

###离线任务计算标签结果：
create table label_local(
tag String ,
tag_value String ,
user_id UInt64
)
ENGINE = MerTree()
partition by tag
order by tag,tag_value
​
### bitmap标签表
create table label_bitmap_local
(
tag String comment '标签名称'，
tag_value String comment '标签值'，
user_ids AggregatingMergeTree()
)
partition by tag
order by (tag,tag_value)
settings index_granularity = 128;
​
###数据导入
insert into table  label_bitmap_local
select 
tag,
tag_value,
groupBitmapState(user_id)
from label_local
group by tag,tag_value

6.2.3.2 使用bitmap做人群（群体）圈选

with
(
    select  groupbitmapMergeState(user_ids)  //获取满足条件数据包
    from label_bitmap_local
    where tag='性别' and tag_value = '女'
)as user_group_1,
(
    select  groupbitmapMergeState(user_ids) 
    from label_bitmap_local
    where tag='年龄段' and tag_value = '18-30'
) as user_group_2 
select  bitmapToArray(bitmapAnd(user_group_1,user_group_2)) //求交集，转数组
### Result = (性别=女生) ∩（年龄段=18-30）= 年轻女性

6.2.3.3 群体分析

###人群预估，群体基数
select bitmapCardinality(bitmapAnd(user_group_1,user_group_2)) 
###查询个人是否存在群体中
select bitmapContains(bitmapAnd(user_group_1,user_group_2),user_id)
### 查询群体是否存在公共元素
select bitmapHasAny(user_group_1,user_group_2) 
### 群体是否包含另一群体所有
select bitmapHasAll(user_group_1,user_group_2)
###圈定群体（年轻女性）其他情况分布（消费水平）
​
with
(
select bitmapAnd(user_group_1,user_group_2) 
) as filter_users
​
select  bitmapCardinality(bitmapAnd(filter_users, group_by_users)) AS count,    
tag,  
tag_value 
from (
        SELECT    groupBitmapMergeState(user_ids) AS group_by_users,    tag,    tag_value
        FROM lable_bitmap_local
        WHERE tag = "消费水平"
        GROUP BY (tag, tag_value)
        );
  //年轻女性群体消费能力分布

七、 缺点、局限

• 不支持事务，不支持真正的删除/更新；

• 数据一致性差，或者只能保证最终数据一致性

• 适用宽边多维度分析查询，多表join性能差 可以使用物化视图进行优化

• 不支持高并发，官方建议qps为100 Clickhouse快是由于采用了并行处理机制，即便一个查询，也会用服务器一半的CPU去执行，因此ClickHouse不能支持高并发的使用场景，默认单查询使用CPU核数为服务器核数的一半

八、优化

• 建议每次写入多于1000行的批量写入，或每秒不超过一个写入请求

• 同一个分区的 两个part的数据量都很大，然后后台自动的merge合并成一个的时候，会消耗大量的cpu 控制merge的线程,后台默认的线程数是16 降低线程数的配置; 实时的数据尽量在ssd上.

• 选择更加合适的引擎

• 避免数据出现大量空值字段 空值会被单独存储，字段可以设置默认值

• ...