选择正确的联接算法（ClickHouse 中对 Join 类型的支持 - Part 5）

在之前的三篇文章中，我们对为ClickHouse开发的6种不同联接算法进行了深入探讨。在这篇最后的文章中，我们将总结并直接比较所有ClickHouse联接算法的执行时间和内存使用情况。基于这些结果，我们将提供决策树以及联接类型支持概述，供您在决定哪种联接算法最适合您的特定场景时使用。

ClickHouse 联接算法概述

迄今为止，ClickHouse 已开发出以下 6 种联接算法：

• Direct join

• Hash join

• Parallel hash join

• Grace hash join

• Full sorting merge join

• Partial merge join

这些算法决定了联接查询的规划和执行方式。默认情况下，ClickHouse根据使用的联接类型、严格性和被联接表的引擎使用直接联接或哈希联接算法。此外，ClickHouse可以根据资源的可用性和使用情况，在运行时自适应地选择和动态更改联接算法。当将join_algorithm设置为 auto 时，ClickHouse首先尝试使用哈希联接算法，如果该算法的内存限制被违反，算法会即时切换到部分合并联接。您可以通过跟踪日志观察到选择了哪种算法。ClickHouse还允许用户自行指定所需的联接算法。以下图表根据相对内存消耗和执行时间对ClickHouse联接算法进行了概述：

直接联接是ClickHouse最快的联接算法，在右侧表的底层存储支持低延迟键值请求且LEFT ANY JOIN语义适用时使用。特别是对于较大的右侧表，直接联接比所有其他ClickHouse联接算法在执行时间上有显著的改进。

ClickHouse的另外三种联接算法是基于内存中的哈希表：

• 哈希联接是快速但受内存限制的算法，是支持所有联接类型和严格性设置的最通用的联接算法。该算法可能受到内存使用的限制。此外，从联接的右侧表创建内存中的哈希表是单线程的，如果右侧表非常大，可能成为联接执行时间的瓶颈。

• 并行哈希联接可以在右侧表很大的情况下更快地执行，通过同时构建多个哈希表，但它需要更多的内存。

• 优雅哈希联接是一种非受内存限制的版本，它会将数据临时溢出到磁盘，而无需对数据进行排序。这克服了其他非受内存限制的ClickHouse联接算法的一些性能挑战，这些算法也会将数据临时溢出到磁盘，但需要对数据进行预排序。

ClickHouse提供了基于外部排序的另外两种非受内存限制的联接算法：

• 全排序合并联接基于内存或外部排序，并且可以利用联接表的物理行顺序并跳过排序阶段。在这种情况下，联接性能可以与上面图表中的某些哈希联接算法竞争，同时通常需要较少的主内存。

• 部分合并联接针对联接大表时最小化内存使用进行了优化，并始终通过外部排序完全对右侧表进行排序。左侧表也始终在内存中以块为单位进行排序。如果左侧表的物理行顺序与联接键排序顺序匹配，则联接匹配过程运行得更高效。

选择正确的联接算法

联接算法的选择主要取决于三个因素：

• 性能

• 内存使用

• 联接类型支持

下面的三个部分为这些因素提供指导。

性能

除了上面的概述图表外，您可以使用此决策树来选择适合尽快执行联接的正确联接算法：

① 如果右侧表的数据可以预先加载到内存中的低延迟键值数据结构（例如字典）中，并且联接键与底层键值存储的键属性匹配，并且LEFT ANY JOIN语义适用，则可以使用直接联接，它提供了最快的方法。

② 如果表的物理行顺序与联接键排序顺序匹配，则情况就不同了。在这种情况下，全排序合并联接可以跳过排序阶段，从而显著减少内存使用量，并且根据数据大小和联接键值分布，比某些哈希联接算法的执行时间更快。然而，如果右侧表适合内存，即使考虑到并行哈希联接的额外内存使用开销，这个算法或者哈希联接可能更快。这取决于数据大小、数据类型和联接键列的值分布。

③ 如果右侧表不适合内存，则情况又不同了。ClickHouse提供了三种非受内存限制的联接算法。这三种算法都会将数据临时溢出到磁盘。全排序合并联接和部分合并联接需要对数据进行预排序，而优雅哈希联接则是从数据中构建哈希表。根据数据的大小、数据类型和联接键列的值分布，构建哈希表可能比对数据进行排序更快，反之亦然。

部分合并联接在联接大表时优化了内存使用，但联接速度较慢。特别是当左侧表的物理行顺序与联接键排序顺序不匹配时，情况尤其如此。

优雅哈希联接是三种非受内存限制的联接算法中最灵活的，通过其grace_hash_join_initial_buckets设置可以很好地控制内存使用量和联接速度。根据数据的大小，如果选择的桶数量使两种算法的内存使用量大致相同，则优雅哈希联接可能比部分合并算法更快或更慢。在我们的测试运行中，当将优雅哈希联接的内存使用量配置为与 全排序合并 的内存使用量大致对齐时， 全排序合并 总是更快。

哪种非受内存限制的算法最快取决于数据的大小、数据类型和联接键列的值分布。最好根据真实数据的实际数据量运行一些基准测试，以确定哪种算法最快。

内存使用

如果您想将联接优化为最低的内存使用量而不是最快的执行时间，则可以使用以下决策树：

① 如果您的表的物理行顺序与联接键排序顺序匹配，则全排序合并联接的内存使用量将尽可能低。此外，由于禁用了排序阶段，它还具有良好的联接速度。

② 通过配置大量的桶来调优优雅哈希联接，可以实现非常低的内存使用量，但联接速度会受到影响。部分合并联接故意使用较少的主内存。启用外部排序的全排序合并联接通常比部分合并联接使用更多内存（假设行顺序与键排序顺序不匹配），但联接执行时间显著更好。

联接类型支持

在选择正确的联接算法时，不仅执行速度和内存消耗是因素，还取决于联接算法是否支持所需的联接类型。为此，我们创建了这个概述图表：

比较

现在我们将比较所有ClickHouse联接算法的执行时间和峰值内存消耗。

测试配置

我们将使用在第二篇文章中介绍的表格。

在所有联接查询运行中，我们使用默认的max_threads设置。执行查询的ClickHouse Cloud节点具有30个CPU核心（和120 GB的主内存），因此默认的 max_threads 设置为30。用于查询运行的ClickHouse版本是23.5.1。

联接查询

我们使用不同的联接算法设置对右侧较大的表格运行相同的联接查询：

SELECT *
FROM actors AS a
JOIN roles AS r ON a.id = r.actor_id
FORMAT `Null`
SETTINGS join_algorithm = ...;

数据集

 IMDB Large table

回顾一下，在之前的文章中，我们使用了imdb_large数据库中的 actors 和 roles 表。下面的查询列出了每个表中的行数和未压缩数据量：

SELECT
    table,
    formatReadableQuantity(sum(rows)) AS rows,
    formatReadableSize(sum(data_uncompressed_bytes)) AS data_uncompressed
FROM system.parts
WHERE (database = 'imdb_large') AND active
GROUP BY table
ORDER BY table ASC;

┌─table──┬─rows───────────┬─data_uncompressed─┐
│ actors │ 1.00 million   │ 21.81 MiB         │
│ roles  │ 100.00 million │ 2.63 GiB          │
└────────┴────────────────┴───────────────────┘

IMDB X-Large 数据表

为了进一步比较联接算法，我们生成了一个更大的imdb_xlarge数据库：

SELECT
    table,
    formatReadableQuantity(sum(rows)) AS rows,
    formatReadableSize(sum(data_uncompressed_bytes)) AS data_uncompressed
FROM system.parts
WHERE (database = 'imdb_xlarge') AND active
GROUP BY table
ORDER BY table ASC;

┌─table──┬─rows───────────┬─data_uncompressed─┐
│ actors │ 100.00 million │ 2.13 GiB          │
│ roles  │ 1.00 billion   │ 26.33 GiB         │
└────────┴────────────────┴───────────────────┘

在下面的部分，我们将呈现对两个数据库的每个联接算法运行示例查询的图表进行比较。

直接联接有一点特别

请注意，对于直接联接算法，我们使用单独的图表，因为将这种算法与类似的、受内存限制的非排序算法（如哈希和并行哈希）进行比较才有意义。正如前面提到的，使用由字典支持的右侧表的直接联接实际上也是一个左侧任意联接：

SELECT *
FROM actors AS a
LEFT ANY JOIN roles AS r ON a.id = r.actor_id
FORMAT `Null`
SETTINGS join_algorithm = ...;

IMDB Large 联接运行

下面的图表总结了使用 imdb_large 表格的相同示例联接查询的峰值内存使用量和执行时间，其中：

• 联接中右侧的表格是较大的表格

• 使用不同的联接算法设置

• 使用具有 30个CPU核心 和 max_threads 设置为默认值30的节点

请注意，图表中的10次查询运行按执行时间排序，最快的查询运行在图表的左侧：

对于我们的示例表格，最快的联接算法（见①和②）使用了最多的内存。显著的例外是③  full sorting merge 。当可以跳过排序阶段，因为表格在磁盘上的物理行顺序与联接键排序顺序匹配时， full sorting merge 的执行时间变得具有竞争力（或更好-请参见下面的下一个图表），而且所需的内存显著较少。在这种情况下，因为来自两个表格的数据以块方式按顺序通过查询引擎流动，所以在进行合并时同时在内存中只有少量的数据块。

对于④两个联接表格的内存排序，full sorting merge join的内存消耗较高。对于⑤外部排序（排序的数据被溢出到磁盘而不是在内存中排序），内存消耗减少，但执行速度也受到影响。

Grace hash连接是一种灵活的非内存绑定的连接算法，可以通过调整桶的数量来控制内存使用量。在比较中，我们运行了不同桶数量的Grace hash连接算法，以使其内存使用量与其他非内存绑定的连接算法保持一致。

在第⑥次运行中，我们使用4个桶将Grace hash连接的内存使用量与第⑤次运行的全排序合并连接进行了对齐。在这种情况下，Grace hash连接比全排序合并连接慢，用于连接我们的示例表。

在第⑦次运行中，我们使用8个桶将Grace hash连接的内存使用量与第⑨次运行的部分合并连接大致对齐。在这种情况下，Grace hash连接比部分合并连接快两倍。

在第⑧次运行中，我们将Grace hash连接的内存使用量降低到低于部分合并连接的水平，同时仍然实现更快的执行。

对于我们的示例表来说，部分合并连接运行（见第⑨次和第⑩次）是最慢的。部分合并连接完全对右表进行排序，并临时将排序块与最小-最大索引文件存储到磁盘中。然后，它将左表的每个块与右表的排序块进行排序和比较，并利用最小-最大索引跳过不匹配的块。这种方法在节省内存方面效果显著，但是执行速度较慢，特别是在第⑩次运行中，左表的物理行顺序与连接键排序顺序不匹配的情况下。

总体而言，明确对表进行排序以确定连接匹配比仅从其中一个表构建哈希表更耗时。然而，需要注意的是，这些基准测试是在特定的数据集上进行的。根据数据量、数据类型和连接键列的值分布，可能存在排序数据块比构建哈希表更快的情况。

IMDB X-Large 联接运行

下表总结了在使用imdb_xlarge数据库中的表进行连接时的查询运行情况：

在上图中，与之前的图表相比，使用了 imdb_xlarge 数据库中更大的表进行连接。在这种情况下，使用全排序合并连接运行②、③和④比哈希连接运行⑤更快，并且使用的峰值内存更少。正如前面提到的，从哈希连接的右侧表创建内存中的哈希表是单线程的，并且在右侧表非常大时可能成为瓶颈。

当使用8个bucket的 grace hash 运行（⑥）的内存使用量与外部排序的全排序合并运行（④）大致相同时，就像之前的图表一样，grace hash连接对于连接我们的两个示例表而言比全排序合并连接要慢。但在内存使用量大致相等的⑧运行中，即使用64个bucket，与部分合并运行（⑦和⑨）的内存使用量相当，这次与之前的图表相反，部分合并连接比grace hash连接更快。对于我们的示例右侧表，具有10亿行，构建和溢出排序块以及基于最小-最大索引的扫描（部分合并）比构建和溢出以及扫描64个哈希表（带有64个bucket的grace hash）更快。部分合并还受益于左侧表按连接键排序的事实，这使得对我们非常大的右侧表的排序块进行高效的最小-最大索引扫描成为可能。

然而，在运行⑨中，当左侧表的物理行顺序与连接键的排序顺序不匹配时，右侧表排序块的最小-最大索引帮助不大，而且在最坏的情况下，实际上在两个表的块之间创建了一个笛卡尔积：对于左侧表的每个块，都会从磁盘加载一组大的排序块。显然，这导致非常高的执行时间，尤其是对于非常大的表格。

直接联接运行

IMDB Large 联接运行

以下图表总结了相同的示例 LEFT ANY JOIN 查询在 imdb_large 数据库中的峰值内存使用和执行时间，具体包括：

• 右侧表为较大的表

• 不同的连接算法设置

• 具有 30个CPU核心 的节点和 max_threads 的默认设置为30

图表中的4个查询运行按执行时间排序，最快的查询运行在图表的左侧：

直接连接（direct join）是速度最快的。① 当右侧表由使用 flat 内存布局的字典支持时，该算法比④ hash join快约25倍，比③ parallel hash 快约15倍，比② 使用 hashed 内存布局的字典支持的直接连接快约2.5倍。无论字典布局类型如何，总体峰值内存消耗都较低，与hash算法运行相比。

IMDB X-Large 联接运行

以下图表总结了直接连接算法的比较运行，当联接的表来自imdb_xlarge数据库时：

① 使用 flat 内存布局的字典支持的direct连接算法在约1秒内将左表的1亿行进行连接。速度非常快！与④  hash 算法相比快约32倍，与③ parallel hash算法相比快约22倍，与② 使用 hashed 内存布局的字典支持的直接连接相比快约4倍。与前面的图表类似，直接连接的整体峰值内存消耗较低，相比于hash算法的运行。

这就结束了我们对ClickHouse联接算法的探索。

在我们的下一个联接系列中，我们将探索ClickHouse中的分布式联接执行，请继续关注！

联系我们

手机号：13910395701

邮箱：Tracy.Wang@clickhouse.com

满足您所有的在线分析列式数据库管理需求