JOIN 查询

• 1: 哈希联接与合并联接
• 2: 相同分段
• 3: 联接可变长度字符串数据

1 - 哈希联接与合并联接

Vertica 优化器使用以下算法之一实施联接：

• 当联接表的投影已在联接列中排序时，使用合并联接。与哈希联接相比，合并联接较快并且使用较少的内存。

• 当联接表的投影尚未在联接列上排序时，使用哈希联接。在这种情况下，优化器会在内表的联接列上构建一个内存中的哈希表。优化器然后扫描外部表以查找与哈希表的匹配，并相应地联接两个表中的数据。如果整个哈希表可以拟合到内存中，则执行哈希联接的成本将非常低。如果哈希表必须写入磁盘，成本会显着增加。

在给定可用的投影的情况下，优化器会自动选择最合适的算法来执行查询。

促进合并联接

为了促进合并联接，请为联接表创建在联接谓词列上排序的投影。联接谓词列应该是 ORDER BY 子句中的第一列。

例如，表 first 和 second 定义如下，分别带有投影 first_p1 和 second_p1。投影在 data_first 和 data_second 上排序：

CREATE TABLE first ( id INT, data_first INT );
CREATE PROJECTION first_p1 AS SELECT * FROM first ORDER BY data_first;

CREATE TABLE second ( id INT, data_second INT );
CREATE PROJECTION second_p1 AS SELECT * FROM second ORDER BY data_second;

当您在未排序的列 first.id 和 second.id 上联接这些表时，Vertica 使用哈希联接算法：

 EXPLAIN SELECT first.data_first, second.data_second FROM first JOIN second ON first.id = second.id;

 Access Path:
 +-JOIN HASH [Cost: 752, Rows: 300K] (PATH ID: 1) Inner (BROADCAST)

您可以创建投影 first_p2 和 second_p2（它们分别在联接列 first_p2.id 和 second_p2.id 上排序），从而使用合并联接算法促进执行此查询：

CREATE PROJECTION first_p2 AS SELECT id, data_first FROM first ORDER BY id SEGMENTED BY hash(id, data_first) ALL NODES;
CREATE PROJECTION second_p2 AS SELECT id, data_second FROM second ORDER BY id SEGMENTED BY hash(id, data_second) ALL NODES;

如果查询联接了大量数据，则查询优化器使用合并算法：

EXPLAIN SELECT first.data_first, second.data_second FROM first JOIN second ON first.id = second.id;

 Access Path:
 +-JOIN MERGEJOIN(inputs presorted) [Cost: 731, Rows: 300K] (PATH ID: 1) Inner (BROADCAST)

您还可以使用子查询对联接谓词列进行预排序，从而促进合并联接。例如：

SELECT first.id, first.data_first, second.data_second FROM
  (SELECT * FROM first ORDER BY id ) first JOIN (SELECT * FROM second ORDER BY id) second ON first.id = second.id;

2 - 相同分段

要在联接多个表时提升查询性能，请创建在联接键上进行相同分段的投影。使用相同分段的投影时，联接可以在每个节点本地进行，因此有助于在查询处理期间减少整个网络中的数据移动。

要确定投影是否在查询联接键中进行了相同分段，请创建具有 EXPLAIN 的查询计划。如果查询计划包括 RESEGMENT 或 BROADCAST，则表示投影没有进行相同分段。

Vertica 优化器会选择投影为查询中的每个表提供行。如果要联接的投影进行了分段，那么优化器则会根据查询联接表达式评估其分段。因此，它会确定行是否放置在每个节点上，以便于它联接行，而无需从其他节点获取数据。

相同分段的投影的联接条件

如果 p 的分段表达式的所有列引用是联接表达式中的列的子集，那么投影 p 会在联接列上进行分段。

如果表 t1 的分段投影 p1 和表 t2 的分段投影 p2 这两个分段投影要使 t1 联接到 t2，则必须满足以下条件：

• 联接条件必须采用以下形式：

  t1.j1 = t2.j1 AND t1.j2 = t2.j2 AND ... t1.jN = t2.jN
  

• 联接列必须共享相同的基本数据类型。例如：

  • 如果 t1.j1 是 INTEGER，则 t2.j1 可以是 INTEGER，但它不能是 FLOAT。

  • 如果 t1.j1 是 CHAR(10)，则 t2.j1 可以是任何 CHAR 或 VARCHAR（例如，CHAR(10)、VARCHAR(10)、VARCHAR(20)），但是 t2.j1 不能是 INTEGER。

• 如果 p1 按列 {t1.s1, t1.s2, ... t1.sN} 上的表达式分段，则每个分段列 t1.sX 必须位于联接列集 {t1.jX} 中。

• 如果 p2 按列 {t2.s1, t2.s2, ... t2.sN} 上的表达式分段，则每个分段列 t2.sX 必须位于联接列集 {t2.jX} 中。

• p1 和 p2 的分段表达式必须在结构上等效。例如：

  • 如果 p1 是 SEGMENTED BY hash(t1.x) 并且 p2 是 SEGMENTED BY hash(t2.x)，那么 p1 和 p2 进行了相同分段。

  • 如果 p1 是 SEGMENTED BY hash(t1.x) 并且 p2 是 SEGMENTED BY hash(t2.x + 1)，那么 p1 和 p2 没有进行相同分段。

• p1 和 p2 必须有相同的分段计数。

• 向节点分配的分段必须匹配。例如，如果 p1 和 p2 使用了 OFFSET 子句，它们的偏移必须匹配。

• 如果 Vertica 发现 t1 和 t2 的投影没有进行相同分段，必要时数据会在查询运行时在整个网络中重新分布。

  提示

  如果您创建了自定义设计，请尝试在条件允许时为联接使用分段投影。

示例

以下语句创建了两个表，并指定要创建相同分段：

=> CREATE TABLE t1 (id INT, x1 INT, y1 INT) SEGMENTED BY HASH(id, x1) ALL NODES;
=> CREATE TABLE t2 (id INT, x1 INT, y1 INT) SEGMENTED BY HASH(id, x1) ALL NODES;

以此设计为例，以下查询中的联接条件可以利用相同分段：

=> SELECT * FROM t1 JOIN t2 ON t1.id = t2.id;
=> SELECT * FROM t1 JOIN t2 ON t1.id = t2.id AND t1.x1 = t2.x1;

相反，以下查询中的联接条件需要重新分段：

=> SELECT * FROM t1 JOIN t2 ON t1.x1 = t2.x1;
=> SELECT * FROM t1 JOIN t2 ON t1.id = t2.x1;

另请参阅

• 分区和分段
• CREATE PROJECTION

3 - 联接可变长度字符串数据

当您在 VARCHAR 列上联接表时，Vertica 会计算缓冲联接列数据所需的存储空间。它通过采用以下两种方式之一设置列数据格式来完成此操作：

• 使用联接列元数据将列数据的大小调整为固定长度，并相应地缓冲。例如，给定一个定义为 VARCHAR(1000) 的列，Vertica 始终缓冲 1000 个字符。

• 使用联接列数据的实际长度，因此每个联接的缓冲区大小不同。例如，给定字符串 Xi、John 和 Amrita 的联接，Vertica 仅缓冲每个联接所需的存储空间 — 在本例中，分别为 2、4 和 6 个字节。

第二种方法可以提高联接查询性能。它还可以减少内存消耗，这有助于防止联接溢出并最大限度地减少从资源管理器借用内存的频率。通常，在联接列的定义大小显著超过其数据的平均长度的情况下，这些好处尤其显著。

设置和验证可变长度格式

您可以通过配置参数 JoinDefaultTupleFormat 控制 Vertica 如何在会话或数据库级别实施联接，或者通过 JFMT 提示控制针对单个查询实施联接。Vertica 支持除 merge 和 event series 联接之外的所有联接的可变长度格式。

使用 EXPLAIN VERBOSE 通过检查以下标志来验证给定查询是否使用可变字符格式：

• JF_EE_VARIABLE_FORMAT

• JF_EE_FIXED_FORMAT