[MySQL] NL, Sort Merge, Hash Join 최적의 결합 방법 선택하기

테이블을 조인 할 때, Nested Loops, Sort Merge, Hash Join 결합을 많이 사용합니다.

Nested Loops

외부 테이블(Outer Table)에서 조건에 만족하는 레코드를 검색한 후, 결합 조건에 일치하는 레코드를 내부 테이블(Inner Table)에서 조인하는 방식입니다.

Outer Join 에서 만족하는 레코드가 적을수록 NL 조인에서 효율이 좋습니다.

조인 조건에 해당하는 컬럼들은 인덱스를 가지고 있어야 합니다.

Sort Merge Join

외부 테이블(Outer Table) 에서 조건에 만족하는 레코드를 검색한 후, 조인 키를 기준으로 정렬작업을 수행합니다.

내부 테이블(Inner Table) 에서 조건에 만족하는 레코드를 검색한 후, 조인 키를 기준으로 정렬작업을 수행합니다.

정렬된 결과를 이용하여 조인을 수행하며 조인에 성공하면 추출버퍼에 넣은 뒤 출력합니다.

조인 조건에 해당하는 컬럼에 인덱스가 없어도 가능합니다.

만약 두 테이블의 정렬 조건의 데이터 차이가 커서, 정렬의 시간차이가 커지는 경우 하나의 테이블은 다른 테이블의 정렬작업이 끝날때까지 대기하여야 하므로, 비 효율적일 수 있습니다.

또한 정렬 데이터가 많아, 메모리에서 수행이 불가능 한 경우 임시영역을 사용하게 되므로 성능이 떨어질 수 있습니다.

Hash Join

외부 테이블 전체를 읽어 결합 키를 기준으로 해시 테이블을 만들어 PGA ( GOLDILOCKS PSA ) 영역에 올린 후,

해시 테이블을 기반으로 내부 테이블을 검색하여 결합하는 방식입니다.

해시 테이블은 처음 생성하는 경우 시간이 걸리지만, 이후는 메모리 영역에서 수행되므로 매우 빨라집니다.

동등조건에서만 사용이 가능하며, 해시 테이블이 메모리에 적재할 수 있는 크기보다 커지면 디스크에 해시 테이블을 저장합니다.

조인 조건에 해당하는 컬럼에 인덱스가 없어도 가능합니다.

아쉽게도 GOLDILOCKS 는 메모리 데이터베이스 이다 보니 이부분에서는 많은 효과를 거두긴 힘들거 같네요.

결합 방법 힌트 주기

결합 방법을 강제로 지정하고 싶은 경우, 다음과 같이 쿼리에 강제 힌트를 줄 수 있습니다.

결합 명칭	힌트 방법
Nested Loops	SELECT /*+ USE_NL ( TABLE_NAME, TABLE_NAME2 ... ) */ COLUMN ..
Sort Merge	SELECT /*+ USE_MERGE ( TABLE_NAME, TABLE_NAME2 .. ) */ COLUMN ..
Hash Join	SELECT /*+ USE_HASH ( TABLE_NAME, TABGLE_NAME2 ... ) */ COLUMN ..