[MSSQL] 중첩루프, 머지, 해시조인 3가지 설명

MSSQL에서 지원하는 조인 방식은 아래와 같이 3가지가 존재한다.

1.중첩루프조인(NESTED LOOP JOIN)

2.머지조인(SORT MERGE JOIN)

3.해시조인(HASH JOIN)

---

중첩루프조인(NESTED LOOP JOIN) 

- 순차적으로 처리된다.

- 작은 입력을 인덱스를 사용하여 조인할 때 가장 효율적 성능을 보인다.(OLTP 환경에 적합)

- 정렬이나 해시 테이블 생성과 같은 대기(STOP&GO)를 해야하는 추가적인 선행 작업이 필요 없기 때문에 추가 메모리를 사용하지 않는다. 따라서 메모리 사용량이 가장 적다

- 메모리를 사용하지 않는 점은 많은 동시 사용자를 처리하는 OLTP 환경에서 큰 장점으로 작용한다.

 

외부참조 테이블

- 통계를 기반으로 데이터가 작은 테이블을 외부테이블로 선택

- 조인횟수는 외부참조 테이블에 의해 결정되기 때문에 WHERE 조건으로 테이블 크기를 줄여야한다. 따라서 인덱스는 WHERE 조건을 기준으로 생성

 

내부참조 테이블

- 조인조건의 컬럼(ON) 기준으로 인덱스 생성 필요(내부참조 테이블에 인덱스가 없는 경우 외부 참조테이블에서 조인할 때마다 풀스캔이 발생하여 성능이 저하된다)

 

주의사항

- 인덱스가 테이블 선택의 영향을 주기 때문에 중첩루프 조인을 사용한다면 내측 테이블로 사용할 테이블에는 반드시 적당한 인덱스를 만들어야 원하는 실행 계획을 만들 수 있다

- 외부입력이 작고 내부 입력이 크면 더 효과적

 

 

---

머지조인(SORT MERGE JOIN)

중첩루프조인은 내부테이블의 조인조건에 대한 인덱스 존재 유무가 매우 중요하다.

만약 인덱스가 존재하지 않으면 조인 건 수마다 내부테이블을 풀스캔하기 때문이다.

이럴경우 옵티마이저는 머지조인 or 해시조인을 고려한다

 

머지조인 양쪽테이블을 조인 컬럼을 기준으로 정렬 후 -> 조인을 진행한다(전체 테이블을 한번만)

정렬 작업은 메모리를 사용하게되고 만약 메모리가 부족하다면 느린 디스크까지 사용하게된다

 

따라서 대용량 테이블로 갈수록 정렬 작업은 부담이 되기 때문에  조인컬럼에 인덱스를 생성하여 정렬작업을 패스하고 바로 조인을 진행하게 하면 좋은 성능을 낼 수 있다.

단 클러스터인덱스가 아닌 넌클러스터 인덱스를 사용할 경우 Lookup비용(커버드 인덱스 사용으로 제거가능)을 고려해야한다

 

인덱스 선정기준

1.조인 컬럼에 인덱스 생성

큰 테이블을 정렬하는 작업의 부하가 큰 경우 적합
대용량 테이블의 정렬 작업은 메모리가 많이 할당되기 때문에 메모리가 부족하여 디스크를 추가로 사용할 경우 성능저하가 발생하니 주의

2.엑세스 컬럼에 인덱스 생성

각 테이블이 스스로 테이블에 있는 인덱스를 사용해서 조인에 참여할 데이터의 크기를 줄여줄 수 있어서 정렬을 하는 부하가 작을 경우 적합
비록 정렬을 하는 작업이 필요하지만 메모리에서 충분히 정렬 할 수 있다면 성능 저하 없이 조인가능

주의사항

- 두 집합을 각각 정렬한 후에 조인함으로 각 집합의 크기, 즉 테이블별 검색 조건(where)에 의해 전체 비용이 좌우된다
- 최소한 한 개의 = 조건이 있어야 한다(단 FULL OUTER JOIN의 경우 = 조건 없이 머지조인 가능)

 

---

해시조인(HASH JOIN)

해시조인은 인덱스를 사용할 수 없는 환경에서 결과를 정렬할 필요가 없을 때 효율적으로 사용할 수 있는 조인방법이다.
만약 결과를 정렬할 필요가 있다면 머지조인이 더 효율적일 수 있다.
조인 후 결과가 아주 작아 메모리에서 정렬이 가능하면 해시 조인을 한 후에 정렬을 해도 문제가 되지 않는다.

 

빌드입력 테이블

- 조인 테이블 중에 통계를 기반으로 데이터가 작은 테이블을 빌드입력으로 선정함(해시 테이블 생성시 마다 메모리에 올리는 작업이 진행되기 때문에 비용감소목적)
- 빌드입력의 조인컬럼에 해시함수를 적용해서 해시키(인덱스 열할)를 생성 후 -> 해시 테이블에 저장

프로브입력 테이블

- 해시 테이블 생성이 완료되면 프로브 입력으로 선정된 테이블의 조인조건에 같은 방법으로 해시함수를 적용하여 해시키(인덱스 역할)를 생성
- 이후 해시 테이블에서 같은 해시키(인덱스 역할)를 찾아서 조인을 진행

 

주의사항

해시 테이블을 생성하는 중 메모리가 부족하면 유예해시조인으로 진행되며 추가로 디스크를 사용하게 되고 느린 디스크는 전체적인 성능 저하로 이어집니다.
조건자가 = 일 경우에만 사용이가능하다

---

예측가능한 성능 구현을 위한 방법

1. 통계 히스토그램을 보지 못하게 내부 바인드 변수로 한번 더 받아서 실행
2. 힌트 사용 (OPTIMIZE FOR UNKNOWN: 특정 값에 따라 행수가 달라질 경우 평균 밀도를 특정해서 인덱스 Scan or Seek 중에 고정시킴)
3. 실행 마다 매번 쿼리 리컴파일 진행(OLTP환경에서는 성능 이슈로 추천하지 않음)
4. 2개의 다른 프로시저로 운영 (if a=1 exec P_test_01 else exec P_test02 )