高性价比OLAP列式数据库—Clickhouse（表引擎）

原创迷三张 2025-02-14

表引擎（即表的类型）是ClickHouse 的一大特色。可以说表引擎决定了如何存储表的数据。

介绍

表引擎都决定了哪些呢？

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

表引擎的使用方式就是必须显式在创建表时定义该表使用的引擎，以及引擎使用的相关参数。特别注意的式引擎的名称大小写敏感

表引擎

一、日志类型

TinyLog

最简单的表引擎，用于将数据存储在磁盘上。每列都存储在单独的压缩文件中。写入时，数据将附加到文件末尾。此引擎适用于相对较小的表（建议最多1,000,000行）

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

INSERT INTO t_tinylog_test (id, name) VALUES
('1', 'Alice'),
('2', 'Bob'),
('3', 'Charlie'),
('4', 'David'),
('5', 'Eve');


SELECT * FROM t_tinylog;

二、合并树家族

MergeTree

ClickHouse 中最强大的表引擎当属 MergeTree（合并树）引擎及该系列（*MergeTree）中的其他引擎，支持索引和分区，常用于设计用于插入极大量的数据到一张表当中。

create table t_order_mergetree(
 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);

insert into t_order_mergetree 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');

MergeTree 有很多参数，但这三个参数（partition by | primary | order by）更加重要的。

1、partition by 分区(可选)

不要使用客户端指定分区标识符或分区字段名称来对数据进行分区（而是将分区字段标识或名称作为 ORDER BY 表达式的第一列来指定分区）。分区的目的主要是降低扫描的范围，优化查询速度。尽量不要使用过细粒度的分区键，到月级别即可。

分区目录：MergeTree 是以列文件+索引文件+表定义文件组成的，但是如果设定了分区那么这些文件就会保存到不同的分区目录中。

分区字段支持类型：整型字段作为分区键；日期型字段作为分区键；其他类型作为分区键如 String，Float类型等，会通过128位的Hash算法取其值作为分区键

并行：分区后，面对涉及跨分区的查询统计，ClickHouse 会以分区为单位并行处理。

数据写入与分区合并：任何一个批次的数据写入都会产生一个临时分区，不会纳入任何一个已有的分区。写入后的某个时刻（大概 10-15 分钟后），ClickHouse 会自动执行合并操作（等不及也可以手动通过 optimize 执行），把临时分区的数据，合并到已有分区中。

optimize table xxxx final;

如下：再次插入相同数据

insert into t_order_mergetree 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');

数据没有合并分区。手动执行合并后请看，明显进行了数据分区合并：

2、primary key 主键(可选)

如果要选择与排序键不同的主键，在这里指定，可选项。默认情况下主键跟排序键（由 ORDER BY 子句指定）相同。因此，大部分情况下不需要再专门指定一个 PRIMARY KEY 子句。它只提供了数据的一级索引，但是却不是唯一约束。这就意味着是可以存在相同 primary key 的数据的。主键的设定主要依据是查询语句中的 where 条件。根据条件通过对主键进行某种形式的二分查找，能够定位到对应的 index granularity,避免了全表扫描。

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

稀疏索引：稀疏索引的好处就是可以用很少的索引数据，定位更多的数据，代价就是只能定位到索。引粒度的第一行，然后再进行进行一点扫描。

3、order by（必选）

可以是一组列的元组或任意的表达式。例如: ORDER BY (CounterID, EventDate) 。如果没有使用 PRIMARY KEY 显式指定的主键，ClickHouse 会使用排序键作为主键。如果不需要排序，可以使用 ORDER BY tuple()。order by 设定了分区内的数据按照哪些字段顺序进行有序保存。

必填：order by 是 MergeTree 中唯一一个必填项，甚至比 primary key 还重要，因为当用户不设置主键的情况，很多处理会依照 order by 的字段进行处理（比如后面会讲的去重和汇总）。

要求：主键必须是 order by 字段的前缀字段。比如 order by 字段是 (id,sku_id) 那么主键必须是 id 或者(id,sku_id)

4、二级索引

目前在 ClickHouse 的官网上二级索引的功能在 v20.1.2.4 之前是被标注为实验性的，在这个版本之后默认是开启的。

老版本使用二级索引前需要增加设置：是否允许使用实验性的二级索引（v20.1.2.4 开始，这个参数已被删除，默认开启）。

create table t_order_mergetreev2(
 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 是设定二级索引对于一级索引粒度的粒度。

insert into t_order_mergetreev2 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_mergetreev2 where total_amount > toDecimal32(900., 2)

对比效果：可以看出二级索引能够为非主键字段的查询发挥作用。

5、数据 TTL

TTL 即Time To Live，MergeTree 指定行存储的持续时间并定义数据片段在硬盘和卷上的移动逻辑的规则列表，提供了可以管理数据表或者列的生命周期的功能。

列级别 TTL：

create table t_order_mergetreev3 (
 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);

insert into t_order_mergetreev3 values
(106,'sku_001',1000.00,'2020-08-31 19:35:30'),
(107,'sku_002',2000.00,'2020-08-31 19:35:30'),
(110,'sku_003',600.00,'2020-08-31 19:35:00');

手动合并，查看效果到期后，指定的字段数据归 0

表级别 TTL：

这条语句是数据会在 create_time 之后 10 秒丢失

alter table t_order_mergetreev3 MODIFY TTL create_time + INTERVAL 10 SECOND;

表结构如果直接创建变成如下：

CREATE TABLE t_order_mergetreev3 (
    id UInt32,
    sku_id String,
    total_amount Decimal(16, 2),
    create_time Datetime,
    TTL create_time + INTERVAL 10 SECOND  -- 修改后的 TTL 策略
)
ENGINE = MergeTree
PARTITION BY toYYYYMMDD(create_time)
PRIMARY KEY (id)
ORDER BY (id, sku_id);

涉及判断的字段必须是 Date 或者 Datetime 类型，推荐使用分区的日期字段。能够使用的时间周期：

- SECOND
- MINUTE
- HOUR
- DAY
- WEEK
- MONTH
- QUARTER
- YEAR

ReplacingMergeTree

ReplacingMergeTree 是 MergeTree 的一个变种，它存储特性完全继承 MergeTree，只是多了一个去重的功能。尽管 MergeTree 可以设置主键，但是 primary key 其实没有唯一约束的功能。如果你想处理掉重复的数据，可以借助这个 ReplacingMergeTree。

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

去重范围：如果表经过了分区，去重只会在分区内部进行去重，不能执行跨分区的去重。所以ReplacingMergeTree 能力有限， ReplacingMergeTree 适用于在后台清除重复的数据以节省空间，但是它不保证没有重复的数据出现。

create table t_order_rmergetree(
 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_rmergetree 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');

1、第一次查询

2、手动合并，第二次查询

3、通过测试得到结论

实际上是使用 order by 字段作为唯一键
去重不能跨分区
只有同一批插入（新版本）或合并分区时才会进行去重
认定重复的数据保留，版本字段值最大的
如果版本字段相同则按插入顺序保留最后一笔

SummingMergeTree

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

create table t_order_smergetree(
 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_smergetree 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');

1、第一次查询

2、手动合并，第二次查询

3、通过测试得到结论

以 SummingMergeTree（）中指定的列作为汇总数据列可以填写多列必须数字列，如果不填，以所有非维度列且为数字列的字段为汇总数据列
以 order by 的列为准，作为维度列
其他的列按插入顺序保留第一行
不在一个分区的数据不会被聚合
只有在同一批次插入(新版本)或分片合并时才会进行聚合

三、外部集成

ClickHouse 提供了多种方式来与外部系统集成，包括表引擎。像所有其他的表引擎一样，使用CREATE TABLE或ALTER TABLE查询语句来完成配置。然后从用户的角度来看，配置的集成看起来像查询一个正常的表，但对它的查询是代理给外部系统的。这种透明的查询是这种方法相对于其他集成方法的主要优势之一，比如外部字典或表函数，它们需要在每次使用时使用自定义查询方法。以下是支持的集成方式：

ODBC
JDBC
MySQL
MongoDB
HDFS
S3
Kafka
EmbeddedRocksDB
RabbitMQ
PostgreSQL
SQLite
Hive

JDBC表引擎

创建MySQL表

mysql> CREATE TABLE `test`.`test` (
    ->   `int_id` INT NOT NULL AUTO_INCREMENT,
    ->   `int_nullable` INT NULL DEFAULT NULL,
    ->   `float` FLOAT NOT NULL,
    ->   `float_nullable` FLOAT NULL DEFAULT NULL,
    ->   PRIMARY KEY (`int_id`));
Query OK, 0 rows affected (0,09 sec) 


mysql> insert into test (`int_id`, `float`) VALUES (1,2);
Query OK, 1 row affected (0,00 sec)


mysql> select * from test;
+------+----------+-----+----------+
| int_id | int_nullable | float | float_nullable |
+------+----------+-----+----------+
|      1 |         NULL |     2 |           NULL |
+------+----------+-----+----------+
1 row in set (0,00 sec)

创建CK引擎表

CREATE TABLE jdbc_table
(
    `int_id` Int32,
    `int_nullable` Nullable(Int32),
    `float` Float32,
    `float_nullable` Nullable(Float32)
)
ENGINE JDBC('jdbc:mysql://localhost:3306/?user=root&password=root', 'test', 'test')

查询数据即可获取MySQL中表的内容

SELECT *
FROM jdbc_table

┌─int_id─┬─int_nullable─┬─float─┬─float_nullable─┐
│      1 │         ᴺᵁᴸᴸ │     2 │           ᴺᵁᴸᴸ │
└────────┴──────────────┴───────┴────────────────┘

其他类型集成引擎大家伙有兴趣可以自己研究尝试~~~~

四、特殊类型

Memory内存引擎

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

CREATE TABLE t_user_info (
    user_id String,
    username String,
    email String,
    age UInt8,
    registration_date Date
) ENGINE = Memory;

INSERT INTO t_user_info (user_id, username, email, age, registration_date) VALUES
('u1', 'alice', 'alice@example.com', 25, '2021-05-10'),
('u2', 'bob', 'bob@example.com', 30, '2022-07-15'),
('u3', 'charlie', 'charlie@example.com', 28, '2023-02-25'),
('u4', 'david', 'david@example.com', 35, '2020-12-01'),
('u5', 'eve', 'eve@example.com', 22, '2023-03-05'); 


SELECT * FROM t_user_info;

总结

当然这四类中有很多的表引擎，以上是着重介绍常用类型，想要了解探索更多可到ClickHouse官网~~~

https://clickhouse.com/docs/zh/engines/table-engines
clickhouse表引擎官方地址

欢迎微信扫描二维码，关注我的公众号~~

clickhouse olap数据库列式数据库

最后修改时间：2025-02-17 13:19:02

文章转载自迷三张，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

高性价比OLAP列式数据库—Clickhouse（表引擎）

TinyLog

评论