# ClickHouse存算一体表引擎概述
#### 背景介绍
表引擎在ClickHouse中的作用十分关键，不同的表引擎决定了：
- 数据存储和读取的位置。
- 支持哪些查询方式。
- 能否并发式访问数据。
- 能否使用索引。
- 是否可以执行多线程请求。
- 数据复制使用的参数。
其中MergeTree和Distributed是ClickHouse表引擎中最重要，也是最常使用的两个引擎，本文将重点进行介绍。
#### 概述
表引擎即表的类型，在云数据库ClickHouse中决定了如何存储和读取数据、是否支持索引、是否支持主备复制等。云数据库ClickHouse支持的表引擎，请参见下表。
![](https://support.huaweicloud.com/usermanual-cloudtable/public_sys-resources/note_3.0-zh-cn.png)
MergeTree引擎为单副本，无法保证高可用和数据可靠性，建议只在测试环境中使用。Replicated\*MergeTree引擎用于生产环境。
表1表引擎 
| 系列                    | 描述                                                                                                                                                                                                                                                                                                                                                                                                                                               | 表引擎                          | 特点                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|:---|:---|:---|:---|
| MergeTree             | - MergeTree系列引擎适用于高负载任务，支持大数据量的快速写入并进行后续的数据处理，通用程度高且功能强大。  - 该系列引擎的共同特点是支持数据副本、分区、数据采样等特性。   | MergeTree                    | - 基于分区键（partitioning key）的数据分区分块存储。  - 数据索引排序（基于primary key和order by）。  - 支持数据复制（带Replicated前缀的表引擎）。  - 支持数据抽样。   在写入数据时，该系列引擎表会按照分区键将数据分成不同的文件夹，文件夹内每列数据为不同的独立文件，以及创建数据的序列化索引排序记录文件。该结构使得数据读取时能够减少数据检索时的数据量，极大的提高查询效率。 |
| MergeTree             | - MergeTree系列引擎适用于高负载任务，支持大数据量的快速写入并进行后续的数据处理，通用程度高且功能强大。  - 该系列引擎的共同特点是支持数据副本、分区、数据采样等特性。   | RelacingMergeTree            | 用于解决MergeTree表引擎相同主键无法去重的问题，可以删除主键值相同的重复项。                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
| MergeTree             | - MergeTree系列引擎适用于高负载任务，支持大数据量的快速写入并进行后续的数据处理，通用程度高且功能强大。  - 该系列引擎的共同特点是支持数据副本、分区、数据采样等特性。   | CollapsingMergeTree          | CollapsingMergeTree它通过定义一个sign标记位字段记录数据行的状态。如果sign标记为1，则表示这是一行有效的数据。如果sign标记为-1，则表示这行数据需要被删除。                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
| MergeTree             | - MergeTree系列引擎适用于高负载任务，支持大数据量的快速写入并进行后续的数据处理，通用程度高且功能强大。  - 该系列引擎的共同特点是支持数据副本、分区、数据采样等特性。   | VersionedCollapsingMergeTree | 在建表语句中新增Version列，用于解决CollapsingMergeTree表引擎乱序写入导致无法正常折叠（删除）的问题。                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
| MergeTree             | - MergeTree系列引擎适用于高负载任务，支持大数据量的快速写入并进行后续的数据处理，通用程度高且功能强大。  - 该系列引擎的共同特点是支持数据副本、分区、数据采样等特性。   | SummigMergeTree              | 用于对主键列进行预先聚合，将所有相同主键的行合并为一行，从而大幅度降低存储空间占用，提升聚合计算性能。                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
| MergeTree             | - MergeTree系列引擎适用于高负载任务，支持大数据量的快速写入并进行后续的数据处理，通用程度高且功能强大。  - 该系列引擎的共同特点是支持数据副本、分区、数据采样等特性。   | AggregatingMergeTree         | AggregatingMergeTree是预先聚合引擎的一种，用于提升聚合计算的性能。AggregatingMergeTree引擎能够在合并分区时，按照预先定义的条件聚合数据，同时根据预先定义的聚合函数计算数据并通过二进制的格式存入表内。                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
| MergeTree             | - MergeTree系列引擎适用于高负载任务，支持大数据量的快速写入并进行后续的数据处理，通用程度高且功能强大。  - 该系列引擎的共同特点是支持数据副本、分区、数据采样等特性。   | GraphiteMergeTree            | 用于存储Graphite数据并进行汇总，可以减少存储空间，提高Graphite数据的查询效率。                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
| Replicated\*MergeTree | ClickHouse中的所有MergeTree家族引擎前面加上Replicated就成了支持副本的合并树引擎。                                                                                                                                                                                                                                                                                                                                                                                          | Replicated\*MergeTree系列      | Replicated系列引擎借助ZooKeeper实现数据的同步，创建Replicated复制表时通过注册到ZooKeeper上的信息实现同一个分片的所有副本数据进行同步。                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
| Distributed           | -                                                                                                                                                                                                                                                                                                                                                                                                                                                | Distributed                  | 本身不存储数据，可以在多个服务器上进行分布式查询。                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
   
#### MergeTree
- 建表语法。
  ```
  CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER ClickHouse集群名]
  (
      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;
  ```
  示例参数说明：
  表2参数说明 
  | 参数                           | 说明                                   |
  |:---|:---|
  | ENGINE = MergeTree()         | MergeTree表引擎。                        |
  | PARTITION BY toYYYYMM(name5) | 分区，示例数据将以月份为分区，每个月份一个文件夹。            |
  | ORDER BY                     | 排序字段，支持多字段的索引排序，第一个相同的时候按照第二个排序依次类推。 |
  | index_granularity = 8192     | 排序索引的颗粒度，每8192条数据记录一个排序索引值。          |
     
  ![](https://support.huaweicloud.com/usermanual-cloudtable/public_sys-resources/note_3.0-zh-cn.png)
  如果被查询的数据存在于分区或排序字段中，能极大降低数据查找时间。
  
 
#### ReplacingMergeTree
为了解决MergeTree表引擎相同主键无法去重的问题，云数据库ClickHouse提供了ReplacingMergeTree表引擎，用于删除主键值相同的重复项。
- 建表语句。
  ```
  CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER ClickHouse集群名]
  (
      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, ...]
  ```
  
 
#### CollapsingMergeTree
CollapsingMergeTree表引擎用于消除ReplacingMergeTree表引擎的功能限制。该表引擎要求在建表语句中指定一个标记列Sign，按照Sign的值将行分为两类：Sign=1的行称为状态行，用于新增状态。Sign=-1的行称为取消行，用于删除状态。
- 建表语句。
  ```
  CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER ClickHouse集群名]
  (
      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, ...]
  ```
  
 
- 使用示例。
  - 示例数据。 例如：我们要计算用户在某个网站上访问了多少页面以及他们在那里的时间。 在某个时间点，我们用用户活动的状态写下面的行。
    表3示例数据 
    | UserID              | PageViews | Duration | Sign |
    |:---|:---|:---|:---|
    | 4324182021466249494 | 5         | 146      | 1    |
    | 4324182021466249494 | 5         | 146      | -1   |
    | 4324182021466249494 | 6         | 185      | 1    |
       
    - sign--- 指定行类型的列名: 1 是一个 "state" 行, -1 是一个"cancel"行。
     
  
  - 建表Test。
    ```
    CREATE TABLE Test(UserID UInt64,PageViews UInt8,Duration UInt8,Sign Int8)ENGINE = CollapsingMergeTree(Sign) ORDER BY UserID;
    ```
    
  
  - 插入数据。
    - 第一次插入数据。
      ```
      INSERT INTO Test VALUES (4324182021466249494, 5, 146, 1);
      ```
      
    
    - 第二次插入数据。
      ```
      INSERT INTO Test VALUES (4324182021466249494, 5, 146, -1),(4324182021466249494, 6, 185, 1);
      ```
      
     
  
  - 查看数据。
    ```
    SELECT * FROM Test;
    ```
    查询结果。
    ```
    ┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
    │ 4324182021466249494 │         5 │      146 │   -1 │
    │ 4324182021466249494 │         6 │      185 │    1 │
    └─────────────────────┴───────────┴──────────┴──────┘
    ┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
    │ 4324182021466249494 │         5 │      146 │    1 │
    └─────────────────────┴───────────┴──────────┴──────┘
    ```
    
  
  - 对指定列进行数据聚合。
    ```
    SELECT UserID,sum(PageViews * Sign) AS PageViews,sum(Duration * Sign) AS Duration FROM Test GROUP BY UserID HAVING sum(Sign) > 0;
    ```
    查询结果如下所示。
    ```
    ┌──────────────UserID─┬─PageViews─┬─Duration─┐
    │ 4324182021466249494 │         6 │      185 │
    └─────────────────────┴───────────┴──────────┘
    ```
    
  
  - 强制折叠数据，用以下SQL命令。
    ```
    SELECT * FROM Test FINAL;
    ```
    查询结果如下所示。
    ```
    ┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
    │ 4324182021466249494 │         6 │      185 │    1 │
    └─────────────────────┴───────────┴──────────┴──────┘
    ```
    
   
#### VersionedCollapsingMergeTree
为了解决CollapsingMergeTree表引擎乱序写入导致无法正常折叠（删除）问题，云数据库ClickHouse提供了VersionedCollapsingMergeTree表引擎，在建表语句中新增一列Version，用于在乱序情况下记录状态行与取消行的对应关系。后台Compaction时会将主键相同、Version相同、Sign相反的行折叠（删除）。
- 建表语句。
  ```
  CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER ClickHouse集群名]
  (
      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, ...]
  ```
  
 
- 使用示例。
  - 示例数据。
    例如：我们要计算用户在某个网站上访问了多少页面以及他们在那里的时间。 在某个时间点，我们用用户活动的状态写下面的行。
    表4示例数据 
    | UserID              | PageViews | Duration | Sign | Version |
    |:---|:---|:---|:---|:---|
    | 4324182021466249494 | 5         | 146      | 1    | 1       |
    | 4324182021466249494 | 5         | 146      | -1   | 1       |
    | 4324182021466249494 | 6         | 185      | 1    | 2       |
       
    - sign--- 指定行类型的列名: 1 是一个 "state" 行, -1 是一个"cancel"行。
    
    - version--- 指定对象状态版本的列名。
     
  
  - 创建表T。
    ```
    CREATE TABLE T(UserID UInt64,PageViews UInt8,Duration UInt8,Sign Int8,Version UInt8)ENGINE = VersionedCollapsingMergeTree(Sign, Version)ORDER BY UserID;
    ```
    
  
  - 插入两部分不同的数据。
    ```
    INSERT INTO T VALUES (4324182021466249494, 5, 146, 1, 1);
    ```
    ```
    INSERT INTO T VALUES (4324182021466249494, 5, 146, -1, 1),(4324182021466249494, 6, 185, 1, 2);
    ```
    
  
  - 查看数据。
    ```
    SELECT * FROM T;
    ```
    
  
  - 对指定列进行数据聚合。
    ```
    SELECT UserID, sum(PageViews * Sign) AS PageViews,sum(Duration * Sign) AS Duration,Version FROM T GROUP BY UserID, Version HAVING sum(Sign) > 0;
    ```
    查询显示结果如下。
    ```
    ┌──────────────UserID─┬─PageViews─┬─Duration─┬─Version─┐
    │ 4324182021466249494 │         6 │      185 │       2 │
    └─────────────────────┴───────────┴──────────┴─────────┘
    ```
    
  
  - 强制折叠数据，用以下SQL命令。
    ```
    SELECT * FROM T FINAL;
    ```
    查询显示结果如下。
    ```
    ┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┬─Version─┐
    │ 4324182021466249494 │         6 │      185 │    1 │       2 │
    └─────────────────────┴───────────┴──────────┴──────┴─────────┘
    ```
    
   
#### SummingMergeTree
SummingMergeTree表引擎用于对主键列进行预先聚合，将所有相同主键的行合并为一行，从而大幅度降低存储空间占用，提升聚合计算性能。
- 建表语句。
  ```
  CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER ClickHouse集群名]
  (
      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;
    ```
    
  
  - testTable表中插入数据。
    ```
    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;
    ```
    查询表数据。
    ```
    SELECT * FROM testTable;
    ```
    查询结果。
    ```
    ┌─id─┬─value─┐
    │  1 │    12 │
    │  2 │    11 │
    │  3 │    26 │
    │  4 │    19 │
    │  5 │    26 │
    │ 43 │     5 │
    │ 88 │     5 │
    └────┴───────┘
    ```
    ![](https://support.huaweicloud.com/usermanual-cloudtable/public_sys-resources/note_3.0-zh-cn.png)
    - SummingMergeTree根据ORDER BY排序键作为聚合数据的条件Key。即如果排序key是相同的，则会合并成一条数据，并对指定的合并字段进行聚合。
    
    - 后台执行合并操作时才会进行数据的预先聚合，而合并操作的执行时机无法预测，所以可能存在部分数据已经被预先聚合、部分数据尚未被聚合的情况。因此，在执行聚合计算时，SQL中仍需要使用GROUP BY子句。
      
   
 
#### AggregatingMergeTree
AggregatingMergeTree表引擎也是预先聚合引擎的一种，用于提升聚合计算的性能。
- 建表语句。
  ```
  CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER ClickHouse集群名]
  (
      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';
    ```
    
  
  
  
  - 查询数据。
    ```
    select name1,name2,uniqMerge(name3),sumMerge(name4) from test_table group by name1,name2;
    ```
    查询结果。
    ```
    ┌─name1─┬─name2─┬─uniqMerge(name3)─┬─sumMerge(name4)─┐
    │ 8     │ test1 │                1 │             300 │
    └───────┴───────┴──────────────────┴─────────────────┘
    ```
    
   
#### Replicated\*MergeTree引擎
ClickHouse中的所有MergeTree家族引擎前面加上Replicated就成了支持副本的合并树引擎。
图1合并树引擎图   
![](https://support.huaweicloud.com/usermanual-cloudtable/zh-cn_image_0000002523986889.png "点击放大")
- Replicated表引擎的创建模板：
  ```
  ENGINE = Replicated*MergeTree('ZooKeeper存储路径','副本名称', ...)
  ```
  表5参数表 
  | 参数            | 说明                                                                |
  |:---|:---|
  | ZooKeeper存储路径 | ZooKeeper中该表相关数据的存储路径，建议规范化，如：/clickhouse/tables/{shard}/数据库名/表名。 |
  | 副本名称          | 一般用{replica}即可。                                                   |
     
  
 
#### Distributed表引擎
Distributed表引擎本身不存储任何数据，而是作为数据分片的透明代理，能够自动路由数据到集群中的各个节点，分布式表需要和其他本地数据表一起协同工作。分布式表会将接收到的读写任务分发到各个本地表，而实际上数据的存储在各个节点的本地表中。
图2Distributed   
![](https://support.huaweicloud.com/usermanual-cloudtable/zh-cn_image_0000002491707206.png)
- Distributed表引擎创建模板：
  ```
  ENGINE = Distributed(cluster_name, database_name, table_name, [sharding_key])
  ```
  表6Distributed表参数说明 
  | 参数            | 说明                                                |
  |:---|:---|
  | cluster_name  | 集群名称，在对分布式表执行读写的过程中，使用集群的配置信息查找对应的ClickHouse实例节点。 |
  | database_name | 数据库名称。                                            |
  | table_name    | 数据库下对应的本地表名称，用于将分布式表映射到本地表上。                      |
  | sharding_key  | 分片键（可选参数），分布式表会按照这个规则，将数据分发到各个本地表中。               |
     
  
- 使用示例。
  - 先创建一个表名为demo的ReplicatedMergeTree本地表。
    ```
    CREATE TABLE default.demo ON CLUSTER default_cluster( `EventDate` DateTime, `id` UInt64)ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/default/demo', '{replica}') PARTITION BY toYYYYMM(EventDate) ORDER BY id;
    ```
    
  
  - 基于本地表demo创建表名为demo_all的Distributed表。
    ```
    CREATE TABLE default.demo_all ON CLUSTER default_cluster( `EventDate` DateTime, `id` UInt64)ENGINE = Distributed(default_cluster, default, demo, rand());
    ```
    
   
- 分布式表创建规则。
  - 创建Distributed表时需加上on cluster cluster_name，这样建表语句在某一个ClickHouse实例上执行一次即可分发到集群中所有实例上执行。
  
  - 分布式表通常以本地表加"_all"命名。它与本地表形成一对多的映射关系，之后可以通过分布式表代理操作多张本地表。
  
  - 分布式表的表结构尽量和本地表的结构一致。如果不一致，在建表时不会报错，但在查询或者插入时可能会抛出异常。