기능적 관점으로 구분하는 결합

inner join

같은 기능을 하는 상관 서브쿼리

이때 SELECT 구문에 서브쿼리를 넣어도 괜찮을까? 라고 생각할 수 있지만,

dept_id는 부서 테이블의 PK이므로, 레코드가 한 개로 한정되어 스칼라 서브쿼리로 사용할 수 있다.

SELECT E.emp_id, E.emp_name, E.dept_id,
       (SELECT D.dept_name
          FROM Departments D
         WHERE E.dept_id = D.dept_id) AS dept_name
FROM Employees E;

상관 서브쿼리 vs inner join

상관 서브 쿼리를 스칼라 서브쿼리로 사용하면, 결과 레코드 수만큼 상관 서브쿼리를 실행하기 때문에 실행 비용이 너무 높다

self join

self join은 outer join, inner join 과는 약간 다른 분류이다.

생성되는 결과를 기준으로 분류하는 것이 아닌, 연산에 대상으로 무엇을 사용하는 지에 따른 분류이다.

따라서 self join을 하기 위한 방식으로는 self + cross, self + outer 가 있다.

self join + cross join

digit
0
1
2
3
4

SELECT D1.digit + (D2.digit * 10) AS seq
FROM Digits D1 CROSS JOIN Digits D2;

0
1
2
3
4

사실 물리 레벨에서 보면 같은 테이블과 결합하지만, 논리적으론 서로 다른 두 개의 테이블을 결합한다고 생각해도 된다.

결합 알고리즘 & 성능

• Nested Loops
• Hash
• Sort Merge

MySQL은 Nested Loops와 그 파생 버전만 지원, Hash 또는 Sort Merge 사용 X

DBMS 버전이 오르면 알고리즘이 바뀌는 경우가 있으므로, DBMS의 최신 동향에 유의하자.

Nested Loops

• 결합 대상 테이블(A)에서 레코드를 하나씩 반복해가며 스캔. A는 구동 테이블 또는 외부 테이블이라고 부른다. B는 내부 테이블
• 외부 테이블(A)의 레코드 하나마다 내부 테이블의 레코드를 하나씩 스캔해서 결합 조건에 맞으면 리턴
• 이러한 작동을 구동 테이블의 모든 레코드에 반복

특징

• 테이블 A와 B가 있다고 했을 때, 접근되는 레코드 수가 R(A) x R(B)가 된다. 따라서, 실행 시간은 레코드 수에 비례
• 하나의 단계에서 레코드 수가 적기 때문에 다른 방식에 비해 메모리 소비가 적다
• 모든 DBMS에서 지원

Nested Loops를 개선할 수 있는 방법?

‘구동 테이블(외부 테이블)’로는 작은 테이블을 선택하자’

❗️내부 테이블의 결합 키 필드에 인덱스가 존재한다면❗️

위 ‼️조건을 만족하지 못한다면, 구동 테이블의 크기가 작아도소용이 없다.

이렇게 내부 테이블(B)에 인덱스가 존재한다면, 모든 레코드를 탐색하지 않아도 된다.

Nested Loops의 단점

위의 개선 방법으로 개선한다 해도, 내부 테이블의 결합 키가 유일한 값이 아니라면 결국 히트되는 레코드가 많기 때문에 많은 반복이 발생 → 지연 발생

예를 들어, 점포 테이블과 주문 테이블이 있다고 가정하자. 점포 하나당 여러 개의 주문 데이터가 있을테니 크기는 점포 테이블이 작다.

점포 테이블을 구동 테이블(외부 테이블)로 선택 (테이블의 크기가 더 작기 때문)

• 점포와 주문은 1 : N 관계이기 때문에, 수백, 수천만 건의 주문 레코드가 히트된다.
• 결국 점포당 주문 데이터에 따라 성능이 달라진다.

대처하는 방법

• 구동 테이블로 큰 테이블을 선택(역설적) → 항상 하나의 레코드가 히트되기 때문에 같은 성능 유지
• 해시 사용

Hash

• 작은 테이블을 먼저 스캔해, 결합 키에 해시 함수를 적용해서 해시값으로 변환 → 해시테이블 생성
• 그 후 다른 테이블(큰 테이블)을 스캔해, 결합 키가 해시 값에 존재하는 지 확인

왜 작은 테이블부터 스캔할까?
→ 작은 테이블의 결합 키를 이용해 해시테이블을 만들면 그만큼 작은 해시 테이블을 만들 수 있어, 워킹 메모리를 그나마 조금 사용

특징

• 결합 테이블로부터 해시 테이블 생성 → Nested Loops보다 많은 메모리 사용
• 메모리가 부족하면 임시 저장소를 사용 → 지연 발생
• 출력되는 해시값은 입력값의 순서 알지 X → 등차 결합에만 사용

Hash가 유용한 경우 (Nested Loops의 차선책)

• Nested Loops에서 적절한 구동 테이블 존재하지 않는 경우
• Nested Loops에서 내부 테이블에서 히트되는 레코드 수가 많은 경우
• Nested Loops에서 내부 테이블에 인덱스가 존재하지 않는 경우

Trade Off 사항

• 동시 실행성이 높은 OLTP 처리할 때, 저장소가 사용된다면 지연이 발생할 리스크 존재 → 따라서 OLTP 처리할 때 사용 X → BI/DWH 같은 시스템은 Hash 사용
  
• 양쪽 테이블을 스캔하다보니, 풀 스캔이 사용되는데 풀 스캔에 걸리는 시간도 고려

용어 정리

• OLTP(Online Transactional Processing): 온라인 뱅킹, 쇼핑, 주문 입력 등 동시에 발생하는 다수의 트랜잭션을 실행하는 데이터 처리 유형
• BI(Business Intelligence):원자료를 이용할 수 있는 정보로 변환, 비즈니스 의사결정과 기술을 지원
• DWH(Data WareHouse): 데이터 웨어하우스

Sort Merge

Nested Loops가 비효율적일 때, 또 다른 선택지

결합 대상 테이블을 결합 키로 정렬 후, 매칭시키는 방법

특징

• 정렬해야하므로, Nested Loops보다 많은 메모리 소모, TEMP 탈락이 발생하면 I/O 비용 증가 및 지연 발생 위험
• 동치 결합 포함 부등호를 사용한 결합에도 사용할 수 있다
• 결합 키로 정렬되어 있다면, 정렬 생략 가능 → SQL에서 레코드의 물리적인 위치를 알고 있을 때만 (구현의존적)

Sort Merge가 유효한 경우

테이블 정렬에 많은 시간과 리소스를 요구하는 방법이기 때문에,

테이블 정렬을 생략할 수 있는 경우에만 고려하자

의도하지 않은 cross join

크로스 조인을 실무에서 사용할 일은 거의 없지만, 의도치 않게 발생하는 경우가 있다.

SELECT A.col_a, B.col_b, C.col_c
　FROM Table_A A
         INNER JOIN Table_B B
            ON A.col_a = B.col_b
         INNER JOIN Table_C C
            ON A.col_a = C.col_c;

결합 조건은 A와 B, A와 C에만 존재

Nested Loops 경우

• Table_A를 구동 테이블로, Table_B와 결합
• 그 결과를 Table_C와 결합

Cross join 경우

• Table_B와 Table_C를 먼저 결합
• 그 결과를 Table_A와 결합

→ 이때, Table_B와 Table_C는 결합 조건이 없으므로, 크로스 조인을 할 수 밖에 없다.

Table_B와 Table_C의 크기가 충분히 작았다면, 먼저 결합 후, 다시 결합하는 게 합리적인 선택일 수도 있다.

특이한 경우가 아니다?

작은 마스터 테이블을 결합할 때 자주 나타나는 일

하지만 비교적 큰 테이블끼리 결합할 때나 검색 조건으로 히트되는 레코드 수가 변할 때 문제가 발생할 수 있다.

레코드 수를 꽤 압축할 수 있는 입력이 들어왔을 때, 크로스 결합으로 충분하겠다 판단한다. 하지만 이 정보를 바탕으로 이후에도 압축할 수 없는 입력임에도 같은 선택을 할 수 있으니 조심

의도치 않은 cross join 피하기

불필요한 결합 조건 추가해주기

SELECT A.col_a, B.col_b, C.col_c
FROM Table_A A
         INNER JOIN Table_B B
            ON A.col_a = B.col_b
         INNER JOIN Table_C C
            ON A.col_a = C.col_c
         AND C.col_c = B.col_b;

join 성능 최적화

join 알고리즘

이름	장점	단점
Nested Loops	- 작은 구동 테이블 + 내부 테이블의 인덱스 ⇒ 굉장히 빠르다

• 메모리, 디스크 소모 작음 ⇒ OLTP에 적합
• 비동기 join에서도 사용 가능 | - 대규모 테이블 join에 부적합
• 내부 테이블 인덱스 사용 X, 또는 내부 테이블의 선택률이 높으면 느리다 |
  | Hash | - 대규모 테이블 join할 때 적합 | - 메모리 소비량이 큰 OLTP에 부적합
• 메모리 부족 발생 시 TEMP 탈락
• 동기 join에서만 사용 가능 |
  | Sort Merge | - 대규모 테이블 join할 때 적합
• 비동기 join에서도 사용 가능 | - 메모리 소비량이 큰 OLTP에 부적합
• 메모리 부족 발생 시 TEMP 탈락
• 데이터 정렬 안되어 있으면 비효율적 |

장단점에 맞춰 선택하자

• 소규모 - 소규모
  • 결합 대상 테이블이 작으면, 성능 차이 작음
• 소규모 - 대규모
  • Nested Loops 사용. Nested Loops의 특징을 못 살린다면, Hash 사용
• 대규모 - 대규모
  • 일단 Hash 사용, 정렬되어 있는 상태라면 Sort Merge 사용

실행 계획 제어

사용자가 SQL 구문에 직접 힌트를 작성해 옵티마이저 조종하기

실행 계획을 직접 조종할 시에 리스크

데이터의 양과 카디널리티는 DB를 운영하면서 바뀌기 떄문에, 항상 지금의 실행 계획이 효율적이라고는 장담 못한다.

따라서 시간에 따라 어떻게 데이터가 변할 지 유추하고, 테스트 해봐야 한다.

흔들리는 실행 계획

시간에 따라 효율적인 실행 계획 변동이 발생한다면, 골치아프다..

따라서 SQL 성능 변동 위험을 줄이려면 join을 피해야 한다!