Making a dashboard query scan 230× less data 让一条看板查询,少扫 230 倍的数据

A ClickHouse tuning demo you can run yourself: the same 50-million-row events table in a naive schema versus a tuned one, measured with the database's own query_log. A filtered dashboard query drops from scanning 1.9 GiB to about 8 MiB — roughly 230× less data, 16× faster — and a daily rollup gets 54× faster with a projection. One command rebuilds it from empty; a CI job re-runs it on every push and fails the build if the tuning ever regresses. 一个你能自己跑的 ClickHouse 调优 demo:同一张 5000 万行的事件表,朴素 schema 对比调优后的 schema,用数据库自己的 query_log 度量。一条带过滤的看板查询,从扫描 1.9 GiB 降到约 8 MiB——大约少扫 230 倍数据、快 16 倍——一个日聚合再用 projection 提速 54 倍。一条命令从空库重建;CI 每次推送都重跑,一旦调优退化就让构建失败。

ClickHouseData Warehouse数据仓库Query Tuning查询调优OLAPProven with query_logquery_log 实证

The problem问题

An analytical table grows to tens or hundreds of millions of rows, and the dashboards on top of it slow to a crawl. Each query reads far more data than it needs, latency climbs, and — on a metered warehouse — the bill climbs with it. The usual response is "add more hardware," which pays to scan the same wasted bytes faster instead of scanning fewer of them. 一张分析表涨到几千万、上亿行,架在它上面的看板就慢成蜗牛。每条查询读的数据远超所需,延迟往上爬,而在按量计费的仓库上,账单也跟着爬。惯常反应是"加机器"——那是花钱把同样浪费的字节扫得更快,而不是少扫一些。

The constraint: prove the win with numbers, not vibes约束:用数字证明,不靠感觉

Tuning advice is easy to assert and hard to trust. So the whole thing is built to be measured and reproduced: identical data and identical queries on both schemas, the win read straight from query_log (bytes read, rows read, duration), and a single command that rebuilds the benchmark from an empty database so anyone can check the result themselves. 调优建议好说难信。所以整件事都为"可度量、可复现"而建:两个 schema 上用完全相同的数据和查询,提升直接从 query_log 读出(读取字节数、读取行数、耗时),再用一条命令从空库重建整个基准,让任何人都能自己核对结果。

What moves the numbers是什么在推动这些数字

A sorting key that matches how you query — the right ORDER BY plus partitioning lets ClickHouse skip whole granules of data instead of reading them, which is where the 230× reduction in bytes scanned comes from. 与查询方式匹配的排序键——对的 ORDER BY 加分区,让 ClickHouse 整段跳过数据、而非读取它们,这正是"少扫 230 倍字节"的来源。
Projections for the heavy rollups — a projection precomputes a daily aggregate so the recurring report reads a tiny materialized result instead of re-scanning the base table, for the 54× speedup. 为重聚合用 projection——projection 预计算一个日聚合,让那份周期性报表读一个很小的物化结果,而不是重扫基表,换来 54 倍提速。
Right-sized types — LowCardinality and tighter column types shrink what has to be read and held in memory in the first place. 合适大小的类型——LowCardinality 和更紧的列类型,从源头缩小了需要读取和驻留内存的量。
A regression gate — the benchmark runs in CI on every push and fails the build if a schema change quietly makes a query scan more again, so a win stays a win. 回归闸门——基准在 CI 里每次推送都跑,一旦某次 schema 改动悄悄让查询又多扫了,就让构建失败,于是"赢"能一直是"赢"。

Why this is my kind of problem为什么这正是我擅长的问题

Data-platform reliability is my center: most of six years of backend was building and operating ETL and storage under load — a 7-microservice cross-database migration, throughput taken from 50 to 5,000 QPS, a binlog-level recovery that restored 100% of lost data with zero downtime. The instinct is the same here: don't argue about performance, instrument it, prove the win with the database's own numbers, and put a gate around it so it can't silently rot. 数据平台的可靠性是我的中心:六年后端里,大部分是在负载下构建和运维 ETL 与存储——一次 7 微服务跨库迁移、把吞吐从 50 拉到 5000 QPS、一次 binlog 级恢复零停机找回 100% 丢失数据。这里的直觉一样:不争论性能,给它上仪表、用数据库自己的数字证明提升,再套一道闸门让它不会悄悄烂掉。

Proof证明

Runnable, reproducible, and on video. The full benchmark is public on GitHub — the naive and tuned schemas, the queries, and the query_log numbers — and it rebuilds from empty with one command, with a GitHub Actions job re-running it on every push. There's a 3-minute walkthrough of it running live. The dataset is a generated 50M-row benchmark rather than a specific client's data; what it proves is the method and the measured result, which port straight onto a real warehouse. Code → · 3-min walkthrough → 可运行、可复现、还有视频。完整基准公开在 GitHub 上——朴素与调优两套 schema、查询、以及 query_log 数字——一条命令从空库重建,GitHub Actions 每次推送都重跑。另有一段 3 分钟的实时运行走查。数据集是生成的 5000 万行基准,而非某个具体客户的数据;它证明的是方法与实测结果,可直接迁到真实仓库上。 代码 → · 3 分钟走查 →

Where else this applies还能用在哪

Any analytical or OLAP workload where queries scan more than they should: product analytics, event pipelines, reporting warehouses, dashboards that got slow as the data grew. If a query feels slow and nobody can say exactly why, the first move is to measure what it actually reads — and then make it read far less. 任何"查询扫得比应该扫的多"的分析/OLAP 场景:产品分析、事件管线、报表仓库、随数据变大而变慢的看板。如果一条查询感觉慢、却没人说得清到底为什么,第一步是量它真正读了多少——然后让它读得远远更少。

← All case studies← 全部案例