ClickHouse 支持的 Join 类型

此博客文章是系列文章的一部分

• ClickHouse Join 内幕 - 哈希 Join、并行哈希 Join、Grace 哈希 Join
• ClickHouse Join 内幕 - 完全排序合并 Join、部分合并 Join
• ClickHouse Join 内幕 - 直接 Join
• 选择正确的 Join 算法

ClickHouse 是一个开源的面向列的 DBMS，构建并优化用于需要对大量数据进行超低延迟分析查询的用例。为了为分析应用程序实现最佳性能，通常会将表组合在一个称为数据反规范化的过程中。扁平化表通过避免 Join 来帮助最大限度地减少查询延迟，但代价是 ETL 复杂性的增加，这通常是可以接受的，以换取亚秒级查询。

然而，我们认识到，对于某些工作负载，例如来自更传统的数据仓库的工作负载，数据反规范化并不总是实用的，有时分析查询的部分源数据需要保持规范化。这些规范化表占用更少的存储空间，并为数据组合提供灵活性，但它们需要在查询时进行 Join 以进行某些类型的分析。

幸运的是，与一些误解相反，ClickHouse 完全支持 Join！除了支持所有标准 SQL JOIN 类型外，ClickHouse 还提供额外的 JOIN 类型，这些类型对于分析工作负载和时间序列分析非常有用。ClickHouse 允许您在 6 种不同的算法（我们将在本博客系列的下一部分中详细探讨）之间进行选择以执行 Join，或者允许查询规划器根据资源可用性和使用情况自适应地选择和动态更改运行时算法。

即使对于 ClickHouse 中对大型表的 Join，您也可以获得良好的性能，但这种用例目前特别要求用户仔细选择和调整 Join 算法以适应其查询工作负载。虽然我们预计随着时间的推移，这种情况也会变得更加自动化和启发式驱动，但本博客系列提供了对 ClickHouse 中 Join 执行内部机制的深入了解，因此您可以为应用程序使用的常见查询优化 Join。

在本文中，我们将使用规范化的关系数据库示例模式，以演示 ClickHouse 中可用的不同 Join 类型。在接下来的文章中，我们将深入了解 ClickHouse 中可用的 6 种不同 Join 算法的内部机制。我们将探讨 ClickHouse 如何将这些 Join 算法集成到其 query pipeline 中，以便尽可能快地执行 Join 类型。未来的一部分将介绍分布式 Join。

我们使用 Venn 图和示例查询，在源自关系数据集存储库的规范化 IMDB 数据集上，解释 ClickHouse 中可用的 Join 类型。

有关创建和加载表的说明，请点击此处。该数据集也可在我们的 playground 中找到，供想要重现查询的用户使用。

我们将使用示例数据集中的 4 个表

这 4 个表中的数据代表电影。一部电影可以有一个或多个类型。电影中的角色由演员扮演。上图中的箭头表示外键到主键的关系。例如，genres 表中一行的 movie_id 列包含 movies 表中一行的 id 值。

电影和演员之间存在多对多关系。这种多对多关系通过使用 roles 表规范化为两个一对多关系。roles 表中的每一行都包含 movies 表和 actors 表的 id 列的值。

• INNER JOIN （内连接）
• OUTER JOIN （外连接）
• CROSS JOIN （交叉连接）
• SEMI JOIN （半连接）
• ANTI JOIN （反连接）
• ANY JOIN （任意连接）
• ASOF JOIN （ASOF 连接）

对于每对在 Join 键上匹配的行，INNER JOIN 返回左表的行的列值，并与右表的行的列值组合在一起。如果一行有多个匹配项，则返回所有匹配项（这意味着为具有匹配 Join 键的行生成笛卡尔积）。

此查询通过将 movies 表与 genres 表连接来查找每部电影的类型

SELECT
    m.name AS name,
    g.genre AS genre
FROM movies AS m
INNER JOIN genres AS g ON m.id = g.movie_id
ORDER BY
    m.year DESC,
    m.name ASC,
    g.genre ASC
LIMIT 10;

┌─name───────────────────────────────────┬─genre─────┐
│ Harry Potter and the Half-Blood Prince │ Action    │
│ Harry Potter and the Half-Blood Prince │ Adventure │
│ Harry Potter and the Half-Blood Prince │ Family    │
│ Harry Potter and the Half-Blood Prince │ Fantasy   │
│ Harry Potter and the Half-Blood Prince │ Thriller  │
│ DragonBall Z                           │ Action    │
│ DragonBall Z                           │ Adventure │
│ DragonBall Z                           │ Comedy    │
│ DragonBall Z                           │ Fantasy   │
│ DragonBall Z                           │ Sci-Fi    │
└────────────────────────────────────────┴───────────┘

10 rows in set. Elapsed: 0.126 sec. Processed 783.39 thousand rows, 21.50 MB (6.24 million rows/s., 171.26 MB/s.)

✎

请注意，可以省略 INNER 关键字。

可以通过使用以下其他 Join 类型之一来扩展或更改 INNER JOIN 的行为。

LEFT OUTER JOIN 的行为类似于 INNER JOIN；此外，对于不匹配的左表行，ClickHouse 会为右表的列返回默认值。

RIGHT OUTER JOIN 查询类似，也会返回来自右表中不匹配行的值，以及左表列的默认值。

FULL OUTER JOIN 查询结合了 LEFT 和 RIGHT OUTER JOIN，并返回来自左表和右表中不匹配行的值，以及右表和左表列的默认值。

请注意，可以将 ClickHouse 配置为返回 NULL 而不是默认值（但是，出于性能原因，不太推荐这样做）。

此查询通过查询 movies 表中在 genres 表中没有匹配项的所有行，从而（在查询时）获得 movie_id 列的默认值 0，来查找所有没有类型的电影

SELECT m.name
FROM movies AS m
LEFT JOIN genres AS g ON m.id = g.movie_id
WHERE g.movie_id = 0
ORDER BY
    m.year DESC,
    m.name ASC
LIMIT 10;


┌─name──────────────────────────────────────┐
│ """Pacific War, The"""                    │
│ """Turin 2006: XX Olympic Winter Games""" │
│ Arthur, the Movie                         │
│ Bridge to Terabithia                      │
│ Mars in Aries                             │
│ Master of Space and Time                  │
│ Ninth Life of Louis Drax, The             │
│ Paradox                                   │
│ Ratatouille                               │
│ """American Dad"""                        │
└───────────────────────────────────────────┘

10 rows in set. Elapsed: 0.092 sec. Processed 783.39 thousand rows, 15.42 MB (8.49 million rows/s., 167.10 MB/s.)

✎

请注意，可以省略 OUTER 关键字。

CROSS JOIN 生成两个表的完整笛卡尔积，而不考虑 Join 键。左表中的每一行都与右表中的每一行组合。

因此，以下查询将 movies 表中的每一行与 genres 表中的每一行组合

SELECT
    m.name,
    m.id,
    g.movie_id,
    g.genre
FROM movies AS m
CROSS JOIN genres AS g
LIMIT 10;

┌─name─┬─id─┬─movie_id─┬─genre───────┐
│ #28  │  0 │        1 │ Documentary │
│ #28  │  0 │        1 │ Short       │
│ #28  │  0 │        2 │ Comedy      │
│ #28  │  0 │        2 │ Crime       │
│ #28  │  0 │        5 │ Western     │
│ #28  │  0 │        6 │ Comedy      │
│ #28  │  0 │        6 │ Family      │
│ #28  │  0 │        8 │ Animation   │
│ #28  │  0 │        8 │ Comedy      │
│ #28  │  0 │        8 │ Short       │
└──────┴────┴──────────┴─────────────┘

10 rows in set. Elapsed: 0.024 sec. Processed 477.04 thousand rows, 10.22 MB (20.13 million rows/s., 431.36 MB/s.)

✎

虽然之前的示例查询本身没有太多意义，但可以使用 WHERE 子句对其进行扩展，以关联匹配的行，从而复制 INNER join 行为来查找每部电影的类型

SELECT
    m.name AS name,
    g.genre AS genre
FROM movies AS m
CROSS JOIN genres AS g
WHERE m.id = g.movie_id
ORDER BY
    m.year DESC,
    m.name ASC,
    g.genre ASC
LIMIT 10;

┌─name───────────────────────────────────┬─genre─────┐
│ Harry Potter and the Half-Blood Prince │ Action    │
│ Harry Potter and the Half-Blood Prince │ Adventure │
│ Harry Potter and the Half-Blood Prince │ Family    │
│ Harry Potter and the Half-Blood Prince │ Fantasy   │
│ Harry Potter and the Half-Blood Prince │ Thriller  │
│ DragonBall Z                           │ Action    │
│ DragonBall Z                           │ Adventure │
│ DragonBall Z                           │ Comedy    │
│ DragonBall Z                           │ Fantasy   │
│ DragonBall Z                           │ Sci-Fi    │
└────────────────────────────────────────┴───────────┘

10 rows in set. Elapsed: 0.150 sec. Processed 783.39 thousand rows, 21.50 MB (5.23 million rows/s., 143.55 MB/s.)

✎

CROSS JOIN 的另一种语法是在 FROM 子句中使用逗号分隔的多个表。

如果查询的 WHERE 部分存在 Join 表达式，ClickHouse 会将 CROSS JOIN 重写为 INNER JOIN。

我们可以通过 EXPLAIN SYNTAX（它返回查询在执行之前被重写成的语法优化版本）来检查示例查询

EXPLAIN SYNTAX
SELECT
    m.name AS name,
    g.genre AS genre
FROM movies AS m
CROSS JOIN genres AS g
WHERE m.id = g.movie_id
ORDER BY
    m.year DESC,
    m.name ASC,
    g.genre ASC
LIMIT 10;

┌─explain─────────────────────────────────────┐
│ SELECT                                      │
│     name AS name,                           │
│     genre AS genre                          │
│ FROM movies AS m                            │
│ ALL INNER JOIN genres AS g ON id = movie_id │
│ WHERE id = movie_id                         │
│ ORDER BY                                    │
│     year DESC,                              │
│     name ASC,                               │
│     genre ASC                               │
│ LIMIT 10                                    │
└─────────────────────────────────────────────┘

11 rows in set. Elapsed: 0.077 sec.

✎

语法优化的 CROSS JOIN 查询版本中的 INNER JOIN 子句包含 ALL 关键字，该关键字被显式添加，以便即使在被重写为 INNER JOIN 时也保持 CROSS JOIN 的笛卡尔积语义，对于 INNER JOIN，笛卡尔积可以被禁用。

并且因为，如上所述，对于 RIGHT OUTER JOIN 可以省略 OUTER 关键字，并且可以添加可选的 ALL 关键字，所以您可以编写 ALL RIGHT JOIN，它也能正常工作。

LEFT SEMI JOIN 查询为左表中在右表中至少有一个 Join 键匹配的每一行返回列值。仅返回第一个找到的匹配项（笛卡尔积被禁用）。

RIGHT SEMI JOIN 查询类似，为右表中在左表中至少有一个匹配项的所有行返回值，但仅返回第一个找到的匹配项。

此查询查找 2023 年在电影中表演的所有男演员/女演员。请注意，如果使用正常的 (INNER) join，如果同一男演员/女演员在 2023 年扮演了多个角色，则会出现多次

SELECT
    a.first_name,
    a.last_name
FROM actors AS a
LEFT SEMI JOIN roles AS r ON a.id = r.actor_id
WHERE toYear(created_at) = '2023'
ORDER BY id ASC
LIMIT 10;

┌─first_name─┬─last_name──────────────┐
│ Michael    │ 'babeepower' Viera     │
│ Eloy       │ 'Chincheta'            │
│ Dieguito   │ 'El Cigala'            │
│ Antonio    │ 'El de Chipiona'       │
│ José       │ 'El Francés'           │
│ Félix      │ 'El Gato'              │
│ Marcial    │ 'El Jalisco'           │
│ José       │ 'El Morito'            │
│ Francisco  │ 'El Niño de la Manola' │
│ Víctor     │ 'El Payaso'            │
└────────────┴────────────────────────┘

10 rows in set. Elapsed: 0.151 sec. Processed 4.25 million rows, 56.23 MB (28.07 million rows/s., 371.48 MB/s.)

✎

LEFT ANTI JOIN 为左表中所有不匹配的行返回列值。

类似地，RIGHT ANTI JOIN 为右表中所有不匹配的行返回列值。

我们之前的外部 Join 示例查询的另一种形式是使用反连接来查找数据集中没有类型的电影

SELECT m.name
FROM movies AS m
LEFT ANTI JOIN genres AS g ON m.id = g.movie_id
ORDER BY
    year DESC,
    name ASC
LIMIT 10;

┌─name──────────────────────────────────────┐
│ """Pacific War, The"""                    │
│ """Turin 2006: XX Olympic Winter Games""" │
│ Arthur, the Movie                         │
│ Bridge to Terabithia                      │
│ Mars in Aries                             │
│ Master of Space and Time                  │
│ Ninth Life of Louis Drax, The             │
│ Paradox                                   │
│ Ratatouille                               │
│ """American Dad"""                        │
└───────────────────────────────────────────┘

10 rows in set. Elapsed: 0.077 sec. Processed 783.39 thousand rows, 15.42 MB (10.18 million rows/s., 200.47 MB/s.)

✎

LEFT ANY JOIN 是 LEFT OUTER JOIN + LEFT SEMI JOIN 的组合，这意味着 ClickHouse 为左表中的每一行返回列值，要么与右表中匹配行的列值组合，要么在不存在匹配项的情况下与右表的默认列值组合。如果左表中的一行在右表中有多个匹配项，ClickHouse 只返回第一个找到的匹配项的组合列值（笛卡尔积被禁用）。

类似地，RIGHT ANY JOIN 是 RIGHT OUTER JOIN + RIGHT SEMI JOIN 的组合。

而 INNER ANY JOIN 是禁用了笛卡尔积的 INNER JOIN。

我们使用一个抽象示例演示 LEFT ANY JOIN，该示例使用 values 表函数构造的两个临时表（left_table 和 right_table）

WITH
    left_table AS (SELECT * FROM VALUES('c UInt32', 1, 2, 3)),
    right_table AS (SELECT * FROM VALUES('c UInt32', 2, 2, 3, 3, 4))
SELECT
    l.c AS l_c,
    r.c AS r_c
FROM left_table AS l
LEFT ANY JOIN right_table AS r ON l.c = r.c;

┌─l_c─┬─r_c─┐
│   1 │   0 │
│   2 │   2 │
│   3 │   3 │
└─────┴─────┘

3 rows in set. Elapsed: 0.002 sec.

✎

这是使用 RIGHT ANY JOIN 的相同查询

WITH
    left_table AS (SELECT * FROM VALUES('c UInt32', 1, 2, 3)),
    right_table AS (SELECT * FROM VALUES('c UInt32', 2, 2, 3, 3, 4))
SELECT
    l.c AS l_c,
    r.c AS r_c
FROM left_table AS l
RIGHT ANY JOIN right_table AS r ON l.c = r.c;

┌─l_c─┬─r_c─┐
│   2 │   2 │
│   2 │   2 │
│   3 │   3 │
│   3 │   3 │
│   0 │   4 │
└─────┴─────┘

5 rows in set. Elapsed: 0.002 sec.

✎

这是使用 INNER ANY JOIN 的查询

WITH
    left_table AS (SELECT * FROM VALUES('c UInt32', 1, 2, 3)),
    right_table AS (SELECT * FROM VALUES('c UInt32', 2, 2, 3, 3, 4))
SELECT
    l.c AS l_c,
    r.c AS r_c
FROM left_table AS l
INNER ANY JOIN right_table AS r ON l.c = r.c;

┌─l_c─┬─r_c─┐
│   2 │   2 │
│   3 │   3 │
└─────┴─────┘

2 rows in set. Elapsed: 0.002 sec.

✎

ASOF JOIN 由 Martijn Bakker 和 Artem Zuikov 于 2019 年在 ClickHouse 中实现，提供非精确匹配功能。如果左表中的一行在右表中没有完全匹配项，则使用右表中最近的匹配行作为替代匹配项。

这对于时间序列分析特别有用，并且可以大大降低查询复杂性。

我们将以股票市场数据的时间序列分析为例。quotes 表包含基于一天中特定时间的股票代码报价。在我们的示例数据中，价格每 10 秒更新一次。trades 表列出了股票代码交易 - 特定数量的股票代码在特定时间被买入

为了计算每笔交易的具体成本，我们需要将交易与其最接近的报价时间匹配。

使用 ASOF JOIN 可以轻松简洁地实现这一点，我们在 ON 子句中指定精确匹配条件，在 AND 子句中指定最接近的匹配条件 - 对于特定股票代码（精确匹配），我们正在查找 quotes 表中时间 '最接近' 且不晚于该股票代码交易时间（非精确匹配）的行

SELECT
    t.symbol,
    t.volume,
    t.time AS trade_time,
    q.time AS closest_quote_time,
    q.price AS quote_price,
    t.volume * q.price AS final_price
FROM trades t
ASOF LEFT JOIN quotes q ON t.symbol = q.symbol AND t.time >= q.time
FORMAT Vertical;

Row 1:
──────
symbol:             ABC
volume:             200
trade_time:         2023-02-22 14:09:05
closest_quote_time: 2023-02-22 14:09:00
quote_price:        32.11
final_price:        6422

Row 2:
──────
symbol:             ABC
volume:             300
trade_time:         2023-02-22 14:09:28
closest_quote_time: 2023-02-22 14:09:20
quote_price:        32.15
final_price:        9645

2 rows in set. Elapsed: 0.003 sec.

请注意，ASOF JOIN 的 ON 子句是必需的，并且在 AND 子句的非精确匹配条件旁边指定了一个精确匹配条件。

ClickHouse 目前尚不支持（尚未）Join，其中 Join 键的任何部分都不执行严格匹配。

这篇博客文章展示了 ClickHouse 如何支持所有标准 SQL JOIN 类型，以及专门的 Join 来支持分析查询。我们描述并演示了所有支持的 JOIN 类型。

在本系列的下一部分中，我们将探讨 ClickHouse 如何将其经典 Join 算法适配到查询管道中，以尽可能快地执行本文中描述的 Join 类型。

敬请期待！