第 11 章 TTL、分区与数据生命周期

学习目标：掌握 ClickHouse 「分区设计 + TTL + 冷热分层存储」三件套；能为一张「日增 10 亿行、保留 3 年」的事实表设计出正确的分区粒度与多温度盘的搬迁规则；遇到「90 天前数据自动归档到 S3」「超过半年自动 ZSTD 重压缩」「快速删除上个月的数据」这类需求，能直接背出 SQL。

11.0 一句话总览

「分区是数据的『仓库楼层』，TTL 是数据的『保质期戳』，存储策略是『冷库 / 常温 / 冷藏柜』 —— 三者配合，才能让 PB 级数据自动按时间漂移到合适的硬件上。」

   ┌─────────────────────────────────────────────────────────┐
   │  PARTITION BY toYYYYMM(date)   │   分区 = 楼层           │
   ├─────────────────────────────────────────────────────────┤
   │  TTL date + INTERVAL 90 DAY    │   TTL  = 保质期戳        │
   │     TO DISK 'cold'             │                          │
   │     [DELETE / RECOMPRESS ...]  │                          │
   ├─────────────────────────────────────────────────────────┤
   │  storage_policies              │   存储策略 = 货架组       │
   │   hot (SSD) → cold (HDD) → s3  │                          │
   └─────────────────────────────────────────────────────────┘

11.1 分区是什么：图书馆按月份归档报刊

                  报刊阅览室                                   
   ┌─────────┬─────────┬─────────┬─────────┬─────────┐         
   │ 2026-01 │ 2026-02 │ 2026-03 │ 2026-04 │ 2026-05 │  ← 每月一个柜子
   └─────────┴─────────┴─────────┴─────────┴─────────┘         
        ▲              ▲                                       
        │              │                                       
        └─ 想看 1 月报刊  └─ 想看 3 月报刊                       
           只走过去一柜       只翻这一柜                          
                                                               
   ❌ 不要按"报刊种类"分区，因为读者按时间找。                    
   ❌ 不要按"日期+作者"细分，柜子太多管理员崩溃。

ClickHouse 的分区（PARTITION BY <expr>）做的就是这件事：把同一表的数据按表达式切成多个独立的物理目录，每个分区一个目录。

/var/lib/clickhouse/data/learn_ck/events_log/
├── 202604_1_42_3/        ← 4 月分区，第 1~42 个 Part 合并到第 3 代
├── 202604_43_60_2/
├── 202605_1_25_2/        ← 5 月分区
├── ...
└── detached/             ← DETACH 出去的"离线"分区放这里

📌 首次术语解释 · Part：MergeTree 写入时每个 Block 落地为一个 Part（数据目录），后台再合并成更大的 Part；Part 的命名格式是 <分区>_<minBlock>_<maxBlock>_<level>。

11.1.1 分区表达式怎么选

表达式	适用场景
`toYYYYMM(date)`	大多数事实表，月级分区，每天 ~ 几亿行
`toYYYYMMDD(date)`	日活跃，需要按天 DROP PARTITION
`toMonday(date)`	周报常态分析
`toYYYYMM(date), category`	极少用 — 多键分区会让 Part 总数爆炸
`tuple()` 或不写	不分区，整张表一个分区 (小维度表 / Replicated 协调表)

经验比例：单个分区 1 亿 ~ 10 亿行是甜蜜区。

11.1.2 分区粒度的取舍：不是越细越好

            分区太细                          分区太粗
  ─────────────────────────         ─────────────────────────
   PARTITION BY (date, region)       PARTITION BY toYear(date)
                                     
   ✗ 1 天 × 30 region = 30 分区/天    ✗ 一个分区里 365 亿行
   ✗ 一年下来 1 万+ 分区              ✗ DROP PARTITION 一删就是一年
   ✗ 每个分区都有自己的 Part         ✗ TTL 触发也只能删一整年
   ✗ Merge 任务排队 → CPU 飙高        ✗ 想清掉 90 天前的数据？做不到
   ✗ Insert/Mutation 全表慢         ✓ Part 数少，Merge 压力小

⚠️ 生产事故金句：「Part 爆炸是 ClickHouse 第一杀手」。MergeTree 后台合并是单线程串行的，一旦活跃 Part 数超过几千，写入会被节流（Too many parts 错误），整个表不可用。

观测：

sql

SELECT
    database, table,
    count()                                AS active_parts,
    sum(rows)                              AS rows,
    formatReadableSize(sum(bytes_on_disk)) AS size
FROM system.parts
WHERE active
GROUP BY database, table
ORDER BY active_parts DESC
LIMIT 20;

如果某张表的 active_parts > 1000，拉响警报，立即检查分区粒度和 INSERT 批次。

11.2 分区裁剪：WHERE 命中分区键就能跳过整个目录

SELECT * FROM events_log
WHERE event_date BETWEEN '2026-04-01' AND '2026-04-30';

           ┌──────────────────────────────────────────┐
           │  ClickHouse 优化器看到 WHERE 命中分区键    │
           │  → 直接跳到 202604 分区目录              │
           │  → 不打开 202601/02/03/05/06/07 ...      │
           └──────────────────────────────────────────┘

EXPLAIN ESTIMATE SELECT ... 会告诉你：
  parts: 12 → 1   (仅扫描 1 个分区的 12 个 Part 中的相关 Mark)

命中条件：

WHERE 里使用分区键本身或可以推导到分区键的表达式。
WHERE event_date >= '...' 命中 PARTITION BY toYYYYMM(event_date)，因为 event_date 范围可以推回 toYYYYMM 范围。
但 WHERE toString(event_date) = '2026-04-17' 不命中（函数包了一层，CK 没法反推）。

📌 第一定律：让 WHERE 天然命中分区键。如果业务总是按用户 ID 查，但分区键是日期 → 永远扫全表。这种情况要么换分区键，要么加 Projection。

11.3 分区操作六件套

ClickHouse 把"按分区批量管理数据"做到了极致 —— 这些操作比 DELETE / UPDATE 快几个数量级。

命令	作用	快不快
`ALTER TABLE t DROP PARTITION p`	永久删除一个分区，等同于 `rm -rf` 那个目录	⚡ 几毫秒
`ALTER TABLE t DETACH PARTITION p`	把分区移到 `detached/` 子目录，元数据脱离	⚡ 几毫秒
`ALTER TABLE t ATTACH PARTITION p`	把 `detached/` 里的分区挂回来	⚡ 几毫秒
`ALTER TABLE t REPLACE PARTITION p FROM s`	用源表 `s` 的同名分区整体替换目标分区	⚡ 仅元数据切换，秒级
`ALTER TABLE t MOVE PARTITION p TO TABLE u`	把分区整体搬到另一张表 (Schema 必须一致)	⚡ 文件 hardlink
`ALTER TABLE t MOVE PART '...' TO DISK 'cold'`	把单个 Part 搬到指定磁盘	🐢 真实 IO

为什么这么快：分区是物理目录隔离的，所谓的"删除"就是改一下元数据，把目录从『活跃』标记成『非活跃』，真正的文件由 background 慢慢回收 (old_parts_lifetime 控制保留期)。

典型用法：

sql

-- 场景 1: 业务下线某月数据
ALTER TABLE events DROP PARTITION '202312';

-- 场景 2: 安全归档：先 DETACH 再人工搬走
ALTER TABLE events DETACH PARTITION '202401';
-- 现在数据在 detached/ 里，可以 cp 到 S3 或归档盘
ALTER TABLE events DROP DETACHED PARTITION '202401';   -- 确认无误后清理

-- 场景 3: 用 staging 表替换正式表的某个分区 (类似 MySQL 的 EXCHANGE PARTITION)
ALTER TABLE events_main REPLACE PARTITION '202604' FROM events_staging;

11.4 TTL：让数据自动「到期就办手续」

TTL (Time To Live) 是 ClickHouse 独有的「数据保质期机制」。你定义一条规则，引擎在后台 Merge 时自动按规则处理过期数据。

11.4.1 三种 TTL 形态

sql

CREATE TABLE events_log
(
    event_date  Date,
    user_id     UInt64,
    payload     String,                   -- 大字段
    debug_log   String                    -- 不重要
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, user_id)
TTL
    -- ① 表级 TTL：到期把整行处理掉
    event_date + INTERVAL 90 DAY DELETE,
    event_date + INTERVAL 30 DAY TO DISK 'cold',
    event_date + INTERVAL 60 DAY TO VOLUME 's3_volume',
    event_date + INTERVAL 45 DAY RECOMPRESS CODEC(ZSTD(17)),

    -- ② 列级 TTL：到期清空某列
    debug_log SET TTL event_date + INTERVAL 7 DAY,

    -- ③ Group By 聚合 TTL：到期把明细按 GROUP BY 聚合
    -- 必须配合 GROUP BY 使用
    INTERVAL 30 DAY GROUP BY event_date, user_id
        SET payload = any(payload);

📌 TTL 触发时机：不是定时任务，而是后台 Merge 发生时顺便检查每个 Part 的 min/max ttl。所以 Part 没合并的时候 TTL 不会生效。要立即触发可以 OPTIMIZE TABLE ... FINAL 或者 MATERIALIZE TTL。

11.4.2 表级 TTL 的四种动作

动作	含义	举例
`DELETE`	直接物理删除数据（默认）	`TTL date + 90 DAY DELETE`
`TO DISK 'cold'`	把整个 Part 搬到指定 disk	`TTL date + 30 DAY TO DISK 'hdd'`
`TO VOLUME 'v_s3'`	把 Part 搬到指定 volume (volume 是 disk 的逻辑组)	`... TO VOLUME 's3'`
`RECOMPRESS CODEC(ZSTD(17))`	用更高压缩比重新编码，省空间但减慢扫描	`... 60 DAY RECOMPRESS ...`

可以多条 TTL 串成一条时间线，30 天搬 HDD → 90 天 ZSTD 重压缩 → 365 天上 S3 → 3 年 DELETE。

11.4.3 列级 TTL：让"非核心列"先消失

业务场景：

"原始 payload 留 7 天给排错，但后面 90 天 PV/UV 还要查 user_id / event_date，这两列要保留。"

sql

ALTER TABLE events_log MODIFY COLUMN debug_log String TTL event_date + INTERVAL 7 DAY;

到期后 debug_log 列变成空字符串（基础类型变成"零值"）；user_id / event_date 完整保留。列级 TTL 不会删行。

11.4.4 GROUP BY TTL：到期把明细 rollup 成聚合

sql

TTL event_date + INTERVAL 30 DAY GROUP BY event_date, user_id
    SET payload = max(payload), cnt = sum(cnt);

30 天后，所有同 (event_date, user_id) 的明细会被自动合并成一行。配合 SummingMergeTree / AggregatingMergeTree 是终极「冷数据自动 rollup」方案。

11.4.5 强制立即应用 TTL

sql

-- 全表强制扫一遍 TTL（生产慎用，扫全表）
ALTER TABLE events_log MATERIALIZE TTL;

-- 仅对某分区
OPTIMIZE TABLE events_log PARTITION '202604' FINAL;

11.5 冷热分层存储：`storage_policies`

多温度盘的玩法：让最近 7 天的数据待在 NVMe SSD（贵，快），30 天前的搬到 HDD（便宜，慢），90 天前的搬到 S3（更便宜，更慢）。CK 通过 config.xml 里配置 storage_policies 实现。

11.5.1 storage_policies 配置示例

xml

<!-- /etc/clickhouse-server/config.d/storage.xml -->
<clickhouse>
  <storage_configuration>
    <disks>
      <hot>
        <path>/data/ssd/clickhouse/</path>
      </hot>
      <cold>
        <path>/data/hdd/clickhouse/</path>
      </cold>
      <s3_archive>
        <type>s3</type>
        <endpoint>https://my-bucket.s3.amazonaws.com/ck/</endpoint>
        <access_key_id>AKIA...</access_key_id>
        <secret_access_key>...</secret_access_key>
      </s3_archive>
    </disks>

    <policies>
      <hot_to_cold_to_s3>
        <volumes>
          <hot>
            <disk>hot</disk>
            <max_data_part_size_bytes>10737418240</max_data_part_size_bytes>  <!-- 10 GB -->
          </hot>
          <cold>
            <disk>cold</disk>
          </cold>
          <s3_volume>
            <disk>s3_archive</disk>
          </s3_volume>
        </volumes>
        <move_factor>0.2</move_factor>   <!-- hot 用满 80% 自动迁出 -->
      </hot_to_cold_to_s3>
    </policies>
  </storage_configuration>
</clickhouse>

11.5.2 建表关联策略

sql

CREATE TABLE events_log
(...)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (event_date, user_id)
TTL
    event_date + INTERVAL 30 DAY  TO DISK 'cold',
    event_date + INTERVAL 90 DAY  TO VOLUME 's3_volume',
    event_date + INTERVAL 365 DAY DELETE
SETTINGS storage_policy = 'hot_to_cold_to_s3';

11.5.3 数据搬迁观察

sql

SELECT
    partition, name, disk_name, rows,
    formatReadableSize(bytes_on_disk) AS size
FROM system.parts
WHERE database = 'learn_ck' AND table = 'events_log' AND active
ORDER BY partition, name;

可以看到每个 Part 当前所在的 disk_name —— 自动从 hot 迁到 cold 再到 s3_archive。

11.5.4 手动搬迁

sql

-- 把指定 Part 立即搬到指定 disk / volume
ALTER TABLE events_log MOVE PART '202604_1_42_3' TO DISK 'cold';
ALTER TABLE events_log MOVE PARTITION '202401' TO VOLUME 's3_volume';

11.6 `OPTIMIZE TABLE ... PARTITION xxx FINAL` 的代价

sql

OPTIMIZE TABLE events_log PARTITION '202604' FINAL;

它做了什么：强制把指定分区的所有 active Part 合并成一个。听起来很美？代价是：

   一个分区的所有 Part         →   合并成一个超大 Part
   (假设 50 GB 共 32 个)             (50 GB 单 Part)

   IO 写: 50 GB
   CPU:  全核满载几分钟到几十分钟
   阻塞: 同分区的并发 Merge / Insert 会排队

什么时候用：

离线时段做一次性 Compaction
让 ReplacingMergeTree / SummingMergeTree 立即出最终态（替代 SELECT FINAL）
让 TTL 立即生效

绝对不要：

在线高峰跑
当成「定时清理任务」每天跑
当成「修一下数据」的随手命令

📌 生产黄金法则：让 OPTIMIZE FINAL 成为例外，而不是常态。如果你"不得不"频繁 OPTIMIZE FINAL，说明设计有问题（分区粒度、TTL、引擎选型）。

11.7 📌 与 MySQL / PG 分区表的对比小框

维度	ClickHouse	MySQL 分区	PostgreSQL 声明式分区
分区表达式	`toYYYYMM(date)` 等任意函数	`RANGE / LIST / HASH / KEY`	`RANGE / LIST / HASH`
分区裁剪	✅ 自动且强	✅	✅ (Pruning)
DROP PARTITION	⚡ 几毫秒，仅改元数据	🐢 重写表	⚡ DETACH 子表
跨分区查询	✅ 透明	✅	✅
自动 TTL	✅ CK 独门	❌ 需 cron 删	❌ 需 pg_cron / pgpartman
冷热分层存储	✅ CK 独门 (storage_policies)	❌	⚠️ 需借助 tablespace + 外部脚本
多温度搬迁	✅ TTL TO DISK / TO VOLUME	❌	❌
重压缩	✅ TTL RECOMPRESS CODEC	❌	❌
GROUP BY TTL	✅ 到期自动 rollup	❌	❌

金句："分区在三家都有，但 TTL + 冷热分层是 ClickHouse 一家独大。"

11.8 本章小结

┌──────────────────────────────────────────────────────────────┐
│                     第 11 章核心要点                            │
├──────────────────────────────────────────────────────────────┤
│                                                                │
│  ① 分区 = 物理目录隔离，目的是「按时间快速 DROP / 裁剪 / 归档」 │
│     甜蜜区：单分区 1~10 亿行，月级分区是默认选择。              │
│                                                                │
│  ② Part 爆炸警报：active_parts > 1000 → 立即排查。              │
│                                                                │
│  ③ DROP / DETACH / ATTACH / REPLACE / MOVE PARTITION 极快，     │
│     都只改元数据，比 DELETE / UPDATE 快几个数量级。              │
│                                                                │
│  ④ TTL 三种形态：表级 / 列级 / GROUP BY 聚合。                   │
│     表级 TTL 四种动作：DELETE / TO DISK / TO VOLUME /           │
│     RECOMPRESS。可串成时间线。                                   │
│                                                                │
│  ⑤ TTL 触发是 Merge 期间顺手做，不是定时任务。                   │
│     立即生效用 MATERIALIZE TTL 或 OPTIMIZE PARTITION FINAL。    │
│                                                                │
│  ⑥ storage_policies 配 hot/cold/s3 三层 volume，                │
│     配合 TTL TO DISK 实现 PB 级数据全自动漂移。                  │
│                                                                │
│  ⑦ OPTIMIZE FINAL 极重，只在离线 / 必要时用，禁止当日常任务。   │
│                                                                │
└──────────────────────────────────────────────────────────────┘

11.9 面试高频题

Q1：ClickHouse 的 PARTITION BY 怎么选？粒度怎么定？

考察点：对分区设计原则的理解 + 踩坑经验。

标准答案：

核心原则：让业务查询的 WHERE 条件自然命中分区键，让 DROP 操作能按业务粒度（天 / 月）整块删。
甜蜜区：单个分区 1 亿 ~ 10 亿行。少了导致 Part 数爆炸，多了导致裁剪粒度太粗。
大多数事实表：PARTITION BY toYYYYMM(event_date) 月级；日活极大（每天 10 亿+）才用 toYYYYMMDD。
小维度表 / 协调表：不分区（默认 tuple()）。
避免：多键分区（(date, region)）—— Part 数 = 时间数 × region 数，几个月就几万分区，整个表卡住。

加分项：能说出怎么观测分区合理性 —— system.parts 看 active_parts 数量、bytes_on_disk 平均大小；如果平均 Part < 10MB 说明 INSERT 太碎，需要批量化。

易错点：把 PARTITION BY 当成 MySQL 的索引来想 —— 它的目的是"分目录隔离 + 整块管理"，不是"加速点查"。

Q2：TTL 是什么？什么时候真正触发？

考察点：是否真懂 TTL 的执行机制。

标准答案：

TTL 是 ClickHouse 给 MergeTree 表加的「数据保质期」机制，定义在 CREATE TABLE 或 ALTER TABLE，可作用于整行（表级）/ 某列（列级）/ 整 Part 聚合（Group By TTL）。
表级 TTL 的动作有：DELETE / TO DISK 'xxx' / TO VOLUME 'xxx' / RECOMPRESS CODEC(...)。
TTL 不是定时任务，它在两个时机被检查：
1. 后台 Merge 时顺便处理过期数据（默认）。
2. 显式触发：ALTER TABLE t MATERIALIZE TTL 或 OPTIMIZE TABLE t PARTITION p FINAL。
因此，TTL 的"延迟"取决于 Merge 频率。冷分区如果一直没 Merge，过期数据就一直留着 —— 这是常见的"明明设了 TTL 却没删"的根因。

加分项：能讲出 merge_with_ttl_timeout (默认 4 小时) 控制后台 TTL Merge 的最小间隔；能解释列级 TTL 不是删行而是把列置零值；GROUP BY TTL 是把明细 rollup 成聚合，必须配合聚合列定义。

易错点：以为 TTL 像 cron 一样到点就跑 —— 实际上没 Merge 就没 TTL。

Q3：怎么实现 ClickHouse 的冷热分层存储？

考察点：对 storage_policies + TTL 的整合理解。

标准答案：

在 config.xml 中定义多个 disks（不同物理盘 / S3 / HDFS），并把它们组合成 volumes，再把 volumes 编排成 policies。
一个 policy 内部按 volume 从前到后排列 (hot → cold → s3)，写入新 Part 时优先放在第一个 volume；当 volume 用满（max_data_part_size_bytes 或 move_factor 触发），自动溢出到下一个。
建表时用 SETTINGS storage_policy = 'xxx' 关联策略，再用 TTL ... TO DISK 'cold' 或 TTL ... TO VOLUME 'v_s3' 主动驱动搬迁。
监控：system.parts 的 disk_name 列；手动操作：ALTER TABLE t MOVE PART/PARTITION ... TO DISK/VOLUME ...。

加分项：能说 S3 disk 的元数据本地、数据远程的实现细节；S3 适合冷归档，因为延迟高、计费按 GET 数；可以用 <cache> 包一层 S3 disk 加 SSD 缓存。

易错点：以为 TTL TO DISK 可以让数据"半边在 SSD 半边在 HDD" —— 实际上是按 整个 Part 整体迁移。

Q4：为什么不要频繁 OPTIMIZE TABLE ... FINAL？

考察点：对 OPTIMIZE FINAL 代价的认知。

标准答案：

OPTIMIZE FINAL 强制把指定分区（或全表）的所有 active Part 合并成一个，本质是整个分区数据重写一遍。
代价：
1. 大量 IO：50 GB 分区要写 50 GB；
2. CPU 满载：合并、解压、再压缩；
3. 阻塞 Merge / Insert：同分区其他后台任务排队；
4. 临时空间：合并过程中需要 1.5x ~ 2x 磁盘容量；
5. 如果是 Replicated 表，还会同步给副本，放大整个集群压力。
替代方案：
1. ReplacingMergeTree / SummingMergeTree 想看最终态 → 用 SELECT ... FINAL（小代价的查询时合并）或 argMax 模式查询。
2. 想让 TTL 立即生效 → MATERIALIZE TTL（仍重，但精确）。
3. Compaction → 让后台慢慢做，调整 merge_with_ttl_timeout / parts_to_throw_insert。

加分项：能说"如果一定要跑，用 PARTITION 限定范围 + 离线时段 + DEDUPLICATE 可选"。

易错点：以为 OPTIMIZE FINAL 是「轻量优化」 —— 其实它和 PG 的 VACUUM FULL、MySQL 的 OPTIMIZE TABLE 一样重，甚至更重。

Q5：ALTER TABLE DROP PARTITION 为什么这么快？和 DELETE 有什么区别？

考察点：对 ClickHouse 数据组织的理解。

标准答案：

DROP PARTITION 仅改元数据：把目标分区下的所有 active Part 标记为「inactive」，过 old_parts_lifetime 秒（默认 480s）后由后台真正删除文件。所以命令本身几毫秒返回。
DELETE FROM ... WHERE ...（轻量级 DELETE）/ ALTER TABLE ... DELETE（Mutation）需要：
1. 解析谓词
2. 找到所有匹配的 Part
3. 重写每个匹配 Part（生成新 Part，旧 Part 标记 inactive）
4. 等所有 Part 重写完
- 大表上可能跑几小时，且 IO/CPU 占满。
因此「按分区删数据」永远比「按谓词删数据」快几个数量级。所以分区设计要尽量让常见的"按时间删除"能用 DROP PARTITION 完成。

加分项：能补 DETACH PARTITION + DROP DETACHED PARTITION 的两步删法（更安全，可后悔），以及 ATTACH PARTITION FROM 'path' 从外部目录挂载分区做数据恢复。

易错点：把 DROP PARTITION 当 TRUNCATE TABLE 想 —— 它只是删一个分区，不影响其他分区；不会清掉 system.metric_log 等关联记录。

Q6：分区粒度太细会发生什么？怎么观测和处理 Part 爆炸？

考察点：生产事故经验。

标准答案：

症状：

INSERT 报 Too many parts (N). Merges are processing significantly slower than inserts，写入挂起。
查询慢：每次都要打开几千个 Part 的 primary.idx / mark 文件。
system.merges 永远有任务排队。

根因：

分区粒度过细（按小时 / 按 user_id 取模等）。
INSERT 批次太小，每次只写几行 → 每个 INSERT 形成一个 Part。
高并发 Mutation 占用合并通道。

处理：

sql

-- 1. 看活跃 Part 数排名
SELECT database, table, count() AS n
FROM system.parts WHERE active
GROUP BY 1, 2 ORDER BY n DESC LIMIT 10;

-- 2. 看 merges 进度
SELECT * FROM system.merges;

-- 3. 紧急合并某个表 (只能对小表/小分区做)
OPTIMIZE TABLE bad_table PARTITION '202604';

-- 4. 调阈值（治标）
ALTER TABLE bad_table MODIFY SETTING parts_to_throw_insert = 3000;

-- 5. 治本：换分区粒度（重建表 + INSERT SELECT）或改 INSERT 批次

加分项：能解释 parts_to_throw_insert (默认 3000) / parts_to_delay_insert (默认 150) / inactive_parts_to_throw_insert 这几个调控参数；能补 ClickHouse 24.x 后引入的 "ParallelReplicas" 在大量 Part 时的劣势。

易错点：靠调参 parts_to_throw_insert 把阈值拉到 100000，结果合并跟不上，集群直接挂。

📌 下一章预告：第 12 章我们讲 Mutation：UPDATE / DELETE 的真相 —— 为什么 CK 的 UPDATE 和 MySQL 完全不是一回事，轻量级 DELETE 怎么用墓碑列实现，以及为什么"频繁改的表"在 CK 上是反模式。

🎬 可视化演示

演示加载缓慢或样式异常？点此在新标签页打开 ↗

💻 示例代码

ttl_demo.py

python

#!/usr/bin/env python3
"""
第 11 章 · 分区与 TTL 实操脚本

演示:
  1. 灌入跨多月的数据 → 看分区自动拆成多个目录 (system.parts)
  2. 演示分区裁剪 (有/无 WHERE event_date) 的 read_rows 差距
  3. DROP / DETACH / ATTACH PARTITION 的耗时与效果
  4. 用 OPTIMIZE PARTITION FINAL 触发 TTL，观察列级 TTL 把 debug_log 置空
  5. MOVE PART 演示（仅在配置了多盘时生效）

前置:
    1. clickhouse-client --multiquery < ../init.sql
    2. pip install clickhouse-connect

用法:
    python3 ttl_demo.py
    python3 ttl_demo.py --rows 200000
"""
from __future__ import annotations

import argparse
import random
import time
from datetime import date, datetime, timedelta

import clickhouse_connect


def banner(title: str) -> None:
    print("\n" + "=" * 72)
    print(f"  {title}")
    print("=" * 72)


def show_parts(client, table: str, only_active: bool = True) -> None:
    cond = "AND active" if only_active else ""
    sql = f"""
        SELECT partition,
               name,
               disk_name,
               rows,
               formatReadableSize(bytes_on_disk) AS size,
               toString(min_time)::String AS mn,
               toString(max_time)::String AS mx
        FROM system.parts
        WHERE database = 'learn_ck' AND table = '{table}' {cond}
        ORDER BY partition, name
    """
    rs = client.query(sql).result_rows
    if not rs:
        print(f"  (no {'active' if only_active else 'any'} parts in {table})")
        return
    print(f"  {'partition':<10}  {'name':<28}  {'disk':<10}  {'rows':>10}  {'size':>10}  range")
    for r in rs:
        print(f"  {r[0]:<10}  {r[1]:<28}  {r[2]:<10}  {r[3]:>10}  {r[4]:>10}  {r[5]} ~ {r[6]}")


def seed_multi_month(client, total_rows: int) -> None:
    cols = ["event_date", "event_time", "user_id", "event_type", "payload", "debug_log"]
    today = date.today()

    # 跨 6 个月：今天、上月、再上月、... 各塞一部分
    months_back = [0, 30, 60, 95, 130, 200]
    per = max(1, total_rows // len(months_back))

    for back in months_back:
        rows = []
        for _ in range(per):
            d = today - timedelta(days=back + random.randint(0, 5))
            rows.append((
                d,
                datetime(d.year, d.month, d.day, random.randint(0, 23), random.randint(0, 59)),
                random.randint(1, 100_000),
                random.choice(["click", "view", "buy", "share"]),
                "P" * random.randint(50, 200),
                "DEBUG-" + "X" * random.randint(20, 100),
            ))
        client.insert("learn_ck.ttl_events_log", rows, column_names=cols)
        print(f"  inserted {len(rows)} rows for date {today - timedelta(days=back)} (back={back}d)")


def demo_pruning(client) -> None:
    banner("Demo 2: 分区裁剪对比")
    sql_full = "SELECT count() FROM learn_ck.ttl_events_log"
    sql_pruned = (
        "SELECT count() FROM learn_ck.ttl_events_log "
        f"WHERE event_date >= '{(date.today()-timedelta(days=15)).isoformat()}'"
    )
    for label, sql in [("无 WHERE  (全表扫)", sql_full), ("WHERE event_date >= today-15  (裁剪)", sql_pruned)]:
        # query_log 有几秒延迟，所以先 sync 一下
        t0 = time.time()
        cnt = client.query(sql).result_rows[0][0]
        cost = (time.time() - t0) * 1000
        # 取 EXPLAIN ESTIMATE 看预估读 part 数
        est = client.query(f"EXPLAIN ESTIMATE {sql}").result_rows
        print(f"\n  {label}")
        print(f"    SQL    : {sql}")
        print(f"    rows   : {cnt}")
        print(f"    cost   : {cost:.1f} ms")
        print(f"    EXPLAIN ESTIMATE:")
        for row in est:
            print(f"      {row}")


def demo_partition_ops(client) -> None:
    banner("Demo 3: DROP / DETACH / ATTACH PARTITION")
    parts = client.query(
        "SELECT DISTINCT partition FROM system.parts "
        "WHERE database='learn_ck' AND table='ttl_events_log' AND active "
        "ORDER BY partition"
    ).result_rows
    if len(parts) < 2:
        print("  分区不足 2 个，跳过该 demo")
        return

    # 选最老的一个分区演示
    oldest = parts[0][0]
    print(f"\n  选定最老分区：{oldest}")

    # DETACH
    t0 = time.time()
    client.command(f"ALTER TABLE learn_ck.ttl_events_log DETACH PARTITION '{oldest}'")
    print(f"  DETACH PARTITION '{oldest}'  cost={1000*(time.time()-t0):.1f}ms")

    # 看 detached
    det = client.query(
        "SELECT partition_id, name FROM system.detached_parts "
        f"WHERE database='learn_ck' AND table='ttl_events_log' AND partition_id = '{oldest}'"
    ).result_rows
    print(f"  detached_parts: {det}")

    # ATTACH 回来
    t0 = time.time()
    client.command(f"ALTER TABLE learn_ck.ttl_events_log ATTACH PARTITION '{oldest}'")
    print(f"  ATTACH PARTITION '{oldest}'  cost={1000*(time.time()-t0):.1f}ms")

    # 演示 DROP（用最老的分区，假装不要了）
    if len(parts) >= 3:
        victim = parts[0][0]
        t0 = time.time()
        client.command(f"ALTER TABLE learn_ck.ttl_events_log DROP PARTITION '{victim}'")
        print(f"  DROP PARTITION '{victim}'  cost={1000*(time.time()-t0):.1f}ms (毫秒级!)")


def demo_ttl(client) -> None:
    banner("Demo 4: 强制 OPTIMIZE 触发 TTL，看 debug_log 列被清空")

    # 先看一行老数据
    sample = client.query(
        "SELECT event_date, length(debug_log) FROM learn_ck.ttl_events_log "
        "WHERE event_date < today() - 7 LIMIT 1"
    ).result_rows
    if not sample:
        print("  没有 7 天前的数据，跳过")
        return
    print(f"  TTL 触发前: event_date={sample[0][0]}, len(debug_log)={sample[0][1]}")

    # OPTIMIZE 触发 TTL
    parts = client.query(
        "SELECT DISTINCT partition FROM system.parts "
        "WHERE database='learn_ck' AND table='ttl_events_log' AND active "
        "ORDER BY partition LIMIT 1"
    ).result_rows
    if parts:
        p = parts[0][0]
        t0 = time.time()
        client.command(f"OPTIMIZE TABLE learn_ck.ttl_events_log PARTITION '{p}' FINAL")
        print(f"  OPTIMIZE PARTITION '{p}' FINAL  cost={1000*(time.time()-t0):.0f}ms")

    after = client.query(
        "SELECT event_date, length(debug_log) FROM learn_ck.ttl_events_log "
        "WHERE event_date < today() - 7 LIMIT 1"
    ).result_rows
    if after:
        print(f"  TTL 触发后: event_date={after[0][0]}, len(debug_log)={after[0][1]}  (列被置空)")


def main() -> None:
    parser = argparse.ArgumentParser()
    parser.add_argument("--host", default="127.0.0.1")
    parser.add_argument("--port", type=int, default=8123)
    parser.add_argument("--user", default="default")
    parser.add_argument("--password", default="")
    parser.add_argument("--rows", type=int, default=120_000, help="总灌入行数")
    parser.add_argument("--skip-seed", action="store_true")
    args = parser.parse_args()

    client = clickhouse_connect.get_client(
        host=args.host, port=args.port,
        username=args.user, password=args.password,
    )

    if not args.skip_seed:
        banner("Demo 1: 灌入跨多月数据 + 看分区自动拆")
        client.command("TRUNCATE TABLE learn_ck.ttl_events_log")
        seed_multi_month(client, args.rows)
        time.sleep(1)

    print("\n  现在的 active parts (注意每个月一个 partition 目录):")
    show_parts(client, "ttl_events_log")

    demo_pruning(client)
    demo_partition_ops(client)
    demo_ttl(client)

    banner("最终状态")
    show_parts(client, "ttl_events_log")
    print("\n看完后可以手动跑：")
    print("  ALTER TABLE learn_ck.ttl_events_log MOVE PART '<part_name>' TO DISK 'cold';")
    print("  (前提：服务端配置了名为 cold 的 disk)")


if __name__ == "__main__":
    main()

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215

ttl_demo.py ↗

第 11 章 TTL、分区与数据生命周期 ​

11.0 一句话总览 ​

11.1 分区是什么：图书馆按月份归档报刊 ​

11.1.1 分区表达式怎么选 ​

11.1.2 分区粒度的取舍：不是越细越好 ​

11.2 分区裁剪：WHERE 命中分区键就能跳过整个目录 ​

11.3 分区操作六件套 ​

11.4 TTL：让数据自动「到期就办手续」 ​

11.4.1 三种 TTL 形态 ​

11.4.2 表级 TTL 的四种动作 ​

11.4.3 列级 TTL：让"非核心列"先消失 ​

11.4.4 GROUP BY TTL：到期把明细 rollup 成聚合 ​

11.4.5 强制立即应用 TTL ​

11.5 冷热分层存储：storage_policies ​

11.5.1 storage_policies 配置示例 ​

11.5.2 建表关联策略 ​

11.5.3 数据搬迁观察 ​

11.5.4 手动搬迁 ​

11.6 OPTIMIZE TABLE ... PARTITION xxx FINAL 的代价 ​

11.7 📌 与 MySQL / PG 分区表的对比小框 ​

11.8 本章小结 ​

11.9 面试高频题 ​

Q1：ClickHouse 的 PARTITION BY 怎么选？粒度怎么定？ ​

Q2：TTL 是什么？什么时候真正触发？ ​

Q3：怎么实现 ClickHouse 的冷热分层存储？ ​

Q4：为什么不要频繁 OPTIMIZE TABLE ... FINAL？ ​

Q5：ALTER TABLE DROP PARTITION 为什么这么快？和 DELETE 有什么区别？ ​

Q6：分区粒度太细会发生什么？怎么观测和处理 Part 爆炸？ ​

🎬 可视化演示 ​

💻 示例代码 ​

第 11 章 TTL、分区与数据生命周期

11.0 一句话总览

11.1 分区是什么：图书馆按月份归档报刊

11.1.1 分区表达式怎么选

11.1.2 分区粒度的取舍：不是越细越好

11.2 分区裁剪：WHERE 命中分区键就能跳过整个目录

11.3 分区操作六件套

11.4 TTL：让数据自动「到期就办手续」

11.4.1 三种 TTL 形态

11.4.2 表级 TTL 的四种动作

11.4.3 列级 TTL：让"非核心列"先消失

11.4.4 GROUP BY TTL：到期把明细 rollup 成聚合

11.4.5 强制立即应用 TTL

11.5 冷热分层存储：`storage_policies`

11.5.1 storage_policies 配置示例

11.5.2 建表关联策略

11.5.3 数据搬迁观察

11.5.4 手动搬迁

11.6 `OPTIMIZE TABLE ... PARTITION xxx FINAL` 的代价

11.7 📌 与 MySQL / PG 分区表的对比小框

11.8 本章小结

11.9 面试高频题

Q1：ClickHouse 的 PARTITION BY 怎么选？粒度怎么定？

Q2：TTL 是什么？什么时候真正触发？

Q3：怎么实现 ClickHouse 的冷热分层存储？

Q4：为什么不要频繁 OPTIMIZE TABLE ... FINAL？

Q5：ALTER TABLE DROP PARTITION 为什么这么快？和 DELETE 有什么区别？

Q6：分区粒度太细会发生什么？怎么观测和处理 Part 爆炸？

🎬 可视化演示

💻 示例代码