요약
- 문제
- 운영 환경에서 조회 API 응답 지연(1초 이상) 이슈 발생
- 상관 서브쿼리로 인한 N+1 패턴이 DB 레벨에서 발생
- 과정
- 실행 계획 분석을 통해 Nested Loop 반복 실행과 옵티마이저 통계 오류를 확인
- 상관 서브쿼리를 CTE + Hash Join 구조로 리팩토링
- 성과
- 서브쿼리 실행 92회에서 1회로 감소
- 총 실행 비용 246.0에서 40.6로 감소 (약 83% 개선, 6배 성능 향상)
- 운영 데이터 기준으로 실행 계획 변화와 성능 개선을 수치로 검증
문제 배경
운영 중인 서비스에서 특정 테넌트 기준 조회 API의 응답지연이 보고되었다. 슬로우 쿼리 분석 결과, 직원을 그룹화하는 테이블의 조회 쿼리 내부에 상관 서브쿼리(Correleated Subquery)가 포함되어있었다. 그 영향으로 메인 쿼리 결과 행 수만큼 서브쿼리가 반복 실행되는 문제가 발생했다.
이로 인해:
- 메인 결과가 늘어날수록 쿼리 비용이 선형 이상으로 증가
- 특정 테넌트에서는 1초 이상 응답 지연 발생
원인 분석
상관 서브쿼리(Correlated Subquery)
쿼리는 작성하기에 따라 메인(Main)이 되는 쿼리와 서브(Sub)가 되는 쿼리로 나눠질 수 있다. 메인 쿼리는 실행 결과의 중심이 되고, 서브 쿼리는 메인 쿼리 내부에 속해 조건이나 데이터로 사용된다. 예를 들어, 직원 중 평균 연봉보다 높은 연봉 받는 직원들의 연봉를 조회하고자 한다. 다음과 같이 쿼리를 작성할 수 있다.
-- [메인 쿼리]
SELECT employee_name, salary
FROM employees
WHERE salary > (
-- [서브 쿼리]
SELECT AVG(salary)
FROM employees
);
여기서 직원마다 연봉를 보여주는 쿼리는 메인 쿼리이며, WHERE 절에 들어가 '연봉 평균'을 계산하여 하나의 스칼라값을 반환하여 조건으로 사용되는 쿼리를 서브쿼리라고 할 수 있다. 서브 쿼리는 작성되는 위치(여기서는 WHERE)에 따라 분류되기도 하며, 여기서는 서브 쿼리는 실행 방식에 따른 분류에 집중해보려고 한다. 실행 방식은 두 가지가 있다:
- 비 상관 서브쿼리 (Uncorrelated Subquery)
- 상관 서브쿼리 (Correlated Subquery)
비 상관 서브쿼리는 메인 쿼리와 독립적으로 실행되는 쿼리이며, 위 예시에 해당된다. '연봉 평균'을 먼저 집계하여 독립적으로 계산하고 이를 메인 쿼리에 넘긴다. 메인 쿼리는 이를 조건으로 사용한다. 어찌보면 독립적으로 실행되는것이 아닌 의존적으로 실행되는 것으로 생각할 수 있다. 왜냐하면 '연봉 평균'을 계산해야 메인 쿼리가 실행될 수 있기 때문이다. 이는 '데이터를 의존한다' 관점에서 맞는 말이다.
하지만, 데이터베이스에서 쿼리의 '상관'관계는 서브가 메인을 '참조'하냐?에 달려있다. 더 이해하기 위해 아래 예시 플로우를 살펴본다.
[ 1단계: 서브 쿼리 실행 (딱 1회) ]
(계산 중...) ➡ 결과: 5000
⬇
⬇ (이 값을 메인 쿼리에 전달)
⬇
[ 2단계: 메인 쿼리 실행 ]
---------------------------------
| 사원명 | 연봉 | 비교 ( > 5000?) |
|--------|------|----------------|
| 철수 | 4000 | No |
| 영희 | 6000 | Yes |
| 민수 | 5500 | Yes |
---------------------------------
(이미 구해진 5000이라는 숫자와 계속 비교만 함)
서브 쿼리는 평균 연봉인 5000을 딱 1회만 계산한다. 이 스칼라 값은 메인 쿼리에 전달되며 계산된 5000을 이용해 계속 비교하게 된다. 그렇다면, 상관쿼리는 어떤 차이를 갖는가? 어떻게 동작하는가? 아래 예시 플로우를 살펴본다.
[ 메인 쿼리 - 1번째 줄: 철수(영업팀, 4000) 검사 ]
⬇ ("야, 영업팀 평균 얼마냐?" -> 데이터 전달) ┐
⬇ │
⬇ [ 서브 쿼리 실행 ] <----------------┘
⬇ (영업팀만 계산...) ➡ 결과: 3000
⬇ │
⬇ <---- ("3000 입니다" 반환) -----┘
⬇
[ 철수: 4000 > 3000 ? OK! 합격 ]
----------------------------------------------------
[ 메인 쿼리 - 2번째 줄: 영희(인사팀, 5000) 검사 ]
⬇ ("야, 인사팀 평균 얼마냐?" -> 데이터 전달) ┐
⬇ │
⬇ [ 서브 쿼리 실행 ] <----------------┘
⬇ (인사팀만 계산...) ➡ 결과: 6000
⬇ │
⬇ <---- ("6000 입니다" 반환) -----┘
⬇
[ 영희: 5000 > 6000 ? No! 탈락 ]
이 경우는 요구사항 자체가 달라진다: '각 직원이 속한 부서의 연봉 평균보다 높은 직원을 조회' 해야한다. 메인 쿼리의 FROM 절에 오는 테이블은 직원 테이블이다. 첫 번째 직원인 철수을 불러와서 직원이 속한 그룹을 찾고, 영업팀에 속한 직원들을 조회해서 연봉평균을 집계하여 철수가 영업팀 직원들의 연봉평균보다 높은지 조건절로 비교한다. 높다면 결과 테이블에 포함된다. 두 번째 직원인 영희는 인사팀에 속하며 인사팀 직원들의 연봉평균을 계산해서 높은지 판단한다. 영희는 연봉평균보다 낮기 때문에 결과 테이블에서 제외된다. 이러한 작업이 직원 테이블의 모든 레코드에서 수행된다.
여기서 말하는 의존성은 서브쿼리의 연산에 메인을 '참조'하게 됨을 나타낸다. '속한 부서의 연봉 평균'에서 부서는 '누가 속한 부서'를 알아야 하며 이는 메인에 존재한다.
소스코드에서 발견된 상관 서브쿼리
지금까지 보여준 예시와 유사한 쿼리가 소스코드에서 발견되었다. 그룹을 조회하는 메인 쿼리에 각 그룹에 속하는 구성원의 카운트를 집계해서 출력해야하는 쿼리다. 다음과 같은 쿼리로 작성되어있었다:
SELECT T1.GROUP_ID, ...
(SELECT count(...)
FROM GROUP_HISTORY S1
WHERE S1.GROUP_ID = T1.GROUP_ID -- <== 여기가 바로 "상관" 관계!
AND ...)
FROM GROUP T1
여기서 메인 쿼리에 중복이 없거나, 소수의 레코드에서 서브쿼리가 실행되는 경우 성능 이슈가 없다. 하지만, 중복이 발생하고 여러 건의 레코드에서 서브쿼리를 실행해야 한다면 O(N x M)만큼의 시간복잡도로 성능이 기하급수적으로 하락한다.
해결 방법
실행 계획 분석
더 명확한 원인을 파악하기 위해 실행 계획을 분석했다. 덤프 DB에서 특정 성능 이슈가 보고된 테넌트를 기준으로 실행했다. 분석 결과는 다음과 같다:
메인 쿼리의 결과 레코드인 77건을 기준으로 상관 서브쿼리가 반복되었다. 여기서 통계 관련 문제도 확인할 수 있었는데, Row에 약 34,472건을 예상했지만 실제로는 5건에 불과했다. 서브쿼리 내에서 사용되는 또다른 테이블이 있는데 데이터 갱신이 빈번해서 통계정보가 틀어진것으로 판단된다. 이로 인해 옵티마이저가 비효율적인 실행 경로를 선택했다.
쿼리 개선
이를 해결하기 위한 방법은 간단하다. 프로그래밍 관점에서 상관 서브쿼리는 N+1 문제와 유사하다. 아래 코드를 살펴본다.
// 1. 메인 쿼리 (GROUP 테이블: 92건)
for (Group group : GROUPS) {
// 2. 서브쿼리 (매번 실행됨!)
// group.getId()를 파라미터로 넣어서 무거운 DB 조회 함수 호출
int count = executeComplexQuery( group.getId() );
print(group, count);
}
// 결과: 무거운 함수가 92번 실행됨 (비효율의 극치)
for 문 내부에서 실행 비용이 비싼 함수가 매번 실행된다. 이를 해결하는 방법 중 하나는 for 문 외부에서 실행한 결과를 가져와서 활용하는 방법이다. 이러한 방법은 실무에서 가장 흔하게 사용해온 성능 개선 방법이다. for 문 내에서 가져올 수 있도록 키값 자료구조에 결과를 저장하고 이를 for 문 내부에서 가져온다. 비싼 계산이나 왕복이 오래걸리는 I/O작업을 한 번에 처리해서 키값 자료구조에 넣는 방식이다.
// 1. CTE (서브쿼리 먼저 한 번만 실행)
// DB에 있는 모든 그룹의 카운트를 미리 다 가져와서 Map에 저장
Map<String, Integer> countMap = executeComplexQuery_ALL_AT_ONCE();
// 2. 메인 쿼리 (GROUP 테이블)
for (Group group : GROUPS) {
// 3. 메모리에서 값만 쏙 꺼냄 (0.0001초 소요)
int count = countMap.get(group.getId());
print(group, count);
}
// 결과: 무거운 함수는 딱 1번만 실행됨 (고속도로!)
이를 쿼리 관점에서 수정하면 아래 처럼 수정할 수 있었다.
WITH HistoryStats AS (
-- [1단계] 서브쿼리 내용을 밖으로 꺼내서 '그룹핑' 합니다.
-- 메인 쿼리에 의존하지 않고, 독립적으로 Group 별 Count를 미리 계산합니다.
SELECT GROUP_ID, COUNT(*) AS CNT
FROM GROUP_HISTORY
WHERE ... -- (기존 서브쿼리 내부에 있던 조건들, 예: 삭제되지 않은 이력 등)
GROUP BY GROUP_ID
)
SELECT T1.GROUP_ID,
-- T1.OTHER_COLUMNS...
COALESCE(HS.CNT, 0) AS CNT -- [3단계] 매칭되는 게 없으면 0으로 처리
FROM GROUP T1
LEFT JOIN HistoryStats HS -- [2단계] 미리 계산된 결과와 조인
ON T1.GROUP_ID = HS.GROUP_ID;
가독성을 위해 CTE로 분리했다. 그리고 LEFT JOIN을 이용해서 메인 쿼리와 결합한다. 결과값이 NULL인 경우는 0으로 처리한다. 이렇게 수정된 쿼리의 실행 계획을 분석해보았다.
가장 상단에 눈에 띄는 것은 92번 반복하던 루프가 사라지고, 메모리 상에서 해시 알고리즘을 통해 순식간에 조인시켰다. 그리고 Materialize CTE에서 알 수 있듯이 반복되던 서브쿼리가 단 1번 실행되어 메모리에 임시 테이블(8건)로 생성되었다.
개선 결과
수정 전 후의 쿼리를 각각 12번씩 실행하고 첫 실행은 warm up으로 데이터 셋에서 제외하며, 최대값과 최소값 또한 데이터 셋에서 제외하여 그래프로 나타내었다.
단위는 ms으로 어마어마하게 느렸던 쿼리가 1000ms 이하로 내려간것을 확인할 수 있었다. 결과를 테이블로 요약하면 다음과 같다:
| 비교 항목 | 개선 전 (Before) | 개선 후 (After) | 변화 |
| 총 소요 비용 | 246.0 | 40.6 | 약 83% 감소 (6배 빨라짐) |
| 반복 횟수 | 92회 (메인 쿼리 행 수만큼) | 1회 (CTE 생성 시) | 92배 감소 |
| 조인 방식 | Nested Loops (비효율) | Hash Join (초고속) | 알고리즘 개선 |
| 통계 의존도 | 높음 (틀린 통계로 인해 느려짐) | 낮음 (실제 결과 8건을 보고 판단) | 안정성 확보 |
회고
이 이슈는 사용자 리포팅 이후에야 인지했다는 점에서 아쉬움이 남는다. 로컬 환경의 데이터 규모가 작아 문제를 사전에 드러내지 못했다.
다만, 이론적으로만 알고 있던 상관 서브쿼리의 위험성을 실제 운영환경에서 직접 체감하고, 수치로 개선을 증명했다는 점은 큰 학습이었다.
쿼리는 "틀려서" 느려진 것이 아니라, 당시의 합리적인 선택이 상황 변화에 의해 병목이 된 것일 수 있다. 중요한 것은 비난이 아니라, 문제를 구조적으로 제거하고 재발 가능성을 낮추는 것이라고 생각한다.
