从重复扫描到分钟汇总:ClickHouse 监控页面的分层性能优化
一个监控页面变慢,不能只用“数据有几亿行”解释,也不能一上来就分库分表。这次优化沿着四层展开:先消除 SQL 自己制造的中间结果,再合并重复查询,用分钟汇总承接高频统计,最后取消前端已经不需要的请求。高密度隔离样本中的六组 SQL 快约 3.39~11.20 倍,但低密度样本里有查询反而变慢;这两个结果同样重要。
🎧 文章导读
先界定问题:几亿行不是分库分表的充分条件
这次改造的是某用户体验监控项目的主区域,包括总览、性能趋势、错误趋势和操作失败统计。现场主要表的快照如下,行数表示采集事件记录,不是真实用户数,也不能把多张表相加后当成独立事件数。
| 表 | 当时行数 | 占用 |
|---|---|---|
ty_trace_raw |
3.84 亿 | 53.1 GB |
brs_ajax_data |
4.86 亿 | 26.7 GB |
brs_ajax_trace_data |
6235 万 | 12.3 GB |
brs_jserr_trace_data |
1021 万 | 12.2 GB |
brs_page_data |
9618 万 | 4.95 GB |
监控系统持续产生事件本来就是业务特征。是否需要扩容或分片,要看每日增量、保留周期、单次查询扫描量、并发、写入吞吐和机器资源,而不是只看总行数。原始表已经按天分区,但“同一张表内按天分区”和“跨节点分片”是两回事。
还有一个容易被忽略的背景:现场当时只剩约两天高峰期数据,是此前因为查询慢而持续删除历史的结果,并不是经过确认的长期保留策略。优化可以减少以后的扫描压力,却不能恢复已经删除的历史。上线初期的长周期统计与环比缺失,必须先按数据缺失去解释,而不是当成业务指标下降。
因此,本轮目标不是重做存储架构,而是回答一个更具体的问题:在保留原始明细和现有统计口径的前提下,页面统计能不能少做无效工作、少读数据?
第一层:先消除 SQL 自己制造的中间结果
总览性能同时需要页面加载指标和网络请求指标。旧查询直接组合两张分钟表,却没有用分钟或应用等条件把对应行配对。假设页面分钟结果有 100 行、请求分钟结果有 80 行,组合后会先形成 8000 个中间行,再做后续聚合。
这类问题的重点不是“数据库处理 8000 行一定很慢”,而是数据量被人为放大了:真实分钟数增长时,中间结果按乘法增长,CPU、内存和临时数据开销也会随之增加。
图 1:先把两张分钟表分别压成单行,再组合结果,避免 M × N 的中间膨胀。
新 SQL 的思路是先让两个子查询各自完成聚合,再组合两个单行结果。下面是去掉业务字段和过滤条件后的逻辑示意:
1 | FROM ( |
注意,新 SQL 仍然可以保留 CROSS JOIN。优化点不是机械地删掉这个关键字,而是让它处理 1 × 1,不再处理 M × N。如果业务确实需要按分钟或应用逐组对应,则应补上明确的配对键,而不是依赖笛卡尔组合后再聚合。
这也是 SQL 优化里值得先做的检查:在讨论索引之前,先看查询是否正在生成本来不需要的数据。
第二层:当前值和环比,不必分两次重复扫描
页面既要显示当前周期,也要显示上一周期。旧实现分别调用查询,性能、错误率和移动端三部分最多产生 6 次 SQL。改造后在同一个覆盖两个周期的窗口里使用条件聚合,一次取出两组值:
1 | SELECT |
这段 SQL 同样只展示算法。真实实现要保留应用、权限、指标类型等过滤条件,并确保两个周期的边界互不重叠。
合并后总览最多由 6 次 SQL 降为 3 次。后续在“开启新汇总且有省份或用户过滤”的特定总览路径上,性能与错误率又共用一次用户分钟汇总扫描和一次边界补读,加上移动端查询后,这条路径进一步由 3 次降为 2 次。其他查询路径不能套用这个数字。
SQL 次数减半也不等于接口耗时减半:两个周期的数据仍然要读,接口里还有权限解析、序列化和网络传输。减少的是重复扫描与数据库往返,最终收益仍要测量完整链路。
第三层:让分钟汇总承担高频统计
前两层减少了无效组合和重复查询,但权限过滤场景仍可能反复扫描原始事件、提取 JSON 指标并做相同计算。本轮因此新增两级分钟汇总:
| 对象 | 粒度与内容 | 保留期 |
|---|---|---|
agg_monitoring_user_minute |
用户、应用、分钟;18 项计数与耗时累加量 | 30 天 |
agg_monitoring_minute |
应用、分钟;页面数、失败页面数、JS 错误数 | 90 天 |
vw_monitoring_user_minute |
原始事件插入时生成用户分钟统计 | 随插入触发 |
vw_monitoring_minute |
用户汇总插入块继续生成应用分钟统计 | 随插入触发 |
图 2:实时写入和历史回填共用同一条链路,应用汇总不能再次独立回填。
项目脚本针对 ClickHouse 23.11,汇总表使用 AggregatingMergeTree 和 SimpleAggregateFunction(sum, ...) 保存可累加指标。下面仅保留表结构要点,18 个指标中的其余字段已省略:
1 | CREATE TABLE IF NOT EXISTS agg_monitoring_user_minute |
用户分钟视图从原始事件中计算 countIf、sumIf 等指标;应用分钟视图再按应用和分钟聚合用户汇总。历史回填只插入用户汇总表,让下游视图自动累计应用汇总。如果再对应用表独立执行一次回填,同一批历史就会被算两遍。
这条链路的收益来自三个方面:
- 查询读取的是较小的分钟结果,而不是每次重新读取全部匹配事件。
- JSON 提取和指标分类在写入或回填时完成,不在每次页面刷新时重复执行。
- 原始明细仍然保留,操作失败明细、影响用户列表等下钻能力不必被迫迁走。
代价也同样明确:物化视图会增加写入计算、存储与后台合并开销。用户基数越大,“用户 × 应用 × 活跃分钟”的组合越多,汇总表就越可能接近明细规模。因此它不是无成本的缓存,更不是所有数据分布下都必然更快的开关。
平均值与比率:保存分子分母,不平均各分钟平均值
分钟汇总如果只保存“这一分钟的平均 FCP”,查询七天的平均值时就无法正确还原总量。假设第一分钟有两个样本 100、200,第二分钟只有一个样本 900:
- 直接平均两个分钟均值:
(150 + 900) / 2 = 525; - 按全部样本计算:
(100 + 200 + 900) / 3 = 400。
两者不同,因为每分钟样本数不同。正确做法是保存 fcp_sum 与 fcp_count,查询时先分别求和,再用 Σfcp_sum / Σfcp_count。错误率、失败率同理,先累加失败数和总数,再相除。
本轮还刻意保留了一处既有权限态错误率分母口径。它与无省份过滤时的公式存在差异,可能值得业务上重新确认,但性能改造不能顺手把报表公式改掉,否则新旧结果不一致时,将无法区分“实现错误”和“口径变化”。
精确滚动窗口:完整分钟走汇总,边界分钟回查明细
“过去一天”或“过去七天”通常是精确到秒的滚动窗口,不一定从整分钟开始。如果直接按整分钟读取汇总,就可能多算起点前的事件,或漏掉结束分钟内的事件。
新查询把窗口拆成两类数据:
- 完整分钟区间读取分钟汇总;
- 起点、当前/上一周期分界、结束位置的不完整分钟读取原始秒数据,最多三个边界分钟。
图 3:汇总与原始秒数据各管一段,既减少扫描,也避免重复和遗漏。
这意味着“使用汇总”不等于“完全不读原始表”。边界查询仍可能访问相关分区,需要检查实际扫描行数和字节数。
它还带来一个保留期约束:七天环比会涉及最近十四天。如果原始数据只保留七天,上一周期末端附近的精确边界可能无法回查。新汇总表保留更久并不能自动补齐已经删除的原始秒数据;需要另行调整保留策略,或设计能支持精确边界的更细粒度摘要。本轮没有修改原始 TTL。
权限与查询范围不能因加速而扩大
权限态查询仍按当前解析出的用户列表过滤,授权为空时返回空范围,不因为存在应用级汇总就读取全量数据。无省份过滤的总览错误率可以使用应用分钟汇总;有省份或用户过滤时,性能、错误率、两类趋势和操作失败统计使用用户分钟汇总。
本轮受益的接口前缀为 /monitoring/analysis,具体包括:
| 接口 | 新路径适用范围 |
|---|---|
/overview |
权限过滤时性能与错误率走用户汇总;无省份过滤时错误率可走应用汇总 |
/performance-trend |
有省份或用户过滤时走用户分钟汇总 |
/error-rate-trend |
有省份或用户过滤时走用户分钟汇总 |
/operation-failures |
有省份或用户过滤时走用户分钟汇总 |
移动端总览部分、用户旅程、操作失败明细和影响用户列表没有在本轮获得同等改造。不能把一张接口表扩展成“整个监控系统都快了”的结论。
数据库之外:减少点数、重复权限解析和无效等待
页面性能不只由 SQL 决定,这次还处理了几项前后端工作。
趋势降采样。 一天保留分钟点,多天按小时聚合。连续七天的分钟桶最多为 10080 个,小时桶为 168 个,能同时减少数据库分组、响应传输和 ECharts 绘制压力。实际返回点数取决于数据,不是每次请求都恰好有这些点。
权限解析 single-flight。 相同“登录用户 + 排序后的省份组合”的并发调用共享一次正在执行的权限解析,完成后立即移除,异常不缓存。它不是持久权限缓存;不同用户和不同省份仍独立执行,避免为了复用而串用权限。
取消旧请求。 前端使用 AbortController,在筛选变化或组件卸载时中止不再需要的请求,同时保留竞态保护,避免较慢的旧结果覆盖新筛选。查询期间禁止重复提交,刷新时保留旧图表,减少空白闪烁。
局部失败隔离。 四个主区域使用 Promise.allSettled 和独立加载、错误状态。一个区域失败不再拖垮整个主区域,但这只是交互韧性,不能把失败请求包装成成功。
路由懒加载。 五个页面改为 React.lazy 独立分包。当日构建记录中主 JS 约从 2.61 MB 降至 2.51 MB,这是加载侧改善,不直接改善后端 SQL。
性能结果:高密度明显受益,低密度存在反例
下面的数字来自测试服务器隔离 ClickHouse 库。旧、新 SQL 交错执行 3 次取中位数,单次查询限制 2 线程、256 MiB。它们测量的是 SQL 执行时间,不是完整 HTTP 接口耗时,也不是 P95。
高密度负载样本包含 1000 万条数据、10 个应用和 20 个模拟用户,约 5 万条/分钟,覆盖约 200 分钟;另有 2430 条跨十四天的边界回归数据。用户分钟汇总约 4.19 万行,应用分钟汇总约 2042 行。
| SQL 场景 | 优化前 | 优化后 | 旧耗时 ÷ 新耗时 |
|---|---|---|---|
| 总览性能部分,有用户过滤 | 1035.0 ms | 92.4 ms | 11.20 倍 |
| 总览错误率部分,有用户过滤 | 252.8 ms | 74.5 ms | 3.39 倍 |
| 性能趋势,有用户过滤 | 484.5 ms | 93.1 ms | 5.20 倍 |
| 错误趋势,有用户过滤 | 351.0 ms | 90.8 ms | 3.87 倍 |
| 操作失败,有用户过滤 | 303.0 ms | 89.4 ms | 3.39 倍 |
| 总览错误率部分,无省份过滤 | 260.3 ms | 76.3 ms | 3.41 倍 |
低密度样本则有 200 万行,约 100 条/分钟,用户汇总约 17.4 万行,应用汇总约 16.7 万行。结果并不是全面加速:
| SQL 场景 | 优化前 | 优化后 | 观察 |
|---|---|---|---|
| 总览性能部分,有用户过滤 | 390.4 ms | 130.1 ms | 仍然受益 |
| 总览错误率部分,有用户过滤 | 99.5 ms | 138.4 ms | 反而变慢 |
| 性能趋势,有用户过滤 | 105.0 ms | 115.2 ms | 反而变慢 |
| 错误趋势,有用户过滤 | 86.9 ms | 113.7 ms | 反而变慢 |
| 操作失败,有用户过滤 | 80.1 ms | 112.9 ms | 反而变慢 |
| 总览错误率部分,无省份过滤 | 121.8 ms | 110.1 ms | 小幅改善 |
原因不神秘:汇总读取、联合查询和边界校正都有固定成本。高压缩比时,少扫描明细能覆盖这部分成本;低密度或低压缩比时,固定成本可能大于节省下来的工作。
高密度样本只接近“十亿条 / 十四天”的平均事件速率,并没有真正写入十亿行,也没有模拟完整生产用户基数、完整十四天数据、并发以及全部既有物化视图的写入开销。真实用户数远大于 20 个模拟用户,同样的明细行数可能形成更多用户分钟组合,压缩比和权限过滤成本都会改变。因此不能把表格外推成“生产必然快 3~11 倍”。
正确性验证与测试库迁移分别证明了什么
原记录中的验证分为几层:
- Java 定向测试 10 项通过;隔离 ClickHouse 集成测试完成 126 组新旧结果对比,覆盖应用、权限、空数据、一天/七天、精确秒边界、历史回填与实时写入。
- 回填工具最初 8 项 Python 标准库测试通过;增加一键建表和日期范围后,相关 15 项测试通过。
- 默认开启调整后,重新运行 6 项 Mapper SQL 测试和 4 项 Service 测试,合计 10 项通过。该次默认值调整没有改 SQL,因此没有重复执行数据库性能测试。
- 测试业务库完成十三天建表与回填,逐日均为
verified: true;另按用户、应用、分钟逐组对比 18 项累计指标,4813 组全部一致;应用、分钟维度的 4486 组三项计数也全部一致。原始表迁移前后均为 51807 行,时间范围未变。
这些结果分别证明查询路由、汇总公式、回填核对和该测试库的数据一致性。它们不等于新版本接口已经上线加速。按 2026 年 11 月 5 日记录,测试业务库迁移完成,但新业务版本尚未部署,生产环境尚未迁移;测试消费者原本禁用,也保持原状态。
上线顺序与回退:先迁移,再打开新读取路径
最终代码默认开启汇总查询,所以部署顺序不能反:
- 保持旧版本运行,记录相同条件的原接口结果与耗时,检查没有遗留的关闭开关覆盖值。
- 评估真实用户分钟组合数、磁盘余量和回填资源开销。
- 在低峰窗口暂停所有原始表写入消费者,确认 Kafka 积压空间和保留时间足够;同时避免历史清理任务继续删除回填源数据。
- 建表并按现存日期回填,只写用户汇总,逐日核对页面数、失败数和 JS 错误数。
- 恢复消费者,确认积压正常消化、实时汇总持续更新,再部署默认开启汇总的新版本。
- 验收全部/单应用、单省/多省/空授权、一天/七天、当前/上一周期,记录完整接口 P50/P95、扫描量、CPU、内存、磁盘与写入延迟。
回填工具默认只检查,实际写入必须同时提供 --apply --ingest-paused。目标日期已有数据会拒绝重复回填,不自动覆盖或重试。ClickHouse 回填不是跨日期、跨表事务,超时或部分失败后可能已经写入部分块,不能直接重跑整段 INSERT。
需要回退读取路径时,设置 MONITORING_AGGREGATE_ENABLED=false 并重启即可,无需删表。但这个开关只控制读取,物化视图仍会继续写入,新汇总的资源开销不会随回退消失。
暂停采集期间页面新增数据会延迟,回填也会争用数据库资源,所以这不是“全程无影响”的在线迁移。把影响说清楚,比承诺一个无法验证的无感上线更可靠。
下一轮该收集什么
本轮给出的不是最终容量答案,而是一条可验证的优化路径。后续最值得持续收集的是:
- 每日事件增量、真实活跃用户数、用户应用分钟组合数;
- 典型省份授权用户数和汇总压缩比;
- 各接口 P50/P95、扫描行数与字节数;
- 查询并发、写入吞吐、物化视图延迟与磁盘增长;
- 原始数据实际保留期能否满足十四天精确边界。
如果用户分钟表接近明细规模,应先看真实扫描和权限范围,再评估排序键、查询路径或更粗的汇总粒度;如果单机资源持续成为瓶颈,再讨论 ClickHouse 多节点分片与副本。架构升级应当由这些证据触发,而不是由“几亿行”这个数字单独触发。
延伸阅读
本文根据 2026 年 11 月 5 日工程笔记、项目 DDL 与迁移记录整理。项目名称、内网地址、账号和本机路径已省略;性能与测试结果均为原记录结果,本次整理未重新执行业务压测、数据库迁移或生产部署。