DB - EXPLAIN 실행계획 조인 방식에 대해

1. Nested Loop Join (NLJ, 중첩 루프 조인)

동작방식: 외부 테이블의 각 행마다 내부 테이블을 검색

특징 :

 1. 작은 테이블 vs 큰 테이블일 때 자주 사용

 2. 인덱스가 걸려있으면 빠름

 3. 인덱스가 없으면 내부 테이블 풀스캔 -> 성능 저하

EXPLAIN 에서 표시

 type=ref, type=eq_ref, type=ALL (조건에 따라)

 

2. Hash Join

동작방식 : 한쪽 테이블을 해시테이블에 올려두고 다른 테이블과 매칭

특징 :

 1. 대량 데이터 조인시 효과적

 2. 인덱스 없어도 잘 동작.

 3. PostgreSQL, Oracle에서 많이 사용. Mysql은 8.0 부터 지원

EXPLAIN에서 표시

 Mysql 8.0+: hash join 표기됨

 

3. Merge Join (Sort-Merge Join)

동작방식 : 두 테이블을 각각 정렬 후 머지(merge)하면서 매칭.

특징 :

 1. 정렬 비용 있음.

 2. 이미 정렬된 상태(인덱스 순서)라면 효율적

EXPLAIN에서 표시

 PostgreSql/oracle에서 주로 확인가능, Mysql에서는 거의 사용안함

 

4. Block Nested Loop Join (BNLJ)

동작방식: 외부테이블을 블록 단위로 읽어 내부 테이블과 비교.

특징 : 

 1. 인덱스가 없어도 사용할수있음

 2. 메모리 사용량 증가

 Mysql Explain

   type=ALL + Extra에 Using join buffer (Block Nested Loop)가 표시됨

 

5. Index Nested Loop Join (INLJ)

동작방식: Nested Loop Join인데 내부 테이블 탐색 시 인덱스를 활용

특징 : 

 1. 인덱스 있으면 성능 좋음

 2. 조인 조건이 인덱스를 잘 타는 경우에만 사용

EXPLAIN

 ref or eq_ref 유형

 

6. Semi Join / Anti Join

동작방식 : EXISTS, IN, NOT EXISTS, NOT IN 같은 서브쿼리 최적화 시 등장.

특징 : 

  1. 조건을 만족하는 첫 행만 찾으면 된다는 경우 Semi Join 사용

  2. 조건을 만족하지 않는 경우 찾는다면 Anti Join 사용

Mysql EXPLAIN

  DEPENDENT SUBQUERY 최적화 시 보일수 있음

 

요약 : 

 작은테이블 + 인덱스 있음 -> Nested Loop Join

 큰테이블 + 인덱스 없음 -> Hash Join

 이미 정렬된 상태 -> Merge Join

 인덱스 없고 큰 테이블 -> Block Nested Loop Join