[SQL 레벨업] Ch.6 결합

CROSS JOIN, INNER JOIN, OUTER JOIN에 대한 내용은 생략하겠다.

결합 알고리즘과 성능

옵티마이저가 선택 가능한 결합 알고리즘의 종류

• Nested Loops
• Hash
• Sort Merge

알고리즘을 선택하는 기준은 데이터의 크기, 결합 키의 분산이다.
가장 기본적으로 사용하는 것이 Nested Loops, Hash, Sort Merge 순이다.

DBMS의 종류에 따라 지원하지 않는 경우가 있다.
MySQL의 경우 Nested Loops와 그 파생 버전만 지원하고 Hash, Sort Merge를 사용하지 않는다. 버전에 따라 알고리즘이 바뀌는 경우가 있기 때문에 사용하는 DBMS의 최신 동향에는 주의를 기울여야한다.

1. Nested Loops

• 2중 for문과 같은 방식이다.
• 접근되는 레코드 수 = Table_A * Table_B
• 실행 시간은 레코드 수에 비례
• 한 번의 단계에서 처리하는 레코드 수가 적으므로 Hash, Sort Merge에 비해 메모리 소비가 적다.

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

이상적인 경우 : 구동 테이블의 레코드 한 개에 내부 테이블의 레코드 한 개가 대응

• 내부 테이블의 인덱스를 사용해 찾을 수 있으므로 접근하는 레코드 수는
  Table_A * 2가 된다.

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

'구동 테이블이 작은 Neted Loops + 내부 테이블의 결합 키에 인덱스' 조합은 SQL 튜닝의 기본 중에 기본이다.

단점 :
결합 키로 내부 테이블에 접근할 때 히트되는 레코드가 너무 많으면 기대만큼의 응답 시간이 나오지 않는다. '결합 키가 내부 테이블에 대해 유일하지 않은 경우'에 발생

해결 방안 :

• 구동 테이블로 큰 테이블을 선택하는 역설적인 방법
  내부 테이블에 대한 점포 테이블의 접근이 기본 키로 수행되므로, 항상 하나의 레코드로 접근하는 것이 보장된다.
  -> 큰 내부 테이블에 계속해서 접근하는 것이 아닌 보다 작은 테이블에 접근하는 것이 효율적이므로 이런 방식이 성능 저하를 막을 수 있는 것 같다.

• 해시

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

2. Hash

1. 작은 테이블을 스캔
2. 결합 키에 해시 함수를 적용해 해시값으로 반환
3. 다른 테이블(큰 테이블)을 스캔하고, 결합 키가 해시값에 존재하는지 확인

작은 테이블을 스캔하는 이유는 해시 테이블이 워킹 메모리에 저장되므로 조금이라도 작은 것이 효율적이기 때문!

특징

• 해시 테이블을 만들어서 활용해 Nested Loops에 비해 메모리를 크게 소모
• 메모리가 부족하면 저장소를 사용하므로 지연이 발생
• 출력되는 해시값은 입력값의 순서를 알지 못하므로, 등치 결합에만 사용 가능

유용한 경우

• Nested Loops에서 적절한 구동 테이블(상대적으로 충분히 작은 테이블)이 존재하지 않는 경우
• 구동 테이블로 사용할만한 작은 테이블은 있지만, 내부 테이블에서 히트되는 레코드 수가 너무 많은 경우
• 내부 테이블에 인덱스가 존재하지 않는 경우

Nested Loops가 효율적으로 작동하지 않는 경우의 차선책이다.

• OLTP를 처리할 때 Hash를 사용하면, 메모리가 부족해져 저장소가 사용된다.
  결국 지연이 발생함.

OLTP : 온라인 트랜잭션 처리 -> 동시에 발생하는 다수의 트랜잭션을 실행하는 데이터 처리 유형

• 동시 처리가 적은 야간 배치같은 시스템에 한해 Hash를 사용하면 유용하다.

양쪽의 테이블을 모두 읽어야 하므로 데이터 풀스캔이 사용되는 경우가 많다.
테이블의 규모가 굉장히 크다면, 풀 스캔에 걸리는 시간도 고려야해 한다.

Sort Merge

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

특징

• 대상 테이블을 모두 정렬해야 하므로 많은 메모리를 소비한다.
• Hash는 한쪽 테이블에 대해서만 해시 테이블을 만들기 때문에 Hash보다 많은 메모리를 사용하기도 한다.
• 동치 결합뿐만 아니라 부등호를 사용한 결헙에도 사용할 수 있다.
  하지만 부정 조건(<>) 결합에는 사용할 수 없다.

테이블 정렬에 많은 시간과 리소스를 요구할 가능성이 있다.

테이블 정렬을 생략할 수 있는 경우에는 고려해볼 만 하지만, 그 이외의 경우는 Nested Loop와 Hash를 우선적으로 고려해야 한다.

실행 계획을 제어하는 법

• Oracle : 힌트 구를 사용한다. (USE_NL, USE_HASH, USE_MERGE)
  구동 테이블도 지정 가능 (LEADING)
• MSSQL : 힌트 구(LOOP, HASH, MERGE)
• PostgreSQL : pg_hint_plan 기능을 통해 힌트 구 처럼 제어 가능
• MySQL : 결합 알고리즘 자체가 Nested Loops 밖에 없어 선택 불가능

사용자가 실행 계획을 제어하면, 실행 계획이 고정되기 때문에 비효율적일 수 있다.
그렇다고 옵티마이저의 선택이 언제나 옳지도 않다.

방법

• 비정규화 (뭔지 모름)
• 결합을 최소화 -> 상관 서브쿼리로 대체, 등

결합을 회피하는 대체 수단을 학습하자!