쿼리 처리 과정
같은 결과를 내는 SQL이라도 내부 실행 방식은 천차만별입니다. 옵티마이저(Optimizer)가 어떻게 쿼리를 처리하는지 이해해야 성능 문제를 해결할 수 있습니다.
SQL을 작성하면 DBMS 내부에서 복잡한 과정을 거쳐 실행 계획이 만들어지고 실행됩니다. 옵티마이저는 통계와 비용 모델을 바탕으로 좋은 계획을 찾지만, 항상 실제 최단 경로를 아는 것은 아닙니다. 이 과정을 이해하면 왜 이 쿼리가 느린지, 추정값과 실제 실행 통계 중 무엇을 비교해야 하는지, 어떤 근거로 튜닝해야 하는지를 판단할 수 있습니다.
SQL 처리 흐름
단계 1: 구문 분석 (Parse)
구문 분석은 SQL 문장을 DBMS가 처리할 수 있는 내부 표현으로 바꾸는 단계입니다. 보통 문법 검사, 객체/컬럼 확인, 권한 확인이 포함되지만, parse, bind, rewrite, cache lookup의 경계는 DBMS마다 다르게 나뉠 수 있습니다.
Hard Parse vs Soft Parse
Oracle은 한번 파싱한 SQL의 커서와 실행 계획을 Shared Pool에 캐시합니다. 같은 SQL이 다시 들어오면 기존 커서를 재사용하는 Soft Parse가 가능하지만, SQL 텍스트, 세션 환경, 권한, 바인드 민감도 같은 조건에 따라 재최적화가 일어날 수도 있습니다. 특히 데이터 분포가 치우친 컬럼에서는 바인드 값에 따라 계획을 나누는 adaptive cursor sharing 같은 기능까지 함께 봐야 합니다.
-- 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 Injection 위험을 낮추고, DBMS가 실행 계획을 재사용할 가능성도 높입니다. 다만 PostgreSQL의 custom/generic plan, Oracle의 bind peeking/adaptive cursor sharing, SQL Server의 parameter sniffing처럼 바인드 값과 계획 재사용의 세부 동작은 DBMS마다 다릅니다.
DBMS별 SQL 캐시
SQL 캐시는 모든 DBMS에서 같은 의미로 동작하지 않습니다. Oracle과 SQL Server는 공유 계획 캐시의 성격이 강하고, PostgreSQL은 세션 안의 prepared statement에서 custom plan과 generic plan 선택이 중요합니다. MySQL은 예전의 Query Cache와 준비된 문장, 옵티마이저 추적 기능을 구분해서 이해해야 합니다.
옵티마이저의 역할
단계 2: 최적화 (Optimize)
옵티마이저는 SQL의 실행 계획을 결정하는 DBMS의 핵심 컴포넌트입니다. 같은 결과를 내는 여러 실행 방법 중에서, 제한된 탐색 시간과 통계 정보를 바탕으로 비용 모델상 가장 낮다고 추정되는 계획을 선택합니다. 따라서 선택된 계획은 “증명된 최단 경로”가 아니라 “현재 정보로 가장 싸다고 추정한 경로”입니다.
옵티마이저의 비유
규칙 기반 vs 비용 기반 옵티마이저
| 구분 | 규칙 기반 (RBO) | 비용 기반 (CBO) |
|---|---|---|
| 판단 기준 | 미리 정해진 규칙 (인덱스 우선 등) | 통계 정보 기반 비용 계산 |
| 통계 의존 | 낮음 | 높음 |
| 한계 | 데이터 분포와 실제 비용 반영 어려움 | 통계와 추정 오류에 영향받음 |
| 유연성 | 고정적 | 데이터와 조건에 따라 다른 계획 |
| 현재 사용 | Oracle에서는 오래전에 폐기 | 주요 DBMS의 일반적 방식 |
RBO의 한계
현재 주요 관계형 DBMS는 대체로 비용 기반 옵티마이저(CBO)를 중심으로 실행 계획을 선택합니다. 다만 탐색 공간이 매우 크기 때문에 휴리스틱, 시간 제한, 캐시된 계획 재사용 같은 현실적인 절충도 함께 사용합니다.
비용(Cost)의 의미
CBO에서 비용은 DBMS 내부의 상대적인 수치입니다. 밀리초나 디스크 I/O 횟수와 일대일로 대응하지 않으며, 주로 다음 요소를 모델링합니다.
통계 정보
CBO가 좋은 실행 계획을 세우려면 데이터에 대한 통계가 필요합니다. 통계가 오래되었거나 컬럼 간 상관관계를 충분히 표현하지 못하면 옵티마이저가 실제와 다른 행 수를 추정할 수 있습니다. 이때 실행 계획을 볼 때 가장 먼저 비교할 값은 “예상 행 수”와 “실제 행 수”입니다.
통계의 종류
| 통계 항목 | 설명 | 옵티마이저 활용 |
|---|---|---|
| 테이블 통계 | 행 수, 블록 수, 평균 행 길이 | 테이블 스캔 비용 추정 |
| 컬럼 통계 | NDV(고유 값 수), 최소/최대값, NULL 비율 | 선택도(Selectivity) 계산 |
| 확장 통계 | 여러 컬럼의 상관관계, 표현식 분포 | 복합 조건 행 수 보정 |
| 인덱스 통계 | 깊이, 리프 블록 수, 클러스터링 팩터 | 인덱스 스캔 비용 추정 |
| 히스토그램 | 데이터 분포의 상세 정보 | 데이터 편향 반영 |
선택도란
히스토그램
균일하지 않은 데이터 분포를 더 잘 반영하기 위해 히스토그램을 사용합니다. 히스토그램은 단일 컬럼의 편향을 보완하지만, 여러 컬럼이 서로 강하게 연결된 조건까지 자동으로 해결하지는 못합니다. 그런 경우에는 PostgreSQL extended statistics, Oracle column group statistics처럼 다중 컬럼 통계를 검토해야 합니다.
통계 수집
-- 테이블 통계 수집
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은 자동 optimizer statistics gathering 작업을 제공하고, 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 / OFFSET위 다이어그램은 학습을 위한 간략화된 흐름입니다. 실제 계획에서는 DISTINCT, LIMIT/OFFSET, 윈도우 함수, 집계 방식이 DBMS와 쿼리 형태에 따라 더 복잡하게 배치될 수 있습니다.
조인 알고리즘
옵티마이저가 선택할 수 있는 대표적인 조인 방법입니다. 실제 지원 범위와 선택 기준은 DBMS, 버전, 인덱스, 메모리, 통계에 따라 달라집니다.
Nested Loop Join (중첩 루프 조인)
Hash Join (해시 조인)
Sort Merge Join (정렬 병합 조인)
동작 원리:
1. Sort 단계: 양쪽 테이블을 조인 키 기준으로 정렬
2. Merge 단계: 정렬된 두 테이블을 동시에 순회하며 매칭
table_a (정렬): 1, 2, 3, 4, 5
table_b (정렬): 1, 3, 3, 5, 7
│ │ │
match match match
특징:
* 양쪽 테이블이 이미 정렬되어 있으면 매우 효율적
* 일반적으로 정렬 가능한 조인 키의 동등 조인에서 설명됨
* 일부 DBMS와 조건에서는 범위 조건 계열 최적화가 가능
* 정렬 비용이 크면 비효율적
* 인덱스로 정렬을 대체할 수 있으면 유리조인 방법 비교
실행 계획 읽기
실행 계획(Execution Plan)은 옵티마이저가 선택했거나 실행 중 사용한 쿼리 처리 방법을 보여줍니다. 예상 계획과 실제 실행 통계가 다를 수 있으므로 둘을 구분해서 봐야 합니다.
-- PostgreSQL
EXPLAIN SELECT * FROM users WHERE email = 'test@test.com';
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@test.com';
-- MySQL 8.0.18+
EXPLAIN SELECT * FROM users WHERE email = 'test@test.com';
EXPLAIN ANALYZE 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);
-- Oracle: 실제 실행 통계가 수집된 커서 확인
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));EXPLAIN 계열은 예상 계획을 보여주고, EXPLAIN ANALYZE나 실제 실행 계획은 쿼리를 실행한 뒤 실제 시간과 실제 행 수를 함께 보여줍니다. SELECT에는 유용하지만, 변경문에 사용할 때는 트랜잭션 처리와 부작용을 주의해야 합니다.
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: 실제 실행 시간쿼리 변환 (Query Transformation)
옵티마이저는 실행 전에 쿼리를 더 효율적인 형태로 변환할 수 있습니다. 서브쿼리 unnesting, predicate pushdown, view merging 같은 변환은 DBMS별 조건을 만족할 때 적용되며, 항상 적용되는 것은 아닙니다.
핵심 정리
다음 절에서는 실행 계획을 읽고 해석하는 방법을 더 자세히 다루겠습니다.
실행 계획을 읽는 능력은 데이터베이스 성능 튜닝의 기본이며, SQL 개발자와 DBA 모두에게 중요한 역량입니다. 느린 쿼리를 조사할 때는 EXPLAIN, 실제 실행 통계, 인덱스, 통계 상태를 함께 봐야 합니다.
쿼리 성능은 데이터 양, 인덱스 구성, 통계 정확도에 따라 크게 달라집니다. 특히 배포 전 핵심 쿼리와 장애 상황의 느린 쿼리는 실행 계획을 근거로 확인해야 합니다.