[SQL 레벨업] Ch.7 서브쿼리

Manx·2022년 8월 18일
0

SQL 레벨업

목록 보기
8/11

서브쿼리란 SQL 내부에서 작성되는 일시적인 테이블이다.
테이블과 서브쿼리는 기능적인 관점에서 차이가 없기 때문에 SQL은 두 가지를 모두 같은 것으로 취급한다.

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

서브쿼리의 문제점

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

연산 비용 추가

  • 데이터를 저장하고 있지 않아, 서브쿼리에 접근할 때마다 SELECT 구문을 실행해서 데이터를 만들어야 한다.

데이터 I/O 비용 발생

  • 연산 결과는 어딘가에 저장하기 위해 써두어야 한다.
  • 데이터양이 큰 경우에는 DBMS가 저장소에 있는 파일에 결과를 쓸 때도 있다.
  • TEMP 탈락 현상이 발생해 접근 속도가 급격하게 떨어진다.

최적화를 받을 수 없음

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

서브쿼리의 폐해

예제 테이블

cust_id(고객 ID)seq(순번)price(구입 가격)
A1500
A21000
A3700
B5100
B65000
B7600
C20200
C3150

구입 시기가 오래될수록 순번(seq)는 작은 값을 갖는다.
이때, 고객별 최소 순번 레코드를 구해보자.

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

첫번째 방법 : 최소 순번 값을 저장하는 서브쿼리(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;

단점
1. 서브쿼리를 사용하면 코드가 여러 계층에 걸쳐 만들어져 가독성이 떨어진다.
2. 성능

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

해결 방안

윈도우 함수로 결합을 제거

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회로 줄일 수 있다.

결합을 사용한 쿼리의 단점은 6장에서 정리했으므로 생략하겠다.

응용 - 최대값과 최소값의 차이

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;

max_seq와 min_seq는 다른 테이블에 존재하기 때문에 뺄셈할 수 없지만, GROUP BY cust_id로 한 개의 레코드로 집약을 통해 가능했다.

서브쿼리에 비해 읽기 쉽고, 테이블의 스캔 횟수가 4회에서 1회로 감수했다.
서브쿼리는 너무 길어서 안쓰도록 하겠다. (궁금하면 책보기)

profile
백엔드 개발자

0개의 댓글