조인의 수행 원리에 대해 알아봅시다.
중첩 루프 조인(Nested Loop Join): 중첩 for문과 같은 원리로 조건에 맞는 조인을 하는 방법정렬 병합 조인(Sorted Merge Join): 각각의 테이블을 조인할 필드 기준으로 정렬한 후 조인을 하는 방법해시 조인(Hash Join): 해시 테이블을 기반으로 조인하는 방법
- 의미: 중첩 for 문과 같은 원리로 조건에 맞는 조인을 하는 방법
- 반복문의 외부에 있는 테이블을 선행 테이블 또는 외부 테이블 (Outer Table)이라고 한다.
- 반복문의 내부에 있는 테이블을 후행 테이블 또는 내부 테이블(Inner Table)이라고 한다.
-
수행 과정
- 선행 테이블에서 조건을 만족하는 첫 번째 행을 스캔한다. 이 때, 선행 테이블에 주어진 조건을 만족하지 않는 경우 해당 데이터는 필터링 한다.
- 선행 테이블의 조인 키를 가지고 후행 테이블에 조인키가 존재하는지 스캔한다.
- 후행 테이블의 인덱스에 선행 테이블의 조인 키가 존재하는지 확인한다. 선행 테이블의 조인값이 후행 테이블에 존재하지 않으면 선행 테이블 데이터는 필터링 한다.
- 인덱스에서 추출한 레코드 식별자를 이용하여 후행 테이블을 액세스한다. 후행 테이블에 주어진 조건까지 모두 만족하면 해당 행을 추출버퍼에 넣는다.
-
특징
- 랜덤 접근에 대한 비용이 많이 증가하므로 대용량의 테이블에서는 사용하지 않는다.
- 절차적이며, 프로그래밍에서 FOR, WHILE문과 같은 반복문 구조로 수행된다.
- 선행테이블은 풀스캔하므로, 선행테이블의 크기가 작을수록 유리하다.
- 후행테이블에 대해서는 반드시 인덱스가 존재해야 NLJ가 가능하다.
- 인덱스 구성 전략에 따라 조인 효율이 크게 달라진다.
-
블록 중첩 루프 조인(BNL, Block Nested Loop)
- 중첩 루프 조인에서 발전한 형태이다.
- 조인할 테이블을 작은 블록으로 나눠서 블록 하나씩으로 조인한다.
- 의미: 각각의 테이블을 조인할 필드 기준으로 정렬하고, 정렬이 끝난 이후에 조인 작업을 수행하는 조인
-
수행 과정
- 선행 테이블에서 주어진 조건을 만족하는 행을 찾는다.
- 선행 테이블의 조인 키를 기준으로 정렬작업을 수행한다.
- 1~2번 작업을 선행 테이블의 조건을 만족하는 모든 행에 대해 반복 수행한다.
- 후행 테이블에서 주어진 조건을 만족하는 행을 찾는다.
- 후행 테이블의 조인 키를 기준으로 정렬작업을 수행한다.
- 3~4번 작업을 후행 테이블의 조건을 만족하는 모든 행에 대해 반복 수행한다.
- 정렬된 결과를 이용하여 조인을 수행하며 조인에 성공하면 추출버퍼에 넣는다.
-
특징
- 인덱스가 없어도 가능한 조인 방법이다.
- 대부분 해시 조인보다 느린 성능이지만, 아래와 같은 상황에서는 정렬 병합 조인이 유용하다.
- First 테이블에 Sort 연산을 대체할 인덱스가 있을 때
- 조인할 First 집합이 이미 정렬되어 있을 때
- 조인 조건식이 등차(=) 조건이 아닐때
- 두 테이블의 사이즈가 비슷한 경우에 유리하다.
- 의미: 해시 테이블을 기반으로 조인하는 방법
-
수행 과정
- 선행 테이블에서 주어진 조건을 만족하는 행을 찾는다.
- 선행 테이블의 조인키를 기준으로 해시함수를 적용하여 해시 테이블을 생성한다. 이때, 조인 컬럼과 SELECT 절에서 필요로 하는 컬럼도 함께 저장한다.
- 1~2번 작업을 선행 테이블의 조건을 만족하는 모든 행에 대해 반복 수행한다.
- 후행 테이블에서 주어진 조건을 만족하는 행을 찾는다.
- 후행 테이블의 조인키를 기준으로 해시 함수를 적용하여 해당 버킷을 찾는다.
- 조인에 성공하면 추출버퍼에 넣는다.
- 3~5번 작업을 후행 테이블의 조건을 만족하는 모든 행에 대해 반복 수행한다.
-
특징
- 조인 칼럼의 인덱스가 존재하지 않은 경우에도 사용할 수 있다.
- 메모리 사용이 큰 대용량 테이블 조인 시 메모리 외에 임시영역까지 사용하여 저장할 수 있어 유리하다.
- 두 개의 테이블을 조인한다고 했을 때, 하나의 테이블이 메모리에 온전히 들어간다면 보통 중첩 루프 조인보다 더 효율적이다.
- 메모리에 올릴 수 없을 정도로 크다면 디스크를 사용하는 비용이 발생된다.
- 동등(=) 조인에서만 사용할 수 있다.
- MySQL8.0.18 릴리스와 함께 이 기능을 사용할 수 있게 되었다.
-
빌드 단계
- 의미: 입력 테이블 중 하나를 기반으로 메모리 내 해시 테이블을 빌드하는 단계
- ex) persons와 countries라는 테이블을 조인한다고 했을 때 둘 중 더 작은 바이트의 테이블을 기반으로 해시 테이블을 빌드한다. 조인에 사용되는 필드인 'countries.country_id'가 해시 테이블의 키로 사용된다.
-
프로브 단계
- 의미: 레코드 읽기를 시작하며 각 레코드에서 해시 테이블의 키와 일치하는 레코드를 찾아서 결괏값으로 반환하는 단계
- 각 테이블은 한 번씩만 읽게 되어 중첩 루프 조인보다 보통 성능이 더 뛰어나다.
- 참고: 사용 가능한 메모리양은 시스템 변수 join_buffer_size에 의해 제어되며, 런타임 시에 조정할 수 있다.
- ex) 각 레코드에서 'persons.country_id'에 일치하는 레코드를 찾아서 결괏값으로 반환한다.
- 의미: 레코드 읽기를 시작하며 각 레코드에서 해시 테이블의 키와 일치하는 레코드를 찾아서 결괏값으로 반환하는 단계
