表引擎 - 云数据库 ClickHouse

云数据库ClickHouse支持的表引擎分为MergeTree、Log、Integrations和Special四个系列。本文主要对这四类表引擎进行概要介绍，并通过示例介绍常用表引擎的功能。

概述

表引擎即表的类型，在云数据库ClickHouse中决定了如何存储和读取数据、是否支持索引、是否支持主备复制等。云数据库ClickHouse支持的表引擎，请参见下表。

系列	描述	表引擎	特点
MergeTree	MergeTree系列引擎适用于高负载任务，支持大数据量的快速写入并进行后续的数据处理，通用程度高且功能强大。该系列引擎的共同特点是支持数据副本、分区、数据采样等特性。	MergeTree	用于插入极大量的数据到一张表中，数据以数据片段的形式一个接着一个的快速写入，数据片段按照一定的规则进行合并。
		Data Replication	用于将数据从一个节点复制到其他节点，并保证数据的一致性。
		Custom Partitioning Key	用于自定义数据的分区，根据您的需求定义分区键，以将数据分布到不同的分区中。
		ReplacingMergeTree	用于解决MergeTree表引擎相同主键无法去重的问题，可以删除主键值相同的重复项。
		CollapsingMergeTree	在建表语句中新增标记列`Sign`，用于消除ReplacingMergeTree表引擎的如下功能限制。在分布式场景下，相同主键的数据可能被分布到不同节点上，不同分片间可能无法去重。在没有彻底optimize之前，可能无法达到主键去重的效果，比如部分数据已经被去重，而另外一部分数据仍旧有主键重复。 optimize是后台动作，无法预测具体执行时间点。手动执行optimize在海量数据场景下需要消耗大量时间，无法满足业务即时查询的需求。
		VersionedCollapsingMergeTree	在建表语句中新增`Version`列，用于解决CollapsingMergeTree表引擎乱序写入导致无法正常折叠（删除）的问题。
		SummingMergeTree	用于对主键列进行预先聚合，将所有相同主键的行合并为一行，从而大幅度降低存储空间占用，提升聚合计算性能。
		AggregatingMergeTree	预先聚合引擎的一种，用于提升聚合计算的性能，可以指定各种聚合函数。
		GraphiteMergeTree	用于存储Graphite数据并进行汇总，可以减少存储空间，提高Graphite数据的查询效率。
		Approximate Nearest Neighbor Search Indexes	用于近似最近邻搜索的索引引擎，在大规模数据集中高效地查找最接近给定查询点的数据点。
		Full-text Search using Inverted Indexes	使用倒排索引进行全文搜索，用于在大规模文本数据中进行全文搜索和检索。
Log	Log系列引擎适用于快速写入小表（1百万行左右的表）并读取全部数据的场景。该系列引擎的共同特点如下。数据被追加写入磁盘中。不支持`delete`、`update`。不支持索引。不支持原子性写。 `insert`会阻塞`select`操作。	TinyLog	不支持并发读取数据文件，格式简单，查询性能较差，适用于暂存中间数据。
		StripeLog	支持并发读取数据文件，将所有列存储在同一个大文件中，减少了文件数，查询性能比TinyLog好。
		Log	支持并发读取数据文件，每个列会单独存储在一个独立文件中，查询性能比TinyLog好。
Integrations	Integrations系列引擎适用于将外部数据导入到云数据库ClickHouse中，或者在云数据库ClickHouse中直接使用外部数据源。	Kafka	将Kafka Topic中的数据直接导入到云数据库ClickHouse。
		MySQL	将MySQL作为存储引擎，直接在云数据库ClickHouse中对MySQL表进行`select`等操作。
		JDBC	通过指定JDBC连接串读取数据源。
		ODBC	通过指定ODBC连接串读取数据源。
		HDFS	直接读取HDFS上特定格式的数据文件。
Special	Special系列引擎适用于特定的功能场景。	Distributed	本身不存储数据，可以在多个服务器上进行分布式查询。
		MaterializedView	用于创建物化视图。
		Dictionary	将字典数据展示为一个云数据库ClickHouse表。
		Merge	本身不存储数据，可以同时从任意多个其他表中读取数据。
		File	直接将本地文件作为数据存储。
		NULL	写入数据被丢弃，读取数据为空。
		Set	数据总是保存在RAM中。
		Join	数据总是保存在内存中。
		URL	用于管理远程HTTP、HTTPS服务器上的数据。
		View	本身不存储数据，仅存储指定的`SELECT`查询。
		Memory	数据存储在内存中，重启后会导致数据丢失。查询性能极好，适合于对于数据持久性没有要求的1亿以下的小表。在云数据库ClickHouse中，通常用来做临时表。
		Buffer	为目标表设置一个内存Buffer，当Buffer达到了一定条件之后会写入到磁盘。

说明

表引擎的更多信息，具体请参见表引擎介绍。

MergeTree

MergeTree表引擎主要用于海量数据分析，支持数据分区、存储有序、主键索引、稀疏索引和数据TTL等。MergeTree表引擎支持云数据库ClickHouse的所有SQL语法，但是部分功能与标准SQL存在差异。

本文以主键为例进行介绍。云数据库ClickHouse的SQL语法中主键用于去重，保持数据唯一，而在MergeTree表引擎中，其主要作用是加速查询，即便在Compaction完成后，主键相同的数据行也仍旧共同存在。

说明

MergeTree表引擎的更多信息，具体请参见MergeTree。

示例如下。

创建表test_tbl，主键为id和create_time，并且按照主键进行存储排序，按照create_time进行数据分区。

CREATE TABLE test_tbl ON CLUSTER default (
  id UInt16,
  create_time Date,
  comment Nullable(String)
) ENGINE = MergeTree()
   PARTITION BY create_time
     ORDER BY  (id, create_time)
     PRIMARY KEY (id, create_time)
     SETTINGS index_granularity=8192;

写入主键重复的数据。

insert into test_tbl values(1, '2019-12-13', null);
insert into test_tbl values(1, '2019-12-13', null);
insert into test_tbl values(2, '2019-12-14', null);
insert into test_tbl values(3, '2019-12-15', null);
insert into test_tbl values(3, '2019-12-15', null);

查询数据。

select * from test_tbl;

查询结果如下。

┌─id─┬─create_time─┬─comment──┐
│  1 │ 2019-12-13  │   NULL   │
│  1 │ 2019-12-13  │   NULL   │
│  2 │ 2019-12-14  │   NULL   │
│  3 │ 2019-12-15  │   NULL   │
│  3 │ 2019-12-15  │   NULL   │
└────┴─────────────┴──────────┘

由于MergeTree系列表引擎采用类似LSM Tree的结构，很多存储层处理逻辑直到Compaction期间才会发生，因此需执行optimize语句强制后台Compaction。
```
optimize table test_tbl final;
```

再次查询数据。

select * from test_tbl;

查询结果如下，主键重复的数据仍存在。

┌─id─┬─create_time─┬─comment──┐
│  1 │ 2019-12-13  │   NULL   │
│  1 │ 2019-12-13  │   NULL   │
│  2 │ 2019-12-14  │   NULL   │
│  3 │ 2019-12-15  │   NULL   │
│  3 │ 2019-12-15  │   NULL   │
└────┴─────────────┴──────────┘

ReplacingMergeTree

为了解决MergeTree表引擎相同主键无法去重的问题，云数据库ClickHouse提供了ReplacingMergeTree表引擎，用于删除主键值相同的重复项。

虽然ReplacingMergeTree表引擎提供了主键去重的能力，但是仍然存在很多限制，因此ReplacingMergeTree表引擎更多被用于确保数据最终被去重，而无法保证查询过程中主键不重复，主要限制如下。

在分布式场景下，相同主键的数据可能被分布到不同节点上，不同分片间可能无法去重。
在没有彻底optimize之前，可能无法达到主键去重的效果，比如部分数据已经被去重，而另外一部分数据仍旧有主键重复。
optimize是后台动作，无法预测具体执行时间点。
手动执行optimize在海量数据场景下需要消耗大量时间，无法满足业务即时查询的需求。

说明

ReplacingMergeTree表引擎的更多信息，具体请参见ReplacingMergeTree。

示例如下。

创建表test_tbl_replacing。

CREATE TABLE test_tbl_replacing (
  id UInt16,
  create_time Date,
  comment Nullable(String)
) ENGINE = ReplacingMergeTree()
   PARTITION BY create_time
     ORDER BY  (id, create_time)
     PRIMARY KEY (id, create_time)
     SETTINGS index_granularity=8192;

写入主键重复的数据。

insert into test_tbl_replacing values(1, '2019-12-13', null);
insert into test_tbl_replacing values(1, '2019-12-13', null);
insert into test_tbl_replacing values(2, '2019-12-14', null);
insert into test_tbl_replacing values(3, '2019-12-15', null);
insert into test_tbl_replacing values(3, '2019-12-15', null);

查询数据。

select * from test_tbl_replacing;

查询结果如下。

┌─id─┬─create_time─┬─comment──┐
│  1 │ 2019-12-13  │   NULL   │
│  1 │ 2019-12-13  │   NULL   │
│  2 │ 2019-12-14  │   NULL   │
│  3 │ 2019-12-15  │   NULL   │
│  3 │ 2019-12-15  │   NULL   │
└────┴─────────────┴──────────┘

由于MergeTree系列表引擎采用类似LSM Tree的结构，很多存储层处理逻辑直到Compaction期间才会发生，因此需执行optimize语句强制后台Compaction。
```
optimize table test_tbl_replacing final;
```

再次查询数据。

select * from test_tbl_replacing;

查询结果如下，主键重复的数据已消除。

┌─id─┬─create_time─┬─comment──┐
│  1 │ 2019-12-13  │   NULL   │
│  2 │ 2019-12-14  │   NULL   │
│  3 │ 2019-12-15  │   NULL   │
└────┴─────────────┴──────────┘

CollapsingMergeTree

CollapsingMergeTree表引擎用于消除ReplacingMergeTree表引擎的功能限制。该表引擎要求在建表语句中指定一个标记列Sign，按照Sign的值将行分为两类：Sign=1的行称为状态行，用于新增状态。Sign=-1的行称为取消行，用于删除状态。

说明

CollapsingMergeTree表引擎虽然解决了主键相同数据即时删除的问题，但是状态持续变化且多线程并行写入情况下，状态行与取消行位置可能乱序，导致无法正常折叠（删除）。

后台Compaction时会将主键相同、Sign相反的行进行折叠（删除），而尚未进行Compaction的数据，状态行与取消行同时存在。因此为了能够达到主键折叠（删除）的目的，需要业务层进行如下操作。

记录原始状态行的值，或者在执行删除状态操作前先查询数据库获取原始状态行的值。
具体原因：执行删除状态操作时需要写入取消行，而取消行中需要包含与原始状态行主键一样的数据（Sign列除外）。
在进行count()、sum(col)等聚合计算时，可能会存在数据冗余的情况。为了获得正确结果，业务层需要改写SQL，将count()、sum(col)分别改写为sum(Sign)、sum(col * Sign)。
具体原因如下。
- 后台Compaction时机无法预测，在发起查询时，状态行和取消行可能尚未进行折叠（删除）。
- 云数据库ClickHouse无法保证主键相同的行落在同一个节点上，不在同一节点上的数据无法进行折叠（删除）。

说明

CollapsingMergeTree表引擎的更多信息，具体请参见CollapsingMergeTree。

示例如下。

创建表test_tbl_collapsing。

CREATE TABLE test_tbl_collapsing
(
    UserID UInt64,
    PageViews UInt8,
    Duration UInt8,
    Sign Int8
)
ENGINE = CollapsingMergeTree(Sign)
ORDER BY UserID;

插入状态行Sign=1。
```
INSERT INTO test_tbl_collapsing VALUES (4324182021466249494, 5, 146, 1);
```
说明
如果先插入取消行，再插入状态行，可能会导致位置乱序，即使强制后台Compaction，也无法进行主键折叠（删除）。
插入取消行Sign=-1，除Sign列外其他值与插入的状态行一致。同时，插入一行相同主键数据的新状态行。
```
INSERT INTO test_tbl_collapsing VALUES (4324182021466249494, 5, 146, -1), (4324182021466249494, 6, 185, 1);
```

查询数据。

SELECT * FROM test_tbl_collapsing;

查询结果如下。

┌────────UserID───────┬─PageViews─┬─Duration─┬─Sign──┐ 
│ 4324182021466249494 │    5      │    146   │   1   │ 
│ 4324182021466249494 │    5      │    146   │  -1   │ 
│ 4324182021466249494 │    6      │    185   │   1   │ 
└─────────────────────┴───────────┴──────────┴───────┘

如果您需要对指定列进行聚合计算，以sum(col)为例，为了获得正确结果，需改写SQL语句如下。

SELECT
    UserID,
    sum(PageViews * Sign) AS PageViews,
    sum(Duration * Sign) AS Duration
FROM test_tbl_collapsing
GROUP BY UserID
HAVING sum(Sign) > 0;

进行聚合计算后，查询结果如下。

┌────────UserID───────┬─PageViews─┬─Duration──┐ 
│ 4324182021466249494 │    6      │    185    │ 
└─────────────────────┴───────────┴───────────┘

由于MergeTree系列表引擎采用类似LSM Tree的结构，很多存储层处理逻辑直到Compaction期间才会发生，因此需执行optimize语句强制后台Compaction。
```
optimize table test_tbl_collapsing final;
```

再次查询数据。

SELECT * FROM test_tbl_collapsing;

查询结果如下。

┌────────UserID───────┬─PageViews─┬─Duration─┬─Sign──┐  
│ 4324182021466249494 │    6      │    185   │   1   │ 
└─────────────────────┴───────────┴──────────┴───────┘

VersionedCollapsingMergeTree

为了解决CollapsingMergeTree表引擎乱序写入导致无法正常折叠（删除）问题，云数据库ClickHouse提供了VersionedCollapsingMergeTree表引擎，在建表语句中新增一列Version，用于在乱序情况下记录状态行与取消行的对应关系。后台Compaction时会将主键相同、Version相同、Sign相反的行折叠（删除）。

与CollapsingMergeTree表引擎类似，在进行count()、sum(col)等聚合计算时，业务层需要改写SQL，将count()、sum(col)分别改写为sum(Sign)、sum(col * Sign)。

说明

VersionedCollapsingMergeTree表引擎的更多信息，具体请参见VersionedCollapsingMergeTree。

示例如下。

创建表test_tbl_Versioned。

CREATE TABLE test_tbl_Versioned
(
    UserID UInt64,
    PageViews UInt8,
    Duration UInt8,
    Sign Int8,
    Version UInt8
)
ENGINE = VersionedCollapsingMergeTree(Sign, Version)
ORDER BY UserID;

插入取消行Sign=-1。

INSERT INTO test_tbl_Versioned VALUES (4324182021466249494, 5, 146, -1, 1);

插入状态行Sign=1、Version=1，其他列值与插入的取消行一致。同时，插入一行相同主键数据的新状态行。
```
INSERT INTO test_tbl_Versioned VALUES (4324182021466249494, 5, 146, 1, 1),(4324182021466249494, 6, 185, 1, 2);
```

查询数据。

SELECT * FROM test_tbl_Versioned;

查询结果如下。

┌────────UserID───────┬─PageViews─┬─Duration─┬─Sign───┬Version─┐ 
│ 4324182021466249494 │    5      │    146   │  -1    │    1   │
│ 4324182021466249494 │    5      │    146   │   1    │    1   │
│ 4324182021466249494 │    6      │    185   │   1    │    2   │
└─────────────────────┴───────────┴──────────┴────────┴────────┘

如果您需要对指定列进行聚合计算，以sum(col)为例，为了获得正确结果，需改写SQL语句如下。

SELECT
    UserID,
    sum(PageViews * Sign) AS PageViews,
    sum(Duration * Sign) AS Duration
FROM test_tbl_Versioned
GROUP BY UserID
HAVING sum(Sign) > 0;

进行聚合计算后，查询结果如下。

┌────────UserID───────┬─PageViews─┬─Duration─┐ 
│ 4324182021466249494 │    6      │    185   │
└─────────────────────┴───────────┴──────────┘

由于MergeTree系列表引擎采用类似LSM Tree的结构，很多存储层处理逻辑直到Compaction期间才会发生，因此需执行optimize语句强制后台Compaction。
```
optimize table test_tbl_Versioned final;
```

再次查询数据。

SELECT * FROM test_tbl_Versioned;

查询结果如下。

┌────────UserID───────┬─PageViews─┬─Duration─┬─Sign───┬Version─┐ 
│ 4324182021466249494 │    6      │    185   │   1    │    2   │
└─────────────────────┴───────────┴──────────┴────────┴────────┘

SummingMergeTree

SummingMergeTree表引擎用于对主键列进行预先聚合，将所有相同主键的行合并为一行，从而大幅度降低存储空间占用，提升聚合计算性能。

使用SummingMergeTree表引擎时，需要注意如下几点。

云数据库ClickHouse只在后台Compaction时才会对主键列进行预先聚合，而Compaction的执行时机无法预测，所以可能存在部分数据已经被预先聚合、部分数据尚未被聚合的情况。因此，在执行聚合计算时，仍需要使用GROUP BY子句。
在预先聚合时，云数据库ClickHouse会对主键列之外的其他所有列进行预聚合。如果这些列是可聚合的（比如数值类型），则直接sum求和。如果不可聚合（比如String类型），则会随机选择一个值。
通常建议将SummingMergeTree表引擎与MergeTree表引擎组合使用，MergeTree表引擎存储完整的数据，SummingMergeTree表引擎用于存储预先聚合的结果。

说明

SummingMergeTree表引擎的更多信息，具体请参见SummingMergeTree。

示例如下。

创建表test_tbl_summing。

CREATE TABLE test_tbl_summing
(
    key UInt32,
    value UInt32
)
ENGINE = SummingMergeTree()
ORDER BY key;

写入数据。

INSERT INTO test_tbl_summing Values(1,1),(1,2),(2,1);

查询数据。

select * from test_tbl_summing;

查询结果如下。

┌─key─┬value─┐
│  1  │  1   │
│  1  │  2   │
│  2  │  1   │
└─────┴──────┘

由于MergeTree系列表引擎采用类似LSM Tree的结构，很多存储层处理逻辑直到Compaction期间才会发生，因此需执行optimize语句强制后台Compaction。
```
optimize table test_tbl_summing final;
```

强制后台Compaction后，仍旧需要执行GROUP BY子句进行聚合计算，再次查询数据。

SELECT key, sum(value) FROM test_tbl_summing GROUP BY key;

查询结果如下，主键重复的数据已进行了聚合。

┌─key─┬value─┐
│  1  │  3   │
│  2  │  1   │
└─────┴──────┘

AggregatingMergeTree

AggregatingMergeTree表引擎也是预先聚合引擎的一种，用于提升聚合计算的性能。与SummingMergeTree表引擎的区别在于，SummingMergeTree表引擎用于对非主键列进行sum聚合，而AggregatingMergeTree表引擎可以指定各种聚合函数。

AggregatingMergeTree的语法比较复杂，需要结合物化视图或云数据库ClickHouse的特殊数据类型AggregateFunction一起使用。

说明

AggregatingMergeTree表引擎的更多信息，具体请参见AggregatingMergeTree。

示例如下。

结合物化视图使用

创建明细表visits。

CREATE TABLE visits
(
    UserID UInt64,
    CounterID UInt8,
    StartDate Date,
    Sign Int8
)
ENGINE = CollapsingMergeTree(Sign)
ORDER BY UserID;

对明细表visits建立物化视图visits_agg_view，并使用sumState和uniqState函数对明细表进行预先聚合。

CREATE MATERIALIZED VIEW visits_agg_view
ENGINE = AggregatingMergeTree() PARTITION BY toYYYYMM(StartDate) ORDER BY (CounterID, StartDate)
AS SELECT
    CounterID,
    StartDate,
    sumState(Sign)    AS Visits,
    uniqState(UserID) AS Users
FROM visits
GROUP BY CounterID, StartDate;

写入数据至明细表visits中。

INSERT INTO visits VALUES(0, 0, '2019-11-11', 1);
INSERT INTO visits VALUES(1, 1, '2019-11-12', 1);

使用聚合函数sumMerge和uniqMerge对物化视图进行聚合，并查询聚合数据。

SELECT
    StartDate,
    sumMerge(Visits) AS Visits,
    uniqMerge(Users) AS Users
FROM visits_agg_view
GROUP BY StartDate
ORDER BY StartDate

说明

函数sum和uniq不能再使用，否则会出现SQL报错：Illegal type AggregateFunction(sum, Int8) of argument for aggregate function sum...

查询结果如下。

┌──StartDate──┬─Visits─┬─Users──┐
│  2019-11-11 │   1    │   1    │
│  2019-11-12 │   1    │   1    │
└─────────────┴────────┴────────┘

结合特殊数据类型AggregateFunction使用

创建明细表detail_table。

CREATE TABLE detail_table
(   CounterID UInt8,
    StartDate Date,
    UserID UInt64
) ENGINE = MergeTree() 
PARTITION BY toYYYYMM(StartDate) 
ORDER BY (CounterID, StartDate);

写入数据至明细表detail_table中。

INSERT INTO detail_table VALUES(0, '2019-11-11', 1);
INSERT INTO detail_table VALUES(1, '2019-11-12', 1);

创建聚合表agg_table，其中UserID列的类型为AggregateFunction。

CREATE TABLE agg_table
(   CounterID UInt8,
    StartDate Date,
    UserID AggregateFunction(uniq, UInt64)
) ENGINE = AggregatingMergeTree() 
PARTITION BY toYYYYMM(StartDate) 
ORDER BY (CounterID, StartDate);

使用聚合函数uniqState将明细表的数据插入至聚合表中。
```
INSERT INTO agg_table
select CounterID, StartDate, uniqState(UserID)
from detail_table
group by CounterID, StartDate;
```
说明
不能使用INSERT INTO agg_table VALUES(1, '2019-11-12', 1);语句向聚合表插入数据，否则会出现SQL报错：Cannot convert UInt64 to AggregateFunction(uniq, UInt64)...

使用聚合函数uniqMerge对聚合表进行聚合，并查询聚合数据。

SELECT uniqMerge(UserID) AS state 
FROM agg_table 
GROUP BY CounterID, StartDate;

查询结果如下。

┌─state─┐
│   1   │
│   1   │
└───────┘