在 ClickHouse 中使用 JOIN

ClickHouse 具有完整的 JOIN 支持，并提供多种连接算法。为了最大限度地提高性能，我们建议遵循本指南中列出的 JOIN 优化建议。

为了获得最佳性能，您应该尽量减少查询中的 JOIN 数量，尤其是在需要毫秒级性能的实时分析工作负载中。目标是在查询中最多使用 3 到 4 个 JOIN。我们在数据建模部分详细介绍了一些减少 JOIN 的方法，包括反规范化、字典和物化视图。
从 ClickHouse 24.12 开始，查询计划器会自动重新排序两表 JOIN，将较小的表放在右侧以获得最佳性能。在版本 25.9 中，这扩展到优化连接三个或更多表的查询中的 JOIN 顺序。
如果您的查询需要直接 JOIN，即 LEFT ANY JOIN - 如下所示，我们建议尽可能使用字典。

如果执行内连接，通常将其编写为使用 IN 子句的子查询会更优化。考虑以下查询，它们在功能上是等效的。两者都查找在问题中未提及 ClickHouse 但在 comments 中提及的 posts 的数量。

SELECT count()
FROM stackoverflow.posts AS p
ANY INNER `JOIN` stackoverflow.comments AS c ON p.Id = c.PostId
WHERE (p.Title != '') AND (p.Title NOT ILIKE '%clickhouse%') AND (p.Body NOT ILIKE '%clickhouse%') AND (c.Text ILIKE '%clickhouse%')

┌─count()─┐
│       86 │
└─────────┘

1 row in set. Elapsed: 8.209 sec. Processed 150.20 million rows, 56.05 GB (18.30 million rows/s., 6.83 GB/s.)
Peak memory usage: 1.23 GiB.

请注意，我们使用 ANY INNER JOIN 而不是仅仅使用 INNER JOIN，因为我们不希望笛卡尔积，即我们希望每个 post 只有一个匹配项。

此 JOIN 可以使用子查询重写，从而显著提高性能

SELECT count()
FROM stackoverflow.posts
WHERE (Title != '') AND (Title NOT ILIKE '%clickhouse%') AND (Body NOT ILIKE '%clickhouse%') AND (Id IN (
        SELECT PostId
        FROM stackoverflow.comments
        WHERE Text ILIKE '%clickhouse%'
))
┌─count()─┐
│       86 │
└─────────┘

1 row in set. Elapsed: 2.284 sec. Processed 150.20 million rows, 16.61 GB (65.76 million rows/s., 7.27 GB/s.)
Peak memory usage: 323.52 MiB.

虽然 ClickHouse 会尝试将条件推送到所有 JOIN 子句和子查询，但我们建议用户始终手动将条件应用于所有子子句，从而最大限度地减少要 JOIN 的数据量。考虑以下示例，我们希望计算自 2020 年以来与 Java 相关的帖子收到的赞数量。

一个朴素的查询，左侧的表较大，需要 56 秒才能完成

SELECT countIf(VoteTypeId = 2) AS upvotes
FROM stackoverflow.posts AS p
INNER JOIN stackoverflow.votes AS v ON p.Id = v.PostId
WHERE has(arrayFilter(t -> (t != ''), splitByChar('|', p.Tags)), 'java') AND (p.CreationDate >= '2020-01-01')

┌─upvotes─┐
│  261915 │
└─────────┘

1 row in set. Elapsed: 56.642 sec. Processed 252.30 million rows, 1.62 GB (4.45 million rows/s., 28.60 MB/s.)

重新排序此 JOIN 可以显著提高性能到 1.5 秒

SELECT countIf(VoteTypeId = 2) AS upvotes
FROM stackoverflow.votes AS v
INNER JOIN stackoverflow.posts AS p ON v.PostId = p.Id
WHERE has(arrayFilter(t -> (t != ''), splitByChar('|', p.Tags)), 'java') AND (p.CreationDate >= '2020-01-01')

┌─upvotes─┐
│  261915 │
└─────────┘

1 row in set. Elapsed: 1.519 sec. Processed 252.30 million rows, 1.62 GB (166.06 million rows/s., 1.07 GB/s.)

向左侧表添加筛选器可以进一步提高性能到 0.5 秒。

SELECT countIf(VoteTypeId = 2) AS upvotes
FROM stackoverflow.votes AS v
INNER JOIN stackoverflow.posts AS p ON v.PostId = p.Id
WHERE has(arrayFilter(t -> (t != ''), splitByChar('|', p.Tags)), 'java') AND (p.CreationDate >= '2020-01-01') AND (v.CreationDate >= '2020-01-01')

┌─upvotes─┐
│  261915 │
└─────────┘

1 row in set. Elapsed: 0.597 sec. Processed 81.14 million rows, 1.31 GB (135.82 million rows/s., 2.19 GB/s.)
Peak memory usage: 249.42 MiB.

可以通过将 INNER JOIN 移动到子查询来进一步改进此查询，如前所述，同时在外部和内部查询中保留筛选器。

SELECT count() AS upvotes
FROM stackoverflow.votes
WHERE (VoteTypeId = 2) AND (PostId IN (
        SELECT Id
        FROM stackoverflow.posts
        WHERE (CreationDate >= '2020-01-01') AND has(arrayFilter(t -> (t != ''), splitByChar('|', Tags)), 'java')
))

┌─upvotes─┐
│  261915 │
└─────────┘

1 row in set. Elapsed: 0.383 sec. Processed 99.64 million rows, 804.55 MB (259.85 million rows/s., 2.10 GB/s.)
Peak memory usage: 250.66 MiB.

选择 JOIN 算法

ClickHouse 支持多种 JOIN 算法。这些算法通常在内存使用量和性能之间进行权衡。以下概述了基于相对内存消耗和执行时间的 ClickHouse JOIN 算法

这些算法决定了 JOIN 查询的计划和执行方式。默认情况下，ClickHouse 根据使用的 JOIN 类型、严格性和连接表的引擎使用直接或哈希 JOIN 算法。或者，可以配置 ClickHouse 以自适应地选择并在运行时动态更改要使用的 JOIN 算法，具体取决于资源可用性和使用情况：当 join_algorithm=auto 时，ClickHouse 首先尝试哈希 JOIN 算法，如果该算法的内存限制被违反，则该算法会动态切换到部分合并 JOIN。您可以通过跟踪日志观察选择的算法。ClickHouse 还允许您通过 join_algorithm 设置自行指定所需的 JOIN 算法。

每个 JOIN 算法支持的 JOIN 类型如下所示，应在优化之前加以考虑

可以在此处找到每个 JOIN 算法的完整详细描述，包括它们的优点、缺点和缩放属性。

选择合适的 JOIN 算法取决于您是希望优化内存还是性能。

优化 JOIN 性能

如果您的关键优化指标是性能，并且希望尽可能快地执行 JOIN，则可以使用以下决策树来选择正确的 JOIN 算法

(1) 如果右侧表中的数据可以预加载到内存中的低延迟键值数据结构中，例如字典，并且如果 JOIN 键与底层键值存储的键属性匹配，并且如果 LEFT ANY JOIN 语义足够 - 那么 直接 JOIN 适用，并提供最快的方法。
(2) 如果您的表的物理行顺序与 JOIN 键排序顺序匹配，那么这取决于情况。在这种情况下，完全排序合并 JOIN 跳过排序阶段，从而显著减少内存使用量，并且，取决于数据大小和 JOIN 键值分布，执行时间比某些哈希 JOIN 算法更快。
(3) 如果右侧表适合内存，即使有 并行哈希 JOIN 的额外内存使用开销，那么此算法或哈希 JOIN 可能会更快。这取决于数据大小、数据类型和 JOIN 键列的值分布。
(4) 如果右侧表不适合内存，那么再次取决于情况。ClickHouse 提供三种不依赖内存的 JOIN 算法。所有三个算法都会暂时将数据溢出到磁盘。完全排序合并 JOIN 和 部分合并 JOIN 需要先对数据进行排序。Grace 哈希 JOIN 而是从数据构建哈希表。根据数据量、数据类型和 JOIN 键列的值分布，构建哈希表可能比排序数据更快。反之亦然。

部分合并 JOIN 针对在连接大表时最大限度地减少内存使用量进行了优化，但代价是 JOIN 速度非常慢。当左侧表的物理行顺序与 JOIN 键排序顺序不匹配时，尤其如此。

Grace 哈希 JOIN 是三种不依赖内存的 JOIN 算法中最灵活的算法，并提供通过其 grace_hash_join_initial_buckets 设置来控制内存使用量与 JOIN 速度的良好控制。根据数据量，Grace 哈希可能比部分合并算法更快或更慢，当 bucket 的数量选择为使两种算法的内存使用量大致对齐时。

三种不依赖内存的算法中哪一种最快取决于数据量、数据类型和 JOIN 键列的值分布。始终最好使用真实数据量和真实数据运行一些基准测试，以确定哪种算法最快。

优化内存

如果您想优化 JOIN 以获得最低的内存使用量而不是最快的执行时间，则可以使用以下决策树。

(1) 如果您的表的物理行顺序与 JOIN 键排序顺序匹配，那么 完全排序合并 JOIN 的内存使用量尽可能低。此外，由于跳过了排序阶段，因此具有良好的 JOIN 速度禁用。
(2) 可以通过配置大量 bucket 来调整 Grace 哈希 JOIN 以实现非常低的内存使用量，但代价是 JOIN 速度。部分合并 JOIN 故意使用少量主内存。具有启用外部排序的 完全排序合并 JOIN 通常比部分合并 JOIN 使用更多的内存（假设行顺序与键排序顺序不匹配），但具有显著更好的 JOIN 执行时间。

对于需要更多上述细节的用户，我们建议阅读以下博客系列。

选择 JOIN 算法​

优化 JOIN 性能​

优化内存​

选择 JOIN 算法

优化 JOIN 性能

优化内存