ClickHouse에서 JOIN 사용

ClickHouse에서 JOIN 사용 (Using JOINs in ClickHouse)

ClickHouse는 다양한 조인 알고리즘과 함께 완전한 JOIN 지원을 제공합니다. 성능을 극대화하려면 이 가이드의 조인 최적화 제안을 따르는 것이 좋아요.

출처: 문서

본문

ClickHouse는 다양한 조인 알고리즘 선택과 함께 완전한 JOIN 지원을 가집니다. 성능을 최대화하려면 이 가이드에 나열된 조인 최적화 제안을 따르는 것을 권장합니다.

최적의 성능을 위해, 특히 밀리초 성능이 필요한 실시간 분석 워크로드에서는 쿼리의 JOIN 수를 줄이는 것을 목표로 해야 합니다. 쿼리에서 최대 3~4개 조인을 목표로 하세요. 데이터 모델링 섹션에서 역정규화, 딕셔너리, 머티얼라이즈드 뷰를 포함해 조인을 최소화하는 여러 변경 사항을 자세히 설명합니다.
ClickHouse 24.12부터 쿼리 플래너는 두 테이블 조인을 자동으로 재정렬해 더 작은 테이블을 오른쪽에 배치해 최적의 성능을 냅니다. 버전 25.9에서는 이것이 세 개 이상의 테이블을 조인하는 쿼리 전체에 조인 순서를 최적화하도록 확장되었습니다.
쿼리에 직접 조인, 즉 LEFT ANY JOIN이 필요하다면 — 아래처럼 — 가능하면 딕셔너리를 사용할 것을 권장합니다.
내부 조인을 수행한다면, IN 절을 사용한 서브쿼리로 작성하는 것이 종종 더 최적입니다. 기능적으로 동등한 다음 쿼리들을 고려해 보세요. 둘 다 질문에 ClickHouse를 언급하지 않지만 comments에는 언급하는 posts의 수를 찾습니다.

SELECT count()
FROM stackoverflow.posts AS p
ANY INNER `JOIN` stackoverflow.comments AS c ON p.Id = c.PostId
WHERE (p.Title != '') AND (p.Title NOT ILIKE '%clickhouse%') AND (p.Body NOT ILIKE '%clickhouse%') AND (c.Text ILIKE '%clickhouse%')

┌─count()─┐
│       86 │
└─────────┘

1 row in set. Elapsed: 8.209 sec. Processed 150.20 million rows, 56.05 GB (18.30 million rows/s., 6.83 GB/s.)
Peak memory usage: 1.23 GiB.

카테시안 곱을 원하지 않으므로(각 게시물에 하나의 일치만 원하므로) 일반 INNER 조인 대신 ANY INNER JOIN을 사용함을 주목하세요.

이 조인은 서브쿼리를 사용해 다시 쓸 수 있고 성능이 크게 개선됩니다:

SELECT count()
FROM stackoverflow.posts
WHERE (Title != '') AND (Title NOT ILIKE '%clickhouse%') AND (Body NOT ILIKE '%clickhouse%') AND (Id IN (
        SELECT PostId
        FROM stackoverflow.comments
        WHERE Text ILIKE '%clickhouse%'
))

┌─count()─┐
│       86 │
└─────────┘

1 row in set. Elapsed: 2.284 sec. Processed 150.20 million rows, 16.61 GB (65.76 million rows/s., 7.27 GB/s.)
Peak memory usage: 323.52 MiB.

ClickHouse가 모든 조인 절과 서브쿼리로 조건을 밀어 내리려 시도하지만, 사용자는 가능한 모든 하위 절에 항상 조건을 수동으로 적용할 것을 권장합니다 — 그래야 JOIN할 데이터 크기를 최소화할 수 있습니다. 2020년 이후 Java 관련 게시물의 up-vote 수를 계산하려는 아래 예제를 고려해 보세요.

더 큰 테이블이 왼쪽에 있는 순진한 쿼리는 56초에 완료됩니다:

SELECT countIf(VoteTypeId = 2) AS upvotes
FROM stackoverflow.posts AS p
INNER JOIN stackoverflow.votes AS v ON p.Id = v.PostId
WHERE has(arrayFilter(t -> (t != ''), splitByChar('|', p.Tags)), 'java') AND (p.CreationDate >= '2020-01-01')

┌─upvotes─┐
│  261915 │
└─────────┘

1 row in set. Elapsed: 56.642 sec. Processed 252.30 million rows, 1.62 GB (4.45 million rows/s., 28.60 MB/s.)

이 조인을 재정렬하면 성능이 1.5초로 극적으로 개선됩니다:

SELECT countIf(VoteTypeId = 2) AS upvotes
FROM stackoverflow.votes AS v
INNER JOIN stackoverflow.posts AS p ON v.PostId = p.Id
WHERE has(arrayFilter(t -> (t != ''), splitByChar('|', p.Tags)), 'java') AND (p.CreationDate >= '2020-01-01')

┌─upvotes─┐
│  261915 │
└─────────┘

1 row in set. Elapsed: 1.519 sec. Processed 252.30 million rows, 1.62 GB (166.06 million rows/s., 1.07 GB/s.)

왼쪽 테이블에 필터를 추가하면 성능이 0.5초로 더 개선됩니다.

SELECT countIf(VoteTypeId = 2) AS upvotes
FROM stackoverflow.votes AS v
INNER JOIN stackoverflow.posts AS p ON v.PostId = p.Id
WHERE has(arrayFilter(t -> (t != ''), splitByChar('|', p.Tags)), 'java') AND (p.CreationDate >= '2020-01-01') AND (v.CreationDate >= '2020-01-01')

┌─upvotes─┐
│  261915 │
└─────────┘

1 row in set. Elapsed: 0.597 sec. Processed 81.14 million rows, 1.31 GB (135.82 million rows/s., 2.19 GB/s.)
Peak memory usage: 249.42 MiB.

이 쿼리는 앞서 언급한 대로 INNER JOIN을 서브쿼리로 옮기고 바깥·안쪽 쿼리 모두에서 필터를 유지하면 더 개선될 수 있습니다.

SELECT count() AS upvotes
FROM stackoverflow.votes
WHERE (VoteTypeId = 2) AND (PostId IN (
        SELECT Id
        FROM stackoverflow.posts
        WHERE (CreationDate >= '2020-01-01') AND has(arrayFilter(t -> (t != ''), splitByChar('|', Tags)), 'java')
))

┌─upvotes─┐
│  261915 │
└─────────┘

1 row in set. Elapsed: 0.383 sec. Processed 99.64 million rows, 804.55 MB (259.85 million rows/s., 2.10 GB/s.)
Peak memory usage: 250.66 MiB.

JOIN 알고리즘 선택

ClickHouse는 여러 조인 알고리즘을 지원합니다. 이 알고리즘들은 보통 메모리 사용을 성능과 맞바꿉니다. 다음은 상대적 메모리 소비와 실행 시간에 따른 ClickHouse 조인 알고리즘의 개요입니다:

이 알고리즘들은 조인 쿼리가 계획되고 실행되는 방식을 결정합니다. 기본적으로 ClickHouse는 사용된 조인 타입, 엄격성, 조인된 테이블의 엔진에 따라 직접(direct) 또는 해시 조인 알고리즘을 사용합니다. 대안으로 ClickHouse는 리소스 가용성과 사용에 따라 런타임에 사용할 조인 알고리즘을 적응적으로 선택하고 동적으로 변경하도록 구성할 수 있습니다: join_algorithm=auto일 때 ClickHouse는 먼저 해시 조인 알고리즘을 시도하고, 그 알고리즘의 메모리 한도가 위반되면 실행 중에 부분 병합 조인(partial merge join)으로 전환합니다. 트레이스 로깅을 통해 어떤 알고리즘이 선택되었는지 관찰할 수 있습니다. ClickHouse는 또한 join_algorithm 설정으로 원하는 조인 알고리즘을 직접 지정할 수 있게 합니다.

각 조인 알고리즘에서 지원되는 JOIN 타입은 아래에 표시되며 최적화 전에 고려해야 합니다:

각 JOIN 알고리즘의 전체 상세 설명은 여기에서 찾을 수 있으며, 장점, 단점, 확장 속성을 포함합니다.

적절한 조인 알고리즘 선택은 메모리 최적화를 원하는지 성능 최적화를 원하는지에 달려 있습니다.

JOIN 성능 최적화

핵심 최적화 지표가 성능이고 조인을 최대한 빠르게 실행하려 한다면, 올바른 조인 알고리즘을 선택하는 다음 결정 트리를 사용할 수 있습니다:

(1) 오른쪽 테이블의 데이터를 인메모리 저지연 키-값 데이터 구조(예: 딕셔너리)에 미리 로드할 수 있고, 조인 키가 기본 키-값 스토리지의 키 속성과 일치하며, LEFT ANY JOIN 의미가 적절하다면 — 직접 조인(direct join)이 적용 가능하며 가장 빠른 접근 방식을 제공합니다.
(2) 테이블의 물리적 행 순서가 조인 키 정렬 순서와 일치한다면 경우에 따라 다릅니다. 이 경우 완전 정렬 병합 조인(full sorting merge join)이 정렬 단계를 건너뛰어 메모리 사용이 크게 줄고, 데이터 크기와 조인 키 값 분포에 따라 일부 해시 조인 알고리즘보다 실행 시간도 더 빠릅니다.
(3) 병렬 해시 조인(parallel hash join)의 추가 메모리 사용 오버헤드와 함께도 오른쪽 테이블이 메모리에 들어맞는다면 이 알고리즘 또는 해시 조인이 더 빠를 수 있습니다. 이것은 데이터 크기, 데이터 타입, 조인 키 컬럼의 값 분포에 달려 있습니다.
(4) 오른쪽 테이블이 메모리에 들어맞지 않는다면 역시 경우에 따라 다릅니다. ClickHouse는 세 가지 메모리 비한정 조인 알고리즘을 제공합니다. 세 가지 모두 일시적으로 데이터를 디스크로 유출(spill)합니다. 완전 정렬 병합 조인과 부분 병합 조인은 데이터의 사전 정렬이 필요합니다. Grace 해시 조인은 데이터에서 해시 테이블을 대신 구축합니다. 데이터 양, 데이터 타입, 조인 키 컬럼의 값 분포에 따라 데이터에서 해시 테이블을 구축하는 것이 데이터를 정렬하는 것보다 빠른 시나리오가 있을 수 있습니다. 그 반대도 있습니다.

부분 병합 조인은 큰 테이블이 조인될 때 메모리 사용을 최소화하도록 최적화되어 있으며, 조인 속도가 매우 느린 대가가 있습니다. 특히 왼쪽 테이블의 물리적 행 순서가 조인 키 정렬 순서와 일치하지 않을 때 그렇습니다.

Grace 해시 조인은 세 가지 메모리 비한정 조인 알고리즘 중 가장 유연하며, grace_hash_join_initial_buckets 설정으로 메모리 사용과 조인 속도를 잘 제어할 수 있습니다. 데이터 양에 따라 grace 해시는 두 알고리즘의 메모리 사용이 대략 정렬되도록 buckets 수를 선택했을 때 부분 병합 알고리즘보다 빠르거나 느릴 수 있습니다. grace 해시 조인의 메모리 사용이 완전 정렬 병합의 메모리 사용과 대략 정렬되도록 구성되면, 테스트 실행에서 완전 정렬 병합이 항상 더 빨랐습니다.

세 가지 메모리 비한정 알고리즘 중 어느 것이 가장 빠른지는 데이터 양, 데이터 타입, 조인 키 컬럼의 값 분포에 달려 있습니다. 어떤 알고리즘이 가장 빠른지 결정하려면 현실적인 데이터 양으로 몇 가지 실제 데이터 벤치마크를 실행하는 것이 항상 최선입니다.

메모리 최적화

빠른 실행 시간 대신 가장 낮은 메모리 사용으로 조인을 최적화하려면 대신 이 결정 트리를 사용할 수 있습니다:

(1) 테이블의 물리적 행 순서가 조인 키 정렬 순서와 일치하면 완전 정렬 병합 조인의 메모리 사용이 최저입니다. 정렬 단계가 비활성화되므로 좋은 조인 속도라는 추가 이점이 있습니다.
(2) grace 해시 조인은 조인 속도를 희생하고 많은 수의 buckets를 구성해 매우 낮은 메모리 사용으로 조정할 수 있습니다. 부분 병합 조인은 의도적으로 적은 메모리를 사용합니다. 외부 정렬이 활성화된 완전 정렬 병합 조인은 일반적으로 부분 병합 조인보다 더 많은 메모리를 사용하며(행 순서가 키 정렬 순서와 일치하지 않는다고 가정), 조인 실행 시간이 훨씬 좋다는 이점이 있습니다.

위 내용에 대해 더 자세한 정보가 필요한 사용자는 다음 블로그 시리즈를 권장합니다.

ClickHouse에서 JOIN 사용