서브쿼리 의존증

서브쿼리의 문제점

서브쿼리의 성능적 문제는 결과적으로 서브쿼리가 실체적인 데이터를 저장하고 있지 않다는 점에서 기인합니다. 이에 따라서 다음과 같은 문제가 발생합니다.

 

• 연산 비용 추가

실제적인 데이터를 저장하고 있지 않다는 것은 서브쿼리에 접근할 때마다 SELECT 구문을 실행해서 데이터를 만들어야 한다는 뜻입니다. 따라서 SELECT 구문 실행에 발생하는 비용이 추가됩니다. 서브쿼리의 내용이 복잡하면 복잡할수록 이러한 실행 비용은 더 높아집니다.

 

• 데이터 I/O 비용 발생

연산 결과는 어딘가에 저장하기 위해 쌓아두어야 합니다. 메모리 용량이 충분하다면 이러한 오버헤드가 적지만, 데이터양이 큰 경우에는 DBMS가 저장소에 있는 파일에 결과를 쓸 때도 있습니다. TEMP 탈락 현상의 일종인데 저장소 성능에 따라 접근 속도가 급격하게 떨어질 수 있습니다.

 

• 최적화를 받을 수 없음

서브쿼리로 만들어지는 데이터는 구조적으로 테이블과 차이가 없습니다.하지만 명시적인 제약 또는 인덱스가 작성되어 있는 테이블과 달리 서브쿼리에는 그러한 메타 정보가 하나도 존재하지 않습니다. 따라서 옵티마이저가 쿼리를 해석하기 위해 필요한 정보를 서브쿼리에서 얻을 수 없습니다.

 

즉, 서브쿼리는 유연성으로 인해 코딩을 할 때 편리하지만 성능 리스크를 고려해야 합니다. 해당 내용이 정말 서브쿼리를 사용하지 않으면 구할 수 없는 것인지를 항상 생각해야 합니다.

 

 

서브쿼리 의존증 예시

---

고객의 구입 명세 정보를 기록하는 테이블에서 고객별 최소 순번 레코드를 구하는 경우를 생각해 봅니다.

 

• 서브쿼리 조인을 사용하는 경우

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

 

위 SQL은 Receipts 테이블에 2번 접근하며, 서브쿼리는 대부분 일시적인 영역에 확보되므로 오버헤드가 생깁니다. 서브쿼리는 인덱스 또는 제약 정보를 가지고 있지 않으므로 최적화되지 못하며, 결합이 필요하므로 비용이 높고 실행 계획 변동 리스크가 발생합니다.

 

 

• 상관 서브쿼리를 사용하는 경우

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

 

R2 접근에 기본 키의 인덱스 온리 스캔(index only scan)을 사용할 가능성도 있습니다. 하지만 그렇다고 해도 Receipts 테이블에 접근 1회와 기본 키 인덱스 접근 1회가 필요합니다. 따라서 결합과 성능적으로 장점이 없습니다.

 

 

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

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;

 

Receipts 테이블에 접근을 1회로 줄였습니다. SQL 튜닝에서 가장 중요한 부분이 I/O를 줄이는 것입니다. 접근을 줄이려면 윈도우 함수 ROW_NUMBER를 사용합니다. 이것으로 고객들의 최초 구매 이력을 쉽게 찾을 수 있습니다.

 

 

서브쿼리 사용이 더 나은 경우

---

서브쿼리가 항상 나쁜 것은 아닙니다. 결합과 관련된 쿼리에서 서브쿼리는 성능이 나을 수 있습니다. 결합할 때 최대한 결합 대상 레코드 수를 줄이는 것이 중요합니다. 그런데 옵티마이저가 이러한 것을 잘 판별하지 못할 때는, 사람이 직접 연산 순서를 명시해 주면 좋은 결과를 얻을 수도 있습니다.

 

회사의 주요 사업소의 직원수를 구하는 쿼리를 만들어보겠습니다.

 

• 결합을 먼저 실행

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

 

 

• 집약을 먼저 실행

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

 

첫 번째 방법은 회사 테이블과 사업소 테이블의 결합을 먼저 수행하고, 결과에 GROUP BY를 적용해서 집약했습니다. 반면 두 번째 방법은 먼저 사업소 테이블을 집약해서 직원 수를 구하고, 회사 테이블과 결합했습니다. 두 가지 방법은 모두 같은 결과를 만들어내므로 기능적 관접이 같지만, 성능적인 측면에서는 다릅니다.

 

결합을 먼저 한 경우 회사 테이블 레코드 4개와 사업소 테이블 10개를 결합하지만, 집약을 먼저 한 경우에는 회사 테이블 레코드 4개와 사업소 테이블 4개를 결합합니다. 첫 번째 방법보다 두 번째 방법이 개수가 더 적으므로 결합 비용을 낮출 수 있습니다. 만약에 데이터의 갯수가 커지면 성능 차이가 훨씬 크게 나타납니다. 즉, 테이블의 규모가 매우 크다면 일단 결합 대상 레코드 수를 집약하는 편이 I/O 비용을 더 줄일 수 있습니다. 튜닝 선택지 중 하나로 '사전에 결합 레코드 수를 압축한다'라는 방법을 알아두면 좋습니다.

 

 

참고

* SQL 레벨업