ClickHouse选择正确的join算法

支持的JOIN类型

 

JOIN算法概览

clickhouse提供了6种JOIN算法：

1.直接连接(Direct join)

2.哈希连接(Hash join)

3.并行哈希连接(Parallel hash join)

4.优雅哈希连接(Grace hash join)

5.全排序合并连接(Full sorting merge join)

6.部分合并连接(Partial merge join)

 

这些算法决定了连接查询的执行计划和执行方式。默认情况下，ClickHouse会根据连接类型、严格性以及被连接表的引擎，使用直接连接或哈希连接算法。

另外，ClickHouse可以配置为根据资源的可用性和使用情况，自适应地选择并动态地更改运行时使用的连接算法：当join_algorithm设置为auto时，ClickHouse首先尝试哈希连接算法，如果超出了哈希连接算法内存的限制，那么算法将即时切换到部分合并连接。

:) show settings like 'join_algorithm';

SHOW SETTINGS LIKE 'join_algorithm'

Query id: bbdc4ad4-0d60-4f84-bf36-93749e733782

┌─name───────────┬─type──────────┬─value───┐
│ join_algorithm │ JoinAlgorithm │ default │
└────────────────┴───────────────┴─────────┘

default表示等价与hash或direct。

 

你可以通过跟踪日志来观察选择了哪种算法。ClickHouse还允许用户自己指定所需的连接算法。下面这张图表给出了基于它们相对内存消耗和执行时间的ClickHouse连接算法的概览：

直接连接是ClickHouse最快的连接算法，适用于右侧表的底层存储支持低延迟键值请求，以及当LEFT ANY JOIN语义是适当的情况。特别是对于大型的右侧表，直接连接在执行时间上显著优于所有其他ClickHouse连接算法。

 

ClickHouse有三种连接算法是基于内存哈希表：

1.哈希连接(hash join)速度快，但受内存限制，是支持所有连接类型和严格设置的最通用的连接算法。

此算法可能受到高内存使用的限制。此外，连接的右侧表创建内存哈希表是单线程的，如果右侧表非常大，可能会成为连接执行时间的瓶颈。

2.并行哈希连接(Parallel hash join)可以通过并行构建多个哈希表来加快大型右侧表的处理速度，但需要更多的内存。

3.Grace哈希连接(Grace hash join)是一种非内存绑定版本，它可以将数据临时溢出到磁盘，无需对数据进行排序。

这克服了其他非内存绑定的ClickHouse连接算法的一些性能挑战，这些算法将数据临时溢出到磁盘，但需要先对数据进行排序。

 

 

ClickHouse提供了两种基于外部排序的非内存绑定连接算法：

1.完全排序合并连接(Full sorting merge join)基于内存或外部排序，可以利用连接表的物理行顺序并跳过排序阶段。在这种情况下，连接性能可以与上图中的一些哈希连接算法竞争，同时通常需要显著较少的内存。

2.部分合并连接(Partial merge join)针对连接大表时最小化内存使用进行了优化，总是先通过外部排序完全排序右表。左表也总是在内存中按块排序。如果左表的物理行顺序与连接键排序顺序匹配，连接匹配过程将更有效。

 

选择正确的连接算法

如何选择连接算法主要取决于三个要素：

1.性能

2.内存

3.支持的连接类型

 

性能

除了上面的概览图，当主要标准是尽可能快地执行连接时，还可以使用此决策树来选择正确的连接算法：

①如果右侧表中的数据是可以预先加载到内存中的低延迟键值数据结构（如字典），并且如果连接键与底层键值存储的键属性相匹配，并且如果LEFT ANY JOIN语义充分--那么直接连接是适用的，也是最快的方法。

②如果表的物理行顺序与连接键的排序顺序一致，那么就要看情况了。在这种情况下，完全排序合并连接会跳过排序阶段，从而大大减少内存使用量，而且根据数据大小和连接键值分布，执行时间比某些哈希连接算法更快。

③不过，如果右侧的表能放入内存，即使并行哈希连接会带来额外的内存使用开销，那么这种算法或哈希连接也会更快。这取决于数据大小、数据类型和连接键列的值分布。

④如果右侧的表无法放入内存，那就再看情况。ClickHouse提供三种不占用内存的连接算法。

这三种算法都会将数据暂时溢出到磁盘。完全排序合并连接和部分合并连接需要事先对数据进行排序。Grace hash join则是通过数据建立哈希表。根据数据量、数据类型和连接键列的值分布，在某些情况下，从数据建立哈希表可能比排序数据更快。反之亦然。

 

部分合并连接经过优化，可以在连接大型表时最大限度地减少内存使用量，但连接速度却相当慢。尤其是当左表的物理行顺序与连接键排序顺序不一致时，这种情况尤其明显。

 

Grace hash join是三种非内存绑定连接算法中最灵活的一种，通过grace_hash_join_itial_buckets设置，可以很好地控制内存使用量和连接速度。根据数据量的不同，当选择的数据桶数量使两种算法的内存使用量大致相等时，Grace hash join可能比部分合并算法快，也可能比部分合并算法慢。在我们的测试运行中，如果将Grace hash join的内存使用量配置为与full sorting merge的内存使用量大致相等，那么full sorting merge的速度总是更快。

 

三种非内存绑定算法中哪一种最快，取决于数据量、数据类型和连接键列的值分布。为了确定哪种算法最快，最好使用真实数据量运行一些基准测试。

 

内存

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

①如果表的物理行顺序与连接键排序顺序一致，那么full sorting merge join的内存使用量就会非常低。由于排序阶段被禁用，因此还能带来更快的连接速度。

②grace hash join可以通过配置较多的数据桶来实现极低的内存使用率，但会牺牲连接速度。partial merge join内部使用较少的内存。启用外部排序的full sorting merge join通常比partial merge join使用更多内存（假设行顺序与键排序顺序不匹配），但好处是连接执行时间大大缩短。

 

支持的连接类型

在选择合适的连接算法时，不仅要考虑执行速度和内存消耗。它还取决于连接算法是否支持你所需的连接类型。为此，我们制作了这张概览图：

 

比较

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

 

对于所有连接查询，我们使用默认的max_threads设置。执行查询的ClickHouse云节点有30个CPU内核（和120GB内存），因此默认的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 = ...;

所用的数据集可以从这下载：https://gist.github.com/tom-clickhouse/5d391b45a1c19948ed6d43c87cf7e788

imdb_large表：

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_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         │
└────────┴────────────────┴───────────────────┘

 

直接连接(Direct join)有点特殊

请注意，我们对直接连接算法使用了单独的图表，因为只有将这种算法与类似的、内存绑定的非排序算法（如哈希和并行哈希）进行比较才有意义：

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内核的节点，最大线程默认设置为30

 

请注意，这10个查询运行按执行时间排序，从图表左侧最快的查询运行开始：

对于我们的示例表，最快的连接算法（见①和②）使用的内存量最大。但③full sorting merge是个明显的例外。如果因为表在磁盘上的物理行顺序与连接键排序顺序一致而可以跳过排序阶段，那么full sorting merge的执行时间就能与哈希连接算法相媲美（甚至更好，见下图），而所需内存却大大减少。在这种情况下，由于两个表中的数据都是按顺序分块流经查询引擎的，因此在合并时内存中同时只有几个数据块。

 

在对两个连接表进行④内存排序的情况下，full sorting merge join的内存消耗更高。如果采用⑤外部排序（将排序数据溢出到磁盘而不是内存排序），内存消耗会减少，但执行速度会降低。

Grace hash join是三种非内存绑定连接算法之一，它能将数据暂时溢出到磁盘，以减少内存消耗。与其他两种算法--full sorting merge和partial merge不同的是，grace hash允许根据配置的桶数量控制内存使用量。我们有兴趣比较Grace hash在内存使用量与其他两种非内存绑定算法大致相同或更少时的连接速度。为此，我们使用三种不同的数据桶数量运行grace hash。在运行 ⑥（4 个桶）时，我们将grace hash的内存使用量与full sorting merge运行⑤保持一致。在这种情况下，在连接两个示例表时，grace hash比 full sorting merge慢。在运行⑦8个数据桶时，我们将内存使用量大致与partial merge运行⑨的内存使用量保持一致。在这里，grace hash运行速度快了两倍。使用32个数据桶的运行⑧降低了内存使用率，低于partial merge运行，但运行速度仍然更快。

对于连接我们的示例表，partial merge运行（见⑨和⑩）的速度最慢。

请记住，partial merge连接会先对右表进行完全排序，并将其排序块和最小-最大索引文件暂时存储到磁盘。然后，它将左表的每个数据块与右表在磁盘上的排序数据块进行排序和比较，同时利用最小-最大索引跳过不匹配的数据块。对于我们的示例表，这种方法内存效率高，但速度慢。尤其是在运行⑩时，左表的物理行顺序与连接键排序顺序不匹配。

一般来说，我们可以看到，在识别连接匹配之前对表进行显式排序，在执行时间上要比只从其中一个表建立哈希表花费更多。

不过，请记住，我们只对一个特定的imdb数据集进行了连接基准测试。根据数据量、数据类型和连接键列的值分布，可能会出现数据块排序比建立哈希表更快的情况。示例请参见下图。imdb_xlarge的连接。

 

imdb_xlarge表的连接

下图总结了使用imdb_xlarge表进行同一连接查询示例的峰值内存使用量和执行时间：

与上一张图表一样，parallel hash join运行①最快，但内存消耗也最大。

与上一个图表不同的是，在连接imdb_xlarge数据库中两个较大的表时，full sorting merge运行②、③和④的速度比哈希连接运行⑤快，但使用的内存峰值却较少。如前所述，哈希连接的右侧表创建内存哈希表是单线程的，如果右侧表非常大，显然会成为瓶颈。

当在⑥中运行8个数据桶的grace hash的内存使用量与在④中运行外部排序的full sorting merge的内存使用量近似一致时，与上一张图表一样，在连接两个示例表时，grace hash比full sorting merge要慢。在运行⑧（64个数据桶）时，内存使用量与partial merge运行（见⑦和⑨）的内存使用量大致相当，因此与上一张图表相反，这次partial merge连接比grace has连接更快。对于我们这个有10亿行的右表示例，建立和散布排序块以及基于最小-最大索引的扫描（partial_merge）比建立和散布以及扫描64个哈希表（64个桶的grace_hash）更快。partial merge还得益于左表是按连接键排序的，这使得基于最小索引的高效扫描成为可能。

然而，当左表的物理行顺序与运行⑨中的连接键排序顺序不一致时，右表中已排序数据块的最小最大索引就起不了多大作用了，在最坏的情况下，实际上是在两个表的数据块之间产生了交叉积：左表中的每个数据块都要从磁盘中加载右表中的一大组已排序数据块。显然，这将导致非常高的执行时间，尤其是对于非常大的表。

 

 

Direct Join下的imdb_large表测试

下图总结了imdb_large在相同的LEFT ANY JOIN查询示例的峰值内存使用量和执行时间，其中

·较大的表位于连接的右侧

·不同的连接算法设置

·在一个有30个CPU内核的节点上，最大线程数默认设置为30

 

4个查询按执行时间排序，图表左侧从运行速度最快的查询开始：

direct join的速度是最快的。①在右侧表由具有平面内存布局的字典(a dictionary with a flat memory layout)支持的情况下，该算法比④哈希连接快25倍，比③并行哈希快15倍，比②右侧表由具有哈希内存布局的字典支持的直接连接快2.5倍。无论字典布局类型如何，总体峰值内存消耗都低于哈希算法运行。

 

 

 

Direct Join下的imdb_xlarge表测试

下图总结了imdb_large在相同的LEFT ANY JOIN查询示例的峰值内存使用量和执行时间，其中

①右侧表由具有平面内存布局的字典支持的direct join算法在~1 s内连接左侧表中的1亿行！这比④哈希算法快~32倍，比③并行哈希算法快~22倍，比右侧表由哈希内存布局字典支持的②direct join算法快4倍。如上图所示，direct join运行的总体峰值内存消耗低于哈希算法运行。

 

至此，我们完成了对ClickHouse连接算法的探索。