SQL 레벨업 02

 

UNION은 대부분의 상황에서 비효율적인 쿼리일 가능성이 높다. CASE로 분기하자.

- 외부적으로는 하나의 SQL 구문을 실행하는 것처럼 보이지만

- 내부적으로는 여러 개의 select 구문을 실행하는 실행 계획으로 해석된다.

- 즉 테이블에 접근하는 횟수가 많아져 I/O 비용이 늘어난다.

- (SQL UNION SQL) 이렇게 있으면 SQL 각각 select 연산을 수행한다.

- select 구문으로 분기하지 않고 CASE 구문으로 분기하는 접근만으로도 SQL 효율을 올릴 수 있다.

 

 

결합 알고리즘

- JOIN 연산을 수행할 때 옵티마이저가 선택하는 알고리즘

- Nested Loops

- Hash

- Sort Merge의 크게 3가지가 있다.

- 참고로 MySQL은 Nested Loops만을 공식적으로 지원하고 파생 기능을 통해 다른 결합 알고리즘의 부재를 보완하고 있다.

Nested Loops

- 테이블 a, b를 결합한다고 가정하자

- 하나의 테이블이 외부 테이블(구동 테이블)이 되고 나머지 테이블이 내부 테이블이 된다.

- for문을 도는 것처럼 내부 테이블의 모든 row를 탐색하면서 외부 테이블의 row 값과 조건이 일치하는지 판단하면서 결합한다.

- 수행 시간은 테이블 a, b의 데이터의 수에 비례한다. 즉 a_row_nums * b_row_nums가 실행시간이 된다.

- 한 번에 비교엔 실제로 a 테이블의 row 1개와 b 테이블의 row 1개를 비교한다. 즉 처리하는 레코드 수가 적기 때문에 메모리 소비는 적다.

- 모든 DBMS에서 지원한다.

- 구동 테이블의 사이즈가 작고, 내부 테이블의 결합 키에 인덱스가 있도록 조합하면(즉 결합 키가 내부 테이블에 유일하도록 하는 인덱스, 예를 들면 row 고유 id) 최고의 성능을 낼 수 있는 결합 알고리즘이다.

- 구동 테이블의 사이즈가 작다면 for문의 변수 하나의 값이 작다는 것이고, 내부 테이블의 결합 키에 인덱스가 있도록 한다면 많은 데이터를 skip 할 수 있다.

- 하지만 위 조건이 맞지 않을 경우는 그냥 full scan이 빠를 수 있다. 예를 들어 결합 키가 내부 테이블에 유일하지 않다고 가정하면 해당 키에 해당하는 row 수가 많아지는데, 그 말은 일정 비율이 넘어가면 그냥 full scan 해서 나머지 값들을 버리는 게 빠를 수 있다는 뜻이기 때문이다.

 

- 해결법으론 구동 테이블로 큰 테이블을 선택해 내부 테이블의 기본키로 결합을 수행해서 항상 하나의 레코드로 접근하는 것을 보장하기와

- 다른 결합 알고리즘을 사용하는 방법이 있다.

 

Hash

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

- 즉 메모리가 부족하면 디스크 I/O가 생겨서 디스크를 사용하기 때문에 지연이 발생한다.

- 하지만 Nested Loops가 사용되기 어려운 아래 상황에서 대안책으로 사용된다.

- 적절한 구동 테이블이 없거나,

- 구동 테이블은 있지만 내부에서 히트되는 레코드 수가 너무 많거나

- 내부 테이블에 인덱스가 존재하지 않거나 인덱스를 사용할 수 없는 경우

 

- 메모리를 많이 사용하기 때문에 동시 처리가 적은 야간 배치나 BI/DWH 같은 시스템에 한해 사용한다.

- 그리고 양쪽 테이블 레코드를 전부 읽어야 하기 때문에 full scan이 필요하다.

 

Soft Merge

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

- 대상 테이블 2개를 모두 정렬해야 하기 때문에 메모리 사용량은 Hash 기법보다 높을 수 있다.

- Hash 기법과 다르게 결합 키에 대한 동치 결합뿐만 아니라 부등호를 사용한 결합에도 사용할 수 있다.(단 <> 은 제외)

- 이론적으로 이미 정렬이 되어있으면 정렬 과정을 생략할 수 있지만, SQL 테이블의 물리적 레코드 위치가 정렬되어있을 때를 의미한다.

- 즉 SQL에서 테이블에 있는 레코드의 물리적인 위치를 알고 있을 때만 가능한 경우기 때문에 위 케이스는 구현 의존적이다.

- Soft Merge는 정렬을 생략할 수 있는 특수한 경우를 제외하곤 위 2개 기법보다 비싼 방법이기 때문에 가능하면 Nested Loops나 Hash 사용을 고려하자.

 

그 외에도 CROSS 결합이 있다고 한다.