join 방식 종류
- Nested Loop join(=NL join)
- Sort Merge join
- Hash join
위 3가지 방식이 있다. MySQL에서는 Sort Merge join은 지원하지 않는다.
1. NL join / Nested Loop join
동작 과정
Outer, Inner테이블 모두 인덱스가 있는 경우
1. Outer 테이블의 인덱스 테이블에서 조건을 만족하는 행을 찾는다.
2. 1에서 찾은 값으로, Outer 테이블에서 행을 식별한다.
3. Inner 테이블에서, 2의 결과를 바탕으로 조인할 행의 인덱스를 찾는다.
4. 찾은 인덱스 값으로 Inner 테이블에서 해당 row를 찾는다.
특징
- 인덱스를 잘 구성하는 것이 특히 중요하다.
- Outer 테이블의 컬럼에는 인덱스가 없어도 되지만, Inner 테이블에는 꼭 인덱스가 있는 것이 좋다. 그렇지 않으면 Outer테이블에서 조건을 만족하는 매 행마다 Inner 테이블을 탐색하면서, 테이블 풀 스캔을 한다.
- 랜덤 액세스로 인한 비용이 있으므로, 대량 데이터를 다룰 땐 적합하지 않다. 소규모로, 트랜잭션 단위의 작업이 자주 일어나는 환경(OLTP라고 한다.)에서 적합하다.
- Driving(Outer) 테이블은, 대상 row수가 적어야 유리하다.
2. Merge join / Sort Merge join
- MySQL에서는 지원되지 않으므로, Oracle을 기준으로 설명한다.
동작 과정
- PGA라는, 프로세스별로 할당받는 메모리 공간에, 대상 테이블들을 조인 컬럼 기준으로 정렬한 상태로 저장한다.
- 정렬한 두 테이블의 데이터를 조인한다.
특징
- 정렬 과정에서 CPU 사용량이 증가하며, 대상 테이블들이 커서 PGA에 할당된 공간을 초과하면, 디스크의 임시 공간(Temporary tablespace)에 적재되어 디스크 I/O가 발생한다.
- 둘 중 한 쪽이라도 정렬이 완료되지 않으면, 조인을 시작하지 않는다. 따라서 양쪽의 테이블 조회 ~ 정렬 완료까지의 시간을 맞춰주면 효율적이다.
사용 시기
- 조인 컬럼에 인덱스가 없을 때
- 인덱스가 있지만, 대량 데이터를 조인하는 경우라서 랜덤 액세스로 인한 부하가 클 때
- 조인 조건으로 범위 비교 연산자(<, >, <=, >=)가 사용될 때
- 데이터가 이미 어느정도 정렬되어 있을 때
3. Hash join
동작 방식
- Build 단계 : Driving 테이블의 컬럼 값들로 해시 테이블을 생성한다.
- Probe 단계 : Driven 테이블의 컬럼 값들을 읽으면서, 해시 테이블을 탐색하며 조인한다.
특징
- 대량 데이터 처리에 적합하다.
- 대상 테이블이 커서, Sort 부하가 부담될 때도 적합하다.
- 다만 해시 테이블은 조인 연산 후 사라지는 임시 데이터이므로, 자주 발생하는 쿼리에 해시 조인을 사용하면 CPU 부하가 커질 수 있다.
- 따라서 간헐적으로 많은 데이터를 처리하는 배치 작업 등에 적합하다.
- Build 단계의 input에 중복값이 많으면 효율이 떨어진다.
- 카테시안 곱(조건이 없는 조인) 일 때도 해시 조인이 사용된다.
- 필터링 결과 row 수가 작은 테이블이 Driving(Outer), 다른 쪽이 Driven(Inner) 테이블이 된다.
- 한 쪽 테이블을 기준으로 해시 테이블을 생성하므로, 두 대상 테이블 중 한쪽만 작으면 된다.(메모리 공간 초과로 인한 디스크 I/O가 발생하지 않는다.)
MySQL에서, join_buffer_size 변수를 통해 해시 테이블이 저장될 공간의 크기를 설정할 수 있다.
- 만약 필요한 공간의 크기가 해당 값을 초과하면, 디스크의 파일에 저장하며, 디스크 I/O가 발생하여 속도가 느려진다.
- 만약 해시테이블 크기가 커서 많은 파일을 생성하고, 이로 인해 open_file_limit 변수값보다 많은 파일을 생성하면, 조인이 실패할 수 있다.
입력한 쿼리에서 어떤 조인 방식을 쓰는지 확인하는 방법
실행 계획을 조회하면 된다.
요청한 쿼리를 어떤 과정을 통해 처리할 것인지를 보여준다.
MySQL에서, explain(또는 explain analyze) 에 format=TREE 옵션을 줘서 확인할 수 있다.(대소문자 구별 X)
예시)
쿼리
EXPLAIN FORMAT=TREE
SELECT *
FROM Other o
JOIN Parent p on o.val = p.id
where o.id < 100000;결과
Join의 성능도 인덱스의 영향을 받는지?
중요하다. 특히 NL join의 내용에서도 확인했듯, 인덱스 유무가 조인 성능에 많은 영향을 미친다는 것을 알 수 있다.
3중 조인부터 바뀌는 동작 방식과, 성능에 미치는 영향
- 조인 연산은 두 테이블씩 여러 번 진행하며, 앞서 나온 결과를 임시로 저장한 후, 그 결과와 다음 테이블을 조인하는 식으로 진행한다.
- 이에 따라 더 효율적인 실행 계획을 찾기가 어려워진다.
- 또한 위의 임시 결과 집합을 저장하는 과정에서 메모리를 사용하며, 상황에 따라 디스크 공간을 사용하여 디스크 I/O가 발생할 수 있다.
참고 링크
