ClickHouse Schema 建模最佳实践：主键、分区、压缩与宽窄表设计

核心目标：
□ 查询读得少：分区 + 主键裁剪（扫描量最小）
□ 存储省：类型、压缩、编码（每列都省一点）
□ 写入快：主键列数、part 控制（别拖累插入）
□ 演进稳：能改表、能迁移、不堵查询

查询模式驱动（Design for Query）：
□ 先列出「高频查询长什么样」
  → 过滤列（WHERE）、分组列（GROUP BY）、聚合列
□ 再倒推 Schema：
  → 过滤列 → 分区/主键
  → 分组列 → 主键/物化视图
  → 聚合列 → 预聚合/压缩

方法论：
□ 反范式：OLAP 不用像 OLTP 一样规约
  → 冗余字段（宽表）是常态（为了少 join）
□ 少 join：能冗余就冗余（字典关联除外）
□ 先建模再建表：改表比建表贵（尤其大表）

设计步骤：
1. 列高频查询（过滤/分组/聚合）
2. 选类型（省存储）
3. 定主键（前缀匹配）
4. 定分区（裁剪粒度）
5. 定压缩/编码（列级）
6. 定引擎 + 预聚合

查询 → Schema 推导：
WHERE event_time, user_id → 分区(时间) + 主键(时间, user)
GROUP BY channel         → 主键前缀 / 物化视图
sum(amount) 高频          → 预聚合表
→ 反向设计，Schema 服务查询

类型选择原则：
□ 用最小能装的类型（整数 vs 字符串 vs 浮点）
□ 优先数值类型（列式压缩更好）
□ 枚举（Enum）/低基数列 → 字典编码更省
□ 时间用 DateTime/Datetime64（别用 String）

数值类型：
□ 整数：UInt8/16/32/64 按量级选
  → 能 UInt16 别用 UInt32
□ 浮点：Float32/64（注意精度）
□ Decimal：金额/精度敏感（别用 Float 存钱）
□ 布尔：UInt8（0/1）

字符串 vs 编码：
□ 低基数字符串 → Enum / LowCardinality（省 + 快）
□ 高基数（ID）→ 数值化（字符串转数字）
□ 别把「数值型 ID」存成 String（又大又慢）

时间类型：
□ DateTime（秒）/ Datetime64（亚秒）
□ 别用 String 存时间（无法做范围裁剪优化）
□ 时区处理：统一存储 UTC，展示层转换

错误的代价：
□ String 存 ID → 大几倍 + 裁剪失效
□ Float 存金额 → 精度错误
□ 大类型存小值 → 存储翻倍

-- 类型优化示例
event_time DateTime,                    -- 不要 String
user_id UInt64,                         -- 不要 String
channel LowCardinality(String),         -- 低基数省
status Enum('active'=1, 'closed'=2),    -- 枚举省
amount Decimal(18,2),                   -- 金额精度

主键的本质：
□ ORDER BY 决定数据物理排序
□ 生成稀疏索引（每 N 行一个标记）
□ 查询命中「主键前缀」→ 跳过大量数据块

前缀匹配原则：
□ 查询 WHERE 用了「主键的前几个列」→ 索引生效
□ 高基数列放前面（能更快缩小范围）
□ 过滤列顺序 = 查询 WHERE 的常见组合

主键设计要点：
□ 别堆太多列（每列加索引体积 + 插入开销）
□ 前缀越短越常被命中越好
□ 范围查询（时间）放前面 → 时间裁剪
□ 维度查询（user）跟着时间 → user 过滤

常见错误：
□ 把「唯一 ID」当主键 → 每行一个值，索引无裁剪价值
  → 排序键应该按「查询过滤模式」，不是唯一性
□ 主键列过多 → 索引膨胀、插入变慢
□ 高基数列放后面 → 前缀命中率低

设计范例：
□ 日志表：ORDER BY (event_time, event_type, user_id)
  → WHERE event_time 范围 + event_type 过滤 → 全命中
□ 维表：ORDER BY (key, ...) 按查询键

-- 好的排序键：查询前缀命中
ORDER BY (event_time, user_id)
-- WHERE event_time >= ...            → 命中（前缀）
-- WHERE event_time >= ... AND user_id = ... → 命中（更长前缀）
-- WHERE user_id = ...（无时间）       → 不命中（跳过前缀）

分区的作用：
□ PARTITION BY 把数据按键切块
□ 查询 WHERE 含分区键 → 跳过无关分区
□ 也决定：part 数量、TTL 粒度、合并粒度

分区粒度的权衡：
□ 太粗（全年一个分区）→ 裁剪失效，扫全表
□ 太细（每小时一个分区）→ 大量小分区
  → 小文件多、合并压力大、查询碎片化
□ 平衡：按「查询时间粒度 + 数据量」定

分区键选择：
□ 时间是最常用分区键（按天/月）
  → 查询常带时间范围 → 时间裁剪收益最大
□ 其他维度（渠道/地域）→ 需查询常过滤且基数适中
□ 多级分区：PARTITION BY (toYYYYMM(ts), channel) 少用（粒度翻倍）

分区 vs 主键的配合：
□ 分区做「粗裁剪」（时间块）
□ 主键做「细裁剪」（块内定位）
□ 两者都匹配查询 → 扫描量最小

分区数控制：
□ 单表分区数「适度」（几十~几百，勿上万）
□ 数据量小 → 分区别太细（浪费）
□ 数据量极大 → 按天甚至按小时

-- 分区设计示例
PARTITION BY toYYYYMM(event_time)   -- 按月（月查询/月归档）
-- 或按天（日查询/日 TTL）：
PARTITION BY toYYYYMMDD(event_time)
-- 查询 WHERE event_time 在月内 → 只扫当月分区

压缩策略选择：
□ 默认 LZ4：快（解压快 → 查询快），压缩比一般
□ ZSTD：高压缩比（省磁盘），解压 CPU 略高
□ 选型：热查询用 LZ4（快）、冷归档用 ZSTD（省）
  → 列级可单独设置 CODEC

列编码（CODEC）：
□ 按列特性选编码：
  → 低基数 → 字典编码（枚举/重复值）
  → 单调递增 → Delta（时间戳、自增）
  → 数值 → 组合（Delta + ZSTD）
□ 默认压缩不够时，针对性加编码

哪些列值得压缩：
□ 大字符串/重复值 → 高收益
□ 数值列 → Delta + 压缩
□ 时间戳 → Delta（相邻差值小）

收益衡量：
□ 压缩比：原始 / 压缩后（越大越省）
□ 存储省 → 磁盘成本降
□ IO 省 → 读更少字节 → 查询快（尤其列裁剪后）
□ 代价：写入/解压 CPU 开销

实践建议：
□ 默认：大部分列 LZ4 就好
□ 冷数据 / 大字段：ZSTD
□ 时间/自增列：Delta CODEC
□ 低基数：字典（LowCardinality 类型自带）

-- 列级压缩与编码示例
event_time DateTime CODEC(DoubleDelta, ZSTD),  -- 时间戳差分
user_id UInt64 CODEC(ZSTD),                     -- 压缩
channel LowCardinality(String),                 -- 低基数编码
message String CODEC(ZSTD),                     -- 大字段高压缩

引擎速查（详见表引擎篇）：
□ MergeTree：默认主力（明细 + 范围查询 + TTL）
□ AggregatingMergeTree：预聚合（sumState/countState）
□ SummingMergeTree：同键求和（计数类指标）
□ ReplacingMergeTree：去重（按排序键取最后）
□ Collapsing/Versioned：数据变更/状态快照
□ Distributed：分布式写入/查询入口
□ Kafka / S3 / MySQL 等：外部数据接入

选择逻辑：
□ 明细可追加 → MergeTree
□ 要预聚合 → Aggregating/Summing（配合物化视图）
□ 要去重 → Replacing/去重表
□ 要状态/更新 → Collapsing/Versioned
□ 要分布式入口 → Distributed 包一层

搭配：
□ 同源多表：明细表 + 物化视图聚合表（见预聚合篇）
□ 冷热：存储策略 + TTL（见 S3 篇）

常见误选：
□ 全表都用默认 MergeTree（没利用预聚合/去重）
□ 用 Collapsing 处理去重（语义复杂，优先 Replacing）
□ 外部数据场景忘了 Kafka/S3 引擎（多跳一步）

引擎选择流：
明细追加 → MergeTree
高频聚合 → + Aggregating/Summing
有重复 → + Replacing
状态更新 → Collapsing/Versioned
外部数据 → Kafka/S3 引擎
→ 别默认 MergeTree 到底

宽表（大宽表）：
□ 一行包含所有维度 + 指标（冗余）
□ 优点：查询少 join（OLAP 核心诉求）、快
□ 代价：字段冗余（存储增）、更新/重建复杂
□ 适合：固定报表、看板、维度少且稳定

窄表（细粒度 + 外键）：
□ 明细字段 + 维度外键
□ 优点：存储省、灵活（join 维度）
□ 代价：查询要 join（慢）、实时 join 复杂
□ 适合：灵活分析、维度多且变化

ClickHouse 的倾向：
□ OLAP 反范式 → 倾向宽表（少 join 是王道）
□ 但「无脑大宽表」也坑：字段太多、更新难、列裁剪失效
□ 折中：核心宽表 + 维度 Dictionary（把 join 变字典）

宽窄决策：
□ 查询固定 → 宽表（预聚合 + 宽字段）
□ 查询多变 → 窄表 + 字典关联
□ 维度稳定 → 宽表（冗余固化）
□ 维度频繁变 → 窄表 + 字典（避免改宽表）

实践原则：
□ 明细表保留「够用」的宽字段（少下游 join）
□ 聚合表按主题宽表（一行一维度组合）
□ 维度表 → Dictionary 内存关联

宽窄选择：
固定报表 → 宽表（少 join 快）
灵活分析 → 窄表 + 字典
维度稳定 → 宽表；维度多变 → 窄表
→ 核心指标宽表、长尾查询窄表

支持的复杂类型：
□ 数组（Array）：Array(T) 多值
□ 嵌套（Nested）：嵌套结构（等价数组的数组）
□ Map / Tuple：键值对 / 元组
□ JSON 列：存原始 JSON（但查询不便，见 JSON 篇）

数组 / 嵌套的正确用法：
□ 多值属性（标签、列表）→ Array
□ 结构化子记录（子事件）→ Nested
□ 展开：arrayJoin 把数组摊平成行（查询时）

使用注意：
□ 别把「本应是行」的数据塞进数组（查询展开反而复杂）
□ Nested 不是「对象」，是「列的数组」→ 别当对象存
□ 数组大小控制（过大 → 存储/查询都重）

典型场景：
□ 标签数组：Array(String)（低基数列表）
□ 事件明细：Nested（子事件序列）
□ 指标历史：Array(Float64)（滚动序列）
□ 属性字典：Map(String, String)

与规整表的选择：
□ 数据「本来就是数组」→ 用 Array/Nested（自然）
□ 数据「本是独立行」→ 用明细行（别强塞数组）
→ 按数据的自然形态建模

-- 数组与嵌套示例
tags Array(String),                                -- 标签列表
events Nested(                                    -- 子事件序列
  event_time DateTime,
  event_type String
),
-- 查询展开
SELECT arrayJoin(tags) AS tag, count() FROM t GROUP BY tag;

建模反模式（别踩）：
□ 主键堆太多列 → 索引胖 + 写入慢
□ 分区过细 → 小文件爆炸
□ 类型随意（ID 存 String）→ 存储翻倍 + 裁剪失效
□ 无脑大宽表 → 更新难 + 列多裁剪失效
□ 每列都 ZSTD → 解压 CPU 拖累查询
□ 默认 MergeTree 到底 → 错过预聚合/去重

改表（ALTER）：
□ 加列：ADD COLUMN（轻量，加列快）
□ 删列：DROP COLUMN（有重写成本）
□ 改类型：MODIFY COLUMN（大表慢，重写列）
□ 改主键：MODIFY ORDER BY（大表重写数据！）
  → 改排序键 = 全表重排序（昂贵，谨慎）
□ TTL/分区变更：同样涉及重写/迁移

演进策略：
□ 建模时就「留演进空间」：
  → 加列是常态（列式加列便宜）→ 别怕少字段
  → 改主键/分区是大事 → 提前想好
□ 大表迁移：
  → 建新表 → 批量灌入 → 原子切换（RENAME）
  → 或分区级迁移（老分区逐步）
□ 版本化：字段名带版本 / 预留通用字段（少用）

大表变更铁律：
□ 先小表验证，再大表执行
□ 变更前备份（见备份篇）
□ 变更窗口 + 监控（重写期间资源占用）

演进决策：
加列 → 直接 ADD（便宜）
改类型/删列 → 评估重写成本
改主键/分区 → 全表重写（谨慎，走迁移流程）
→ 加列常态、改主键慎动