6장 - 결합

결합 알고리즘은 결합의 성능을 결정하고, SQL 전체의 성능을 좌우하는 요인이므로 굉장히 중요하다.

결합 VS 상관 서브쿼리

상관 서브쿼리: 메인 쿼리의 테이블 값을 참조하여 처리되는 서브쿼리

-- 결합을 사용하는 코드
select e.emp_id, e.emp_name, e.dept_id, d.dept_name
from employees e
inner join department 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) dept_name
from employees e;

두 코드는 같은 결과를 나타낸다.

그럼 결합과 상관 서브쿼리 중 무엇을 사용하는 것이 좋을까?

▶ 상관 서브쿼리를 사용하면 결과의 레코드 수만큼 상관 서브쿼리가 실행되기 때문에 비용이 높아진다.

▶ 따라서 결합을 사용하는 것이 더 일반적이다.

결합 알고리즘과 성능

옵티마이저가 선택 가능한 결합 알고리즘은 크게 세 가지가 있다.

• Nested Loops
• Hash
• Sort Merge

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

MySQL은 Nested Loops와 그 파생 버전만 지원하고, Hash/Sort Merge는 지원하지 않는다.

 

<Nested Loops>

Nested Loops의 내부 동작 과정

• 테이블 A와 B를 결합하면 실행 시간은 (A의 레코드 수 x B의 레코드 수)에 비례한다.
• 한 번의 단계에서 처리하는 레코드 수가 적어서 메모리 소비가 적다.

Nested Loops의 성능은 구동 테이블을 무엇으로 선택하는 지에 따라 변화한다.

언뜻 보면 구동 테이블이 무엇이 되었건간에 접근하는 레코드의 수는 변하지 않는다고 할 수 있지만, 구동 테이블의 크기가 작을 수록 Nested Loops의 성능이 좋아진다.

단, 내부 테이블의 결합 키 필드에 인덱스가 존재해야 한다.

해당 인덱스를 통해 내부 테이블을 완전히 순회하지 않아도 되기 때문이다.

따라서 내부 테이블의 결합 키가 사용되지 않는다면 구동 테이블이 아무리 작아봤자 장점이 없다.

 

기대만큼의 성능이 나오지 않는 경우도 있다.

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

결국 내부 테이블의 데이터가 절대적으로 많다면 지연이 발생하는 것이다.

 

이 문제를 해결하는 방법은 두 가지가 존재한다.

 

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

이를 통해 성능의 비균등 문제를 해결하고, 극단적인 성능 저하를 막을 수 있다.

 

두 번째는 Hash 알고리즘이다.

 

<Hash>

해시 결합은 다음과 같이 수행된다.

1. 작은 테이블 스캔
2. 결합 키에 해시 함수를 적용해서 해시값으로 변환
3. 다른 테이블 스캔
4. 결합 키가 해시값에 존재하는지 확인

작은 테이블에서 해시 테이블을 만드는 이유는 해시 테이블을 워킹 메모리에 조금이라도 작은 것을 저장하기 위함이다.

 

해시 알고리즘의 특징은 다음과 같다.

• 해시 테이블을 생성하므로 Nested Loops에 비해 메모리를 크게 소모
• 메모리가 부족하면 저장소를 사용하여 지연 발생
• 등치 결합에만 사용 가능

해시 알고리즘이 유용한 경우는 다음과 같다.

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

해시 알고리즘을 사용할 때 발생하는 트레이드오프는 다음과 같다.

• 동시 실행성이 높은 OLTP 처리를 하면 메모리가 부족해져 저장소 사용 -> 지연 발생
• 동시 처리가 적은 야간 배치 또는 데이터 웨어하우스와 같은 시스템에 한해 해시 알고리즘을 사용하는 것이 기본 전략
• 해시 알고리즘은 반드시 두 테이블을 풀 스캔하므로 걸리는 시간 고려

<Sort Merge>

Sort Merge 알고리즘은 테이블 정렬을 생략할 수 있는 경우에만 고려해볼 만하다.

 

크로스 결합을 사용하지 않는 이유

• 크로스 결합의 결과가 필요한 경우가 없다.
• 비용이 매우 많이 드는 연산이다.

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

의도하지 않은 크로스 결합

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에는 결합 조건이 존재하지 않는다는 점이 포인트다.

이런 경우 옵티마이저가 table_B와 table_C를 크로스 결합하는 경우가 있는데, 이는 옵티마이저가 두 테이블의 크기가 충분히 작다고 판단했기 때문이다.

비록 두 테이블의 크기가 충분히 작다면 상관 없지만, 반대일 경우 결과에 아무런 영향을 주지 않는 선에서 결합 조건을 추가하는 것이 좋다.

총 정리

최적의 결합 알고리즘을 결합 대상 레코드 수의 관점에서 정리하면 다음과 같다.

• 소규모 - 소규모 : 어떤 알고리즘이든 상관 X
• 소규모 - 대규모 : Nested Loops, 내부 테이블의 레코드가 너무 많다면 구동 테이블과 바꾸거나 Hash 고려
• 대규모 - 대규모 : Hash, 처음부터 정렬되어 있다면 Sort Merge

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

 

추가 내용 - 결합은 왜 SQL 성능의 변동 위험을 가져오는가?

결합 알고리즘은 크게 세 가지 종류가 있다는 걸 알게 됐다.

이들 중에서 어떤 것을 선택할지는 테이블의 크기 등을 고려해 옵티마이저가 자동으로 결정한다.

따라서 처음에는 Nested Loops를 사용하다가도 레코드 수가 늘어나면 실행 계획 변동이 발생한다.

이때 성능에 큰 변화가 생겨 변동 리스크를 안을 수밖에 없다.