SQL 레벨업 Ch.4~5 — 집약과 자르기, 반복문

4장은 이해하기 쉬운 내용이라 새로 알게 된 것만 간단히 적고, 5장과 한 글로 묶었다.

Ch.4 집약과 자르기

GROUP BY의 실행 계획

GROUP BY로 집약할 때 DBMS는 정렬보다 해시 알고리즘을 주로 사용한다. 경우에 따라 정렬을 쓰기도 하지만 해시가 더 빠르고, 해시의 성질상 GROUP BY 키의 유일성이 높을수록 효율적으로 동작한다.

해시(또는 정렬)에 쓸 워킹 메모리가 충분하지 않으면 저장소의 파일을 사용하게 되어 매우 느려진다. Oracle은 정렬과 해시에 PGA라는 메모리 영역을 쓰는데, 집약 대상 데이터에 비해 PGA가 부족하면 일시 영역(저장소)으로 부족분을 채운다. 이 현상을 ’TEMP 탈락’이라고 한다.

PARTITION BY

GROUP BY에서 집약 기능을 빼고 자르는 기능만 남긴 것이 윈도우 함수의 PARTITION BY 구다. GROUP BY는 입력 집합을 집약해 전혀 다른 레벨의 출력으로 바꾸지만, PARTITION BY는 입력에 정보를 추가할 뿐이라 원본 레코드가 그대로 유지된다.

Ch.5 반복문

SQL은 반복문을 의도적으로 언어 설계에서 제외했다. 그래서 흔히 다음과 같은 방식으로 반복을 구현한다. 이를 반복계라고 하고, 반복 없이 한 번에 처리하는 방식을 포장계라고 한다.

레코드에 하나씩 접근하는 SELECT 구문을 반복 실행한다.
호스트 언어에서 반복 처리한 뒤 테이블에 갱신한다.

반복계의 단점

1. 성능

레코드 수가 적을 때는 반복계가 빠를 수도 있지만, 레코드가 많아질수록 포장계와의 차이가 벌어진다. SQL 한 번을 실행할 때마다 다음 오버헤드가 붙기 때문이다.

SQL 구문을 네트워크로 전송
데이터베이스 연결
SQL 구문 파스
실행 계획 생성 및 평가
결과 집합을 네트워크로 전송

1번과 5번은 같은 LAN 안이라면 밀리초 단위라 큰 문제가 아니고, 2번은 커넥션 풀로 해결된다. 문제는 3번과 4번이다. 파스는 SQL을 받을 때마다 실행되므로 작은 SQL을 여러 번 실행하는 반복계에서는 오버헤드가 커질 수밖에 없다.

2. 병렬 분산이 어렵다

반복계는 1회 처리를 극도로 단순화하기 때문에 리소스를 분산하는 병렬 처리 최적화가 통하지 않는다. 한 번에 접근하는 데이터양이 너무 적어 I/O를 병렬화하기 어렵다.

3. DBMS 진화의 혜택을 받지 못한다

DBMS 버전이 오를수록 옵티마이저와 아키텍처가 개선되지만, 그 중심은 대규모 데이터를 다루는 복잡한 SQL을 빠르게 만드는 데 있다. 단순한 SQL을 반복하는 반복계는 이 혜택을 거의 받지 못한다.

단, 포장계가 빠르다는 것은 SQL이 충분히 튜닝되어 있다는 전제 위의 이야기다. 반복계의 단순한 SQL은 튜닝 여지가 거의 없고, 포장계의 SQL은 복잡한 만큼 튜닝 가능성이 크다. 그 복잡함이 포장계의 단점이기도 하다.

반복계의 장점

실행 계획의 안정성: SQL이 단순해 실행 계획도 단순하고, 운용 중에 실행 계획이 바뀌어 갑자기 느려질 위험이 거의 없다. 뒤집어 말하면 이것이 포장계의 약점이다.
트랜잭션 제어의 편리함: 트랜잭션 단위를 세밀하게 제어할 수 있다. 일정 반복 횟수마다 커밋해 두면 중간에 오류가 나도 그 지점 근처부터 다시 처리할 수 있다.

SQL에서 반복을 표현하는 방법

SQL에서 반복을 대신하는 수단은 CASE 식과 윈도우 함수다. 다음은 회사별로 직전 연도와 매출을 비교해 +, -, =를 기록하는 쿼리다.

INSERT INTO Sales2
SELECT company, year, sale,
       CASE SIGN(sale - MAX(sale)
                          OVER (PARTITION BY company
                                    ORDER BY year
                                     ROWS BETWEEN 1 PRECEDING
                                              AND 1 PRECEDING))
            WHEN 0 THEN '='
            WHEN 1 THEN '+'
            WHEN -1 THEN '-'
            ELSE NULL END AS var
  FROM Sales;

SIGN: 양수면 1, 음수면 -1, 0이면 0을 반환한다.
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING: 현재 레코드의 1개 이전부터 1개 이전까지, 즉 직전 레코드 하나만 가리킨다.

같은 회사의 직전 매출을 가져와 현재 매출과의 차이를 SIGN으로 부호화하는 방식이다.

반복 횟수가 정해지지 않은 경우: 재귀 쿼리

이사할 때마다 레코드를 추가하고, 새 우편번호(new_pcode)로 이전 주소와 다음 주소를 연결해 둔 테이블이 있다. 이렇게 데이터를 줄줄이 잇는 구조를 포인터 체인, 이를 사용하는 테이블 형식을 인접 리스트 모델이라고 한다. 여기서 가장 오래된 주소를 찾고 싶다.

SQL에서 이런 계층 구조를 따라가는 방법 중 하나가 재귀 공통 테이블 식이다.

WITH RECURSIVE Explosion (name, pcode, new_pcode, depth)
AS
(SELECT name, pcode, new_pcode, 1
   FROM PostalHistory
  WHERE name = 'A'
    AND new_pcode IS NULL -- 검색 시작점
 UNION
 SELECT Child.name, Child.pcode, Child.new_pcode, depth + 1
   FROM Explosion AS Parent, PostalHistory AS Child
  WHERE Parent.pcode = Child.new_pcode
    AND Parent.name = Child.name)
-- 메인 SELECT 구문
SELECT name, pcode, new_pcode
  FROM Explosion
 WHERE depth = (SELECT MAX(depth)
                  FROM Explosion);

처음에는 이해하기 어려워 다시 공부했다. 구조는 “초기식 UNION 재귀식”이고, 재귀식의 WHERE 조건에 맞는 레코드가 더 이상 없으면 반복이 끝난다. 여기서는 Parent.pcode = Child.new_pcode AND Parent.name = Child.name 조건으로 연결된 이전 주소가 있는지 찾고, 있으면 depth를 1 늘린다. depth가 최대인 레코드가 가장 오래전에 살던 주소다.

PostgreSQL의 실행 계획에서는 다음 두 가지가 눈에 띈다.

Recursive Union: 재귀 연산. 몇 번을 이사했더라도 대응할 수 있는 유연한 쿼리다.
WorkTable: Explosion 뷰에 여러 번 접근하므로 일시 테이블로 만들어 둔다.

일시 테이블과 PostalHistory 테이블은 인덱스를 사용한 Nested Loops로 결합되므로 꽤 효율적인 계획이다.

정리

RDB에서 고성능을 얻으려면 절차 지향적인 사고의 편향에서 벗어나야 한다.
동시에 반복계와 포장계의 장단점을 따져 어느 쪽을 택할지 냉정하게 판단해야 한다.
SQL의 강력한 도구와 튜닝 방법을 활용하려면 집합 지향의 사고방식이 필요하다.

5장부터 쿼리를 이해하기가 점점 힘들어지고 내용이 급격히 어려워졌다.