我把 ClickHouse 冷热数据分层做完后，查询 P99 从 6s 压到 80ms：分区策略 + 冷热表引擎实战

我把 ClickHouse 冷热数据分层做完后查询 P99 从 6s 压到 80ms分区策略 冷热表引擎实战搞了 4 天总算把 ClickHouse 的冷热分层跑顺了。我们有个用户行为分析系统数据量大概 800 亿条单表跑了一年多。最近老板要做近 30 天实时分析 历史趋势回顾这种场景。结果 P99 飙到 6s业务方天天催。我一开始想的是加机器、加 SSD但 D 跟我说预算只够你买两块 4T HDD。得卷冷热分层吧。一开始为什么慢我们原来的表结构很简单CREATETABLEevents(event_timeDateTime,user_id UInt64,event_type String,payload String)ENGINEMergeTree()PARTITIONBYtoYYYYMM(event_time)ORDERBY(user_id,event_time);按月分区主键(user_id, event_time)。看起来挺合理对吧问题就出在这个payload String上。我们 payload 里塞的是 JSON长的有 5KB短的 200B。MergeTree是按ORDER BY排序的列存但payload不在主键里CH 对它的处理是按主键聚簇后再附加——也就是说每次按 user_id 范围扫数据时CH 要去拉一坨大字段。更要命的是90% 的查询“近 30 天”只需要前 30 天的数据但 CH 在扫描的时候会把所有月份的 part 都打开只是过滤掉大部分。这种宽扫在数据量上亿之后非常明显。我用system.parts看了下SELECTpartition,name,rows,bytes_on_diskFROMsystem.partsWHEREtableeventsANDactiveORDERBYpartitionDESCLIMIT10;结果吓一跳3 个月前的数据占了总磁盘的 73%但查询量只有 4%。这就是典型的冷热数据混在一起。第一步把冷热数据拆到两张表我决定按时间把数据拆成两张表events_hot近 90 天用 SSD 存events_cold90 天之前用 HDD 存拆分 SQL-- 1. 新建热表迁到 SSD 盘CREATETABLEevents_hotASeventsENGINEMergeTree()PARTITIONBYtoDate(event_time)ORDERBY(user_id,event_time)TTL toDate(event_time)INTERVAL90DAYSETTINGS storage_policyhot_policy;-- 2. 新建冷表存 HDDCREATETABLEevents_coldASeventsENGINEMergeTree()PARTITIONBYtoYYYYMM(event_time)ORDERBY(user_id,event_time)SETTINGS storage_policycold_policy;storage_policy是在config.xml里定义的卷组storage_configurationdiskssdd1path/mnt/ssd/clickhouse//path/sdd1hdd1path/mnt/hdd/clickhouse//path/hdd1/diskspolicieshot_policyvolumeshotdisksdd1/disk/volumes/hot_policycold_policyvolumescolddiskhdd1/disk/volumes/cold_policy/policies/storage_configuration冷热表用Distributed引擎合并起来CREATETABLEevents_allASeventsENGINEDistributed(cluster,analytics,,rand());-- 视图层做路由CREATEVIEWeventsASSELECT*FROMevents_hotUNIONALLSELECT*FROMevents_cold;这样业务侧查询完全无感知。冷数据迁移我用了一个简单的脚本importclickhouse_driver clientclickhouse_driver.Client(localhost)# 按月分区搬运months[2026-01,2026-02,2026-03,2026-04]forminmonths:print(f迁移{m}...)client.execute(f INSERT INTO events_cold SELECT * FROM events_all WHERE toYYYYMM(event_time) {m} )# 验证行数一致后再 ALTERcnt_srcclient.execute(fSELECT count() FROM events_all WHERE toYYYYMM(event_time) {m})[0][0]cnt_dstclient.execute(fSELECT count() FROM events_cold WHERE toYYYYMM(event_time) {m})[0][0]ifcnt_srccnt_dst:# 物理删除源数据client.execute(fALTER TABLE events_hot DROP PARTITION {m})else:print(f!!! 行数不一致:{cnt_src}vs{cnt_dst})第一阶段做完后热表从 1.2TB 缩到 180GB只剩 90 天数据冷表 700GB 丢到 HDD 上。P99 立刻从 6s 降到 2.1s。但还不够。第二步按天分区 冷热合并CH 的 part 文件是按分区切的按月分区意味着一个 part 里塞了 30 天的数据。当我们只查最近 7 天时CH 还是会打开所有月份 part然后 filter 掉大部分。我改成按天分区TRUNCATETABLEevents_hot;CREATETABLEevents_hot(event_timeDateTime,user_id UInt64,event_type LowCardinality(String),payload String)ENGINEMergeTree()PARTITIONBYtoDate(event_time)ORDERBY(user_id,event_time)TTL toDate(event_time)INTERVAL90DAYTOVOLUMEcoldSETTINGS storage_policytiered_policy;重点来了——我把storage_policy改成了tiered_policy在 hot 表内部就用 TTL 自动把数据从 SSD 卷滚到 HDD 卷policiestiered_policyvolumeshotdisksdd1/diskmax_data_part_size_bytes1073741824/max_data_part_size_bytes/hotcolddiskhdd1/disk/cold/volumes/tiered_policy/policies这样一张表就能实现冷热自动迁移。CH 会在后台把超过 90 天的 part 物理迁移到 HDD业务方完全无感。我合并后只保留一张表DROPTABLEevents_cold;RENAMETABLEevents_hotTOevents;清爽多了。按天分区后查近 7 天时 CH 只打开 7 个 part 而不是 3 个。P99 进一步降到 800ms。第三步payload 拆开冷热分层已经把宽扫问题解决了一半。剩下的一半是payload字段。我看了下慢查询的 profileSELECTquery_id,ProfileEvents.Values[indexOf(ProfileEvents.Names,SelectedMarks)],ProfileEvents.Values[indexOf(ProfileEvents.Names,SelectedRanges)]FROMsystem.query_logWHEREtype!QueryStartANDquery_duration_ms1000ORDERBYquery_duration_msDESCLIMIT10;SelectedMarks高得离谱。说明 CH 在 mark 过滤阶段被 payload 拉慢了。payload不在主键里但MergeTree是按ORDER BY列聚簇的其它列的物理存储顺序是跟着主键走的。我读user_id范围时CH 要把对应的payload也读出来即使我用SELECT user_id, count()也躲不过——因为 mark file 是按主键 所有列组织的。解法是把payload拆出去用Nested或者直接走JOIN-- 主表只保留查询列CREATETABLEevents_main(event_timeDateTime,user_id UInt64,event_type LowCardinality(String))ENGINEMergeTree()PARTITIONBYtoDate(event_time)ORDERBY(user_id,event_time)TTL toDate(event_time)INTERVAL90DAYTOVOLUMEcoldSETTINGS storage_policytiered_policy;-- payload 放副表CREATETABLEevents_payload(event_timeDateTime,user_id UInt64,payload String)ENGINEMergeTree()PARTITIONBYtoDate(event_time)ORDERBY(user_id,event_time)TTL toDate(event_time)INTERVAL30DAYDELETE-- 30 天后删SETTINGS storage_policytiered_policy;主表只放 3 列副表放 payload30 天就过期业务方说 30 天够用了。聚合查询现在只读主表-- 这种查询 80ms 跑完SELECTuser_id,count()AScntFROMevents_mainWHEREevent_timenow()-INTERVAL7DAYANDevent_typeclickGROUPBYuser_idORDERBYcntDESCLIMIT100;要查 payload 的时候才 JOINSELECTm.user_id,m.cnt,p.payloadFROM(SELECTuser_id,count()AScntFROMevents_mainWHEREevent_timenow()-INTERVAL7DAYGROUPBYuser_id)mLEFTJOINevents_payload pUSING(user_id,event_time)WHEREm.cnt10LIMIT100;JOIN 走主键本地 join 很快。最终数据阶段P99表数主表大小原始单表 800 亿6.0s11.2 TB冷热分表2.1s2180 GB 700 GB按天分区 TTL 滚动800ms1180 GB hot 700 GB cold自动迁移payload 拆出去80ms245 GB hot 50 GB cold业务侧反馈实时看板的卡顿感消失了趋势分析能下钻到小时级老板在群里说这才像现代数仓。写在最后回头看整个过程核心思路就一句话别让冷数据挤占热查询的 I/O 通道。ClickHouse 的冷热分层有几个容易踩的坑storage_policy必须先在config.xml里建好再创建表否则会报UNKNOWN_STORAGE_POLICYTTL 滚动是异步的第一次配置完可能要等几小时数据才开始迁移按天分区会增加 part 数量900建议至少按周分区监控system.parts拆字段后JOIN要走主键顺序否则退化成 ALL JOIN最大的教训是CH 的性能问题 90% 都和宽扫 冷数据混存有关。在你考虑加机器、调参数之前先看下你的数据是真的都用得上还是历史包袱。如果你们也在搞 ClickHouse 冷热分层欢迎评论区交流。我把config.xml模板、迁移脚本、监控告警规则都整理出来了有需要可以找我拿。