서브쿼리의 단점
1. 실체적인 데이터를 저장하지 않아 문제 발생
실제 데이터를 저장하지 않고 쿼리를 실행하기 때문에 문제가 발생할 수 있습니다.
2. 연산 비용 추가
서브쿼리에 접근할 때마다 SELECT 구문을 실행하여 데이터를 생성해야 합니다. 내용이 복잡할수록 실행 비용이 급증합니다.
3. 데이터 I/O 비용 발생
메모리 용량이 충분하지 않거나 연산 결과가 클 경우, 저장소의 파일에 결과를 저장하여 오버헤드가 커질 수 있습니다.
4. 최적화 불가
옵티마이저가 쿼리를 해석하기 위해 필요한 정보(명시적인 제약, 인덱스 등)를 서브쿼리에서 얻을 수 없습니다.
결론: 내부적으로 복잡한 연산을 수행하거나 결과 크기가 큰 서브쿼리를 사용할 때는 성능 리스크를 고려해야 합니다.
2. 서브쿼리 의존증
문제: 고객별로 최소 순번을 가진 레코드 구하기
1) 서브쿼리 사용
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;
SQL
복사
•
코드가 여러 계층에 걸쳐 작성되어 가독성이 떨어집니다.
•
서브쿼리는 대부분 일시적인 영역에 할당되어 오버헤드가 발생합니다.
•
인덱스와 제약 정보가 없어 최적화가 불가능합니다.
•
결합이 필요하여 비용이 높고 실행 계획이 변경될 수 있습니다.
•
Receipts 테이블을 2번 스캔합니다.
2) 상관 서브쿼리
SELECT cust_id, seq, price
FROM Receipts R1
WHERE seq=(SELECT MIN(seq)
FROM Receipts R2
WHERE R1.cust_id=R2.cust_id);
SQL
복사
Receipt 테이블을 2번 스캔해야 하므로 서브쿼리 방식과 비교했을 때 성능적으로 큰 차이가 없음
3) 윈도우 함수
SELECT cust_id, price
FROM (SELECT cust_id, price,
ROW_NUMBER()
OVER(PARTITION BY cust_id
ORDER BY seq) AS row_seq
FROM Receipts) WORK
WHERE WORK.row_seq=1;
SQL
복사
ROW_NUMBER 윈도우 함수를 사용해 Receipts 테이블 접근을 1회 감소
→ I/O를 줄임
3. 장기적 관점에서의 리스크 관리
SQL 튜닝의 기본 원칙은 저장소의 I/O 부하를 감소시키는 것입니다.
결합의 불안정 요소
•
결합 알고리즘 변동 리스크
◦
테이블 크기에 따라 실행 계획이 변동되는데, 이러한 변동 리스크로 인해 성능이 좋아지거나 악화될 수 있습니다. 이는 성능적으로 좋지 않습니다.
◦
대략적으로 말하면, 레코드 수가 적은 테이블이 포함된 경우에는 Nested Loops가 선택되기 쉽고, 큰 테이블들을 결합하는 경우에는 Sort Merge 또는 Hash가 선택되기 쉽습니다.
◦
Sort Merge 또는 Hash에 필요한 메모리가 부족해지면 일시적으로 저장소를 사용합니다. 결국 그 시점을 기준으로 성능이 대폭 떨어지게 되지요(TEMP 탈락현상)
•
환경 요인에 의한 자연 리스크
◦
인덱스의 유무나 작업 메모리의 크기에 따라 성능 차이가 발생합니다.
◦
Sort Merge 또는 Hash가 선택되어 TEMP 탈락이 발생하는 경우 작업 메모리를 늘려주면 성능을 개선할 수 있습니다. 하지만 항상 결합 키에 인덱스가 존재하는 것은 아닙니다.
장기적 관점에서 고려해야 할 리스크가 증가하게 됩니다.
결합 쿼리는 성능이 비선형적으로 악화될 가능성이 있습니다.
실행 계획이 단순할수록 성능이 안정적입니다.
4. 서브쿼리 의존증 - 응용편
Receipts 테이블에서 최댓값을 가지는 레코드와 price 필드의 최대, 최소 차이 구하기
1) 서브쿼리 사용
SELECT TMP_MIN.cust_id,
TMP_MIN.price - TMP_MAX.price AS diff
FROM (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) TMP_MIN
INNER JOIN
(SELECT R3.cust_id, R3.seq, R3.price
FROM Receipts R3
INNER JOIN
(SELECT cust_id, MAX(seq) AS min_seq
FROM Receipts
GROUP BY cust_id) R4
ON R3.cust_id=R4.cust_id
AND R3.seq=R4.min_seq) TMP_MAX
ON TMP_MIN.cust_id=TMP_MAX.cust_id;
SQL
복사
•
TMP_MIN: 최솟값의 집합, TMP_MAX: 최댓값의 집합
•
최솟값의 집합과 최댓값의 집합을 고객 ID를 키로 결합
•
테이블 접근 4회 발생
2) 테이블 접근과 결합을 줄이는 방식
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;
SQL
복사
•
단일 테이블에 대한 셀프 조인이 필요한 서브쿼리는 윈도우 함수를 통해 조인을 줄여 io를 줄일 수 있다.
•
min_seq는 seq 값이 작은 순서대로, max_seq는 값이 큰 순서대로 정렬하여 순번을 매김
•
min_seq과 max_seq에서 가장 윗 번호인 1번 레코드만 가져와 차이를 구함
•
CASE 식으로 최솟값과 최댓값을 다른 필드에 할당
5. 서브쿼리 장점?
쿼리를 처음 고민할 때, 서브쿼리를 사용하여 문제를 분할하면 생각하기 쉬워집니다. 이는 집합을 세세한 부분으로 나누는 기술로, bottom-up 타입의 사고방식과 일맥상통합니다. 단, 이는 효율적인 코드가 되지는 않습니다.
서브쿼리 사용이 더 나은 경우
1. 결합과 집약 순서
결합할 때 사람이 직접 연산 순서를 명시해주면 성능 개선 가능
여러 사업소가 한 회사에 속함
문제: 회사(district)마다 주요 사업소의 총 직원수(sum_emp)를 구하기
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_flg='Y'
GROUP BY C.co_cd;
SQL
복사
회사 테이블과 사업소 테이블의 결합 수행 후
결과에 GROUP BY 적용해 집약
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_flg='Y'
GROUP BY co_cd) CSUM
ON C.co_cd=CSUM.co_cd;
SQL
복사
사업소 테이블 집약해 직원수 구한 후 회사 테이블과 결합
실행 계획을 보아 결합(Nested Loops) 과 집약(HashAggregate)의 조작 순서가 서로 다르다는 것을 알 수 있음
희사 테이블의 규모에 비해 사업소 테이블의 규모가 매우 크다면, 일단 결합 대상 레코드 수를 집약하는 편이 I/O 비용을 더 줄일 수 있습니다.
회사 테이볼 : 레코드 1,000개
사업소 테이블 (WHERE main_flg='Y') : 레코드 500만 개
사업소 테이블(CSUM) : 레코드 1,000개
SQL
복사
물론 두 번째 방법에서는 집약 비용이 첫 번째 방법보다 클 것입니다. 하지만 TEMP 탈락이 발생하지 않는다면 괜찮은 트레이드오프입니다.
여기서 말하는 Temp 탈락
→ 더 큰 테이블을 aggregate를 하면서 발생하는 워킹 메모리 활용 → temp 탈락할 확률이 존재함
집약 후 결합하는 방법이 결합 비용을 낮출 수 있어 성능적으로 좋음
- 서브쿼리는 복잡한 문제를 분할할 수 있는 편리한 도구지만, 결합을 늘리는 성능 악화를 일으킬 수 있음
- SQL 성능을 결정하는 요인은 1/0가 절대적
- 서브쿼리와 결합을 윈도우 함수로 대체하면 성능을 개선할 가능성이 있음
- 서브쿼리를 사용할때는 결합 대상 레코드 수를 사전에 압축해서 성능을 개선할 수 있음