ClickHouse中select final和optimize table final的区别

使用 OPTIMIZE TABLE FINAL 该语句会对表的数据部分进行计划外的合并，通常不建议使用。见官档：传送门

而在select中当 FINAL 被指定，ClickHouse会在返回结果之前完全合并数据，从而执行给定表引擎合并期间发生的所有数据转换。见官档：传送门

其中，需要特别注意的是：

1.optimize table final会立刻对表数据进行物理合并；select final只针对此次查询生效，并不会对实际物理分区进行合并。

2.optimize table final只会对同个分片同个分区数据进行物理合并；select final则可以对同个分片不同分区的数据也会进行合并，但是不同分片即使满足合并条件也无法合并

以下针对上述两个点进行实验。

1.optimize table final会立刻对表数据进行物理合并；select final只针对此次查询生效，并不会对实际物理分区进行合并。

创建单点表并插入测试数据，语句如下：防偷防爬

CREATE TABLE zkm
(
    `id` String DEFAULT 'NULL' COMMENT '用户编号',
    `repo` String DEFAULT 'NULL' COMMENT '仓库编号',
    `CREATE_TIME` DateTime DEFAULT '1971-01-01' COMMENT '数据建立时间'
)
ENGINE = ReplacingMergeTree()
PARTITION BY toYYYYMMDD(CREATE_TIME) 
ORDER BY id SETTINGS old_parts_lifetime = 0; 


insert into zkm settings insert_deduplicate=0 values ('id1','repo1','2023-12-11 11:30:11') ; 
insert into zkm settings insert_deduplicate=0 values ('id1','repo1','2023-12-11 12:30:11') ; 
insert into zkm settings insert_deduplicate=0 values ('id2','repo1','2023-12-12 11:30:11') ;

此时可以看到对应路径中产生3个文件夹，分别时这3条insert产生的，如下：

其中有两条数据属于同一天即同一个分区。

[root@ck01 zkm]# ll
total 4
drwxr-x--- 2 clickhouse clickhouse 259 Dec 12 17:26 20231211_1_1_0
drwxr-x--- 2 clickhouse clickhouse 259 Dec 12 17:26 20231211_2_2_0
drwxr-x--- 2 clickhouse clickhouse 259 Dec 12 17:26 20231212_3_3_0
drwxr-x--- 2 clickhouse clickhouse   6 Dec 12 17:26 detached
-rw-r----- 1 clickhouse clickhouse   1 Dec 12 17:26 format_version.txt

https://www.cnblogs.com/PiscesCanon/p/17898382.html

此时，执行optimize table final，文件夹all_1_1_0和all_2_2_0将被合并为all_1_2_1，

文件夹20231212_3_3_0被合并为20231212_3_3_1（没错，即便只有它自己一个同分区数据）。

ck01 :) optimize table zkm final;

OPTIMIZE TABLE zkm FINAL

Query id: 0aadfe30-c489-45d8-a063-d29f8a72b73d

Ok.

0 rows in set. Elapsed: 0.010 sec. 

[root@ck01 zkm]# ll
total 4
drwxr-x--- 2 clickhouse clickhouse 259 Dec 12 17:27 20231211_1_2_1
drwxr-x--- 2 clickhouse clickhouse 259 Dec 12 17:27 20231212_3_3_1
drwxr-x--- 2 clickhouse clickhouse   6 Dec 12 17:26 detached
-rw-r----- 1 clickhouse clickhouse   1 Dec 12 17:26 format_version.txt

如果将上述optimize table final换成select final操作(可删除表zkm且重复执行脚本)。

此时：

[root@ck01 zkm]# ll
total 4
drwxr-x--- 2 clickhouse clickhouse 259 Dec 12 17:36 20231211_1_1_0
drwxr-x--- 2 clickhouse clickhouse 259 Dec 12 17:36 20231211_2_2_0
drwxr-x--- 2 clickhouse clickhouse 259 Dec 12 17:36 20231212_3_3_0
drwxr-x--- 2 clickhouse clickhouse   6 Dec 12 17:36 detached
-rw-r----- 1 clickhouse clickhouse   1 Dec 12 17:36 format_version.txt

执行select final操作：

可以看到，select final并不会实际发生物理上的合并。

ck01 :) select * from zkm final;

SELECT *
FROM zkm
FINAL

Query id: b28a3986-39a6-4c0b-bf87-5474778317cf

┌─id──┬─repo──┬─────────CREATE_TIME─┐
│ id2 │ repo1 │ 2023-12-12 11:30:11 │
└─────┴───────┴─────────────────────┘
┌─id──┬─repo──┬─────────CREATE_TIME─┐
│ id1 │ repo1 │ 2023-12-11 12:30:11 │
└─────┴───────┴─────────────────────┘

2 rows in set. Elapsed: 0.006 sec. 

[root@ck01 zkm]# ll
total 4
drwxr-x--- 2 clickhouse clickhouse 259 Dec 12 17:36 20231211_1_1_0
drwxr-x--- 2 clickhouse clickhouse 259 Dec 12 17:36 20231211_2_2_0
drwxr-x--- 2 clickhouse clickhouse 259 Dec 12 17:36 20231212_3_3_0
drwxr-x--- 2 clickhouse clickhouse   6 Dec 12 17:36 detached
-rw-r----- 1 clickhouse clickhouse   1 Dec 12 17:36 format_version.txt

2.optimize table final只会对同个分片同个分区数据进行物理合并；select final则可以对同个分片不同分区的数据也会进行合并，但是不同分片即使满足合并条件也无法合并。

不讨论optimize table final，因为此语句因为只涉及物理合并因此只限定在单台ck服务器，讨论集群情况无意义。

这之前先介绍我实验环境的ck集群架构：

共4个节点，其中1，2互为副本（分片1），3，4互为副本（分片2）。

创建分布式表和本地表，并且在不同节点（即控制不同分片插入不同数据）插入数据。

CREATE TABLE zkm on cluster ceb_cluster
(
    `id` String DEFAULT 'NULL' COMMENT '用户编号',
    `repo` String DEFAULT 'NULL' COMMENT '仓库编号',
    `CREATE_TIME` DateTime DEFAULT '1971-01-01' COMMENT '数据建立时间'
)
ENGINE = Distributed('ceb_cluster', 'zkm', 'zkm_local', hiveHash(toYYYYMMDD(CREATE_TIME)));

CREATE TABLE zkm_local on cluster ceb_cluster
(
    `id` String DEFAULT 'NULL' COMMENT '用户编号',
    `repo` String DEFAULT 'NULL' COMMENT '仓库编号',
    `CREATE_TIME` DateTime DEFAULT '1971-01-01' COMMENT '数据建立时间'
)
ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/ceb_cluster-{shard}/ceb/zkm_local', '{replica}')
PARTITION BY toYYYYMMDD(CREATE_TIME) 
ORDER BY id 
SETTINGS old_parts_lifetime = 0; 


行1：分片1（节点1或2执行）：insert into zkm_local settings insert_deduplicate=0 values ('id1','jiedian1','2023-12-11 01:01:01'); 
行2：分片1（节点1或2执行）：insert into zkm_local settings insert_deduplicate=0 values ('id1','jiedian2','2023-12-12 02:02:02'); 
行3：分片2（节点3或4执行）：insert into zkm_local settings insert_deduplicate=0 values ('id1','jiedian3','2023-12-11 03:03:03');

其中，行1，2属于同一个分片，不同分区

行1，3属于不同分片，物理上是不同分区，逻辑上则属于同一个分区

现在在节点1或者2执行如下语句，可以看到：

本地表可以看到行1，2，加上final的时候，即使行1，2属于不同分区也进行了合并

szeport-ck01 :) select * from zkm_local;

SELECT *
FROM zkm_local

Query id: 8324bd02-484a-49c9-af0f-b87a9f108d9d

┌─id──┬─repo─────┬─────────CREATE_TIME─┐
│ id1 │ jiedian2 │ 2023-12-12 02:02:02 │
└─────┴──────────┴─────────────────────┘
┌─id──┬─repo─────┬─────────CREATE_TIME─┐
│ id1 │ jiedian1 │ 2023-12-11 01:01:01 │
└─────┴──────────┴─────────────────────┘

2 rows in set. Elapsed: 0.004 sec. 

szeport-ck01 :) select * from zkm_local final;

SELECT *
FROM zkm_local
FINAL

Query id: a81e2185-ab0f-40ce-84f1-0f27867e4f90

┌─id──┬─repo─────┬─────────CREATE_TIME─┐
│ id1 │ jiedian2 │ 2023-12-12 02:02:02 │
└─────┴──────────┴─────────────────────┘

1 row in set. Elapsed: 0.005 sec.

现在在节点1或者2执行如下语句，可以看到：

查询分布式表加上final的时候，即使行2，3属于不同分区也没有合并，而行1，2则还是不同分区但是合并。

szeport-ck01 :) select * from zkm;

SELECT *
FROM zkm

Query id: d5b59807-f8cf-43fc-a6e1-e52095cf7da4

┌─id──┬─repo─────┬─────────CREATE_TIME─┐
│ id1 │ jiedian2 │ 2023-12-12 02:02:02 │
└─────┴──────────┴─────────────────────┘
┌─id──┬─repo─────┬─────────CREATE_TIME─┐
│ id1 │ jiedian1 │ 2023-12-11 01:01:01 │
└─────┴──────────┴─────────────────────┘
┌─id──┬─repo─────┬─────────CREATE_TIME─┐
│ id1 │ jiedian3 │ 2023-12-11 03:03:03 │
└─────┴──────────┴─────────────────────┘

3 rows in set. Elapsed: 0.010 sec. 

szeport-ck01 :) select * from zkm final;

SELECT *
FROM zkm
FINAL

Query id: 45562a30-f11e-484a-867b-67b90ac7093a

┌─id──┬─repo─────┬─────────CREATE_TIME─┐
│ id1 │ jiedian2 │ 2023-12-12 02:02:02 │
└─────┴──────────┴─────────────────────┘
┌─id──┬─repo─────┬─────────CREATE_TIME─┐
│ id1 │ jiedian3 │ 2023-12-11 03:03:03 │
└─────┴──────────┴─────────────────────┘

2 rows in set. Elapsed: 0.011 sec.