서브쿼리가 일으키는 폐해
[서브쿼리의 문제점]
서브쿼리의 성능적 문제점의 원인은 서브쿼리가 실체적인 데이터를 저장하지 않는 점입니다.
문제점은 다음과 같습니다.
- 연산 비용 추가
- 서브쿼리에 접근할 때마다 SELECT 구문을 실행해야 한다.
- 데이터 I/O 비용 발생
- 서브쿼리의 연산 결과가 클 경우 저장소에 저장해야 한다.
- 최적화를 받을 수 없음
- 서브쿼리에는 메타 정보가 존재하지 않아 옵티마이저에 의한 최적화가 힘들다.
[서브쿼리 의존증]
예시를 통해 서브쿼리를 사용하면 좋지 않은 경우를 살펴보겠습니다.
CREATE TABLE Receipts
(
cust_id CHAR(1) NOT NULL,
seq INTEGER NOT NULL,
price INTEGER NOT NULL,
PRIMARY KEY (cust_id, seq)
);
| 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 |
위와 같은 테이블이 있다고 할 때 고객 별로 최소 순번 레코드를 구하는 코드를 구해야 합니다.
이 문제를 서브쿼리를 통해 풀면 다음과 같습니다.
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) AS R2
ON R1.cust_id = R2.cust_id AND R1.seq = R2.min_seq;
최소 순번 값을 저장하는 서브쿼리를 만들고 기존의 테이블과 결합하는 방법입니다.
위의 방법은 아이디어 자체는 간단하지만 코드가 복잡하고 성능이 떨어진다는 단점이 존재합니다.
코드의 성능이 나쁜 이유는 다음과 같습니다.
- 서브쿼리 결과를 위한 메모리나 저장소 영역이 필요하다.
- 서브쿼리는 메타 정보를 가지지 않기 때문에 최적화를 할 수 없다.
- 결합을 사용하기 때문에 비용이 높고 실행 계획의 변동 리스크가 발생한다.
- Receipts 테이블에 대해 스캔이 2번 필요하다.
MySQL 기준으로 위의 쿼리의 실행계획을 살펴보면 R2의 데이터를 보관하는 임시 테이블을 만들고 Nested Loops를 통해 테이블 간 결합을 하는 것으로 확인됐습니다.
이번에는 상관 서브 쿼리를 이용한 다른 쿼리를 살펴보겠습니다.
SELECT cust_id, seq, price
FROM Receipts R1
WHERE seq = (SELECT MIN(seq)
FROM Receipts R2
WHERE R1.cust_id = R2.cust_id);
이와 같이 서브쿼리의 결과를 등호를 통해 비교하여 결과를 나타내었습니다.
위의 실행 계획을 살펴보면 다음 특징이 존재합니다.
- 서브쿼리의 select_type이 DEPENDENT SUBQUERY로 표시된다.
- 서브쿼리를 실행할 때 PK(인덱스)를 이용한다.
- 테이블을 2번 접근한다.
1번째 특징의 경우 바깥 쿼리의 칼럼이 서브쿼리에 사용됐을 때 나타난다고 합니다. 바깥 쿼리의 칼럼 수만큼 서브쿼리가 실행되므로 SUBQUERY 보다 더 비효율적입니다.
서브쿼리를 실행할 때 인덱스를 사용하더라도 결국 테이블을 2번 접근하기 때문에 성능적으로 크게 개선된 것은 아닙니다.
마지막으로 윈도우 함수를 통해 테이블 접근 횟수를 1회로 낮춘 쿼리를 살펴보겠습니다.
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) AS WORK
WHERE WORK.row_seq = 1;
위와 같이 작성하면 WORK는 다음과 같이 나타납니다.
WORK
| 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 |
윈도우 함수를 통해 쿼리를 한번 더 실행하지 않을 수 있습니다.
실행 계획을 살펴보면 다음과 같은 특징이 존재합니다.
- 테이블에 대한 접근이 1회이다.
- 윈도우 함수의 정렬로 인해 EXTRA에 Using filesort로 표시된다.
윈도우 함수에서 정렬하는 부분이 존재하지만 다른 쿼리에서 집계 함수를 사용하기 때문에 이런 부분에서 비용 차이는 크지 않습니다.
3개의 쿼리를 비교했을 때 어떤 것이 성능이 좋은 지는 DBMS 환경, 서버의 성능 등에 따라 바뀔 수 있어 쉽게 단언하기는 힘듭니다.
하지만 저장소 I/O 양을 감소시키는 것이 SQL 튜닝의 가장 기본적인 원칙이라는 점은 변하지 않습니다.
또한 처음 쿼리에 비해 나머지 쿼리의 경우 결합이 사용되지 않아 성능 안정성을 확보할 수 있는 장점이 존재합니다.
결합을 사용하면 왜 성능이 불안정하게 나올까요?
이에 대한 답으로 다음 2가지가 존재합니다.
- 결합 알고리즘 변동 리스크
- 환경 요인에 의한 지연 리스크(인덱스, 메모리, 매개변수 등)
알고리즘 변동 리스크
초기에 테이블의 레코드 수가 적으면 보통 Nested Loops가 선택되다가, 점점 레코드가 늘어나면 Sort Merge나 Hash 알고리즘이 선택될 수 있습니다.
알고리즘 변경을 함에 따라 성능이 좋아지는 경우도 있지만 오히려 악화되는 경우가 있어 변동 리스크가 존재할 수 있습니다.
또한, 같은 실행 계획을 계속 선택하더라도 Sort Merge 나 Hash를 위한 메모리가 부족해지면 저장소를 사용하게 되어 성능이 대폭 떨어질 수 있는 리스크가 존재합니다.
윈도우 함수에서도 정렬을 이용하기 때문에 이와 같은 메모리 부족 문제가 발생할 수 있습니다.
다만, 윈도우 함수의 경우 SELECT 구문에서 실행되므로 레코드의 압축이 많이 된 상태에서 실행합니다.
이에 따라 정렬에 필요한 메모리가 그렇게 많이 필요하지 않습니다.
환경 요인에 의한 리스크
Nested Loops 알고리즘을 이용할 때는 내부 테이블에 인덱스를 이용하지 못하는 경우가 존재합니다.
또한 Sort Merge 나 Hash를 위한 메모리 증가는 한정된 리소스 내에서 항상 선택할 수 있는 것은 아닙니다.
결론,
결론적으로 결과만 제대로 나온다고 안주하지 말고 다음 사항들을 고려하여 쿼리를 짜는 것이 좋습니다.
- 실행 계획이 단순할수록 성능이 안정적이다.
- 엔지니어는 기능(결과)뿐만 아니라 비기능적인 부분(성능)도 보장할 책임이 있다.
서브쿼리는 항상 나쁠까?
위에서 서술된 대로 서브쿼리는 안좋은 점이 존재하지만, 항상 나쁜 것은 아닙니다.
예를 들어 서브 쿼리는 처음 쿼리를 짤 때 생각의 보조 도구로 유용합니다.
바텀업 방식으로 여러 부분을 합쳐 최종 결과물로 만드는 것은 쿼리를 짜기 쉽게 해줍니다.
하지만, 비절차 지향형 언어인 SQL과 바텀업 사고방식은 지향하는 방향과 성질이 달라, 코드 레벨에서 본다면 효율적이지 않을 수 있습니다.
[서브쿼리 사용이 더 나은 경우]
서브쿼리를 사용하는 것이 결합과 관련된 쿼리에서 오히려 성능이 더 나은 경우가 존재합니다.
결합은 두 대상 레코드의 수가 적게 하는 것이 유리합니다.
따라서 서브쿼리를 통해 대상 레코드 수를 적게하는 것이 이번에 살펴볼 내용입니다.
일대다로 연관된 두 테이블이 다음과 같이 존재합니다.
CREATE TABLE Companies
(
co_cd CHAR(3) NOT NULL,
district CHAR(1) NOT NULL,
CONSTRAINT pk_Companies PRIMARY KEY (co_cd)
);
CREATE TABLE Shops
(
co_cd CHAR(3) NOT NULL,
shop_id CHAR(3) NOT NULL,
emp_nbr INTEGER NOT NULL,
main_flg CHAR(1) NOT NULL,
CONSTRAINT pk_Shops PRIMARY KEY (co_cd, shop_id)
);
Companies(회사)
| co_cd(회사 코드) | district(지역) |
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
Shops(가맹점)
| co_cd(회사 코드) | shop_id(가맹점 ID) | emp_nbr(종업원 수) | main_flg(주요 가맹점 플래그) |
| 1 | 1 | 300 | Y |
| 1 | 2 | 400 | N |
| 1 | 3 | 250 | Y |
| 2 | 1 | 100 | Y |
| 2 | 2 | 20 | N |
| 3 | 1 | 400 | Y |
| 3 | 2 | 500 | Y |
| 3 | 3 | 300 | N |
| 3 | 4 | 200 | Y |
| 4 | 1 | 999 | Y |
회사마다 주요 사업소의 직원수를 지역과 함께 보여주는 쿼리는 어떻게 작성할까요?
이 결과를 얻는 방법에는 2가지 방법이 존재합니다.
- 먼저 결합을 하고 집약을 하는 경우
- 먼저 집약을 하고 결합을 하는 경우
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;
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) AS CSUM on C.co_cd = CSUM.co_cd;
두 쿼리의 실행 계획을 살펴보면 결합과 집약의 순서가 다른 것을 볼 수 있고, 결과도 똑같이 나옵니다.
하지만 성능적으로는 차이가 존재합니다.
1번째의 경우 결합 대상 레코드 수는 회사(4개), 가맹점(10개)입니다.
2번째의 경우는 회사(4개), 가맹점(4개)입니다.
2번째의 경우는 가맹점의 결과를 4개로 줄여 결합 비용을 더 낮출 수 있습니다.
가맹점의 레코드 수가 적어 현재는 차이가 많이 나지 않지만, 가맹점의 레코드 수가 100만 단위가 되면 결합 비용 차이가 매우 클 것입니다.
다만, 2번째의 경우 결합을 더 큰 레코드를 대상으로 하기 때문에 1번째에 비해 더 클 수 있어 Trade-Off를 고려해야 합니다.
환경을 고려해 실제로는 성능을 테스트하고 판단을 내려야 하는 것은 맞지만, 결합 전에 레코드 수를 줄인다라는 방법은 알아두는 것이 좋습니다.
궁금한점
쿼리를 최적화하지 못해 사용하는 뷰 머지는 뭘까?
뷰 머징이란?
옵티마이저는 최적화 쿼리 수행을 위해 서브 쿼리블록을 풀어서 메인 쿼리와 결합하려는 특성이 있는데, 이때의 특성을 가르킵니다.
따라서 집약 우선 코드에 대해서 결합을 먼저 실행하고 집약을 하는 경우를 볼 수 있습니다.
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) AS CSUM on C.co_cd = CSUM.co_cd;
그렇다면 왜 옵티마이저는 뷰 머징을 실행할까요?
가장 먼저 생각할 수 있는 것은 결합을 통해 레코드 수를 크게 줄일 수 있는 경우입니다.
이렇게 되면 전체적인 비용을 줄일 수 있기 때문에 뷰 머징을 실행할 수 있습니다.
또한, 서브쿼리의 GROUP BY를 위한 연산이나 조회를 효율적으로 하지 못하는 경우가 있습니다. 인덱스를 이용하지 못하는 등 다양한 경우가 있을 수 있습니다.
MySQL에서 왜 서브쿼리 대신 JOIN 문을 권장할까?
해당 글을 보면 MySQL 5.5까지는 서브쿼리의 최적화가 최악이라 실제 실행 속도가 매우 오래 걸리는 것을 볼 수 있습니다.
물론 MySQL 5.6부터는 서브쿼리가 내부적으로 JOIN으로 변환되지만 최적화가 제대로 안되는 조건도 존재하여 JOIN을 통해 성능 향상을 하도록 권하고 있습니다.
최적화 적용이 되는 경우
- IN(subquery) 또는 = ANY(subquery) 형태
- UNION 없는 단일 SELECT
- 집계함수 와 HAVING 절을 가지지 말아야
- 서브쿼리의 WHERE 조건이 외부쿼리의 다른 조건과 AND 로 연결
- 조인을 사용한 UPDATE 나 DELETE 가 이니어야
- 미리 수립된 실행계획을 사용하지 않는 경우(PreparedStatement 사용시 실행계획 재사용됨)
- 외부쿼리와 서브쿼리가 실제 테이블 사용(가상 테이블 사용시 세* 미조인 최적화 안됨)
- 외부쿼리와 서브쿼리가 straight_join 힌트 미사용
