Clickhouse MergeTree表引擎TTL分布式实现

1. 执行SQL

clickhouse1 :) 
clickhouse1 :) CREATE TABLE ttl_table_local ON CLUSTER sharding_ha
:-] (
:-]     `id` UInt64,
:-]     `create_time` DateTime,
:-]     `code` String TTL create_time + toIntervalSecond(30),
:-]     `type` UInt8 TTL create_time + toIntervalSecond(30)
:-] )
:-] ENGINE = ReplicatedMergeTree('/clickhouse/tables/ttl_table/{shard}', '{replica}')
:-] ORDER BY id
:-] TTL create_time + toIntervalMinute(1);

CREATE TABLE ttl_table_local ON CLUSTER sharding_ha
(
    `id` UInt64,
    `create_time` DateTime,
    `code` String TTL create_time + toIntervalSecond(30),
    `type` UInt8 TTL create_time + toIntervalSecond(30)
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/ttl_table/{shard}', '{replica}')
ORDER BY id
TTL create_time + toIntervalMinute(1)

Query id: bfc40713-1fe0-458a-9ac8-3a59872a224a

┌─host────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ clickhouse2 │ 9000 │      0 │       │                   3 │                0 │
│ clickhouse3 │ 9000 │      0 │       │                   2 │                0 │
│ clickhouse1 │ 9000 │      0 │       │                   1 │                0 │
│ clickhouse4 │ 9000 │      0 │       │                   0 │                0 │
└─────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘

4 rows in set. Elapsed: 0.149 sec. 

clickhouse1 :) 
clickhouse1 :) CREATE TABLE ttl_table_all ON CLUSTER sharding_ha
:-] (
:-]     `id` UInt64,
:-]     `create_time` DateTime,
:-]     `code` String,
:-]     `type` UInt8
:-] )
:-] ENGINE = Distributed(sharding_ha, default, ttl_table_local, rand());

CREATE TABLE ttl_table_all ON CLUSTER sharding_ha
(
    `id` UInt64,
    `create_time` DateTime,
    `code` String,
    `type` UInt8
)
ENGINE = Distributed(sharding_ha, default, ttl_table_local, rand())

Query id: 6f657775-d637-44fe-bf07-25a04084c72d

┌─host────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ clickhouse2 │ 9000 │      0 │       │                   3 │                0 │
│ clickhouse3 │ 9000 │      0 │       │                   2 │                0 │
│ clickhouse1 │ 9000 │      0 │       │                   1 │                0 │
│ clickhouse4 │ 9000 │      0 │       │                   0 │                0 │
└─────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘

4 rows in set. Elapsed: 0.135 sec. 

clickhouse1 :) 
clickhouse1 :) insert into ttl_table_all(id, create_time, code, type) values(66, now(), 'A', 1), (66, now() + interval 5 minute, 'A', 1);

INSERT INTO ttl_table_all (id, create_time, code, type) VALUES

Query id: d18773fe-bcbc-4e85-9cc4-563a686de5db

Ok.

2 rows in set. Elapsed: 0.015 sec. 

clickhouse1 :)

查询数据如下：

clickhouse1 :) 
clickhouse1 :) select * from ttl_table_all limit 10;

SELECT *
FROM ttl_table_all
LIMIT 10

Query id: 5ab98d68-1253-4760-b654-ef130715cbd0

┌─id─┬─────────create_time─┬─code─┬─type─┐
│ 66 │ 2021-07-14 11:37:00 │ A    │    1 │
│ 66 │ 2021-07-14 11:42:00 │ A    │    1 │
└────┴─────────────────────┴──────┴──────┘

2 rows in set. Elapsed: 0.011 sec. 

clickhouse1 :) 

过了30秒之后，再次查看：

clickhouse1 :) 
clickhouse1 :) optimize table ttl_table_local on cluster sharding_ha final;

OPTIMIZE TABLE ttl_table_local ON CLUSTER sharding_ha FINAL

Query id: b9b55917-6974-4d26-8e7d-697aee1cec1a

┌─host────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ clickhouse2 │ 9000 │      0 │       │                   3 │                0 │
│ clickhouse1 │ 9000 │      0 │       │                   2 │                0 │
│ clickhouse3 │ 9000 │      0 │       │                   1 │                0 │
│ clickhouse4 │ 9000 │      0 │       │                   0 │                0 │
└─────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘

4 rows in set. Elapsed: 0.164 sec. 

clickhouse1 :)
clickhouse1 :) select * from ttl_table_all limit 10;

SELECT *
FROM ttl_table_all
LIMIT 10

Query id: e814ed48-fea6-4a39-b27e-5b37dddc3bc1

┌─id─┬─────────create_time─┬─code─┬─type─┐
│ 66 │ 2021-07-14 11:37:00 │      │    0 │
│ 66 │ 2021-07-14 11:42:00 │ A    │    1 │
└────┴─────────────────────┴──────┴──────┘

2 rows in set. Elapsed: 0.017 sec. 

clickhouse1 :) 

又过了30秒之后，再次查看：

clickhouse1 :) 
clickhouse1 :) optimize table ttl_table_local on cluster sharding_ha final;

OPTIMIZE TABLE ttl_table_local ON CLUSTER sharding_ha FINAL

Query id: 63d61b9e-755f-4a17-a78f-c5cce6a76b93

┌─host────────┬─port─┬─status─┬─error─┬─num_hosts_remaining─┬─num_hosts_active─┐
│ clickhouse2 │ 9000 │      0 │       │                   3 │                0 │
│ clickhouse1 │ 9000 │      0 │       │                   2 │                0 │
│ clickhouse3 │ 9000 │      0 │       │                   1 │                0 │
│ clickhouse4 │ 9000 │      0 │       │                   0 │                0 │
└─────────────┴──────┴────────┴───────┴─────────────────────┴──────────────────┘

4 rows in set. Elapsed: 0.131 sec. 

clickhouse1 :)
clickhouse1 :) select * from ttl_table_all limit 10;

SELECT *
FROM ttl_table_all
LIMIT 10

Query id: 230225a3-3c2b-447a-a490-bfe1f936c9c8

┌─id─┬─────────create_time─┬─code─┬─type─┐
│ 66 │ 2021-07-14 11:42:00 │ A    │    1 │
└────┴─────────────────────┴──────┴──────┘

1 rows in set. Elapsed: 0.009 sec. 

clickhouse1 :) 

2. 说明

• 同时设置了列TTL和表TTL，以先到期的为主
• 主键字段不能设置TTL，TTL字段类型为Date或DateTime, TTL时间类型有second、minute、hour、day、 week、month、quarter和year
• 修改或添加列字段的TTL：alter table ttl_table_local on cluster sharding_ha modify column code String ttl create_time + interval 1 day
• 修改表TTL：alter table ttl_table_local on cluster sharding_ha modify ttl create_time + interval 3 day
• 没有提供取消列和表TTL的方法

3. TTL的运行原理

todo