[MSSQL] hash, merge, Nested loop Join 실행계획 설명

이번시간에는 실행계획에서 볼수있는 조인의 3가지 방식에 대해서 알아보겠습니다.

 

일반적으로 알고 있는 조인의 종류는 다음과 같습니다.

1. INNER

2. LEFT OUTER

3. RIGHT OUTER JOIN

 

그리고 위의 3가지 조인은 내부적으로 아래의 방식으로 돌아가게 됩니다.

(SQL 옵티마이저가 자동으로 적절한 실행계획을 만듬)

1. NESTED LOOP JOIN (중첩반복)
2. SORT MERGE JOIN (정렬병합)
3. HASH JOIN (해쉬매치)

 

내부적으로 발생하는 조인의 방식에 따라 SQL쿼리 성능이 달라질 수 있습니다.

테이블 및 인덱스 등을 고려하여 SQL 옵티머이저가 실행계획을 자동으로 생성하지만 옵션을 주어서 강제로 변경도 가능합니다.

 

 

아래에서 설명하는 내부적인 조인의 로직을 기억해 두시고 옵션을 사용해 비교해보시기 바랍니다.

---

1. NESTED LOOP JOIN (중첩반복)

바깥 테이블의 처리 범위를 하나씩 접근하면서 추출된 값으로 테이블을 조인하는 방식입니다.
프로그래밍 개발시 FOR문과 동일하게 돈다고 생각하시면 되겠습니다.

옵션으로 LOOP JOIN을 사용합니다.

SELECT * FROM [스키마명].[테이블명] AS T1
INNER JOIN [후행테이블] AS 별명2 ON T1.키 = T2.키
OPTION (LOOP JOIN)

주요 특징
1. 순차적으로 처리되어 돌아가기 떄문에 안쪽 테이블에 인덱스 필요
2. 메모리 사용량이 가장 적음
3. 두 테이블의 랜덤 I/O가 높게 나옴

 

2. SORT MERGE JOIN (정렬병합)

두 테이블의 조인 컬럼을 기준으로 정렬을 진행한 다음 정렬된 양쪽 집합을 머지합니다.

경우에 따라 LOOP JOIN보다 훨씬 빠를 수도 있고, 랜덤 엑세스가 줄어들어 시스템 부하를 감소시킵니다.

(오라클과 다르게 SQL 서버는 조인연산자가 '='인 경우에만 sort merge join을 실행합니다.)

 

SELECT * FROM [스키마명].[테이블명] AS T1
INNER JOIN [후행테이블] AS 별명2 ON T1.키 = T2.키
OPTION (MERGE JOIN)

 

주요 특징

1. 동시처리(두 테이블이 모두 정렬되어 머지할 준비가 되어야 진행됨)

2. 머지할 작업대상을 줄여 주기 때문에 인덱스 권장

3. 정렬할 테이블의 크기에 따라 메모리 사용량이 증가합니다.

 

 

3. HASH JOIN (해시매치)

해시 함수 기법을 사용하여 조인을 수행합니다.

주로 양쪽 테이블의 조인 컬럼에 인덱스가 없는 경우 사용하게 됩니다.

 

1) 조인할 두개의 테이블 중에서 범위가 좁은 테이블을 메모리로 가져옵니다.
2) Join 조건 칼럼의 데이터를 Hash 함수에 넣어서 나온 Hash Value 값으로 Hash Table을 생성합니다.
3) 후행 테이블의 Join 조건을 Hash 함수에 넣어서 Hash Value를 생성하고 이 값을 선행 테이블의 Hash Table의 값과 비교하여 같은 값을 찾아 매칭합니다.

SELECT * FROM [스키마명].[테이블명] AS T1
INNER JOIN [후행테이블] AS 별명2 ON T1.키 = T2.키
OPTION (HASH JOIN)

Sort Merge Join과 Hash Join은 테이블 전체를 읽는다는 부분은 동일하지만

Sort-Merge Join에서 실행하는 정렬은 실행하지 않기 때문에 성능이 2배 이상 날 수 있습니다,

 

주요 특징

1. 인덱스 필요없음

2. 대용량 테이블에 유리

3. 정렬하지 않은 상태로 출력

4. 메모리 사용

 

이상으로 조인의 내부적인 실행계획 3가지 종류에 대해서 알아보았습니다.