Clickhouse Table Engine 介绍

这是我参与8月更文挑战的第18天，活动详情查看：8月更文挑战

简介

表引擎可以定义：

• 数据如何存储，如何读取
• 支持何种查询
• 并发数据访问能力
• 索引的使用
• 是否支持多线程请求执行
• 数据如何同步

1. TinyLog 引擎

1. TinyLog 是最简单的表引擎，它将数据保存到磁盘，每一列都以单独压缩文件形式保存。当写入数据时，数据追加到文件的末尾
2. 并发数据访问不限制任何形式

   同时读写，读操作将报错 同时写，数据损坏

3. 典型使用场景：一次写入，多次读取的应用场景。此引擎适用于相对较小的表（建议最多 100W 行）。 如果您有许多小表，则使用此表引擎是有意义的，因为它比 Log 引擎更简单（需要打开的文件更少）。 当您拥有大量小表时，这种情况可能会导致生产效率低下。
4. TinyLog 引擎不支持索引

在 Yandex.Metrica 中，TinyLog 表用于数据的小批量处理的中间表

2. Log 引擎

1. 类似 TinyLog，不同于 TinyLog 在于：标记marks的小文件同时保留在列存储文件中，这些标记会写到每个数据块中。标记中包含偏移量，可以知道从哪开始读文件，跳过特定的行数，此机制使得能够支持多线程并行读取。
2. 并发读取，写入时阻塞读取，如果写入失败，表将会被破坏
3. Log 引擎不支持索引

Log 引擎适用于临时数据，一次写入表以及测试或演示目的

3. Memory 引擎

1. 内存表引擎保存在内存中, 数据处于未压缩状态，数据保存格式与读取数据的格式相同
2. 并发数据访问是同步的，无锁访问
3. 读写数据操作互相不受影响，但数据索引不支持
4. 因为无磁盘读写, 压缩/解压缩, 和序列化/反序列化操作，因此单个 SQL 语句查询可达到 10 GB/秒（高效率，多数情况下，效率能等同于 Merge Tree)
5. 当重启服务器后, 数据会在表中清空
6. 它可用于小数据量(1亿条左右)的高速读取数据场景或用于测试环境.

内存引擎也可用于外部数据的临时表查询和实现 GLOBAL IN 操作

4. Buffers 引擎

将数据写入到内存中，周期性刷新数据到另外的表中。在读取操作的过程中，数据从 Buffer 和另外的表中同时读取。

1. 周期性刷新数据到下级表，可以设置 时间/条数/size，满足就进行刷新
2. Buffer 表不支持索引，完全扫描大缓存区的数据可能会很慢（下级表使用自己的索引）
3. 如果 Buffer 表中的列集与下级表中的列集不匹配，则会插入两个表中存在的列的子集
4. 如果 Buffer 表和下级表中的某列在类型上不匹配，则会输出异常，并清除缓存
5. 如果对应的下级表不存在，同样会输出异常，并清除缓存
6. 如果需要执行 Alert，建议先删除 buffer 表，则 Alert 下级表，然后再次创建 Buffer 表
7. 如果服务器异常重启，缓冲区中的数据将丢失
8. PREWHERE，FINAL 和 SAMPLE 对缓冲表不起作用，这些条件直接传递到下级表，不用于 Buffer 中的数据
9. 当添加数据到 Buffer 时，如果一个缓冲区被锁，这时读操作可能会出现延迟
10. 写入到 Buffer 中的数据，最终可能会以不同的顺序被刷新不同的块中，因此，Buffer 表很难用于正确写入 CollapsingMergeTree
11. 对于目标表是复制表，Buffer 表中的数据会丢失复制表的相关特性，在使用过程中容易出问题

只建议在极少数情况下使用 Buffer 表

• 当在一个单位时间内从大量服务器接收到太多 INSERT 并且在插入之前无法缓冲数据时使用缓冲表，这意味着 INSERT 不能足够快地运行
• 请注意，一次插入一行数据是没有意义的，即使对于 Buffer 表也是如此。这只会产生每秒几千行的速度，而插入更大的数据块每秒可以产生超过一百万行

5. 外部数据

ClickHouse 允许向服务器发送处理查询所需的数据以及 SELECT 查询。 此数据放在临时表中（请参阅“临时表”一节），可以在查询中使用（例如，在 IN 运算符中）。

如果需要使用大量外部数据运行多个查询，请不要使用此功能，最好提前将数据上传到数据库

可以使用命令行客户端（在非交互模式下）或使用 HTTP 接口上载外部数据。

6. Merge Tree 引擎

Merge Tree系列引擎，是Clickhouse 最强大的引擎

主要特性：

1. 按主键排序存储
2. 支持稀疏索引，便于更快查找数据
3. 支持分区，在使用分区的某些操作时，Clickhouse有自动机制确保更佳的查询性能
4. 支持数据复制，见ReplicatedMergeTree家族引擎
5. 支持数据抽样

数据存储

1. 表由按主键排序的数据部分组成。
2. 插入数据时，会创建单独的数据块（part），每个数据部分按主键排序按字典顺序排序；例如，如果主键是（CounterID，Date），则 part 中的数据先按 CounterID 排序，再按 Date 排序。
3. 属于不同分区的数据被分割在不同的块中（part），为了更高效的存储，Clickhouse 会合并不同的 part。不同分区中的数据块不会进行合并，合并机制不保证具有相同主键的所有行都在同一数据部分中
4. 对于每个数据部分，ClickHouse 创建一个索引文件，其中包含每个索引行的主键值（“mark”）。索引行号被定义为n * index_granularity。最大值 n 等于将总行数除以index_granularity的整数部分。对于每列，“标记”也写入与主键相同的索引行。这些“标记”允许您直接在列中查找数据。

MergeTree 引擎的适合场景：单个大型表，并且以小块的形式不断向其中添加数据

对于主键和索引在查询中的高效原理

回忆两个 clickhouse 的特性：

1. 数据按照主键排序
2. 稀疏索引

举例：主键(CounterID, Date)，排序后的数据及生成的索引类似于：

Whole data:     [-------------------------------------------------------------------------]
CounterID:      [aaaaaaaaaaaaaaaaaabbbbcdeeeeeeeeeeeeefgggggggghhhhhhhhhiiiiiiiiikllllllll]
Date:           [1111111222222233331233211111222222333211111112122222223111112223311122333]
Marks:           |      |      |      |      |      |      |      |      |      |      |
                a,1    a,2    a,3    b,3    e,2    e,3    g,1    h,2    i,1    i,3    l,3
Marks numbers:   0      1      2      3      4      5      6      7      8      9      10

当数据查询为：

• CounterID in ('a', 'h'), the server reads the data in the ranges of marks [0, 3) and [6, 8)
• CounterID IN ('a', 'h') AND Date = 3, the server reads the data in the ranges of marks [1, 3) and [7, 8).

上面的示例表明，使用索引总是比完全扫描更有效。

稀疏索引

• 稀疏索引允许读取额外的数据，当读取单个主键范围时，可以从每个数据块中额外读取 index_granularity * 2行，在index_granularity=8192时，Clickhouse并不会有性能问题
• 稀疏索引能够支撑处理非常大的数据表行，因为索引存储在RAM中
• Clickhouse并不要求唯一主键。支持重复插入相同主键的数据

如何选择主键 主键中的列数没有明确限制，可以通过增减主键中的列，以达到：

1. 提高索引性能
   • 如果主键是（a，b），则在满足以下条件时添加另一列c将提高性能：
     • 在列c上存在条件的查询。
     • 具有相同（a，b）值的长数据范围（比index_granularity长几倍）是常见的。 换句话说，添加另一列时，您可以跳过很长的数据范围。
2. 提高数据压缩率.
   • ClickHouse按主键对数据进行排序，因此一致性越高，压缩越好。
3. 在CollapsingMergeTree和SummingMergeTree引擎中合并数据部件时提供其他逻辑

多列主键会对插入性能和内存消耗产生负面影响，但主键中的额外列在进行select查询时不会影响ClickHouse性能。

选择与排序键不同的主键

1. 可以指定与排序键（用于对数据部分中的行进行排序的表达式）不同的主键（表达式，其值被写入每个标记的索引文件中）。在这种情况下，主键表达式元组必须是排序键表达式元组的前缀
2. 排序键的ALTER是一个轻量级操作，因为当一个新列同时添加到表和排序键时，数据部分不需要更改（它们仍然按新的排序键表达式排序）
3. 使用SummingMergeTree和AggregatingMergeTree表引擎时，此功能很有用。在使用这些引擎的常见情况下，该表有两种类型的列：维度和度量。典型查询使用任意GROUP BY聚合度量列的值并按维度过滤。由于SummingMergeTree和AggregatingMergeTree使用相同的排序键值聚合行，因此很自然地向其添加所有维度。因此，键表达式包含一长串列，并且必须使用新添加的维度频繁更新此列表。在这种情况下，在主键中只留下几列可以提供有效的范围扫描并将剩余的维列添加到排序键元组是有意义的。

在查询中使用索引和分区 对于SELECT查询，ClickHouse会判断是否可以使用索引：

对于在主键或者分区键的列，如果

1. WHERE / PREWHERE子句具有表示相等或不等式比较操作的表达式（作为连接元素）
2. 或者在列操作或表达式上具有固定前缀的IN或LIKE
3. 或某些部分重复的函数
4. 或存在逻辑关系的表达式。

因此，Clickhouse可以利用索引机制在主键的一个或多个范围上进行快速高效查询。

在下面的示例中，针对特定跟踪代码运行时查询会很快; 对于特定标签和日期范围; 对于特定的标签和日期; 对于具有日期范围的多个标签，依此类推。

示例：

让我们看看配置如下的引擎：

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

查询 SQL：

SELECT count() FROM table WHERE EventDate = toDate(now()) AND CounterID = 34
SELECT count() FROM table WHERE EventDate = toDate(now()) AND (CounterID = 34 OR CounterID = 42)
SELECT count() FROM table WHERE ((EventDate >= toDate('2014-01-01') AND EventDate <= toDate('2014-01-31')) OR EventDate = toDate('2014-05-01')) AND CounterID IN (101500, 731962, 160656) AND (CounterID = 101500 OR EventDate != toDate('2014-05-01'))

ClickHouse将使用主键索引来筛选合适数据，使用月分区键来确定在日期范围内的分区。

从上面的示例 SQL 可以看出，复杂表达式也是可以支持索引的，clickhouse 从表中扫描数据是有规则的，使用索引不会比全表 scan 慢

下面的查询 SQL 示例，将不能使用 SQL

SELECT count() FROM table WHERE CounterID = 34 OR URL LIKE '%upyachka%'

要在运行查询时检查ClickHouse是否可以使用索引，请使用设置force_index_by_date和force_primary_key。

按月分区的关键是只允许读取包含适当范围日期的数据块。 在这种情况下，数据块可能包含许多日期（最多整月）的数据。 在块中，数据按主键排序，主键可能不包含日期作为第一列。 因此，使用仅具有未指定主键前缀的日期条件的查询将导致读取的数据多于单个日期。

对于并发：

• 使用多版本控制方案，支持并发表访问。 换句话说，当同时读取和更新表时，从查询时当前的一组part读取数据。 没有冗长的锁。 插入不会妨碍读取操作。
• 从表中读取会自动并行化。

7. Distributed 引擎

1. 分布式引擎本身不存储数据，但允许从服务器上执行分布式查询
2. 被写⼊入，做转发
3. 查询，作为中间件， 聚合后返回给⽤用户

Clusters配置如下：

<remote_servers>
    <logs>
        <shard>
            <!-- Optional. Shard weight when writing data. Default: 1. -->
            <weight>1</weight>
            <!-- Optional. Whether to write data to just one of the replicas. Default: false (write data to all replicas). -->
            <internal_replication>false</internal_replication>
            <replica>
                <host>example01-01-1</host>
                <port>9000</port>
            </replica>
            <replica>
                <host>example01-01-2</host>
                <port>9000</port>
            </replica>
        </shard>
        <shard>
            <weight>2</weight>
            <internal_replication>false</internal_replication>
            <replica>
                <host>example01-02-1</host>
                <port>9000</port>
            </replica>
            <replica>
                <host>example01-02-2</host>
                <secure>1</secure>
                <port>9440</port>
            </replica>
        </shard>
    </logs>
</remote_servers>

8. Dictionary 引擎

Clickhouse 支持从其他数据源引入数据作为字典使用，字典完全存储在内存中，不同的存储方式支持 100W-1000W 左右的数据。

9. Merge

1. 不同于MergeTree引擎，也不属于MergeTree家族引擎
2. Merge engine 本身也不存储数据
3. 允许同时从任意数量的其他表读取数据，读取过程自动并行化。
4. 不支持写入表
5. 读取时，如果存在，则使用实际读取的表的索引。
6. 使用Merge引擎的典型场景是使用大量TinyLog表，就像使用单个表一样。

Merge引擎接受参数：数据库名称和表的正则表达式。

#hits库，以WatchLog 开头的表
Merge(hits, '^WatchLog')

通过正则选择要合并的表时，不会选择Merge表本身，这是为了避免无限循环。注意多个 Merge 表间，可能会出现无休止地尝试读取彼此的数据。

Merge表引擎示例：

CREATE TABLE WatchLog_old(date Date, UserId Int64, EventType String, Cnt UInt64) 
ENGINE=MergeTree(date, (UserId, EventType), 8192);
INSERT INTO WatchLog_old VALUES ('2018-01-01', 1, 'hit', 3);

CREATE TABLE WatchLog_new(date Date, UserId Int64, EventType String, Cnt UInt64) 
ENGINE=MergeTree PARTITION BY date ORDER BY (UserId, EventType) SETTINGS index_granularity=8192;
INSERT INTO WatchLog_new VALUES ('2018-01-02', 2, 'hit', 3);

CREATE TABLE WatchLog as WatchLog_old ENGINE=Merge(currentDatabase(), '^WatchLog');

SELECT *
FROM WatchLog

┌───────date─┬─UserId─┬─EventType─┬─Cnt─┐
│ 2018-01-01 │      1 │ hit       │   3 │
└────────────┴────────┴───────────┴─────┘
┌───────date─┬─UserId─┬─EventType─┬─Cnt─┐
│ 2018-01-02 │      2 │ hit       │   3 │
└────────────┴────────┴───────────┴─────┘

10. 其他特殊引擎

10.1 File

数据源是特定格式（TabSeparated, Native, etc.）的文件 The data source is a file that stores data in one of the supported input formats (TabSeparated, Native, etc.).

应用场景举例:

• 数据从 Clickhouse 导出到文件
• 数据格式转换
• 通过 export - 修改文件 - import 的方式来实现 Update

10.2 Null

写入 Null 表时，将忽略数据。 从 Null 表读取时，响应为空。 但是，您可以在 Null 表上创建实例化视图。 因此写入表中的数据将最终出现在视图中。

10.3 Set

始终在 RAM 中的数据集。 它适用于IN运算符的右侧（请参见“IN运算符”部分）。

可以使用 INSERT 在表中插入数据。 新元素将添加到数据集中，而重复项将被忽略。 但是你不能从表中执行 SELECT。 检索数据的唯一方法是在IN运算符的右半部分使用它。

数据始终位于 RAM 中。 对于 INSERT，插入数据块也会写入磁盘上的表目录。 启动服务器时，此数据将加载到 RAM。 换句话说，重新启动后，数据仍然存在。

对于非正常的服务器重启，磁盘上的数据块可能会丢失或损坏。 如果出现数据文件损坏，则需要手动删除包含损坏数据的文件。

10.4 Join

为 JOIN 操作 预处理的数据结构，始终位于 RAM 中。

Join(ANY|ALL, LEFT|INNER, k1[, k2, ...])

参数：

• ANY | ALL - 严格;
• LEFT | INNER - 类型。 这些参数设置不带引号，并且必须与表将用于的JOIN匹配。
• k1，k2，...是USING子句中将要进行连接的关键列。

该表不能用于GLOBAL JOIN。

您可以使用INSERT向表中添加数据，类似于Set引擎。 对于任何情况，将忽略重复键的数据。 对于ALL，它将被计算在内。 您无法直接从表中执行SELECT。 检索数据的唯一方法是将其用作JOIN的“右手”表。

在磁盘上存储数据与Set引擎相同。

10.5 URL

类似于 File 引擎。只不过数据源是通过访问 URL 地址获取

示例： 1.创建 url 表引擎：

CREATE TABLE url_engine_table (word String, value UInt64)
ENGINE=URL('http://127.0.0.1:12345/', CSV)

2.模拟http服务：

from http.server import BaseHTTPRequestHandler, HTTPServer

class CSVHTTPServer(BaseHTTPRequestHandler):
    def do_GET(self):
        self.send_response(200)
        self.send_header('Content-type', 'text/csv')
        self.end_headers()

        self.wfile.write(bytes('Hello,1\nWorld,2\n', "utf-8"))

if __name__ == "__main__":
    server_address = ('127.0.0.1', 12345)
    HTTPServer(server_address, CSVHTTPServer).serve_forever()

python3 server.py

3.查询：

SELECT * FROM url_engine_table

• 读写并行
• 不支持：
  • ALTER and SELECT...SAMPLE operations.
  • Indexes
  • Replication

10.6 View 视图

用于实现视图， 它不存储数据，仅存储指定的 SELECT 查询。 从表中读取时，它会运行此查询。

执行查询时等同于：

SELECT a, b, c FROM (SELECT ...)

10.7 MaterializedView：物化视图

相对于 View：

• View 视图是虚拟表，不存储数据
• MaterializedView 存储相应 Select 查询转换的数据

Select 查询转换可以包含 DISTINCT, GROUP BY, ORDER BY, LIMIT...

注意的是：相应的转化是在每次插入数据块上独立执行的，例如 Group By，只针对插入数据的单个数据包的数据聚合，数据不会进一步汇总。例外情况是使用独立执行数据聚合的 ENGINE，例如 SummingMergeTree。