JOIN의 방식

JOIN연산이란?

 

 - SQL 명령문에 의해서 여러 테이블에 저장된 데이터를 한번에 조회할 수 있게 하는 DBMS의 기능

 

 - 두 집합 간의 곱으로 데이터를 연결하는 가장 대표적인 데이터 연결법

 

---

JOIN의 종류(5가지)

 

1. INNER JOIN

 

2. OUTER JOIN

 

3. CROSS JOIN

 

4. FULL OUTER JOIN

 

5. SELF JOIN 

 

JOIN의 방식(3가지)

 

1. Nested Loop JOIN - 중첩반복

 

2. Merge JOIN - 정렬병합

 

3. Hash JOIN - 해시매치

 

JOIN의 종류 -> 논리적 JOIN

 

JOIN의 방식 -> 물리적 JOIN

 

---

물리적 JOIN

 

1. Nested Loops JOIN (중첩 반복 조인)

 

 - 바깥 테이블의 처리 범위를 하나씩 엑세스하면서 그 추출된 값으로 안쪽 테이블을 JOIN하는 방식

 

   1) 순차적으로 처리된다.

 

   2) 바깥 테이블과 일치하는 값을 안쪽 테이블에서 찾아야 하므로 안쪽 테이블의 해당 열에 인덱스가 필요

 

   3) 메모리 사용량은 가장 적다

 

   4) 바깥 테이블과 안쪽 테이블의 크기는 성능과 관련이 없다.

 

* 작동 원리

 

  * 두개의 테이블을 조인할 때, 어떤게 Outer 테이블이고 어떤게 Inner 테이블?

* 원리

 

 - 순차적인 진행

 

 첫 테이블 필터링 -> 두 테이블간 JOIN - > 최종운반 단위 산출까지 반복적, 순차적으로 진행

 

- 선행적

 

  1) 선행 테이블의 처리 범위가 전체 일의 양을 결정

 

  2) 후행 테이블의 필터링 조건은 선행 테이블에서 나온 결과를 한번 더 걸러주는 체크 조건 역할을 할 뿐

  

     전체 처리량을 좌우하지 않음

 

 - 종속적

 

  1) 후행 테이블은 선행 테이블의 결과 값을 받아 처리된다.

 

  2) 선행 테이블의 결과에 종속적이다.

 

  3) 선행테이블을 후행 테이블의 전체 일의 양을 줄여줄 수 있는 필터링 조건으로는 사용 할 수 없음

      (체크 조건으로만 사용 가능) 

 

 - Random Access

 

 1) 선행 테이블의 결과를 통해 후행 테이블을 할때 대량의 랜덤 I/O가 발생

 

 2) 선행 테이블은 최초 ROW만 엑세스가 발생하고 이후에는 스캔방식으로 진행

 

장점

 

1. 처리량이 적다.

 

   - 랜덤 I/O 때문에 선행 테이블의 카디널리티를 획기적으로 줄일 수 있다면 메모리를 가장 적게 사용하는

 

     좋은 조인 방식이 된다.

 

2. 부분 범위 처리

 

   - 다른 조인 방법은 부분 범위 처리가 원천적으로 불가능하다.

 

3. 처리의 방향성이 필요하다.

  - 다른 테이블의 처리 결과를 받아야만 처리 범위를 확 줄여줄 수 있을 때 사용하면 좋다.

 

 단점

 

  두 테이블을 연결할 때의 랜덤 I/O가 가장 큰 부담

 

**구체적인 실행순서 

두가지 가정

 

 - 옵티마이저가 통계 데이터를 보고 TAB1을 선행 테이블로 선택

 

 - A.KEY, B.KEY에만 인덱스가 잡혀있다.

 

1) 인덱스 페이지에서 A.KEY로 111를 찾는다.

   

    이때, 인덱스 페이지라 KEY컬럼으로 정렬되어 있다면 스캔 방식으로 인덱스 페이지를 읽고

 

    인덱스가 없다면 스캔 방식으로 한번에 읽는 것이 유리하기 때문에 정렬을 한다.

 

2) 인덱스 페이지에서 찾은 결과로 만약 A.KEY가 넌클러스터드 인덱스라면 RID를 통해 TAB1의 데이터 페이지를 

 

   접근하고 클러스터드 인덱스라면 리프페이지가 데이터 페이지기 때문에 별도의 페이지 접근이 없다.

 

   여기서 COL1 LIKE '222%'조건으로 한번 더 필터링 된다.

 

3) TAB1의 결과를 A.KEY = B.KEY연결고리를 통해 INDEX2를 랜덤 엑세스한다.

 

  즉, B.KEY = '111'로 랜덤 엑세스를 한다.

 

  반복한다.

 

---

2. Sort Merge JOIN(정렬 병합 조인) 

 

 1) 양쪽 테이블의 처리 범위를 각자 엑세스하여 정렬한 결과를 차례로 스캔하며, 연결고리 조건으로 Merge하는 방식

 

 2) 동시적 처리

 

    - 각 키에 의해 정렬된 양쪽 행들을 순차적으로 병합하여 조인을 수행

 

 3) 인덱스 필요

  

    - 양 테이블이 모두 조인키에 의해 정렬되어 있어야 함.

 

  4) 전체 범위 처리

  

    - 선행 테이블, 후행 테이블의 크기는 성능과 관련이 없음

 

    - 그러나, 선행 테이블에 중복행이 존재하지 않을때 메모리 사용량이 적어 권장

 

    - 부분 범위처리 불가 -> 항상 전체 범위처리

 

  5) 스캔 방식

 

    - 주로 스캔방식 이용

 

    - 자신의 처리범위를 줄이기 위해 인덱스를 사용하는 경우만 랜덤 엑세스, 나머지 작업은 스캔 방식

 

 6) 독립적

 

  - 중첩 반복조인은 선행 테이블의 처리 결과가 후행 테이블에 영향을 미치지만 정렬병합 조인에서 처리범위를

 

    줄일 수 있는 유일한 수단은 각자가 가지고 있는 필터링 조건이다.

 

 7) 체크 조건의 의미

 

  - 중첩반복 조인은 후행 테이블의 필터링 조건은 단지 선행 테이블에서 처리된 내용을 최종 운반 단위로 보낼 때 

 

    그 양을 줄여주는 역할만 할 뿐 전체 처리량을 줄이지는 못한다.

 

    즉, 선행 테이블의 결과 ROW수가 10건이였다면, 이 10건을 울반단위로 보내기 전에 후행 테이블의 필터링

 

    조건을 확인하여 만족하는지 체크만 할 뿐이다.

 

    모두가 만족하면 10건이 보내지고 아닌 ROW는 제외된다.

 

    근데, Sort Merge의 경우 인덱스를 사용하지 않는 단순 체크조건이라도 Merge할 범위를 줄여주기 때문에 

 

    상당한 의미가 있다.

 

장점

 

 - 처리량이 많을때 성능상 이점 존재

 

 - Nested Lopp(중첩반복)은 연결고리의 상태가 굉장히 중요

 

   한쪽 연결고리에 이상이 발생하면 중첩반복은 심히 고려해야하는데 Sort Merge(정렬병합)은 연결고리에

 

   영향을 받지 않는다.

 

- 또한, 중첩반복 조인은 선행 테이블의 처리가 늦어지면 후행 테이블은 한 없이 대기해야 한다는 단점.

 

단점

 

  - 정렬에 따른 부담(메모리 사용 증가)

    

    -> 정렬은 tempdb를 사용하는데 정렬량이 극도로 많아 임계치를 넘으면 각가지 오류가 발생하는데

 

         가공없이 Clustered index를 그대로 사용하게 되면 정렬은 따로 하지 않아도 되서 부담 없음

 

** 구체적인 수행 순서

가정

 

 - A.KEY, B.KEY에만 인덱스가 있다.

 

 1) INDEX1에서 A.KEY가 111인것들을 찾은 후 A.COL1 LIKE '222%' 조건으로 최종 필터링된 결과를

 

    연결고리인 A.KEY 값으로 정렬한다.

 

 2) TABLE2은 인덱스를 사용 할 수 없으므로(B.KEY로 검색하는게 X) TABLE2을 테이블 스캔하여

 

     B.COL2 = '333'의 결과를 필터링해서 B.KEY값으로 정렬한다.

 

  ----> 여기서 1,2번은 순서대로 일어나는게 아니라 동시에 일어난다.

 

 3) 두 개의 정렬도니 결과를 가지고 A.KEY = B.KEY를 만족하는 결과를 병합(Merge) 한다.

 

 

* 두 조인의 단점

 

- Nested Loops : 랜덤 엑세스

 

- Sort Merge : 정렬(메모리 사용 증가)

 

 ==> Hash Match 조인은 왜 나왔을까?

 

---

3. Hash Match 조인

 - 조인할 테이블에 대해서 해시 버킷 생성 (빌드 입력) -> 해시 버킷의 순서대로 결과 출력

 

    따라서, 테이블의 인덱스는 사용되지 않고, 인덱스가 없거나 임의성 쿼리에 탁월한 성능을 발휘

 

   * 적은 행에 대해 인덱스가 있는 테이블에 대해서는 중첩루프 조인이 사용될 가능성이 높음

 

     하지만, 용량이 커지고 행수가 많아지면 중첩 루프 조인은 행의 수만큼 테이블 스캔이 발생하므로 느린 방법

 

     이때 해시 조인 발생

 

  - 연결고리

    

      -> 각 테이블의 연결고리의 인덱스는 사용하지 않음

  

          대신, 테이블에 대한 해시 버킷을 생성하며 그것을 통해 조인

 

          조인완료시 삭제, --> 반복적으로 명령을 수행할 경우 생성 -> 삭제 반복

 

   - 조인 결과

 

      -> 조인 결과는 정렬하지 않은 상태로 출력

 

            (정렬을 원하면 ORDER BY절 사용)

 

   - 해시 버킷을 만들기 때문에 많은 메모리 사용

 

   - 소량과 대용량 테이블을 조인할 때 사용하면 좋음