📚 课程目录第4章数据建模

4. 数据建模

在ClickHouse中做金融时序数据平台，表结构设计不是“建一张表”那么简单，而是把查询模式、写入模式、存储成本与数据生命周期四件事一次性对齐。金融时序数据通常具备高吞吐写入、按时间范围查询、按标的聚合、历史数据冷热分层等特征，因此建模的核心是：用分区键控制数据裁剪，用排序键控制索引与扫描效率，用主键控制去重与稀疏索引，用TTL控制数据生命周期。

核心原理：四件套各司其职

ClickHouse的MergeTree家族表引擎中，分区键、排序键、主键和TTL共同决定数据在磁盘上的组织方式。分区键决定数据被切分成多少个“分区目录”，查询时能直接跳过无关分区；排序键决定分区内数据按什么顺序排列，直接影响稀疏索引的过滤效率；主键是排序键的前缀，用于构建稀疏索引并支持去重；TTL则定义数据在何时被删除、移动到冷存储或聚合降采样。

关键关系：主键必须是排序键的前缀；排序键必须是分区键中表达式可用的列组合；TTL可以作用于列、分区或整表。四者顺序设计错误，会导致查询扫描量放大数倍甚至数十倍。

方法流程：从查询模式反推表结构

金融时序建模不能先写DDL再想查询，而应遵循“查询模式 → 分区粒度 → 排序键顺序 → 主键前缀 → TTL策略”的流程。以下以A股逐笔成交表为例：

明确查询模式：最常见查询是“某标的某日全部成交”“某标的某时间段成交”“某日全市场成交额聚合”。
选择分区粒度：按toYYYYMM(trade_date)月分区，兼顾分区数量与裁剪效率；若数据量极大可按日分区。
设计排序键：将等值过滤列放前，范围过滤列放后，即(symbol, trade_date, ts)。
确定主键：取排序键前缀(symbol, trade_date)，既保证稀疏索引有效，又支持ReplacingMergeTree按标的和日期去重。
配置TTL：热数据保留3个月，温数据12个月，冷数据3年后删除，并配合TO VOLUME实现冷热分层。

CREATE TABLE market.tick_trade
(
    symbol        LowCardinality(String),
    trade_date    Date,
    ts            DateTime64(3),
    price         Decimal(18, 4),
    volume        UInt64,
    amount        Decimal(24, 4),
    exchange      LowCardinality(String),
    ingest_time   DateTime DEFAULT now()
)
ENGINE = ReplacingMergeTree(ingest_time)
PARTITION BY toYYYYMM(trade_date)
ORDER BY (symbol, trade_date, ts)
PRIMARY KEY (symbol, trade_date)
TTL trade_date + INTERVAL 3 MONTH TO VOLUME 'hot',
    trade_date + INTERVAL 12 MONTH TO VOLUME 'warm',
    trade_date + INTERVAL 3 YEAR DELETE
SETTINGS index_granularity = 8192;

案例场景：逐笔成交与分钟K线双表设计

实际平台中，逐笔成交与分钟K线的查询模式差异很大。逐笔表强调“按标的+时间范围”扫描，排序键以symbol开头；分钟K线表强调“按时间+标的”聚合，且常按交易日全市场扫描，排序键可设计为(trade_date, symbol, minute)。两者分区键都可用月分区，但TTL策略不同：逐笔数据量大、价值衰减快，保留3个月热数据即可；分钟K线可保留3年用于回测。

表类型	分区键	排序键	主键	TTL策略
逐笔成交	toYYYYMM(trade_date)	(symbol, trade_date, ts)	(symbol, trade_date)	3月转冷，3年删除
分钟K线	toYYYYMM(trade_date)	(trade_date, symbol, minute)	(trade_date, symbol)	1年转冷，5年删除
日频指标	toYYYY(calc_date)	(symbol, calc_date)	(symbol, calc_date)	永久保留，按年归档

误区与复盘：常见建模陷阱

误区一：分区键粒度过细。按toYYYYMMDD日分区，一年产生365个分区，元数据膨胀，小分区合并压力大。建议月分区或周分区。

误区二：排序键顺序颠倒。把ts放在symbol前面，导致按标的查询无法利用稀疏索引，扫描量剧增。

误区三：主键与排序键不一致。主键不是排序键前缀时，ClickHouse会报错或索引失效，必须保证前缀关系。

误区四：TTL只写DELETE不写TO VOLUME。冷热分层缺失，所有数据挤在本地盘，成本与性能双输。

复盘建议：建表后用EXPLAIN indexes = 1验证查询是否命中分区裁剪与主键索引；用system.parts观察分区数量与大小；用system.moves确认TTL移动是否按预期执行。建模不是一次性的，应随查询模式变化定期复盘调整。

总结来看，金融时序表结构设计的本质是：分区键管“跳过多少数据”，排序键管“扫描多快”，主键管“索引多准”，TTL管“留多久、放哪里”。四者协同，才能在写入吞吐、查询延迟与存储成本之间取得平衡。