跳至主要内容

在 ClickHouse 中使用 JOIN

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

  • 为了获得最佳性能，用户应尽量减少查询中的 JOIN 数量，尤其是在需要毫秒级性能的实时分析工作负载中。目标是在一个查询中最多使用 3 到 4 个连接。我们在数据建模部分详细介绍了一些减少连接的更改，包括反规范化、字典和物化视图。
  • 目前，ClickHouse 不会重新排序连接。始终确保最小的表位于 Join 的右侧。对于大多数连接算法，这将保存在内存中，并确保查询的内存开销最低。
  • 如果您的查询需要直接连接，即 LEFT ANY JOIN - 如下所示，我们建议尽可能使用字典。

NEEDS ALT
  • 如果执行内部连接，通常更优化的做法是使用 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 连接，因为我们不希望笛卡尔积，即我们希望每个帖子只匹配一次。

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

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 的数据的大小。考虑以下示例，我们希望计算 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.)

重新排序此连接可以显著提高性能，达到 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.

选择连接算法​

ClickHouse 支持多种连接算法。这些算法通常以内存使用量换取性能。以下是基于 ClickHouse 连接算法的相对内存消耗和执行时间的概述


NEEDS ALT

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

每种连接算法支持的 JOIN 类型如下所示，在优化之前应予以考虑


NEEDS ALT

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

选择合适的连接算法取决于您是要优化内存还是性能。

优化 JOIN 性能​

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


NEEDS ALT

  • (1) 如果可以将右侧表的数据预加载到内存中的低延迟键值数据结构中，例如字典，并且如果连接键与底层键值存储的关键属性匹配，并且 LEFT ANY JOIN 语义足够 - 那么直接连接适用并提供最快的方法。

  • (2) 如果表的物理行顺序与连接键排序顺序匹配，则取决于具体情况。在这种情况下，完全排序合并连接跳过排序阶段，从而显著减少内存使用量，此外，根据数据大小和连接键值分布，执行时间比某些哈希连接算法更快。

  • (3) 如果右侧表适合内存，即使有并行哈希连接的额外内存使用开销，那么此算法或哈希连接可能会更快。这取决于数据大小、数据类型以及连接键列的值分布。

  • (4) 如果右侧表不适合内存，则再次取决于具体情况。ClickHouse 提供三种不受内存限制的连接算法。所有三种算法都将数据临时溢出到磁盘。完全排序合并连接和部分合并连接需要对数据进行预排序。Grace 哈希连接而是从数据中构建哈希表。根据数据量、数据类型和连接键列的值分布，在某些情况下，从数据中构建哈希表比对数据排序更快。反之亦然。

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

Grace 哈希连接是三种不受内存限制的连接算法中最灵活的，并通过其grace_hash_join_initial_buckets设置提供了对内存使用量与连接速度的良好控制。根据数据量，当桶的数量被选择为使两种算法的内存使用量大致对齐时，Grace 哈希连接可能比部分合并算法快或慢。当 Grace 哈希连接的内存使用量被配置为与完全排序合并的内存使用量大致对齐时，在我们的测试运行中，完全排序合并始终更快。

三种不受内存限制的算法中哪一种最快取决于数据量、数据类型以及连接键列的值分布。始终最好使用现实数据量的现实数据运行一些基准测试，以确定哪种算法最快。

优化内存​

如果您希望优化连接以获得最低的内存使用量而不是最快的执行时间，则可以使用此决策树


NEEDS ALT
  • (1) 如果表的物理行顺序与连接键排序顺序匹配，那么完全排序合并连接的内存使用量将达到最低。此外，连接速度也很快，因为排序阶段已禁用。
  • (2) 通过配置大量桶，可以将Grace 哈希连接调整为非常低的内存使用量，但代价是连接速度。部分合并连接有意使用少量主内存。完全排序合并连接启用外部排序后，通常比部分合并连接使用更多内存（假设行顺序与键排序顺序不匹配），但连接执行时间明显更好。

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