서브쿼리

서브쿼리란 ??

SQL 내부에서 작성되는 일시적인 테이블이다 -> 이를 영속화한 것이 뷰 ! 

사실 테이블과 서브쿼리는 기능적인 관점에서는 전혀 차이가 없다. 사용자는 자신이 다루는 대상 테이블이 테이블인지 뷰인지 서브쿼리인지 따로 구분하지 않고 사용할 수 있다. 하지만 각각의 차이를 정리해보면 다음과 같다.

 

테이블 : 영속적인 데이터 저장

뷰 : 영속적이지만 데이터는 저장하지 않음, 따라서 접근할 때마다 SELECT 구문이 실행됨 

서브쿼리 : 비영속적인 생존 기간(스코프)이 SQL 구문 실행 중으로 한정

 

기능적인 유연성 때문에 서브쿼리는 SQL 코딩에서 빈번하게 사용이 되지만 테이블에 비해 성능이 나쁜 경향이 있다. 
밑에서는 서브쿼리를 사용할 때 신경써야 할 점 과 문제 패턴을 분석해볼 것이다. 

 

1. 문제점 

가장 근본적인 문제점 -> 실체적인 데이터를 저장하고 있지 않다는 점

 

1. 연산 비용 추가 
▶ 데이터를 저장하지 않기 때문에 접근할 때마다 실행해줘야 한다는 것
▶ 복잡하면 복잡할 수록 비용이 높아짐 

 

2. 데이터 I/O 비용 발생
▶ 연산 결과를 어딘가에 저장하기 위해 써두어야 한다. 메모리 용량이 충분하면 오버헤드가 적지만 데이터양이 큰 경우 DBMS가 저장소에 있는 파일에 결과를 쓸 대도 있다. (TEMP 탈락 현상) -> 저장소 성능에 따라 접근 속도가 급격히 떨어짐

 

3. 최적화를 받을 수 없음 
▶ 구조적으로 테이블과 차이가 없다. 하지만 명시적인 제약 또는 인덱스가 작성되어 있는 테이블과 달리 서브쿼리에는 그러한 메타 정보가 없다. (옵티마이저가 쿼리를 해석하기 위해 필요한 정보가 없다) 

 

 

2. 의존증 

서브쿼리의 유연성과 편리성 때문에 서브쿼리에 의존하는 경우가 많다. 먼저 서브쿼리를 사용하지 않는 편이 성능적으로 좋은 경우와 어떤 경우에 위험한지 알아보자.

 

구입 명세 테이블 

cust_id(고객 ID)	seq(순번)	price(구입 가격)
A	1	500
A	2	1000
A	3	700
B	5	100
B	6	5000
B	7	300
B	9	200
B	12	1000
C	10	600
C	20	100
C	45	200
C	70	50
D	3	2000

고객별 최소 SEQ를 구하는 경우를 생각해보자. 

 

서브쿼리를 사용한 방법 

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
    IN R1.cust_id = R2.cust_id
    AND r1.seq = R2.min_seq;

R2

cust_id(고객 ID)	seq(순번)
A	1
B	5
C	10
D	3

->즉, R1 + R2 = Result_table

cust_id(고객 ID)	seq(순번)	price(구입 가격)
A	1	500
B	5	100
C	10	600
D	3	2000

 

이렇게 방식은 간단하지만 단점이 있다. 

1. 코드가 복잡해서 읽기 어렵다. -> 쉬운 것인데 어려워보인다. 
2. 성능
▶서브쿼리는 대부분 일시적인 영역에 확보되므로 오버헤드가 생긴다.
▶서브쿼리는 인덱스 또는 제약 정보를 가지지 않기 때문에 최적화하지 못함
▶결합이 필요하므로 비용이 높고 실행 계획 변동 리스크가 발생
▶Receipts 테이블에 스캔이 두번이 필요한다.

 

이런건 흔히 상관 서브쿼리로 해결할려고 하지만 할 수 없다. 

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

상관서브트리를 사용하더라도 결국 Receipts 테이블에 접근이 두번 발생하는걸 볼 수 있다. 

 

-> 그렇다면 더 좋은 코드는 어떻게 작성해야 할까??

 

▶ 윈도우 함수로 결함을 제거하는 방법이 있다. 

SQL 튜닝에서 가장 중요한 부분이 바로 I/O를 줄이는 것이다. R 테이블에 대한 접근을 1회로 줄여야 한다. 

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

 

cust_id(고객 ID)	seq(순번)	price(구입 가격)	row_seq
A	1	500	1
A	2	1000	2
A	3	700	3
B	5	100	1
B	6	5000	2
B	7	300	3
B	9	200	4
B	12	1000	5
C	10	600	1
C	20	100	2
C	45	200	3
C	70	50	4
D	3	2000	1

테이블을 늘림으로서 seq 필드의 최솟값이 불확실해 쿼리를 한 번 더 사용해야 했던 이전 문제가 해결 됨

쿼리도 간단해지고 가독성도 올라갔다. 

row_seq = WORK 테이블 

 

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

상관 서브쿼리와 결합을 사용한 쿼리 

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

- 환경 요인에 의한 지연 리스크 (인덱스, 메모리, 매개변수 등)

의 불안정 요소가 있다. 

 

알고리즘 변동 리스크

옵티마이저가 테이블의 크기 등을 고려해 어떤 알고리즘을 선택할지 결정한다.

(ex. 레코드 수가 적은 테이블 -> Nested Loops

큰 테이블들을 결합하는 경우 -> Sort Merge or Hash) 

처음에는 테이블의 레코드 수가 적었으나 점점 늘어나 역치를 넘으면 옵티마이저는 실행 계획을 변동시킨다. 

-> 좋아지는 경우도 있지만 대부분 악화된다. 즉 결국 결합을 사용하면 이러한 변동 리스크를 안고 갈 수 밖에 없다. 

 

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

Nested Loops의 내부 테이블 결합 키에 인덱스가 존재하면 성능이 크게 개선된다. 또 sort Merge or Hash 가 선택되어 TEMP 탈락이 발생하는 경우 작업 메모리를 늘려주면 성능을 개선할 수 있다. 하지만 항상 결합 키에 인덱스가 존재하는 것은 아니다. 또한 메모리 튜닝은 한정된 리소스 내부에서의 트레이드 오프를 발생시킨다. 

 

-> 결합을 사용한다는 것은 곧 장기적 관점에서 고려해야할 리스크를 늘리게 된다는 뜻

 

즉 우리가 기억해야 할 것은 2가지이다.

1. 실행 계획이 단순할수록 성능이 안정적이다.

2. 기능(결과)뿐만 아니라 성능도 보장할 책임이 있다.

 

▶ 레코드 간 비교에서도 결합은 불필요

SELEECT 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 min_seq FROM Receipts) WORK
WHERE WORK.min_seq = 1 OR WORK.max_seq = 1 GROUP BY cust_id;

 

이러면 서브쿼리는 WORK 하나이다. 결합도 없다. 신경써야할 부분은 내림차순으로 정렬하는 부분 뿐이다. 

 

 

 

하지만 서브쿼리 자체가 나쁜 것은 아니다. 
서브쿼리를 사용하면 문제를 분할하여 생각하기가 쉬워지는 만큼 생각의 보조 도구라고 할 수 있다. 즉 집합을 세세한 부분으로 나누는 기술이므로, 각 부분을 조합해서 최종적인 결과 집합을 만들어내는, 바텀업 타입의 사고방식과 굉장히 좋은 상성을 가지고 있다. 

다만 코드 레벨에서 본다면 실제로 효율적이지 못한 것뿐이다. 

 

 

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

=> 결합과 관련된 쿼리에서 더 좋다. 결합할 때는 최대한 결합 대상 레코드 수를 줄이는 것이 중요한데 옵티마이저가 이러한 것을 잘 판별하지 못할 때는 사람이 직접 연산 순서를 명시해주면 성능적으로 좋은 결과를 얻을 수 있다. 

 

결합과 집약 순서 <- 이때는 서브쿼리를 사용하자.

 

회사T.

co_cd(회사 코드)	district(지역)
001	A
002	B
003	C
004	D

사업소 T.

co_cd(회사 코드)	shop_id(사업소 id)	emp_nbr(종업원 수)	main_flag(사업 플래그)
001	1	300	Y
001	2	400	N
001	3	250	Y
002	1	100	Y
002	2	20	N
003	1	400	Y
003	2	500	Y
003	3	300	N
003	4	200	Y
004	1	999	Y

 

-> 회사마다 사업 플래그가 y인 직원의 수를 구해 결과를 얻어보는 것

 

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_flag = '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_flag = 'Y' GROUP BY co_cd) CSUM
ON C.co_cd = CSUM.co_cd;

 

생각해야할 것은 결합 대상 레코드 수를 줄이는 것이다. -> 1번째 방법 = 10개 , 2번째 방법 = 4개로 2번째 방법이 더 좋다.