跳到主要内容
跳到主要内容

IN 操作符

INNOT INGLOBAL INGLOBAL NOT IN 操作符分别讨论,因为它们的功能相当丰富。

操作符的左侧可以是单个列或元组。

示例:

SELECT UserID IN (123, 456) FROM ...
SELECT (CounterID, UserID) IN ((34, 123), (101500, 456)) FROM ...

如果左侧是一个在索引中的单列,而右侧是一组常量,则系统会利用索引来处理查询。

不要显式列出过多的值(即数百万个)。如果数据集很大,请将其放入临时表中(例如,请参见查询处理的外部数据一节),然后使用子查询。

操作符的右侧可以是一组常量表达式、一组带有常量表达式的元组(如上面示例所示),或者是用括号括起来的数据库表名或SELECT子查询。

ClickHouse 允许 IN 子查询的左侧和右侧的类型不同。 在这种情况下,它将右侧的值转换为左侧的类型,就好像对右侧应用了accurateCastOrNull函数一样。

这意味着数据类型变为Nullable,如果无法进行转换,则返回NULL

示例

查询:

SELECT '1' IN (SELECT 1);

结果:

┌─in('1', _subquery49)─┐
│                    1 │
└──────────────────────┘

如果操作符的右侧是表的名称(例如,UserID IN users),这就等同于子查询UserID IN (SELECT * FROM users)。在处理与查询一起发送的外部数据时使用此方法。例如,查询可以与加载到“users”临时表中的用户 ID 集合一起发送,这些用户 ID 应该被过滤。

如果操作符的右侧是具有 Set 引擎的表名(一个始终在 RAM 中的数据集),该数据集将不会为每个查询重复创建。

子查询可以指定多个列以过滤元组。

示例:

SELECT (CounterID, UserID) IN (SELECT CounterID, UserID FROM ...) FROM ...

IN 操作符左侧和右侧的列应具有相同的类型。

IN 操作符和子查询可以出现在查询的任何部分,包括聚合函数和 lambda 函数。 示例:

SELECT
    EventDate,
    avg(UserID IN
    (
        SELECT UserID
        FROM test.hits
        WHERE EventDate = toDate('2014-03-17')
    )) AS ratio
FROM test.hits
GROUP BY EventDate
ORDER BY EventDate ASC
┌──EventDate─┬────ratio─┐
│ 2014-03-17 │        1 │
│ 2014-03-18 │ 0.807696 │
│ 2014-03-19 │ 0.755406 │
│ 2014-03-20 │ 0.723218 │
│ 2014-03-21 │ 0.697021 │
│ 2014-03-22 │ 0.647851 │
│ 2014-03-23 │ 0.648416 │
└────────────┴──────────┘

对于 3 月 17 日之后的每一天,计算在 3 月 17 日访问了该网站的用户产生的页面浏览量的百分比。 IN 子句中的子查询在单个服务器上始终只运行一次。没有依赖子查询。

NULL 处理

在请求处理过程中,IN 操作符假定与NULL的操作结果始终等于 0,无论 NULL 在操作符的右边还是左边。NULL 值不包含在任何数据集中,不彼此对应,也无法在transform_null_in = 0时进行比较。

这是一个包含 t_null 表的示例:

┌─x─┬────y─┐
│ 1 │ ᴺᵁᴸᴸ │
│ 2 │    3 │
└───┴──────┘

运行查询 SELECT x FROM t_null WHERE y IN (NULL,3) 将返回以下结果:

┌─x─┐
│ 2 │
└───┘

可以看到,其中 y = NULL 的行被排除在查询结果之外。这是因为 ClickHouse 无法决定 NULL 是否包含在 (NULL,3) 集合中,返回操作的结果为 0,因此 SELECT 将该行从最终输出中排除。

SELECT y IN (NULL, 3)
FROM t_null
┌─in(y, tuple(NULL, 3))─┐
│                     0 │
│                     1 │
└───────────────────────┘

分布式子查询

IN 操作符与子查询有两种选项(类似于 JOIN 操作符):普通 IN / JOINGLOBAL IN / GLOBAL JOIN。它们在分布式查询处理中的运行方式不同。

备注

请记住,下面描述的算法可能会根据设置中的distributed_product_mode设置而异。

使用常规的 IN 时,查询会发送到远程服务器,每台服务器都会运行 INJOIN 子句中的子查询。

使用 GLOBAL IN / GLOBAL JOIN 时,首先对所有子查询进行 GLOBAL IN / GLOBAL JOIN,并将结果收集到临时表中。然后将临时表发送到每个远程服务器,在这些服务器上使用该临时数据运行查询。

对于非分布式查询,请使用常规的 IN / JOIN

在分布式查询处理时,使用 IN / JOIN 子句中的子查询时请小心。

让我们看一些示例。假设集群中的每台服务器都有一个普通的 local_table。每台服务器还有一个 distributed_table 表,其类型为 Distributed,可以查看集群中的所有服务器。

针对 distributed_table 的查询将发送到所有远程服务器,并在它们上使用 local_table

例如,查询

SELECT uniq(UserID) FROM distributed_table

将以如下格式发送到所有远程服务器

SELECT uniq(UserID) FROM local_table

并在每台服务器上并行运行,直到达到可以合并中间结果的阶段。然后将中间结果返回给请求者服务器并在其上合并,最终结果将发送给客户端。

现在让我们检查一个带有 IN 的查询:

SELECT uniq(UserID) FROM distributed_table WHERE CounterID = 101500 AND UserID IN (SELECT UserID FROM local_table WHERE CounterID = 34)
  • 计算两个网站受众的交集。

该查询将以如下格式发送到所有远程服务器

SELECT uniq(UserID) FROM local_table WHERE CounterID = 101500 AND UserID IN (SELECT UserID FROM local_table WHERE CounterID = 34)

换句话说,IN 子句中的数据集将在每台服务器上独立收集,仅针对每台服务器本地存储的数据。

如果您已经为此情况做好准备,并且在集群服务器上分布数据,使得单个 UserID 的数据完全位于一台服务器上,则这样工作会正确且最佳。在这种情况下,所有必要的数据都会在每台服务器的本地可用。否则,结果将不准确。我们将这种查询的变体称为“本地 IN”。

为了修正查询在数据随机分布于集群服务器上的工作方式,您可以在子查询中指定 distributed_table。查询将如下所示:

SELECT uniq(UserID) FROM distributed_table WHERE CounterID = 101500 AND UserID IN (SELECT UserID FROM distributed_table WHERE CounterID = 34)

该查询将以如下格式发送到所有远程服务器

SELECT uniq(UserID) FROM local_table WHERE CounterID = 101500 AND UserID IN (SELECT UserID FROM distributed_table WHERE CounterID = 34)

子查询将在每个远程服务器上开始运行。由于子查询使用分布式表,因此每个远程服务器上的子查询将重新发送到每台远程服务器,如下所示:

SELECT UserID FROM local_table WHERE CounterID = 34

例如,如果您有一个 100 台服务器的集群,执行整个查询将需要 10,000 个基本请求,这通常被认为是不可接受的。

在这种情况下,您应该始终使用 GLOBAL IN 而不是 IN。让我们看看查询的工作原理:

SELECT uniq(UserID) FROM distributed_table WHERE CounterID = 101500 AND UserID GLOBAL IN (SELECT UserID FROM distributed_table WHERE CounterID = 34)

请求服务器将运行子查询:

SELECT UserID FROM distributed_table WHERE CounterID = 34

结果将放入 RAM 中的临时表中。然后请求将以如下格式发送到每个远程服务器:

SELECT uniq(UserID) FROM local_table WHERE CounterID = 101500 AND UserID GLOBAL IN _data1

临时表 _data1 将与查询一起发送到每台远程服务器(临时表的名称是实现定义的)。

这比使用常规的 IN 更具优化。但是,请记住以下几点:

  1. 创建临时表时,数据不会变得唯一。为了减少通过网络传输的数据量,请在子查询中指定 DISTINCT。(对于普通的 IN,您不需要这样做。)
  2. 临时表将发送到所有远程服务器。传输不考虑网络拓扑。例如,如果 10 台远程服务器位于与请求服务器非常偏远的数据中心,则数据将在通道上向远程数据中心发送 10 次。在使用 GLOBAL IN 时,尝试避免大型数据集。
  3. 向远程服务器传输数据时,网络带宽的限制不可配置。您可能会导致网络过载。
  4. 尝试在服务器间分配数据,以便您不需要定期使用 GLOBAL IN
  5. 如果您需要频繁使用 GLOBAL IN,请规划 ClickHouse 集群的位置,以便一组副本位于不超过一个数据中心中,并且它们之间的网络快速,这样查询可以完全在单一数据中心内处理。

GLOBAL IN 子句中指定本地表也是有意义的,如果该本地表仅在请求服务器上可用,并且您希望在远程服务器上使用该数据。

分布式子查询与 max_rows_in_set

您可以使用 max_rows_in_setmax_bytes_in_set 来控制分布式查询期间传输的数据量。

这在 GLOBAL IN 查询返回大量数据时尤为重要。请考虑以下 SQL:

SELECT * FROM table1 WHERE col1 GLOBAL IN (SELECT col1 FROM table2 WHERE <some_predicate>)

如果 some_predicate 的选择性不够,它将返回大量数据并导致性能问题。在这种情况下,限制通过网络传输的数据量是明智的。此外,请注意,set_overflow_mode 默认为 throw,这意味着达到了这些阈值时会抛出异常。

分布式子查询与 max_parallel_replicas

max_parallel_replicas大于 1 时,分布式查询会进一步转换。

例如,以下内容:

SELECT CounterID, count() FROM distributed_table_1 WHERE UserID IN (SELECT UserID FROM local_table_2 WHERE CounterID < 100)
SETTINGS max_parallel_replicas=3

在每台服务器上转换为:

SELECT CounterID, count() FROM local_table_1 WHERE UserID IN (SELECT UserID FROM local_table_2 WHERE CounterID < 100)
SETTINGS parallel_replicas_count=3, parallel_replicas_offset=M

其中 M 介于 13 之间,具体取决于本地查询在哪个副本上执行。

这些设置影响查询中的每个 MergeTree 系列表,并与对每个表应用 SAMPLE 1/3 OFFSET (M-1)/3 的效果相同。

因此,仅在两个表具有相同的复制方案并按 UserID 或其子键进行采样时,添加 max_parallel_replicas 设置才会产生正确的结果。特别是如果 local_table_2 没有采样键,将会产生不正确的结果。相同的规则适用于 JOIN

如果 local_table_2 不满足要求,一种解决方法是使用 GLOBAL INGLOBAL JOIN

如果表没有采样键,可以使用更灵活的选项 parallel_replicas_custom_key 来产生不同且更优化的行为。