13장 : 쿼리 최적화와 실행 계획
쿼리 처리 과정
같은 결과를 내는 SQL이라도 내부 실행 방식은 천차만별입니다. 옵티마이저(Optimizer)가 어떻게 쿼리를 처리하는지 이해해야 성능 문제를 해결할 수 있습니다.
SQL을 작성하면 DBMS 내부에서 복잡한 과정을 거쳐 최적의 방법으로 실행됩니다. 이 과정을 이해하면 왜 이 쿼리가 느린지, 어떻게 튜닝해야 하는지를 판단할 수 있습니다. 특히 대규모 시스템에서는 쿼리 하나의 실행 계획이 전체 시스템 성능을 좌우할 수 있으므로, 옵티마이저의 동작 원리를 아는 것이 매우 중요합니다.
SQL 처리 흐름
SQL 문장 입력
│
▼
┌─────────────┐
│ 구문 분석 │ 문법 검사, 의미 검사 (테이블/컬럼 존재?)
│ (Parse) │ → Shared Pool에 캐시 확인 (Soft Parse)
└──────┬──────┘
│
▼
┌─────────────┐
│ 최적화 │ 실행 계획 생성
│ (Optimize) │ 옵티마이저가 최적 경로 선택
└──────┬──────┘
│
▼
┌─────────────┐
│ 실행 │ 실행 계획대로 데이터 접근
│ (Execute) │ Buffer Cache에서 데이터 읽기/쓰기
└──────┬──────┘
│
▼
┌─────────────┐
│ 결과 반환 │ SELECT의 경우 Fetch
│ (Fetch) │
└─────────────┘단계 1: 구문 분석 (Parse)
구문 분석은 세 가지 검사를 수행합니다.
┌────────────────────────────────────────────────────────┐
│ 1. 문법 검사 (Syntax Check) │
│ SQL 문법이 올바른가? │
│ SELCT * FROM users; → 에러! (SELCT는 잘못된 키워드) │
│ │
│ 2. 의미 검사 (Semantic Check) │
│ 참조하는 객체가 존재하는가? │
│ SELECT * FROM non_existing_table; → 에러! │
│ SELECT invalid_column FROM users; → 에러! │
│ │
│ 3. 권한 검사 (Privilege Check) │
│ 실행 권한이 있는가? │
│ SELECT 권한 보유 여부 확인 │
└────────────────────────────────────────────────────────┘Hard Parse vs Soft Parse
Oracle은 한번 파싱한 SQL을 Shared Pool(또는 Plan Cache)에 캐시합니다. 같은 SQL이 다시 들어오면 파싱을 건너뛰는 Soft Parse가 됩니다.
-- Hard Parse (매번 새로 파싱) — 비효율적
SELECT * FROM users WHERE id = 1;
SELECT * FROM users WHERE id = 2; -- 다른 SQL로 인식!
SELECT * FROM users WHERE id = 3; -- 또 다른 SQL로 인식!
-- Soft Parse (캐시 재사용) — 효율적
SELECT * FROM users WHERE id = :id; -- 바인드 변수 사용
-- id에 1, 2, 3을 넣어도 같은 실행 계획 재사용바인드 변수 미사용: SQL 100만 가지 → 100만 번 Hard Parse
바인드 변수 사용: SQL 1가지 → 1번 Hard Parse + 99만9999번 Soft Parse
Hard Parse 비용: CPU 소모, 래치(Latch) 경합 → 동시 접속 시 병목바인드 변수는 SQL Injection 방지에도 필수적이므로, 반드시 사용해야 합니다.
DBMS별 SQL 캐시
| DBMS | 캐시 영역 | 특징 |
| ---------- | ----------- | ------------------------------ |
| Oracle | Shared Pool | Library Cache에 실행 계획 캐시 |
| MySQL | Query Cache | 8.0에서 제거됨 (비효율적) |
| PostgreSQL | plan_cache | Prepared Statement에서 캐시 |
| SQL Server | Plan Cache | 자동 매개변수화 지원 |옵티마이저의 역할
단계 2: 최적화 (Optimize)
옵티마이저는 SQL의 실행 계획을 결정하는 DBMS의 핵심 컴포넌트입니다. 같은 결과를 내는 여러 실행 방법 중에서 가장 비용이 낮은 방법을 선택합니다.
┌────────────────────────────────────────────────────────┐
│ 1. 접근 방법 (Access Path) │
│ * Full Table Scan vs Index Scan │
│ * 어떤 인덱스를 사용할 것인가 │
│ │
│ 2. 조인 순서 (Join Order) │
│ * 3개 테이블 조인: 3! = 6가지 순서 │
│ * 10개 테이블 조인: 10! = 362만 가지 순서! │
│ * 어떤 순서로 조인하느냐에 따라 성능 차이 극심 │
│ │
│ 3. 조인 방법 (Join Method) │
│ * Nested Loop Join │
│ * Hash Join │
│ * Sort Merge Join │
│ │
│ 4. 기타 결정 │
│ * 병렬 처리 여부 │
│ * 서브쿼리를 조인으로 변환할 것인가 │
│ * 인라인 뷰를 풀어헤칠 것인가 (View Merging) │
│ * 정렬을 인덱스로 대체할 것인가 │
└────────────────────────────────────────────────────────┘옵티마이저의 비유
목적지: 서울 → 부산
경로 후보
경로 1: 경부고속도로 (거리: 400km, 예상시간: 4시간)
경로 2: 중부고속도로 + 호남고속도로 (거리: 450km, 예상시간: 4.5시간)
경로 3: 국도 (거리: 380km, 예상시간: 7시간)
네비게이션은 실시간 교통 정보(= 통계 정보)를 바탕으로
가장 빠른 경로(= 최적 실행 계획)를 선택
교통 정보가 오래되면? → 잘못된 경로 선택 → 통계 최신화 필요규칙 기반 vs 비용 기반 옵티마이저
| 구분 | 규칙 기반 (RBO) | 비용 기반 (CBO) |
|---|---|---|
| 판단 기준 | 미리 정해진 규칙 (인덱스 우선 등) | 통계 정보 기반 비용 계산 |
| 통계 필요 | 불필요 | 필수 |
| 정확성 | 데이터 분포 무시 | 데이터 분포 반영 |
| 유연성 | 고정적 | 데이터에 따라 다른 계획 |
| 현재 사용 | Oracle 10g 이후 미지원 | 현재 표준 |
RBO의 한계
인덱스가 있으면 무조건 인덱스 사용 (RBO 규칙)
상황: users 테이블에 100만 행, gender 컬럼에 인덱스
SELECT * FROM users WHERE gender = 'M';
RBO: 인덱스 있으니 인덱스 스캔! → 50만 건을 인덱스로 하나씩 접근
CBO: 50%의 행을 읽어야 하니 Full Scan이 빠르다!
결과:
RBO (인덱스 스캔): 30초
CBO (Full Scan): 3초현재 모든 주요 DBMS는 비용 기반 옵티마이저(CBO)를 사용합니다.
비용(Cost)의 의미
CBO에서 비용은 상대적인 수치이며, 주로 다음을 포함합니다.
| 비용 요소 | 설명 |
| ------------- | ------------------------------- |
| I/O 비용 | 디스크에서 읽어야 할 블록 수 |
| CPU 비용 | 데이터 처리에 필요한 연산량 |
| 네트워크 비용 | 분산 DB에서 노드 간 데이터 전송 |
| 메모리 비용 | 정렬, 해시 등에 필요한 메모리 |
비용 = 주로 I/O 비용이 지배적
디스크 접근: ~10ms
메모리 접근: ~0.0001ms (10만 배 빠름)
→ 디스크 I/O를 줄이는 것이 성능 튜닝의 핵심통계 정보
CBO가 최적의 실행 계획을 세우려면 데이터에 대한 정확한 통계가 필요합니다. 통계가 오래되면 옵티마이저가 잘못된 판단을 내립니다.
통계의 종류
| 통계 항목 | 설명 | 옵티마이저 활용 |
|---|---|---|
| 테이블 통계 | 행 수, 블록 수, 평균 행 길이 | 테이블 스캔 비용 추정 |
| 컬럼 통계 | NDV(고유 값 수), 최소/최대값, NULL 비율 | 선택도(Selectivity) 계산 |
| 인덱스 통계 | 깊이, 리프 블록 수, 클러스터링 팩터 | 인덱스 스캔 비용 추정 |
| 히스토그램 | 데이터 분포의 상세 정보 | 데이터 편향 반영 |
선택도란
선택도 = 조건을 만족하는 행 수 / 전체 행 수
예시: users 테이블 (100만 행)
WHERE id = 1001 → 선택도: 1/1000000 = 0.000001 (매우 낮음 → 인덱스 유리)
WHERE gender = 'M' → 선택도: 500000/1000000 = 0.5 (높음 → Full Scan 유리)
WHERE city = '서울' → 선택도: 200000/1000000 = 0.2 (중간 → 상황에 따라)
선택도가 낮을수록 (소수의 행만 선택) → 인덱스가 효과적
선택도가 높을수록 (많은 행을 선택) → Full Scan이 효과적히스토그램
균일하지 않은 데이터 분포를 정확하게 반영하기 위해 히스토그램을 사용합니다.
status 컬럼의 분포
'active': 980,000건 (98%)
'inactive': 15,000건 (1.5%)
'banned': 5,000건 (0.5%)
히스토그램 없이: NDV = 3이므로 각 33%로 추정
WHERE status = 'banned' → 33만 건 예상 → Full Scan 선택 ❌
히스토그램 있으면: 실제 분포 반영
WHERE status = 'banned' → 5,000건 예상 → Index Scan 선택 ✅통계 수집
-- 테이블 통계 수집
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'EMPLOYEES');
-- 스키마 전체 통계 수집
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('HR');
-- 히스토그램 포함 통계 수집
EXEC DBMS_STATS.GATHER_TABLE_STATS(
'HR', 'EMPLOYEES',
METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO'
);-- 수동 통계 수집
ANALYZE employees;
-- 특정 컬럼만 분석
ANALYZE employees (department_id, salary);
-- 자동 VACUUM과 함께 통계 수집 (기본 활성화)
-- postgresql.conf: autovacuum = on-- InnoDB 테이블 통계는 자동 수집
ANALYZE TABLE employees;
-- 히스토그램 생성 (MySQL 8.0+)
ANALYZE TABLE employees UPDATE HISTOGRAM ON salary WITH 100 BUCKETS;통계가 오래되면 CBO가 잘못된 실행 계획을 세울 수 있습니다. Oracle은 기본적으로 매일 자동 통계 수집을 수행하며, PostgreSQL은 autovacuum 프로세스가 자동으로 ANALYZE를 실행합니다.
대량 데이터가 한꺼번에 적재된 후에는 수동으로 통계를 갱신하는 것이 좋습니다. 자동 통계 수집이 실행되기 전까지는 이전 통계 정보를 사용하므로, 옵티마이저가 부정확한 판단을 내릴 수 있기 때문입니다.
SQL 실행 순서
SQL은 작성 순서와 실행 순서가 다릅니다. 이를 이해해야 성능 문제를 진단할 수 있습니다.
작성 순서 실행 순서
1. SELECT 1. FROM / JOIN
2. FROM 2. WHERE
3. WHERE 3. GROUP BY
4. GROUP BY 4. HAVING
5. HAVING 5. SELECT
6. ORDER BY 6. DISTINCT
7. LIMIT 7. ORDER BY
8. LIMIT / OFFSETSELECT department, COUNT(*) AS cnt -- 5번째: 결과 컬럼 결정
FROM employees -- 1번째: 테이블 접근
WHERE salary > 3000 -- 2번째: 행 필터링
GROUP BY department -- 3번째: 그룹화
HAVING COUNT(*) >= 3 -- 4번째: 그룹 필터링
ORDER BY cnt DESC -- 6번째: 결과 정렬
LIMIT 10; -- 7번째: 결과 제한
WHERE에서 별칭(cnt) 사용 불가 → SELECT보다 먼저 실행되므로
HAVING에서 집계 함수 사용 가능 → GROUP BY 이후 실행
ORDER BY에서 별칭 사용 가능 → SELECT 이후 실행조인 알고리즘
옵티마이저가 선택하는 세 가지 주요 조인 방법입니다.
Nested Loop Join (중첩 루프 조인)
동작 원리: 이중 반복문
for each row in outer_table: -- 외부 테이블 (드라이빙)
for each row in inner_table: -- 내부 테이블 (프로브)
if join_condition:
output row
특징
* 소량 데이터 + 인덱스 → 매우 빠름
* 외부 테이블이 작고 내부에 인덱스가 있을 때 최적
* 대량 데이터 시 O(N×M) → 느림
* Random I/O 발생 (내부 테이블 접근)
최적 상황: 외부 10건 × 내부 인덱스 → 10번만 인덱스 조회
최악 상황: 외부 100만 × 내부 100만 = 1조 번 비교Hash Join (해시 조인)
동작 원리
1. Build 단계: 작은 테이블을 해시 테이블로 메모리에 로드
hash(join_column) → bucket
2. Probe 단계: 큰 테이블을 스캔하며 해시 매칭
for each row in big_table:
hash(join_column) → bucket에서 매칭 검색
특징
* 대량 데이터 동등 조인(=)에 최적
* 한쪽 테이블이 메모리에 올라갈 크기면 매우 효율적
* 범위 조건(>, <, BETWEEN)에는 사용 불가
* 인덱스 불필요
* O(N + M) 시간 복잡도Sort Merge Join (정렬 병합 조인)
동작 원리:
1. Sort 단계: 양쪽 테이블을 조인 키 기준으로 정렬
2. Merge 단계: 정렬된 두 테이블을 동시에 순회하며 매칭
table_a (정렬): 1, 2, 3, 4, 5
table_b (정렬): 1, 3, 3, 5, 7
│ │ │
match match match
특징:
* 양쪽 테이블이 이미 정렬되어 있으면 매우 효율적
* 비동등 조인(>=, BETWEEN)에도 사용 가능
* 정렬 비용이 크면 비효율적
* 인덱스로 정렬을 대체할 수 있으면 유리조인 방법 비교
| 상황 | 최적 조인 방법 |
| --------------------------- | -------------- |
| 소량 + 인덱스 있음 | Nested Loop |
| 대량 + 등가 조인 (=) | Hash Join |
| 대량 + 이미 정렬됨 | Sort Merge |
| 대량 + 범위 조인 (>, <) | Sort Merge |
| 한쪽 매우 작고 한쪽 매우 큼 | Hash / NL |
| OLTP (소량 반복 조회) | Nested Loop |
| OLAP (대량 분석 쿼리) | Hash / Merge |실행 계획 읽기
실행 계획(Execution Plan)은 옵티마이저가 선택한 쿼리 처리 방법을 보여줍니다.
-- PostgreSQL
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@test.com';
-- MySQL
EXPLAIN SELECT * FROM users WHERE email = 'test@test.com';
-- Oracle
EXPLAIN PLAN FOR SELECT * FROM users WHERE email = 'test@test.com';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);Index Scan using idx_users_email on users (cost=0.42..8.44 rows=1 width=100)
Index Cond: (email = 'test@test.com'::text)
Planning Time: 0.1 ms
Execution Time: 0.05 ms
읽는 방법
* Index Scan: 인덱스 스캔 사용 ✅
* cost=0.42..8.44: 시작 비용..총 비용 (상대적 수치)
* rows=1: 예상 반환 행 수
* width=100: 예상 행 크기 (바이트)
* Execution Time: 실제 실행 시간┌─────────────────────────────────────────────────────────┐
│ 좋은 패턴 │
│ * Index Scan / Index Only Scan │
│ * Nested Loop (소량 데이터) │
│ * Hash Join (대량 등가 조인) │
│ * rows 추정치와 실제 행 수가 비슷 │
├─────────────────────────────────────────────────────────┤
│ 나쁜 패턴 (튜닝 대상) │
│ * Seq Scan / Full Table Scan (대량 테이블) │
│ * Sort (대량 데이터 정렬) │
│ * Nested Loop (대량 × 대량) │
│ * rows 추정치와 실제 행 수가 크게 다름 │
│ → 통계 갱신 필요 │
│ * Using temporary, Using filesort (MySQL) │
└─────────────────────────────────────────────────────────┘쿼리 변환 (Query Transformation)
옵티마이저는 실행 전에 쿼리를 더 효율적인 형태로 자동 변환합니다. 이 변환은 옵티마이저가 내부적으로 수행하므로 사용자가 직접 할 필요는 없지만, 알아두면 쿼리 작성 시 도움이 됩니다.
1. 서브쿼리 풀기 (Subquery Unnesting)
WHERE id IN (SELECT ...) → SEMI JOIN으로 변환
2. 뷰 병합 (View Merging)
FROM (SELECT ... FROM t) v → 직접 t에 접근
3. 조건 이행 (Transitive Closure)
WHERE a.x = b.x AND b.x = 10 → a.x = 10 AND b.x = 10 추가
4. 조건 푸시다운 (Predicate Pushdown)
뷰/서브쿼리 안으로 WHERE 조건을 밀어넣음
5. OR 확장 (OR Expansion)
WHERE a = 1 OR b = 2 → UNION ALL로 변환 (각각 인덱스 활용)핵심 정리
┌────────────────────────────────────────────────────────┐
│ SQL 처리 흐름: Parse → Optimize → Execute → Fetch │
├────────────────────────────────────────────────────────┤
│ 구문 분석 │
│ 문법 검사 → 의미 검사 → 권한 검사 │
│ 바인드 변수 → Soft Parse로 캐시 재사용 │
├────────────────────────────────────────────────────────┤
│ 옵티마이저 │
│ CBO: 통계 기반 비용 계산 (현재 표준) │
│ 결정: 접근 방법, 조인 순서, 조인 방법 │
├────────────────────────────────────────────────────────┤
│ 통계 정보 │
│ 테이블 통계: 행 수, 블록 수 │
│ 컬럼 통계: NDV, 히스토그램 │
│ 인덱스 통계: 깊이, 클러스터링 팩터 │
│ 오래된 통계 → 잘못된 실행 계획! │
├────────────────────────────────────────────────────────┤
│ 조인 알고리즘 │
│ Nested Loop: 소량 + 인덱스 │
│ Hash Join: 대량 + 등가 조인 │
│ Sort Merge: 대량 + 비등가 조인 │
├────────────────────────────────────────────────────────┤
│ 실행 계획 │
│ EXPLAIN으로 확인, Index Scan이 좋고 Full Scan이 나쁨 │
│ 추정 행 수 vs 실제 행 수 비교 → 통계 정확성 확인 │
└────────────────────────────────────────────────────────┘다음 절에서는 실행 계획을 읽고 해석하는 방법을 더 자세히 다루겠습니다.
실행 계획을 읽는 능력은 데이터베이스 성능 튜닝의 기본이며, SQL 개발자와 DBA 모두에게 필수적인 역량입니다. EXPLAIN을 습관적으로 사용하면 성능 문제를 사전에 방지할 수 있습니다.
쿼리 성능은 데이터 양, 인덱스 구성, 통계 정확도에 따라 크게 달라지므로 실행 계획을 항상 확인하는 습관을 들여야 합니다.