1. 서브쿼리란?
1.1 테이블 vs. 서브쿼리 vs. 뷰
| 종류 | 설명 |
| 테이블 | 영속적인 데이터를 저장 |
| 서브쿼리 | 비영속적인 생존 기간(scope)이 SQL 구문 실행 중으로 한정 |
| 뷰 | 영속적이지만 데이터는 저장하지 않음 따라서 접근할 때마다 SELECT 구문이 실행됨 |
1. 기능적인 관점에서 보면 테이블과 서브쿼리는 전혀 차이가 없다.
2. 반면 비기능적인(특히 성능적인) 관점에서 보면 큰 차이가 있다.
3. 서브쿼리(또는 뷰)는 테이블에 비해 성능이 안 좋은 경향이 있다.
이 세 개의 문장을 잘 기억해두고 세세한 부분을 살펴보자.
2. 서브쿼리가 일으키는 폐해
2.1 서브쿼리의 문제점
서브쿼리는 실체적인 데이터를 저장하고 있지 않기 때문에
1. 연산 비용 추가 : 접근할 때마다 SELECT 구문 실행 -> 실행 비용 발생
2. 데이터 I/O 비용 발생 : 메모리 용량이 부족하다면 TEMP 탈락 현상에 따라 DBMS가 저장소에 있는 파일에 결과 작성
3. 최적화 불가 : 구조적으로는 테이블과 차이가 없지만 명시적인 제약 또는 인덱스와 같은 메타 정보가 하나도 존재하지 않음 -> 옵티마이저가 쿼리 해석 불가
와 같은 문제들이 발생한다.
물론 서브쿼리는 유연하게 사용할 수 있다는 편리함이 있지만 내가 구현하고자 하는 내용이 정말 서브쿼리가 꼭 필요한지에 대해 먼저 고민이 선행되는 것이 쿼리 성능을 높이는 방법이라고 할 수 있다.
2.2 서브쿼리 의존증
고객의 구입 명세 정보를 기록하는 테이블(Receipts)이 있다.
순번(seq) 필드는 구입 시기가 오래될수록 작은 값을 갖는다.
이 때 고객별 최소 순번 레코드를 구하는 경우를 생각해보자.
원하는 결과는 아래와 같다.
2.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;
1. 최소 순번 값을 저장하는 서브쿼리를 만들고 (R2)
2. 기존의 Receipt 테이블과 결합한다.
그러나 이 방법은 성능적으로 좋지 않은 방법이다.
✔️ 서브쿼리는 대부분 일시적인 영역(메모리 또는 디스크)에 확보되므로 오버헤드가 생긴다.
✔️ 서브쿼리는 인덱스 또는 제약 정보를 가지지 않기 때문에 최적화되지 못한다.
✔️ 이 쿼리는 결합을 필요로 하기 때문에 비용이 높고 실행 계획 변동 리스크가 발생한다.
✔️ Receipts 테이블에 스캔이 두 번 필요하다. -> mySql 에서는 1번 실행
위에서도 언급했던 서브쿼리의 문제점들이다.
이를 실행계획에서도 살펴볼 수 있다.
🪶 테이블 풀스캔 1번 실행
🪶 GROUP BY 연산 수행 -> Index Range Scan 활용 (인덱스를 사용해 효율적으로 데이터 조회하는 방식)
🪶 Unique Key Lookup 실행 -> 단일 키 값으로 직접 데이터를 조회하는 방식
🪶 전체 쿼리 비용 : 4.35
이 쿼리의 경우 Full Table Scan으로 인해 성능 저하 가능성이 있다. 이를 개선하기 위해서는 적절한 인덱스 생성 등의 최적화가 필요하다.
2.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);
실행계획도 함께 살펴보자.
🪶 테이블 풀스캔 1번 수행
🪶 서브쿼리 : Non-Unique Key Lookup 이라는 액세스 방식 채택 -> 인덱스 사용보다 효율성 떨어짐
🪶 외부 쿼리에서 각 고객에 대한 작업을 진행하고 내부 서브쿼리에서는 외부 쿼리의 각 고객마다 해당 고객의 seq 값 중 가장 작은 값을 찾는 작업이 실행되어야 함 -> 즉 외부 쿼리의 고객 수만큼 내부 서브쿼리 실행
🪶 전체적인 쿼리 비용 : 1.65 (작을수록 효과적)
이 실행계획만 보면 그리 좋지 않은 쿼리이다. (물론 서브쿼리를 쓴 방법보다 전체 쿼리 비용은 많이 감소했다.)
이를 개선하기 위해서는 인덱스를 활용하는 등 쿼리 최적화 작업이 필요하다.
2.2.3 윈도우 함수를 사용한 방법 (결합 제거)
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;
실행 계획을 살펴보자.
🪶 Non-Unique Key Lookup 연산을 통해 테이블 스캔
🪶 윈도우 함수인 ROW_NUMBER()를 사용해 고객별로 seq 값을 오름차순으로 정렬 후 순번 매기기
🪶 WHERE 절에서 순번이 1인 행만 선택
🪶 전체적인 쿼리 비용 : 0.45
가장 효율적인 쿼리로 보인다. 서브쿼리 없이 윈도우 함수를 활용하여 단일 쿼리로 처리할 수 있기 때문이다,
*TO DO*
1. 서브쿼리, 상관서브쿼리, 윈도우함수를 각각 썼을 때 성능의 차이가 드러나는 사례를 만들고 각각의 쿼리를 만들어서 실행계획 비교하는 자료 필요!!
사례 선정 : 직원들의 부서별 최대 급여자 확인하기
테이블 :
방법 1) 서브쿼리
쿼리 :
SELECT E.emp_id, E.dept_id, E.salary
FROM Employees E
WHERE E.salary = (
SELECT MAX(salary)
FROM Employees
WHERE dept_id = E.dept_id
);
결과 :
실행계획 :
🪶 테이블 풀스캔 1번 -> 6개 행 스캔
🪶 서브쿼리는 Employees 테이블에 대한 Non-Unique Key Lookup을 수행해 2개의 행 스캔
🪶 전체적인 쿼리 비용 : 0.85
방법 2) 상관 서브쿼리
쿼리 :
SELECT E.emp_id, E.dept_id, E.salary
FROM Employees E
WHERE NOT EXISTS (
SELECT 1
FROM Employees E2
WHERE E2.dept_id = E.dept_id
AND E2.salary > E.salary
);
실행 계획 :
🪶 테이블 풀스캔 2번 -> 각각 6개 행 스캔
🪶 서브쿼리는 hash join을 사용
🪶 서브쿼리는 Employees 테이블에 대한 Non-Unique Key Loopup을 수행해 1개의 행 스캔
🪶 전체적인 쿼리 비용 : 4.70
방법 3) 윈도우 함수
쿼리 :
SELECT emp_id, dept_id, salary
FROM (
SELECT emp_id, dept_id, salary,
RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
FROM Employees
) ranked
WHERE rnk = 1;
실행 계획 :
🪶 Non-Unique Key Lookup 연산을 통해 테이블 스캔 -> 1개의 행 스캔
🪶 윈도우 함수인 RANK()를 사용
🪶 서브쿼리는 각 부서 내에서 직원들을 급여 순으로 랭킹하고, 메인 쿼리는 랭킹 1위인 직원 선택
🪶 전체적인 쿼리 비용 : 0.35
3. 서브쿼리 의존증
고객이 가지는 순번의 최솟값을 가진 레코드를 찾아봤는데 이번에는 최댓값을 가지는 레코드와 함께, 양쪽 price 필드의 차이도 구해보자.
3.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;
서브쿼리를 사용해 구해본 쿼리이다.
최솟값의 집합을 먼저 찾고, 최댓값의 집합을 찾은 뒤에, 고객 ID를 키로 결합했다.
쿼리가 매우 길어지고, 가독성 또한 떨어지는 것을 확인할 수 있다.
실행 계획도 살펴보자.
🪶 테이블 풀 스캔 1번 -> 4개의 행 처리, GROUP 이라는 추가 처리 진행
🪶 R1 이라는 기본 키를 사용해 1개의 행 조회 -> UNIQUE KEY LOOKUP 연산으로 처리
🪶 auto_key0 이라는 키를 사용해 2개의 행 조회 -> NON-UNIQUE KEY LOOKUP 연산으로 처리 -> GROUP 이라는 추가 처리 진행
🪶 R3 라는 기본 키를 사용해 1개의 행 조회 -> UNIQUE KEY LOOKUP 연산으로 처리
🪶 전체적인 쿼리 비용 : 9.95
테이블 풀스캔, 유니크 키 조회, 비유니크 키 조회 등의 연산이 사용되고, GROUP이라는 추가 처리 단계도 있다. 여러 단계의 복잡한 작업을 수행하고 있으며 전체 쿼리 비용도 높은 편이므로 쿼리를 개선해야 한다.
3.2 개선 포인트
개선해보자.
여기서 개선 포인트는 '테이블 접근과 결합을 줄이기' 이다.
이를 위해 추가로 CASE식도 사용했다.
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;
실행계획과 함께 봐보자.
🪶 테이블 풀스캔 1번 -> 13개의 행 처리
🪶 이 결과는 tmp table 이라는 임시 테이블로 저장
🪶 전체 쿼리비용 : 3.96
이 쿼리는 상당히 효율적인 것으로 보인다. 서브쿼리와 윈도우 함수를 사용해 단일 쿼리로 원하는 결과를 얻고 있다.
테이블 풀스캔을 하긴 하지만 13개의 행만 처리하므로 전체 쿼리 비용이 3.96으로 낮게 나타난다.
서브쿼리 사용이 언제나 성능을 안 좋게 만드는가? 에 대한 의문은 다음으로 이어지는 내용에서 해소할 수 있다.
다음은 서브쿼리 사용이 더 나은 경우에 대해 얘기해보자.
4. 결합과 집약 순서
[ Companies ]
[ Shops ]
4.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;
테이블 full index scan -> 4개 행 작업
4개 행을 co_cd 기준으로 group 작업
shops 테이블과 inner join 수행
전체 쿼리 비용 : 2.05
4.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;
shops 테이블에 full index scan -> 10개의 행 작업
10개 행을 co_cd 기준으로 group 작업 -> 집약 (sum)
companies 테이블과 unique key lookup으로 join 수행
전체 쿼리 비용 : 3.42
쿼리 복잡도 : 첫 번째 쿼리가 조인 이후 집계를 하므로 더 복잡
조인을 먼저 수행 시 관련 행만 처리하면 되지만 모든 행을 집계한 후 조인을 하면 상대적으로 비효율적이다
'DATABASE > 📖 SQL 레벨업' 카테고리의 다른 글
| [ SQL 레벨업 ] 4장. 집약과 자르기 (0) | 2024.12.03 |
|---|