IN 运算符

IN、NOT IN、GLOBAL IN 和 GLOBAL 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 位于运算符的右侧还是左侧。如果 transform_null_in = 0，则 NULL 值不包含在任何数据集中，彼此不对应，也不能进行比较。

这是一个使用 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 / JOIN 和 GLOBAL IN / GLOBAL JOIN。它们在分布式查询处理中的运行方式不同。

注意

请记住，下面描述的算法可能根据设置 distributed_product_mode 设置的不同而有所不同。

使用普通 IN 时，查询将发送到远程服务器，并且每个服务器都会在 IN 或 JOIN 子句中运行子查询。

使用 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 更为优化。但是，请记住以下几点

创建临时表时，数据不会变得唯一。为了减少通过网络传输的数据量，请在子查询中指定 DISTINCT。（对于普通 IN，您不需要这样做。）
临时表将发送到所有远程服务器。传输不考虑网络拓扑。例如，如果 10 台远程服务器驻留在相对于请求服务器而言非常遥远的某个数据中心中，则数据将通过通道传输 10 次到远程数据中心。在使用 GLOBAL IN 时，尽量避免使用大型数据集。
将数据传输到远程服务器时，网络带宽限制不可配置。您可能会使网络过载。
尝试将数据分布到服务器上，以便您无需定期使用 GLOBAL IN。
如果您需要经常使用 GLOBAL IN，请规划 ClickHouse 集群的位置，以便一组副本最多驻留在一个数据中心中，并且它们之间具有快速的网络，以便查询可以在单个数据中心内完全处理。

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

分布式子查询和 max_rows_in_set

您可以使用 max_rows_in_set 和 max_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介于1和3之间，具体取决于本地查询在哪个副本上执行。

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

因此，添加max_parallel_replicas设置只有在两个表都具有相同的复制方案并且都根据UserID或其子键进行采样时才能产生正确的结果。特别是，如果local_table_2没有采样键，则会产生错误的结果。同样的规则也适用于JOIN。

如果local_table_2不满足要求，一种解决方法是使用GLOBAL IN或GLOBAL JOIN。

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

IN 运算符

NULL 处理​

分布式子查询​

分布式子查询和 max_rows_in_set​

分布式子查询和 max_parallel_replicas​

NULL 处理

分布式子查询

分布式子查询和 max_rows_in_set

分布式子查询和 max_parallel_replicas