四、ClickHouse表引擎详解

ClickHouse引擎

ClickHouse引擎

4.2 ClickHouse数据库引擎

ClickHouse数据库引擎

4.3 ClickHouse表引擎

4.3.1 MergreTree系列

该引擎是官方主推的存储引擎，有主键索引、数据分区、数据副本、数据采样、删除和修改等功能，ReplacingMergeTree有了去重功能，SummingMergeTree有了汇总求和功能，AggregatingMergeTree有聚合功能，CollapsingMergeTree有折叠删除功能，VersionedCollapsingMergeTree有版本折叠功能，GraphiteMergeTree有压缩汇总功能。在这些的基础上还可以叠加Replicated和Distributed。

MergreTree系列

ReplacingMergeTree

1、使用order by排序键作为判断重复数据的唯一键；
2、只有在合并分区的时候才会触发删除重复数据的逻辑；
3、以数据分区为单位删除数据。当分区合并时，同一分区内的重复数据会被删除，不同分区之间的重复数据不会被删除；
4、在进行数据去重时，因为分区内的数据已经基于 order by进行了排序，所以能够找到那些相邻的重复数据。
数据去重策略有两种：

如果没有设置版本号，则保留同一组重复数据中的最后一行。
如果设置了版本号，则保留同一组重复数据中ver字段取值最大的那一行

SummingMergeTree

1、用order by排序键作为聚合数据的条件key
2、只有在合并分区的时候才会触发聚合计算的逻辑
3、以数据分区为单位来聚合数据。当分区合并时，同一数据分区内聚合key相同的数据会被合并汇总，而不同分区之间的数据则不会被汇总。
4、如果在定义引擎时指定了columns汇总列（非主键的数值类型字段），则sum汇总这些列字段，如果未指定，则聚合所有非主键数值字段。
5、在进行数据汇总时，因为分区内的数据已经基于order by排序，所以能够找到相邻且拥有相同聚合key的数据。
6、在汇总数据时，同一分区内相同聚合key的多行数据会合并成一行。其中汇总字段会进行sum计算；对于那些非汇总字段，则会使用第一行数据的取值。
7、支持嵌套结构，但字段名称必须以map后缀结尾。嵌套类型中，默认以第一个字段为聚合key。除第一个字段意外，任何名称以key，id或type为后缀结尾的字段，都将和第一个字段组合成复合key。

AggregatingMergeTree

1、用order by排序键作为聚合数据的条件key
2、使用AggregateFunction字段类型定义聚合函数的类型以及聚合字段。
3、只有在合并分区的时候才会触发聚合计算的逻辑
4、以数据分区为单位来聚合数据，当分区合并时，同一分区内聚合key相同的数据会被合并计算，而不同分区之间的数据则不会被计算。
5、在进行数据计算时，因为分区内的数据已经基于order by排序，所以能够找到相邻且拥有相同聚合key的数据。
6、在聚合数据时，同一分区内相同聚合key的多行数据会合并成一行。对于非主键、非AggregateFunction字段，则会使用第一行数据的取值
7、AggregateFunction字段使用二进制存储，写入数据时，需要调用State函数；而在查询数据时，则需要调用相应的Merge函数。其中，*表示定义时使用的聚合函数
8、AggregateMergeTree通常作为物化视图的表引擎，与普通MergeTree搭配使用。

CollapsingMergeTree（VersionedCollapsingMergeTree）

CollapsingMergeTree（VersionedCollapsingMergeTree） 会异步的删除（折叠）这些除了特定列 Sign 有 1 和 -1 的值以外，其余所有字段的值都相等的成对的行。没有成对的行会被保留。该引擎可以显著的降低存储量并提高 SELECT 查询效率。

GraphiteMergeTree
  该引擎用来对 Graphite数据进行瘦身及汇总。

4.3.2 MergreTree建表

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()
[PARTITION BY expr]
[ORDER BY expr]
[PRIMARY KEY expr]
[SAMPLE BY expr]
[TTL expr [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'], ...]
[SETTINGS name=value, ...]

1、PARTITIONBY：分区键。指定表数据以何种标准进行分区。分区键既可以是单个列字段，也可以通过元组的形式使用多个列字段，同时它也支持使用列表达

式。

2、ORDERBY：排序键，用于指定在一个数据片段内，数据以何种标准排序。默认情况下主键(PRIMARYKEY)与排序键相同。

3、PRIMARYKEY：主键。声明后会依照主键字段生成一级索引。默认情况下，主键与排序键(ORDERBY)相同，所以通常直接使用ORDERBY代为指定主键。

4、SETTINGS：index_granularity选项表示索引的粒度，默认值为8192。MergeTree索引在默认情况下，每间隔8192行数据才生成一条索引。

5、SAMPLEBY：抽样表达式，用于声明数据以何种标准进行采样。
6、SETTINGS：index_granularity默认是8192。index_granularity_bytes:默认10M，需要通过enable_mixed_granularity_parts来开启。

4.3.3 MergreTree分区目录结构

 1. 创建数据库
> CREATE DATABASE IF NOT EXISTS database_dict;
2. 创建表
 CREATE TABLE c_user
(
    id UInt64, 
    sex UInt64, 
    user_name String,
    idcard String,
    create_time DateTime
)

 ENGINE=MergeTree() 
PARTITION BY toYYYYMM ( create_time ) 
ORDER BY id  
SETTINGS index_granularity = 8192;

C_USER表目录

目录文件解释：

1. 分区目录：202106_1_5_1_4，一个分区可能会有多个不同的目录，该目录下存储该分区的数据及其他各种形式的数据。后台会执行合并，把相同分区的多个目录合并到一个分区。

2. checksums.txt：校验文件。使用二进制格式存储。它保存了余下各类文件(primary.idx、count.txt等)的size大小及size 验文件的完整性和正确性。

   checksums.txt

3. columns.txt：列信息文件，使用明文格式存储。用于保存此数据分区下的列字段信息。

   columns.txt

4. count.txt：计数文件，使用明文格式存储。用于记录当前数据分区目录下数据的总行数

   count.txt

5. primary.idx：一级索引文件，主键索引文件

6. xxx.bin：数据文件，使用压缩格式存储，默认为LZ4压缩格式，用于存储某一列的数据，每一列都对应一个该文件，如列date为date.bin

7. xxx.mrk2：列字段标记文件，如果使用了自适应大小的索引间隔，则标记文件以.mrk2命名，否则以.mrk命名。它建立primary.idx稀疏索引与xxx.bin数据文件之间的映射关系，先通过主键索引找到数据的偏移量，然后去xxx.bin数据文件中找到真实数据

8. ...还有二级索引和分区键相关信息文件等等

关于一级索引：
MergeTree 的主键使用 PRIMARY KEY 定义，待主键定义之后，MergeTree 会依据 index_granularity 间隔（默认 8192 行），为数据表生成一级索引并保存至 primary.idx 文件内。一级索引是稀疏索引，意思就是说：每一段数据生成一条索引记录，而不是每一条数据都生成索引，如果是每一条数据都生成索引，则是稠密索引。稀疏索引的好处，就是少量的索引标记，就能记录大量的数据区间位置信息，比如不到 24414 条标记信息，就能为 2E 条数据提供索引（算法：200000000 / 8192）。在 ClickHouse 中，一级索引常驻内存。总的来说：一级索引和标记文件一一对齐，两个索引标记之间的数据，就是一个数据区间，在数据文件中，这个数据区间的所有数据，生成一个压缩数据块。

需要注意的是：ClickHouse 的主键索引与 MySQL 等数据库不同，它并不用于去重，即便 primary key 相同的行，也可以同时存在于数据库中。要想实现去重效果，需要结合具体的表引擎 ReplacingMergeTree、CollapsingMergeTree、VersionedCollapsingMergeTree 实现。

关于二级索引：
又称之为跳数索引。目的和一级索引一样，是为了减少待搜寻的数据的范围。跳数索引的默认是关闭的，需要通过SET allow_experimental_data_skipping_indices=1来开启，索引生成粒度由 granularity 控制，如果生成了二级索引，则会在分区目录下生成额外的：skp_idx_[Column].idx 与 skp_idx_[Column].mrk 文件。跳数索引的生成规则：按照特定规则每隔 granularity 个 index_granularity 条数据，就会生成一条跳数索引。比如 minmax 跳数索引，生成的是：granularity 个 index_granularity 条数据内的最大值最小值生成一条索引，如果将来需要针对构建二级索引的这个字段求最大值最小值，则可以帮助提高效率。跳数索引一共支持四种类型：minmax（最大最小）、set（去重集合）、ngrambf_v1（ngram分词布隆索引）和 tokenbf_v1（标点符号分词布隆索引），一张数据表支持同时声明多个跳数索引。比如：

GRANULARITY=你在创建二级索引索引的指定的

INDEX_GRANULARITY=8192

GRANULARITY*INDEX_GRANULARITY

`

CREATE TABLE skip_test (
    ID String,
    URL String,
    Code String,
    EventTime Date,
    INDEX a ID TYPE minmax GRANULARITY 5,
    INDEX b (length(ID) * 8) TYPE set(2) GRANULARITY 5,
    INDEX c (ID,Code) TYPE ngrambf_v1(3,256,2,0) GRANULARITY 5,
    INDEX d ID TYPE tokenbf_v1(256,2,0) GRANULARITY 5
) ENGINE = MergeTree()
ORDER BY ID ;

（1）minmax:minmax索引记录了一段数据内的最小和最大极值，其索引的作用类似分区目录的minmax索引，能够快速跳过无用的数据区间，示例如下所示：
INDEX a ID TYPE minmax GRANULARITY 5
上述示例中minmax索引会记录这段数据区间内ID字段的极值。极值的计算涉及每5个index_granularity区间中的数据。

（2）set:set索引直接记录了声明字段或表达式的取值（唯一值，无重复），其完整形式为set(max_rows)，其中max_rows是一个阈值，表示在一个index_granularity内，索引最多记录的数据行数。如果max_rows=0，则表示无限制，例如：
INDEX b (length(ID) * 8) TYPE set(2) GRANULARITY 5
上述示例中set索引会记录数据中ID的长度 * 8后的取值。其中，每个index_granularity内最多记录100条。

（3）ngrambf_v1:ngrambf_v1索引记录的是数据短语的布隆表过滤器，只支持String和FixedString数据类型。ngrambf_v1只能够提升in、notIn、like、equals和notEquals查询的性能，其完整形式为ngrambf_v1(n,size_of_bloom_filter_in_bytes, number_of_hash_functions, random_seed)。这些参数是一个布隆过滤器的标准输入，如果你接触过布隆过滤器，应该会对此十分熟悉。它们具体的含义如下：
❑ n:token长度，依据n的长度将数据切割为token短语。
❑ size_of_bloom_filter_in_bytes：布隆过滤器的大小。
❑ number_of_hash_functions：布隆过滤器中使用Hash函数的个数。❑ random_seed: Hash函数的随机种子。

例如在下面的例子中，ngrambf_v1索引会依照3的粒度将数据切割成短语token，token会经过2个Hash函数映射后再被写入，布隆过滤器大小为256字节。
INDEX c (ID,Code) TYPE ngrambf_v1(3,256,2,0) GRANULARITY 5,
（4）tokenbf_v1:tokenbf_v1索引是ngrambf_v1的变种，同样也是一种布隆过滤器索引。tokenbf_v1除了短语token的处理方法外，其他与ngrambf_v1是完全一样的。tokenbf_v1会自动按照非字符的、数字的字符串分割token，具体用法如下所示：
INDEX d ID TYPE tokenbf_v1(256,2,0) GRANULARITY 5