ClickHouse 技术研究及语法简介 | 京东云技术团队

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

1 基础概念

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

1.1 集群架构

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

• Shard：集群内划分为多个分片或分组（Shard 0 … Shard N），通过 Shard 的线性扩展能力，支持海量数据的分布式存储计算。

• Node： 每个 Shard 内包含一定数量的节点（Node，即进程），同一 Shard 内的节点互为副本，保障数据可靠。ClickHouse 中副本数可按需建设，且逻辑上不同 Shard 内的副本数可不同。

• ZooKeeper Service： 集群所有节点对等，节点间通过 ZooKeeper 服务进行分布式协调。

1.2 数据分区

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

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

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

1.3 列式存储

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

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

列式存储的优点

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

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

查询时的区别：

• 列存储：仅从存储系统中读取必要的列数据（select + where 涉及到的），无用列不读取，速度非常快。

• 行存储：从存储系统读取所有满足条件的行数据，然后在内存中过滤出需要的字段，速度较慢。

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 分析领域有两个典型的方向：

• ROLAP， 通过列存、索引等各类技术手段，提升查询时性能。

• 宽表、大表场景，where 条件过多且动态，mysql 无法每列都建索引。

• MOLAP， 通过预计算提前生成聚合后的结果数据，降低查询读取的数据量，属于计算换性能方式。

• 复杂的报表查询，聚合、筛选很复杂的场景。

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

• 绝大多数都是用于读访问

• 无更新、大批量的更新（大于 1000 行）。（ck 没有高速、低延迟的更新和删除方法）

• 查询的列尽量少，但行数很多。

• 不需要事务、可以避免事务（clickhouse 不支持事务）

• 数据一致性要求较低

• 多表 join 时，只有一个是大表、大表关联小表

• 单表的查询、聚合效率最高，建议数据做宽表处理

2 横向对比

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

2.1 clickhouse 与 ElasticSearch 对比

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

es 的优势：

• 支持实时更新，对 update、delete 操作支持更完整。

• 数据分片更均匀，集群扩展更加方便

es 的局限性：

• 数据量超过千万或者亿级时，若聚合的列数太多，性能也到达瓶颈；

• 不支持深度二次聚合，导致一些复杂的聚合需求，需要人工编写代码在外部实现，这又增加很多开发工作量。

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

• 合并树表引擎系列（MergeTree ），提供了数据分区、一级索引、二级索引。

• 向量引擎（Vector Engine），数据不仅仅按列存储，同时还按向量(列的一部分)进行处理，这样可以更加高效地使用 CPU

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

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

2.2 clickhouse 与 TiDB 对比

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

TiDB 的优势：

• 兼容 Mysql 协议和绝大多数 Mysql 语法，在大多数情况下，用户无需修改一行代码就可以从 Mysql 无缝迁移到 TiDB

• 高可用、强制一致性（Raft）

• 支持 ACID 事务（依赖事务列表），支持二级索引

• 适合快速的点插入，点更新和点删除

TiDB 的局限性：

• 更擅长 OLTP

• 性能依赖硬件和集群规模，单机的读写性能不够出色

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 不能利用其他机器补齐缺失的副本数据，需要用户 ian 补齐节点后，才能自动在副本件进行数据同步。

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

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

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

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

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

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

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

• 如果预估自己的业务数据量不大(日增不到百万行), 那么写分布式表和本地表都可以, 但要注意如果选择写本地表, 请保证每次写入数据都建立新的连接, 且每个连接写入的数据量基本相同，手动保持数据均匀

• 如果预估自己的业务数据量大(日增百万以上, 并发插入大于 10), 那么请写本地表

• 建议每次插入 50W 行左右数据, 最多不可超过 100W 行. 总之 CH 不像 MySQL 要小事务. 比如 1000W 行数据, MySQL 建议一次插入 1W 左右, 使用小事务, 执行 1000 次. CH 建议 20 次,每次 50W. 这是 MergeTree 引擎原理决定的, 频繁少量插入会导致 data part 过多, 合并不过来.

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

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

• Integration 系列：主要用于将外部数据导入到 ClickHouse 中，或者在 ClickHouse 中直接操作外部数据源。

• Special 系列：大多是为了特定场景而定制的。上面提到的 Distributed 就属于该系列。

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 从所有具有相同排序键的行中选择一行留下：

• 如果 ver 列未指定，保留最后一条。

• 如果 ver 列已指定，保留 ver 值最大的版本。

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 系列里的表可支持副本：

• ReplicatedMergeTree

• ReplicatedSummingMergeTree

• ReplicatedReplacingMergeTree

• ReplicatedAggregatingMergeTree

• ReplicatedCollapsingMergeTree

• ReplicatedVersionedCollapsingMergetree

• ReplicatedGraphiteMergeTree

• 副本是表级别的，不是整个服务器级的。所以，服务器里可以同时有复制表和非复制表。

• 副本不依赖分片。每个分片有它自己的独立副本。

• 要使用副本，必须配置文件中设置 ZooKeeper 集群的地址。 （京东云提供的 clickhouse 已经完成了配置，我们直接使用即可）

<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 个且必填：

• zoo_path — ZooKeeper 中该表的路径。

• replica_name — ZooKeeper 中的该表的副本名称

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

<macros>    <layer>05</layer>    <shard>02</shard>    <replica>example05-02-1.yandex.ru</replica></macros>

4.3 Special 系列

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

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

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

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

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

• Distributed：分布式引擎，可以在多个服务器上进行分布式查询

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())

说明：

• distributed_table：分布式表的表名

• local_table：本地表名

• as local_table：保持分布式表与本地表的表结构一致。此处也可以用 （column dataType）这种定义表结构方式代替

• cluster：集群名

注意事项：

• 分布式表本身并不存储数据，只是提供了一个可以分布式访问数据的框架，查询分布式表的时候 clickhouse 会自动去查询对应的每个本地表中的数据，聚合后再返回

• 注意 AS {local_table}，它表明了分布式表所对应的本地表（本地表是存储数据的）

• 可以配置 Distributed 表引擎中的最后一个参数 rand()来设置数据条目的分配方式

• 可以直接往分布式表中写数据，clickhouse 会自动按照上一点所说的方式来分配数据和自平衡，数据实际会写到本地表

• 也可以自己写分片算法，然后往本地表中写数据【网上资料的场景是每天上千亿写入，性能考虑要直接写本地表】

4.4 Log 系列

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

几种 Log 表引擎的共性是：

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

• 不支持 delete、update；

• 不支持 index；

• 不支持原子性写；

• insert 会阻塞 select 操作。

它们彼此之间的区别是：

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

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

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

4.5 Integration 系列

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

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

• MySQL：将 Mysql 作为存储引擎，直接在 ClickHouse 中对 MySQL 表进行 select 等操作；猜测：如果有 join 需求，又不想将 mysql 数据导入 ck 中

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

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

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, ...];

选项描述：

• db：指定数据库名称，如果当前语句没有包含‘db’，则默认使用当前选择的数据库为‘db’。

• cluster：指定集群名称，目前固定为 default。ON CLUSTER 将在每一个节点上都创建一个本地表。

• type：该列数据类型，例如 UInt32。

• DEFAULT：该列缺省值。如果 INSERT 中不包含指定的列，那么将通过表达式计算它的默认值并填充它（与 mysql 一致）。

• MATERIALIZED：物化列表达式，表示该列不能被 INSERT，是被计算出来的； 在 INSERT 语句中，不需要写入该列；在 SELECT * 查询语句结果集不包含该列；需要指定列表来查询（虚拟列）

• ALIAS ：别名列。这样的列不会存储在表中。 它的值不能够通过 INSERT 写入，同时 SELECT 查询使用星号时，这些列也不会被用来替换星号。 但是它们可以用于 SELECT 中，在这种情况下，在查询分析中别名将被替换。

• 物化列与别名列的区别： 物化列是会保存数据，查询的时候不需要计算，而别名列不会保存数据，查询的时候需要计算，查询时候返回表达式的计算结果

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

• PARTITION BY：指定分区键。通常按照日期分区，也可以用其他字段或字段表达式。（定义分区键一定要考虑清楚，它影响数据分布及查询性能）

• ORDER BY：指定 排序键。可以是一组列的元组或任意的表达式。

• PRIMARY KEY： 指定主键，默认情况下主键跟排序键相同。因此，大部分情况下不需要再专门指定一个 PRIMARY KEY 子句。

• SAMPLE BY ：抽样表达式，如果要用抽样表达式，主键中必须包含这个表达式。

• SETTINGS：影响 性能的额外参数。

• GRANULARITY ：索引粒度参数。

示例，创建一个本地表：

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_nameENGINE = Distributed(<cluster>, <database>, <shard table> [, sharding_key])

参数说明：

• db：数据库名。

• local_table_name：对应的已经创建的本地表表名。

• shard table：同上，对应的已经创建的本地表表名。

• sharding_key：分片表达式。可以是一个字段，例如 user_id（integer 类型），通过对余数值进行取余分片；也可以是一个表达式，例如 rand()，通过 rand()函数返回值/shards 总权重分片；为了分片更均匀，可以加上 hash 函数，如 intHash64(user_id)。

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

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 …

支持下列动作：

• ADD COLUMN — 添加列

• DROP COLUMN — 删除列

• CLEAR COLUMN — 重置列的值

• COMMENT COLUMN — 给列增加注释说明

• MODIFY COLUMN — 改变列的值类型，默认表达式以及 TTL

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

6.2 DML

注意：

1. 索引列不支持更新、删除

2. 分布式表不支持更新、删除

7 复杂查询 JOIN

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

• INNER JOIN，只返回匹配的行。

• LEFT OUTER JOIN，除了匹配的行之外，还返回左表中的非匹配行。

• RIGHT OUTER JOIN，除了匹配的行之外，还返回右表中的非匹配行。

• FULL OUTER JOIN，除了匹配的行之外，还会返回两个表中的非匹配行。

• CROSS JOIN，产生整个表的笛卡尔积, “join keys” 是 不 指定。

查询优化：

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

• 数据同步成本：clickhouse 可以做到与 mysql 的表结构一致，进而数据同步成本较低，不需要调整数据结构、不需要额外做宽表处理（当然转为宽表效率更高）。

• SQL 迁移成本：支持 jdbc、mybatis 接入；支持标准 SQL 的语法；支持 join、in、函数，SQL 迁移成本较低。

当然如果花功夫对表结构、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 场景，在报表库中有极大性能优势。如果想作为应用数据库，可以灵活采用其表引擎特点，尽量避免数据修改。其实，没有最好的，只有最合适的。

作者：京东物流 耿宏宇

来源：京东云开发者社区