IN

IN, NOT IN, GLOBAL IN, GLOBAL NOT IN 연산자는 기능이 상당히 풍부해서 별도로 다룹니다.

연산자의 왼쪽은 단일 컬럼이거나 튜플입니다.

Examples:

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

왼쪽이 인덱스에 있는 단일 컬럼이고 오른쪽이 상수 집합이면, 시스템은 쿼리 처리를 위해 인덱스를 사용합니다.

너무 많은 값을 나열하지 마세요(예: 수백만 개). 데이터 집합이 크면 임시 테이블에 넣고(예: 외부 데이터 쿼리 처리 섹션 참고) 서브쿼리를 사용하세요.

연산자의 오른쪽은 상수 표현식 집합, 상수 표현식이 있는 튜플 집합(위 예제에 표시됨), 또는 데이터베이스 테이블 이름이나 대괄호 안의 SELECT 서브쿼리가 될 수 있습니다.

역사적 호환을 위해 오른쪽이 단일 tuple 표현식이면, IN 연산자의 왼쪽에 따라 값 집합 또는 단일 튜플 값 중 하나로 해석될 수 있습니다. 왼쪽이 스칼라 값이면 ClickHouse는 이 단일 오른쪽 tuple 표현식의 요소를 별도의 IN 값으로 취급합니다:

SELECT
    1 IN (tuple(1, 2)) AS one_in_tuple,
    2 IN (tuple(1, 2)) AS two_in_tuple,
    3 IN (tuple(1, 2)) AS three_in_tuple;

┌─one_in_tuple─┬─two_in_tuple─┬─three_in_tuple─┐
│            1 │            1 │              0 │
└──────────────┴──────────────┴────────────────┘

이는 SELECT 1 IN (1, 2)처럼 동작합니다. 왼쪽도 튜플이면 오른쪽은 튜플 값의 집합으로 해석됩니다:

SELECT tuple(1, 2) IN (tuple(1, 2)) AS tuple_in_tuple;

┌─tuple_in_tuple─┐
│              1 │
└────────────────┘

이 특별 처리는 오른쪽이 단일 tuple 표현식일 때만 적용됩니다. 스칼라 왼쪽은 여러 튜플 값을 포함하는 오른쪽과 매칭될 수 없습니다:

SELECT 1 IN (tuple(1, 2), tuple(3, 4));

Code: 43. DB::Exception: Unsupported types for IN. First argument type UInt8. Second argument type Tuple(Tuple(UInt8, UInt8), Tuple(UInt8, UInt8)). (ILLEGAL_TYPE_OF_ARGUMENT)

ClickHouse는 IN 서브쿼리의 왼쪽과 오른쪽에서 타입이 달라도 허용합니다. 이 경우 오른쪽 값에 accurateCastOrNull 함수가 적용된 것처럼 오른쪽 값을 왼쪽 타입으로 변환합니다.

즉 데이터 타입이 Nullable이 되고, 변환이 수행될 수 없으면 NULL을 반환합니다.

Example

SELECT '1' IN (SELECT 1);

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

연산자의 오른쪽이 테이블 이름(예: UserID IN users)이면 UserID IN (SELECT * FROM users) 서브쿼리와 동등합니다. 쿼리와 함께 전송되는 외부 데이터로 작업할 때 사용하세요. 예를 들어 쿼리는 필터링해야 하는 'users' 임시 테이블에 로드된 사용자 ID 집합과 함께 전송될 수 있습니다.

연산자의 오른쪽이 Set 엔진을 가진 테이블 이름(항상 RAM에 있는 준비된 데이터 집합)이면, 데이터 집합은 각 쿼리마다 다시 생성되지 않습니다.

서브쿼리는 튜플을 필터링하기 위해 둘 이상의 컬럼을 지정할 수 있습니다.

Example:

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

IN 연산자의 왼쪽과 오른쪽 컬럼은 같은 타입이어야 합니다.

IN 연산자와 서브쿼리는 집계 함수와 람다 함수를 포함한 쿼리의 어떤 부분에서도 나타날 수 있습니다. Example:

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 Processing

요청 처리 중에 IN 연산자는 NULL을 사용한 연산 결과가 NULL이 연산자의 오른쪽인지 왼쪽인지와 무관하게 항상 0이라고 가정합니다. 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 │
└───────────────────────┘

Distributed Subqueries

서브쿼리가 있는 IN 연산자(JOIN 연산자와 유사)에는 일반 IN / JOIN과 GLOBAL IN / GLOBAL JOIN 두 가지 옵션이 있습니다. 분산 쿼리 처리에서 실행 방식이 다릅니다.

아래 설명된 알고리즘은 settings distributed_product_mode 설정에 따라 다르게 동작할 수 있음을 기억하세요.

일반 IN을 사용하면 쿼리가 원격 서버로 전송되고, 각 서버가 IN 또는 JOIN 절의 서브쿼리를 실행합니다.

GLOBAL IN / GLOBAL JOIN을 사용하면 먼저 모든 서브쿼리가 GLOBAL IN / GLOBAL JOIN으로 실행되고, 결과가 임시 테이블에 수집됩니다. 그런 다음 임시 테이블이 각 원격 서버로 전송되고, 서버들은 이 임시 데이터로 쿼리를 실행합니다.

GLOBAL ... JOIN의 경우 조인의 어느 쪽이 서브쿼리로 계산되는지는 조인 종류에 따라 달라집니다: LEFT/INNER 조인에서는 오른쪽 테이블이 계산되고, RIGHT 조인에서는 오른쪽 테이블이 보존되는 쪽이라 샤드에서 읽어야 하므로 왼쪽 테이블이 대신 계산됩니다.

비분산 쿼리에는 일반 IN / JOIN을 사용하세요.

분산 쿼리 처리에서 IN / JOIN 절의 서브쿼리를 사용할 때는 주의하세요.

몇 가지 예를 살펴보겠습니다. 클러스터의 각 서버에 일반 local_table이 있다고 가정합니다. 각 서버에는 클러스터의 모든 서버를 대상으로 하는 Distributed 타입의 distributed_table도 있습니다.

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의 데이터가 전적으로 단일 서버에 있도록 준비했다면 이는 정확하고 최적으로 동작합니다. 이 경우 필요한 모든 데이터가 각 서버에 로컬로 제공됩니다. 그렇지 않으면 결과가 부정확합니다. 이 쿼리 변형을 "local 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개의 기본 요청이 필요하며, 일반적으로 허용되지 않는 것으로 간주됩니다.

이런 경우 항상 IN 대신 GLOBAL 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 절 안에 로컬 테이블을 지정하는 것도 의미가 있습니다. 이 로컬 테이블이 요청 서버에서만 사용 가능하고 원격 서버에서 그 데이터를 사용하려는 경우입니다.

Distributed Subqueries and 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(기본값)로 설정되어 있어 이러한 임계값에 도달하면 예외가 발생한다는 점을 기억하세요.

Distributed Subqueries and 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에 대한 더 유연한 옵션을 사용할 수 있으며, 이는 더 다르고 더 최적인 동작을 만들 수 있습니다.

더 알아보기 (Learn more)

IN operator