SQL 레벨업 - 7장

21강 서브쿼리가 일으키는 폐해

1. 서브쿼리의 문제점: 실체적인 데이터를 저장하지 않음

- 연산 비용추가

  실체적 데이터를 저장하고 있지 않아서, 서브쿼리에 접근할 때 마다 SELECT 구문 실행해야함. -> 복잡할수록 실행 비용이 더 높아짐

- 데이터 I/O 비용 발생

  메모리 용량이 충분하지 않으면 오버헤드 발생. ( DBMS 가 저장소에 있는 파일에 결과를 쓸 수 있음)

- 최적화 불가

  서브쿼리로 만들어지는 데이터는 테이블과 차이가 없지만, 서브쿼리에는 메타 정보 (명시적 제약, 인덱스) 가 존재하지 않아서 옵티마이저  가 쿼리를 해석하기 위한 정보를 서브 쿼리에서 얻을 수 없음

 

2. 서브쿼리 의존증

고객의 구입 명세 정보를 저장하는 테이블(Receipts)에 순번(Seq 또는 AI idx) 필드는 오래전에 구입했을수록 낮은 값을 가진다.

 

ex)

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

 

구하고자 하는 값 : 고객별 최소 순번 레코드는?

 

1. 서브쿼리를 사용한 방법

고객들의 최소 순번 값을 저장하는 서브쿼리(R2)를 만들고, 기존의 Receipts 테이블과 결합하는 방법

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

 

단점

- 코드가 복잡해서 가독성이 떨어짐

- 성능이 떨어짐

• 서브쿼리는 대부분 일시적인 영역(메모리 or 디스크)에 확보되므로 오버헤드 발생
• 서브쿼리는 인덱스 또는 제약 정보를 가지지 않아 최적화되지 못함
• 이 쿼리는 결합을 필요로 하기 때문에 비용이 높고 실행 계획 변동 리스크가 발생
• Receipts 테이블에 스캔이 두 번 발생

--------------------------------------------------------------------------

2. 상관 서브 쿼리를 사용해도 해결할 수 없음. -> Receipts 테이블에 두 번 접근함.

SELECT user_id, seq, price
         FROM Receipts R1.
WHERE seq = (SELECT MIN(seq)
        FROM Receipts R2
     WHERE R1.user_id = R2.userId);

 

3. 윈도우 함수

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

 

ROW_NUMBER 윈도우 함수를 사용해 Receipts 테이블 접근을 1회 감소 가능

 

 

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

저장소의 I/O 부하를 감소시키는 것이 SQL 튜닝의 기본 원칙

 

결합의 불안정 요소

- 결합 알고리즘 변동 리스크

Nested Loops, Sort Merge, Hash 중 어떤 것을 선택할지는 테이블의 크기등을 고려하여 옵티마이저가 결정함.

레코드 수가 적은 테이블은 Nested Loops가 선택되기 쉽고, 큰 테이블의 경우에는 Sort Merge, Hash가 선택되기 쉬움.

 테이블 크기에 따라 실행 계획이 변동될 때 성능이 좋아질지 단언하기 어려움 - > 변동 리스크 발생

 

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

Netsed Loops의 내부 테이블 결합 키에 인덱스가 존재하면 성능이 크게 개선됨.

또한 Sort Merge, Hash가 선택되어 TEMP 탈락이 발생하는 경우에 작업 메모리를 늘려주면 성능을 개선 가능.

인덱스 유무, 작업 메모리의 크기( 트레이드 오프), 매개변수에 따라 성능차이 발생 

 

 

-----> 결과적으로, 옵티마이저가 이해하기 쉽게 쿼리를 단순하게 작성해야함

 

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

 

1. 서브쿼리 사용

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 min_seq
                FROM Receipts
                GROUP BY cust_id) R4
        ON R3.cust_id=R4.cust_id
        AND R3.seq=R4.min_seq) TMP_MAX
        ON TMP_MIN.cust_id=TMP_MAX.cust_id;

 

2. 테이블 접근과 결합을 줄이는 방식

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;

 

 

5. 서브쿼리 장점

1. 결합과 집약 순서

결합할 때 사람이 직접 연산 순서를 명시해주면 성능 개선 가능

 

1) 결합 후 집약

SELECT C.co_cd, MAX(C.district),
        SUM(emp_nbr) AS sum_emp
FROM Companies C
    INNER JOIN
        Shops S
    ON C.co_cd=S.co_cd
WHERE main_flg='Y'
GROUP BY C.co_cd;

 

2) 집약 후 결합하는 방법

SELECT C.co_cd, C.district, sum_emp
FROM Companies C
    INNER JOIN
        (SELECT co_cd, SUM(emp_nbr) AS sum_emp
        FROM Shops
        WHERE main_flg='Y'
        GROUP BY co_cd) CSUM
    ON C.co_cd=CSUM.co_cd;