6장 결합

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

- SQL에는 '결합'이라는 이름이 붙은 연산이 굉장히 많다.

    ㆍ크로스 결합

    ㆍ내부 결합

    ㆍ외부 결합

    ㆍ자기 결합

    ㆍ등가 결합/비등가 결합

    ㆍ자연 결합

- 기능적인 관점으로 분류한 것이 크로스 결합, 내부 결합, 외부 결합이다.

- 등가 결합/비등가 결합은 결합 조건으로 등호(=)를 사용하는지, 이 이외의 부등호를 사용하는지의 차이를 의미한다.

- 자연결합은 가장 자주 사용하는 '내부 결합이면서 등가 결합'이라는 조합을 간단하게 작성하는 것이다.

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

    ㆍ자연 결합은 자주 사용하는 등가 결합을 짧게 쓸 수 있다는 것이 장점이다.

    ㆍ하지만 실제로 내부 결합으로 써도 딱히 써야할 양이 많아지는 것이 아니다.

    ㆍ또한 필드 이름이 다르거나 자료형이 다르면 적용할 수 없어 확장성이 떨어진다.

    ㆍ추가로 테이블 정의를 제대로 이해하지 않고 있다면, 문장을 보았을 때 결합 조건이 무엇인지 잘 알 수 없으므로 가독성도 좋지 않다.

    ㆍ결론적으로 특별한 사정이 없다면, 그냥 내부 결합을 사용하는 것을 추천

 

1. 크로스 결합 - 모든 결합의 모체

- 크로스 결합은 실무에서 사용할 기회가 거의 없다.

- 크로스 결합은 수학에서 데카르트 곱이라고 불리는 연산으로, 2개 테이블의 레코드에서 가능한 모든 조합을 구하는 연산이다.

 

크로스 결합이 실무에서 사용되지 않는 이유

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

- 비용이 매우 많이 드는 연산이다.

 

실수로 사용한 크로스 결합

SELECT *
    FROM Employees, Departments;

- 이 경우, 결합 조건이 없으므로 DBMS는 어쩔 수 없이 두 개의 테이블에서 나올 수 있는 모든 조합을 만든다.

 

2. 내부 결합 - 왜 '내부' 라는 말을 사용할까?

- 내부 결합(inner join)은 가장 많이 사용되는 조합 중 하나이다.

 

내부 결합의 동작

- 내부는 '데카르트 곱의 부분 집합'이라는 의미이다.

- 처음부터 결합 대상을 최대한 축소하는 형태로 작동한다.

 

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

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;

- 내부 결합과 상관 서브쿼리 중에 기본적으로 결합을 사용하는 것이 좋다.

- 상관 서브쿼리를 스칼라 서브쿼리로 사용하면 결과 레코드 수만큼 상관 서브쿼리를 실행해 비용이 꽤 높아진다.

 

3. 외부 결합 - 왜 '외부' 라는 말을 사용할까?

- 외부 결합(outer join)은 내부 결합과 함께 자주 사용되는 결합이다.

- 외부는 '데카르트 곱의 부분 집합이 아니다'라는 의미

- 다만 데이터 상태 따라 경우에 따라서는 데카르트 곱의 부분 집합이 되기도 한다.

 

외부 결합의 작동

- 외부 결합의 종류 3가지

    ㆍ왼쪽 외부 결합

    ㆍ오른쪽 외부 결합

    ㆍ완전 외부 결합

-- 왼쪽 외부 결합(왼쪽 테이블이 마스터)
SELECT E.emp_id, E.emp_name, E.dept_id, D.dept_name
  FROM Departments D LEFT OUTER JOIN Employees E
    ON D.dept_id = E.dept_id;
    
-- 오른쪽 외부 결합(오른쪽 테이블이 마스터)
SELECT E.emp_id, E.emp_name, E.dept_id, D.dept_name
  FROM Employess E RIGHT OUTER JOIN Departments D
    ON E.dept_id = D.dept_id;

 

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

- 외부 결합 결과가 크로스 결합 결과의 부분 집합이 아닌 이유는 외부 결합이 마스터 테이블의 정보를 모두 보존하고자 NULL을 생성하기 떄문이다.

    ㆍ반면 크로스 결합과 내부 결합은 NULL을 생성하지는 않는다.

 

5. 자기 결합 - '자기'란 누구일까?

- 자기 결합(self join)은 문자 그대로 자기 자신과 결합하는 연산으로, 간단하게 말하면 같은 테이블(또는 같은 뷰)를 사용해 결합하는 것이다.

- 자기 결합은 생성되는 결과를 기준으로 분류하는 것이 아니라 연산의 대상으로 무엇을 사용하는지에 따른 분류이다.

 

---

19. 결합 알고리즘과 성능

- 옵티마이저가 선택 가능한 결합 알고리즘은 크게 다음과 같은 3가지가 있다.

    ㆍ1. Nested Loops

    ㆍ2. Hash

    ㆍ3. Sort Merge

- 옵티마이저가 어떤 알고리즘을 선택할지 여부는 데이터 크기와 또는 결합 키의 분산이라는 요인에 의존한다.

    ㆍ그중 가장 빈번하게 볼 수 있는 알고리즘은 Nested Loops로, 각종 결합 알고리즘의 기본이 되는 알고리즘이다.

    ㆍ다음으로 중요한 것은 Hash이다.

    ㆍSort Merge는 앞의 두 가지에 비해 중요성이 한 단계 떨어진다.

 

1. Nested Loops

Nested Loops의 작동

- Nested Loops는 이름 그대로 중첩 반복을 사용하는 알고리즘이다.

- SQL에서 결합은 한 번에 두 개의 테이블만 결합하므로 본질적으로 이중 반복과 같은 의미이다.

- 세부 처리

    ㆍ1. 결합 대상 테이블(Table_A)에서 레코드를 하나씩 반복해가며 스캔한다.

            이 테이블을 구동 테이블(driving table) 또는 외부 테이블(outer table)이라고 부른다.

            다른 테이블(Table_B)은 내부 테이블(inner table)이라고 부른다.

    ㆍ2. 구동 테이블의 레코드 하나마다 내부 테이블의 레코드를 하나씩 스캔해서 결합 조건에 맞으면 리턴한다.

    ㆍ3. 이러한 작동을 구동 테이블의 모든 레코드에 반복한다.

- 특징

    ㆍTable_A, Table_B의 결합 대상 레코드를 R(A), R(B)라고 하면 접근되는 레코드 수는 R(A) x R(B) 가 된다.

         Nested Loops의 실행 시간은 이러한 레코드 수에 비례한다

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

    ㆍ모든 DBMS에서 지원한다.

- Nested Loops는 매우 단순해보일 수 있지만 결합 성능에서 가장 중요한 것이라 해도 과언이 아닌 중요한 알고리즘이다.

- 구동 테이블의 선택은 Nested Loops의 성능에 굉장히 중요한 의미를 가진다.

- 구체적으로 말하자면 구동 테이블이 작을수록 Nested Loops의 성능이 좋아진다.

    ㆍ여기서 중요한 것은 이중 반복의 외측과 내측의 반복 처리가 비대칭이라는 점이다.

 

구동 테이블의 중요성

- Nested Loops의 성능을 개선하는 키워드가 '구동 테이블로는 작은 테이블을 선택하는 것'

    ㆍ기본적으로 틀린 방침은 아니지만, 실제로는 전제 조건이 없으면 의미가 없는 말이다.

- 암묵적인 전제로는 "내부 테이블의 결합 키 필드에 인덱스가 존재" 이다.

- 만약 내부 테이블의 결합 키 필드에 인덱스가 존재한다면, 해당 인덱스를 통해 DBMS는 내부 테이블을 완전히 순환하지 않아도 된다.

    ㆍ달리 말하면 내부 테이블의 반복을 어느 정도 건너뛸 수 있게 된다는 것이다.

- 반면 결합 키가 내부 테이블에 대해 유일하지 않은 경우는 인덱스로 내부 테이블에 접근하는 경우라도 여러 개의 레코드가 히트될 가능성이 있다.

- 이렇게 생각해보면 '구동 테이블을 작게' 라는 말보다도 '내부 테이블을 크게'라고 이해하는 편이 더 쉬울 것이다.

    ㆍ내부 테이블이 클수록 인덱스 사용으로 인한 반복 생략 효과가 커지기 때문이다.

 

Nested Loops의 단점

- '구동 테이블이 작은 Nested Loops' + '내부 테이블의 결합 키에 인덱스' 

    ㆍ기대만큼의 응답 시간이 나오지 않기도 한다.

- 보통 이런 경우는 결합 키로 내부 테이블에 접근할 때 히트되는 레코드가 너무 많기 떄문이다.

    ㆍ'결합 키가 내부 테이블에 대해 유일하지 않은 경우'에 발생

- 최종적으로 SQL의 성능은 처리하는 데이터양에 의존한다.

- 이 문제에 대처하는 방법 2가지

    ㆍ1. 구동 테이블로 큰 테이블을 선택하는 역설적인 방법이다.

    ㆍ2. Hash 사용

 

2. Hash

Hash의 작동

- 입력에 대해 어느 정도 유일성과 균일성을 가진 값을 출력하는 함수를 해시라고 한다.

- 해시 결합은 일단 작은 테이블을 스캔하고, 결합 키에 해시 함수를 적용해서 해시값으로 변환한다.

    ㆍ이어서 다른 테이블(큰 테이블)을 스캔하고, 결합 키가 해시값에 존재하는지를 확인하는 방법으로 결합을 수행한다.

- 작은 테이블에서 해시 테이블을 만드는 이유는, 해시 테이블은 DBMS의 워킹 메모리에 저장되므로 조금이라도 작은 것이 효율적이기 때문이다.

- Hash가 사용되는 경우 어떤 한 쪽의 테이블이 극단적으로 작거나 크지 않는다.

 

Hash의 특징

- 결합 테이블로부터 해시 테이블을 만들어서 활용하므로, Nested Loops에 비해 메모리를 크게 소모한다.

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

- 출력되는 해시값은 입력값의 순서를 알지 못하므로, 등치 결합에만 사용할 수 있다.

 

Hash가 유용한 경우

- Nested Loops에서 적절한 구동 테이블(상대적으로 충분히 작은 테이블)이 존재하지 않는 경우

- 앞서 'Nested Loops의 단점'에서 본 것처럼 구동 테이블로 사용할만한 작은 테이블은 있지만, 내부 테이블에서 히트되는 레코드 수가 너무 많은 경우

- Nested Loops의 내부 테이블에 인덱스가 존재하지 않는(또는 여러 가지 사정에 의해 인덱스를 추가할 수 없는) 경우

 

- 한마디로 말하자면, Nested Loops가 효율적으로 작동하지 않는 경우의 차선책이 Hash이다.

- 다만 Hash에도 주의해야 하는 트레이드 오프가 있다.

1. 초기에 해시 테이블을 만들어야 하므로, Nested Loops에 비해 소비하는 메모리 양이 많다는 것이다.

    ㆍ따라서 동시 실행성이 높은 OLTP 처리를 할 때 Hash가 사용되면, DBMS가 사용할 수 있는 메모리가 부족해져 저장소가 사용된다.

    ㆍ결국 지연이 발생할 리스크가 있다.

    ㆍ따라서 OLTP 처리를 할 때 Hash를 사용하면 안된다.

    ㆍ반대로 동시 처리가 적은 야간 배치 또는 BI/DWH와 같은 시스템에 한해 사용하는 것이 Hash를 사용하는 기본 전략이다.

2. Hash의 결합은 반드시 양쪽 테이블의 레코드를 전부 읽어야 하므로, 테이블 풀 스캔이 사용되는 경우가 많다.

    ㆍ따라서 테이블의 규모가 굉장히 크다면, 이런 풀 스캔에 걸리는 시간도 고려해야 한다. 

 

3. Sort Merge

Sort Merge의 작동

- Sort Merge는 간단하게 Merge 또는 Merge Join이라 부르기도 한다.

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

 

Sort Merge의 특징

1. 대상 테이블을 모두 정렬해야 하므로 Nested Loops보다 많은 메모리를 소비한다.

    ㆍHash와 비교하면 규모에 따라 다르지만,

        Hash는 한쪽 테이블에 대해서만 해시 테이블을 만들므로 Hash보다 많은 메모리를 사용하기도 한다

    ㆍ메모리 부족으로 TEMP 탈락이 발생하면 I/O 비용이 늘어나고 지연이 발생할 위험이 있다

2. Hash와 다르게 동치 결합뿐만 아니라 부등호를 사용한 결합에도 사용할 수 있다.

    ㆍ하지만 부정조건(<>) 결합에서는 사용할 수 없다.

3. 원리적으로 테이블이 결합 키로 정렬되어 있다면 정렬을 생략할 수 있다.

    ㆍ다만 이는 SQL에서 테이블에 있는 레코드의 물리적인 위치를 알고 있을 때이다.

    ㆍ따라서 이러한 생략은 구현 의존적이다.

4. 테이블을 정렬하므로 한쪽 테이블을 모두 스캔한 시점에 결합을 완료할 수 있다.

 

Sort Merge가 유효한 경우

- Sort Merge 결합 자체에 걸리는 시간은 결합 대상 레코드 수가 많더라도 나쁘지 않은 편이지만, 테이블 정렬에 많은 시간과 리소스를 요구할 가능성이 있다.

- 따라서 테이블 정렬을 생략할 수 있는 경우에는 고려해볼만 하지만, 그 이외에는 Nested Loops와 Hash를 우선적으로 고려해라!

 

4. 의도하지 않은 크로스 결합

- 의도하지 않게 크로스 결합이 나타나는 경우가 있는데, 바로 '삼각 결합'이라 부르는 패턴이다.

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;

- 결합 조건에 'Table_B - Table_C' 에는 결합 조건이 존재하지 않는다는 점이 포인트이다.

- 이러한 경우 4가지 실행 계획이 나올 수 있다.

    ㆍTable_A를 구동 테이블로 Table_B와 결합하고 그 결과를 Table_C와 결합

    ㆍTable_A를 구동 테이블로 Table_C와 결합하고 그 결과를 Table_B와 결합

    ㆍTable_B를 구동 테이블로 Table_A와 결합하고 그 결과를 Table_C와 결합

    ㆍTable_C를 구동 테이블로 Table_A와 결합하고 그 결과를 Table_B와 결합

 

의도하지 않은 크로스 결합을 회피하는 방법

- 결합 조건이 존재하지 않는 테이블 사이에 불필요한 결합 조건을 추가해주는 방법이 있다.

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;

 

---

20. 결합이 느리다면

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

이름	장점	단점
Nested Loops	- '작은 구동 테이블' + '내부 테이블의 인덱스' 라는 조건이 있다면 굉장히 빠르다. - 메모리 또는 디스크 소비가 적으므로 OLTP에 적합 - 비등가 결합에서도 사용 가능	- 대규모 테이블들의 결합에는 부적합 - 내부 테이블의 인덱스가 사용되지 않거나, 내부 테이블의 선택률이 높으면 느리다
Hash	- 대규모 테이블들을 결합할 때 적합	- 메모리 소비량이 큰 OLTP에는 부적합 - 메모리 부족이 일어나면 TEMP 탈락 발생 - 등가 결합에서만 사용 가능
Sort Merge	- 대규모 테이블들을 결합할 때 적합 - 비등가 결합에서도 사용 가능	- 메모리 소비량이 큰 OLTP에는 부적합 - 메모리 부족이 일어나면 TEMP 탈락 발생 - 데이터가 정렬되어 있지 않다면 비효울적

 

1. 소규모 - 소규모

- 결합 대상 테이블이 작은 경우에는 어떤 알고리즘을 사용해도 성능 차이가 크지 않다.

2. 소규모 - 대규모

- 소규모 테이블을 구동 테이블로 하는 Nested Loops를 사용한다.

- 대규모 테이블의 결합 키에 인덱스를 만들어주는 것을 잊지마라!

- 하지만 내부 테이블의 결합 대상 레코드가 너무 많다면 구동 테이블과 내부 테이블을 바꾸거나, Hash를 사용해볼 것을 검토

3. 대규모 - 대규모

- 일단은 Hash를 사용한다

- 결합 키로 처음부터 정렬되어 있는 상태라면 Sort Merge를 사용한다.

 

2. 실행 계획 제어

DBMS 별 실행 계획 제어

- Oracle

    ㆍ힌트 구로 결합 알고리즘을 제어할 수 있다(USE_NL, USE_HASH, USE_MERGE).

    ㆍ구동 테이블도 지정할 수 있다(LEADING).

- Microsoft SQL Server

    ㆍ힌트 구로 결합 알고리즘을 제어할 수 있다(LOOP, HASH, MERGE)

- DB2

    ㆍ힌트 구가 없으며, 원칙적으로 사용자가 실행 계획을 제어할 수 없다.

- PostgreSQL

    ㆍpg_hint_plan 기능을 사용해 힌트 구처럼 결합 알고리즘을 제어할 수 있다.

    ㆍ또한 서버 매개변수로 데이터베이스 전체를 제어할 수도 있다(enable_nestloop, enable_hashjoin, enable_mergejoin)

- MySQL

    ㆍ결합 알고리즘 자체가 Nested Loops 계열밖에 없으므로 따로 선택의 여지가 없다.

 

3. 흔들리는 실행 계획

- 실행 계획 변동이 일어나기 가장 쉬운 연산이 결합이다.

- 결합은 여러개의 알고리즘을 사용할 수 없다.

- SQL 성능의 변동 위험을 줄이려면 되도록 결합을 피해야 한다.

 

 

---

마치며..

- 결합은 SQL의 성능 문제의 화약고

- 기본은 Nested Loops, 배치 또는 BI/DWH 에서는 Hash, Hash를 사용할 때는 TEMP 탈락에 주의

- Nested Loops가 효율적으로 동작하려면 '작은 구동 테이블'과 '내부 테이블의 인덱스'가 필요

- 결합은 알고리즘이 복잡하므로 실행 계획 변동이 일어나기 쉬움. 이를 방지하려면 '결합을 사용하지 않는 것'이 중요한 전략