ClickHouse JOIN性能狂飙26倍！两年磨一剑的秘密揭秘

 

本文字数：10964；估计阅读时间：28分钟

作者：Tom Schreiber

 

 

TL;DR 

在过去两年中，ClickHouse 在连接操作繁重的分析工作负载方面实现了 26 倍 的性能提升。本文将深入探讨使连接成为其核心优势的工程实践。

 

 

两年专注于连接优化工程

ClickHouse 以其快速的分析查询、高数据压缩率和大规模实时处理能力而闻名。

在过去两年里，主要的工程工作之一，便是将同样的性能优势带入到包含大量连接操作的 SQL 查询中。

在 ClickHouse 24.5 发布网络研讨会 上，ClickHouse 的发明者 Alexey Milovidov 明确指出了未来的发展方向：

“今后，您将在每一个 ClickHouse 版本中，持续看到 JOIN（连接）性能的改进。”

下面的图表展示了这一承诺在实践中的表现。

 

第一年主要致力于奠定基础：实现了更快的并行哈希连接（parallel hash join）、更智能的查询规划、激进的谓词下推（filter pushdown）以及局部连接重排序（local join reordering）。

截至 25.4 版本，同样的 TPC-H SF100 以连接操作为主的工作负载，相比 22.4 版本已实现了 4.4 倍 的性能提升。

第二年则更上一层楼。在 25.4 到 26.4 版本之间，新一轮的优化器（optimizer）和执行（execution）层面的改进，使得相同的工作负载在采用默认设置的情况下，性能又提升了 6 倍。

综合来看，ClickHouse 目前在 TPC-H SF100 工作负载上的性能，比 22.4 版本 提升了 26 倍。

本文将详细解释我们是如何实现这些里程碑的。而 配套文章 则揭示了这些成就所带来的突破：ClickHouse Cloud 现在运行 TPC-H 的成本低于一美分，并在 SF100 评测中与 Snowflake、Databricks、BigQuery 和 Redshift 展开正面竞争。

 

 

第一年：奠定基础

一年前，在 我们在旧金山举行的首届 Open House 用户大会 上，ClickHouse 连接工程负责人 Robert Schulze 详细介绍了第一年在连接性能优化方面的主要进展。

第一年的工作主要专注于奠定基础。ClickHouse 在 24.12 版本中将并行哈希连接（parallel hash join）设为默认选项，为两表连接新增了局部自动连接重排序（local automatic join reordering）功能，并推出了一系列底层的执行（execution）层面改进。

这些改进中的多项功能，均在连续的多个版本中得以实现并发布：

• 24.7 版本 ： 改进了哈希表分配，以实现更快的并行哈希连接

• 24.12 版本 ：将 并行哈希连接 设为默认策略，并引入了 局部自动连接重排序

• 25.1 版本 ： 加快了哈希连接的探测阶段

• 25.2 版本 ： 消除了哈希连接构建阶段的线程争用

综合来看，这些改进使得相同的 TPC-H SF100 连接密集型工作负载在 25.4 版本时比 22.4 版本快了 4.4 倍。

然而，第一年仅仅是奠定基础。第二年，连接在默认设置下的性能才得到了显著提升。

 

 

第二年：默认设置下，连接性能达到领先水平

在今年的 Open House 活动上，Robert 再次回归，带来了连接性能故事的第二章。

第一年奠定了基础。第二年则让连接在默认设置下具备了竞争力。

目标很简单：用户无需重写查询、手动调优连接顺序，也无需了解应启用哪些内部优化。ClickHouse 应该能更好地识别连接密集型 SQL，自动选择更优的执行计划，并在执行过程中避免不必要的工作。

下面的图表展示了 25.4 版本到 26.4 版本之间默认生效的四个主要改进。综合来看，它们使相同的 TPC-H SF100 连接密集型工作负载又快了 6 倍。

 

本文的其余部分将详细介绍这四个改进：

① 连接中的相关子查询 — 直接支持更多实际场景中的 SQL 查询。

② 惰性列复制 — 避免复制连接操作产生的重复值。

③ 运行时过滤器 — 在执行代价高昂的哈希表查找之前，跳过探测侧（probe-side）的行。

④ 基于统计信息的连接重排序 —— 自动选择更优的连接计划。

所有示例都在相同硬件上使用 TPC-H SF100，即一台 AWS EC2 m6i.8xlarge 实例（32 个 vCPU，128 GiB RAM），因此性能提升很容易对比。

 

 

 ① JOIN 中的相关子查询

 

两年前，问题不仅仅是一些连接的执行速度低于预期。更重要的是，一些涉及大量连接的重要查询根本无法运行。

为什么这很重要

在我们提交至 VLDB 2024 的第一篇研究论文的 TPC-H 基准测试部分中，我们不得不排除七个查询：Q2、Q4、Q13、Q17 以及 Q20–Q22。原因在于它们使用了相关子查询 (correlated subqueries)，而 ClickHouse 当时尚未完全支持。

一个包含相关子查询的 TPC-H 查询示例

TPC-H Q4 便是一个很好的例子。它包含一个针对 lineitem 表的内部查询，该内部查询引用了 orders 表，后者来自外部查询：

 

 

这正是它被称为相关子查询的原因：内部查询无法独立理解，因为它依赖于外部查询中的值。

从逐行执行到面向集合的计划

相关子查询很常见，因为它们编写起来十分自然。在生成的 SQL 中，包括由代理系统 (agentic systems) 生成的查询，它们也越来越普遍。然而，对于数据库引擎而言，高效执行这类查询却颇具挑战。

最直接的方案是，针对外部查询的每一行，都执行一次内部查询。这种方法虽然简单，但对性能而言往往是灾难性的。为了加速这些查询，优化器必须对其进行去相关化 (decorrelate) 处理：将查询重写为等效的面向集合的执行计划，这通常涉及使用连接 (joins)、聚合 (aggregations)、半连接 (semi joins) 或反连接 (anti joins) 等操作。

这种重写必须在处理重复项、聚合和 NULL 值时，保留 SQL 语义的一致性，这使得其复杂程度远超预期。

结果

现在，相关子查询在 ClickHouse 中已成为一等公民的 SQL 特性。此前需要手动重写（包括那些曾被排除的 TPC-H 查询）的查询，现在都可以直接运行。

 

 

② JOIN 中的惰性列复制

当 JOIN 操作在逻辑上多次重复相同的值时，惰性列复制 (Lazy column replication) 能够显著降低 CPU 和内存消耗。

问题：逻辑重复变为物理复制

在 JOIN 结果中，一行输入记录可能产生多行输出记录。例如，在 TPC-H 中，每个客户可以拥有多个订单。当我们对 orders 表和 customer 表进行 JOIN 操作时，客户信息列会针对每个匹配的订单逻辑上重复出现一次。

SELECT
    o.o_orderkey,
    o.o_orderdate,
    c.c_name,
    c.c_address,
    c.c_comment
FROM orders AS o
INNER JOIN customer AS c
    ON o.o_custkey = c.c_custkey;

逻辑上，结果示例如下：

 

 

从概念上讲，这是正确的结果：每个匹配订单的旁边都显示了相同的客户值。

开销大的部分在于物理复制。如果 ClickHouse 将 c_address 或 c_comment 等宽值复制到每个连接行中，那么连接操作会重复复制相同数据，从而消耗大量的 CPU 周期和内存带宽。而如果后续操作符（例如聚合）又丢弃了这些行中的大部分，那么之前的大量复制工作就都成了无用功。

列惰性复制避免了这种预先复制。

ClickHouse 不会在连接过程中物理复制重复值，而是将原始值在小型字典数据结构中仅存储一次，并将连接列表示为指向这些值的紧凑索引。

 

 

如果后续查询步骤需要完全具体化的列，ClickHouse 仍然可以生成它。但许多分析操作可以直接在紧凑表示上进行，因此重复值根本无需复制。

这对于复制宽列（例如字符串）的 JOIN 尤其有用。

基准测试：避免物理复制

在此基准测试中，我们对 orders 表进行自连接，以创建大量重复的 o_comment 值，然后立即使用 cityHash64 对其进行处理。

首先，我们在未启用列惰性复制的情况下运行了以下示例 JOIN。

SELECT sum(cityHash64(t1.o_comment))
FROM orders AS t1
INNER JOIN orders AS t2
    ON t1.o_custkey = t2.o_custkey
SETTINGS
    enable_lazy_columns_replication = 0,
    allow_special_serialization_kinds_in_output_formats = 0;

三次运行中最快的一次：

1 row in set. Elapsed: 5.419 sec. Processed 300.00 million rows, 8.89 GB (55.36 million rows/s., 1.64 GB/s.)
Peak memory usage: 5.27 GiB.

接着，我们运行了启用列惰性复制的相同查询。

这是列惰性复制的理想场景：o_comment 仅作为函数的输入使用，因此 ClickHouse 可以直接在紧凑表示上计算 cityHash64，而无需物理具体化重复的字符串列。

SELECT sum(cityHash64(t1.o_comment))
FROM orders AS t1
INNER JOIN orders AS t2
    ON t1.o_custkey = t2.o_custkey
SETTINGS
    enable_lazy_columns_replication = 1,
    allow_special_serialization_kinds_in_output_formats = 1;

三次运行中最快的一次：

1 row in set. Elapsed: 2.847 sec. Processed 300.00 million rows, 8.89 GB (105.36 million rows/s., 3.12 GB/s.)
Peak memory usage: 5.22 GiB.

结果

结果：列惰性复制使此 JOIN 提速 1.9 倍，将运行时间从 5.419 秒缩短至 2.847 秒，同时峰值内存使用量也从 5.27 GiB 略微降低到 5.22 GiB。

这种速度提升得益于直接在紧凑的复制列表示上计算 cityHash64，而非物理复制重复的字符串。

 

 

③ JOIN 中的运行时过滤器

运行时过滤器可减少哈希 JOIN 中探测端不必要的工作负载。

这项技术是对ClickHouse的全排序归并连接算法 (Full Sorting Merge Join Algorithm)中已用方法的推广。在该算法中，参与连接的表可以在连接操作执行前，通过彼此的连接键 (Join Keys) 进行过滤。ClickHouse为默认的并行哈希连接算法 (Parallel Hash Join Algorithm)引入了一个类似的概念。

值得回顾的是，并行哈希连接算法 (Parallel Hash Join Algorithm)是哈希连接 (Hash Join)的一种变体。它通过将输入数据拆分，并发地构建多个哈希表，从而加速了构建阶段 (Build Phase)。

在下面的示例中，我们将max_threads设置为2，因此ClickHouse会并行构建两个哈希表。在实际应用中，max_threads通常根据可用CPU核心数推导而来。

该图展示了一个TPC-H查询的物理查询计划 (Physical Query Plan)，在ClickHouse中被称为查询管道 (Query Pipeline)。这个查询将orders表与customer表连接起来。

 

 

① customer表上的谓词被下推，并在连接操作之前执行。

② 在构建阶段 (Build Phase)，作为连接右侧的经过过滤的customer行被分成两个桶，因为max_threads被设置为2。每个桶用于构建一个内存哈希表。

③ 在探测阶段 (Probe Phase)，作为连接左侧的orders行被并行流式传输，并路由到对应的哈希表进行查找。

问题：探测侧的冗余工作

值得注意的是，在步骤②的构建阶段 (Build Phase)，只有部分customer键被插入到哈希表中。然而，在步骤③中，ClickHouse仍然处理所有的orders行。对于那些customer已经被customer过滤器移除的orders行，查找操作注定失败。在连接操作将这些行拒绝之前，它们仍然会消耗CPU和内存带宽。

核心思想：在查找前过滤探测行

运行时过滤器 (Runtime Filters) 正是为了解决这种冗余工作而设计的。

当ClickHouse从经过筛选的客户行构建哈希表（在DRAM中）时，它还会根据成功进入构建侧的连接键创建一个紧凑的过滤器（布隆过滤器或min/max值）。在我们的示例中，这意味着只有满足 c_nation = 'DE' 条件的客户键会被用于构建此过滤器。

该过滤器在哈希表查找之前应用于 orders 表（探测侧）。如果某一行订单数据中的 o_custkey 不存在于经过过滤的构建侧中，ClickHouse 就可以提前将其丢弃，从而避免将其送入连接操作。

运行时过滤器远小于哈希表本身，因此它可以更接近 CPU，通常驻留在 L1 或 L2 缓存中。

运行时过滤器如何融入查询管道

更新后的管道结构如下：

 

 

更新后的管道增加了两个步骤：

② 在读取经过筛选的客户行时，ClickHouse 会根据这些行的连接键构建一个运行时过滤器。

④ 在探测哈希表之前，ClickHouse 将该运行时过滤器应用于 orders 表（探测侧）的行。

⑤ 只有通过运行时过滤器的行，才能继续进行哈希表查找。

并行哈希连接的结构保持不变，但大量探测侧的行会在开销较大的查找操作之前被移除。

这使得并行哈希连接结构保持不变，同时移除了那些永远无法匹配的探测侧行。在选择性连接中，这可以显著减少 CPU 工作量和内存带宽消耗。

在查询计划中的体现

我们可以通过 EXPLAIN plan 查看逻辑查询计划来观察这一点。

首先，当运行时预过滤禁用时：

EXPLAIN plan
SELECT *
FROM orders, customer
WHERE o_custkey = c_custkey
SETTINGS enable_join_runtime_filters = 0;

...                             
Join                                                       
...
ReadFromMergeTree (default.orders)                   
ReadFromMergeTree (default.customer)

我们将跳过计划的其余部分，专注于其核心机制。

从下到上阅读查询计划的输出，我们可以看到 ClickHouse 计划从 orders 和 customer 这两个表读取数据并执行连接操作。

接下来，让我们检查相同连接的逻辑查询计划，但这一次，我们启用运行时预过滤功能：

EXPLAIN plan
SELECT *
FROM orders, customer
WHERE o_custkey = c_custkey
SETTINGS enable_join_runtime_filters = 1;

计划中的相关部分如下所示：

...
Join                                                                                                                                                                                             
...                                                                                                                                                                         
Prewhere filter column: __filterContains(_runtime_filter_14211390369232515712_0, __table1.o_custkey)                                                                          
...
BuildRuntimeFilter (Build runtime join filter on __table2.c_custkey (_runtime_filter_14211390369232515712_0))
...

从下到上阅读该计划，我们可以看到 ClickHouse 首先 ① 从右表 (customer) 的连接键值构建一个运行时过滤器。然后，该运行时过滤器 ② 作为 PREWHERE 过滤器应用于左表 (orders)，从而允许在连接执行之前跳过不匹配的行。

基准测试：更少的行，更少的内存占用

为了衡量其效果，我们使用一个稍微扩展的查询版本，该查询连接了 orders、customer 和 nation 表，并计算法国客户的平均订单总额。

我们首先在 禁用运行时预过滤 (runtime pre-filtering) 的情况下运行：

SELECT avg(o_totalprice)
FROM orders, customer, nation
WHERE (c_custkey = o_custkey) AND (c_nationkey = n_nationkey) AND (n_name = 'FRANCE')
SETTINGS enable_join_runtime_filters = 0;

┌──avg(o_totalprice)─┐
│ 151149.41468432106 │
└────────────────────┘

1 row in set. Elapsed: 1.005 sec. Processed 165.00 million rows, 1.92 GB (164.25 million rows/s., 1.91 GB/s.)
Peak memory usage: 1.24 GiB.

接着，我们再次运行相同的查询，但这次是 启用运行时预过滤：

SELECT avg(o_totalprice)
FROM orders, customer, nation
WHERE (c_custkey = o_custkey) AND (c_nationkey = n_nationkey) AND (n_name = 'FRANCE')
SETTINGS enable_join_runtime_filters = 1;

┌──avg(o_totalprice)─┐
│ 151149.41468432106 │
└────────────────────┘

1 row in set. Elapsed: 0.471 sec. Processed 165.00 million rows, 1.92 GB (350.64 million rows/s., 4.08 GB/s.)
Peak memory usage: 185.18 MiB.

 

结果

运行时预过滤使此查询速度 提升了 2.1 倍，执行时间从 1.005 秒缩短至 0.471 秒，同时将峰值内存从 1.24 GiB 降低到 185 MiB。

通过提前过滤行，ClickHouse 避免了探查侧无法产生匹配的不必要工作，从而减少了 CPU 负载和内存使用。

 

 

④ 基于统计的连接重排序 (join reordering)

ClickHouse 现在可以对涉及数十个表和 所有主要连接类型 的复杂连接关系进行重排序。

之所以如此重要，是因为 SQL 连接满足 结合律：相同的逻辑查询通常可以通过多种不同的连接顺序执行。虽然最终结果相同，但执行时间可能会大相径庭。

问题：连接顺序至关重要

下图展示了同一连接查询的三种不同连接顺序，以及由此产生的哈希连接树 (hash join trees)。在这三种情况下，查询结果都是相同的。

 

 

查询连接的表越多，连接顺序就变得越发重要。

正如前一节所解释的，在哈希连接 (hash join) 中，右侧用于构建内存中的哈希表，而左侧则探查该哈希表。通常，将较小的输入放在构建哈希表的一侧会效率更高。

在某些情况下，好的连接顺序和差的连接顺序在执行时间上可能相差多个数量级！

寻找最佳连接顺序

寻找一个好的连接顺序是困难的，因为搜索空间增长得极快。仅有 12 个连接表时，可能的连接顺序就已达 28 万亿 种。

因此，ClickHouse 使用了一个连接顺序优化器。从宏观层面来看，它会枚举候选的连接树，评估它们的成本，然后选择一个最佳方案。

 

列统计数据助力优化器发挥效用

优化器需要基数估计：即在应用过滤器和连接谓词后，每个中间连接结果预计包含的行数。

这些估计来源于列统计信息。自 26.4 版本以来，ClickHouse 会自动为所有表生成这些统计信息，从而使基于统计信息的连接重排序在默认设置下也能发挥作用。

对于小型连接图，ClickHouse 可以使用穷举动态规划 (DPSize) 来寻找最优顺序。而对于大型连接图，它则采用贪婪搜索，这种方法速度快得多，但不能保证找到最优解。

 

 

基准测试：从一小时到 2.7 秒

为了衡量其影响，我们对相同的六表 TPC-H 查询进行了两次运行：

1. 不启用统计信息：使用 默认 DDL 创建的表。

2. 启用统计信息：使用 启用列统计信息的扩展 DDL 创建的表。

两次运行均使用了相同的查询和相同的连接顺序优化器设置。

SELECT
  n_name,
  sum(l_extendedprice * (1 - l_discount)) AS revenue
FROM
  customer, 
  orders, 
  lineitem, 
  supplier, 
  nation, 
  region
WHERE
c_custkey = o_custkey
AND l_orderkey = o_orderkey
AND l_suppkey = s_suppkey
AND c_nationkey = s_nationkey
AND s_nationkey = n_nationkey
AND n_regionkey = r_regionkey
AND r_name = 'ASIA'
AND o_orderdate >= DATE '1994-01-01'
AND o_orderdate < DATE '1994-01-01' + INTERVAL '1' year
GROUP BY
  n_name
ORDER BY
  revenue DESC;

首先，我们在不启用统计信息的表上执行了查询：

USE tpch_no_stats;
SET query_plan_optimize_join_order_limit = 10;
SET allow_statistics_optimize = 1;

┌─n_name────┬─────────revenue─┐
│ VIETNAM   │  5310749966.867 │
│ INDIA     │ 5296094837.7503 │
│ JAPAN     │ 5282184528.8254 │
│ CHINA     │ 5270934901.5602 │
│ INDONESIA │ 5270340980.4608 │
└───────────┴─────────────────┘

5 rows in set. Elapsed: 3903.678 sec. Processed 766.04 million rows, 16.03 GB (196.23 thousand rows/s., 4.11 MB/s.)
Peak memory usage: 99.12 GiB.

这耗时一个多小时！🐌 并占用了 99 GiB 的内存。

接着，我们在启用统计信息的表上重复执行了相同的查询：

USE tpch_stats;
SET query_plan_optimize_join_order_limit = 10;
SET allow_statistics_optimize = 1;

┌─n_name────┬─────────revenue─┐
│ VIETNAM   │  5310749966.867 │
│ INDIA     │ 5296094837.7503 │
│ JAPAN     │ 5282184528.8254 │
│ CHINA     │ 5270934901.5602 │
│ INDONESIA │ 5270340980.4608 │
└───────────┴─────────────────┘

5 rows in set. Elapsed: 2.702 sec. Processed 638.85 million rows, 14.76 GB (236.44 million rows/s., 5.46 GB/s.)
Peak memory usage: 3.94 GiB.

结果

采用基于统计信息的连接重排序后，这个六表 TPC-H 查询的执行时间从 3903.7 秒降至 2.7 秒，速度提升了约 1,450 倍，同时峰值内存占用也从 99.1 GiB 降至 3.9 GiB。

 

 

带来的突破

两年前，我们决定将连接操作打造成为 ClickHouse 的一项核心优势。

第一年，我们奠定了基础：实现了更快的并行哈希连接、更智能的构建/探测端选择、更优的规划、更积极的过滤器下推以及局部连接重排序。到 25.4 版本时，相同的 TPC-H SF100 连接密集型工作负载已比 22.4 版本快了 4.4 倍。

第二年，连接性能再次获得显著提升。在 25.4 到 26.4 版本之间，通过一系列优化器和执行方面的改进，使得相同的工作负载在默认设置下又提速了 6 倍。

我们上面介绍的四项改进，每一项都针对连接问题的不同方面：

① 关联子查询 (Correlated subqueries) 弥补了 SQL 的一个主要兼容性空白。ClickHouse 现在可以在 SELECT、WHERE 和 HAVING 子句中运行关联子查询，包括那些以前需要手动重写或必须完全排除的 TPC-H 查询。

② 惰性列复制 (Lazy column replication) 避免了对连接操作产生的重复值进行物理复制。在我们的 TPC-H 示例中，它使连接速度提升了 1.9 倍。

③ 运行时过滤器 (Runtime filters) 通过在行到达哈希表查找前进行过滤，减少了探测端不必要的计算开销。在我们的基准测试中，运行时预过滤使查询速度提升了 2.1 倍，并将峰值内存降低了 近 7 倍。

④ 基于统计信息的连接重排序 (Statistics-based join reordering) 使 ClickHouse 能够自动选择更优的物理连接计划。在针对六张表的 TPC-H 查询中，运行时从 3903.7 秒缩短至 2.7 秒——速度提升了约 1,450 倍——同时峰值内存从 99.1 GiB 降至 3.9 GiB。

综合来看，这些改进将连接操作从“ClickHouse 能够处理”提升到“ClickHouse 能够以极具竞争力、自动化且采用默认设置的方式高效处理”的水平。

正是这些改进使得相关基准测试得以实现：ClickHouse Cloud 现在能够以不到一美分的成本，快速且经济高效地运行完整的连接密集型 TPC-H 工作负载，并与 Snowflake、Databricks、BigQuery 和 Redshift 展开竞争。

 

 

 

后续计划

ClickHouse 在连接（joins）方面已取得了长足的进步。

两年前，TPC-H SF100 工作负载的运行表现截然不同。经过第一年对连接（join）工程的重点投入，其速度已提升 4.4 倍。在第二年之后，相较于 22.4 版本，目前速度已提升 26 倍，其中仅去年一年在默认设置下就贡献了额外的 6 倍加速。

本文所讲述的正是 ClickHouse 在过去两年里，如何将连接操作打造成为其一流优势的历程。

但我们才刚刚起步。第三年的工作已全面展开，分布式连接是迈向 TPC-H SF1000 及更大规模工作负载的关键一步。

我们将在一年后再次向大家汇报。

“你们何时会停止优化连接性能？我们永远不会停止！”——Alexey Milovidov，ClickHouse 发明者，在 ClickHouse 25.10 发布网络研讨会上

 

ClickHouse是面向 AI 时代打造的高性能实时分析数据库，能够以极致性能处理海量数据分析任务。凭借高并发、低延迟和云原生架构，ClickHouse 广泛应用于可观测性、数据仓库、实时分析及 AI 数据基础设施等场景。我们致力于帮助企业在公有云平台上构建安全、弹性且高性价比的实时分析与 AI 数据平台，加速释放数据价值，推动智能化创新与数字化转型。目前，Trip.com、DiDi、Meta、Sony、Netflix、Deutsche Bank、Sierra、Cloudflare 等全球领先企业均在使用 ClickHouse 支撑其关键业务和数据分析平台。