[MSSQL] Merge join에 대한 고찰

안녕하세요 

마푸입니다. 

개인적으로 저는 DATABASE를 배우다보면 제일 재미있고 흥미로운게 집합이라고 생각합니다. 

집합 그 중에서 가장 핵심적인 부분이 JOIN이지용 

각 집합에 대해서 이리저리 붙여보고 돌려보고 덧대어 볼 수 있기 때문에 재미 지다는 표현이 더 좋을 지 모르겠네요

사실 어렵긴합니다. INNER JOIN이니 OUTER JOIN이니 그런 것들... 

위에 언급한 두가지는 대부분의 사람들은 바로 이해가 안되지만 두어번 읽어보면 감이 오긴합니다. 

자세히는 모르지만 말이죠 ^^;;

저역시 그래왔고 가끔씩 헛깔릴 때가 많지요

문제는 그 다음에 등장하는 녀석 때문입니다. Merge Join이라는 녀석인데요.

마치 프로그램 랭귀지에서 포인터 역할을 하는 녀석이지요 ㅋ;

어렵지는 않으나 이해해야하는 기능인것에 대해서는 변함이 없습니다. 

그래서 어려운거 부터 짚고 넘어 가기로 하겟습니다. 

아래는 merge 조인에 대한 정리 입니다. 

--

-- 머지 조인 (Merge Join) 정리 

--  A. 머지조인의 동작원리

--  B. 머지조인에 사용되는 메모리 체크하기

--  C. 머지조인의 정렬 회피하기

-- 

--  A. 머지조인의 동작원리

조인 조건 중 '=' 조건이 반드시 있어야한다. (예외 : FULL OUTER JOIN의 경우 예외)

조인 대상의 인덱스가 없거나 인덱스가 너무 커서 사용하는 비용이 크다면, 테이블을 읽는것이 효과적이다. 

NL 조인은 테이블을 반복해서 읽기 때문에 이 대상을 정렬을 통해서 비교하면 1회 읽기 만으로 결과를 도출 할 수 있다. 

이를 머지 조인이라 한다. 

1. T1과 T2 테이블을 머지 조인한다. 

2. T1의 조인에 참여할 집합과 T2의 조인에 참여할 집합을 '조인조건'으로 정렬한다. 

3. 정렬된 T1의 첫번째 값을 가져와서 정렬된 T2입력의 첫번째 값부터 비교한다. 

4. T1과 T2의 비교 결과에 따라서 아래와 같이 분기한다. 

     a. T1이 큰경우 : T2 집합의 다음 값으로 비교한다. 

     b. T2가 큰경우 : T1집합의 다음 값으로 비교한다. 

     (* 집합이 asc로 정렬되어있다고 가정할 때, 상위값이 작다면 하위 값으로 비교하면 보다 큰값과 비교 할 수 있다.)

5. 결과를 만족할때까지 4번과정을 반복한다. 

(* 결과적으로 머지조인은 두 집합을 한번씩만 비교하면 결과를 도출 할 수 있다.)

예외사항 

     A. T1이 유니크가 아닐때는 임시버퍼를 이용해서 두 테이블의 값을 비교합니다. (다:다)

     (* SQL Server는 Column에 제약조건(Unique Key)를 지정하지 않으면 기본적으로 M으로 인식핮다. 1:M의 관계를 지정하고 싶다면 Pk나 Unique를 지정하도록 하자)

Unique 제약을 지정했을때를 (Unique o) 이라고 표기한다면 아래의 표와 같다. 

논리적으로 유니크한 1인 관계가 T1의 위치에 올때 T2 테이블이 M이면 임시테이블을 생성해서 중복여부를 비교합니다. 

이때 두 테이블의 위치를 바꾸면 첫번째 테이블이 M이고 두번재 테이블의 1(Unique x)가 되기 때문에 임시테이블을 할당하지만 실제로 사용하지 않습니다. 

이는 성능적으로 이점이 있어서 보통 논리적으로 유니크한 테이블과 M관계의 테이블이 Merge 조인을 한다면 선행관계를 바꾸면 성능이 향상됩니다. 

이러한 관계를 잘 이해해서 직접 지정해 준다면 쿼리의 성능을 높일 수 있습니다. 

* T1, T2를 지정하는 방법 : INNER JOIN에 직접 명시한다.

SELECT T1.*, '#####', T2.*
FROM T1 INNER MERGE JOIN T2 on T1.ColA = T2. ColB
 

또한, M:M관계에서 선행테이블을 GROUP BY 하게되면 내부적으로 Unique로 변경되기 때문에 M:M관계보다 성능이 향상되게 됩니다. 하지만 이러한 경우 

테이블의 크기에 따라서 GROUP BY에 사용되는 비용이 더 클경우 효율이 나빠질 수 있습니다. 

* M:M 관계에서 Unique 테이블 만들기 : GROUP BY를 통해서 행을 내부적으로 Unique하게 만든다. 

SELECT T2.*
FROM (SELECT ColA FROM T1 GROUP BY ColA) AS G1
    inner MERGE JOIN T2 on G1.ColA =T2. ColB

--  B. 머지조인에 사용되는 메모리 체크하기

머지 조인은 기본적으로 정렬을 진행하고 데이터를 비교 하기 때문에 정렬 작업에서 메모리를 사용하게 됩니다. 

1. DMV를 이용해서 메모리 사용량을 확인합니다. (머지조인을 실행하는 세션아이디를 확인 후 진행합니다.)

SELECT granted_memory_kb,               -- 할당된 총 메모리양
       used_memory_kb ,                 -- 조회 시점에 사용한 메모리양
       max_used_memory_kb                -- 조회 시점에 사용한 최대 메모리 양
FROM sys .dm_exec_query_memory_grants
WHERE Session_id= 56