쿼리 처리 과정

같은 결과를 내는 SQL이라도 내부 실행 방식은 천차만별입니다. 옵티마이저(Optimizer)가 어떻게 쿼리를 처리하는지 이해해야 성능 문제를 해결할 수 있습니다.

SQL을 작성하면 DBMS 내부에서 복잡한 과정을 거쳐 최적의 방법으로 실행됩니다. 이 과정을 이해하면 왜 이 쿼리가 느린지, 어떻게 튜닝해야 하는지를 판단할 수 있습니다. 특히 대규모 시스템에서는 쿼리 하나의 실행 계획이 전체 시스템 성능을 좌우할 수 있으므로, 옵티마이저의 동작 원리를 아는 것이 매우 중요합니다.

SQL 처리 흐름

단계 1: 구문 분석 (Parse)

구문 분석은 세 가지 검사를 수행합니다.

Hard Parse vs Soft Parse

Oracle은 한번 파싱한 SQL을 Shared Pool(또는 Plan Cache)에 캐시합니다. 같은 SQL이 다시 들어오면 파싱을 건너뛰는 Soft Parse가 됩니다.

Hard Parse vs 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 Injection 방지에도 필수적이므로, 반드시 사용해야 합니다.

DBMS별 SQL 캐시

옵티마이저의 역할

단계 2: 최적화 (Optimize)

옵티마이저는 SQL의 실행 계획을 결정하는 DBMS의 핵심 컴포넌트입니다. 같은 결과를 내는 여러 실행 방법 중에서 가장 비용이 낮은 방법을 선택합니다.

옵티마이저의 비유

규칙 기반 vs 비용 기반 옵티마이저

구분	규칙 기반 (RBO)	비용 기반 (CBO)
판단 기준	미리 정해진 규칙 (인덱스 우선 등)	통계 정보 기반 비용 계산
통계 필요	불필요	필수
정확성	데이터 분포 무시	데이터 분포 반영
유연성	고정적	데이터에 따라 다른 계획
현재 사용	Oracle 10g 이후 미지원	현재 표준

RBO의 한계

현재 모든 주요 DBMS는 비용 기반 옵티마이저(CBO)를 사용합니다.

비용(Cost)의 의미

CBO에서 비용은 상대적인 수치이며, 주로 다음을 포함합니다.

통계 정보

CBO가 최적의 실행 계획을 세우려면 데이터에 대한 정확한 통계가 필요합니다. 통계가 오래되면 옵티마이저가 잘못된 판단을 내립니다.

통계의 종류

통계 항목	설명	옵티마이저 활용
테이블 통계	행 수, 블록 수, 평균 행 길이	테이블 스캔 비용 추정
컬럼 통계	NDV(고유 값 수), 최소/최대값, NULL 비율	선택도(Selectivity) 계산
인덱스 통계	깊이, 리프 블록 수, 클러스터링 팩터	인덱스 스캔 비용 추정
히스토그램	데이터 분포의 상세 정보	데이터 편향 반영

선택도란

히스토그램

균일하지 않은 데이터 분포를 정확하게 반영하기 위해 히스토그램을 사용합니다.

통계 수집

통계 수집 (Oracle)

-- 테이블 통계 수집
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'
);

통계 수집 (PostgreSQL)

-- 수동 통계 수집
ANALYZE employees;

-- 특정 컬럼만 분석
ANALYZE employees (department_id, salary);

-- 자동 VACUUM과 함께 통계 수집 (기본 활성화)
-- postgresql.conf: autovacuum = on

통계 수집 (MySQL)

-- InnoDB 테이블 통계는 자동 수집
ANALYZE TABLE employees;

-- 히스토그램 생성 (MySQL 8.0+)
ANALYZE TABLE employees UPDATE HISTOGRAM ON salary WITH 100 BUCKETS;

통계가 오래되면 CBO가 잘못된 실행 계획을 세울 수 있습니다. Oracle은 기본적으로 매일 자동 통계 수집을 수행하며, PostgreSQL은 autovacuum 프로세스가 자동으로 ANALYZE를 실행합니다.

대량 데이터가 한꺼번에 적재된 후에는 수동으로 통계를 갱신하는 것이 좋습니다. 자동 통계 수집이 실행되기 전까지는 이전 통계 정보를 사용하므로, 옵티마이저가 부정확한 판단을 내릴 수 있기 때문입니다.

SQL 실행 순서

SQL은 작성 순서와 실행 순서가 다릅니다. 이를 이해해야 성능 문제를 진단할 수 있습니다.

SQL 작성 순서 vs 실행 순서

작성 순서                 실행 순서
  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

조인 알고리즘

옵티마이저가 선택하는 세 가지 주요 조인 방법입니다.

Nested Loop Join (중첩 루프 조인)

Hash Join (해시 조인)

Sort Merge 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

특징:
  * 양쪽 테이블이 이미 정렬되어 있으면 매우 효율적
  * 비동등 조인(>=, BETWEEN)에도 사용 가능
  * 정렬 비용이 크면 비효율적
  * 인덱스로 정렬을 대체할 수 있으면 유리

조인 방법 비교

실행 계획 읽기

실행 계획(Execution Plan)은 옵티마이저가 선택한 쿼리 처리 방법을 보여줍니다.

실행 계획 확인 (주요 DBMS)

-- 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);

PostgreSQL EXPLAIN ANALYZE 결과 읽기

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)

옵티마이저는 실행 전에 쿼리를 더 효율적인 형태로 자동 변환합니다. 이 변환은 옵티마이저가 내부적으로 수행하므로 사용자가 직접 할 필요는 없지만, 알아두면 쿼리 작성 시 도움이 됩니다.

핵심 정리

다음 절에서는 실행 계획을 읽고 해석하는 방법을 더 자세히 다루겠습니다.

실행 계획을 읽는 능력은 데이터베이스 성능 튜닝의 기본이며, SQL 개발자와 DBA 모두에게 필수적인 역량입니다. EXPLAIN을 습관적으로 사용하면 성능 문제를 사전에 방지할 수 있습니다.

쿼리 성능은 데이터 양, 인덱스 구성, 통계 정확도에 따라 크게 달라지므로 실행 계획을 항상 확인하는 습관을 들여야 합니다.

목차