이전 포스팅에서는 NL 조인에 대해서 알아보았다. NL 조인의 경우, 조인 컬럼에 대해 인덱스가 있는 환경에서 작동하기 때문에 탐색 대상 데이터가 많을 수록 성능이 나빠지게 된다.(랜덤 I/O가 많아지기 때문에)
이런 단점을 해결하기 위해 소트 머지 조인과 해시 조인이라는 방법이 나오게 됐는데, 이번 포스팅에서는 소트 머지 조인, 해시 조인에 대해서 알아보려고 한다.
소트 머지 조인(Merge Sort Join)
소트 머지 조인은 조인 컬럼 기준으로 양쪽 테이블을 정렬한 후, 정렬된 양쪽 테이블을 순차적으로 병합(Merge)하여 Join을 진행한다.
다만, 정렬 연산이 발생하기 때문에, 조인 컬럼에 인덱스가 없을 경우 정렬 비용이 발생하게 된다.
MySQl은 Sort Merge Join을 지원하지 않는다.
해시 조인 (Hash Join)
해시 조인은 해시 테이블을 이용하여 양쪽 테이블에서 Join하게 될 row를 찾는 방식이다.
해시 조인은 다음 두 단계로 진행이 된다.
Build : 작은 테이블(small row set을 갖는 테이블)의 조인 컬럼을 키 값으로, 해시 테이블을 만든다.
Probe : 큰 테이블의 조인 컬럼을 키 값으로 해시 테이블을 탐색하여 조인한다.
NL 조인과 소트 머지 조인은 각각 명확한 단점(인덱스 기반 랜덤 I/O, 테이블 정렬 작업)이 존재한다. 해시 조인은 이 단점들을 각각 다음과 같은 방식으로 해결했다.
- 메모리 사용 : 해시 테이블을 메모리 공간에 생성하기 때문에, 랜덤 I/O가 발생하지 않는다. (NL 조인을 개선)
- 해시 테이블을 통한 탐색: 해시 함수를 통해서 키 값을 얻고, 해당 키 값으로 해시 테이블에서 데이터를 탐색하기 때문에 정렬 연산이 발생하지 않는다.(소트 머지 조인 개선)
다만, 생성해야하는 해시 테이블의 크기에 따라 지나치게 많은 공간을 점유하게 되면 오버헤드가 발생할 수 있다.
MySQL에서
MySQl은 NL 조인이 대량 데이터 조회시에 보이는 약점을 보완하기 위해 Block NL Join을 대안으로 사용해왔다. 하지만, BNL Join은 8.0.18 버전 이후로 더 이상 사용되지 않고, BNL Join이 사용될 상황에 Hash Join이 사용되도록 변경되었다.
조인 버퍼
조인 버퍼는 MySQL에서 해시 조인에 사용될 해시 테이블이 생성되는 공간이다.
공식문서를 보면, Join Buffer가 8.0.18 버전 이후 hash join 용 메모리로 사용되는 것을 알 수 있다. hash join의 경우, join_buffer_size라는 시스템 변수 수치 이상의 메모리를 사용할 수 없고, 이를 초과하게 될 경우 디스크를 사용하게 된다.
해시 조인을 사용할 수 있는 경우
MySQL에서는 다음과 같은 상황에서 해시 조인을 사용할 수 있다.
1."=" 조건이 있는 경우 (equi-join)
가장 일반적인 경우라고 생각된다. 아래와 같이 Join 시에 동등 조건이 있는 경우, hash join을 사용할 수 있다. 단, 조인 컬럼에는 인덱스가 없어야 한다.
SELECT * FROM t1
JOIN t2 ON t1.c1 = t2.c1위처럼 equi-join을 하는 쿼리의 경우에 hash join을 사용할 수 있다고 이해하면 된다.
2."=" 조건이 없는 경우
MySQL 8.0.20 이후 equi-join condition이 없는 경우에도 hash join을 사용할 수 있다. 가령, 아래와 같은 경우에도 동등 조건이 없는 Join을 Hash join으로 처리할 수 있다.
3.카테시안 곱 - Join 조건이 없는 경우
카테시안 곱이란, FROM절에 2개 이상의 테이블이 있을 때, 두 테이블 사이에 유효 join 조건을 적지 않았을 경우 해당 테이블에 대한 모든 데이터를 전부 결합하여 테이블에 존재하는 행 갯수를 곱한 만큼의 결과값이 반환되는 것이다.
아래와 같은 경우가 카테시안 곱이다. t1 테이블과 t2 테이블을 JOIN하고 있지만, 조인 조건이 생략되어있고 실행 계획상으로 hash join을 사용하는 것을 확인할 수 있다.
hash join 옵티마이저 힌트
MySQL에서 hash join을 유도하기 위한 옵티마이저 힌트는 다음과 같다.
1.BNL/NO_BNL : hash join이 도입되기 이전 BNL을 제어하기 위해 사용하던 힌트인데, 8.0.20 버전 이후부터 hash join을 제어할 수 있는 힌트로 사용할 수 있다.
2.HASH_JOIN/NO_HASH_JOIN (MySQL 8.0.18에서만 가능)
이를 바탕으로 실제 hash join이 어떤 상황에서 사용되는지, 사용할 수 있는지 예제를 통해 살펴보자.
해시 조인 예제
이제 직접 MySQL에서 hash join을 사용할 수 있는 상황에 대해 간단한 예제로 실행 계획을 확인해보겠다. 스키마는 이 포스팅에서 생성한 것을 사용한다.
버전 : MySQL 8.0.31
1. 동등 조건이 있는 경우(인덱스 X)
쿼리
SELECT * FROM POST
JOIN COMMENT ON COMMENT.created_at = POST.createdAt;실행계획
양 테이블의 created_at 테이블에 인덱스가 없기 때문에, 옵티마이저 힌트 없이도 hash join이 사용되고 있다. 이는 공식문서에서 확인했던 내용과 동일하다.
2. 동등 조건이 있는 경우 (인덱스 O)
그렇다면 인덱스가 있는 경우에는 어떻게 될까? 먼저 각 테이블의 created_at 컬럼에 인덱스를 생성하고 같은 쿼리의 실행 계획을 살펴보겠다.
인덱스 생성
CREATE INDEX idx_post_createdAt ON POST(createdAt);
CREATE INDEX idx_comment_createdAt ON COMMENT(created_at);실행 계획
인덱스가 생성됐기 때문에 join 방식이 NL join으로 바뀌었다. 이제 옵티마이저 힌트를 통해서 hash join으로 유도를 해보겠다.
쿼리
/*+ BNL(COMMENT) */
SELECT * FROM POST
JOIN COMMENT ON COMMENT.created_at = POST.createdAt;실행계획
인덱스가 있는 경우에도 힌트를 적절하게 사용하면 hash join으로 유도할 수 있다. 다만, 힌트를 사용하지 않았을 때 옵티마이저가 NL Join을 포함한 실행 계획을 선택한 것을 보면 알 수 있듯이, hash join을 사용한 실행 계획의 경우가 약 6.5배 정도 실행 속도가 느리다 (160ms - 1084 ms). 테이블의 크기나 인덱스 구성에 따라 달라질 여지가 있지만, 이런 상황에서는 NL join이 효율이 좋은 것으로 보인다.
3. 동등 조건이 없는 경우 (인덱스 X)
다음은 동등 조건이 없는 경우를 살펴보겠다. 1, 2번에서 실행한 쿼리를 등호 조건만 부등호로 바꿔서 실행해보겠다.
쿼리
SELECT * FROM POST
JOIN COMMENT ON COMMENT.created_at > POST.createdAt;실행계획
일단, 어마무시한 예상 비용과 row 개수를 볼 수 있다... 조인 컬럼에 인덱스가 없기 때문에 부분 범위 처리가 불가능하고, POST 테이블 전체를 해시 테이블에 생성하고 있는데, COMMENT 테이블과 해시 테이블의 컬럼 값을 모두 비교하기 위해서 200만 * 10만 번의 연산이 필요하기 때문이다.
4. 동등 조건이 없는 경우 (인덱스 O)
그렇다면, 다시 createdAt 컬럼에 인덱스를 생성하고, 아래 쿼리에 대해 실행계획을 살펴보겠다.
인덱스 생성
CREATE INDEX idx_post_created_at ON POST(createdAt);
CREATE INDEX idx_post_created_at ON COMMENT(created_at);쿼리
SELECT * FROM POST JOIN COMMENT ON POST.createdAt >
COMMENT.created_at;실행계획
옵티마이저는 NL Join이 포함된 실행계획을 선택하고 있다. 초반에 살펴보았듯이, hash join은 인덱스가 있는 대부분의 경우 NL Join을 선택할 것이다. 옵티마이저 힌트를 이용해서 hash join을 유도해보겠다.
쿼리
SELECT /*+ BNL(COMMNET) */ * FROM POST
JOIN COMMENT ON COMMENT.created_at > POST.createdAt;실행 계획
무슨 이유인지, hash join으로 유도가 되지 않는다. 하지만 이 케이스만 가지고 조인 컬럼에 동등 조건이 없고, 조인 컬럼의 인덱스가 생성 되어있을 경우에 MySQL이 hash join을 사용할 수 없다고 단정지을 수는 없을 것 같다.
5. 카데시안 곱
마지막 카데시안 곱이다.
쿼리
SELECT * FROM POST
JOIN COMMENT
WHERE POST.category > 5;실행계획
여기서 특이한 점은, POST 테이블의 category 컬럼은 인덱스 생성이 되어있는 컬럼이라는 것이다. 동등 조건 여부에 상관 없이, 카데시안 곱의 경우에는 hash join이 사용될 수 있음을 알 수 있다. 물론 NO_BNL(테이블명) 힌트를 활용해서 NL Join으로 유도할 수도 있다.
마무리
이번 포스팅에서 사용한 예제가 해시 조인과 NL 조인에 대한 상황을 100% 대변해주지는 못한다. 하지만 이번에 해시 조인과 소트 머지 조인, NL 조인의 작동 원리 및 개념을 이해했다면 특정 상황에서 어떤 조인이 유리할지 판단할 수 있는 기준은 생겼을 것이다.
특히 예제를 살펴보면서 상황에 따라 어떤 조인의 실행 계획이 저비용인지 판단할 수가 있었는데, 이를 토대로 테이블 구조(인덱스, 크기)에 맞춰서 어떤 조인이 유리한지 판단할 수 있다면 상황에 맞게 튜닝 을 해볼 수 있을 것 같다.
참고
