ClickHouse表引擎

2022-02-17

字数统计: 2.1k字 | 阅读时长≈ 9分

表引擎的使用

TinyLog

以列文件的形式保存在磁盘上，不支持索引，没有并发控制。一般保存少量数据的小表，生产环境上作用有限。可以用于平时练习测试用。如：

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

Memory

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

1	数据保存在内存中,但是内存数据不稳定,一旦服务器重启,数据消失.除非你对查询的性能有非常高的要求.但是这种情况很少.

MergeTree(最强大的表引擎)

ClickHouse中最强大的表引擎当属MergeTree（合并树）引擎及该系列（MergeTree）中的其他引擎，支持索引和分区，地位可以相当于innodb之于Mysql。而且基于MergeTree，还衍生除了很多小弟，也是非常有特色的引擎。

建表语句

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);

Partition: 分区,假如我Datetime设置的是2020-12-12 12:12 但是我的分区是按照2020-12-12来分区的转换成年月日的形式.
primary key: 主键,以前的主键是数据看看表中字段的唯一标记. 现在在clickhouse中主键没有唯一标记.它主要做的就是提升我们的查询效率.他其实就是为了建我们的查询索引的.
order by: 排序,在clickhouse中,order by不仅仅是排序,去重等也是依靠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');

MergeTree其实还有很多参数(绝大多数用默认值即可)，但是三个参数是更加重要的，也涉及了关于MergeTree的很多概念。

partition by 分区（可选项,不填的话数据在一个分区）

1	optimize table xxxx final;

Ø 例如

再次执行上面的插入操作

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');

查看数据并没有纳入任何分区

看到分成了四块,按照create_time分区.

手动optimize之后

1	hadoop202 :) optimize table t_order_mt final;

再次查询

1	插入数据不会立马合并,要不然就是你插入的数据量足够大,要不然就是你插入的数据时间足够长,要不然就是你手动合并.

primary key主键(可选)

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

order by（必选,作用:分区内排序）

二级索引

目前在ClickHouse的官网上二级索引的功能是被标注为实验性的。即不稳定,还有很大的发展空间.

(1)使用二级索引前需要增加设置

是否允许使用实验性的二级索引,开启二级索引:

1	set allow_experimental_data_skipping_indices=1;

(2)创建测试表

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

(3)插入数据

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

(4)对比效果

那么在使用下面语句进行测试，可以看出二级索引能够为非主键字段的查询发挥作用。

1	[shangbaishuyao@hadoop202 lib]$ clickhouse-client --send_logs_level=trace <<< 'select * from t_order_mt2 where total_amount > toDecimal32(900., 2)';

数据TTL

(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);

插入数据（注意：根据实际时间改变）

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

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

其实这个TTL这个手动合并，其实不是帮你解决业务上的合并的。 其实他是从本身优化的角度来考虑的。 比如： 加入我有些数据，数据量比较大， 但是这些数据我执行完成后就不需要了， 那我可以设置一个失效时间。 在长时间后数据失效。 设置完失效时间后，虽然不能到了指定时间后，马上将这个字段重置为0. 但是他会过一段时间后，后台看到这个标记，后台会自动帮你进行合并。这样就相当于把空间释放掉了。这是Clickhouse自己做的一个优化。

(2)表级TTL（指定整个表的失效时间）

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

1	alter table t_order_mt3 MODIFY TTL create_time + INTERVAL 10 SECOND;

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

SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR

ReplacingMergeTree

去重不是业务上的去重,而是Clickhouse本身为了去优化做的去重.所以说,clickhouse是可以去重,但是什么时间去重,我们不确定. 所以说你给Clickhouse的数据应该是在你在前台已经去重的数据这更合适

案例演示

创建表

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);

向表中插入数据

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');

执行第一次查询

1	hadoop202 :) select * from t_order_rmt;

手动合并

1	OPTIMIZE TABLE t_order_rmt FINAL;

再执行一次查询

1	hadoop202 :) select * from t_order_rmt;

SummingMergeTree(提供预聚合的作用)

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