1. ⬜ 개요 및 정의
- 쿼리 옵티마이저(Query Optimizer)란, SQL 질의문을 가장 효율적으로 실행하기 위한 최적의 실행계획(Execution Plan)을 탐색·선택하는 DBMS 핵심 컴포넌트임
- 복수의 실행 계획 중 비용(Cost)이 가장 낮은 경로를 선택하며, 전체 시스템 성능에 미치는 영향이 절대적임
- 관계형 데이터베이스(RDBMS)의 질의 처리 속도와 효율성은 옵티마이저의 정확도 및 지능 수준에 따라 좌우됨
2. ⬜ 쿼리 처리 흐름 상의 옵티마이저 위치
[SQL Parser] → [Query Rewrite] → [Query Optimizer] → [Execution Plan Generator] → [Query Executor]
- SQL 구문이 파싱된 이후, 논리적 질의 트리(Logical Query Tree) 형태로 변환되며 옵티마이저가 개입함
- 옵티마이저는 통계정보(Catalog Statistics), 테이블 구조 정보, 인덱스 유무, 조인 순서, 히스토그램 등을 참조하여 비용 추정 수행
3. ⬜ 쿼리 옵티마이저의 분류
(1) 규칙 기반 옵티마이저(Rule-Based Optimizer, RBO)
- 고정된 우선순위 규칙 기반으로 실행 계획 선택
- 예: 인덱스가 존재하면 항상 인덱스 사용, 조인 순서 고정 등
- 단순하고 빠르나 복잡한 질의에서 비효율 발생
(2) 비용 기반 옵티마이저(Cost-Based Optimizer, CBO)
- 질의 실행 비용(예: I/O, CPU Time) 을 추정하여 최적 계획 선택
- 테이블 통계, 카디널리티(Cardinality), 인덱스 선택성 등 참조
- 현재 대부분 상용 DBMS는 CBO 채택 중
4. ⬜ 쿼리 옵티마이저의 주요 구성 요소
| 구성 요소 |
설명 |
| 질의 트리 생성기 |
SQL을 논리 연산자로 구성된 트리 형태로 파싱 |
| 재작성기(Query Rewriter) |
View, Subquery, Rule-Based Rewrite 등 수행 |
| 비용 추정기(Cost Estimator) |
통계 기반으로 각 실행 경로의 비용 계산 |
| 플랜 생성기(Plan Generator) |
여러 실행 계획 생성 및 후보군 평가 |
| 최적 플랜 선택기 |
최종적으로 최소 비용 경로 선택 |
5. ⬜ 최적화 대상 및 전략
(1) 조인 순서 결정
- 조인의 순서에 따라 결과 행 수와 I/O 횟수가 급격히 변동
- 예: A ⨝ B ⨝ C 순서와 C ⨝ B ⨝ A 순서는 비용 차이 발생
(2) 접근 경로 결정
- 테이블 접근 방식 결정: Full Scan, Index Scan, Index Only 등
- 인덱스 존재 여부, 선택성, 범위 여부 등 고려
(3) 조인 방식 결정
- Nested Loop Join, Hash Join, Merge Join 등 조인 알고리즘 결정
- 데이터 양, 정렬 여부, 메모리 사용 가능성 등 고려
(4) 서브쿼리 재작성
- IN → EXISTS, Scalar Subquery → JOIN 등 변환 전략 사용
- 비효율적 서브쿼리 구조를 JOIN 방식으로 변경하여 최적화
(5) 통계 기반 힌트 적용
- 옵티마이저 힌트(Optimizer Hint)를 통한 수동 개입 가능
- /+ INDEX(table column) */, /+ USE_HASH(table) */ 등 활용 가능
6. ⬜ 쿼리 옵티마이저의 성능 영향 요인
| 영향 요인 |
설명 |
| 통계정보 정확도 |
오래된 통계는 잘못된 비용 추정으로 인해 비효율적 실행 계획 유발 |
| 인덱스 설계 |
잘 설계된 인덱스는 옵티마이저가 효율적 접근 경로 선택 가능 |
| 쿼리 복잡도 |
서브쿼리, 조건문, 조인 수가 증가할수록 탐색 공간 커짐 |
| 파라미터 스니핑 |
초기 실행 시 사용된 파라미터에 따라 잘못된 플랜 고정될 수 있음 |
| 환경 설정값 |
optimizer_mode, work_mem, query_cache_size 등 DB 설정값 영향 |
7. ⬜ 실무 활용 예시
(1) Oracle
- 옵티마이저 모드: ALL_ROWS / FIRST_ROWS / RULE 등
- DBMS_STATS로 통계 수집 필요
- 힌트 사용 시 CBO 우선순위 조정 가능
(2) PostgreSQL
- 고도화된 비용 기반 옵티마이저 내장
- 실행계획은
EXPLAIN ANALYZE로 확인
- Parallel Query 지원으로 다중 CPU 활용 가능
(3) SQL Server
- 쿼리 저장 계획 캐싱, Adaptive Join, Parameter Sniffing 등 존재
- 최신 버전은 Intelligent Query Processing 기능 포함
8. ⬜ 성능 최적화를 위한 활용 방안
- 정기적인 통계 갱신으로 비용 추정 정확도 확보
- 복잡한 쿼리는 뷰 분해 또는 CTE(Common Table Expression)로 단순화
- 옵티마이저 힌트를 통한 실행 경로 제어
- 실행계획(EXPLAIN PLAN) 분석을 통한 병목 원인 파악
- 인덱스 리디자인 및 파티셔닝 기반 접근 경로 개선
- 매개변수 Sniffing 방지를 위한 강제 변수 고정 또는 Plan Guide 적용
9. ⬜ 결론
- 쿼리 옵티마이저는 관계형 데이터베이스 성능을 결정짓는 핵심 컴포넌트이며, 실행 계획의 품질에 따라 수십 배 성능 차이 발생 가능
- 올바른 통계 정보와 설계된 인덱스, 최적화된 SQL 작성법, 그리고 실행 계획 분석을 통해 옵티마이저가 최적의 실행 계획을 선택하도록 유도하는 것이 중요
- 즉, 옵티마이저는 단순한 내부 처리 요소가 아닌, 성능을 좌우하는 지능형 핵심 처리 엔진임