我把 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模板、迁移脚本、监控告警规则都整理出来了有需要可以找我拿。

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

相关推荐

卫星传感器信号怎么放大才不失真？抗辐射运放选型

飞算JavaAI协作开发实录：让链途智慧仓储供应链协同平台跑起来

Tiny Guide to Non Fancy Node API设计：merry框架的实用哲学

更多资讯

impeccable Overdrive 命令深度解析：用浏览器全部能力把界面推到“超常“之境

告别 matiec 与 Docker：aiDgePLC Editor 全面拥抱 STVM 字节码编译

305B多模态大模型本地部署实战：量化+llama.cpp消费级显卡跑起来

别被宣传话术裹挟：盘点市面上值得一试的论文去AI痕迹工具

nlohmann::json 的 is_null() 用法解析：如何判断 JSON 值是否为 null

硬件工程师必须掌握的热设计：从功耗计算到结温控制E03

今日更新

能源系统对接碳交易的路径：从碳核算到碳资产运营

智能硬件数据驱动运营：订单日记与预测性维护实践

逆变器下垂控制与虚拟同步机对比：Simulink仿真建模与参数整定

本周精选

超人会飞不算本事：系统稳定依赖清晰规则与边界设计

超人VS蜘蛛侠：拆解超级IP的影响力与传播方法论

基于CNN的调制信号识别：MATLAB实现时频图分类实战

本月热门

基于YOLO的焊缝缺陷检测：从数据标注到工业部署全流程实践

分布式服务的决策记录

创业产品的首版范围

读完文章，想聊聊您的网站？