ClickHouse Senior Course Ⅴ

序言

在了解数据库引擎之后,梳理下表引擎.

数据库引擎决定了数据库的应用方向,那表引擎就是具体的实现方式.官网同时也提供了多种类型的表引擎给我们使用,但是我们只简单了解一个表引擎,后面用到的时候在选择.cuiyaonan2000@163.com

参考资料:

1. 表引擎 | ClickHouse Docs
2. MergeTree | ClickHouse Docs

表引擎作用范围

• 数据的存储方式和位置，写到哪里以及从哪里读取数据
• 支持哪些查询以及如何支持。
• 并发数据访问。
• 索引的使用（如果存在）。
• 是否可以执行多线程请求。
• 数据复制参数。

引擎类型

MergeTree---(clickhouse自身库所支持,并推荐)​

适用于高负载任务的最通用和功能最强大的表引擎。这些引擎的共同特点是可以快速插入数据并进行后续的后台数据处理。 MergeTree系列引擎支持数据复制（使用Replicated* 的引擎版本），分区和一些其他引擎不支持的其他功能。

该类型的引擎：

• MergeTree
• ReplacingMergeTree
• SummingMergeTree
• AggregatingMergeTree
• CollapsingMergeTree
• VersionedCollapsingMergeTree
• GraphiteMergeTree

日志​

具有最小功能的轻量级引擎。当您需要快速写入许多小表（最多约100万行）(此处的许多小表是什么意思,为什么不是一个表cuiyaonan2000@163.com)并在以后整体读取它们时，该类型的引擎是最有效的。

该类型的引擎：

• TinyLog
• StripeLog
• Log

集成引擎(连接其它数据库的表引擎)​

用于与其他的数据存储与处理系统集成的引擎。 该类型的引擎：

• Kafka
• MySQL
• ODBC
• JDBC
• HDFS

用于其他特定功能的引擎(即表中的值为某种类型的数据,才使用的引擎)​

该类型的引擎：

• Distributed
• MaterializedView
• Dictionary
• Merge
• File
• Null
• Set
• Join
• URL
• View
• Memory
• Buffer

虚拟列(就是创建表的时候系统带的字段列,以下划线开头)

虚拟列是表引擎组成的一部分，它在对应的表引擎的源代码中定义。

您不能在 CREATE TABLE 中指定虚拟列，并且虚拟列不会包含在 SHOW CREATE TABLE 和 DESCRIBE TABLE 的查询结果中。虚拟列是只读的，所以您不能向虚拟列中写入数据。

如果想要查询虚拟列中的数据，您必须在SELECT查询中包含虚拟列的名字。SELECT * 不会返回虚拟列的内容。

若您创建的表中有一列与虚拟列的名字相同，那么虚拟列将不能再被访问。我们不建议您这样做。为了避免这种列名的冲突，虚拟列的名字一般都以下划线开头。

MergeTree

Clickhouse 中最强大的表引擎当属 MergeTree （合并树）引擎及该系列（*MergeTree）中的其他引擎。

MergeTree 系列的引擎被设计用于插入极大量的数据到一张表当中。数据可以以数据片段的形式一个接着一个的快速写入，数据片段在后台按照一定的规则进行合并。相比在插入时不断修改（重写）已存储的数据，这种策略会高效很多。----这里的数据片段有点类似于数据分片cuiyaonan2000@163.com

• 存储的数据按主键排序: 这使得您能够创建一个小型的稀疏索引来加快数据检索。----聚合索引

• 如果指定了 分区键 的话，可以使用分区。----数据分片

• 支持数据副本  ----数据的高可用

• 支持数据采样。

创建语句

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

参数说明

• ENGINE - 引擎名和参数。 ENGINE = MergeTree(). MergeTree 引擎没有参数。

• ORDER BY — 排序键。

  可以是一组列的元组或任意的表达式。 例如: ORDER BY (CounterID, EventDate) 。

  如果没有使用 PRIMARY KEY 显式指定的主键，ClickHouse 会使用排序键作为主键。

  如果不需要排序，可以使用 ORDER BY tuple(). 参考 选择主键

• PARTITION BY — 分区键 ，可选项。

  大多数情况下，不需要分使用区键。即使需要使用，也不需要使用比月更细粒度的分区键。分区不会加快查询（这与 ORDER BY 表达式不同）。永远也别使用过细粒度的分区键。不要使用客户端指定分区标识符或分区字段名称来对数据进行分区（而是将分区字段标识或名称作为 ORDER BY 表达式的第一列来指定分区）。---切记分区间最小粒度是月cuiyaonan2000@163.com

  要按月分区，可以使用表达式 toYYYYMM(date_column) ，这里的 date_column 是一个 Date 类型的列。分区名的格式会是 "YYYYMM" 。

• PRIMARY KEY - 如果要 选择与排序键不同的主键，在这里指定，可选项。

  默认情况下主键跟排序键（由 ORDER BY 子句指定）相同。 因此，大部分情况下不需要再专门指定一个 PRIMARY KEY 子句。

• SAMPLE BY - 用于抽样的表达式，可选项。

  如果要用抽样表达式，主键中必须包含这个表达式。例如： SAMPLE BY intHash32(UserID) ORDER BY (CounterID, EventDate, intHash32(UserID)) 。

• TTL - 指定行存储的持续时间并定义数据片段在硬盘和卷上的移动逻辑的规则列表，可选项。

  表达式中必须存在至少一个 Date 或 DateTime 类型的列，比如：

  TTL date + INTERVAl 1 DAY

  规则的类型 DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'指定了当满足条件（到达指定时间）时所要执行的动作：移除过期的行，还是将数据片段（如果数据片段中的所有行都满足表达式的话）移动到指定的磁盘（TO DISK 'xxx') 或 卷（TO VOLUME 'xxx'）。默认的规则是移除（DELETE）。可以在列表中指定多个规则，但最多只能有一个DELETE的规则。

  更多细节，请查看 表和列的 TTL

• SETTINGS — 控制 MergeTree 行为的额外参数，可选项：

  • index_granularity — 索引粒度。索引中相邻的『标记』间的数据行数。默认值8192 。参考数据存储。
  • index_granularity_bytes — 索引粒度，以字节为单位，默认值: 10Mb。如果想要仅按数据行数限制索引粒度, 请设置为0(不建议)。
  • min_index_granularity_bytes - 允许的最小数据粒度，默认值：1024b。该选项用于防止误操作，添加了一个非常低索引粒度的表。参考数据存储
  • enable_mixed_granularity_parts — 是否启用通过 index_granularity_bytes 控制索引粒度的大小。在19.11版本之前, 只有 index_granularity 配置能够用于限制索引粒度的大小。当从具有很大的行（几十上百兆字节）的表中查询数据时候，index_granularity_bytes 配置能够提升ClickHouse的性能。如果您的表里有很大的行，可以开启这项配置来提升SELECT 查询的性能。
  • use_minimalistic_part_header_in_zookeeper — ZooKeeper中数据片段存储方式 。如果use_minimalistic_part_header_in_zookeeper=1 ，ZooKeeper 会存储更少的数据。更多信息参考[服务配置参数](Server Settings | ClickHouse Documentation)这章中的 设置描述 。
  • min_merge_bytes_to_use_direct_io — 使用直接 I/O 来操作磁盘的合并操作时要求的最小数据量。合并数据片段时，ClickHouse 会计算要被合并的所有数据的总存储空间。如果大小超过了 min_merge_bytes_to_use_direct_io 设置的字节数，则 ClickHouse 将使用直接 I/O 接口（O_DIRECT 选项）对磁盘读写。如果设置 min_merge_bytes_to_use_direct_io = 0 ，则会禁用直接 I/O。默认值：10 * 1024 * 1024 * 1024 字节。

    <a name="mergetree_setting-merge_with_ttl_timeout"></a>
    

  • merge_with_ttl_timeout — TTL合并频率的最小间隔时间，单位：秒。默认值: 86400 (1 天)。
  • write_final_mark — 是否启用在数据片段尾部写入最终索引标记。默认值: 1（不要关闭）。
  • merge_max_block_size — 在块中进行合并操作时的最大行数限制。默认值：8192
  • storage_policy — 存储策略。 参见 使用具有多个块的设备进行数据存储.
  • min_bytes_for_wide_part,min_rows_for_wide_part 在数据片段中可以使用Wide格式进行存储的最小字节数/行数。您可以不设置、只设置一个，或全都设置。参考：数据存储
  • max_parts_in_total - 所有分区中最大块的数量(意义不明)
  • max_compress_block_size - 在数据压缩写入表前，未压缩数据块的最大大小。您可以在全局设置中设置该值(参见max_compress_block_size)。建表时指定该值会覆盖全局设置。
  • min_compress_block_size - 在数据压缩写入表前，未压缩数据块的最小大小。您可以在全局设置中设置该值(参见min_compress_block_size)。建表时指定该值会覆盖全局设置。
  • max_partitions_to_read - 一次查询中可访问的分区最大数。您可以在全局设置中设置该值(参见max_partitions_to_read)。

建表实例

ENGINE MergeTree() PARTITION BY toYYYYMM(EventDate) ORDER BY (CounterID, EventDate, intHash32(UserID)) SAMPLE BY intHash32(UserID) SETTINGS index_granularity=8192

在这个例子中，我们设置了按月进行分区。

同时我们设置了一个按用户 ID 哈希的抽样表达式。这使得您可以对该表中每个 CounterID 和 EventDate 的数据伪随机分布。如果您在查询时指定了 SAMPLE 子句。 ClickHouse会返回对于用户子集的一个均匀的伪随机数据采样。