SQL 조인과 쿼리 최적화 완벽 가이드: JOIN 알고리즘부터 윈도우 함수까지
SQL 조인과 쿼리 최적화 완벽 가이드: JOIN 알고리즘부터 윈도우 함수까지
이전 글에서 인덱스의 B+Tree 구조, EXPLAIN 실행 계획 읽는 법, 인덱스 기반 쿼리 최적화를 다루었다. 이번 글에서는 인덱스 이전의 본질, 즉 SQL 자체의 동작 원리를 깊이 있게 정리한다.
“LEFT JOIN과 INNER JOIN의 차이를 설명하세요”, “서브쿼리와 JOIN 중 어떤 걸 써야 하나요”, “쿼리가 느릴 때 어떻게 분석하나요” — 이 질문들에 단순히 문법을 대답하는 것과, 옵티마이저가 내부적으로 어떤 알고리즘을 선택하는지까지 설명하는 것은 차원이 다르다. JOIN의 종류와 알고리즘, 서브쿼리 패턴, 쿼리 리라이팅 기법, 윈도우 함수, 그리고 옵티마이저의 의사결정 과정을 정리한다.
EXPLAIN 실행 계획의 상세 읽는 법과 인덱스 설계 전략은 [인덱스 동작 원리 글]을 참고한다.
1. JOIN의 종류와 동작 원리
1.1 JOIN이란
JOIN은 두 개 이상의 테이블을 특정 조건으로 결합하여 하나의 결과 집합을 만드는 연산이다. 정규화된 데이터베이스에서 데이터는 여러 테이블로 분리되어 있으므로, 의미 있는 조회를 위해 JOIN은 필수이다.
1
2
3
4
5
6
7
8
9
10
11
12
13
기본 테이블 구조 (이하 모든 예제에서 사용):
employees (사원) departments (부서)
┌────┬──────────┬─────────┐ ┌─────────┬────────────┐
│ id │ name │ dept_id │ │ dept_id │ dept_name │
├────┼──────────┼─────────┤ ├─────────┼────────────┤
│ 1 │ 김철수 │ 10 │ │ 10 │ 개발팀 │
│ 2 │ 이영희 │ 20 │ │ 20 │ 마케팅팀 │
│ 3 │ 박민수 │ 30 │ │ 30 │ 영업팀 │
│ 4 │ 최지은 │ 10 │ │ 40 │ 인사팀 │
│ 5 │ 정다연 │ NULL │ └─────────┴────────────┘
└────┴──────────┴─────────┘
(정다연은 아직 부서 미배정) (인사팀에는 소속 사원 없음)
1.2 INNER JOIN
양쪽 테이블에 모두 일치하는 행만 반환한다. 가장 일반적인 JOIN이다.
1
2
3
SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
INNER JOIN 동작:
employees departments
┌────┬──────┬───┐ ┌───┬────────┐
│ id │ name │d_id│ │d_id│ d_name │
├────┼──────┼───┤ ├───┼────────┤
│ 1 │ 김철수│ 10 │─────│ 10│ 개발팀 │ ✓ 매칭
│ 2 │ 이영희│ 20 │─────│ 20│ 마케팅팀│ ✓ 매칭
│ 3 │ 박민수│ 30 │─────│ 30│ 영업팀 │ ✓ 매칭
│ 4 │ 최지은│ 10 │─────│ 10│ 개발팀 │ ✓ 매칭
│ 5 │ 정다연│NULL│ ✗ │ 40│ 인사팀 │ ✗ 매칭 안 됨
└────┴──────┴───┘ └───┴────────┘
결과:
┌──────────┬────────────┐
│ name │ dept_name │
├──────────┼────────────┤
│ 김철수 │ 개발팀 │
│ 이영희 │ 마케팅팀 │
│ 박민수 │ 영업팀 │
│ 최지은 │ 개발팀 │
└──────────┴────────────┘
→ 정다연(dept_id=NULL): 제외
→ 인사팀(dept_id=40): 제외
1.3 LEFT (OUTER) JOIN
왼쪽 테이블의 모든 행을 반환한다. 오른쪽에 매칭이 없으면 NULL로 채운다.
1
2
3
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
LEFT JOIN 동작:
employees (LEFT = 기준) departments
┌────┬──────┬───┐ ┌───┬────────┐
│ id │ name │d_id│ │d_id│ d_name │
├────┼──────┼───┤ ├───┼────────┤
│ 1 │ 김철수│ 10 │───────│ 10│ 개발팀 │ ✓
│ 2 │ 이영희│ 20 │───────│ 20│ 마케팅팀│ ✓
│ 3 │ 박민수│ 30 │───────│ 30│ 영업팀 │ ✓
│ 4 │ 최지은│ 10 │───────│ 10│ 개발팀 │ ✓
│ 5 │ 정다연│NULL│→ NULL │ 40│ 인사팀 │ (매칭 대상 없음)
└────┴──────┴───┘ └───┴────────┘
결과:
┌──────────┬────────────┐
│ name │ dept_name │
├──────────┼────────────┤
│ 김철수 │ 개발팀 │
│ 이영희 │ 마케팅팀 │
│ 박민수 │ 영업팀 │
│ 최지은 │ 개발팀 │
│ 정다연 │ NULL │ ← LEFT 테이블은 무조건 포함
└──────────┴────────────┘
실무 활용: “매칭되지 않는 행”을 찾을 때 LEFT JOIN + WHERE IS NULL을 사용한다.
1
2
3
4
5
6
-- 부서에 소속되지 않은 사원 찾기
SELECT e.name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL;
-- 결과: 정다연
1.4 RIGHT (OUTER) JOIN
오른쪽 테이블의 모든 행을 반환한다. LEFT JOIN의 반대이다.
1
2
3
SELECT e.name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;
1
2
3
4
5
6
7
8
9
10
11
12
13
결과:
┌──────────┬────────────┐
│ name │ dept_name │
├──────────┼────────────┤
│ 김철수 │ 개발팀 │
│ 이영희 │ 마케팅팀 │
│ 박민수 │ 영업팀 │
│ 최지은 │ 개발팀 │
│ NULL │ 인사팀 │ ← RIGHT 테이블은 무조건 포함
└──────────┴────────────┘
실무에서는 RIGHT JOIN보다 LEFT JOIN을 선호한다.
→ 테이블 순서를 바꿔서 LEFT JOIN으로 표현하는 것이 가독성이 좋다.
1.5 FULL (OUTER) JOIN
양쪽 테이블의 모든 행을 반환한다. 매칭 안 되는 행은 NULL로 채운다.
1
2
3
4
5
6
7
8
9
-- MySQL은 FULL OUTER JOIN을 지원하지 않음!
-- LEFT JOIN + UNION + RIGHT JOIN으로 구현
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
UNION
SELECT e.name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;
1
2
3
4
5
6
7
8
9
10
11
결과:
┌──────────┬────────────┐
│ name │ dept_name │
├──────────┼────────────┤
│ 김철수 │ 개발팀 │
│ 이영희 │ 마케팅팀 │
│ 박민수 │ 영업팀 │
│ 최지은 │ 개발팀 │
│ 정다연 │ NULL │ ← 왼쪽만 있는 행
│ NULL │ 인사팀 │ ← 오른쪽만 있는 행
└──────────┴────────────┘
1.6 CROSS JOIN (교차 조인)
카르테시안 곱: 양쪽 테이블의 모든 행 조합을 반환한다. 조건 없이 결합한다.
1
2
3
4
SELECT e.name, d.dept_name
FROM employees e
CROSS JOIN departments d;
-- 결과: 5 × 4 = 20행
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CROSS JOIN — 모든 조합:
employees × departments:
┌──────────┬────────────┐
│ name │ dept_name │
├──────────┼────────────┤
│ 김철수 │ 개발팀 │
│ 김철수 │ 마케팅팀 │
│ 김철수 │ 영업팀 │
│ 김철수 │ 인사팀 │
│ 이영희 │ 개발팀 │
│ 이영희 │ 마케팅팀 │
│ ... │ ... │ (총 20행)
└──────────┴────────────┘
주의: WHERE 절 없이 CROSS JOIN하면 데이터 폭발!
테이블 A(10만 행) × 테이블 B(10만 행) = 100억 행!
실무 활용:
- 달력 테이블 생성
- 테스트 데이터 생성
- 매트릭스 보고서
1.7 SELF JOIN
동일 테이블을 자기 자신과 조인한다. 계층 구조 표현에 사용한다.
1
2
3
4
5
-- 사원과 상사의 관계 (매니저 조회)
-- employees 테이블에 manager_id 컬럼이 있다고 가정
SELECT e.name AS 사원, m.name AS 상사
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELF JOIN — 조직도 조회:
employees:
┌────┬──────┬────────────┐
│ id │ name │ manager_id │
├────┼──────┼────────────┤
│ 1 │ 대표 │ NULL │ ← 최상위
│ 2 │ 팀장A │ 1 │
│ 3 │ 팀장B │ 1 │
│ 4 │ 사원C │ 2 │
│ 5 │ 사원D │ 2 │
└────┴──────┴────────────┘
결과:
┌──────┬──────┐
│ 사원 │ 상사 │
├──────┼──────┤
│ 대표 │ NULL │
│ 팀장A │ 대표 │
│ 팀장B │ 대표 │
│ 사원C │ 팀장A │
│ 사원D │ 팀장A │
└──────┴──────┘
1.8 JOIN 종류 한눈에 비교
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
벤 다이어그램으로 이해하는 JOIN:
INNER JOIN: LEFT JOIN: RIGHT JOIN:
┌─────┬─────┐ ┌─────┬─────┐ ┌─────┬─────┐
│ A │█████│ B │ │█████│█████│ B │ │ A │█████│█████│
│ │█████│ │ │█████│█████│ │ │ │█████│█████│
└─────┴─────┘ └─────┴─────┘ └─────┴─────┘
교집합만 A 전체 + 교집합 교집합 + B 전체
FULL OUTER JOIN: LEFT EXCLUSIVE: FULL EXCLUSIVE:
┌─────┬─────┐ ┌─────┬─────┐ ┌─────┬─────┐
│█████│█████│█████│ │█████│ │ │ │█████│ │█████│
│█████│█████│█████│ │█████│ │ │ │█████│ │█████│
└─────┴─────┘ └─────┴─────┘ └─────┴─────┘
A ∪ B 전체 A에만 있는 것 교집합 제외한 나머지
(LEFT JOIN (FULL OUTER JOIN
WHERE B IS NULL) WHERE A IS NULL
OR B IS NULL)
2. JOIN 알고리즘 — 옵티마이저의 실행 전략
같은 JOIN 쿼리라도 옵티마이저가 어떤 알고리즘을 선택하느냐에 따라 성능이 수십~수백 배 달라진다. 면접에서 “JOIN이 느릴 때 어떻게 최적화하나요?”의 답은 여기에 있다.
2.1 Nested Loop Join (NLJ)
가장 기본적인 JOIN 알고리즘이다. 이중 for 루프와 같다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
Nested Loop Join 동작:
for each row r1 in 외부 테이블 (Driving Table):
for each row r2 in 내부 테이블 (Driven Table):
if r1.join_key == r2.join_key:
output (r1, r2)
예: employees(5행) INNER JOIN departments(4행)
외부 루프 (employees): 내부 루프 (departments):
┌────────────────────┐ ┌────────────────────┐
│ r1 = 김철수(d=10) │ ──→ │ 개발팀(10) ✓ 매칭! │
│ │ │ 마케팅(20) ✗ │
│ │ │ 영업(30) ✗ │
│ │ │ 인사(40) ✗ │
├────────────────────┤ ├────────────────────┤
│ r1 = 이영희(d=20) │ ──→ │ 개발팀(10) ✗ │
│ │ │ 마케팅(20) ✓ 매칭! │
│ │ │ 영업(30) ✗ │
│ │ │ 인사(40) ✗ │
├────────────────────┤ ├────────────────────┤
│ ... │ │ ... │
└────────────────────┘ └────────────────────┘
비교 횟수: 5 × 4 = 20회
시간 복잡도: O(M × N)
최적화 — 내부 테이블에 인덱스가 있으면?
┌────────────────────────────────────────────────────┐
│ departments.dept_id에 인덱스가 있으면: │
│ 내부 루프가 O(N) → O(log N)으로 개선 (B+Tree 탐색) │
│ │
│ 비교 횟수: 5 × log₂(4) ≈ 10회 │
│ 시간 복잡도: O(M × log N) │
│ │
│ → 이것이 "JOIN 컬럼에 인덱스를 걸어라"의 이유! │
└────────────────────────────────────────────────────┘
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Block Nested Loop Join (BNL) — MySQL의 최적화:
단순 NLJ는 내부 테이블을 외부 행 수만큼 반복 스캔한다.
BNL은 Join Buffer를 사용하여 디스크 I/O를 줄인다.
┌──────────────────────────────────────────────────────┐
│ 1. 외부 테이블의 여러 행을 Join Buffer에 적재 │
│ 2. 내부 테이블을 1번만 스캔하며 Buffer의 모든 행과 비교 │
│ 3. Buffer가 가득 차면 내부 테이블을 다시 스캔 │
│ │
│ Join Buffer (메모리): │
│ ┌──────┬──────┬──────┬──────┐ │
│ │ 김철수 │ 이영희 │ 박민수 │ 최지은 │ ← 외부 행 4개 적재 │
│ │ d=10 │ d=20 │ d=30 │ d=10 │ │
│ └──────┴──────┴──────┴──────┘ │
│ ↓ ↓ ↓ ↓ │
│ departments 1번 스캔으로 4개 행 모두 비교! │
│ → 디스크 I/O: 4번 → 1번으로 감소 │
│ │
│ join_buffer_size 시스템 변수로 크기 조절 │
└──────────────────────────────────────────────────────┘
MySQL 8.0.18+ : Hash Join이 BNL을 대체
2.2 Hash Join
해시 테이블을 이용한 JOIN이다. 등가 조인(=)에서 매우 효율적이다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
Hash Join 동작 (2단계):
┌──────────────────────────────────────────────────────────┐
│ Phase 1: Build (빌드 단계) │
│ 작은 테이블(Build Table)로 해시 테이블 생성 │
│ │
│ departments (작은 테이블): │
│ dept_id → Hash Function → 해시 테이블 │
│ │
│ Hash Table: │
│ ┌────────┬─────────────────────┐ │
│ │ Bucket │ Value │ │
│ ├────────┼─────────────────────┤ │
│ │ 0 │ (10, 개발팀) │ │
│ │ 1 │ (empty) │ │
│ │ 2 │ (20, 마케팅팀) │ │
│ │ 3 │ (30, 영업팀) │ │
│ │ 4 │ (40, 인사팀) │ │
│ └────────┴─────────────────────┘ │
└──────────────────────────────────────────────────────────┘
┌──────────────────────────────────────────────────────────┐
│ Phase 2: Probe (탐색 단계) │
│ 큰 테이블(Probe Table)을 순회하며 해시 테이블에서 매칭 │
│ │
│ employees (큰 테이블): │
│ 김철수(d=10) → hash(10) → Bucket 0 → (10, 개발팀) ✓ │
│ 이영희(d=20) → hash(20) → Bucket 2 → (20, 마케팅팀) ✓ │
│ 박민수(d=30) → hash(30) → Bucket 3 → (30, 영업팀) ✓ │
│ 최지은(d=10) → hash(10) → Bucket 0 → (10, 개발팀) ✓ │
│ 정다연(NULL) → hash(NULL) → 매칭 없음 ✗ │
│ │
│ 시간 복잡도: O(M + N) (Build: O(N), Probe: O(M)) │
│ → NLJ의 O(M × N)보다 훨씬 빠름! │
└──────────────────────────────────────────────────────────┘
Hash Join의 특성:
┌────────────────────────────────────────────────────────┐
│ ✓ 등가 조인(=)에서만 사용 가능 (범위 조건 불가) │
│ ✓ 인덱스가 없어도 효율적 │
│ ✓ Build 테이블이 메모리에 들어가야 최적 │
│ ✗ 메모리 부족 시 디스크 사용 (Grace Hash Join) → 성능 ↓ │
│ ✗ 비등가 조인(>, <, LIKE 등)에는 사용 불가 │
└────────────────────────────────────────────────────────┘
2.3 Sort-Merge Join
두 테이블을 정렬한 후 병합하는 방식이다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
Sort-Merge Join 동작 (3단계):
┌──────────────────────────────────────────────────────────┐
│ Phase 1: Sort (양쪽 테이블을 JOIN 키로 정렬) │
│ │
│ employees (dept_id로 정렬): departments (dept_id로 정렬):│
│ ┌──────┬───┐ ┌───┬────────┐ │
│ │ 정다연 │NULL│ │ 10│ 개발팀 │ │
│ │ 김철수 │ 10│ │ 20│ 마케팅팀│ │
│ │ 최지은 │ 10│ │ 30│ 영업팀 │ │
│ │ 이영희 │ 20│ │ 40│ 인사팀 │ │
│ │ 박민수 │ 30│ └───┴────────┘ │
│ └──────┴───┘ │
└──────────────────────────────────────────────────────────┘
┌──────────────────────────────────────────────────────────┐
│ Phase 2: Merge (정렬된 두 테이블을 병합) │
│ │
│ 양쪽 포인터를 동시에 이동하며 비교: │
│ │
│ emp↓ dept↓ │
│ NULL 10 → NULL < 10, emp 전진 │
│ 10 10 → 10 == 10, 매칭! (김철수-개발팀) │
│ 10 10 → 10 == 10, 매칭! (최지은-개발팀) │
│ 20 20 → 20 == 20, 매칭! (이영희-마케팅팀) │
│ 30 30 → 30 == 30, 매칭! (박민수-영업팀) │
│ END 40 → 종료 │
│ │
│ 시간 복잡도: O(M log M + N log N) (정렬) + O(M + N) (병합) │
└──────────────────────────────────────────────────────────┘
Sort-Merge Join의 특성:
┌────────────────────────────────────────────────────────┐
│ ✓ 등가 조인 + 비등가 조인(>, <, >=, <=) 모두 가능 │
│ ✓ 이미 정렬된 데이터(인덱스 포함)에서 매우 빠름 │
│ ✓ 대용량 데이터에서 Hash Join보다 나을 수 있음 │
│ ✗ 정렬 비용이 큼 (정렬되지 않은 데이터에서) │
│ ✗ 메모리에 정렬 결과를 저장해야 함 │
└────────────────────────────────────────────────────────┘
2.4 JOIN 알고리즘 비교 및 옵티마이저의 선택
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
┌────────────────┬──────────────┬──────────────┬──────────────┐
│ │ Nested Loop │ Hash Join │ Sort-Merge │
├────────────────┼──────────────┼──────────────┼──────────────┤
│ 시간 복잡도 │ O(M × N) │ O(M + N) │ O(sort+M+N) │
│ (인덱스 없음) │ │ │ │
├────────────────┼──────────────┼──────────────┼──────────────┤
│ 인덱스 있을 때 │ O(M × logN) │ 사용 안 함 │ 정렬 생략 가능│
│ │ 매우 빠름 │ │ │
├────────────────┼──────────────┼──────────────┼──────────────┤
│ 등가 조인 (=) │ ✓ │ ✓ │ ✓ │
│ 비등가 조인 │ ✓ │ ✗ │ ✓ │
├────────────────┼──────────────┼──────────────┼──────────────┤
│ 소량 데이터 │ 가장 빠름 │ 오버헤드 │ 오버헤드 │
│ 대량 데이터 │ 매우 느림 │ 빠름 │ 빠름 │
├────────────────┼──────────────┼──────────────┼──────────────┤
│ 메모리 요구 │ 적음 │ 많음 (해시) │ 많음 (정렬) │
├────────────────┼──────────────┼──────────────┼──────────────┤
│ MySQL │ ✓ (기본) │ ✓ (8.0.18+) │ ✗ │
│ PostgreSQL │ ✓ │ ✓ │ ✓ │
│ Oracle │ ✓ │ ✓ │ ✓ │
└────────────────┴──────────────┴──────────────┴──────────────┘
옵티마이저의 선택 기준:
┌──────────────────────────────────────────────────┐
│ 1. 테이블 크기: │
│ 소량 × 소량 → Nested Loop │
│ 소량 × 대량 → Nested Loop (소량이 Driving) │
│ 대량 × 대량 → Hash Join 또는 Sort-Merge │
│ │
│ 2. 인덱스 유무: │
│ 내부 테이블에 인덱스 있음 → Nested Loop 유리 │
│ 인덱스 없음 → Hash Join 유리 │
│ │
│ 3. 조인 조건: │
│ 등가 조인(=) → Hash Join 가능 │
│ 범위 조인(>, <) → Hash Join 불가, NLJ or SM │
│ │
│ 4. 이미 정렬된 데이터: │
│ ORDER BY와 같은 키로 조인 → Sort-Merge 유리 │
└──────────────────────────────────────────────────┘
3. 서브쿼리 패턴과 최적화
3.1 서브쿼리의 종류
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
서브쿼리 분류:
┌──────────────────────────────────────────────────────┐
│ 위치에 따른 분류: │
│ │
│ ① SELECT 절 — 스칼라 서브쿼리 │
│ SELECT name, (SELECT dept_name FROM departments │
│ WHERE dept_id = e.dept_id) AS dept │
│ FROM employees e; │
│ │
│ ② FROM 절 — 인라인 뷰 (Derived Table) │
│ SELECT * FROM (SELECT dept_id, COUNT(*) AS cnt │
│ FROM employees GROUP BY dept_id) t; │
│ │
│ ③ WHERE 절 — 조건 서브쿼리 │
│ SELECT * FROM employees │
│ WHERE dept_id IN (SELECT dept_id FROM departments); │
└──────────────────────────────────────────────────────┘
┌──────────────────────────────────────────────────────┐
│ 실행 방식에 따른 분류: │
│ │
│ ① 비상관 서브쿼리 (Non-Correlated) │
│ 외부 쿼리와 독립적으로 1번만 실행 │
│ SELECT * FROM employees │
│ WHERE dept_id IN (SELECT dept_id FROM departments); │
│ → 서브쿼리가 먼저 실행되어 결과 집합 생성 │
│ │
│ ② 상관 서브쿼리 (Correlated) │
│ 외부 쿼리의 각 행마다 서브쿼리가 반복 실행! │
│ SELECT * FROM employees e │
│ WHERE EXISTS (SELECT 1 FROM departments d │
│ WHERE d.dept_id = e.dept_id); │
│ → 외부 행 N개 × 서브쿼리 실행 = 잠재적으로 N번 실행 │
└──────────────────────────────────────────────────────┘
3.2 스칼라 서브쿼리의 함정
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
스칼라 서브쿼리 — 성능 함정:
SELECT e.name,
(SELECT d.dept_name
FROM departments d
WHERE d.dept_id = e.dept_id) AS dept_name
FROM employees e;
문제:
┌──────────────────────────────────────────────────┐
│ employees가 100만 행이면? │
│ → 스칼라 서브쿼리가 100만 번 실행! │
│ → 각 실행마다 departments 테이블 조회 │
│ │
│ 물론 옵티마이저가 내부적으로 캐싱할 수 있지만, │
│ dept_id의 종류가 많으면 캐시 미스 빈발 │
└──────────────────────────────────────────────────┘
해결 — JOIN으로 변환:
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;
→ JOIN은 1번의 연산으로 처리!
→ 옵티마이저가 최적의 JOIN 알고리즘을 선택
3.3 EXISTS vs IN — 핵심 차이
면접에서 매우 자주 출제되는 주제이다.
1
2
3
4
5
6
7
8
-- IN: 서브쿼리의 결과 집합에 포함되는지 확인
SELECT * FROM employees e
WHERE e.dept_id IN (SELECT d.dept_id FROM departments d);
-- EXISTS: 서브쿼리가 하나라도 존재하는지 확인
SELECT * FROM employees e
WHERE EXISTS (SELECT 1 FROM departments d
WHERE d.dept_id = e.dept_id);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
IN vs EXISTS 동작 차이:
IN의 동작:
┌──────────────────────────────────────────────────────┐
│ 1. 서브쿼리 실행: {10, 20, 30, 40} 결과 집합 생성 │
│ 2. 외부 쿼리의 각 행에 대해 결과 집합에 포함되는지 검사 │
│ │
│ → 서브쿼리가 1번 실행 (비상관) │
│ → 결과 집합이 크면 메모리 사용량 증가 │
│ → 결과 집합에 NULL이 있으면 예상치 못한 결과! │
└──────────────────────────────────────────────────────┘
EXISTS의 동작:
┌──────────────────────────────────────────────────────┐
│ 1. 외부 쿼리의 각 행에 대해 서브쿼리 실행 │
│ 2. 서브쿼리가 1행이라도 반환하면 TRUE → 즉시 중단! │
│ │
│ → 상관 서브쿼리 (외부 행마다 실행) │
│ → 하지만 매칭되면 즉시 중단 (Short-Circuit) │
│ → 서브쿼리 결과를 메모리에 저장하지 않음 │
└──────────────────────────────────────────────────────┘
선택 기준:
┌──────────────────────────────────────────────────────────┐
│ │
│ 외부 테이블이 크고, 서브쿼리 결과가 작을 때 → IN 유리 │
│ 이유: 서브쿼리 1번 실행, 작은 결과 집합으로 빠르게 비교 │
│ │
│ 외부 테이블이 작고, 서브쿼리 대상이 클 때 → EXISTS 유리 │
│ 이유: 외부 행이 적으므로 서브쿼리 실행 횟수 적음 │
│ + EXISTS는 1행 찾으면 즉시 중단 │
│ │
│ 실무에서는? │
│ → 현대 옵티마이저(MySQL 8.0+)는 IN을 EXISTS로, │
│ EXISTS를 Semi-Join으로 자동 변환하므로 차이가 줄어듦 │
│ → 하지만 NOT IN vs NOT EXISTS는 여전히 큰 차이! │
└──────────────────────────────────────────────────────────┘
3.4 NOT IN vs NOT EXISTS — 치명적 차이
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
NOT IN의 NULL 함정:
departments에 dept_id = NULL인 행이 있다면?
SELECT * FROM employees e
WHERE e.dept_id NOT IN (SELECT d.dept_id FROM departments d);
서브쿼리 결과: {10, 20, 30, 40, NULL}
NOT IN의 평가:
e.dept_id NOT IN (10, 20, 30, 40, NULL)
= e.dept_id != 10 AND e.dept_id != 20 AND ... AND e.dept_id != NULL
= TRUE AND TRUE AND ... AND UNKNOWN
= UNKNOWN (SQL의 3-값 논리!)
결과: 어떤 행도 반환되지 않는다!!!
┌──────────────────────────────────────────────────────┐
│ NOT IN + NULL = 빈 결과 (의도하지 않은 동작!) │
│ │
│ 해결 1: NOT EXISTS 사용 (NULL 안전) │
│ SELECT * FROM employees e │
│ WHERE NOT EXISTS ( │
│ SELECT 1 FROM departments d │
│ WHERE d.dept_id = e.dept_id │
│ ); │
│ → NULL 비교가 없으므로 안전하게 동작 │
│ │
│ 해결 2: NOT IN + IS NOT NULL │
│ WHERE e.dept_id NOT IN ( │
│ SELECT d.dept_id FROM departments d │
│ WHERE d.dept_id IS NOT NULL │
│ ); │
└──────────────────────────────────────────────────────┘
결론: NOT IN 대신 NOT EXISTS를 습관적으로 사용하자!
3.5 서브쿼리 → JOIN 변환 (쿼리 리라이팅)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 비효율적: 상관 서브쿼리
SELECT e.name, e.salary
FROM employees e
WHERE e.salary > (
SELECT AVG(e2.salary)
FROM employees e2
WHERE e2.dept_id = e.dept_id -- 상관!
);
-- → 외부 행마다 AVG 서브쿼리 실행
-- 효율적: JOIN으로 변환
SELECT e.name, e.salary
FROM employees e
INNER JOIN (
SELECT dept_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id
) dept_avg ON e.dept_id = dept_avg.dept_id
WHERE e.salary > dept_avg.avg_salary;
-- → 서브쿼리 1번 실행 후 JOIN
4. 쿼리 리라이팅 기법
4.1 OR → UNION 변환
1
2
3
4
5
6
7
8
9
10
-- 비효율적: OR는 인덱스를 활용하기 어려움
SELECT * FROM orders
WHERE customer_id = 100 OR product_id = 200;
-- → 옵티마이저가 Full Table Scan을 선택할 수 있음
-- 효율적: UNION으로 분리하면 각각 인덱스 사용 가능
SELECT * FROM orders WHERE customer_id = 100
UNION
SELECT * FROM orders WHERE product_id = 200;
-- → idx_customer_id와 idx_product_id를 각각 활용
4.2 Sargable vs Non-Sargable
Sargable(Search ARGument ABLE): 인덱스를 활용할 수 있는 조건
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
Non-Sargable (인덱스 사용 불가):
┌──────────────────────────────────────────────────────┐
│ ✗ WHERE YEAR(created_at) = 2026 │
│ → 함수가 컬럼에 적용되면 인덱스 사용 불가 │
│ │
│ ✗ WHERE salary * 12 > 50000000 │
│ → 연산이 컬럼에 적용되면 인덱스 사용 불가 │
│ │
│ ✗ WHERE name LIKE '%철수' │
│ → 앞에 와일드카드가 있으면 인덱스 사용 불가 │
│ │
│ ✗ WHERE CAST(price AS CHAR) = '1000' │
│ → 타입 캐스팅이 컬럼에 적용되면 인덱스 사용 불가 │
└──────────────────────────────────────────────────────┘
Sargable (인덱스 사용 가능):
┌──────────────────────────────────────────────────────┐
│ ✓ WHERE created_at >= '2026-01-01' │
│ AND created_at < '2027-01-01' │
│ → 범위 조건으로 인덱스 활용 가능 │
│ │
│ ✓ WHERE salary > 50000000 / 12 │
│ → 상수에 연산 적용 → 컬럼은 그대로 │
│ │
│ ✓ WHERE name LIKE '김%' │
│ → 접두사 검색은 인덱스 활용 가능 │
└──────────────────────────────────────────────────────┘
원칙: "컬럼을 가공하지 말고, 비교 대상(상수)을 가공하라"
4.3 불필요한 DISTINCT 제거
1
2
3
4
5
6
7
8
9
10
-- 비효율적: DISTINCT는 정렬 또는 해싱 비용 발생
SELECT DISTINCT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;
-- → 1:N 관계에서 1쪽 기준이면 중복이 없는데 DISTINCT 불필요
-- 효율적: JOIN 조건이 PK/FK로 보장되면 DISTINCT 제거
SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;
4.4 OFFSET 기반 페이지네이션의 함정
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
OFFSET 방식의 문제:
SELECT * FROM orders ORDER BY id DESC LIMIT 20 OFFSET 1000000;
┌──────────────────────────────────────────────────────┐
│ 동작: 1,000,020행을 읽고 앞의 1,000,000행을 버린다! │
│ → OFFSET이 커질수록 선형적으로 느려짐 │
│ │
│ 페이지 1 (OFFSET 0): ~1ms │
│ 페이지 100 (OFFSET 2000): ~50ms │
│ 페이지 5만 (OFFSET 1M): ~10초! │
└──────────────────────────────────────────────────────┘
커서 기반 페이지네이션 (Keyset Pagination):
-- 첫 페이지
SELECT * FROM orders ORDER BY id DESC LIMIT 20;
-- 마지막 id = 999980
-- 다음 페이지
SELECT * FROM orders
WHERE id < 999980 -- 커서 조건
ORDER BY id DESC LIMIT 20;
┌──────────────────────────────────────────────────────┐
│ 동작: id < 999980 조건으로 인덱스를 타고 바로 20행 조회 │
│ → OFFSET 무관하게 항상 일정한 성능! │
│ │
│ 모든 페이지: ~1ms (인덱스 활용) │
│ │
│ 제약: "N번째 페이지로 건너뛰기" 불가 │
│ → 무한 스크롤 방식에 적합 │
└──────────────────────────────────────────────────────┘
인덱스 기반 페이지네이션의 상세 구현은 [인덱스 동작 원리 글]을 참고한다.
5. 윈도우 함수 (Window Function)
5.1 윈도우 함수란
GROUP BY는 행을 하나로 축소하지만, 윈도우 함수는 원본 행을 유지하면서 집계 결과를 추가한다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
GROUP BY vs Window Function:
원본 데이터:
┌─────┬─────────┬────────┐
│ id │ dept_id │ salary │
├─────┼─────────┼────────┤
│ 1 │ 10 │ 5000 │
│ 2 │ 10 │ 6000 │
│ 3 │ 20 │ 4000 │
│ 4 │ 20 │ 7000 │
└─────┴─────────┴────────┘
GROUP BY:
SELECT dept_id, AVG(salary)
FROM employees GROUP BY dept_id;
┌─────────┬──────┐
│ dept_id │ avg │
├─────────┼──────┤ ← 4행 → 2행으로 축소!
│ 10 │ 5500 │
│ 20 │ 5500 │
└─────────┴──────┘
Window Function:
SELECT id, dept_id, salary,
AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg
FROM employees;
┌─────┬─────────┬────────┬──────────┐
│ id │ dept_id │ salary │ dept_avg │
├─────┼─────────┼────────┼──────────┤ ← 원본 4행 유지!
│ 1 │ 10 │ 5000 │ 5500 │
│ 2 │ 10 │ 6000 │ 5500 │
│ 3 │ 20 │ 4000 │ 5500 │
│ 4 │ 20 │ 7000 │ 5500 │
└─────┴─────────┴────────┴──────────┘
5.2 윈도우 함수 문법
1
2
3
4
5
함수명(컬럼) OVER (
PARTITION BY 그룹_컬럼 -- 윈도우를 나누는 기준 (GROUP BY와 유사)
ORDER BY 정렬_컬럼 -- 윈도우 내 정렬 기준
ROWS/RANGE BETWEEN ... -- 윈도우 프레임 (범위 지정)
)
5.3 순위 함수: ROW_NUMBER, RANK, DENSE_RANK
1
2
3
4
5
SELECT name, dept_id, salary,
ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS row_num,
RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rank_val,
DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dense_rank
FROM employees;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
순위 함수 비교:
데이터: 부서 10의 사원들 (salary 기준 내림차순)
┌──────┬────────┬──────────┬──────┬────────────┐
│ name │ salary │ ROW_NUM │ RANK │ DENSE_RANK │
├──────┼────────┼──────────┼──────┼────────────┤
│ 최지은│ 7000 │ 1 │ 1 │ 1 │
│ 김철수│ 6000 │ 2 │ 2 │ 2 │
│ 박영수│ 6000 │ 3 │ 2 │ 2 │ ← 동점!
│ 이민호│ 5000 │ 4 │ 4 │ 3 │
└──────┴────────┴──────────┴──────┴────────────┘
차이점:
ROW_NUMBER : 동점이어도 고유한 번호 (1,2,3,4)
RANK : 동점이면 같은 순위, 다음 순위 건너뜀 (1,2,2,4)
DENSE_RANK : 동점이면 같은 순위, 다음 순위 연속 (1,2,2,3)
실무 활용: 부서별 급여 Top 3 조회
1
2
3
4
5
6
7
8
9
10
-- 부서별 급여 상위 3명 조회 (중복 급여 있을 수 있음)
SELECT * FROM (
SELECT name, dept_id, salary,
ROW_NUMBER() OVER (
PARTITION BY dept_id
ORDER BY salary DESC
) AS rn
FROM employees
) ranked
WHERE rn <= 3;
5.4 LAG, LEAD — 이전/다음 행 참조
1
2
3
4
5
SELECT order_date, amount,
LAG(amount, 1) OVER (ORDER BY order_date) AS prev_amount,
LEAD(amount, 1) OVER (ORDER BY order_date) AS next_amount,
amount - LAG(amount, 1) OVER (ORDER BY order_date) AS diff
FROM daily_sales;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
LAG / LEAD 결과:
┌────────────┬────────┬─────────────┬─────────────┬──────┐
│ order_date │ amount │ prev_amount │ next_amount │ diff │
├────────────┼────────┼─────────────┼─────────────┼──────┤
│ 2026-03-01 │ 1000 │ NULL │ 1500 │ NULL │
│ 2026-03-02 │ 1500 │ 1000 │ 1200 │ +500 │
│ 2026-03-03 │ 1200 │ 1500 │ 1800 │ -300 │
│ 2026-03-04 │ 1800 │ 1200 │ NULL │ +600 │
└────────────┴────────┴─────────────┴─────────────┴──────┘
LAG(amount, 1) : 1행 이전의 amount
LEAD(amount, 1) : 1행 이후의 amount
→ 전일 대비 증감 계산에 자주 사용
5.5 누적 합계와 이동 평균
1
2
3
4
5
6
7
8
9
10
11
12
13
-- 누적 합계 (Running Total)
SELECT order_date, amount,
SUM(amount) OVER (ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) AS running_total
FROM daily_sales;
-- 3일 이동 평균
SELECT order_date, amount,
AVG(amount) OVER (ORDER BY order_date
ROWS BETWEEN 2 PRECEDING
AND CURRENT ROW) AS moving_avg_3d
FROM daily_sales;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
프레임 절 (ROWS BETWEEN) 이해:
ROWS BETWEEN ... AND ...
┌──────────────────────────────────────────────────────────┐
│ UNBOUNDED PRECEDING : 파티션의 첫 행 │
│ N PRECEDING : N행 이전 │
│ CURRENT ROW : 현재 행 │
│ N FOLLOWING : N행 이후 │
│ UNBOUNDED FOLLOWING : 파티션의 마지막 행 │
└──────────────────────────────────────────────────────────┘
예: 3일 이동 평균
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
행 1: [■] → avg(행1)
행 2: [■ ■] → avg(행1, 행2)
행 3: [■ ■ ■] → avg(행1, 행2, 행3)
행 4: [■ ■ ■] → avg(행2, 행3, 행4)
행 5: [■ ■ ■] → avg(행3, 행4, 행5)
└─ 윈도우가 이동! ─┘
5.6 NTILE — 균등 분할
1
2
3
4
-- 급여 기준 4분위수 분류
SELECT name, salary,
NTILE(4) OVER (ORDER BY salary) AS quartile
FROM employees;
1
2
3
4
5
6
7
8
9
10
11
12
┌──────┬────────┬──────────┐
│ name │ salary │ quartile │
├──────┼────────┼──────────┤
│ 박민수│ 3000 │ 1 │ ← 하위 25%
│ 이영희│ 4000 │ 1 │
│ 김철수│ 5000 │ 2 │ ← 25~50%
│ 최지은│ 6000 │ 2 │
│ 정다연│ 7000 │ 3 │ ← 50~75%
│ 한지민│ 8000 │ 3 │
│ 강동원│ 9000 │ 4 │ ← 상위 25%
│ 송혜교│ 10000 │ 4 │
└──────┴────────┴──────────┘
6. Cost-Based Optimizer (CBO)의 의사결정
6.1 옵티마이저란
SQL은 선언적 언어이다. “무엇을 원하는지”만 명시하고, “어떻게 실행하는지”는 옵티마이저가 결정한다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
쿼리 실행 과정:
SQL 쿼리
│
▼
┌──────────────┐
│ Parser │ SQL 문법 검사, AST 생성
└──────┬───────┘
▼
┌──────────────┐
│ Optimizer │ 실행 계획 생성 (핵심!)
│ │ 여러 후보 계획 중 최적 선택
└──────┬───────┘
▼
┌──────────────┐
│ Executor │ 실행 계획대로 데이터 조회
└──────────────┘
6.2 Cost 계산
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
옵티마이저의 Cost 계산 요소:
┌──────────────────────────────────────────────────────┐
│ 1. 통계 정보 (Statistics): │
│ - 테이블의 행 수 (Cardinality) │
│ - 컬럼의 고유 값 수 (Selectivity) │
│ - 인덱스의 깊이, 리프 페이지 수 │
│ - 데이터 분포 (히스토그램) │
│ │
│ 2. Cost 모델: │
│ Cost = (디스크 I/O 비용) + (CPU 비용) + (메모리 비용) │
│ │
│ - Sequential Read: 1.0 (기준) │
│ - Random Read: 4.0 (SSD 기준, HDD는 더 큼) │
│ - CPU 연산: 0.01 │
│ │
│ 3. 후보 실행 계획 비교: │
│ Plan A: Full Table Scan → Cost = 1000 │
│ Plan B: Index Scan + Join → Cost = 50 │
│ Plan C: Hash Join → Cost = 80 │
│ → Plan B 선택! │
└──────────────────────────────────────────────────────┘
6.3 통계 정보의 중요성
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
통계가 부정확하면 옵티마이저가 잘못된 계획을 선택한다!
예: orders 테이블에 1000만 행, status 컬럼
┌────────────────────────────────────────────────────┐
│ 실제 데이터 분포: │
│ status = 'COMPLETED' : 9,990,000행 (99.9%) │
│ status = 'PENDING' : 10,000행 (0.1%) │
│ │
│ 통계가 정확하면: │
│ WHERE status = 'PENDING' → 인덱스 스캔 (10,000행) │
│ WHERE status = 'COMPLETED' → 풀 스캔 (99.9%) │
│ │
│ 통계가 부정확하면 (균등 분포로 가정): │
│ WHERE status = 'PENDING' → 풀 스캔 (비효율!) │
│ → 옵티마이저가 500만 행(50%)이라고 예측 │
└────────────────────────────────────────────────────┘
통계 갱신:
-- MySQL
ANALYZE TABLE orders;
-- PostgreSQL
ANALYZE orders;
권장: 대량 데이터 변경 후 반드시 ANALYZE 실행
6.4 옵티마이저 힌트
옵티마이저의 판단이 잘못될 때 개발자가 직접 실행 계획을 유도할 수 있다.
1
2
3
4
5
6
7
8
9
10
11
12
-- MySQL 옵티마이저 힌트
SELECT /*+ JOIN_ORDER(d, e) */ -- 조인 순서 지정
/*+ NO_INDEX(e idx_name) */ -- 특정 인덱스 사용 금지
/*+ HASH_JOIN(e, d) */ -- 해시 조인 강제
e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;
-- PostgreSQL
SET enable_hashjoin = off; -- 해시 조인 비활성화
SET enable_nestloop = off; -- NLJ 비활성화
-- → Sort-Merge Join만 남음
1
2
3
4
5
6
7
8
9
10
힌트 사용 원칙:
┌──────────────────────────────────────────────────┐
│ 1. 힌트는 최후의 수단 │
│ → 먼저 인덱스 추가, 쿼리 리라이팅, 통계 갱신 시도 │
│ │
│ 2. 힌트를 쓰면 코드 리뷰에서 반드시 사유를 남기기 │
│ → 데이터 분포가 변하면 힌트가 오히려 해가 될 수 있음│
│ │
│ 3. 주기적으로 힌트의 유효성 재검증 │
└──────────────────────────────────────────────────┘
7. GROUP BY와 HAVING 최적화
7.1 GROUP BY의 동작 원리
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
GROUP BY 실행 방식:
방식 1: Sort (정렬 후 그룹핑)
┌──────────────────────────────────────────────────┐
│ 1. GROUP BY 컬럼으로 정렬 (O(N log N)) │
│ 2. 정렬된 결과를 순차 스캔하며 같은 값끼리 그룹 │
│ 3. 각 그룹에 대해 집계 함수 적용 │
│ │
│ 인덱스가 GROUP BY 컬럼 순서와 일치하면 정렬 생략! │
└──────────────────────────────────────────────────┘
방식 2: Hash (해시 테이블 이용)
┌──────────────────────────────────────────────────┐
│ 1. GROUP BY 값을 해시하여 버킷에 할당 │
│ 2. 같은 버킷의 행들에 대해 집계 함수 적용 │
│ 3. 정렬 불필요 → 대용량 데이터에서 효율적 │
│ │
│ PostgreSQL: HashAggregate │
│ MySQL 8.0+: 내부적으로 임시 테이블 사용 │
└──────────────────────────────────────────────────┘
7.2 GROUP BY 최적화 기법
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 비효율적: HAVING으로 필터링 (집계 후 필터)
SELECT dept_id, COUNT(*) AS cnt
FROM employees
WHERE salary > 0 -- 모든 행 스캔
GROUP BY dept_id
HAVING dept_id IN (10, 20); -- 집계 후 필터링!
-- → dept_id 30, 40도 집계한 후 버림
-- 효율적: WHERE로 먼저 필터링 (집계 전 필터)
SELECT dept_id, COUNT(*) AS cnt
FROM employees
WHERE salary > 0
AND dept_id IN (10, 20) -- 집계 전에 필터링!
GROUP BY dept_id;
-- → dept_id 10, 20만 집계
-- 원칙: WHERE에서 필터 가능한 조건은 HAVING이 아니라 WHERE에!
-- HAVING은 집계 함수 조건(COUNT, SUM 등)에만 사용
1
2
3
4
5
6
7
8
-- GROUP BY에 인덱스 활용
-- 인덱스: INDEX idx_dept_salary (dept_id, salary)
SELECT dept_id, AVG(salary)
FROM employees
GROUP BY dept_id;
-- → 인덱스가 dept_id로 정렬되어 있으므로 정렬 생략!
-- → EXPLAIN에서 "Using index" 확인 가능
8. JPA/Spring 환경에서의 쿼리 최적화
8.1 JPQL과 네이티브 쿼리의 선택
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {
// JPQL: 엔티티 기반, DB 독립적
// → 단순 조회에 적합
@Query("SELECT o FROM Order o WHERE o.status = :status")
List<Order> findByStatus(@Param("status") OrderStatus status);
// 네이티브 쿼리: SQL 직접 사용
// → 윈도우 함수, 복잡한 JOIN, DB 종속 기능 필요 시
@Query(value = """
SELECT o.*,
ROW_NUMBER() OVER (
PARTITION BY o.customer_id
ORDER BY o.created_at DESC
) AS rn
FROM orders o
WHERE o.status = :status
""", nativeQuery = true)
List<Object[]> findLatestOrderPerCustomer(@Param("status") String status);
// QueryDSL: 타입 안전한 동적 쿼리
// → 검색 조건이 동적으로 변하는 경우
}
8.2 Projection으로 SELECT 최적화
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
// 비효율적: 모든 컬럼을 가져옴 (SELECT *)
@Query("SELECT o FROM Order o WHERE o.customerId = :id")
List<Order> findByCustomerId(Long id);
// → 불필요한 대량 컬럼(description, metadata 등)도 조회
// 효율적: 필요한 컬럼만 Projection
public interface OrderSummary {
Long getId();
String getStatus();
LocalDateTime getCreatedAt();
}
@Query("SELECT o.id AS id, o.status AS status, " +
"o.createdAt AS createdAt FROM Order o " +
"WHERE o.customerId = :id")
List<OrderSummary> findSummaryByCustomerId(@Param("id") Long id);
// → SELECT o.id, o.status, o.created_at만 조회
// → 네트워크 전송량과 메모리 사용량 감소
8.3 Batch Size로 N+1 해결과 쿼리 최적화
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
// application.yml
// spring:
// jpa:
// properties:
// hibernate:
// default_batch_fetch_size: 100
// order_inserts: true
// order_updates: true
// jdbc:
// batch_size: 50
// Bulk Insert 최적화
@Transactional
public void bulkInsert(List<Order> orders) {
// JPA의 persist()는 건건이 INSERT → 느림
// JDBC batch를 활용하면 한 번에 50건씩 INSERT
for (int i = 0; i < orders.size(); i++) {
entityManager.persist(orders.get(i));
if (i % 50 == 0) {
entityManager.flush();
entityManager.clear(); // 영속성 컨텍스트 메모리 방지
}
}
}
9. 면접 빈출 질문 정리
Q1. INNER JOIN과 LEFT JOIN의 차이를 설명하세요.
INNER JOIN은 양쪽 테이블에서 조인 조건이 일치하는 행만 반환한다. 어느 한쪽에만 존재하는 행은 결과에서 제외된다.
LEFT JOIN은 왼쪽(기준) 테이블의 모든 행을 반환하고, 오른쪽 테이블에 매칭되는 행이 없으면 NULL로 채운다. 따라서 기준 테이블의 행이 누락되지 않는다.
실무에서 LEFT JOIN은 “매칭되지 않는 데이터”를 찾을 때 자주 사용한다. 예를 들어 LEFT JOIN ... WHERE right_table.id IS NULL로 오른쪽 테이블에 대응되지 않는 행을 조회할 수 있다. INNER JOIN은 양쪽 모두에 데이터가 있을 때만 관심 있는 경우에 사용한다.
Q2. JOIN의 내부 알고리즘(Nested Loop, Hash Join, Sort-Merge)을 설명하세요.
Nested Loop Join: 이중 for 루프처럼 동작한다. 외부 테이블의 각 행에 대해 내부 테이블을 탐색한다. O(M×N)이지만, 내부 테이블에 인덱스가 있으면 O(M×log N)으로 개선된다. 소량 데이터나 인덱스가 있을 때 유리하다.
Hash Join: 작은 테이블로 해시 테이블을 빌드(Build)한 후, 큰 테이블을 순회하며 해시 테이블에서 매칭(Probe)한다. O(M+N)으로 대량 데이터 등가 조인에 효율적이다. 인덱스가 없어도 빠르지만 메모리를 많이 사용하고 등가 조인(=)에서만 가능하다.
Sort-Merge Join: 양쪽 테이블을 조인 키로 정렬한 후, 양쪽 포인터를 동시에 이동하며 병합한다. 이미 정렬된 데이터에서 빠르고 비등가 조인도 가능하지만, 정렬 비용 O(N log N)이 추가된다.
옵티마이저는 테이블 크기, 인덱스 유무, 조인 조건 등을 고려하여 최적의 알고리즘을 자동 선택한다.
Q3. 서브쿼리와 JOIN 중 어떤 걸 써야 하나요?
일반적으로 JOIN을 우선으로 고려한다. 옵티마이저가 JOIN에 대해 더 다양한 실행 계획(NLJ, Hash, Sort-Merge)을 선택할 수 있고, 인덱스 활용도 용이하다.
특히 상관 서브쿼리(Correlated Subquery)는 외부 행마다 서브쿼리를 반복 실행하므로 성능이 나쁠 수 있다. 스칼라 서브쿼리(SELECT 절)도 행 수만큼 반복 실행될 위험이 있다. 이런 경우 JOIN으로 리라이팅하면 성능이 크게 개선된다.
다만 비상관 서브쿼리가 간단하고 결과 집합이 작은 경우, 현대 옵티마이저(MySQL 8.0+)가 내부적으로 Semi-Join으로 변환하므로 성능 차이가 거의 없다. EXISTS로 존재 여부만 확인하는 경우에도 서브쿼리가 자연스럽다. 결론적으로 가독성과 성능을 모두 고려하되, EXPLAIN으로 실행 계획을 확인하는 것이 정확하다.
Q4. NOT IN과 NOT EXISTS의 차이를 설명하세요.
가장 큰 차이는 NULL 처리이다. NOT IN의 서브쿼리 결과에 NULL이 포함되면, SQL의 3-값 논리(TRUE/FALSE/UNKNOWN)에 의해 어떤 행도 반환되지 않는다. x NOT IN (1, NULL)은 x != 1 AND x != NULL로 평가되는데, x != NULL은 항상 UNKNOWN이므로 전체가 UNKNOWN이 되어 어떤 행도 통과하지 못한다.
NOT EXISTS는 상관 서브쿼리로 동작하며, 행의 존재 여부만 확인하므로 NULL에 안전하다. 따라서 NOT IN 대신 NOT EXISTS를 습관적으로 사용하는 것이 안전하다. 성능 면에서도 NOT EXISTS가 서브쿼리 결과를 메모리에 저장하지 않고 하나라도 찾으면 즉시 중단(Short-Circuit)하므로 유리한 경우가 많다.
Q5. 쿼리가 느릴 때 어떻게 분석하고 최적화하나요?
1단계: EXPLAIN으로 실행 계획을 확인한다. type이 ALL(Full Table Scan)인지, rows 추정치가 실제와 맞는지, key가 NULL인지(인덱스 미사용) 확인한다.
2단계: 인덱스 확인. WHERE/JOIN/ORDER BY/GROUP BY에 사용되는 컬럼에 적절한 인덱스가 있는지, 복합 인덱스의 컬럼 순서가 맞는지 확인한다.
3단계: 쿼리 리라이팅. Sargable 조건으로 변환(컬럼에 함수 적용 제거), 불필요한 서브쿼리를 JOIN으로 변환, OR을 UNION으로 분리, 불필요한 DISTINCT 제거 등을 시도한다.
4단계: 데이터 관점. 통계 정보 갱신(ANALYZE TABLE), 불필요한 컬럼 SELECT 제거(Projection), OFFSET 대신 커서 기반 페이지네이션 적용 등을 검토한다.
EXPLAIN 실행 계획의 상세 해석과 인덱스 설계 전략은 [인덱스 동작 원리 글]을 참고한다.
Q6. 윈도우 함수란 무엇이며, GROUP BY와 어떻게 다른가요?
GROUP BY는 행을 그룹으로 축소하여 그룹당 하나의 결과 행을 반환한다. 원본 행의 개별 정보는 사라진다.
윈도우 함수는 원본 행을 유지하면서 각 행에 대해 “윈도우(범위)” 내의 집계 결과를 추가 컬럼으로 제공한다. OVER(PARTITION BY ... ORDER BY ...)로 윈도우를 정의한다.
예를 들어 “부서별 평균 급여와 각 사원의 급여를 함께 보고 싶다”면, GROUP BY로는 불가능하지만 윈도우 함수 AVG(salary) OVER (PARTITION BY dept_id)로 원본 행을 유지하며 부서별 평균을 추가할 수 있다.
주요 윈도우 함수: ROW_NUMBER(고유 순번), RANK/DENSE_RANK(순위), LAG/LEAD(이전/다음 행), NTILE(균등 분할), SUM/AVG/COUNT + OVER(누적/이동 집계). 실무에서 “부서별 Top N”, “전일 대비 증감”, “누적 매출” 등에 필수적이다.
Q7. OFFSET 기반 페이지네이션이 느린 이유와 대안은?
LIMIT 20 OFFSET 1000000은 내부적으로 1,000,020행을 읽고 앞의 1,000,000행을 버린다. 따라서 OFFSET이 커질수록 선형적으로 느려진다. 100만 페이지를 지나면 수 초가 걸릴 수 있다.
대안은 커서 기반 페이지네이션(Keyset Pagination)이다. 마지막으로 조회한 행의 키 값을 기준으로 WHERE id < 마지막_id ORDER BY id DESC LIMIT 20 형태로 조회한다. 인덱스를 타고 바로 해당 위치부터 20행만 읽으므로, OFFSET에 무관하게 항상 일정한 성능을 보장한다. 단점은 “N번째 페이지로 건너뛰기”가 불가능하여 무한 스크롤 방식에 적합하다.
Q8. Sargable 쿼리란 무엇인가요?
Sargable(Search ARGument ABLE)은 인덱스를 활용할 수 있는 쿼리 조건을 의미한다. 핵심 원칙은 “컬럼을 가공하지 말고, 비교 대상(상수)을 가공하라”이다.
Non-Sargable 예시: WHERE YEAR(created_at) = 2026 — 컬럼에 함수를 적용하면 인덱스의 B+Tree에서 해당 값을 탐색할 수 없어 Full Table Scan이 된다.
Sargable로 변환: WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01' — 컬럼을 그대로 두고 범위 조건으로 표현하면 인덱스의 Range Scan이 가능하다.
같은 원리로 WHERE salary * 12 > 50000000은 WHERE salary > 50000000 / 12로, WHERE CAST(price AS CHAR) = '1000'은 WHERE price = 1000으로 변환해야 한다.
Q9. 옵티마이저가 잘못된 실행 계획을 선택하는 경우는?
옵티마이저는 통계 정보에 기반하여 의사결정을 하므로, 통계가 부정확하면 잘못된 계획을 선택한다. 대량 INSERT/DELETE 후 ANALYZE TABLE을 실행하지 않으면 테이블 행 수, 컬럼별 값 분포 등이 실제와 다를 수 있다.
또한 데이터 분포가 극도로 편향된 경우(예: status가 99.9% COMPLETED, 0.1% PENDING), 히스토그램 없이 균등 분포를 가정하면 인덱스 사용 여부를 잘못 판단한다. 그 외에도 복잡한 다중 테이블 JOIN에서 조인 순서를 잘못 선택하거나, 파라미터 바인딩 시 최초 실행 계획을 캐싱하여 다른 파라미터에 부적합한 계획을 재사용하는 경우가 있다.
대응: 통계 갱신, 히스토그램 생성, 옵티마이저 힌트 사용(최후 수단)으로 해결한다.
Q10. JPA 환경에서 쿼리 성능을 최적화하는 방법은?
1. Projection: SELECT * 대신 필요한 컬럼만 조회한다. 인터페이스/DTO Projection으로 네트워크 전송량과 메모리를 줄인다.
2. N+1 해결: @EntityGraph, JOIN FETCH, default_batch_fetch_size 설정으로 연관 엔티티의 추가 쿼리를 방지한다. (상세는 [N+1 문제 글] 참고)
3. 벌크 연산: 대량 UPDATE/DELETE는 @Modifying @Query로 벌크 처리한다. 영속성 컨텍스트를 거치면 건건이 SQL이 발생한다.
4. 네이티브 쿼리: 윈도우 함수, 복잡한 통계 쿼리 등 JPQL로 표현이 어려운 경우 네이티브 SQL을 사용한다.
5. 쿼리 로그 확인: spring.jpa.show-sql=true 또는 p6spy 라이브러리로 실제 발생하는 SQL과 바인딩 파라미터를 확인하고, EXPLAIN으로 실행 계획을 분석한다.
마무리
SQL은 선언적 언어이지만, 그 이면의 동작 원리를 모르면 성능 최적화가 불가능하다.
- JOIN 종류: INNER, LEFT, RIGHT, FULL, CROSS, SELF — 각각의 결과 집합이 어떻게 다른지 정확히 이해해야 한다.
- JOIN 알고리즘: Nested Loop, Hash Join, Sort-Merge — 옵티마이저가 어떤 기준으로 선택하는지 알아야 느린 쿼리를 개선할 수 있다.
- 서브쿼리: 스칼라, 상관, 비상관 — 특히 NOT IN의 NULL 함정과 EXISTS와의 차이를 반드시 알아야 한다.
- 쿼리 리라이팅: Sargable 조건, OR→UNION, OFFSET→커서 — 같은 결과를 더 효율적으로 얻는 기법이다.
- 윈도우 함수: ROW_NUMBER, LAG/LEAD, 누적 합계 — GROUP BY로 불가능한 분석을 원본 행을 유지하며 수행한다.
인덱스를 아무리 잘 설계해도, SQL 자체가 비효율적이면 한계가 있다. 좋은 SQL을 작성하는 것이 인덱스 설계보다 먼저이다.