clickHouse第四篇：MergeTree引擎族

clickHouse中最强大的表引擎当属MergeTree引擎以及该系列中的其他引擎。

MergeTree系列的引擎被设计用于插入极大量的数据到一张表中。数据以数据片段的形式一个接一个的快速写入，数据片段在后台按照一定的规则进行合并。相比在插入时不断修改(重写)已存储的数据，这种策略的效率会高很多。

主要特点：

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

如果指定了分区键的话，可以使用分区。在相同数据集和相同结果集的情况下，clickHouse中某些带分区的操作会比普通操作更快。查询中指定了分区键的话，clickHouse会自动截取分区数据，这有效地增加了查询性能。

支持数据副本。ReplicatedMergeTree引擎系列的表提供了数据副本功能。

支持数据采样。需要的话，可以给表设置一个采样方法。

ddl语法

建表语句：

create table [if not exists] [db_name.]table_name [on cluster cluster_name] (

  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]

[primary key expr]

[order by expr]

[sample by expr]

[ttl expr [delete|to disk 'xxx'|to volume 'xxx'], ...]

[settings name=value, ...]

创建示例：

第一个建表语句：

create table if not exists test (id int, create_time date, update_time date) engine = MergeTree order by update_time;

int类型会被自动解析成Int32类型。

第二个建表语句：

create table if not exists test2 (id int not null comment 'id', create_time date not null comment '创建时间', update_time date) engine = MergeTree order by update_time;

默认情况下，表仅在当前服务器上创建(创建连接时指定ip的那个服务器)。如果用了on cluster cluster_name，则会在cluster_name集群中的每个节点上都创建这个表。

可以创建一个与现有表相同结构的表，可以指定不同的引擎，也可以不指定，从而和现有表的引擎一样。语法是：create table [if not exists] [db.]table_name as [db2.]table_name2 [engine = xxx]

如参考default库的test表，在like_db库中新建一个like_test表，语句是create table if not exists like_db.like_test as default.test;

如果想指定引擎是TinyLog，则语句是create table if not exists like_db.like_test as default.test engine = TinyLog; 

可以利用表函数创建一个表。表函数是一些特殊的函数，到表函数时再细讲这里。

可以创建一个表，且表内容被select其他表的结果初始化。语法是create table [if not exists] [db.]table_name [(name1 [type1], name2 [type2], ...)] engine = engine_name as select ...

如create table example_result (id UInt8, country_code FixedString(2), action_day Date) engine = MergeTree primary key id as select id, country_code,action_day from example;

null或not null修饰符，用法和mysql一样。

默认值

可以通过以下任意一种方式指定默认值表达式：default expr，materialized expr，alias expr。

如create table if not exists test3 (id int default 0, create_time date default now(), update_time date default now()) engine = MergeTree order by update_time;

materialized和alias使用以后再研究。

主键

在create table时，在指定engine后指定primary key

如create table if not exists test2 (id int, name String, create_time date default now(), update_time date default now(), primary key id)engine = MergeTree;

注意，String，要区分大小写，不然会报Unknown data type family。MergeTree， 也要区分大小写，不然会报Unknown table engine mergeTree。

约束

在列描述后用constraint关键字和check关键字指定，语法是constraint constraint_name check bool_expr，bool_expr是一个返回值是bool类型的表达式

如create table if not exists test2 (id int, name String, create_time date default now(), update_time date default now(), constraint cs1 check id > 0)engine = MergeTree primary key id;

在插入数据时，如果不满足约束，则会插入失败。

ttl表达式

ttl用于设置生命周期，可以为整张表设置，也可以为单个字段设置，但仅能在MergeTree系列的引擎表中使用。表级别的ttl还会指定数据在磁盘和卷上自动转移的逻辑。ttl表达式返回值必须是date或datetime类型。晚点再研究。

列压缩编解码器

默认情况下，clickHouse采用LZ4压缩。可以在create table时给每个列指定压缩方法，如

create table codec_example (dt date codec(ZSTD), ts datetime codec(LZ4HC), float_value Float32 codec(NONE), double_value Float64 codec(LZ4HC(9)), value Float32 codec(Delta, ZSTD)) engine=MergeTree order by dt;

编解码器名区分大小写。类型名Float32、Float64也区分大小写。

通用编解码器、专业编解码器、加密编解码器，以后再研究。

临时表

clickHouse支持具有以下特征的临时表：

会话结束或者连接断开时，临时表消失。

临时表使用Memory引擎。

创建临时表不能指定on cluster。

如果一个临时表与另一个表同名，并且在查询时没哟指定数据库名，则将查询临时表。

对于分布式查询处理，查询中使用的临时表被传递到远程服务器。

创建临时表语法：在table前使用temporary关键字

如create temporary table if not exists temporary_example (dt date codec(LZ4), ts datetime codec(LZ4HC)) engine = Memory  order by dt;

创建完之后，在同一个会话中用show tables命令看不到这个表。为什么？？？

在大多数情况下，临时表不是手动创建的，而是在使用外部数据查询时或分布式in时创建。有些情况下，可以使用Memory引擎表来代替临时表。

排序

order by xxx。不支持asc、desc关键字，如果要用到多个键的话，需要用括号括住，如order by (id, update_time)。

如果没有显式用primary key指定主键的话，则会用order by的表达式作为主键。如果不需要排序的话，则需要用order by tuple()语句。

分区

partition by xxx。要按月分区的话，可以使用表达式toYYYYMM(date_column)，date_column是一个date类型的列，分区名的格式会是"YYYYMM"。

主键

primary key xxx。如果要指定多个字段的话，需要用括号括住。默认情况下主键跟排序键（由 ORDER BY 子句指定）相同，因此，大部分情况下不需要再专门指定primary key。如果没有指定order by，而是指定了primary key，那么order by会跟primary key一样。

如果要显式指定的话，主键必须是排序键的前缀，否则会报错Primary key must be a prefix of the sorting key。

采样

sample by xxx。采样列必须是无符号整数，否则会报Must be one unsigned integer type。

如果要用抽样表达式的话，主键中必须有这个表达式，否则会报Sampling expression must be present in the primary key。

如 create table if not exists test2 (id UInt32 not null comment 'id', create_time date not null comment '创建时间', update_time date) engine = MergeTree primary key (update_time, id) sample by id;

settings：

控制MergeTree引擎族表的额外参数，可选项：

index_granularity：索引粒度，索引中相邻标记间的数据行数，默认为8192。

index_granularity_bytes ：索引粒度，以字节为单位，默认是10M。

数据存储

表由按主键排序的数据片段组成。当数据被插入到表中时，会创建多个数据片段，每个数据片段都是按照主键的字典序排序。例如，主键是(CounterID, Date)，则片段中的数据先按CounterID排序，CounterID相同时再按Date排序。

不同分区的数据会被分成不同的片段，clickHouse在后台合并数据片段(数据片段数会变少)以高效存储。不同分区的数据片段不会合并，合并机制并不保证具有相同主键的行全部合并到同一个数据片段中。

数据片段可以以Wide或Compact格式存储。在Wide格式下，每一列都会在文件系统中存为一个单独的文件。在Compact格式下，所有列都存储在同一个文件中，Compact 格式可以提高插入量少且插入频繁时的性能。

数据存储格式由min_bytes_for_wide_part和min_rows_for_wide_part，这两个表引擎参数决定。当数据片段中的字节数或行数少于相应的设置值时，数据片段会以Compact格式存储，否则会以Wide格式存储。

每个数据片段被逻辑的分割成颗粒，颗粒是clickHouse中进行数据查询时的最小不可分割单元，每个颗粒包含一行或多行。每个颗粒的第一行通过该行的主键值进行标记。clickHouse会为每个数据片段都创建一个索引文件来存储这些标记。For each column, whether it’s in the primary key or not, ClickHouse also stores the same marks. These marks let you find data directly in column files.

颗粒的大小由表引擎参数index_granularity和index_granularity_bytes控制。颗粒的行数在[1, index_granularity]范围中，这取决于行的大小，如果单行的大小超过了index_granularity_bytes设置的值，那么一个颗粒的大小会超过index_granularity_bytes，这种情况下，颗粒的大小等于该行的大小。

主键和索引在查询中的表现

可以使用order by tuple()语法创建没有主键的表。在这种情况下，clickHouse根据数据插入的顺序存储。

选择与排序键不同的主键

clickHouse可以做到指定一个跟排序键不一样的主键，此时排序键用于在数据片段中进行排序，主键用于索引文件中进行标记的写入。这种情况下，主键表达式必须是排序表达式的前缀。

当使用SummingMergeTree和AggregatingMergeTree引擎时，这个特性非常有用。通常在使用这类引擎时，表里的列分为两种：维度和指标。典型的查询会通过任意的group by对指标列进行聚合并通过维度列进行过滤？？？由于SummingMergeTree和AggregatingMergeTree会对排序键相同的行进行聚合，所以把所有的维度放进排序键是很自然的做法，但这将导致排序键中包含大量的列，并且排序键会伴随着新添加的维度不断更新。在这种情况下合理的做法是，只保留少量的列在主键当中用于提升扫描效率，将维度列添加到排序键中。对排序键进行alter是轻量级的操作，因为当一个新列同时被加入到表里和排序键里时，已存在的数据片段不需要修改。由于旧的排序键是新排序键的前缀，并且新添加的列中没有数据，因此在表修改时数据对于新旧的排序键来说都是有序的。

索引和分区在查询中的应用

对于select查询，如果where子句具有下面这些表达式，则可以使用索引：进行相等/不相等的比较；对主键列或分区列进行in运算、有固定前缀的like运算、函数运算(部分函数适用)，还有对上述表达式进行逻辑运算。

因此，在索引键的一个或多个区间上快速地执行查询是可能的。下面例子中，指定标签、指定标签和日期范围、指定标签和日期、指定多个标签和日期范围，都会非常快。

default库的hit_v2，建表语句如下

CREATE TABLE default.hits_v2
(
    `WatchID` UInt64,
    `JavaEnable` UInt8,
    `url` String,
    `GoodEvent` Int16,
    `EventTime` DateTime,
    `counterId` Int32,
    `eventDate` DateTime DEFAULT now()
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(eventDate)
ORDER BY (counterId, eventDate)

这些查询

select count(1) from hits_v2 where eventDate = toDate(now()) and counterId = 34;

select count(1) from hits_v2 where eventDate = toDate(now()) and (counterId = 34 or counterId = 42);

clickHouse会依据主键索引剪掉不符合的数据，依据按月分区的分区键剪掉那些不包含符合数据的分区。

下面这个例子中，不会使用索引

select count(1) from hits_v2 where counterId = 34 or url like '%up%';

要检查clickHouse执行一个查询时能否使用索引，可设置force_index_by_date和force_primary_key。在哪里设置？？？

使用按月分区的分区列允许只读取包含适当日期区间的数据块，这种情况下，数据块会包含很多天的数据。在块中，数据按主键排序，主键第一列可能不包含日期。因此，仅使用日期而没有用主键字段作为条件的查询将会导致需要读取超过这个指定日期以外的数据。

AggregatingMergeTree

该引擎继承自MergeTree，对数据片段的合并逻辑做了改变。clickHouse会将同一个数据片段内所有具有相同排序键的行替换成一行，这一行会存储一系列聚合函数的状态。

可以使用AggregatingMergeTree表来做增量数据的聚合统计，包括物化视图的数据聚合。AggregatingMergeTree适用于能够按照一定规则缩减行数的情况。

SummingMergeTree