관계형 데이터베이스 SQL 처리 아키텍처와 옵티마이저(Optimizer) 완벽 해부
사용자가 던진 선언적 질의(SQL)가 데이터베이스 내부에서 어떻게 파싱(Parsing)되고, 옵티마이저(Optimizer)에 의해 최적의 실행 계획(Execution Plan)으로 수립되며, 최종적으로 스토리지 엔진에서 데이터를 추출하는지 시스템 레벨의 처리 메커니즘을 심층 분석한다.
가. SQL의 본질: 선언적(Declarative) 패러다임
C, Java와 같은 절차적 프로그래밍 언어가 데이터를 처리하는 '방법(How)'을 일일이 명시하는 것과 달리, SQL은 관계형 대수(Relational Algebra)에 기반하여 사용자가 원하는 '결과 집합(What)'만을 선언하는 언어이다. 사용자는 어떤 테이블에서 어떤 조건의 데이터를 가져올지만 명시할 뿐, 인덱스를 탈지 풀 스캔(Full Scan)을 할지, 어떤 순서로 테이블을 조인(Join)할지는 전적으로 DBMS 내부의 핵심 두뇌인 옵티마이저(Optimizer)에게 위임된다.
나. 쿼리 성능을 지배하는 내부 아키텍처의 중요성
- 실행 계획(Execution Plan)의 마법: 동일한 결과를 반환하는 SQL이라 하더라도, DBMS가 선택한 데이터 접근 경로(Access Path)와 조인 방식에 따라 응답 속도는 1초에서 수 시간까지 극단적으로 벌어진다.
- SQL 튜닝(Tuning)의 목적: 개발자나 DBA가 SQL 튜닝을 한다는 것은, DBMS의 옵티마이저가 멍청한 실행 계획을 세우지 않도록 올바른 인덱스를 설계하고, 통계 정보를 갱신하며, 필요 시 힌트(Hint)를 부여하여 최소의 디스크 I/O 비용으로 데이터를 추출하도록 유도하는 아키텍처 제어 행위이다.
가. SQL 엔진의 심층 아키텍처 (SGA 기반 실행계획 도출)
입력된 SQL은 공유 메모리 영역(SGA) 내에서 데이터 딕셔너리를 참조하여 파싱되며, 옵티마이저의 내부 3단계 모듈(변환기, 산정기, 생성기)을 거쳐 최종 실행 코드로 컴파일된다.
나. 하드 파싱(Hard Parsing)과 소프트 파싱(Soft Parsing)
| 파싱 유형 | 메커니즘 설명 | DB 성능에 미치는 영향 및 시사점 |
|---|---|---|
| Soft Parsing (캐시 히트) |
입력된 SQL의 해시값을 라이브러리 캐시(Library Cache)에서 검색하여, 이미 과거에 컴파일되어 최적화가 끝난 실행 계획을 재사용하는 과정이다. | 옵티마이저의 무거운 연산을 완전히 건너뛰므로 CPU 부하가 전혀 없으며 처리 속도가 극히 빠르다. 바인드 변수(Bind Variable, WHERE id = ?) 사용이 필수적이다. |
| Hard Parsing (캐시 미스) |
캐시에 동일한 SQL이 존재하지 않아, 딕셔너리(Data Dictionary)에서 통계 정보를 읽어와 수만 가지의 실행 경로를 평가하고 최적의 플랜을 새로 생성하는 과정이다. | CPU 연산 자원을 대량으로 소모하며, 내부적인 래치(Latch) 경합을 유발하여 다중 동시 접속 환경에서 데이터베이스 전체 멈춤(Hang)의 주범이 된다. 리터럴 SQL 사용 시 발생한다. |
가. RBO (Rule-Based Optimizer) vs CBO (Cost-Based Optimizer)
- 규칙 기반 옵티마이저 (RBO): 과거 오라클 9i 이전의 구시대적 방식으로, 사전에 정의된 15개의 규칙(우선순위)에 따라 실행 계획을 결정한다. (예: 인덱스 매칭이 무조건 풀 스캔보다 우선). 데이터의 양이나 분포도를 전혀 고려하지 않는 심각한 한계가 있다.
- 비용 기반 옵티마이저 (CBO): 현대 RDBMS의 표준 아키텍처이다. 대상 테이블의 레코드 수, 블록 수, 인덱스의 깊이(Depth), 컬럼의 데이터 분포(Histogram) 등 통계 정보(Statistics)를 바탕으로 예상되는 디스크 I/O 횟수와 CPU 비용을 수치화(Costing)하여 가장 비용이 낮은 플랜을 채택한다.
나. 옵티마이저 3대 서브 엔진과 통계 정보의 중요성
CBO 아키텍처에서 옵티마이저는 크게 ① 질의 변환기(Query Transformer), ② 비용 산정기(Estimator), ③ 계획 생성기(Plan Generator)로 나뉩니다. 이 중 비용 산정기는 철저하게 '데이터 딕셔너리의 통계 정보'에 의존합니다. 만약 테이블에 대량의 데이터가 적재되거나 삭제(DML)되었음에도 통계 정보 수집(Gather Statistics)을 갱신하지 않으면, 옵티마이저는 과거의 낡은 지표를 기준으로 엉뚱한 인덱스를 타게 되어 치명적인 실행 계획 악화(Plan Regression)를 유발합니다.
가. 데이터 액세스 패스 (Access Path)
스토리지에서 데이터를 읽어오는 기본 전략이다.
- Full Table Scan (FTS): 테이블의 전체 블록(Block)을 처음부터 끝까지 다중 블록 I/O로 순차적으로 읽어들인다. 추출할 데이터의 비중이 10~15%를 넘어설 때는 오히려 랜덤 액세스가 발생하는 인덱스보다 디스크 헤드 이동 비용이 적어 훨씬 빠르다.
- Index Range Scan: B-Tree 인덱스의 루트 블록에서 리프(Leaf) 블록으로 수직 탐색을 한 뒤, 리프 블록 간의 연결 리스트를 수평으로 스캔하며 필요한 데이터를 추출한다. 필터링 대상이 소수일 때 극적인 성능 향상을 보장한다. (단일 블록 랜덤 I/O 발생)
나. 관계형 데이터베이스 조인(Join) 메커니즘 3대장
| 조인 알고리즘 | 동작 원리 및 아키텍처 특성 | 최적의 활용 환경 |
|---|---|---|
| Nested Loop Join (NL 조인) |
프로그래밍의 중첩 for문과 동일하다. 선행 테이블(Driving)의 조건을 만족하는 각 행마다 후행 테이블(Driven)의 인덱스를 반복적으로 탐색(Random Access)하여 매치한다. | 조인되는 데이터의 양이 적고, 후행 테이블의 조인 컬럼에 반드시 인덱스가 존재할 때 OLTP (온라인 트랜잭션) 환경에서 최적의 성능을 낸다. |
| Sort Merge Join (소트 머지 조인) |
조인 컬럼을 기준으로 양쪽 테이블의 데이터를 각각 정렬(Sort)한 후, 스캔해가며 조인을 수행한다. 인덱스가 없어도 처리가 가능하다. | 메모리(Sort Area)를 대량으로 소모하지만, 조인 조건이 등가(=) 조건이 아닌 부등호(<, >) 조건일 때 Hash 조인의 대안으로 사용된다. |
| Hash Join (해시 조인) |
둘 중 크기가 작은 테이블을 읽어 메모리에 해시 맵(Hash Map)을 만들고, 큰 테이블을 순차 스캔하며 해시 함수에 대입해 조인 대상을 찾는다. | 대용량 데이터 집합 간의 등가(=) 조인 시 발생하며, 데이터 웨어하우스(DW) 환경의 대규모 배치(Batch) 처리에서 가장 빠른 속도를 자랑한다. |
가. 데이터 무결성의 보루: ACID 특성
실행 계획을 통해 데이터를 조작(DML)할 때, DBMS는 금융 시스템과 같은 엄격한 환경에서 데이터의 정합성을 보장하기 위해 논리적 작업 단위인 트랜잭션의 ACID(원자성, 일관성, 격리성, 영속성) 원칙을 강제한다. 이를 위해 내부적으로 언두(Undo) 세그먼트와 리두(Redo) 로그 아키텍처를 동원한다.
나. 트랜잭션 격리 수준(Isolation Level) 트레이드오프
다수의 사용자가 동시에 동일한 레코드에 접근할 때 읽기 일관성(Read Consistency)을 어디까지 보장할 것인지에 대한 표준 설정이다. 높은 격리 수준은 필연적으로 동시성 성능을 떨어뜨리는 트레이드오프 관계를 갖는다.
- Read Uncommitted (Level 0): 다른 트랜잭션이 아직 커밋(Commit)하지 않은 더티 데이터(Dirty Data)를 읽을 수 있도록 허용한다. 데이터 정합성에 치명적인 오류(Dirty Read)를 유발한다.
- Read Committed (Level 1): 커밋이 완료된 데이터만 읽을 수 있다. 오라클 등 대부분의 상용 DBMS의 기본 설정이다. 단, 동일 트랜잭션 내에서 조회를 반복할 때 결과가 달라지는 현상(Non-Repeatable Read)이 발생할 수 있다.
- Repeatable Read (Level 2): 트랜잭션이 시작된 시점의 스냅샷(Snapshot) 데이터를 기준으로 읽기를 수행하여 동일 트랜잭션 내 반복 읽기의 일관성을 보장한다. MySQL(InnoDB)의 기본 설정이다.
- Serializable (Level 3): 가장 엄격한 격리 수준으로, 선행 트랜잭션이 읽은 영역에 대해 다른 트랜잭션의 삽입(Insert)까지 완벽히 차단하여 환상 읽기(Phantom Read)를 방지하지만, 동시 처리 성능이 극단적으로 저하된다.
'데이터베이스' 카테고리의 다른 글
| MSA 환경의 데이터 일관성 보장: 2PC의 Prepare-Commit 원리와 Saga(사가) 패턴 비교 (0) | 2026.04.16 |
|---|---|
| 데이터 파이프라인의 진화: Data Lake에서 Data Mesh, 그리고 카파(Kappa) 아키텍처까지 (0) | 2026.04.09 |
| NoSQL 데이터베이스 종류와 특징 비교 정리 (0) | 2026.03.19 |
| 데이터베이스 검색 속도의 비밀: 실행계획 분석과 쿼리 튜닝 전략 (0) | 2026.03.19 |
| 데이터 무결성을 지키는 핵심 기술: 트랜잭션 격리 수준과 MVCC 원리 (0) | 2026.03.19 |
