DoubleCloud 即将停止运营。立即迁移到 ClickHouse，享受限时免费迁移服务。立即联系我们 ->->

博客 / 工程

ClickHouse 支持的连接类型

Tom Schreiber

2023年3月2日

立即开始使用 ClickHouse Cloud 并获得 300 美元的信用额度。要了解有关我们基于用量的折扣的更多信息，请联系我们或访问我们的定价页面。

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

ClickHouse 是一种开源的列式数据库管理系统，专为需要对大量数据进行超低延迟分析查询的使用案例而构建和优化。为了实现分析应用程序的最佳性能，通常会将表组合到一个称为数据反规范化的过程中。扁平化的表有助于通过避免连接来最大程度地减少查询延迟，但代价是增量 ETL 复杂性，通常可以接受以换取亚秒级查询。

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

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

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

对于这篇文章，我们将使用一个规范化的关系数据库示例架构来演示 ClickHouse 中可用的不同连接类型。在接下来的文章中，我们将深入了解 ClickHouse 中可用的 6 种不同连接算法。我们将探讨 ClickHouse 如何将其连接算法集成到其查询管道中，以便尽可能快地执行连接类型。未来的一部分将涵盖分布式连接。

我们使用维恩图和示例查询，在一个规范化的IMDB数据集上（源自关系数据集存储库），来解释 ClickHouse 中可用的连接类型。

创建和加载表的说明在这里。该数据集也以我们的游乐场形式提供给希望复制查询的用户。

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

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

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

内部连接会针对每个在连接键上匹配的行对返回左侧表中行的列值，并结合右侧表中行的列值。如果一行有多个匹配项，则会返回所有匹配项（这意味着会针对具有匹配连接键的行生成笛卡尔积）。

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


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 关键字。

可以使用以下其他连接类型扩展或更改内部连接的行为。

左外部连接的行为类似于内部连接；此外，对于不匹配的左侧表行，ClickHouse 会针对右侧表的列返回默认值。

右外部连接查询类似，并且还会返回来自右侧表的不匹配行的值以及左侧表的列的默认值。

全外部连接查询组合了左外部连接和右外部连接，并分别返回来自左侧和右侧表的不匹配行的值以及右侧和左侧表的列的默认值。

请注意，可以配置 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 会生成两个表的完整笛卡尔积，而不考虑连接键。左表中的每一行都与右表中的每一行组合。

因此，以下查询将 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 部分中存在连接表达式，ClickHouse 会将 CROSS JOIN 重写为 INNER JOIN。[请参阅此处](https://github.com/ClickHouse/ClickHouse/blob/23.2/src/Core/Settings.h#L896)。

我们可以通过 [EXPLAIN SYNTAX](https://clickhouse.ac.cn/docs/en/sql-reference/statements/explain/#explain-syntax) 来检查示例查询（它会将查询在 [执行](https://youtu.be/hP6G2Nlz_cA)之前重写成的语法优化版本返回）。


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 关键字，该关键字是为了保持 CROSS JOIN 的笛卡尔积语义而显式添加的，即使在重写为 INNER JOIN 时也是如此，因为对于 INNER JOIN，笛卡尔积可以 [禁用](https://clickhouse.ac.cn/docs/en/operations/settings/settings#settings-join_default_strictness)。

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

LEFT SEMI 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 返回所有不匹配的右表行的列值。

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


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_table 和 right_table）使用 [values](https://github.com/ClickHouse/ClickHouse/blob/23.2/src/TableFunctions/TableFunctionValues.h) [表函数](https://clickhouse.ac.cn/docs/en/sql-reference/table-functions/) 演示 LEFT 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
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](https://github.com/ClickHouse/ClickHouse/pull/4774) 和 [Artem Zuikov](https://github.com/ClickHouse/ClickHouse/pull/6211) 于 2019 年为 ClickHouse 实现，提供了非精确匹配功能。如果左表中的一行在右表中没有完全匹配，则使用右表中最接近的匹配行作为匹配项。

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

我们将以 [股票市场数据](https://gist.github.com/tom-clickhouse/58eae026d0893444d9d02012f4adab7d) 作为示例进行时间序列分析。**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 目前（尚未）支持任何部分连接键都不执行严格匹配的连接。

这篇博文展示了 ClickHouse 如何支持所有标准 SQL JOIN 类型，以及用于增强分析查询的专用连接。我们描述并演示了所有支持的 JOIN 类型。

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

敬请期待！

立即 [开始使用](https://clickhouse.cloud/signUp?loc=blog-cta-footer&utm_source=clickhouse&utm_medium=web&utm_campaign=blog) ClickHouse Cloud 并获得 300 美元的积分。在您的 30 天试用期结束时，可以继续使用按需付费计划，或者 [联系我们](/company/contact?loc=blog-cta-footer) 以了解有关我们的批量折扣的更多信息。请访问我们的 [定价页面](/pricing?loc=blog-cta-header) 了解更多详细信息。

分享此文章