ClickHouse表引擎介绍
背景介绍
表引擎在ClickHouse中的作用十分关键，不同的表引擎决定了：
- 数据存储和读取的位置
- 支持哪些查询方式
- 能否并发式访问数据
- 能不能使用索引
- 是否可以执行多线程请求
- 数据复制使用的参数
其中MergeTree和Distributed是ClickHouse表引擎中最重要，也是最常使用的两个引擎，本文将重点进行介绍。
其他表引擎详细可以参考官网链接：https://clickhouse.tech/docs/en/engines/table-engines。
MergeTree系列引擎
MergeTree用于高负载任务的最通用和功能最强大的表引擎，其主要有以下关键特征：
- 基于分区键（partitioning key）的数据分区分块存储
- 数据索引排序（基于primary key和order by）
- 支持数据复制（带Replicated前缀的表引擎）
- 支持数据抽样
在写入数据时，该系列引擎表会按照分区键将数据分成不同的文件夹，文件夹内每列数据为不同的独立文件，以及创建数据的序列化索引排序记录文件。该结构使得数据读取时能够减少数据检索时的数据量，极大的提高查询效率。
- MergeTree
建表语法：
CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster] ( name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1], name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2], ... INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1, INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2 ) ENGINE = MergeTree() ORDER BY expr [PARTITION BY expr] [PRIMARY KEY expr] [SAMPLE BY expr] [TTL expr [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'], ...] [SETTINGS name=value, ...]使用示例：
CREATE TABLE default.test ( name1 DateTime, name2 String, name3 String, name4 String, name5 Date, ... ) ENGINE = MergeTree() PARTITION BY toYYYYMM(name5) ORDER BY (name1, name2) SETTINGS index_granularity = 8192
示例参数说明如下：- ENGINE = MergeTree()：MergeTree表引擎。
- PARTITION BY toYYYYMM(name4)：分区，示例数据将以月份为分区，每个月份一个文件夹。
- ORDER BY：排序字段，支持多字段的索引排序，第一个相同的时候按照第二个排序依次类推。
- index_granularity = 8192：排序索引的颗粒度，每8192条数据记录一个排序索引值。
如果被查询的数据存在于分区或排序字段中，能极大降低数据查找时间。
- ReplacingMergeTree
该引擎和MergeTree的不同之处在于它会删除排序键值相同的重复项。ReplacingMergeTree适合于清除重复数据节省存储空间，但是它不保证重复数据不出现，一般不建议使用。
建表语法：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, ...] - SummingMergeTree
当合并SummingMergeTree表的数据片段时，ClickHouse会把所有具有相同主键的行合并为一行，该行包含了被合并的行中具有数值数据类型的列的汇总值。如果主键的组合方式使得单个键值对应于大量的行，则可以显著的减少存储空间并加快数据查询的速度。
建表语法：
CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster] ( name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1], name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2], ... ) ENGINE = SummingMergeTree([columns]) [PARTITION BY expr] [ORDER BY expr] [SAMPLE BY expr] [SETTINGS name=value, ...]使用示例：
创建一个SummingMergeTree表testTable：
CREATE TABLE testTable ( id UInt32, value UInt32 ) ENGINE = SummingMergeTree() ORDER BY id插入表数据：
INSERT INTO testTable Values(5,9),(5,3),(4,6),(1,2),(2,5),(1,4),(3,8); INSERT INTO testTable Values(88,5),(5,5),(3,7),(3,5),(1,6),(2,6),(4,7),(4,6),(43,5),(5,9),(3,6);
在未合并parts查询所有数据：
SELECT * FROM testTable ┌─id─┬─value─┐ │ 1 │ 6 │ │ 2 │ 5 │ │ 3 │ 8 │ │ 4 │ 6 │ │ 5 │ 12 │ └───┴──── ┘ ┌─id─┬─value─┐ │ 1 │ 6 │ │ 2 │ 6 │ │ 3 │ 18 │ │ 4 │ 13 │ │ 5 │ 14 │ │ 43 │ 5 │ │ 88 │ 5 │ └───┴──── ┘
ClickHouse还没有汇总所有行，如果需要通过ID进行汇总聚合，需要用到sum和GROUP BY子句：
SELECT id, sum(value) FROM testTable GROUP BY id ┌─id─┬─sum(value)─┐ │ 4 │ 19 │ │ 3 │ 26 │ │ 88 │ 5 │ │ 2 │ 11 │ │ 5 │ 26 │ │ 1 │ 12 │ │ 43 │ 5 │ └───┴───────┘
手工执行合并操作：
OPTIMIZE TABLE testTable
此时再查询testTable表数据：
SELECT * FROM testTable ┌─id─┬─value─┐ │ 1 │ 12 │ │ 2 │ 11 │ │ 3 │ 26 │ │ 4 │ 19 │ │ 5 │ 26 │ │ 43 │ 5 │ │ 88 │ 5 │ └───┴──── ┘
SummingMergeTree根据ORDER BY排序键作为聚合数据的条件Key。即如果排序key是相同的，则会合并成一条数据，并对指定的合并字段进行聚合。
后台执行合并操作时才会进行数据的预先聚合，而合并操作的执行时机无法预测，所以可能存在部分数据已经被预先聚合、部分数据尚未被聚合的情况。因此，在执行聚合计算时，SQL中仍需要使用GROUP BY子句。
- AggregatingMergeTree
AggregatingMergeTree是预先聚合引擎的一种，用于提升聚合计算的性能。AggregatingMergeTree引擎能够在合并分区时，按照预先定义的条件聚合数据，同时根据预先定义的聚合函数计算数据并通过二进制的格式存入表内。
建表语法：
CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster] ( name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1], name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2], ... ) ENGINE = AggregatingMergeTree() [PARTITION BY expr] [ORDER BY expr] [SAMPLE BY expr] [TTL expr] [SETTINGS name=value, ...]使用示例：
AggregatingMergeTree无单独参数设置，在分区合并时，在每个数据分区内，会按照ORDER BY聚合，使用何种聚合函数，对哪些列字段计算，则是通过定义AggregateFunction函数类型实现，例如：
create table test_table ( name1 String, name2 String, name3 AggregateFunction(uniq,String), name4 AggregateFunction(sum,Int), name5 DateTime ) ENGINE = AggregatingMergeTree() PARTITION BY toYYYYMM(name5) ORDER BY (name1,name2) PRIMARY KEY name1;
AggregateFunction类型的数据在写入和查询时需要分别调用*state、*merge函数，*表示定义字段类型时使用的聚合函数。如上示例表test_table定义的name3、name4字段分别使用了uniq、sum函数，那么在写入数据时需要调用uniqState、sumState函数，并使用INSERT SELECT语法。
insert into test_table select '8','test1',uniqState('name1'),sumState(toInt32(100)),'2021-04-30 17:18:00'; insert into test_table select '8','test1',uniqState('name1'),sumState(toInt32(200)),'2021-04-30 17:18:00';在查询数据时也需要调用对应的函数uniqMerge、sumMerge：
select name1,name2,uniqMerge(name3),sumMerge(name4) from test_table group by name1,name2; ┌─name1─┬─name2─┬─uniqMerge(name3)─┬─sumMerge(name4)─┐ │ 8 │ test1 │ 1 │ 300 │ └──── ┴──── ┴──────────┴───────── ┘
AggregatingMergeTree更常用的方式是结合物化视图使用，物化视图即其它数据表上层的一种查询视图。详细可以参考：https://clickhouse.tech/docs/en/engines/table-engines/mergetree-family/aggregatingmergetree/
- CollapsingMergeTree
CollapsingMergeTree它通过定义一个sign标记位字段记录数据行的状态。如果sign标记为1，则表示这是一行有效的数据；如果sign标记为-1，则表示这行数据需要被删除。
建表语法：CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster] ( name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1], name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2], ... ) ENGINE = CollapsingMergeTree(sign) [PARTITION BY expr] [ORDER BY expr] [SAMPLE BY expr] [SETTINGS name=value, ...]使用示例：
具体的使用示例可以参考：https://clickhouse.tech/docs/en/engines/table-engines/mergetree-family/collapsingmergetree/。
- VersionedCollapsingMergeTree
VersionedCollapsingMergeTree表引擎在建表语句中新增了一列version，用于在乱序情况下记录状态行与取消行的对应关系。主键相同，且Version相同、Sign相反的行，在Compaction时会被删除。
建表语法：
CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster] ( name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1], name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2], ... ) ENGINE = VersionedCollapsingMergeTree(sign, version) [PARTITION BY expr] [ORDER BY expr] [SAMPLE BY expr] [SETTINGS name=value, ...]使用示例：
具体的使用示例可以参考：https://clickhouse.tech/docs/en/engines/table-engines/mergetree-family/versionedcollapsingmergetree/。
- GraphiteMergeTree
GraphiteMergeTree引擎用来存储时序数据库Graphite的数据。
建表语法：
CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster] ( Path String, Time DateTime, Value <Numeric_type>, Version <Numeric_type> ... ) ENGINE = GraphiteMergeTree(config_section) [PARTITION BY expr] [ORDER BY expr] [SAMPLE BY expr] [SETTINGS name=value, ...]使用示例：
具体的使用示例可以参考：https://clickhouse.tech/docs/en/engines/table-engines/mergetree-family/graphitemergetree/。
Replicated*MergeTree引擎
ClickHouse中的所有MergeTree家族引擎前面加上Replicated就成了支持副本的合并树引擎。
Replicated系列引擎借助ZooKeeper实现数据的同步，创建Replicated复制表时通过注册到ZooKeeper上的信息实现同一个分片的所有副本数据进行同步。
ENGINE = Replicated*MergeTree('ZooKeeper存储路径','副本名称', ...)
Replicated表引擎需指定两个参数：
- ZooKeeper存储路径：ZooKeeper中该表相关数据的存储路径，建议规范化，如：/clickhouse/tables/{shard}/数据库名/表名。
- 副本名称，一般用{replica}即可。
Replicated表引擎使用示例可以参考：ClickHouse表创建。
Distributed表引擎
Distributed表引擎的创建模板：
ENGINE = Distributed(cluster_name, database_name, table_name, [sharding_key])
Distributed表参数解析如下：
- cluster_name：集群名称，在对分布式表执行读写的过程中，使用集群的配置信息查找对应的ClickHouse实例节点。
- database_name：数据库名称。
- table_name：数据库下对应的本地表名称，用于将分布式表映射到本地表上。
- sharding_key：分片键（可选参数），分布式表会按照这个规则，将数据分发到各个本地表中。
Distributed表引擎使用示例：
--先创建一个表名为test的ReplicatedMergeTree本地表
CREATE TABLE default.test ON CLUSTER default_cluster_1
(
`EventDate` DateTime,
`id` UInt64
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/default/test', '{replica}')
PARTITION BY toYYYYMM(EventDate)
ORDER BY id
--基于本地表test创建表名为test_all的Distributed表
CREATE TABLE default.test_all ON CLUSTER default_cluster_1
(
`EventDate` DateTime,
`id` UInt64
)
ENGINE = Distributed(default_cluster_1, default, test, rand())
分布式表创建规则：
- 创建Distributed表时需加上on cluster cluster_name，这样建表语句在某一个ClickHouse实例上执行一次即可分发到集群中所有实例上执行。
- 分布式表通常以本地表加“_all”命名。它与本地表形成一对多的映射关系，之后可以通过分布式表代理操作多张本地表。
- 分布式表的表结构尽量和本地表的结构一致。如果不一致，在建表时不会报错，但在查询或者插入时可能会抛出异常。
