ClickHouse表引擎概述

背景介绍

表引擎在ClickHouse中的作用十分关键，不同的表引擎决定了：

• 数据存储和读取的位置。
• 支持哪些查询方式。
• 能否并发式访问数据。
• 能否使用索引。
• 是否可以执行多线程请求。
• 数据复制使用的参数。

其中MergeTree和Distributed是ClickHouse表引擎中最重要，也是最常使用的两个引擎，本文将重点进行介绍。

概述

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

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条数据记录一个排序索引值。

  说明：

  如果被查询的数据存在于分区或排序字段中，能极大降低数据查找时间。

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

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

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 │
    └────┴───────┘
    说明：

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

Replicated*MergeTree引擎

ClickHouse中的所有MergeTree家族引擎前面加上Replicated就成了支持副本的合并树引擎。

图1 合并树引擎图

• Replicated表引擎的创建模板：

  ENGINE = Replicated*MergeTree('ZooKeeper存储路径','副本名称', ...)

  表5 参数表

  参数	说明
  ZooKeeper存储路径	ZooKeeper中该表相关数据的存储路径，建议规范化，如：/clickhouse/tables/{shard}/数据库名/表名。
  副本名称	一般用{replica}即可。

Distributed表引擎

Distributed表引擎本身不存储任何数据，而是作为数据分片的透明代理，能够自动路由数据到集群中的各个节点，分布式表需要和其他本地数据表一起协同工作。分布式表会将接收到的读写任务分发到各个本地表，而实际上数据的存储在各个节点的本地表中。

图2 Distributed

• Distributed表引擎创建模板：

  ENGINE = Distributed(cluster_name, database_name, table_name, [sharding_key])

  表6 Distributed表参数说明

  参数	说明
  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”命名。它与本地表形成一对多的映射关系，之后可以通过分布式表代理操作多张本地表。
  • 分布式表的表结构尽量和本地表的结构一致。如果不一致，在建表时不会报错，但在查询或者插入时可能会抛出异常。