<返回更多

ClickHouse 技术研究及语法简介

2023-06-21  京东云开发者  
加入收藏

本文对 Clickhouse 架构原理、语法、性能特点做一定研究，同时将其与 MySQL、elasticsearch、tidb 做横向对比，并重点分析与 mysql 的语法差异，为有 mysql 迁移 clickhouse 场景需求的技术预研及参考。

1 基础概念

Clickhouse 是一个用于联机分析（OLAP）的列式数据库管理系统（DBMS)。

1.1 集群架构

ClickHouse 采用典型的分组式的分布式架构，具体集群架构如下图所示：

1.2 数据分区

Clickhouse 是分布式系统，其数据表的创建，与 mysql 是有差异的，可以类比的是在 mysql 上实现分库分表的方式。

Clichhouse 先在每个 Shard 每个节点上创建本地表（即 Shard 的副本），本地表只在对应节点内可见；然后再创建分布式表 [Distributed]，映射到前面创建的本地表。

用户在访问分布式表时，ClickHouse 会自动根据集群架构信息，把请求转发给对应的本地表。

1.3 列式存储

相对于关系型数据库（RDBMS），是按行存储的。以 mysql 中 innodb 的主键索引为例，构建主键索引的 B + 树中，每个叶子节点存储的就是一行记录。

而列式数据库，是将一个表，按 column 的维护进行存储，“单次磁盘 I/O 拿到的是一列的数据”。

列式存储的优点

在查询时，只会读取涉及到的列，会大大减少 IO 次数 / 开销。并且 clickhouse 在存储时会按指定顺序排列数据，因此只需要按 where 条件指定列进行顺序扫描、多个列的扫描结果合并，即可找到满足条件的数据。

但由于 insert 数据时，是按行写入的，因此存储的过程会麻烦一些。

查询时的区别：

1.4 数据排序

每个数据分区内部，所有列的数据是按照 排序键（ORDER BY 列）进行排序的。

可以理解为：对于生成这个分区的原始记录行，先按 排序键 进行排序，然后再按列拆分存储。

1.5 数据分块

每个列的数据文件中，实际是分块存储的，方便数据压缩及查询裁剪，每个块中的记录数不超过 index_granularity，默认 8192，当达到 index_granularity 的值，数据会分文件。

1.6 向量化执行

在支持列存的基础上，ClickHouse 实现了一套面向向量化处理的计算引擎，大量的处理操作都是向量化执行的。

向量化处理的计算引擎：

基于数据存储模型，叠加批量处理模式，利用 SIMD 指令集，降低函数调用次数，降低硬件开销（比如各级硬件缓存），提升多核 CPU 利用率。

再加上分布式架构，多机器、多节点、多线程、批量操作数据的指令，最大限度利用硬件资源，提高效率。

注：SIMD 指令，单指令多数据流，也就是说在同一个指令周期可以同时处理多个数据。(例如：在一个指令周期内就可以完成多个数据单元的比较).

1.7 编码压缩

由于 ClickHouse 采用列存储，相同列的数据连续存储，且底层数据在存储时是经过排序的，这样数据的局部规律性非常强，有利于获得更高的数据压缩比。

同时，超高的压缩比又可以降低存储读取开销、提升系统缓存能力，从而提高查询性能。

1.8 索引

前面提到的列式存储，用于裁剪不必要的字段读取；

而索引，则用于裁剪不必要的记录读取（减少未命中数据的 IO)。

简单解释：

以主键索引为例，Clickhouse 存储数据时，会按排序键（ORDER BY) 指定的列进行排序，并按 Index_granularity 参数切分成块，然后会抽取每个数据块的首行，组织为一份稀疏的排序索引。

类比 B + 树的查找过程，如果 where 条件中包含主键列，就可以通过稀疏索引快速的过滤。稀疏索引对于范围查找比较高效。

二级索引，则是采用 bloom filter 来实现的：minmax，set，ngrambf/tokenbf。

1.9 适用场景

OLAP 分析领域有两个典型的方向：

既然是 OLAP 分析，对数据的使用有些基本要求：

2 横向对比

搬仓系统面临的是从十几亿数据中进行查询、聚合分析，从世面上可选的支持海量数据读写的中间件中搜集到，能够有支持类似场景、有比较轻量级的产品大概有 Clickhouse、ElasticSearch、TiDB。

2.1 clickhouse 与 ElasticSearch 对比

elastic 生态很丰富，es 作为其中的存储产品，从首个版本算起，已经有 10 年发展历史，主要解决的是搜索问题。es 的底层存储采用 lucene，主要包含行存储、列存储和倒排索引，利用分片与副本机制，解决了集群下搜索性能与高可用的问题。

es 的优势：

es 的局限性：

ClickHouse 与 Elasticsearch（排序与聚合查询） 一样，都采用列式存储结构，都支持副本分片，不同的是 ClickHouse 底层有一些独特的实现，如下：

网上资料：聚合查询的性能对比

es 对于在处理大查询，可能导致 OOM 问题，集群虽然能够对异常节点有自动恢复机制，但其查询数据量级不满足搬仓系统需求。

2.2 clickhouse 与 TiDB 对比

TiDB 是一个分布式 NewSQL 数据库。它支持水平弹性扩展、ACID 事务、标准 SQL、MySQL 语法和 MySQL 协议，具有数据强一致的高可用特性，是一个不仅适合 OLTP 场景还适 OLAP 场景的混合数据库。

TiDB 的优势：

TiDB 的局限性：

TiDB 更加适合作为 MySql 的替代，其对 MySQL 的兼容可以使得我们的应用切换成本较低，并且 TiDB 提供的数据自动分片无需人工维护。

3 为什么是 clickhouse

我们的项目场景是每天要同步十几亿单表数据，基本业务的查询在百万，还包含复杂的聚合分析。而 Clickhouse 在处理单表海量数据的查询分析方面，是十分优秀的，因此选用 clickhouse。

3.1 clickhouse 读写性能验证

官方公开 benchmark 测试显示能够达到 50MB-200MB/s 的写入吞吐能力，按照每行 100Byte 估算，大约相当于 50W-200W 条 /s 的写入速度。

下面是对 Clickhouse 的读写性能的简单测试，数据量越大差距越明显。

1）JDBC 方式单表、单次写入性能测试（性能更好）：

2）MyBatis 方式单表、单次写入性能测试：

聚合查询性能举例：下图是搬仓系统一个聚合查询，在 clickhouse 中不同数据量级情况下的表现。这个查询在 mysql 中执行，一百万左右的数据量时，耗时已经是分钟级别。

1）count+distinct 方式聚合：

2）group by 方式聚合：

3.2 不足之处

作为分布式系统，通常包含三个重要组成：1、存储引擎。 2、计算引擎。 3、分布式管控层。

在分布式管控层，CK 显得较为薄弱，导致运营、使用成本较高。

这方面，由于我们直接采用京东云实例，可以省很多事情。

计算引擎，CK 在处理多表关联查询、复杂嵌套子查询等场景，需要人工优化，才能做到明显的性能提升；

实时写入，CK 使用场景并不适合比较分散的插入，因为其没有实现内存表（Memory Table）结构，每批次写入直接落盘，单条记录实时写入会导致底层大量的小文件，影响查询性能。

建议单次大批量写入方式、报表库场景降低小文件产生概率。

集群模式下本地表的写入，需要自定义分片规则，否则随机写入会造成数据不均匀。

依赖分布式表的写入，对网络、资源的占用较高。

从数据量增长情况来看，使用场景：

4.1 MergeTree 表引擎

主要用于海量数据分析，支持数据分区、存储有序、主键索引、稀疏索引、数据 TTL 等。MergeTree 支持所有 ClickHouse SQL 语法，但是有些功能与 MySQL 并不一致，比如在 MergeTree 中主键并不用于去重。

先看一个创建表的简单语法：

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]

) ENGINE = ReplacingMergeTree([ver])

[PARTITION BY expr] -- 数据分区规则

[ORDER BY expr] -- 排序键

[SAMPLE BY expr] -- 采样键

[SETTINGS index_granularity = 8192, ...] -- 额外参数

先忽略表结构的定义，先看看相比于 mysql 建表的差异项。（指定集群、分区规则、排序键、采样 0-1 数字）

数据分区：每个分片副本的内部，数据按照 PARTITION BY 列进行分区，分区以目录的方式管理，本文样例中表按照时间进行分区。

基于 MergeTree 表引擎，CK 扩展很多解决特殊场景的表引擎，下面介绍几种常用的。

4.1.1 ReplacingMergeTree 引擎

该引擎和 MergeTree 的不同之处在于它会删除排序键值 (ORDER BY) 相同的重复项。

官方建表语句：

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]

name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],

name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],

) ENGINE = ReplacingMergeTree([ver])

[PARTITION BY expr]

[ORDER BY expr]

[SAMPLE BY expr]

[SETTINGS name=value, ...]

注意：在设置表引擎时，比 MergeTree 多了一个参数：ver - 版本列，ENGINE = ReplacingMergeTree ([ver]) 。

在数据合并的时候，ReplacingMergeTree 从所有具有相同排序键的行中选择一行留下：

ReplacingMergeTree 引擎，在数据写入后，不一定立即进行去重操作，或者不一定去重完毕（官方描述在 10 到 15 分钟内会进行合并）。

由于去重依赖的是排序键，ReplacingMergeTree 引擎是会按照分区键进行分区的，因此相同排序键的数据有可能被分到不同的分区，不同 shard 间可能无法去重。

在图上，分区 1 的文件块，会进行数据合并去重，但是分区 1 与分区 2 之间的数据是不会进行去重的。因此，如果要保证数据最终能够去重，要保证相同排序键的数据，会写入相同分区。

数据验证

下图为 ReplacingMergeTree 引擎，以日期作为分区键，对于重复主键数据的去重测试：

4.1.2 CollapsingMergeTree 引擎

该引擎要求在建表语句中指定一个标记列 Sign，按照 Sign 的值将行分为两类：Sign=1 的行称之为状态行，Sign=-1 的行称之为取消行。每次需要新增状态时，写入一行状态行；需要删除状态时，则写入一行取消行。

使用场景：

  1. 按 clickhouse 的架构，期合并、折叠操作，都是后台独立现场执行的，因此时间上并不能控制，何时折叠完成也无法预知。
  2. 如果插入的状态行与取消行是乱序的，会导致无法正常折叠

4.1.3 VersionedCollapsingMergeTree 表引擎

为了解决 CollapsingMergeTree 乱序写入情况下无法正常折叠问题，VersionedCollapsingMergeTree 表引擎在建表语句中新增了一列 Version，用于在乱序情况下记录状态行与取消行的对应关系。

主键相同，且 Version 相同、Sign 相反的行，在 Compaction 时会被删除。

4.2 数据副本

数据副本放在表引擎这里单独讲一下，是由于只有 MergeTree 系列里的表可支持副本：

<zookeeper>

<node index="1">

<host>example1</host>

<port>2181</port>

</node>

<node index="2">

<host>example2</host>

<port>2181</port>

</node>

<node index="3">

<host>example3</host>

<port>2181</port>

</node>

</zookeeper>

创建数据副本，是通过设置表引擎位置的参数来控制的，语法示例：

CREATE TABLE table_name

EventDate DateTime,

CounterID UInt32,

UserID UInt32

)ENGINE=ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/table_name', '{replica}') -- 这里

PARTITION BY toYYYYMM(EventDate)

ORDER BY (CounterID, EventDate, intHash32(UserID))

SAMPLE BY intHash32(UserID)

定义数据副本，只需要在以上表引擎名字的前面，带上 Replicated 即可。

上方例子中，使用的表引擎为 MergeTree，开启数据副本，关键字 Replicated，参数有 2 个且必填：

示例中的取值，采用了变量 {layer}、{shard}、{replica}，他们的值取得是配置文件中的值，影响的是生成的副本粒度。

<macros>

<layer>05</layer>

<shard>02</shard>

<replica>example05-02-1.yandex.ru</replica>

</macros>

4.3 Special 系列

Special 系列的表引擎，大多是为了特定场景而定制的。

4.3.1 Distributed 引擎

分布式表引擎，本身不存储数据，也不占用存储空间，在定义时需要指定字段，但必须与要映射的表的结构相同。可用于统一查询 * MergeTree 的每个分片，类比 sharding 中的逻辑表。

比如搬仓系统，使用 ReplicatedReplacingMergeTree 与 Distributed 结合，实现通过分布式表实现对本地表的读写（写入操作本地表，读取操作分布式表）。

CREATE TABLE IF NOT EXISTS {distributed_table} as {local_table}

ENGINE = Distributed({cluster}, '{local_database}', '{local_table}', rand())

说明：

注意事项：

4.4 Log 系列

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

几种 Log 表引擎的共性是：

它们彼此之间的区别是：

4.5 Integration 系列

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

5 数据类型

clickhouse 支持的数据类型如下图，分为基础类型、复合类型、特殊类型。

5.1 CK 与 Mysql 数据类型对照

6 SQL 语法 - 常用介绍

6.1 DDL

6.1.1 创建数据库：

CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster];

如果 CREATE 语句中存在 IF NOT EXISTS 关键字，则当数据库已经存在时，该语句不会创建数据库，且不会返回任何错误。

ON CLUSTER 关键字用于指定集群名称，在集群环境下必须指定该参数，否则只会在链接的节点上创建。

6.1.2 创建本地表：

CREATE TABLE [IF NOT EXISTS] [db.]table_name ON CLUSTER cluster

name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],

name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],

INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,

INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2

) ENGINE = engine_name()

[PARTITION BY expr]

[ORDER BY expr]

[PRIMARY KEY expr]

[SAMPLE BY expr]

[SETTINGS name=value, ...];

选项描述：

以下选项与表引擎相关，只有 MergeTree 系列表引擎支持：

示例，创建一个本地表：

CREATE TABLE ontime_local ON CLUSTER default -- 表名为 ontime_local

Year UInt16,

Quarter UInt8,

Month UInt8,

DayofMonth UInt8,

DayOfWeek UInt8,

FlightDate Date,

FlightNum String,

Div5WheelsOff String,

Div5TAIlNum String

)ENGINE = ReplicatedMergeTree(--表引擎用ReplicatedMergeTree，开启数据副本的合并树表引擎）

'/clickhouse/tables/ontime_local/{shard}', -- 指定存储路径

'{replica}')

PARTITION BY toYYYYMM(FlightDate) -- 指定分区键，按FlightDate日期转年+月维度，每月做一个分区

PRIMARY KEY (intHash32(FlightDate)) -- 指定主键，FlightDate日期转hash值

ORDER BY (intHash32(FlightDate),FlightNum) -- 指定排序键，包含两列：FlightDate日期转hash值、FlightNunm字符串。

SAMPLE BY intHash32(FlightDate) -- 抽样表达式，采用FlightDate日期转hash值

SETTINGS index_granularity= 8192 ; -- 指定index_granularity指数，每个分区再次划分的数量

6.1.3 创建分布式表

基于本地表创建一个分布式表。基本语法：

CREATE TABLE [db.]table_name ON CLUSTER default

AS db.local_table_name

ENGINE = Distributed(<cluster>, <database>, <shard table> [, sharding_key])

参数说明：

示例，创建一个分布式表：

CREATE TABLE ontime_distributed ON CLUSTER default -- 指定分布式表的表名，所在集群

AS db_name.ontime_local -- 指定对应的 本地表的表名

ENGINE = Distributed(default, db_name, ontime_local, rand()); -- 指定表引擎为Distributed（固定）

6.1.4 其他建表

clickhouse 还支持创建其他类型的表：

6.1.5 修改表

语法与 mysql 基本一致：

ALTER TABLE [db].name [ON CLUSTER cluster] ADD|DROP|CLEAR|COMMENT|MODIFY COLUMN …

支持下列动作：

举例：ALTER TABLE bd01.table_1 ADD COLUMN browser String AFTER name; – 在 name 列后面追加一列

6.2 DML

注意：

  1. 索引列不支持更新、删除
  2. 分布式表不支持更新、删除

7 复杂查询 JOIN

所有标准 SQL JOIN 支持类型（INNER 和 OUTER 可以省略）：

查询优化：

  1. A join B 的查询，比 from A,B,C 多表的性能高很多
  2. global join 会把书记发送给所有节点参与计算，针对较小的维度表性能较好
  3. JOIN 会在背地节点操作，适合于相同分片字段的两张表关联（A 表与 B 表的分片字段都包含字段 M）
  4. IN 的性能比 JOIN 好，优先使用 JOIN
  5. 先过滤再 join 效率更好（减低每个分片关联数据量级）
  6. 在做多表 join 时，A 表的查询过滤条件中如果能包含与 B 表的 ON expr 中字段过滤条件，性能更好
  7. join 的顺序，大表在左，小表在右；ck 查询时会从右向左执行

对比 JOIN 与 IN 的查询复杂度：

CK 常用的表引擎会是分布式存储，因此查询过程一定是每个分片进行一次查询，这就导致了 sql 的复杂度越高，查询锁扫描的分片数量越多，耗时也就越久。

假设 AB 两个表，分别存储在 10 个分片中，join 则是查询 10 次 A 表的同时，join10 次 B 表，合计要 10*10 次。采用 Global join 则会先查询 10 次并生成临时表，再用临时表取和 B 表 join，合计要 10+10 次。

这算是分布式架构的查询特点，如果能干预数据分片规则，如果查询条件中带有分片列，则可以直接定位到包含数据的分片上，从而减小查询次数。

CK 对于 join 语法上虽然支持，但是性能并不高。当 join 的左边是子查询结果时，ck 是无法进行分布式 join 的。

8 MySQL 迁移到 CK

当然如果花功夫对表结构、SQL、索引等进行优化，能得到更好的查询效率。

官方支持

在 2020 年下半年，Yandex 公司在 ClickHouse 社区发布了 MaterializeMySQL 引擎，支持从 MySQL 全量及增量实时数据同步。MaterializeMySQL 引擎目前支持 MySQL 5.6/5.7/8.0 版本，兼容 Delete/Update 语句，及大部分常用的 DDL 操作。

也就是说，CK 支持作为 MySQL 的从节点存在，依赖订阅 binlog 方式实现。

https://bbs.huaweicloud.com/blogs/238417

9 总结

ClickHouse 更加适合 OLAP 场景，在报表库中有极大性能优势。如果想作为应用数据库，可以灵活采用其表引擎特点，尽量避免数据修改。其实，没有最好的，只有最合适的。

 

作者：京东物流 耿宏宇
来源：京东云开发者社区
声明：本站部分内容来自互联网,如有版权侵犯或其他问题请与我们联系，我们将立即删除或处理。
▍相关推荐
更多资讯 >>>
Copyright @ 简易百科 V 2.0 京ICP备14042104号