[SQL 레벨업] 6장: 결합

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

등가 결합: 결합 조건으로 등호(=)를 사용하는 경우

비등가 결합: 결합 조건으로 부등호(>=, < 등)를 사용하는 경우

자연 결합: 내부 결합이면서 등가 결합

 

- 자연 결합 예시

SELECT * 
FROM Employees NATURAL JOIN Departments;

내부 결합+ 등가 결합 쿼리와 같은 결과 도출

SELECT * FROM Employees E INNER JOIN Departments D
ON E.dept_id=D.dept_id;

USING 구를 사용한 경우

SELECT * FROM Employees INNER JOIN Departments
USING(dept_id);

1. 크로스 결합

SELECT * FROM Employees
CROSS JOIN Departments;

2개의 테이블의 레코드에서 가능한 모든 조합을 구하는 연산

(사원 테이블 레코드) * (부서 테이블 레코드) 만큼의 레코드 결과 생성

실무에서 사용되지 않는 이유

- 이러한 결과가 필요한 경우가 없음

- 비용이 많이 드는 연산임

실수로 사용한 크로스 결합

SELECT * FROM Employees, Departments;

- 결합 조건을 적지 않으면 크로스 결합이 됨

- 표준 SQL에 맞게 결합 구문을 사용하면 실수를 방지할 수 있음

2. 내부 결합

SELECT E.emp_id, E.emp_name, E.dept_id, D.dept_name
FROM Employees E INNER JOIN Departments D
     ON E.dept_id=D.dept_id;

내부 결합의 결과는 크로스 결합의 부분 집합임

내부 결합과 같은 기능을 하는 상관 서브쿼리

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;

- dept_id는 부서 테이블의 기본 키이므로 레코드가 반드시 하나일 것임

- 기본 키를 사용하면 상관 서브쿼리를 스칼라 서브쿼리로 사용 가능

- 상관 서브쿼리보다는 결합을 사용하는 것이 실행 비용이 적음

3. 외부 결합

종류

- 왼쪽 외부 결합: 마스터가 되는 테이블을 왼쪽에 적은 것

- 오른쪽 외부 결합: 마스터가 되는 테이블을 오른쪽에 적은 것

- 완전 외부 결합

 

마스터 테이블쪽에만 존재하는 키가 있을 경우 제거하지 않고 결과로 보존

키를 모두 가진 레이아웃의 리포트를 만들 때 자주 사용

4. 외부 결합과 내부 결합의 차이

외부 결합 결과는 마스터 테이블의 정보를 모두 보존하고자 NULL값이 든 레코드를 생성

따라서 외부 결합은 크로스 결합의 결과에 없는 레코드를 생성

5. 자기 결합

- 연산의 대상으로 무엇을 사용하는지에 대한 분류임, 생성되는 결과 기준으로 분류하는 것 아님

- 같은 테이블에 별칭을 붙여 다른 테이블인 것처럼 다룸

- (자기 결합) + (크로스/외부/내부 결합) 의 조합으로 사용 가능

digit
0
1
2
3
4
5
6
7
8
9

 

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

0~99까지의 결과가 반환됨

 

결합 알고리즘과 성능

Nested Loops

결합 알고리즘의 기본

중첩 반복을 사용

실행 시간은 레코드 수에 비례함

한 번의 단계에서 처리하는 레코드 수가 적으므로 Hash, Sort Merge에 비해 메모리 소비가 적음

모든 DBMS에서 지원

 

구동 테이블의 중요성

내부 테이블의 결합 키 필드에 인덱스가 존재한다면 내부 테이블을 완전히 순환하지 않고 건너뛸 수 있음

-> 구동 테이블을 작은 테이블로 선택하면 내부 테이블에 결합 키의 인덱스로 접근하여 실행 시간이 줄어들 수 있음

이상적인 경우의 예시

- 구동 테이블의 레코드 한 개에 내부 테이블의 레코드 한 개가 대응하면서 내부 테이블 인덱스를 사용해 찾을 수 있는 경우

- 접근하는 레코드 수: R(A) * 2 개

 

내부 테이블의 반복을 완전하게 생략하는 경우

결합키가 내부 테이블에 대해 유일한 경우

등치 결합이라면 내부 테이블의 접근 대상 레코드를 한 개로 한정 가능하므로 이중 반복의 내측에 있는 반복을 완전하게 생략

ex) 오라클의 INDEX UNIQUE SCAN

 

만약 결합 키가 내부 테이블에 대해 유일하지 않은 경우 인덱스로 내부 테이블에 접근하는 경우라도 여러 개의 레코드가 히트되어 반복을 적용해야 할 가능성 있음

 

요약: (구동 테이블이 작은 Nested Loops) + (내부 테이블의 결합 키에 인덱스) 조합으로 결합이 느릴 때 개선 가능

 

Nested Loops의 단점

예시: 한 개의 점포 당 받는 주문이 많는 경우

점포 테이블을 구동 테이블로 만들고 점포 ID를 결합 키로 사용 -> 수백만~수천만 건의 주문 레코드가 히트된다면 내부 테이블에 대해 반복 횟수가 많아져 성능 저하

해결방법: 1) 구동 테이블로 큰 테이블을 선택해 항상 하나의 레코드로 접근함을 보장

               2) 해시 사용

Hash

- 작은 테이블을 스캔하고 결합 키에 해시 함수 적용해 해시값으로 변환 후 다른 테이블을 스캔해 결합 키가 해시값에 존재하는지 확인하며 결합 수행

- 해시 테이블은 DBMS의 워킹 메모리에 저장되므로 작은 테이블에서 해시 테이블을 만듦

- Nested Loops에 비해 메모리를 크게 소모

- 메모리가 부족하면 저장소를 사용하므로 지연 발생

- 출력되는 해시값은 입력값의 순서를 알지 못하므로 등치 결합에만 사용 가능

Hash가 유용한 경우

- Nested Loops에서 적절한 구동 테이블이 존재하지 않는 경우

- Nested Loops에서 구동 테이블로 사용할만한 작은 테이블은 있지만 내부 테이블에서 히트되는 레코드 수가 너무 많은 경우

- Nested Loops에서 내부 테이블에 인덱스가 존재하지 않는 경우

Hash의 트레이드 오프

동시 실행성이 높은 OLTP 처리(사용자 요구에 시스템이 바로 응답해야 하는 처리)할 때 메모리 부족으로 지연 발생 리스크 있으므로 사용 불가

테이블 풀 스캔이 사용되는 경우가 많으므로 테이블 규모가 크다면 접근 시간 고려해야 함

Sort Merge

- 결합 대상 테이블들을 각각 결합 키로 정렬하고 일치하는 결합 키를 찾으면 결합

- 대상 테이블을 모두 정렬하므로 많은 메모리 소비(Hash보다 많은 메모리 사용 가능: Hash는 한쪽 테이블에 대해서만 테이블을 만듦)

- 등치, 부등호를 사용한 결합에 사용 가능, 부정 조건 결합에서는 사용 불가

- 테이블이 결합 키로 정렬되어 있다면 정렬 생략 가능

- 테이블을 정렬하므로 한쪽 테이블을 모두 스캔한 시점에 결합 완료됨

의도하지 않은 크로스 결합

- 삼각 결합

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;

테이블 B와 테이블 C의 결합 조건이 없음

실행 계획에 따라 테이블 B와 C를 크로스 결합 한 후 테이블 A와 결합할 수 있음

- 의도하지 않은 크로스 결합 회피하기

결합 조건이 존재하지 않는 테이블 사이에서 결과에 아무 영향을 주지 않는 불필요한 결합 조건을 추가

결합이 느릴 때

1. 상황에 따른 최적의 결합 알고리즘

<표6-1>

결합 대상 레코드 수에 따른 최적의 결합 알고리즘

- 소규모 & 소규모: 어떤 알고리즘을 사용해도 성능 차가 크지 않음

- 소규모 & 대규모: 소규모 테이블을 구동 테이블로 하는 Nested Loops 사용, 대규모 테이블의 결합 키에 인덱스 생성

                             내부 테이블의 결합 대상 레코드가 너무 많다면 구동 테이블과 내부테이블 swap 혹은 Hash 사용

- 대규모 & 대규모: 일단 Hash 사용, 결합 키로 정렬되어 있는 상태라면 Sort Merge 사용

2. 실행 계획 제어

DBMS에 따라 힌트 구를 사용해 사용자가 원하는대로 실행 계획을 제어할 수 있는 여부가 다름

옵티마이저는 완벽하지 않으므로 아직까지는 사용자가 실행 계획을 변경하곤 함

하지만 데이터 양과 카디널리티가 계속 변경되므로 실행 계획이 시점에 따라 적절하지 않을 수 있음 -> DBMS에서는 비용 기반에 따른 동적 실행 계획 도입

3. 흔들리는 실행 계획

옵티마이저의 실패: 장기적 운용 중 실행 계획이 안좋은 방향으로 변화해버리는 것, 데이터베이스의 통계 정보가 변했을 때 실행 계획을 변화시키는데 사전에 예측하기 어렵고 돌발적인 슬로다운을 일으킴

 

SQL 성능의 변동 위험을 줄이려면 결합을 피하는 것이 좋다!