从重复扫描到分钟汇总: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
2
3
4
5
6
7
8
9
10
11
12
FROM (
SELECT sum(page_duration) AS duration_sum,
sum(page_count) AS page_count
FROM agg_page_minute
WHERE ts_minute >= {start} AND ts_minute < {end}
) p
CROSS JOIN (
SELECT sum(network_time) AS network_sum,
sum(network_count) AS network_count
FROM agg_request_minute
WHERE ts_minute >= {start} AND ts_minute < {end}
) r;

注意,新 SQL 仍然可以保留 CROSS JOIN。优化点不是机械地删掉这个关键字,而是让它处理 1 × 1,不再处理 M × N。如果业务确实需要按分钟或应用逐组对应,则应补上明确的配对键,而不是依赖笛卡尔组合后再聚合。

这也是 SQL 优化里值得先做的检查:在讨论索引之前,先看查询是否正在生成本来不需要的数据。

第二层:当前值和环比,不必分两次重复扫描

页面既要显示当前周期,也要显示上一周期。旧实现分别调用查询,性能、错误率和移动端三部分最多产生 6 次 SQL。改造后在同一个覆盖两个周期的窗口里使用条件聚合,一次取出两组值:

1
2
3
4
5
SELECT
sumIf(value, ts >= {currentStart} AND ts < {currentEnd}) AS current_value,
sumIf(value, ts >= {previousStart} AND ts < {previousEnd}) AS previous_value
FROM monitoring_source
WHERE ts >= {previousStart} AND ts < {currentEnd};

这段 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
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE IF NOT EXISTS agg_monitoring_user_minute
(
ts_minute DateTime,
app_id UInt32,
user_id String,
page_count SimpleAggregateFunction(sum, UInt64),
fcp_sum SimpleAggregateFunction(sum, UInt64),
fcp_count SimpleAggregateFunction(sum, UInt64)
-- 其余计数与累加字段省略
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMMDD(ts_minute)
ORDER BY (user_id, app_id, ts_minute)
TTL ts_minute + INTERVAL 30 DAY;

用户分钟视图从原始事件中计算 countIf、sumIf 等指标;应用分钟视图再按应用和分钟聚合用户汇总。历史回填只插入用户汇总表,让下游视图自动累计应用汇总。如果再对应用表独立执行一次回填,同一批历史就会被算两遍。

这条链路的收益来自三个方面:

  1. 查询读取的是较小的分钟结果,而不是每次重新读取全部匹配事件。
  2. JSON 提取和指标分类在写入或回填时完成,不在每次页面刷新时重复执行。
  3. 原始明细仍然保留,操作失败明细、影响用户列表等下钻能力不必被迫迁走。

代价也同样明确:物化视图会增加写入计算、存储与后台合并开销。用户基数越大,“用户 × 应用 × 活跃分钟”的组合越多,汇总表就越可能接近明细规模。因此它不是无成本的缓存,更不是所有数据分布下都必然更快的开关。

平均值与比率:保存分子分母,不平均各分钟平均值

分钟汇总如果只保存“这一分钟的平均 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 日记录,测试业务库迁移完成,但新业务版本尚未部署,生产环境尚未迁移;测试消费者原本禁用,也保持原状态。

上线顺序与回退:先迁移,再打开新读取路径

最终代码默认开启汇总查询,所以部署顺序不能反:

  1. 保持旧版本运行,记录相同条件的原接口结果与耗时,检查没有遗留的关闭开关覆盖值。
  2. 评估真实用户分钟组合数、磁盘余量和回填资源开销。
  3. 在低峰窗口暂停所有原始表写入消费者,确认 Kafka 积压空间和保留时间足够;同时避免历史清理任务继续删除回填源数据。
  4. 建表并按现存日期回填,只写用户汇总,逐日核对页面数、失败数和 JS 错误数。
  5. 恢复消费者,确认积压正常消化、实时汇总持续更新,再部署默认开启汇总的新版本。
  6. 验收全部/单应用、单省/多省/空授权、一天/七天、当前/上一周期,记录完整接口 P50/P95、扫描量、CPU、内存、磁盘与写入延迟。

回填工具默认只检查,实际写入必须同时提供 --apply --ingest-paused。目标日期已有数据会拒绝重复回填,不自动覆盖或重试。ClickHouse 回填不是跨日期、跨表事务,超时或部分失败后可能已经写入部分块,不能直接重跑整段 INSERT。

需要回退读取路径时,设置 MONITORING_AGGREGATE_ENABLED=false 并重启即可,无需删表。但这个开关只控制读取,物化视图仍会继续写入,新汇总的资源开销不会随回退消失。

暂停采集期间页面新增数据会延迟,回填也会争用数据库资源,所以这不是“全程无影响”的在线迁移。把影响说清楚,比承诺一个无法验证的无感上线更可靠。

下一轮该收集什么

本轮给出的不是最终容量答案,而是一条可验证的优化路径。后续最值得持续收集的是:

  • 每日事件增量、真实活跃用户数、用户应用分钟组合数;
  • 典型省份授权用户数和汇总压缩比;
  • 各接口 P50/P95、扫描行数与字节数;
  • 查询并发、写入吞吐、物化视图延迟与磁盘增长;
  • 原始数据实际保留期能否满足十四天精确边界。

如果用户分钟表接近明细规模,应先看真实扫描和权限范围,再评估排序键、查询路径或更粗的汇总粒度;如果单机资源持续成为瓶颈,再讨论 ClickHouse 多节点分片与副本。架构升级应当由这些证据触发,而不是由“几亿行”这个数字单独触发。

延伸阅读


本文根据 2026 年 11 月 5 日工程笔记、项目 DDL 与迁移记录整理。项目名称、内网地址、账号和本机路径已省略;性能与测试结果均为原记录结果,本次整理未重新执行业务压测、数据库迁移或生产部署。