解锁 ClickHouse 新技能： JSON 数据性能调优，实时获取业务洞察

本文字数：19861；估计阅读时间：50 分钟

作者： Tom Schreiber

本文在公众号【ClickHouseInc】首发

为什么实时仪表盘的速度至关重要

对于实时分析应用（如仪表盘），流畅的响应速度至关重要。Jakob Nielsen 的研究、心理计时学理论，以及 Steve Henty 的进一步分析总结了人类对系统响应时间的感知标准：

<100ms（瞬时）—— 近乎即时，理想用于筛选或快速更新。

100ms - 500ms（非常快）—— 运行流畅，适用于图表渲染、选项卡切换或摘要计算。

500ms - 1s（可察觉的延迟）—— 用户能明显感受到等待，但对于复杂查询仍可接受。

1s - 2s（缓慢但可容忍）—— 可能会让用户感觉卡顿，建议添加加载指示器。

>2s（过慢）—— 体验不佳，用户可能会失去耐心或注意力。

要在大规模数据集上实现 小于 500ms，甚至小于 100ms 的查询响应时间，尤其是在处理 数十亿个 JSON 文档 时，没有合适的数据库支撑将极具挑战性。随着数据量增长，大多数系统的查询性能都会显著下降，导致仪表盘响应变慢，用户体验受损。

在本文中，我们将通过 三个典型的实时仪表盘场景，展示经过验证的查询加速技术。在这些场景中，所有查询均基于 40 亿个 Bluesky JSON 文档（1.6 TiB 数据） 进行计算，并且 仅运行在一台普通的中等规格服务器上，但仍能实现：

① 确保 ClickHouse 查询始终低于 100ms，提供瞬时响应。  

② 无论数据规模增长多少，查询速度始终保持稳定。  

③ 始终运行在最新数据上，且延迟极低。  

④ CPU 和内存占用极低，仅消耗 KB 级到低 MB 级的 RAM。

下表展示了我们将要实现的优化效果，证明 ClickHouse 即使在 数十亿 JSON 文档规模下，依然能够保障实时仪表盘的高性能。

在探讨优化方案之前，我们先介绍 Bluesky 数据集及其运行环境。然后，我们将通过三个典型的实时仪表盘场景，详细解析如何在 任何数据规模下 实现并保持 <100ms 的瞬时查询性能。

TL;DR？直接查看关键要点！  

如果你想跳过详细过程，直接查看最终结果和优化分析，请前往：  

目标达成：稳定实现 <100ms 查询。

Bluesky JSON 数据集

我们的示例数据集来源于 Bluesky，这是一组 实时 JSON 事件流，捕获自 知名社交媒体平台。正如我们在另一篇文章中介绍的，该数据集会 持续接收 新的事件，如 帖子、点赞、转发。

如何在 ClickHouse 中存储 Bluesky JSON 数据

下方是 ClickHouse 存储 Bluesky 数据集的表结构，该数据集会 不断增长，并可在 ClickHouse SQL playground 中访问：

CREATE TABLE bluesky.bluesky
(
  kind LowCardinality(String),
  data JSON,
  bluesky_ts DateTime64(6)
)
ENGINE = MergeTree
ORDER BY (kind, bluesky_ts);

data 列采用 新版 JSON 类型 存储 原始的 Bluesky JSON 文档。

过去，为了加速查询，我们曾将 事件类型 和 事件时间 提取为顶层列，并用于排序。不过，现在已经不需要这样做了，因为 新版 JSON 类型已支持直接使用 JSON 路径作为排序键和主键列。

数据集的规模有多大？

数据摄取始于 去年 12 月下旬。截至 2025 年 3 月，表中已存储 40 亿+ 条 Bluesky 事件 JSON 文档，未压缩数据总量超过 1.6 TiB。

SELECT
    formatReadableQuantity(sum(rows)) AS docs,
    formatReadableSize(sum(data_uncompressed_bytes)) AS data_size
FROM system.parts
WHERE active AND (database = 'bluesky') AND (table = 'bluesky');

上述查询结果基于 2025 年 3 月的数据。

┌─docs─────────┬─data_size─┐
│ 4.14 billion │ 1.61 TiB  │
└──────────────┴───────────┘

数据集增长有多快？

目前，该表 每月新增约 15 亿条数据：

SELECT
    toStartOfMonth(bluesky_ts) AS month,
    count() AS docs
FROM bluesky.bluesky
GROUP BY month
ORDER BY month DESC
LIMIT 10
SETTINGS enable_parallel_replicas=1;

由于 数据摄取始于去年 12 月下旬，该月份的数据量相对较少。  2025 年 2 月只有 28 天，数据量也比其他月份略少。

当前，该表每天新增约 5000 万条 Bluesky 事件：

SELECT
    toStartOfDay(bluesky_ts) AS day,
    count() AS docs
FROM bluesky.bluesky
WHERE day < toStartOfDay(now())
GROUP BY day
ORDER BY day DESC
LIMIT 10
SETTINGS enable_parallel_replicas=1;

如此快速的增长意味着，如果缺乏合理优化，查询速度必然会下降。

那么，如何在数据量持续增长的同时，依然保持实时查询性能？ 继续阅读...

实时测试所有查询！  

或者，直接让博客为你执行查询。

本博客涉及的所有 Bluesky 表、查询加速技术和示例查询，均可在 ClickHouse SQL playground 中运行，您可以自行探索，亲自执行每个示例。

事实上，本博客中的 所有查询 都会 实时执行，让您边阅读边查看查询结果。

我们的 公共 ClickHouse SQL playground 设有限制（如 配额、访问控制、查询复杂度），确保所有用户都能 公平使用资源，防止个别用户占用过多计算能力。因此，高开销查询可能会受限。

为了提供 完整且准确的查询执行统计信息，本博客中的部分查询 由 ClickHouse Cloud playground 上的管理员用户运行，并通过 clickhouse-client 连接到无约束权限环境 执行，以便展示完整结果。

ClickHouse 运行环境

我们的 ClickHouse SQL playground 运行在 ClickHouse Cloud 上，拥有至少 三个计算节点，每个节点配备 59 核 CPU 和 236 GiB 内存。虽然 ClickHouse Cloud 具备 高可用性和可扩展性，但本博客中的 所有仪表盘查询 均运行在 单个计算节点，因为我们 未启用 ClickHouse Cloud 的并行副本。除了 ClickHouse Cloud 采用共享对象存储 这一点外，本博客的查询性能 可与具备类似 CPU 和 RAM 的独立 ClickHouse 实例相媲美。

在接下来的内容中，我们将展示 如何在这种硬件环境下，实现并稳定保持 <100ms 的 ClickHouse 查询响应时间，无论 Bluesky 表的数据量增长到多少亿条 JSON 文档。  我们将通过 三个实时仪表盘场景，逐步解析 查询优化的核心技术。

仪表盘 1：监测 Bluesky 活跃趋势  

本案例是一个 实时仪表盘，按 小时 统计 最受欢迎的 Bluesky 事件类型，直观展示 一天中用户的活跃时间。

基础查询：按小时统计事件（44 秒）

仪表盘查询逻辑基于 事件类型计数，计算 最受欢迎的 Bluesky 事件：

SELECT
    data.commit.collection AS event,
    toHour(bluesky_ts) AS hour_of_day,
    count() AS count
FROM bluesky.bluesky
WHERE kind = 'commit'
  AND event in [
    'app.bsky.feed.post',
    'app.bsky.feed.repost',
    'app.bsky.feed.like']
GROUP BY event, hour_of_day;

⚠️ 直接运行该查询可能无法顺利执行，因为它 很可能超出 我们 公共 playground 设定的查询时间限制和复杂度阈值。

无限制环境下的查询执行情况：

Elapsed: 44.901 sec. Processed 4.12 billion rows, 189.18 GB (91.84 million rows/s., 4.21 GB/s.)
Peak memory usage: 775.96 MiB.

在 ClickHouse Cloud playground 中，通过 clickhouse-client 运行该查询（无查询限制），结果如下：查询耗时：44 秒，内存占用：776 MiB，性能远不能满足仪表盘的实时交互需求。

 >2s（过慢）—— 体验不佳，用户容易失去专注。

尽管 ClickHouse 处理 Bluesky JSON 数据的速度 远超 其他主流 JSON 数据存储，但即便在 高性能硬件 上，其最大吞吐量也仅为 91.84M 文档/秒（4.21 GB/s）。

然而，面对 40 亿+ 文档且不断增长的数据量，仅靠全表扫描无法直接实现 <100ms 查询。如果我们能在 新事件到达时 预先 聚合数据，是否就能避免每次查询都扫描数十亿行数据呢？这正是 增量物化视图（incremental materialized views） 发挥作用的关键！

增量聚合如何提升查询速度

要实现 瞬时查询，我们需要 实时增量聚合数据，让 聚合结果随着新的 Bluesky 事件到达而持续更新：

我们将 预聚合的数据 存储在 ① events_per_hour_of_day 表中，③ 该表用于支持仪表盘查询。同时，② 增量物化视图 负责 监测源表数据变更，并自动更新预聚合表，确保数据始终保持最新。

基于 物化视图的增量聚合 极大提升资源利用率，特别是在 源表包含数十亿甚至数万亿行数据 的情况下。与其在每次查询时 重新扫描整个数据集并计算聚合，ClickHouse 采用 增量计算 方法，仅对新插入的数据执行局部聚合，并在后台将这些聚合状态与已有数据增量合并，大幅减少计算开销。

以下是 events_per_hour_of_day 表的 DDL 语句，用于存储 预聚合数据：

CREATE TABLE bluesky.events_per_hour_of_da