💡 곤란한 부분은 분할해야만 할까?

• 서브쿼리
  • SQL 내부에서 작성되는 일시적인 테이블 (일시적 테이블을 영속화한 것이 뷰)
  • 테이블과 기능적 관점에서 차이 없음
  • SQL은 서브쿼리, 테이블을 같은 것으로 취급
• 개념정리
  • 테이블 : 영속적 데이터 저장
  • 뷰 : 영속적이지만 데이터 저장 X, 접근 마다 Select 구문 실행
  • 서브쿼리 : 비영속적인 생존 기간이 SQL 구문 실행 중으로 한정

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

서브쿼리 문제점

• 서브쿼리가 실체 데이터를 저장하지 않는다는 점에서 기인

연산비용 추가

• 서브쿼리 접근 마다 SELECT 구문 실행해서 데이터 만들어야 함
• SELECT 구문 실행에 발생하는 비용 추가

데이터 I/O 비용 발생

• 데이터 양이 큰 경우 DBMS 저장소 파일에 결과 기록하는 경우 존재
• TEMP 탈락 일종

최적화 받을 수 없음

• 테이블과 달리 명시적 제약이나 인덱스 같은 메타정보 부재
• 옵티마이저가 쿼리를 해석하기 위해 필요한 정보를 서브쿼리에서 얻을 수 없음

서브쿼리 의존증

• 서브쿼리 사용 코드 → Bad

  • 가독성 나쁨

  • 성능 나쁨
    - 서브쿼리는 대부분 일시적 영역(메모리 or 디스크)에 확보되므로 오버헤드 발생
    - 서브쿼리는 메타정보(인덱스, 제약조건)을 가지지 않기 때문에 최적화 X
    - 결합 필요하기 때문에 비용 높고, 실행계획 변동 리스크 발생
    - 테이블 스캔 2회 필요

    select r.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;

• 상관 서브쿼리 사용 코드 → Bad

  • 테이블 스캔 2회 필요

    select cust_id, seq, price
    from receipts r1
    where seq = (select min(seq)
    							from receipts r2
    							where r1.cust_id = r2.cust_id);

• 윈도우 함수로 결합 제거 → Good

  • SQL 튜닝에서 가장 중요한 I/O 줄이기 위해 윈도우 함수 적용

  • 윈도우 함수 사용으로 정렬이 실행계획에 추가되었긴 하지만, 튜닝에서 가장 중요한 건 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;

장기적 관점에서 리스크 관리

• 결합 사용 쿼리가 갖는 두 가지 불안정 요소

알고리즘 변동 리스크

• 레코드 수가 적으면 Nested Loops, 레코드 수가 큰 테이블 결합은 Hash or Sort Merge.
• 계속 운영하면서 레코드가 늘어나, 역치 넘으면 실행계획 변동 발생 → 성능 변화

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

• Nested Loops 사용 시 내부 테이블 결합 키에 인덱스 존재 여부
• Sort Merge, Hash 사용 시 워킹 메모리 증설로 성능 개선
• 결합을 사용한다는 것은 장기적 관점에서 고려할 리스크가 늘어난다는 것

기억할 것

• 실행 계획이 단순할 수록 안정적 성능
• 결과 뿐만 아니라 성능도 보장

서브쿼리는 정말 나쁠까?

• 서브쿼리를 사용하면 문제를 분할해서 생각하기 쉬워짐 → 생각의 보조 도구
• 서브쿼리는 집합을 작은 부분집합으로 나누는 기술 → 바텀업 사고방식과 찰떡
• 다만, SQL이 비절차 지향형 언어이므로, 바텀업과 상성이 좋지 않음

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

• 결합 관련 쿼리에서 결합 대상 레코드를 줄여 성능개선 하고 싶을 때
• 사람이 직접 연산순서를 서브쿼리로 명시

예제

• 회사 : 사업소 = 1 : N (부모 자식 관계)

1번 방법. 결합 먼저, 집약 나중에

select c.co_cd, max(c.distinct), 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_id;

2번 방법. 집약 먼저, 결합 나중에

• 1번 방법보다 결합 대상 레코드 수가 줄어듬
  • 집약이 먼저 수행되기 때문
  • 결합 대상 레코드 수를 집약하면 SQL 성능 개선 핵심인 I/O 비용을 감소시킬 수 있음

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;

---

정리

• 서브쿼리는 복잡한 문제를 분할할 수 있는 편리한 도구
• 서브쿼리는 결합 늘려 성능 악화, 안정성 악화하는 악마
• SQL 성능은 I/O 지분 절대적
• 서브쿼리 + 결합 → 윈도우 함수 가 답이다
  • 꼭 그렇지 않을때도 있다.
• 결합 대상 레코드 수를 사전에 압축해서 성능 개선 가능

---

연습 문제

💡 집약 우선 쿼리에 결합 우선 실행계획을 적용하는 `뷰 머지`를 옵티마이저가 선택하는 이유

• 결합을 먼저 사용해서 레코드 수를 크게 줄일 가능성이 있을때
• 효율적 접근 가능한 조건 또는 인덱스가 존재하지 않을 경우
  • 결합에 사용되는 테이블에 대한 인덱스 + Nested Loops 가 더 효율적이라 판단할 수 있다.
  • 파티션을 사용하는 경우에도 비슷한 상황 가능