7장 - 서브쿼리

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

서브쿼리의 문제점

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

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

 

<연산 비용 추가>

실체적인 데이터를 저장하고 있지 않은 서브쿼리에 접근할 때마다 SELECT 구문을 실행해서 데이터를 만들어야 한다는 뜻이다.

따라서 구문 실행에 발생하는 비용이 추가된다.

 

<데이터 I/O 비용 발생>

SELECT 구문의 연산 결과를 사용하기 위해서 메모리에 저장해야 한다.

만약 메모리의 공간이 충분하다면 상관 없지만, 아닐 경우 저장소에 저장할 때도 있다.

이를 TEMP 탈락 현상의 일종이라고 말할 수 있는데, 저장소에 접근하는 경우 속도가 크게 저하된다.

 

<최적화를 받을 수 없음>

명시적인 제약 또는 인덱스가 설정되어 있는 테이블과 달리, 서브쿼리는 그러한 메타 정보가 존재하지 않는다.

따라서 옵티마이저가 쿼리를 해석하기 위한 정보를 서브쿼리에서 얻을 수 없다.

 

<결론>

서브쿼리는 유연성으로 인해 코딩할 때 편리하지만, 복잡한 연산을 수행하거나 결과 크기가 큰 서브쿼리를 사용할 때는 성능 리스크를 고려해야 한다.

 

서브쿼리 의존증

-- 서브쿼리 예시
-- 고객별 최소 순번 레코드를 구하는 쿼리

select r1.cust_id, r1.seq, r1.price
from receipts r1
inner join (select cust_id, min(seq) min_seq
            from receipts
            group by cust_id) r2
      on r1.cust_id = r2.cust_id
      and r1.seq = r2.min_seq;

위 쿼리는 가독성이 좋지 않고 성능을 저하시키는 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);

상관 서브쿼리로 변경하면 결합이 사라지고 R2 접근에 기본 키의 인덱스 온리 스캔을 사용할 가능성도 있다.

하지만 그렇다고 해도 Receipts 테이블에 1회 접근하고 기본 키의 인덱스에 1회 접근한다.

그럼 어떻게 더 좋은 코드를 작성할까?

일단 Receipts 테이블에 대한 접근을 1회로 줄일 것이다.

SQL 튜닝에서 가장 중요한 부분이 I/O를 줄이는 것인데, 이를 위해 윈도우 함수를 사용할 수 있다.

-- row_number 윈도우 함수를 사용한 쿼리
select cust_id, seq, price
from (select cust_id, seq, price,
        row_number() over (partition by cust_id order by seq) row_seq
      from receipts) work
where work.row_seq = 1;

윈도우 함수에서 정렬을 사용하지만, 다른 쿼리에서도 MIN 함수를 사용했었으므로 큰 비용 차이가 발생하지 않는다.

리스크 관리

윈도우 함수를 사용한 쿼리가 얼마나 성능이 좋은지는, 사용하는 DBMS 또는 데이터베이스 서버의 성능, 매개 변수나 인덱스와 같은 환경 요인에 의해 크게 바뀔 수 있다.

하지만 저장소의 I/O 양을 감소시키는 것이 SQL 튜닝의 가장 기본 원칙이는 점에는 변함이 없다.

 

결합을 사용하면 실행 계획에 변동이 생길 수 있고, 상관 서브쿼리를 사용하면 ~

하지만 우리는 실행 계획이 단순할수록 성능이 안정적인 사실을 알아야 한다.

정말 서브쿼리 사용이 더 나은 경우는 없을까?

결합할 때는 최대한 결합 대상 레코드 수를 줄이는 것이 중요하다.

그런데 옵티마이저가 이러한 것을 잘 판별하지 못할 때는, 사람이 직접 명시해주면 성능적으로 좋은 결과를 얻을 수 있다.

 

예를 들어, 두 개의 테이블을 존재할 때 다음과 같은 선택지가 있다고 하자.

1. 결합부터 하고 집약하는 방법
2. 집약부터 하고 결합하는 방법

1번의 경우 2번에 비해 I/O 비용이 더 발생하고, 집약의 비용이 더 적게 소모된다.

하지만 TEMP 탈락이 발생하지 않는다면 집약의 비용은 괜찮은 트레이드오프가 될 수 있다.

 

따라서 다음과 같이 서브쿼리를 사용하여 먼저 집약을 하고, 결합하는 방법을 추천한다.

select c.co_cd, c.district, sum_emp
from companies c
inner join (select co_cd,
              sum(emp_nbr) sum_emp
            from shops
            where main_flg = 'Y'
            group by co_cd) csum
on c.co_cd = csum.co_cd;