IN, NOT IN, GLOBAL IN, GLOBAL NOT IN 연산자는 기능이 매우 다양하므로 별도로 설명합니다.
연산자의 왼쪽은 단일 컬럼 또는 튜플이어야 합니다.
예시:
SELECT 서브쿼리를 사용할 수 있습니다.
하위 호환성을 위해, 오른쪽이 단일 tuple 표현식인 경우 IN 연산자의 왼쪽 값에 따라 값의 집합 또는 하나의 튜플 값으로 해석될 수 있습니다. 왼쪽이 스칼라 값인 경우, ClickHouse는 이 단일 오른쪽 tuple 표현식의 각 원소를 별개의 IN 값으로 처리합니다:
Query
Response
SELECT 1 IN (1, 2)와 동일하게 동작합니다. 왼쪽도 튜플인 경우, 오른쪽은 튜플 값의 집합으로 해석됩니다:
Query
Response
tuple 표현식인 경우에만 적용됩니다. 스칼라 왼쪽 값은 여러 튜플 값을 포함하는 오른쪽과 매칭할 수 없습니다:
Query
Response
IN 서브쿼리의 좌측과 우측 타입이 서로 달라도 허용됩니다.
이 경우, 우측에 accurateCastOrNull 함수를 적용한 것처럼 우측 값을 좌측 타입으로 변환합니다.
이는 데이터 타입이 널 허용으로 변환되고, 변환을 수행할 수 없는 경우 NULL을 반환한다는 것을 의미합니다.
예시
Query
Response
UserID IN users), 이는 서브쿼리 UserID IN (SELECT * FROM users)와 동일합니다. 쿼리와 함께 전송되는 외부 데이터를 처리할 때 이 방식을 사용하십시오. 예를 들어, 필터링이 필요한 사용자 ID 집합을 ‘users’ 임시 테이블에 로드한 후 쿼리와 함께 전송할 수 있습니다.
연산자의 오른쪽이 Set 엔진을 사용하는 테이블 이름(항상 RAM에 유지되는 미리 준비된 데이터 집합)인 경우, 데이터 집합은 쿼리마다 새로 생성되지 않습니다.
서브쿼리는 튜플 필터링을 위해 두 개 이상의 컬럼을 지정할 수 있습니다.
예시:
Query
IN 연산자의 왼쪽과 오른쪽에 있는 컬럼은 동일한 타입이어야 합니다.
IN 연산자와 서브쿼리는 집계 함수와 람다 함수를 포함해 쿼리의 어느 부분에나 사용할 수 있습니다.
예시:
Query
Response
IN 절의 서브쿼리는 항상 단일 서버에서 한 번만 실행됩니다. 종속 서브쿼리는 없습니다.
NULL 처리
요청 처리 중IN 연산자는 NULL을 포함한 연산의 결과가 NULL이 연산자의 오른쪽에 있든 왼쪽에 있든 상관없이 항상 0이라고 가정합니다. transform_null_in = 0인 경우 NULL 값은 어떤 데이터셋에도 포함되지 않으며, 서로 대응되지도 않고 비교할 수도 없습니다.
다음은 t_null 테이블을 사용한 예시입니다:
SELECT x FROM t_null WHERE y IN (NULL,3)를 실행하면 다음 결과가 반환됩니다:
y = NULL인 행이 쿼리 결과에서 제외되는 것을 확인할 수 있습니다. 이는 ClickHouse가 NULL이 (NULL,3) Set에 포함되는지 여부를 판단할 수 없어 연산 결과로 0을 반환하고, SELECT가 이 행을 최종 출력에서 제외하기 때문입니다.
외부 메모리에서의 IN
IN이 서브쿼리나 테이블로부터 생성하는 집합(set)은 너무 커서 메모리에 유지할 수 없는 경우 디스크에 기록될 수 있습니다.
이 경우 조회 시 디스크에서 집합(set)을 읽게 되므로 추가적인 디스크 I/O가 발생하고,
쿼리가 느려질 수 있습니다. IN (1, 2, 3)과 같은 값 목록과 Set 엔진을 사용하는 테이블은 항상
메모리에 유지됩니다.
스필 시작 시점은 다음 2개의 설정으로 제어합니다:
max_bytes_before_external_set은 전체 쿼리 메모리를 기준으로 한 임계값을 바이트 단위로 지정합니다. 기본값은0(비활성화됨)입니다.max_bytes_ratio_before_external_set은 서버 또는 사용자 제한 내에서 사용 가능한 메모리 중 일정 비율을 지정하며, 이 값은 실행 시작 시점에 측정됩니다. 기본값은 사용 가능한 메모리의 절반인0.5이며, 두 제한이 모두 적용되지 않으면 아무런 효과가 없습니다.
max_memory_usage는 이 비율에 영향을 주지 않습니다. 쿼리 메모리 제한을 기준으로 스필을 구성하려면
해당 제한보다 낮은 절대 임계값을 설정하십시오. 이 임계값은 메모리 사용량(memory usage)을 제한하지 않습니다. 다른
쿼리 처리(query processing)와 디스크 유출(spill) 자체에 필요한 여유 공간을 남겨 두십시오.
디스크에 있는 집합(set)은 키에 대한 블룸 필터(Bloom filter)를 메모리에 유지하므로, 집합(set)에 없는 키를 조회할 때
디스크 읽기를 건너뛸 수 있습니다. max_bytes_for_external_set_bloom_filter는 이 필터(filter)의 메모리를 제한하며,
기본값은 128 MiB입니다.
디스크에 있는 집합(set)은 조회 시 읽은 블록도 메모리에 캐시하므로, 이후 같은 블록에 있는 키를 조회할 때
디스크를 다시 읽지 않습니다. max_bytes_for_external_set_cache는 이 캐시의 메모리를 제한하며,
기본값은 128 MiB입니다.
생성 중에 디스크에 기록된 집합(set)은 프라이머리 키나 데이터 스키핑 인덱스에 활용할 수 없습니다.
GLOBAL IN을 사용하면 서브쿼리 결과를 원격 서버로 전송하는 임시 테이블은 메모리에 유지되지만,
원격 서버가 이를 바탕으로 생성하는 집합(set)은 디스크에 기록될 수 있습니다.
생성 중에 집합(set)이 디스크에 기록되면 집합(set)의 크기 제한을 검사하기 전에 서브쿼리 전체를 읽습니다.
max_rows_in_set은 고유 키의 개수를 세고, max_bytes_in_set은 각 키를 집합(set) 내에서 차지하는 폭(1~32바이트, 예: UInt64는 8바이트,
String은 16바이트)으로 계산하며, 블룸 필터(Bloom filter)나 캐시처럼 조회에 사용되는 메모리는 포함하지 않습니다.
set_overflow_mode = 'break'를 사용하면 집합(set)은 서브쿼리의 첫 번째 행(First row)들의 키가 아니라,
디스크에 저장되는 순서상 앞에 오는 키를 제한에 도달할 때까지 유지합니다. 숫자, 날짜, 짧은 FixedString 값으로 이루어진 키는
단독이든 튜플 안에 있든 가장 작은 키들이 유지됩니다. String 등 그 밖의 키는 임의의 부분 집합이 유지됩니다.
분산 서브쿼리
서브쿼리와 함께 사용하는IN 연산자에는 두 가지 옵션이 있습니다(JOIN 연산자와 유사): 일반 IN / JOIN과 GLOBAL IN / GLOBAL JOIN입니다. 두 옵션은 분산 쿼리 처리 시 실행 방식에 차이가 있습니다.
아래에 설명된 알고리즘은 설정
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을 사용하여 실행됩니다.
예시로, 다음 쿼리는
IN을 사용한 쿼리를 살펴보겠습니다:
- 두 사이트 잠재고객의 교집합 계산
IN 절의 데이터 집합은 각 서버에서 독립적으로 수집되며, 해당 서버에 로컬로 저장된 데이터만을 대상으로 합니다.
이 방식은 해당 상황을 미리 고려하여 단일 UserID의 데이터가 하나의 서버에 완전히 저장되도록 클러스터 서버 전체에 데이터를 분산해 둔 경우에만 올바르고 최적으로 동작합니다. 이 경우 필요한 모든 데이터를 각 서버에서 로컬로 조회할 수 있습니다. 그렇지 않으면 결과가 부정확해집니다. 이러한 쿼리 변형을 “local IN”이라고 합니다.
클러스터 서버 전체에 데이터가 무작위로 분산되어 있을 때 쿼리가 올바르게 동작하게 하려면, 서브쿼리 내에 distributed_table을 지정하십시오. 쿼리는 다음과 같습니다:
IN 대신 항상 GLOBAL IN을 사용해야 합니다. 다음 쿼리에서 동작 방식을 살펴보겠습니다:
_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을 살펴보십시오:
some_predicate가 충분히 선택적이지 않으면 많은 양의 데이터를 반환해 성능 문제가 발생할 수 있습니다. 이러한 경우 네트워크를 통한 데이터 전송을 제한하는 것이 좋습니다. 또한 set_overflow_mode는 throw(기본값)로 설정되어 있으므로, 이러한 임계값에 도달하면 예외가 발생한다는 점에 유의하십시오.
분산 서브쿼리와 max_parallel_replicas
max_parallel_replicas가 1보다 크면 분산 쿼리가 한 번 더 변환됩니다. 예를 들어, 다음과 같습니다: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의 더 유연한 옵션을 사용하여 서로 다르고 더 최적화된 동작을 구현할 수 있습니다.