本文对Clickhouse架构原理、语法、性能特点做一定研究，同时将其与mysql、elasticsearch、tidb做横向对比，并重点分析与mysql的语法差异，为有mysql迁移clickhouse场景需求的技术预研及参考。
Clickhouse是一个用于联机分析（OLAP）的列式数据库管理系统（DBMS)。
ClickHouse 采用典型的分组式的分布式架构，具体集群架构如下图所示：
Clickhouse是分布式系统，其数据表的创建，与mysql是有差异的，可以类比的是在mysql上实现分库分表的方式。
Clichhouse先在每个 Shard 每个节点上创建本地表（即 Shard 的副本），本地表只在对应节点内可见；然后再创建分布式表[Distributed]，映射到前面创建的本地表。
用户在访问分布式表时，ClickHouse 会自动根据集群架构信息，把请求转发给对应的本地表。
相对于关系型数据库（RDBMS），是按行存储的。以mysql中innodb的主键索引为例，构建主键索引的B+树中，每个叶子节点存储的就是一行记录。
而列式数据库，是将一个表，按column的维护进行存储，“单次磁盘I/O拿到的是一列的数据”。
列式存储的优点
在查询时，只会读取涉及到的列，会大大减少IO次数/开销。并且clickhouse在存储时会按指定顺序排列数据，因此只需要按where条件指定列进行顺序扫描、多个列的扫描结果合并，即可找到满足条件的数据。
但由于insert数据时，是按行写入的，因此存储的过程会麻烦一些。
查询时的区别：
每个数据分区内部，所有列的数据是按照 排序键（ORDER BY 列）进行排序的。
可以理解为：对于生成这个分区的原始记录行，先按 排序键 进行排序，然后再按列拆分存储。
每个列的数据文件中，实际是分块存储的，方便数据压缩及查询裁剪，每个块中的记录数不超过 index_granularity，默认 8192，当达到index_granularity的值，数据会分文件。
在支持列存的基础上，ClickHouse 实现了一套面向向量化处理的计算引擎，大量的处理操作都是向量化执行的。
向量化处理的计算引擎：
基于数据存储模型，叠加批量处理模式，利用SIMD指令集，降低函数调用次数，降低硬件开销（比如各级硬件缓存），提升多核CPU利用率。
再加上分布式架构，多机器、多节点、多线程、批量操作数据的指令，最大限度利用硬件资源，提高效率。
注：SIMD指令，单指令多数据流，也就是说在同一个指令周期可以同时处理多个数据。(例如：在一个指令周期内就可以完成多个数据单元的比较).
由于 ClickHouse 采用列存储，相同列的数据连续存储，且底层数据在存储时是经过排序的，这样数据的局部规律性非常强，有利于获得更高的数据压缩比。
同时，超高的压缩比又可以降低存储读取开销、提升系统缓存能力，从而提高查询性能。
前面提到的列式存储，用于裁剪不必要的字段读取；
而索引，则用于裁剪不必要的记录读取（减少未命中数据的IO)。
简单解释：
以主键索引为例，Clickhouse存储数据时，会按排序键（ORDER BY)指定的列进行排序，并按Index_granularity参数切分成块，然后会抽取每个数据块的首行，组织为一份稀疏的排序索引。
类比B+树的查找过程，如果where条件中包含主键列，就可以通过稀疏索引快速的过滤。稀疏索引对于范围查找比较高效。
二级索引，则是采用bloom filter来实现的：minmax，set，ngrambf/tokenbf。
OLAP 分析领域有两个典型的方向：
既然是OLAP分析，对数据的使用有些基本要求：
搬仓系统面临的是从十几亿数据中进行查询、聚合分析，从世面上可选的支持海量数据读写的中间件中搜集到，能够有支持类似场景、有比较轻量级的产品大概有Clickhouse、ElasticSearch、TiDB。
elastic生态很丰富，es作为其中的存储产品，从首个版本算起，已经有10年发展历史，主要解决的是搜索问题。es的底层存储采用lucene，主要包含行存储、列存储和倒排索引，利用分片与副本机制，解决了集群下搜索性能与高可用的问题。
es的优势：
es的局限性：
ClickHouse 与 Elasticsearch（排序与聚合查询） 一样，都采用列式存储结构，都支持副本分片，不同的是 ClickHouse 底层有一些独特的实现，如下：
网上资料：聚合查询的性能对比
es对于在处理大查询，可能导致OOM问题，集群虽然能够对异常节点有自动恢复机制，但其查询数据量级不满足搬仓系统需求。
TiDB 是一个分布式 NewSQL 数据库。它支持水平弹性扩展、ACID 事务、标准 SQL、MySQL 语法和 MySQL 协议,具有数据强一致的高可用特性,是一个不仅适合OLTP场景还适OLAP场景的混合数据库。
TiDB的优势：
TiDB的局限性：
TiDB更加适合作为MySql的替代，其对MySQL的兼容可以使得我们的应用切换成本较低，并且TiDB提供的数据自动分片无需人工维护。
我们的项目场景是每天要同步十几亿单表数据，基本业务的查询在百万，还包含复杂的聚合分析。而Clickhouse在处理单表海量数据的查询分析方面，是十分优秀的，因此选用clickhouse。
官方公开benchmark测试显示能够达到50MB-200MB/s的写入吞吐能力，按照每行100Byte估算，大约相当于50W-200W条/s的写入速度。
下面是对Clickhouse的读写性能的简单测试，数据量越大差距越明显。
1）JDBC方式单表、单次写入性能测试（性能更好）：
2）Mybatis方式单表、单次写入性能测试：
聚合查询性能举例：下图是搬仓系统一个聚合查询，在clickhouse中不同数据量级情况下的表现。这个查询在mysql中执行，一百万左右的数据量时，耗时已经是分钟级别。
1）count+distinct方式聚合：
2）group by方式聚合：
作为分布式系统，通常包含三个重要组成：1、存储引擎。 2、计算引擎。 3、分布式管控层。
在分布式管控层，CK显得较为薄弱，导致运营、使用成本较高。
这方面，由于我们直接采用京东云实例，可以省很多事情。
计算引擎，CK在处理多表关联查询、复杂嵌套子查询等场景，需要人工优化，才能做到明显的性能提升；
实时写入，CK使用场景并不适合比较分散的插入，因为其没有实现内存表（Memory Table）结构，每批次写入直接落盘，单条记录实时写入会导致底层大量的小文件，影响查询性能。
建议单次大批量写入方式、报表库场景降低小文件产生概率。
集群模式下本地表的写入，需要自定义分片规则，否则随机写入会造成数据不均匀。
依赖分布式表的写入，对网络、资源的占用较高。
从数据量增长情况来看，使用场景：
主要用于海量数据分析，支持数据分区、存储有序、主键索引、稀疏索引、数据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扩展很多解决特殊场景的表引擎，下面介绍几种常用的。
该引擎和 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引擎，以日期作为分区键，对于重复主键数据的去重测试：
该引擎要求在建表语句中指定一个标记列Sign，按照Sign的值将行分为两类：Sign=1的行称之为状态行，Sign=-1的行称之为取消行。每次需要新增状态时，写入一行状态行；需要删除状态时，则写入一行取消行。
使用场景：
为了解决CollapsingMergeTree乱序写入情况下无法正常折叠问题，VersionedCollapsingMergeTree表引擎在建表语句中新增了一列Version，用于在乱序情况下记录状态行与取消行的对应关系。
主键相同，且Version相同、Sign相反的行，在Compaction时会被删除。
数据副本放在表引擎这里单独讲一下，是由于只有 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>
Special系列的表引擎，大多是为了特定场景而定制的。
分布式表引擎，本身不存储数据，也不占用存储空间，在定义时需要指定字段，但必须与要映射的表的结构相同。可用于统一查询*MergeTree的每个分片，类比sharding中的逻辑表。
比如搬仓系统，使用ReplicatedReplacingMergeTree与Distributed结合，实现通过分布式表实现对本地表的读写（写入操作本地表，读取操作分布式表）。
CREATE TABLE IF NOT EXISTS {distributed_table} as {local_table}
ENGINE = Distributed({cluster}, '{local_database}', '{local_table}', rand())
说明：
注意事项：
Log系列表引擎功能相对简单，主要用于快速写入小表（1百万行左右的表），然后全部读出的场景。
几种Log表引擎的共性是：
它们彼此之间的区别是：
该系统表引擎主要用于将外部数据导入到ClickHouse中，或者在ClickHouse中直接操作外部数据源。
clickhouse支持的数据类型如下图，分为基础类型、复合类型、特殊类型。
CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster];
如果CREATE 语句中存在IF NOT EXISTS 关键字，则当数据库已经存在时，该语句不会创建数据库，且不会返回任何错误。
ON CLUSTER 关键字用于指定集群名称，在集群环境下必须指定该参数，否则只会在链接的节点上创建。
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指数，每个分区再次划分的数量
基于本地表创建一个分布式表。基本语法：
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（固定）
clickhouse还支持创建其他类型的表：
语法与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列后面追加一列
注意：
所有标准 SQL JOIN 支持类型（INNER和OUTER可以省略）：
查询优化：
对比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的。
当然如果花功夫对表结构、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
ClickHouse更加适合OLAP场景，在报表库中有极大性能优势。如果想作为应用数据库，可以灵活采用其表引擎特点，尽量避免数据修改。其实，没有最好的，只有最合适的。
作者：京东物流 耿宏宇
来源：京东云开发者社区
为了保证统计数据的准确性，比如订单金额，一个常用的方法是在查询时增加final关键字。那final关键字是如何合并数据的，以及合并的数据范围是怎样的，本文就对此做一个简单的探索。