• sql
  • subquery
  • query-optimization
  • book

SQL 레벨업 Ch.7 — 서브쿼리

서브쿼리가 성능을 떨어뜨리는 이유와, 윈도우 함수로 결합과 테이블 접근 횟수를 줄이는 방법을 정리했다.

Manx4분 읽기
시리즈 · BackEnd 서적5 / 17
  1. SQL 레벨업 Ch.1 — DBMS 아키텍처
  2. SQL 레벨업 Ch.3 — SQL의 조건 분기
  3. SQL 레벨업 Ch.4~5 — 집약과 자르기, 반복문
  4. SQL 레벨업 Ch.6 — 결합
  5. SQL 레벨업 Ch.7 — 서브쿼리
  6. SQL 레벨업 Ch.8 — SQL의 순서
  7. SQL 레벨업 Ch.9 — 갱신과 데이터 모델
  8. SQL 레벨업 Ch.10 — 인덱스 사용
  9. 오브젝트 Ch.1 — 객체, 설계
  10. 오브젝트 Ch.2 — 객체지향 프로그래밍
  11. 오브젝트 Ch.3~4 — 역할, 책임, 협력 / 설계 품질과 트레이드오프
  12. 오브젝트 Ch.5 — 책임 할당하기
  13. 오브젝트 Ch.6 — 메시지와 인터페이스
  14. 오브젝트 Ch.8 — 의존성 관리하기
  15. 오브젝트 Ch.9 — 유연한 설계
  16. 오브젝트 Ch.10, 12 — 상속과 코드 재사용, 다형성
  17. 오브젝트 Ch.13 — 서브클래싱과 서브타이핑

서브쿼리는 SQL 내부에서 만들어지는 일시적인 테이블이다. 기능적으로는 테이블과 차이가 없어 SQL은 둘을 같은 것으로 취급한다.

  • 테이블: 영속적으로 데이터를 저장한다.
  • 뷰: 정의는 영속적이지만 데이터는 저장하지 않는다. 접근할 때마다 SELECT 구문이 실행된다.
  • 서브쿼리: 비영속적이며 생존 기간(스코프)이 SQL 구문 실행 중으로 한정된다.

서브쿼리의 문제점

성능 문제는 서브쿼리가 실체적인 데이터를 저장하고 있지 않다는 점에서 나온다.

  • 연산 비용 추가: 접근할 때마다 SELECT 구문을 실행해 데이터를 만들어야 한다.
  • 데이터 I/O 비용 발생: 연산 결과를 어딘가에 써 두어야 한다. 데이터양이 크면 저장소의 파일에 쓰게 되고(TEMP 탈락), 접근 속도가 급격히 떨어진다.
  • 최적화를 받을 수 없음: 제약과 인덱스가 있는 테이블과 달리 서브쿼리에는 메타 정보가 없어, 옵티마이저가 쿼리 해석에 필요한 정보를 얻지 못한다.

예제: 고객별 최소 순번 레코드

cust_id(고객 ID) seq(순번) price(구입 가격)
A 1 500
A 2 1000
A 3 700
B 5 100
B 6 5000
B 7 600
C 20 200
C 3 150

구입 시기가 오래될수록 순번(seq)이 작다. 고객별로 순번이 가장 작은 레코드를 구해 보자.

서브쿼리와 결합을 사용한 방법

고객별 최소 순번을 구하는 서브쿼리(R2)를 만들고 원래의 Receipts 테이블과 결합한다.

SELECT R1.cust_id, R1.seq, R1.price
  FROM Receipts R1
       INNER JOIN
       (SELECT cust_id, MIN(seq) AS min_seq
          FROM Receipts
         GROUP BY cust_id) R2
    ON R1.cust_id = R2.cust_id
   AND R1.seq = R2.min_seq;

이 쿼리의 단점은 다음과 같다.

  • 코드가 여러 계층으로 만들어져 가독성이 떨어진다.
  • 서브쿼리가 일시적인 영역(메모리 또는 디스크)에 확보되어 오버헤드가 생긴다.
  • 서브쿼리는 인덱스나 제약 정보가 없어 최적화되지 못한다.
  • 결합이 필요해 비용이 높고 실행 계획 변동 리스크가 있다.
  • Receipts 테이블 스캔이 2번 발생한다.

윈도우 함수로 결합 제거

SELECT cust_id, seq, price
  FROM (SELECT cust_id, seq, price,
               ROW_NUMBER()
                 OVER (PARTITION BY cust_id
                           ORDER BY seq) AS row_seq
          FROM Receipts) WORK
 WHERE WORK.row_seq = 1;

ROW_NUMBER로 고객별 구매 이력에 순번을 붙이고 1번만 고른다. 결합이 사라지고 테이블 접근이 1회로 줄어든다.

응용: 최소 순번과 최대 순번의 가격 차이

SELECT cust_id,
       SUM(CASE WHEN min_seq = 1 THEN price ELSE 0 END)
         - SUM(CASE WHEN max_seq = 1 THEN price ELSE 0 END) AS diff
  FROM (SELECT cust_id, price,
               ROW_NUMBER() OVER (PARTITION BY cust_id
                                      ORDER BY seq) AS min_seq,
               ROW_NUMBER() OVER (PARTITION BY cust_id
                                      ORDER BY seq DESC) AS max_seq
          FROM Receipts) WORK
 WHERE WORK.min_seq = 1
    OR WORK.max_seq = 1
 GROUP BY cust_id;

최소 순번의 가격과 최대 순번의 가격은 서로 다른 레코드에 있어 바로 뺄 수 없지만, GROUP BY cust_id로 한 레코드로 집약하면 계산할 수 있다.

책에 실린 서브쿼리 버전과 비교하면 읽기 쉽고, 테이블 스캔 횟수도 4회에서 1회로 줄어든다.