6장 결합
SQL 레벨업이라는 도서를 정리한 내용입니다.
6장에서는 SQL의 결합과 결합이 어떤식으로 동작하는지 설명합니다.
결합의 종류
크로스 결합, 내부 결합, 외부 결합의 관계를 그림으로 표현하면 위 그림과 같습니다.
크로스 결합 - CROSS JOIN
- 크로스 결합은 실무에서 거의 사용되지 않는다.
- 비용이 많이 드는 비효율적인 연산이다.
- 이러한 결과가 필요한 경우가 거의 없다.
- 크로스 결합은 수학에서 데카르트 곱이라고 불리는 연산으로, 가능한 모든 조합을 구하는 연산이다.
내부 결합 - INNER JOIN
- 내부 결합의'내부'는 '데카르트 곱의 부분 집합'이라는 의미이다.
외부 결합 - OUTER JOIN
- 외부 결합의 '외부'는 '데카르트 곱의 부분 집합이 아니다'라는 의미다.
- 경우에 따라서 테카르트 곱의 부분 집합이 되기도 한다.
- 외부 결합은 세 가지 종류가 있다
- 왼쪽 외부 결합 - LEFT OUTER JOIN
- 오른쪽 외부 결합 - RIGHT OUTER JOIN
- 완전 내부 결합 - OUTER JOIN
자기 결합 - SELF JOIN
- 자기 결합은 문자 그대로 자신과 결합하는 것이다. 따라서 앞선 세 결합과는 분류가 자체가 다르다.
- 결합 연산의 대상에 자기 자신이 있으면 자기 결합 연산이 된다.
- 논리적으로는 자기 자신과 똑같이 생긴 다른 테이블을 결합한다고 생각해도 상관 없다.
결합 알고리즘과 성능
Nested Loops
이름 그대로 중접 반복을 사용하는 알고리즘이다.
세부 처리
1.결합 대상 테이블(Table_A)에서 레코드를 하나씩 반복하며 스캔한다. 이 테이블을 구동 테이블(driving table) 또는 외부 테이블(outer_table)이라고 한다. 다른 테이블(Table_B)은 내부 테이블(inner table)이라고 부른다.
2. 구동 테이블이 레코드를 하나마다 내부 테이블의 레코드를 스캔해서 결합 조건에 맞으면 리턴한다.
3. 2를 구동 테이블의 모든 레코드에 반복한다.
특징
- Table_A, Table_B의 결합 대상 레코드를 R(A), R(B)라고 하면 접근되는 레코드 수는 R(A) x R(B)가 된다. Nested Loops의 실행 시간은 이러한 레코드 수에 비례한다.
- 한 번의 단계에서 처리하는 레코드 수가 적어, Hash나 Sort Merge에 비해 메모리 소비가 적다.
- 모든 DMBS에서 지원한다.
구동 테이블의 중요성
NL 결합에서 구동 테이블으로 작은 테이블을 선택하는 것이 성능 개선이 된다는 말이 있다. 하지만 이 말에는 한 가지 조건이 필요하다. 바로 '내부 테이블의 결합 키 필드에 인덱스가 존재해야 한다'는 것이다.
위 조건이 충족되면 DBMS는 내부 테이블을 완전히 순환하지 않아도 된다. 민약 구동 테이블의 한 레코드에 내부 테이블의 한 레코드가 대응하고 이를 인덱스로 찾을 수 있다면, 접근하는 레코드 수는 R(A) x N(리프 노느까지의 거리)가 된다.
다만 한 레코드가 여러 레코드에 대응하는 경우 여러개의 레코드에 반복을 적용해야 한다. ( index range scan )
단점
내부 테이블의 선택률이 높으면 성능이 악화된다.
Hash
세부 처리
- 먼저 작은 테이블을 스캔한다.
- 결합 키에 해시 함수를 적용해서 해시값으로 반환한다. (이런 해시값의 집합을 해시 테이블이라 한다.)
- 이어서 다른 테이블을 스캔하고, 결합 키가 해시값에 존재하는지 확인하는 방법으로 결합을 수행한다.
작은 테이블에서 해시 테이블을 만드는 이유는 해시 테이블이 DBMS의 워킹 메모리에 저장되기 때문이다.
특징
- 결합 테이블로부터 해시 테이블을 만들어서 활용하므로, Nested Loops에 비해 메모리 소모가 크다.
- 따라서 동시 실행성이 높은 OLTP에는 적절하지 않고, 야간 배치, BI/DWH에 사용한다.
- 메모리가 부족하면 저장소를 사용하므로 지연이 일어난다.
- 출력되는 해시값은 입력값의 순서를 알지 못하므로, 등치(=) 결합에만 사용할 수 있다.
유용한 경우
- Nested Loops에 적절한 구동 테이블(상대적으로 충분히 작은 테이블)이 없는 경우
- 구동 테이블로써 적절한 테이블이 있지만, 내부 테이블에서 히트되는 레코드 수가 너무 많은 경우
- Nested Loops의 내부 테이블에 인덱스가 없는 경우
Sort Merge
세부 처리
- 결합 대상 테이블을 각각 결합키로 정렬한다.
- 일치하는 결합키를 찾으면 결합한다.
특징
- 대상 테이블을 모두 정렬하므로 Nested Loops보다 메모리 소모가 크고, 상황에 따라 Hash보다 많은 메모리를 사용한다.
- 동치 결합뿐 아니라 부등호(=, >=, <, ...)를 사용한 결합에도 사용할 수 있다.
- 단 부정조건(<>) 결합에는 사용할 수 없다.
- 테이블이 결합키로 정렬되어 있다면 정렬이 필요 업지만, 이는 구현 의존적이다.
유효한 경우
Sort Merge은 결합 자체에 걸리는 시간은 나쁘지 않은 편이지만, 정렬에 많은 시간과 리소스를 소모할 가능성이 있으므로 테이블 정렬을 생략할 수 있는(매우 예외적인) 상황을 제외하고는 Nested Loops나 Hash를 우선적으로 고려하는게 좋다.
의도하지 않은 크로스 결합
의도하지 않는 크로스 결합이 일어나는 경우는 대부분 작성자의 실수 때문이고, 대부분 '삼각 결합'이라 부르는 패턴에서 문제가 발생한다.
이러한 형태의 삼각 결합에서 Table_B와 Table_C의 결합이 일어난다면, 둘 사이에는 아무런 결합조건이 없기에 크로스 결합이 일어난다.
사실 작은 테이블 사이의 크로스 결합은 자주 일어나며, 크게 두려워할 필요는 없다. 하지만 비교적 큰 테이블 끼리의 크로스 결합이 일어나는 경우는 큰 성능 저하가 일어난다.
의도하지 않은 크로스 결합을 회피하는 법
이처럼 불필요한 결합 조건을 추가해서 크로스 결합을 회피하는 것이다. 이렇게 된다면 실행 계획이 변경되어도 크로스 결합이 일어날 걱정을 할 필요가 없다.
결합이 느리다면
상황에 따른 결합 알고리즘
소규모 - 소규모
어떤 알고리즘을 사용해도 큰 차이가 없습니다.
소규모 - 대규모
소규모 테이블을 구동 테이블로 하는 경우 Nested Loops를 사용한다. 하지만 내부 테이블의 결합 대상 레코드가 너무 많다면 구동 테이블과 내부 테이블을 바꾸거나, Hash를 사용하는 것을 검토해 보아라.
대규모 - 대규모
Hash를 사용한다. 결합키로 정렬이 되어 있다면 Sort Merge를 사용한다.
실행 계획 제어
실행 계획을 바꾸는 것은 DBMS 마다 다르다.
- Oracle
- 힌트 구로 결합 알고리즘을 제어 할 수 있다.
- 구동 테이블도 지정할 수 있다.
- SQL Server
- 힌트 구로 결합 알고리즘을 제어할 수 있다.
- DB2
- 힌트 구가 없으며, 원칙적으로 실행계획을 제어할 수 없다.
- PostgreSQL
- pg_hint_plan을 이용해 결합 알고리즘을 제어할 수 있다.
- 서버 매개변수로 데이터베이스 전체를 제어할 수 있다.
- MySQL
- 결합 알고리즘 자체가 Nested Loops 계열 밖에 없어서 제어의 의미가 없다.
흔들리는 실행 계획
사용자가 제어
사용자가 실행 계획을 제어하는 것은 큰 리스크가 따른다. 데이터와 카디널리티는 데이터베이스를 운용하면서 계속 바뀌기 때문에 어떨때는 적절한 실행계획이 다른 시점에는 좋지 않은 실행계획이 될 수도 있다.
그러므로 사용자가 실행 계획을 제어할 때는 시스템의 미래 상황을 예측하고, 이러한 위험을 충분히 검토하여야 한다.
옵티마이저가 제어
옵티마이저에게 실행계획을 맡겨도 최적의 실행 결과가 선택되지 않는 경우가 있다.
대표적으론 장기적인 운용 중에 실행 계획이 안좋은 방향으로 변화하는 것이다. 이는 사전에 예측하기 어렵고 돌발적인 슬로다운을 일으키는데, 데이터양의 증가나 통계 정보의 변화로 인하여 옵티마이저가 실행 계획을 변경하면서 발생한다.
결합은 여러 알고리즘을 선택할 수 있기 때문에 실행 계획 변동이 가장 일어나기 쉬운 부분이다.
따라서 SQL 성능 변동을 줄이기 위해서는 결합의 수를 줄여야 한다.
