表引擎概述

ClickHouse 的表引擎是 ClickHouse 服务的核心，它们决定了 ClickHouse 如何存储数据，包括：

• 1.数据的存储方式和位置，写到哪里以及从哪里读取数据。
• 2.支持哪些查询操作以及如何支持。
• 3.数据的并发访问。
• 4.数据索引的使用(如果存在)。
• 5.是否可以支持多线程请求。
• 6.是否支持数据复制。
  表引擎的使用方式，就是必须显式在建表时定义该表使用的引擎，以及引擎使用的相关参数。
  注：引擎的名称是大小写敏感的。基本都是大驼峰形式。
  参考：

https://blog.csdn.net/weixin_43823423/article/details/126708069
https://blog.csdn.net/weixin_44758876/article/details/123686608

MergeTree

ck中最强大的表引擎是MergeTree(合并树)，及该家族(*MergeTree)中其他引擎，支持索引和分区，地位相当于innodb之于MySQL。
建表语法：

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

例如：

create table t_order_mt(
    id UInt32,
    sku_id String,
    total_amount Decimal(16,2),
    create_time Datetime
) engine =MergeTree
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id,sku_id)

注：primary key不会加唯一约束，只加索引。order by是必须的。

插入数据：

insert into t_order_mt values
(101,'sku_001',1000.00,'2020-06-01 12:00:00'),
(102,'sku_002',2000.00,'2020-06-01 11:00:00'),
(102,'sku_004',2500.00,'2020-06-01 12:00:00'),
(102,'sku_002',2000.00,'2020-06-01 13:00:00'),
(102,'sku_002',12000.00,'2020-06-01 13:00:00'),
(102,'sku_002',600.00,'2020-06-02 12:00:00');

然后查看数据：

select * from t_order_mt;

如果是在命令行工具中还能看到分区，而且排序是分区内排序。如果使用DBeaver等工具就看不到这种直观分区界面。

分区 partition by (可选)

• 作用：降低扫描范围，优化查询速度。官方建议以天来分区。
• 如果不填则只使用一个分区。
• 并行：分区后，对于涉及跨分区的查询统计，ClickHouse会以分区为单位并行处理。一个分区一个线程。
• 数据写入与分区合并：任何一个批次的数据写入都会产生一个临时分区，不会纳入任何一个已有的分区。写入后某个时刻（大约10~15分钟后），clickhouse会自动执行合并操作（等不及也可以手动通过optimize执行），把临时分区的数据，合并到已有分区中。

optimize table xxxxxx final;
optimize table xxxxxx partition '20200601' final;

• 分区目录：MergeTree是以列文件+索引文件+表定义文件组成的，但如果设定了分区，那么这些文件就会保存到不同的分区目录中。
  我们进入/var/lib/clickhouse/data/（数据目录中），进入数据库、表子目录后，可以看到分区目录。
  分区目录的数字代表的是：分区值最小分区块编号最大分区块编号_合并层级。具体规则见官网。
  随便进入一个分区目录

其中data.bin就是存数据的文件，
.mrk是一个标记文件，在索引文件和bin数据文件之间起到桥梁作用，以偏移量的形式保存，用于通过主索引定位颗粒的物理位置（老版本是每个列都有一组.bin和.mrk），
default_compression_codec.txt记录的是压缩格式，
count.txt记录的是分区中记录的条目数，
columns.txt记录的列的信息，
checksums.txt记录的是校验信息，用于校验各文件的正确性，存放各文件的size及hash值，不可读，
primary.idx是主键的索引文件，（ck的索引属于稀疏索引，类比redis的跳表），
另外minmax_create_time.idx是分区的索引，记录分区键的最大和最小值

主键 primary key (可选)
CK的主键和其他数据库不同，它只提供了数据的一级索引，但不是唯一约束。CK允许存在相同的primary key数据。

主键的设定主要依据是查询语句中where的条件。根据条件通过对主键进行某种二分查找，能够定位到对应的index granularity，避免了全表扫描。

index granularity翻译为索引粒度，指稀疏索引中两相邻索引数据对应的间隔。MergeTree默认是8192。官方不建议修改这个值，除非该列存在大量重复值，比如一个分区中几万行才有一个不同的数据。

order by (必选)
设定了分区内的数据按照哪些字段顺序进行有序保存。
order by是MergeTree中唯一一个必填项，比主键还重要。当不设置主键时，很多处理会按照order by字段进行处理。
要求：主键必须是order by字段的前缀字段。比如order by字段是(id,sku_id)，那主键必须是(id,sku_id)或id。
参考：

https://zhuanlan.zhihu.com/p/571660759
https://zhuanlan.zhihu.com/p/588458902

二级索引(跳数索引)
此功能在20.1.2.4版本之前是实验性的，之后的版本默认才开启。
老版本需要手动开启：

set allow_experimental_data_skipping_indices=1;

从20.1.2.4版本开始，这个参数已删除，默认开启。
每种跳数索引都有一个 GRANULARITY 参数，表示每隔 GRANULARITY 个索引粒度 (index_granularity)才会生成一次跳数索引。
以minmax为例，创建测试表：

create table t_order_mt2(
    id UInt32,
    sku_id String,
    total_amount Decimal(16,2),
    create_time Datetime
    INDEX a total_amount TYPE minmax GRANULARITY 5
) engine =MergeTree
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id,sku_id)

其中GRANULARITY N表示每隔 N * index_granularity(默认8192) 的区间就会记录一次 total_amount 的最大最小值。
官网文档：

https://clickhouse.com/docs/zh/guides/improving-query-performance/skipping-indexes

数据TTL
TTL即Time to live，MergeTree提供了可以管理数据表或者列的生命周期的功能。

1.列级别TTL
创建测试表：

create table t_order_mt3(
    id UInt32,
    sku_id String,
    total_amount Decimal(16,2) TTL create_time+interval 10 SECOND ,
    create_time Datetime
) engine =MergeTree
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id,sku_id)

要求：TTL字段不能基于主键，字段必须是日期类型。
插入数据（根据实际时间改变）

select now();

insert into t_order_mt3 values
(106,'sku_001',1000.00,'2020-06-012 22:52:30'),
(107,'sku_002',2000.00,'2020-06-012 22:52:30'),
(110,'sku_003',600.00,'2020-06-013 12:00:00');

手动合并，查看效果

optimize table t_order_mt3  final;

到期后，指定的字段数据归0

2.表级别TTL
数据会在create_time之后10秒丢失

alter table t_order_mt3 modify TTL create_time + INTERVAL 10 SECOND;

涉及判断的字段必须是Date或Datetime类型，推荐使用分区的日期字段。

能够使用的时间周期：
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR

ReplacingMergeTree

ReplacingMergeTree是MergeTree的一个变种，其存储特性完全继承MergeTree，只是多了定期去重功能（根据order by 字段）。

去重时机
数据的去重只会在同一批插入，或合并的过程中出现。合并会在未知的时间在后台进行，所以无法预先做出计划。有一些数据可能仍未被处理。

去重范围
不能跨分区去重，只是分区内去重。
所以ReplacingMergeTree能力有限，适用于在后台清除重复数据以节省空间，但不保证没有重复数据出现。

例：
建表：

create table t_order_rmt(
    id UInt32,
    sku_id String,
    total_amount Decimal(16,2) ,
    create_time Datetime
) engine =ReplacingMergeTree(create_time)
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id,sku_id)

ReplacingMergeTree()填入的参数为版本字段，重复数据保留版本字段值最大的。如果不填版本字段，默认按插入顺序保留最后一条。

插入数据：

insert into t_order_rmt values
(101,'sku_001',1000.00,'2020-06-01 12:00:00'),
(102,'sku_002',2000.00,'2020-06-01 11:00:00'),
(102,'sku_004',2500.00,'2020-06-01 12:00:00'),
(102,'sku_002',2000.00,'2020-06-01 13:00:00'),
(102,'sku_002',12000.00,'2020-06-01 13:00:00'),
(102,'sku_002',600.00,'2020-06-02 12:00:00');

SummingMergeTree

对于不查询明细，只关心以维度进行汇总聚合结果的场景。如果只使用普通的MergeTree的话，无论是存储空间的开销，还是查询时临时聚合的开销都比较大。
ClickHouse为了这种场景，提供了一种能够预聚合的引擎SummingMergeTree。
聚合只在分区内聚合，聚合时机是分片合并时。

例：
建表：

create table t_order_smt(
    id UInt32,
    sku_id String,
    total_amount Decimal(16,2) ,
    create_time Datetime
) engine =SummingMergeTree(total_amount)
partition by toYYYYMMDD(create_time)
primary key (id)
order by (id,sku_id)

插入数据：

insert into t_order_smt values
(101,'sku_001',1000.00,'2020-06-01 12:00:00'),
(102,'sku_002',2000.00,'2020-06-01 11:00:00'),
(102,'sku_004',2500.00,'2020-06-01 12:00:00'),
(102,'sku_002',2000.00,'2020-06-01 13:00:00'),
(102,'sku_002',12000.00,'2020-06-01 13:00:00'),
(102,'sku_002',600.00,'2020-06-02 12:00:00');

聚合后，其他字段取的是最早的一条。

optimize table t_order_smt  final;

设计聚合表的话，唯一键值、流水号可以去掉，所有字段全审维度、度量或者时间戳。
但是SQL仍然要写sum，因为可能会包含一些还没来得及聚合的临时明细。

select sum(total_amount) from xxx where name = '' and create_date = '';

TinyLog

TinyLog属于Log家族表引擎。
以列文件形式保存在磁盘上，不支持索引，没有并发控制，一般保存数据量较小的小表。生产环境作用有限，一般用于练习和测试。
如：

create table t_tinylog(id String, name String) engine = TinyLog;

Memory

内存引擎，数据以未压缩的原始形式直接保存在内存中，服务器重启数据就会消失。读写操作不会相互阻塞，不支持索引。简单查询下有非常非常搞得性能表现(>10G/s)。一般用到它的地方不多，除了用来测试，就是在需要非常高的性能，同时数据量又不太大(上限大概1亿行)的场景。