021. 서브쿼리가 일으키는 폐해 - llighter/database GitHub Wiki

SQL 레벨업

1. 서브쿼리의 문제점

서브쿼리의 성능적 문제는 결과적으로 서브쿼리가 실체적인 데이터를 저장하고 있지 않다는 점에서 기인한다.

이에 따라 다음과 같은 세 가지 문제가 발생한다.

- 연산 비용 추가

서브쿼리에 접근할 때마다 SELECT구문을 실행해서 데이터 생성 -> 실행 비용 증가

- 데이터 I/O 비용 발생

데이터양이 큰 경우 등에는 DBMS가 저장소에 있는 파일에 결과를 쓸 때도 있다.(TEMP 탈락 현상의 일종)

Oracle에서는 일시 테이블 영역(temp segment) 파일에 서브쿼리의 결과를 쓴다.

- 최적화를 받을 수 없음

명시적인 제약 또는 인덱스가 작성되어 있는 테이블과 달리, 서브쿼리에는 그러한 메타 정보가 하나도 없다. 따라서 옵티마이저가 쿼리를 해석하기 위해 필요한 정보를 서브쿼리에서 얻을 수 없다.

2. 서브쿼리 의존증

고객의 구입 명세 정보를 기록하는 테이블(Receipts)이 있다. 고객별 최소 순번(seq) 레코드를 구하는 경우를 생각해보자.

- 서브쿼리를 사용한 방법

-- 고객들의 최소 순번 값을 저장하는 서브쿼리(R2)를 만들고 기존의 테이블과 결합
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;

이 방식의 두 가지 단점

  1. 코드가 복잡해서 읽기 어렵다.
  2. 성능, 성능이 나쁜 이유 4가지
    1. 서브쿼리는 대부분 일시적인 영역에 확보되므로 오버헤드가 생긴다.
    2. 서브쿼리는 인덱스 또는 제약 정보를 가지지 않기 때문에 최적화 되지 못한다.
    3. 이 쿼리는 결합을 필요로 하기 때문에 비용이 높고 실해 계획 변동 리스크가 발생한다.
    4. Receipts 테이블에 스캔이 두 번 필요하다.

- 상관 서브쿼리는 답이 될 수 없다.

-- 상관 서브쿼리를 사용한 방법
SELECT cust_id, seq, price
  FROM Receipts R1
  WHERE seq = (SELECT MIN(seq)
                 FROM Receipts R2
                 WHERE R1.cust_id = R2.cust_id);

상관 서브쿼리를 사용한다 하더라도 Receipts 테이블에 접근이 두 번 발생한다.

- 윈도우 함수로 결합을 제거

SQL 튜닝에서 가장 중요한 부분이 바로 I/O를 줄이는 것이다.

-- 윈도우 함수를 사용한 방법
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;

3. 장기적 관점에서의 리스크 관리

결합을 사용한 쿼리가 가지는 두 개의 불안정 요소

  • 결합 알고리즘의 변동 리스크
  • 환경 요인에 의한 지연 리스크(인덱스, 메모리, 매개변수 등)

- 알고리즘 변동 리스크

결합 알고리즘은 크게 Nested Loops, Sort Merge, Hash 라는 세 가지 중류가 있다. 이들중에서 어떤 것을 선택할 지는 테이블의 크기 등을 고려해서 옵티마이저가 자동으로 결정한다.

테이블 레코드 개수가 적으면 Nested Loops, 개수가 늘어나면서 Sort Merge나 Hash 등으로 변동될 수 있다. 이때 성능에 큰 변화가 일어난다.

또한 같은 실행 계획이 계속해서 선택되는 경우에도 문제가 있다. 데이터양이 많아지면서 Sort Merge 또는 Hash에 필요한 메모리가 부족해지면 일지적을 저장소를 사용한다. 결국 그 시점을 기준으로 성능이 대폭 떨어지게 된다.(TEMP 탈락 현상)

- 환경 요인에 의한 지연 리스크

결합 쿼리는 성능이 비선형적으로 악화될 리스크를 가진다.

기억해야할 사항

  • 실행 계획이 단순할수록 성능이 안정적이다.
  • 엔지니어는 기능(결과)뿐만 아니라 비기능적인 부분(성능)도 보장할 책임이 있다.

4. 서브쿼리 의존증 - 응용편

고객이 가지는 순번의 최솟값과 최댓값을 통해 양쪽 price 필드의 차이를 구해보자

- 다시 서브쿼리 의존증

-- 서브쿼리 의존증 환자 2호
SELECT TMP_MIN.cust_id,
  TMP_MIN.price - TMP_MAX.price AS diff
FROM (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) TMP_MIN
    INNER JOIN
    (SELECT R3.cust_id, R3.seq, R3.price
        FROM Receipts R3
          INNER JOIN
            (SELECT cust_id, MAX(seq), AS max_seq
              FROM Receipts
             GROUP BY cust_id) R4
          ON R3.cust_id - R4.cust_id
          AND R3.seq = R4.max_seq) TMP_MAX
    ON TMP_MIN.cust_id = TMP_MAX.cust_id;
  • 쿼리가 굉장히 길어지고 가독성도 좋지 않다.
  • 테이블에 대한 접근도 2배가 되어 4번 이루어 진다.

- 레코드 간 비교에서도 결합은 불필요

-- 윈도우 함수와 CASE식
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 DEST) AS max_seq
     FROM Receipts) WORK
WHERE WORK.min_seq = 1
OR WORK.max_seq = 1
GROUP BY cust_id;
  • 이렇게 하면 서브쿼리는 WORK 하나뿐이다.
  • 결합도 발생하지 않는다.
  • 최솟값과 최댓값의 레코드를 식별하기 위해 ROW_NUMBER 함수를 사용하였다.
  • 최댓값을 뽑을 때 내림차순(ORDER BY seq DESC)으로 정렬하였다.
  • GROUP BY cust_id를 통해 집약한 결과에 CASE식을 통해 최솟값과 최댓값을 다른 필드에 할당해준다.
  • Receipts 테이블의 스캔 횟수 1회
  • 윈도우 함수로 정렬이 2회 발생하지만 결합을 반복하는 것보다 저렴하고 실행 게획의 안정성도 확보할 수 있다.

5, 서브쿼리는 정말 나쁠까?

서브쿼리를 사용하면 문제를 분할하여 생각하기가 쉬워지는 만큼 생각의 보조 도구 라고 할 수 있다.

서브쿼리는 집합을 세세한 부분으로 나누는 기술이므로, 각 부분을 조합해서 최종적인 결과 집합을 만들어내는, 바텀업(bottom-up) 타입의 사고방식과 굉장히 좋은 상성을 가지고 있다.

다만 코드 레벨에서 본다면 실제로 효율적인 코드가 되지 않는다.