Top-N 查询快 10 倍？ClickHouse 用“跳过读取”把排序问题变成了元数据问题

TL;DR

ClickHouse 将 Top-N 查询作为一类核心查询模式进行重点优化。借助数据跳过索引中的最小值 最大值元数据，ClickHouse 可以在真正读取数据之前就跳过整个 granule。在我们的示例中，这种方式让 Top-N 查询的执行速度提升了 5–10 倍，同时读取的数据量减少了 10–100 倍，在大表以及缓存尚未命中的场景下效果尤为明显。

想快速了解一下整体思路？

可以观看 Mark 讲解 ClickHouse 是如何优化 Top-N 查询的：

https://youtu.be/SyayPSrsAhI

ClickHouse 之所以能够在性能上保持领先，源于它系统性地识别出常见的分析型查询模式，并在引擎层面对这些模式逐一进行深度优化。

Top-N 查询正是其中最关键的一类。

在真实的分析型工作负载中，它们随处可见，例如仪表盘、监控查询、排行榜报表以及探索式分析等场景。

“展示最新产生的事件。”

“按收入计算，哪些是贡献最高的客户？”

“今天金额最高的订单有哪些？”

SELECT *
FROM orders
ORDER BY total_amount DESC
LIMIT 3;

随着引擎的持续演进，ClickHouse 已经积累了大量专门针对 Top-N 查询的底层优化能力，包括流式执行、按序读取、惰性读取等机制。

本文将聚焦于这一优化工具箱中的另一项能力：一种在尚未读取任何数据之前，就利用数据跳过索引提前消除无效扫描的新方法，从而在数据规模不断增长的情况下，进一步提升 Top-N 查询的性能。

为了高效、快速地执行 Top-N 查询，ClickHouse 已经内置了多种优化策略：

1. 流式 Top-N（内存使用受限）：

   ClickHouse 并不会对全部数据进行一次性排序，而是以流式方式处理输入数据，仅维护当前的 Top-N 候选结果，因此内存开销与 N 成正比，而不会随着表规模线性增长。

2. 按序读取（彻底避免排序）：

   如果磁盘上的数据已经按照 ORDER BY 列排序，或者能够通过 projection 按该顺序读取，ClickHouse 就可以直接顺序读取前面的数据，而无需额外排序。

3. 惰性读取（延后读取非排序列）：

   即便查询中选择了大量列，ClickHouse 也可以先只使用参与排序的列来确定 Top-N 结果行，随后再为这些行读取其余列，从而显著降低 I/O 开销。

这些优化手段彼此独立，关注的是 Top-N 查询执行过程中的不同环节，因此既可以单独生效，也可以在同一个查询中协同使用。

即便如此，传统的 Top-N 查询在执行时仍然需要扫描所有相关的 granule，即使最终返回的结果行数可能只有极少一部分。

ClickHouse 通过引入数据跳过索引，为 Top-N 查询增加了一层额外且彼此独立的优化能力，用于静态或动态的 Top-N 过滤，从而显著减少需要参与处理的数据行数。

接下来的内容将从最简单的场景入手，展示这一机制在实际执行中的工作方式。

我们先来看最简单的一种情况：不包含任何谓词或过滤条件的 Top-N 查询。

SELECT * FROM T ORDER BY c ASC LIMIT 3;

即使查询中没有 WHERE 子句，只要在 ORDER BY 列 c 上存在 minmax 数据跳过索引，ClickHouse 就能够减少实际读取的数据量。

为了便于说明，假设表 T 由 5 个 granule 组成（granule 是 ClickHouse 中最小的处理单元，默认每个 granule 覆盖 8,192 行）。对于每个 granule，列 c 的最小值和最大值都会被记录在 minmax 数据跳过索引中，如下所示：

当 ClickHouse 执行 ORDER BY c LIMIT 3 时，整体流程如下：

首先，仅查看 minmax 索引中的 min(c) 值。

其中最小的几个 min 值为：

• Granule 5 → min = 1

• Granule 3 → min = 5

• Granule 2 → min = 10

因此，只需要从磁盘读取 granule 5、3 和 2。

接着，将这些 granule 中的行合并，返回 Top 3 的结果值。

其余的 granule 则会被完全跳过，不参与后续处理。

（需要注意的是，在实际执行中，可能会读取超过 3 个 granule。这是因为数据是按 block 进行处理的，一个 block 可能跨越多个相邻的 granule，同时还会有多个线程并行读取和处理数据。）

为了量化这一优化带来的效果，我们使用了匿名化的 Web 分析示例数据。在一台 AWS m6i.8xlarge EC2 实例（32 核 CPU、128 GB 内存）上创建表并加载数据，底层存储使用的是 gp3 EBS 卷（16k IOPS，最大吞吐量 1000 MiB/s）。

下面是一个不包含任何谓词条件的 Top-N 查询示例：

SELECT URL, EventTime 
FROM hits 
ORDER BY EventTime
LIMIT 10;

在 12 月 25 日、尚未启用基于数据跳过索引的新优化之前，三次运行中最快的一次耗时为 0.044 秒。

10 rows in set. Elapsed: 0.044 sec. Processed 100.08 million rows, 1.20 GB (2.27 billion rows/s., 27.26 GB/s.)
Peak memory usage: 2.23 MiB.
10 rows in set. Elapsed: 0.044 sec. Processed 100.08 million rows, 1.20 GB (2.29 billion rows/s., 27.56 GB/s.)
Peak memory usage: 2.25 MiB.
10 rows in set. Elapsed: 0.044 sec. Processed 100.08 million rows, 1.20 GB (2.27 billion rows/s., 27.33 GB/s.)
Peak memory usage: 2.24 MiB.

可以看到，此时 hits 表中大约 1 亿行数据都参与了处理。

随后，我们通过开启新的 use_skip_indexes_for_top_k 设置，使用基于数据跳过索引的优化再次执行同一个查询。需要注意的是，该表在 EventTime 列上已经定义了 minmax 数据跳过索引。

SELECT URL, EventTime 
FROM hits 
ORDER BY EventTime
LIMIT 10
SETTINGS use_skip_indexes_for_top_k = 1;

这一次，三次运行中最快的一次仅耗时 0.009 秒。

10 rows in set. Elapsed: 0.009 sec. Processed 163.84 thousand rows, 4.95 MB (17.48 million rows/s., 528.35 MB/s.)
Peak memory usage: 917.51 KiB.
10 rows in set. Elapsed: 0.009 sec. Processed 163.84 thousand rows, 4.95 MB (18.03 million rows/s., 544.98 MB/s.)
Peak memory usage: 198.47 KiB.
10 rows in set. Elapsed: 0.009 sec. Processed 163.84 thousand rows, 4.95 MB (18.19 million rows/s., 549.68 MB/s.)
Peak memory usage: 198.47 KiB.

相比之前，这一结果大约快了 5 倍。ClickHouse 不再扫描整张表中约 1 亿行数据，而是只处理了大约 16.3 万行数据，将读取的数据量从约 1.2 GB 大幅降低到了 4.95 MB。

随着表规模的持续增长，这种 I/O 层面的收益会更加明显：当数据量达到数十亿甚至数万亿行，尤其是在缓存尚未命中的情况下，在 granule 层级避免不必要的数据读取将带来极其显著的性能提升。

更重要的是，这种数据量的缩减完全发生在读取任何实际数据行之前，仅依赖于 granule 级别的元数据信息。

前面的内容中，我们主要讨论了不包含谓词条件的 Top-N 查询。在这种情况下，可以利用 ORDER BY 列上的静态最小值 最大值元数据，对 granule 进行提前筛选。

当 Top-N 查询中引入了谓词条件后，数据跳过机制就不再是静态的，而是转变为动态过程。

在查询执行期间，ClickHouse 会持续维护当前的 Top-N 结果集合，其中当前排名第 N 的值会自然地作为一个动态阈值存在。

这一机制建立在 ClickHouse 25.9 中引入的二级索引流式处理（streaming for secondary indices）之上。与在查询开始前一次性评估数据跳过索引不同，ClickHouse 会将数据跳过索引的检查过程与实际的数据读取过程交替进行。当某个 granule 在完成用于谓词判断的主键索引分析后具备读取条件时，ClickHouse 会立即检查该 granule 对应的 minmax 数据跳过索引条目。

此时，ClickHouse 会将当前的 Top-N 阈值与该 granule 的最小值 最大值元数据进行比较。如果可以确定该 granule 不可能包含任何能够改进当前 Top-N 结果的数据，那么它会被立即裁剪掉，整个 granule 都不会被读取。

随着查询不断向前推进，当更优的 Top-N 候选结果被发现时，这个阈值会持续收紧，从而使 ClickHouse 能够在执行过程中动态跳过越来越多的 granule。由于在 Top-N 结果集一旦确定后查询就会终止，更严格的阈值也意味着 ClickHouse 可以更早地停止读取和跳过后续的 granule。

我们通过另一个包含谓词条件的 Top-N 查询示例来验证这一机制的实际效果：

SELECT URL,EventTime 
FROM hits 
WHERE URL LIKE '%google%'
ORDER BY EventTime
LIMIT 10;

在 12 月 25 日、尚未启用基于数据跳过索引的动态 Top-N 阈值过滤之前，三次运行中最快的一次耗时为 0.325 秒。为了确保每次运行都反映纯粹的数据处理行为，我们在所有测试中都关闭了查询条件缓存。

10 rows in set. Elapsed: 0.333 sec. Processed 100.00 million rows, 9.42 GB (299.96 million rows/s., 28.26 GB/s.)
Peak memory usage: 143.92 MiB.
10 rows in set. Elapsed: 0.325 sec. Processed 100.00 million rows, 9.42 GB (307.37 million rows/s., 28.95 GB/s.)
Peak memory usage: 138.46 MiB.
10 rows in set. Elapsed: 0.334 sec. Processed 100.00 million rows, 9.42 GB (299.55 million rows/s., 28.22 GB/s.)
Peak memory usage: 147.47 MiB.

可以看到，此时 hits 表中的全部约 1 亿行数据都参与了处理。

随后，我们通过启用二级索引的流式处理，并打开新的 use_top_k_dynamic_filtering 设置，再次执行同一个查询。需要说明的是，该表在 EventTime 列上已经定义了 minmax 数据跳过索引，并且我们依然在所有运行中禁用了查询条件缓存，以便完整地隔离动态 Top-N 阈值过滤本身的影响。

SELECT URL,EventTime 
FROM hits 
WHERE URL LIKE '%google%'
ORDER BY EventTime
LIMIT 10
SETTINGS
  use_query_condition_cache = 0,
  use_skip_indexes_on_data_read = 1,
  use_skip_indexes_for_top_k = 1,
  use_top_k_dynamic_filtering = 1;

这一次，三次运行中最快的一次仅耗时 0.033 秒。

10 rows in set. Elapsed: 0.034 sec. Processed 7.66 million rows, 515.98 MB (227.36 million rows/s., 15.32 GB/s.)
Peak memory usage: 51.30 MiB.
10 rows in set. Elapsed: 0.034 sec. Processed 7.59 million rows, 509.67 MB (224.45 million rows/s., 15.08 GB/s.)
Peak memory usage: 51.29 MiB.
10 rows in set. Elapsed: 0.033 sec. Processed 7.67 million rows, 520.58 MB (234.95 million rows/s., 15.96 GB/s.)
Peak memory usage: 47.28 MiB.

相比之前，性能提升约为 10 倍。ClickHouse 不再扫描整张表中约 1 亿行数据，而是只处理了大约 700 万行数据，将读取的数据量从约 9.42 GB 降低到了约 520.58 MB。

与前一个示例类似，这种 I/O 层面的收益会随着表规模的扩大而愈发明显：当表的数据量达到数十亿甚至数万亿行时（尤其是在缓存未命中的情况下），动态跳过那些不可能改进当前 Top-N 结果的 granule 将带来极其显著的效果。

正如前文所述，ClickHouse 正是通过在查询执行过程中持续维护当前的 Top-N 阈值，并借助 minmax 数据跳过索引，动态跳过那些其取值范围不可能改进 Top-10 结果的 granule，从而实现这一优化。

这些机制同样已经在超大规模的生产环境中得到了验证。

在早期测试中，我们在一张包含 500 亿行数据的表上进行了实验，使用跳过索引过滤的 Top-N 查询可以在 0.2 秒以内完成。这一结果表明，即便在极端数据规模下，granule 级别的裁剪依然能够保持良好的效果，而且随着后续优化的推进，性能还有望进一步提升。

借助基于数据跳过的 Top-N 过滤，ClickHouse 实际上把 Top-N 查询转化为了一个由元数据主导的裁剪问题。

通过将当前的 Top-N 阈值与 granule 级别的最小值 最大值元数据进行比较，ClickHouse 可以在完全不读取任何实际数据行的情况下，直接跳过表中的大块数据。对于简单的 Top-N 查询，这种裁剪可以在查询一开始就完成；而对于包含谓词条件的查询，则会随着执行过程的推进、阈值逐步收紧而动态发生。

在采用对象存储或计算与存储解耦的现代系统架构中，这种由元数据驱动的裁剪方式尤为强大。避免不必要的数据读取，不仅节省了 CPU 资源，同时也减少了网络 I/O 开销和整体访问延迟。

在我们的示例中，仅通过跳过那些不可能改进结果的 granule，这种方法就将 Top-N 查询的执行速度提升了 5 到 10 倍，同时将读取的数据量降低了一个到两个数量级。

更关键的是，这一优化可以与现有的 Top-N 技术无缝叠加使用，例如流式执行、按序读取以及惰性读取。每一种优化都针对不同的性能瓶颈，而它们组合在一起，使 ClickHouse 能够高效地将 Top-N 查询从处理数百万行数据扩展到处理数十亿行数据。

对用户而言，这意味着一个熟悉的查询模式 ORDER BY … LIMIT N，会随着数据规模的增长而自动受益于越来越激进的裁剪策略。ClickHouse 通过将常见查询模式视为可以在几乎不触碰数据的情况下解决的问题，实现了这一点。

/END/

试用阿里云 ClickHouse企业版

轻松节省30%云资源成本？阿里云数据库ClickHouse 云原生架构全新升级，首次购买ClickHouse企业版计算和存储资源组合，首月消费不超过99.58元（包含最大16CCU+450G OSS用量）了解详情：https://t.aliyun.com/Kz5Z0q9G