本文介绍了ClickHouse中表引擎的特点，以及常用表引擎的功能。

概述

表引擎即表的类型，在ClickHouse中的作用十分关键，直接决定了数据如何存储和读取、是否支持并发读写、是否支持索引、支持的查询种类、是否支持主备复制等。
ClickHouse提供了多种表引擎，用途广泛。分为MergeTree、Log、Integration、Special四个系列，在这些表引擎之外，ClickHouse还提供了Replicated、Distributed等高级表引擎，功能上与其他表引擎正交，根据场景组合使用。下图是ClickHouse提供的四个系列表引擎汇总。clickhouse表引擎-扩展 - 图1

  • MergeTree系列：适用于高负载任务的最通用和功能最强大的表引擎。这些引擎的共同特点是可以快速插入数据并进行后续的后台数据处理。
  • Log系列：功能相对简单，主要用于快速写入小表（1百万行左右的表），然后全部读出的场景。
  • Integration系列：主要用于将外部数据导入到ClickHouse中，或者在ClickHouse中直接操作外部数据源。
  • Special系列：大多是为了特定场景而定制的。

MergeTree系列

Clickhouse 中最强大的表引擎当属MergeTree（合并树）引擎及该系列（*MergeTree）中的其他引擎。MergeTree系列的引擎被设计用于插入极大量的数据到一张表当中。数据可以以数据片段的形式一个接着一个的快速写入，数据片段在后台按照一定的规则进行合并。相比在插入时不断修改（重写）已存储的数据，这种策略会高效很多。
以下重点介绍MergeTree、ReplacingMergeTree、CollapsingMergeTree、VersionedCollapsingMergeTree、SummingMergeTree、AggregatingMergeTree引擎。

MergeTree

MergeTree表引擎主要用于海量数据分析，支持数据分区、存储有序、主键索引、稀疏索引、数据TTL等。MergeTree支持所有ClickHouse SQL语法，但是有些功能与MySQL并不一致，比如在MergeTree中主键并不用于去重，以下通过示例说明。

  1. 创建表test_tbl，主键为（id, create_time），并且按照主键进行存储排序，按照create_time进行数据分区，数据保留最近一个月。

    1. CREATE TABLE test_tbl (
    2. id UInt16,
    3. create_time Date,
    4. comment Nullable(String)
    5. ) ENGINE = MergeTree()
    6. PARTITION BY create_time
    7.   ORDER BY  (id, create_time)
    8.   PRIMARY KEY (id, create_time)
    9.   TTL create_time + INTERVAL 1 MONTH
    10.   SETTINGS index_granularity=8192;

  2. 写入数据，这里我们写入几条primary key相同的数据。

    1. insert into test_tbl values(0, '2019-12-12', null);
    2. insert into test_tbl values(0, '2019-12-12', null);
    3. insert into test_tbl values(1, '2019-12-13', null);
    4. insert into test_tbl values(1, '2019-12-13', null);
    5. insert into test_tbl values(2, '2019-12-14', null);

  3. 查询数据，可以看到虽然主键id、create_time相同的数据只有3条数据，但是结果却有5行。

    1. select count(*) from test_tbl;
    2. ┌─count()─┐
    3. │       5 │
    4. └─────────┘
    5. select * from test_tbl;
    6. ┌─id─┬─create_time─┬─comment─┐
    7. │  2 │  2019-12-14 │ ᴺᵁᴸᴸ    │
    8. └────┴─────────────┴─────────┘
    9. ┌─id─┬─create_time─┬─comment─┐
    10. │  1 │  2019-12-13 │ ᴺᵁᴸᴸ    │
    11. └────┴─────────────┴─────────┘
    12. ┌─id─┬─create_time─┬─comment─┐
    13. │  0 │  2019-12-12 │ ᴺᵁᴸᴸ    │
    14. └────┴─────────────┴─────────┘
    15. ┌─id─┬─create_time─┬─comment─┐
    16. │  1 │  2019-12-13 │ ᴺᵁᴸᴸ    │
    17. └────┴─────────────┴─────────┘
    18. ┌─id─┬─create_time─┬─comment─┐
    19. │  0 │  2019-12-12 │ ᴺᵁᴸᴸ    │
    20. └────┴─────────────┴─────────┘

  4. 由于MergeTree采用类似LSM tree的结构，很多存储层处理逻辑直到Compaction期间才会发生。因此强制后台Compaction执行完毕，再次查询，发现仍旧有5条数据。

    1. optimize table test_tbl final;
    2. select count(*) from test_tbl;
    3. ┌─count()─┐
    4. │       5 │
    5. └─────────┘
    6. select * from test_tbl;
    7. ┌─id─┬─create_time─┬─comment─┐
    8. │  2 │  2019-12-14 │ ᴺᵁᴸᴸ    │
    9. └────┴─────────────┴─────────┘
    10. ┌─id─┬─create_time─┬─comment─┐
    11. │  0 │  2019-12-12 │ ᴺᵁᴸᴸ    │
    12. │  0 │  2019-12-12 │ ᴺᵁᴸᴸ    │
    13. └────┴─────────────┴─────────┘
    14. ┌─id─┬─create_time─┬─comment─┐
    15. │  1 │  2019-12-13 │ ᴺᵁᴸᴸ    │
    16. │  1 │  2019-12-13 │ ᴺᵁᴸᴸ    │
    17. └────┴─────────────┴─────────┘

    结合以上示例可以看到，MergeTree虽然有主键索引，但是其主要作用是加速查询，而不是类似MySQL等数据库用来保持记录唯一。即便在Compaction完成后，主键相同的数据行也仍旧共同存在。

ReplacingMergeTree

为了解决MergeTree相同主键无法去重的问题，ClickHouse提供了ReplacingMergeTree引擎，用于删除排序键值相同的重复项。示例如下：

  1. -- 建表
  2. CREATE TABLE test_tbl_replacing (
  3.   id UInt16,
  4.   create_time Date,
  5.   comment Nullable(String)
  6. ) ENGINE = ReplacingMergeTree()
  7.    PARTITION BY create_time
  8.      ORDER BY  (id, create_time)
  9.      PRIMARY KEY (id, create_time)
  10.      SETTINGS index_granularity=8192;
  11. -- 写入主键重复的数据
  12. insert into test_tbl_replacing values(0, '2019-12-12', null);
  13. insert into test_tbl_replacing values(0, '2019-12-12', null);
  14. insert into test_tbl_replacing values(1, '2019-12-13', null);
  15. insert into test_tbl_replacing values(1, '2019-12-13', null);
  16. insert into test_tbl_replacing values(2, '2019-12-14', null);
  17. -- 查询，可以看到未compaction之前，主键重复的数据，仍旧存在。
  18. select count(*) from test_tbl_replacing;
  19. ┌─count()─┐
  20. │       5 │
  21. └─────────┘
  22. select * from test_tbl_replacing;
  23. ┌─id─┬─create_time─┬─comment─┐
  24. │  0 │  2019-12-12 │ ᴺᵁᴸᴸ    │
  25. └────┴─────────────┴─────────┘
  26. ┌─id─┬─create_time─┬─comment─┐
  27. │  0 │  2019-12-12 │ ᴺᵁᴸᴸ    │
  28. └────┴─────────────┴─────────┘
  29. ┌─id─┬─create_time─┬─comment─┐
  30. │  1 │  2019-12-13 │ ᴺᵁᴸᴸ    │
  31. └────┴─────────────┴─────────┘
  32. ┌─id─┬─create_time─┬─comment─┐
  33. │  1 │  2019-12-13 │ ᴺᵁᴸᴸ    │
  34. └────┴─────────────┴─────────┘
  35. ┌─id─┬─create_time─┬─comment─┐
  36. │  2 │  2019-12-14 │ ᴺᵁᴸᴸ    │
  37. └────┴─────────────┴─────────┘
  38. -- 强制后台compaction：
  39. optimize table test_tbl_replacing final;
  40. -- 再次查询：主键重复的数据已经消失。
  41. select count(*) from test_tbl_replacing;
  42. ┌─count()─┐
  43. │       3 │
  44. └─────────┘
  45. select * from test_tbl_replacing;
  46. ┌─id─┬─create_time─┬─comment─┐
  47. │  2 │  2019-12-14 │ ᴺᵁᴸᴸ    │
  48. └────┴─────────────┴─────────┘
  49. ┌─id─┬─create_time─┬─comment─┐
  50. │  1 │  2019-12-13 │ ᴺᵁᴸᴸ    │
  51. └────┴─────────────┴─────────┘
  52. ┌─id─┬─create_time─┬─comment─┐
  53. │  0 │  2019-12-12 │ ᴺᵁᴸᴸ    │
  54. └────┴─────────────┴─────────┘

虽然ReplacingMergeTree提供了主键去重的能力，但是仍旧有以下限制：

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

因此ReplacingMergeTree更多被用于确保数据最终被去重，而无法保证查询过程中主键不重复。

CollapsingMergeTree

CollapsingMergeTree用来消除ReplacingMergeTree的功能限制。该引擎要求在建表语句中指定一个标记列Sign，按照Sign的值将行分为两类：Sign=1的行称之为状态行，Sign=-1的行称之为取消行。每次需要新增状态时，写入一行状态行；需要删除状态时，则写入一行取消行。
后台Compaction时会将主键相同、Sign相反的行进行折叠（删除）。而尚未进行Compaction的数据，状态行与取消行同时存在。因此为了能够达到主键折叠（删除）的目的，需要业务层进行适当改造：

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

示例如下：

  1. -- 建表
  2. CREATE TABLE UAct
  3. (
  4.     UserID UInt64,
  5.     PageViews UInt8,
  6.     Duration UInt8,
  7.     Sign Int8
  8. )
  9. ENGINE = CollapsingMergeTree(Sign)
  10. ORDER BY UserID;
  11. -- 插入状态行，注意sign一列的值为1
  12. INSERT INTO UAct VALUES (4324182021466249494, 5, 146, 1);
  13. -- 插入一行取消行，用于抵消上述状态行。注意sign一列的值为-1，其余值与状态行一致；
  14. -- 并且插入一行主键相同的新状态行，用来将PageViews从5更新至6，将Duration从146更新为185.
  15. INSERT INTO UAct VALUES (4324182021466249494, 5, 146, -1), (4324182021466249494, 6, 185, 1);
  16. -- 查询数据：可以看到未Compaction之前，状态行与取消行共存。
  17. SELECT * FROM UAct;
  18. ┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
  19. │ 4324182021466249494 │         5 │      146 │   -1 │
  20. │ 4324182021466249494 │         6 │      185 │    1 │
  21. └─────────────────────┴───────────┴──────────┴──────┘
  22. ┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
  23. │ 4324182021466249494 │         5 │      146 │    1 │
  24. └─────────────────────┴───────────┴──────────┴──────┘
  25. -- 为了获取正确的sum值，需要改写SQL： 
  26. -- sum(PageViews) => sum(PageViews * Sign)、 
  27. -- sum(Duration) => sum(Duration * Sign)
  28. SELECT
  29.     UserID,
  30.     sum(PageViews * Sign) AS PageViews,
  31.     sum(Duration * Sign) AS Duration
  32. FROM UAct
  33. GROUP BY UserID
  34. HAVING sum(Sign) > 0;
  35. ┌──────────────UserID─┬─PageViews─┬─Duration─┐
  36. │ 4324182021466249494 │         6 │      185 │
  37. └─────────────────────┴───────────┴──────────┘
  38. -- 强制后台Compaction
  39. optimize table UAct final;
  40. -- 再次查询，可以看到状态行、取消行已经被折叠，只剩下最新的一行状态行。
  41. select * from UAct;
  42. ┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
  43. │ 4324182021466249494 │         6 │      185 │    1 │
  44. └─────────────────────┴───────────┴──────────┴──────┘

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

  1. -- 建表
  2. CREATE TABLE UAct_order
  3. (
  4.     UserID UInt64,
  5.     PageViews UInt8,
  6.     Duration UInt8,
  7.     Sign Int8
  8. )
  9. ENGINE = CollapsingMergeTree(Sign)
  10. ORDER BY UserID;
  11. -- 先插入取消行
  12. INSERT INTO UAct_order VALUES (4324182021466249495, 5, 146, -1);
  13. -- 后插入状态行
  14. INSERT INTO UAct_order VALUES (4324182021466249495, 5, 146, 1);
  15. -- 强制Compaction
  16. optimize table UAct_order final;
  17. -- 可以看到即便Compaction之后也无法进行主键折叠: 2行数据仍旧都存在。
  18. select * from UAct_order;
  19. ┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
  20. │ 4324182021466249495 │         5 │      146 │   -1 │
  21. │ 4324182021466249495 │         5 │      146 │    1 │
  22. └─────────────────────┴───────────┴──────────┴──────┘

VersionedCollapsingMergeTree

为了解决CollapsingMergeTree乱序写入情况下无法正常折叠问题，VersionedCollapsingMergeTree表引擎在建表语句中新增了一列Version，用于在乱序情况下记录状态行与取消行的对应关系。主键相同，且Version相同、Sign相反的行，在Compaction时会被删除。
与CollapsingMergeTree类似， 为了获得正确结果，业务层需要改写SQL，将count()、sum(col)分别改写为sum(Sign)、sum(col * Sign)。
示例如下：

  1. -- 建表
  2. CREATE TABLE UAct_version
  3. (
  4.     UserID UInt64,
  5.     PageViews UInt8,
  6.     Duration UInt8,
  7.     Sign Int8,
  8.     Version UInt8
  9. )
  10. ENGINE = VersionedCollapsingMergeTree(Sign, Version)
  11. ORDER BY UserID;
  12. -- 先插入一行取消行，注意Signz=-1, Version=1
  13. INSERT INTO UAct_version VALUES (4324182021466249494, 5, 146, -1, 1);
  14. -- 后插入一行状态行，注意Sign=1, Version=1；及一行新的状态行注意Sign=1, Version=2，将PageViews从5更新至6，将Duration从146更新为185。
  15. INSERT INTO UAct_version VALUES (4324182021466249494, 5, 146, 1, 1),(4324182021466249494, 6, 185, 1, 2);
  16. -- 查询可以看到未compaction情况下，所有行都可见。
  17. SELECT * FROM UAct_version;
  18. ┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
  19. │ 4324182021466249494 │         5 │      146 │   -1 │
  20. │ 4324182021466249494 │         6 │      185 │    1 │
  21. └─────────────────────┴───────────┴──────────┴──────┘
  22. ┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
  23. │ 4324182021466249494 │         5 │      146 │    1 │
  24. └─────────────────────┴───────────┴──────────┴──────┘
  25. -- 为了获取正确的sum值，需要改写SQL： 
  26. -- sum(PageViews) => sum(PageViews * Sign)、 
  27. -- sum(Duration) => sum(Duration * Sign)
  28. SELECT
  29.     UserID,
  30.     sum(PageViews * Sign) AS PageViews,
  31.     sum(Duration * Sign) AS Duration
  32. FROM UAct_version
  33. GROUP BY UserID
  34. HAVING sum(Sign) > 0;
  35. ┌──────────────UserID─┬─PageViews─┬─Duration─┐
  36. │ 4324182021466249494 │         6 │      185 │
  37. └─────────────────────┴───────────┴──────────┘
  38. -- 强制后台Compaction
  39. optimize table UAct_version final;
  40. -- 再次查询，可以看到即便取消行与状态行位置乱序，仍旧可以被正确折叠。
  41. select * from UAct_version;
  42. ┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┬─Version─┐
  43. │ 4324182021466249494 │         6 │      185 │    1 │       2 │
  44. └─────────────────────┴───────────┴──────────┴──────┴─────────┘

SummingMergeTree

ClickHouse通过SummingMergeTree来支持对主键列进行预先聚合。在后台Compaction时，会将主键相同的多行进行sum求和，然后使用一行数据取而代之，从而大幅度降低存储空间占用，提升聚合计算性能。
值得注意的是：

  • ClickHouse只在后台Compaction时才会进行数据的预先聚合，而compaction的执行时机无法预测，所以可能存在部分数据已经被预先聚合、部分数据尚未被聚合的情况。因此，在执行聚合计算时，SQL中仍需要使用GROUP BY子句。
  • 在预先聚合时，ClickHouse会对主键列之外的其他所有列进行预聚合。如果这些列是可聚合的（比如数值类型），则直接sum；如果不可聚合（比如String类型），则随机选择一个值。
  • 通常建议将SummingMergeTree与MergeTree配合使用，使用MergeTree来存储具体明细，使用SummingMergeTree来存储预先聚合的结果加速查询。

示例如下：

  1. -- 建表
  2. CREATE TABLE summtt
  3. (
  4.     key UInt32,
  5.     value UInt32
  6. )
  7. ENGINE = SummingMergeTree()
  8. ORDER BY key
  9. -- 插入数据
  10. INSERT INTO summtt Values(1,1),(1,2),(2,1)
  11. -- compaction前查询，仍存在多行
  12. select * from summtt;
  13. ┌─key─┬─value─┐
  14. │   1 │     1 │
  15. │   1 │     2 │
  16. │   2 │     1 │
  17. └─────┴───────┘
  18. -- 通过GROUP BY进行聚合计算
  19. SELECT key, sum(value) FROM summtt GROUP BY key
  20. ┌─key─┬─sum(value)─┐
  21. │   2 │          1 │
  22. │   1 │          3 │
  23. └─────┴────────────┘
  24. -- 强制compaction
  25. optimize table summtt final;
  26. -- compaction后查询，可以看到数据已经被预先聚合
  27. select * from summtt;
  28. ┌─key─┬─value─┐
  29. │   1 │     3 │
  30. │   2 │     1 │
  31. └─────┴───────┘
  32. -- compaction后，仍旧需要通过GROUP BY进行聚合计算
  33. SELECT key, sum(value) FROM summtt GROUP BY key
  34. ┌─key─┬─sum(value)─┐
  35. │   2 │          1 │
  36. │   1 │          3 │
  37. └─────┴────────────┘

AggregatingMergeTree

AggregatingMergeTree也是预先聚合引擎的一种，用于提升聚合计算的性能。与SummingMergeTree的区别在于：SummingMergeTree对非主键列进行sum聚合，而AggregatingMergeTree则可以指定各种聚合函数。
AggregatingMergeTree的语法比较复杂，需要结合物化视图或ClickHouse的特殊数据类型AggregateFunction一起使用。在insert和select时，也有独特的写法和要求：写入时需要使用-State语法，查询时使用-Merge语法。
以下通过示例进行介绍。

示例一：配合物化视图使用。

  1. -- 建立明细表
  2. CREATE TABLE visits
  3. (
  4.     UserID UInt64,
  5.     CounterID UInt8,
  6.     StartDate Date,
  7.     Sign Int8
  8. )
  9. ENGINE = CollapsingMergeTree(Sign)
  10. ORDER BY UserID;
  11. -- 对明细表建立物化视图，该物化视图对明细表进行预先聚合
  12. -- 注意：预先聚合使用的函数分别为： sumState, uniqState。对应于写入语法<agg>-State.
  13. CREATE MATERIALIZED VIEW visits_agg_view
  14. ENGINE = AggregatingMergeTree() PARTITION BY toYYYYMM(StartDate) ORDER BY (CounterID, StartDate)
  15. AS SELECT
  16.     CounterID,
  17.     StartDate,
  18.     sumState(Sign)    AS Visits,
  19.     uniqState(UserID) AS Users
  20. FROM visits
  21. GROUP BY CounterID, StartDate;
  22. -- 插入明细数据
  23. INSERT INTO visits VALUES(0, 0, '2019-11-11', 1);
  24. INSERT INTO visits VALUES(1, 1, '2019-11-12', 1);
  25. -- 对物化视图进行最终的聚合操作
  26. -- 注意：使用的聚合函数为 sumMerge， uniqMerge。对应于查询语法<agg>-Merge.
  27. SELECT
  28.     StartDate,
  29.     sumMerge(Visits) AS Visits,
  30.     uniqMerge(Users) AS Users
  31. FROM visits_agg_view
  32. GROUP BY StartDate
  33. ORDER BY StartDate;
  34. -- 普通函数 sum, uniq不再可以使用
  35. -- 如下SQL会报错： Illegal type AggregateFunction(sum, Int8) of argument 
  36. SELECT
  37.     StartDate,
  38.     sum(Visits),
  39.     uniq(Users)
  40. FROM visits_agg_view
  41. GROUP BY StartDate
  42. ORDER BY StartDate;

示例二：配合特殊数据类型AggregateFunction使用。

  1. -- 建立明细表
  2. CREATE TABLE detail_table
  3. (   CounterID UInt8,
  4.     StartDate Date,
  5.     UserID UInt64
  6. ) ENGINE = MergeTree() 
  7. PARTITION BY toYYYYMM(StartDate) 
  8. ORDER BY (CounterID, StartDate);
  9. -- 插入明细数据
  10. INSERT INTO detail_table VALUES(0, '2019-11-11', 1);
  11. INSERT INTO detail_table VALUES(1, '2019-11-12', 1);
  12. -- 建立预先聚合表，
  13. -- 注意：其中UserID一列的类型为：AggregateFunction(uniq, UInt64)
  14. CREATE TABLE agg_table
  15. (   CounterID UInt8,
  16.     StartDate Date,
  17.     UserID AggregateFunction(uniq, UInt64)
  18. ) ENGINE = AggregatingMergeTree() 
  19. PARTITION BY toYYYYMM(StartDate) 
  20. ORDER BY (CounterID, StartDate);
  21. -- 从明细表中读取数据，插入聚合表。
  22. -- 注意：子查询中使用的聚合函数为 uniqState， 对应于写入语法<agg>-State
  23. INSERT INTO agg_table
  24. select CounterID, StartDate, uniqState(UserID)
  25. from detail_table
  26. group by CounterID, StartDate
  27. -- 不能使用普通insert语句向AggregatingMergeTree中插入数据。
  28. -- 本SQL会报错：Cannot convert UInt64 to AggregateFunction(uniq, UInt64)
  29. INSERT INTO agg_table VALUES(1, '2019-11-12', 1);
  30. -- 从聚合表中查询。
  31. -- 注意：select中使用的聚合函数为uniqMerge，对应于查询语法<agg>-Merge
  32. SELECT uniqMerge(UserID) AS state 
  33. FROM agg_table 
  34. GROUP BY CounterID, StartDate;