SQL 스터디 6주차!

JOIN과 성능

내부적으로 조인이 처리되는 방식

NESTED LOOPS JOIN
MERGE JOIN
HASH JOIN

1. NESTED LOOPS JOIN (NL JOIN)

중첩된 반복문 형태로 데이터를 연결하는 방식
선행 집합(선행 테이블 - 조인을 위해 먼저 접근하는 쪽, 바깥쪽 루프)과 후행 집합(후행 테이블 - 안쪽 루프)의 정의가 매우 중요하다.
관계형 데이터베이스에서 가장 많이 사용하는 내부적인 조인 처리 방식
NL 조인의 처리 과정을 정확히 이해하고, 필요한 부분에 인덱스를 정확히 만들어 준다면 가장 적은 비용으로 빠르게 조인 결과를 얻을 수 있다.
NL 조인 방식은 많은 양의 데이터를 조인하기에는 한계가 있다.

예시

SELECT /** GATHER_PLAN_STATISTICS LEADING(T1) USE_NL(T2) */
       T1.RGN_ID, T1.CUST_ID, T1.CUST_NM
      , T2.ORD_DT, T2.ORD_ST, T2.ORD_AMT
FROM   M_CUS T1
      , T_ORD T2
WHERE  T1.CUS_ID = T2.CUS_ID;

NESTED LOOPS 실행

고객 건수 만큼 주문을 반복 접근 함(주문 반복해서 FULL SCAN). 선행 집한만큼 후행 집합을 반복 접근한다.

후행 테이블의 조인 조건 컬럼에는 인덱스가 필수다.

후행 테이블의 조인 조건에 인덱스가 없을 경우, TABLE FULL SCAN이 일어난다. index를 넣어 INDEX RANGE SCAN을 타게 해야 효율적이다.
- WHERE 조건 컬럼을 복합 인덱스로 넣으면 더 효율적이다.
- 복합 인덱스 컬럼 중 조건에 일부 컬럼이 없을 경우, INDEX SKIP SCAN을 타게 되는데, INDEX SKIP SCAN 보다는 INDEX RANGE SCAN이 더 효율적이다.

선행 집합, 후행 집합 순서 변경에 따른 쿼리 변형

옵티마이저가 자동으로 SQL을 변형하는 기능(쿼리 변형)이 있다.
위의 복합인덱스(CUS_ID, ORD_YMD)가 걸린 후행 집합을 선행집합으로 변경할 경우, 조인 조건에 ORD_YMD만 있어도, CUS_ID 조건을 자동으로 추가해 효율적으로 동작한다.
쿼리 변형이 일어나는 경우는, SQL 변형이 실행 결과에 전혀 영향이 없고, 변형이 성능에 더 좋을 경우이다.

조인 횟수를 줄이자

NL조인 사용시에는 어떤 순서로 처리하는 것이 후행 집합의 접근 횟수를 줄일 수 있는지 고민해야 한다.
선-후행 순서를 바꾸기 전에, 조인에 참여하는 각 테이블들의 조건을 걸어 카운트 해보라. 데이터 갯수가 적은 테이블을 선행 집합으로 조인하는 것이 유리하다.
- 후행 집합의 접근 횟수를 줄이려면 선행 집합의 건수가 작아야 한다.
- 선행 집합을 바꾸고 Buffers가 줄어들어 성능이 개선되었는지 실행계획을 확인해보라.

과도한 성능 개선을 피하자

-인덱스를 생성한 후 얻는 성능 개선의 효과를 고민해보고 적절한 선에서 성능 개선을 해야 한다.

2. MERGE JOIN

두 데이터의 집합을 연결 조건 값으로 정렬한 후 조인을 처리하는 방식. 정렬된 데이터를 차례대로 읽어가며 조인을 수행한다.
연결 조건 기준으로 정렬되어 있어야만 조인이 가능하다 => Sort Merge Join, Sort Join으로도 불린다.
SORT JOIN: 자신의 자식 단계의 결과를 조인을 위해 정렬하는 작업
INDEX FULL SCAN: 인덱스 리프 블록을 처음부터 끝까지 차례대로 읽는 작업. 인덱스 리프 블록은 인덱스 키 값으로 정렬되어 있다.

예시

SELECT /** GATHER_PLAN_STATISTICS LEADING(T1) USE_MERGE(T2) */
       T1.RGN_ID, T1.CUST_ID, T1.CUST_NM
      , T2.ORD_DT, T2.ORD_ST, T2.ORD_AMT
FROM   M_CUS T1
      , T_ORD T2
WHERE  T1.CUS_ID = T2.CUS_ID;

MERGE JOIN(자식단계에는 SORT JOIN 일어남)

고객과 주문을 각각 정렬 후, 양쪽을 순차적으로 읽으면서 조인처리. 조인 컬럼인 CUS_ID로 고객과 주문을 정렬한다. 동일한 데이터를 반복 접근하지 않는다.

머지 조인은 소트 작업을 얼마나 어떻게 줄이느냐가 성능 향상의 주요 포인트다.

머지 조인에서 필요한 인덱스

머지 조인은 조인에 참여하는 데이터를 각각 조회해서 조인을 처리한다. 그러므로 조인에 참여한느 테이블별로 대상을 줄일 수 있는 조건에 인덱스를 만들어주면 된다.
해당 조건에 인덱스를 하용하는 것이 TABLE ACCESS FULL보다는 좋은 성능을 낼 수 있어야 한다.

3. HASH JOIN

해시 함수를 이용한 처리 방식. 대용량 데이터를 조인할 때 적합하다.
장점: 조인 성능 문제가 해시 조인으로 해결되는 경우가 많다.
단점: 다른 방식보다 더 많은 CPU와 메모리 자원을 사용한다. (자주 사용되는 핵심 SQL은 NL조인으로 처리되도록 해야 한다.)

예시

SELECT /** GATHER_PLAN_STATISTICS LEADING(T1) USE_HASH(T2) */
       T1.RGN_ID, T1.CUST_ID, T1.CUST_NM
      , T2.ORD_DT, T2.ORD_ST, T2.ORD_AMT
FROM   M_CUS T1
      , T_ORD T2
WHERE  T1.CUS_ID = T2.CUS_ID;

조인하려는 두 개의 테이블 중 고객 테이블을 선택해 읽어 들인다.
고객을 읽어 들이면서 조인 조건으로 사용된 컬럼 CUS_ID 값에 해시 함수를 적용한다.
해시 함수를 결과값에 따라 데이터를 분류해 해시영역에 올려놓는다.
주문 테이블을 읽어 들인다.
주문 테이블의 CUS_ID 값에 같은 해시 함수 처리를 한다.
해시 함수의 결과값에 따라 해시 영역에 있는 3번 결과와 조인을 수행한다.
4~6 과정을 반복 수행 하면서 조인 결과를 만들어 내보낸다.

NL조인처럼 후행 집합을 반복해서 접근하는 비효율이 없고, 머지 조인처럼 정렬작업을 수행하지 않는다. 해시 조인은 이 단점들을 커버한다.
단, 고 비용의 해시 함수와 메모리의 일부인 해시 영역을 사용하는 비용이 추가 투입되어, 시스템 자원을 소모한다.

빌드 입력 선택의 중요성

해시 조인의 경우 선행 집합은 Build-Input으로 처리하며, 후행집합은 검증입력Probe-Input으로 처리한다.
- 빌드 입력: 조인할 대상에 해시 함수를 적용해 조인 준비를 하는 과정
- 검증 입력: 후행 집합에 해시 함수를 적용해 빌드 입력과 비교해 조인을 처리하는 과정
빌드 입력의 데이터가 적으면 적을수록 성능에 유리하다. 빌드 입력의 데이터가 너무 많아 해시 영역에 모두 올릴 수 없으면 임시 공간을 사용하게 되어 성능 저하가 발생한다.

대량의 데이터에만 사용해야 하는가?

소량의 데이터는 무조건 NL조인으로만 처리해야 하는가? -> NO.
NL조인으로 처리했을 때와 해시 조인으로 처리했을 때 성능 차이가 클 수도 잇다. 특정 SQL이 매우 많이 사용되면서 CPU 점유 시간이 높다면, 해시 조인을 제거해야겠지만, 그런 상황이 아니라면 해시조인이 성능상 유리할 경우 굳이 제거할 필요가 없다.

reference

SQL BOOSTER

Oracle - NL조인, 머지조인, 해시조인

SQL BOOSTER를 읽고