ClickHouse如何通过TTL删除旧行？

在后台合并数据部分时，ClickHouse重写颗粒，排除created_at + INTERVAL小于当前日期的行。该进程以merge_with_ttl_timeout（默认1天）的间隔运行。

我可以更改现有表的TTL吗？

可以，使用ALTER TABLE ... MODIFY TTL。无需重建表。新规则会在后台应用于现有数据。

TTL如何帮助符合GDPR？

您可以在包含个人数据（电子邮件、IP）的单个列上设置TTL。指定时间后，这些列会被替换为默认值——数据被匿名化，但分析功能仍然保留。

TTL与GROUP BY结合有什么作用？

旧的详细行会被折叠成聚合行（例如按天+用户），使用指定的SUM、COUNT函数。空间减少100倍以上，同时保留有用信息。

如何强制立即应用TTL？

运行ALTER TABLE ... MATERIALIZE TTL。ClickHouse会扫描所有数据部分并立即应用规则。对于大表可能较慢。

ClickHouse中的TTL：数据生命周期管理

ClickHouse中的TTL：自动数据生命周期管理

1. 为什么需要TTL——"忘记删除"的问题

让我们回到在线赌场的例子。你存储所有玩家的投注。一个月后，表的大小达到500 GB。一年后，达到5 TB。磁盘填满，查询变慢，旧数据越来越不需要。业务负责人说："玩家只看最近30天的投注，报表只需要更早时期的汇总数据。"

你可以编写一个脚本，每天删除旧分区（如我们在分区文章中学到的）。但这需要一个外部调度器（cron）、一个单独的脚本、监控其执行以及错误处理。

TTL（生存时间） 在数据库层面解决了这个问题。它是ClickHouse内置的机制，自动：

删除旧行，
将它们移动到更便宜的磁盘（HDD、S3），
聚合旧数据（将细节折叠为摘要），
匿名化个人数据（GDPR）。

所有这些都在后台进行，无需你参与，按照你在SQL中定义的计划执行。

现实类比： TTL就像租用仓库。你和仓库主人约定："存放超过30天的货物，移到远处便宜的货架。存放超过一年的，扔掉。"主人会跟踪期限并完成工作；你不需要每次都提醒。

2. 行级TTL——删除旧记录

最简单的选项：行存活一定时间后删除。

使用TTL创建表

-- 创建一个投注表，行存活90天
CREATE TABLE bets
(
    user_id     UInt64,
    amount      Decimal(18,2),
    created_at  DateTime
)
ENGINE = MergeTree()
ORDER BY (user_id, created_at)
TTL created_at + INTERVAL 90 DAY;   -- 在created_at之后90天，行被删除

这里发生了什么：

TTL created_at + INTERVAL 90 DAY – 对于每一行，计算过期日期：created_at + 90天。一旦当前日期（today()）超过这个日期，该行被标记为删除。
后台进程（通常每天一次）扫描颗粒并删除TTL已过期的行。
删除在数据部分级别进行——ClickHouse重写不包含已删除行的部分。

ALTER TABLE——添加或更改TTL

关键特性：TTL可以添加到现有表而无需重建。

-- 向现有表添加TTL
ALTER TABLE bets MODIFY TTL created_at + INTERVAL 90 DAY;

-- 将生存时间从90天改为180天
ALTER TABLE bets MODIFY TTL created_at + INTERVAL 180 DAY;

-- 移除TTL（数据将永久存储）
ALTER TABLE bets REMOVE TTL;

为什么这很重要？ 因为在现实中，存储需求会变化。起初你认为需要永久存储所有数据。后来发现备份占用空间，分析师不需要旧数据。使用MODIFY TTL，你可以用一行命令更改规则。

如果向一个有100亿行的表添加TTL会发生什么？ 没什么大不了的。ClickHouse不会立即重写数据。它会在后台逐步应用TTL。在下次合并部分时，旧行将被排除。

3. 带磁盘迁移的TTL（分层存储）

有时删除数据很可惜，但存储在快速昂贵的SSD上成本高昂。解决方案：将旧数据移动到慢速便宜的HDD（或云存储S3）。

首先，在ClickHouse配置（config.xml）中配置磁盘：

<storage_configuration>
    <disks>
        <ssd>
            <path>/mnt/ssd/clickhouse/</path>
        </ssd>
        <hdd>
            <path>/mnt/hdd/clickhouse/</path>
        </hdd>
    </disks>
    <policies>
        <hot_to_cold>
            <volumes>
                <hot>
                    <disk>ssd</disk>
                </hot>
                <cold>
                    <disk>hdd</disk>
                </cold>
            </volumes>
        </hot_to_cold>
    </policies>
</storage_configuration>

现在创建一个带TTL迁移的表：

CREATE TABLE bets_tiered
(
    user_id     UInt64,
    amount      Decimal(18,2),
    created_at  DateTime
)
ENGINE = MergeTree()
ORDER BY (user_id, created_at)
TTL created_at + INTERVAL 30 DAY TO DISK 'hdd';   -- 30天后，移动到HDD

发生了什么： 行在前30天位于快速SSD上（此时在报表中最常需要）。30天后，ClickHouse在后台将数据部分移动到HDD。查询旧时期数据时，数据仍然可用，但速度稍慢。

你可以组合使用——先移动，后删除：

-- 30天在SSD上，然后在HDD上直到90天，然后删除
CREATE TABLE bets_multi_ttl
(
    user_id UInt64,
    amount Decimal(18,2),
    created_at DateTime
)
ENGINE = MergeTree()
ORDER BY user_id
TTL
    created_at + INTERVAL 30 DAY TO DISK 'hdd',
    created_at + INTERVAL 90 DAY DELETE;

类比： 酒店：前30天你住在套房（快速、昂贵）。然后你被移到标准间（较慢、便宜）。90天后你被驱逐。全部自动。

4. 迁移到S3的TTL

ClickHouse可以与云存储（Amazon S3、MinIO、Google Cloud Storage）配合使用。你可以配置一个包含S3的storage_policy，将旧数据移动到云上，存储成本极低。

配置（简化）：

<storage_configuration>
    <disks>
        <s3>
            <type>s3</type>
            <endpoint>https://s3.amazonaws.com/mybucket/clickhouse/</endpoint>
            <access_key_id>AKIAIOSFODNN7EXAMPLE</access_key_id>
            <secret_access_key>wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY</secret_access_key>
        </s3>
    </disks>
    <policies>
        <s3_policy>
            <volumes>
                <hot>
                    <disk>default</disk>   <!-- 本地SSD -->
                </hot>
                <cold_s3>
                    <disk>s3</disk>        <!-- 云 -->
                </cold_s3>
            </volumes>
        </s3_policy>
    </policies>
</storage_configuration>

带TTL到S3的表：

CREATE TABLE bets_s3
(
    user_id UInt64,
    amount Decimal(18,2),
    created_at DateTime
)
ENGINE = MergeTree()
ORDER BY created_at
TTL created_at + INTERVAL 90 DAY TO VOLUME 'cold_s3';   -- 90天后在S3中

为什么这很酷： 你为S3每GB每月支付几分钱。数据仍然可用于分析（尽管比本地磁盘慢）。而且你不必担心服务器空间不足——S3是无限的。

5. 用于聚合的TTL（最强大的功能）

这是我最喜欢的模式。不是删除旧的详细数据，而是将其折叠为聚合。例如，超过7天的投注不需要每秒级别，但需要每日和每个用户的汇总。

-- 包含详细投注的表
CREATE TABLE bets_detailed
(
    user_id     UInt64,
    bet_id      String,
    amount      Decimal(18,2),
    created_at  DateTime
)
ENGINE = MergeTree()
ORDER BY (user_id, created_at)
TTL created_at + INTERVAL 7 DAY
    GROUP BY toDate(created_at) AS day, user_id
    SET total_bets = sum(amount),           -- 当天所有投注的总和
        bet_count = count()                 -- 当天投注数量
    DELETE WHERE day < now() - INTERVAL 90 DAY;   -- 90天后，也删除聚合

分解：

TTL created_at + INTERVAL 7 DAY – 行（详细投注）创建7天后，它不再是详细的。
GROUP BY toDate(created_at) AS day, user_id – 行按天和用户分组。对于每个用户每天，不再是数千条详细投注，而是一条聚合行。
SET total_bets = sum(amount), bet_count = count() – 在新的聚合行中，字段用聚合函数填充。
DELETE WHERE day < now() - INTERVAL 90 DAY – 超过90天的聚合最终被删除。

实际发生的情况：

第0–7天： 数据以详细形式存储。你可以分析每笔投注。
第7–90天： 详细投注被折叠为每个(user_id, day)一行，包含total_bets和bet_count字段。空间使用减少10–100倍。
第90天以上： 甚至聚合也被删除，仅保留备份。

类比： 你有一本按小时记录的日记。一周后，你将小时记录重写为每日摘要（总计）。三个月后，你甚至扔掉每日摘要，只保留月度报告。

6. 针对特定列的TTL（GDPR匿名化）

根据法律（欧洲的GDPR、个人数据），你需要在特定时间段后删除个人信息（电子邮件、IP地址）。但匿名分析数据（投注金额、游戏次数）可以永久存储。

TTL可以应用于单个列，而不是整行。

-- 包含个人数据的表
CREATE TABLE user_events
(
    user_id     UInt64,
    email       String,           -- 个人列
    ip_address  String,           -- 个人列
    event_type  String,
    event_value UInt64,
    created_at  DateTime
)
ENGINE = MergeTree()
ORDER BY (user_id, created_at)
TTL
    email + INTERVAL 1 YEAR,      -- 一年后，email被置空
    ip_address + INTERVAL 1 YEAR, -- 一年后，IP被置空
    created_at + INTERVAL 10 YEAR; -- 10年后，整行被删除

发生了什么： 行创建一年后，email和ip_address列被替换为默认值（String为空字符串，数字为0）。数据仍然可用于分析（你知道有某个用户，但不知道具体是谁）。10年后，整行被删除。

为什么这对GDPR很重要： 你自动遵守法律，无需手动脚本。审计员可以来检查你的TTL规则，验证个人数据是否未超过允许的存储时间。

7. 配置后台TTL进程

TTL不会在过期后立即触发。ClickHouse运行一个后台进程，该进程：

检查颗粒，
应用TTL规则，
重写不包含过期数据或包含修改列的部分。

配置参数（在config.xml中或通过SET）：

-- TTL进程运行间隔（默认1天）
ALTER SYSTEM MODIFY SETTING merge_with_ttl_timeout = 86400;  -- 以秒为单位

-- 测试时，可以设置更频繁
SET merge_with_ttl_timeout = 3600;   -- 每小时一次

为什么TTL不应该太频繁？ 因为应用TTL是一个数据部分重写操作，会加载CPU和磁盘。每天一次没问题。如果你有TB级数据，每小时一次可能会干扰。

如何检查TTL是否在工作：

-- 查看哪些部分有活动的TTL
SELECT 
    partition,
    name,
    rows,
    modification_time,
    has_ttl_info   -- 1 = TTL已应用于此部分
FROM system.parts
WHERE table = 'bets' AND active = 1;

8. MATERIALIZE TTL——强制应用

有时你需要TTL立即应用，而不是等待后台进程。例如：

你刚刚向一个巨大的表添加了TTL，想要立即清理旧数据。
你正在测试TTL规则，不想等一天。

-- 强制对整个表应用TTL
ALTER TABLE bets MATERIALIZE TTL;

-- 仅对特定分区（更快）
ALTER TABLE bets MATERIALIZE TTL IN PARTITION '202501';

发生了什么： ClickHouse扫描表（或分区）的所有部分，并立即应用所有TTL规则。在大型表上可能需要几分钟或几小时。不要在高峰时段执行此操作。

何时使用： 在夜间、维护期间，或在创建备份之前，以避免备份已经死亡的数据。

9. 实际用例：带GDPR和聚合的赌场

现在让我们把所有内容整合在一起。想象一个用于存储在线赌场事件的完整模式。

-- 原始事件（每笔投注、每场游戏）
CREATE TABLE raw_events
(
    user_id         UInt64,
    session_id      String,
    ip_address      String,           -- GDPR敏感
    event_type      String,           -- 'bet', 'win', 'login'
    event_value     Int64,
    created_at      DateTime
)
ENGINE = MergeTree()
ORDER BY (user_id, created_at)
TTL
    -- 详细事件存储30天
    created_at + INTERVAL 30 DAY DELETE,
    -- 但IP地址在14天后移除（GDPR）
    ip_address + INTERVAL 14 DAY,
    -- 旧数据聚合：30天后，折叠为每日摘要
    created_at + INTERVAL 30 DAY
        GROUP BY toDate(created_at) AS day, user_id
        SET total_bets = sumIf(event_value, event_type = 'bet'),
            total_wins = sumIf(event_value, event_type = 'win'),
            sessions_count = countDistinct(session_id)
        DELETE WHERE day < now() - INTERVAL 2 YEAR;   -- 聚合存储2年

我们实现了什么：

0–14天： 完整信息，包括IP地址。可以调查事件，检测多账户。
15–30天： IP地址已置空（匿名化），但详细事件仍然存在。可以分析用户行为，无需地理位置。
31天–2年： 详细事件被删除。取而代之的是每个(day, user)的聚合行。空间使用减少100倍。仪表板运行快速。
超过2年： 所有数据被删除。仅保留S3上的备份（如果你做了的话）。

TTL后如何读取聚合数据：

-- 现在表是混合的：详细行（前30天）和聚合行（最多2年）
-- 你仍然编写正常的聚合查询；它对两种行都有效
SELECT 
    toDate(created_at) AS day,
    user_id,
    sum(event_value) AS total
FROM raw_events
WHERE created_at >= today() - 45
GROUP BY day, user_id;

ClickHouse自己会判断：对于新鲜数据，它汇总详细行；对于旧数据，它使用预计算的聚合。神奇。

10. 何时不使用TTL及替代方案

TTL不适用的场景：

数据频繁更新。 TTL在插入时触发，而不是在最后更新时。如果你通过INSERT取消（CollapsingMergeTree）修改行，TTL将从原始的created_at开始计算。解决方案：在TTL表达式中使用updated_at列。
需要精确的时间控制。 TTL进程是后台的且不精确。如果你需要保证在午夜精确删除——它不会工作。延迟可能长达数小时。
非常大的表且合并很少。 TTL在部分合并期间应用。如果表合并很少（例如，由于merge_with_ttl_timeout设置），旧数据可能停留更长时间。

TTL的替代方案：

方法	何时使用	优点	缺点
分区 + DROP PARTITION	数据持续流入，按日历删除	即时删除，无开销	需要外部调度器，不灵活（仅按分区）
TTL DELETE	数据可能延迟到达，按行年龄删除	内置，灵活，无需外部脚本	删除时间不精确，合并负载
TTL TO DISK	需要保留数据但使用廉价存储	节省存储成本，对查询透明	需要storage_policy配置
TTL GROUP BY	不需要详细数据，只需要聚合	大幅减少空间（100倍以上）	丢失细节，调试复杂

与分区结合以获得最佳效果

最佳实践：同时使用分区和TTL。

CREATE TABLE bets_optimized
(
    user_id UInt64,
    amount Decimal(18,2),
    created_at DateTime
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(created_at)           -- 按月分区
ORDER BY (user_id, created_at)
TTL created_at + INTERVAL 30 DAY DELETE;    -- 按30天TTL

为什么这很好：

分区有助于快速删除整个月（如果TTL滞后）。
TTL在分区内部更灵活地清理（按行年龄，而不是按日历）。

下一步

现在你拥有了在ClickHouse中自动管理数据的所有工具。接下来的主题：

高级存储策略 – 如何设置从SSD → HDD → S3的自动迁移，每个阶段使用不同的TTL。
监控TTL – 如何使用system.query_log跟踪正在删除多少数据以及速度。
TTL + 物化视图 – 如何自动将旧数据聚合到单独的表中，而不与详细数据混合。

总结： ClickHouse中的TTL是数据生命周期管理的瑞士军刀。它可以删除、移动、聚合和匿名化。使用TTL ... DELETE进行清理，TTL ... TO DISK节省成本，TTL ... GROUP BY实现聚合魔法，以及TTL on columns满足GDPR。当需要立即应用规则时，不要忘记MATERIALIZE TTL。

← 上一篇: ClickHouse中的ORDER BY与PRIMARY KEY：如何正确设置索引
→ 下一篇: ClickHouse中的字典：无需JOIN的快速查找

— Editorial Team

ClickHouse中的TTL：数据生命周期管理

ClickHouse中的TTL：自动数据生命周期管理

1. 为什么需要TTL——"忘记删除"的问题

2. 行级TTL——删除旧记录

使用TTL创建表

ALTER TABLE——添加或更改TTL

3. 带磁盘迁移的TTL（分层存储）

4. 迁移到S3的TTL

5. 用于聚合的TTL（最强大的功能）

6. 针对特定列的TTL（GDPR匿名化）

7. 配置后台TTL进程

8. MATERIALIZE TTL——强制应用

9. 实际用例：带GDPR和聚合的赌场

10. 何时不使用TTL及替代方案

TTL不适用的场景：

TTL的替代方案：

与分区结合以获得最佳效果

下一步

社区

继续阅读

最近浏览

标签

主题

常见问题

ClickHouse中的TTL：自动数据生命周期管理

1. 为什么需要TTL——"忘记删除"的问题

2. 行级TTL——删除旧记录

使用TTL创建表

ALTER TABLE——添加或更改TTL

3. 带磁盘迁移的TTL（分层存储）

4. 迁移到S3的TTL

5. 用于聚合的TTL（最强大的功能）

6. 针对特定列的TTL（GDPR匿名化）

7. 配置后台TTL进程

8. MATERIALIZE TTL——强制应用

9. 实际用例：带GDPR和聚合的赌场

10. 何时不使用TTL及替代方案

TTL不适用的场景：

TTL的替代方案：

与分区结合以获得最佳效果

下一步

社区

继续阅读

ClickHouse中的ORDER BY和PRIMARY KEY：索引选择

ClickHouse中的分区：策略与操作

ClickHouse中的CollapsingMergeTree：无需UPDATE即可更新

ClickHouse中的SummingMergeTree和AggregatingMergeTree

ClickHouse中的ReplacingMergeTree：完整指南

Oracle在耶路撒冷开设了一个地下云计算区域——以色列首个主权云

Matic机器人吸尘器：放弃激光雷达和AI导航

Insurights：管理健康保险的AI平台融资2200万美元

KAI和IAI：游荡弹药与防空压制的新概念

新格伦蓝色起源爆炸：对NASA阿尔忒弥斯和亚马逊柯伊伯的影响

最近浏览

标签

主题

常见问题

分享本文