SQL 레벨업 - 7장 서브쿼리 (1/2)
21강. 서브쿼리가 일으키는 폐해
1. 서브쿼리의 문제점
서브쿼리의 성능적 문제는 실체적인 데이터를 저장하고 있지 않다는 점이다. 따라서 다음과 같은 문제가 발생한다.
- 연산 비용 추가
실체적인 데이터를 저장하고 있지 않기 때문에 서브쿼리에 접근할 때 마다 SELECT 구문을 실행해서 데이터를 만들어야 한다는 뜻이다. 즉, SELECT 구문 실행에 발생하는 비용이 추가되며 복잡할수록 실행비용이 더 높아진다.
- 데이터 I/O 비용 발생
연산 결과는 어딘가에 저장하기 위해 써두어야 한다. 메모리 용량이 충분하다면 이러한 오버헤드가 적지만, 데이터양이 큰 경우에는 DBMS가 저장소에 있는 파일에 결과를 쓸 때도 있다. (일종의 TEMP 탈락)
- 최적화를 받을 수 없다
서브쿼리로 만들어지는 데이터는 구조적으로 테이블과 차이가 없다. 그러나 명시적 제약, 인덱스가 작성되어 있는 테이블과 달리 서브쿼리에는 메타 정보가 하나도 존재하지 않는다. 따라서 옵티마이저가 쿼리를 해석하기 위해 필요한 정보가 없다.
2. 서브쿼리 의존증
고객의 구입 명세 정보를 저장하는 테이블(Receipts)에 순번(Seq 또는 AI idx) 필드는 오래전에 구입했을수록 낮은 값을 가진다.
이때 고객별 최소 순번을 구하는 상황을 가정 하자.
처음 직면하는 문제점은 최소 순번이 고객마다 다르다는 것이다.
user_id | seq | price
--------*-----*------
A | 1| 500
B | 5| 100
C | 10| 600
구하고자 하는 답
만약 구하고자 하는 최소값이 1이라는 보장이 있다면 seq = 1 조건을 통해 답을 도출할 수 있다. 하지만 없으므로 동적으로 구해야 한다.
- 서브쿼리를 사용한 방법
간단하게 고개들의 최소 순번 값을 저장하는 서브쿼리(R2)를 만들고, 기존의 Receipts 테이블과 결합하는 방법이 있다.
SELECT R1.user_id, seq, R1.price
FROM Receipts R1
INNER JOIN
(SELECT user_id, MIN(seq) AS min_seq
FROM Receipts
GROUP BY user_id) R2
ON R1.user_id = R2.user_id
AND R1.seq = R2.min_seq;
이 방법은 간단하지만 두 가지의 단점을 가지고 있다.
- 코드가 복잡해서 읽기 어렵다.
- 성능이 떨어진다.
- 서브쿼리는 대부분 일시적인 영역(메모리 또는 디스크)에 확보 되므로 오버헤드가 생긴다.
- 서브쿼리는 인덱스 또는 제약 정보가 없기 때문에 최적화 되지 못한다.
- 이 쿼리는 결합을 필요로 하기 때문에 비용이 높고 실행 계획 변동 리스크가 발생한다.
- Receipts 테이블에 스캔이 두 번 발생한다.
- 상관 서브쿼리는 답이 될 수 없다
SELECT user_id, seq, price
FROM Receipts R1
WHERE seq = (SELECT MIN(seq)
FROM Receipts R2
WHERE R1.user_id = R2.userId);
상관 서브쿼리를 이용한 방법
상관 서브쿼리를 사용하더라도 Receipts 테이블에 두 번 접근하게 된다.
Receipts 테이블에 접근 1회와 기본 키의 인덱스 접근 1회가 필요하다. 결국 성능적인 장점이 없다.
- 윈도우 함수로 결합을 제거
먼저 개선해야 하는 부분은 Receipts 테이블에 대한 접근을 1회로 줄이는 것이다. 접근을 줄이기 위해 ROW_NUMBER()함수를 사용한다.
SELECT user_id, seq, price
FROM (SELECT user_id, seq, price,
ROW_NUMBER()
OVER (PARTITION BY user_id
ORDER BY seq) AS row_seq
FROM REceipts) WORK
WHERE WORK.row_seq = 1;
ROW_NUMBER 함수로 각 사용자의 구매 이력에 번호를 붙이고 그 번호를 조건을 걸어 조회 하는 쿼리이다. 이 쿼리를 통해 Receipts 테이블에 한번만 접근하게 된다.
- 장기적 관점에서의 리스크 관리
최초의 쿼리와 상관 서브쿼리를 사용한 쿼리에 비해 윈도우 함수를 사용한 쿼리가 얼마나 성능이 좋은지는 여러 환경에 의해 단언하기 어렵다.
하지만 저장소의 I/O 양을 감소시키는 것이 SQL 튜닝의 가장 기본 원칙이다.
처음 사용한 쿼리와 비교해보면 결합을 제거했다. 따라서 단순 성능 향상뿐만 아니라 성능의 안정성 확보도 기대 가능하다.
결합을 사용한 쿼리는 두 개의 불안정 요소가 있다.
- 결합 알고리즘의 변동 리스크
- 환경 요인에 의한 지연 리스크(인덱스, 메모리, 매개변수 등)
- 알고리즘 변동 리스크
결합 알고리즘에는 크게 Nested Loops, Sort Merge, Hash 세 가지 종류가 있다. 어떤 것을 선택할지는 테이블의 크기등을 고려하여 옵티마이저가 결정한다. 레코드 수가 적은 테이블은 Nested Loops가 선택되기 쉽고, 큰 테이블의 경우에는 Sort Merge, Hash가 선택되기 쉽다.
따라서 처음에는 레코드가 적어 Nested Loops를 사용하다가 어느 역치를 넘어서면 실행계획에 변동이 생긴다. 이 경우 성능이 좋아지는 경우도 있겠지만, 나빠지는 경우도 많다. 결국 결합을 사용하면 이러한 변동 리스크를 안을 수 밖에 없다.
또한 데이터양이 많아지면서 Sort Merge, Hash에 필요한 메모리가 부족해지면 일시적으로 저장소를 사용한다. 그 시점에는 성능이 대폭 떨어진다.
- 환경 요인에 의한 지연 리스크
Netsed Loops의 내부 테이블 결합 키에 인덱스가 존재하면 성능이 크게 개선된다. 또한 Sort Merge, Hash가 선택되어 TEMP 탈락이 발생하는 경우에 작업 메모리를 늘려주면 성능을 개선할 수 있다.
하지만 항상 인덱스가 존재하는 것이 아니고, 메모리 튜닝은 한정된 리소스 내부에서 트레이드 오프를 발생시킨다.
즉 장기적으로 고려해야할 리스크를 늘리게 된다는 뜻이다.
따라서 옵티마이저가 이해하기 쉽게 쿼리를 단순하게 작성해야 한다.
요약
- 실행 계획이 단순할수록 성능이 안정적이다.
- 엔지니어는 기능뿐만이 아니라 비기능적인 부분도 보장해야한다