데이터베이스 옵티마이저(Optimizer) 란

우리는 '무엇(What)'을 요구하고, DB는 '어떻게(How)'를 결정한다

학교 다니던 시절부터 2년차가 되었던 시절에는 데이터베이스는 일종의 '블랙박스' 였다. SQL 문법에 맞춰 데이터를 달라고 요청하면, DB는 알아서 결과를 반환해줬다. 하지만 트래픽이 몰리고 데이터가 수천만 건을 넘어가는 시나리오를 만들어보니 직접 느끼게되었다. "같은 결과를 내는 SQL이라도, 어떻게 수행하느냐에 따라 성능은 매우 크게 차이가 난다."

SQL은 비절차적 언어이다. "데이터를 줘"라고 말할 뿐, "어떤 인덱스를 타고, 어떤 순서로 조인해서 가져와"라고는 명시하지 않는다.

 

'어떻게(How)'를 결정하는 것이 옵티마이저(Optimizer)이다. 시나리오를 만들고 테스트를 해가며 슬로우 쿼리와 싸우며 알게 된 옵티마이저의 작동 원리는 한번 볼 필요가 있다. 

1. 옵티마이저란 무엇인가?

옵티마이저는 사용자가 질의한 SQL을 가장 빠르고 효율적으로 수행하기 위한 최적의 처리 경로(Execution Plan)를 생성하는 DBMS의 핵심 엔진이다.  일종의 내비게이션이다.

• 사용자(SQL): "서울에서 부산으로 가고 싶어."
• 옵티마이저: "현재 교통량(통계 정보)을 보니 경부고속도로보다는 중부내륙을 타는 게 15분 더 빠르겠어." -> 경로 안내(실행 계획)

옵티마이저의 종류

옛날에는 RBO(Rule-Based Optimizer, 규칙 기반)를 썼지만, 현대의 대부분 RDBMS(Oracle, PostgreSQL, MySQL 등)는 CBO(Cost-Based Optimizer, 비용 기반)을 사용한다.

• RBO: 미리 정해진 우선순위 규칙(인덱스가 있으면 무조건 탄다)에 따라 경로를 결정. 데이터의 양이나 분포를 고려하지 않아 유연성이 떨어진다. 
• CBO: 다양한 경로의 비용(Cost)을 계산하여 가장 비용이 적은 경로를 선택한다. 여기서 비용은 I/O 횟수(예상 소요 시간)를 의미한다.

2. 옵티마이저의 최적화 수행 단계

옵티마이저는 이러한 복잡한 과정을 거칩니다.

1. Parser (구문 분석): SQL 문법 검사 및 의미 검사 (테이블/컬럼 존재 여부)
2. Query Transformer (질의 변환): SQL을 더 처리하기 쉬운 형태로 재작성 (View Merging, Subquery Unnesting 등)
3. Plan Generator (대안 생성): 가능한 모든 실행 계획을 생성 (조인 순서 변경, 인덱스 선택 등)
4. Estimator (비용 예측): 각 실행 계획의 예상 비용을 계산 (이 단계가 핵심!)
5. Plan Selection (계획 선택): 최저 비용의 실행 계획을 선택하여 실행 엔진으로 전달

3. 옵티마이저의 판단 근거: 통계 정보 (Statistics)

옵티마이저가 올바른 판단을 내리기 위해서는 정확한 지도가 필요하다. 이 지도가 바로 통계 정보이다.

옵티마이저는 다음과 같은 정보를 바탕으로 비용을 계산한다. 

• Table Statistics: 전체 행 수(Rows), 블록 수(Blocks), 평균 행 길이
• Column Statistics: 컬럼의 고유 값 수(NDV: Number of Distinct Values), NULL 값의 수, 데이터 분포(Histogram)
• System Statistics: CPU 속도, 디스크 I/O 속도 등

통계 정보의 함정

옵티마이저가 엉뚱한 인덱스를 타서 성능이 나락으로 가는 경우의 80%는 통계 정보가 최신 상태가 아니기 때문이다. 대량의 데이터를 Insert/Update 한 직후에는 반드시 ANALYZE 나 GATHER_STATS 명령어를 통해 통계 정보를 갱신해줘야 옵티마이저가 멍청한 짓을 하지 않는다.

4. 옵티마이저가 비용을 계산하는 핵심 요소

옵티마이저가 "이 길이다!"라고 선택할 때 사용하는 세 가지가 있다. 

A. 선택도 (Selectivity)

전체 데이터 중 조건에 맞는 데이터가 차지하는 비율이다. 

• ID = 1 (Unique Key): 선택도가 매우 낮음 (1건) -> Index Scan 유리
• GENDER = 'M': 선택도가 높음 (50%) -> Full Table Scan 유리

B. 카디널리티 (Cardinality)

특정 쿼리 실행 시 결과로 나올 것으로 예상되는 건수이다. 

$$Cardinality = Total Rows \times Selectivity$$

C. 비용 (Cost)

시스템 리소스(I/O, CPU, 메모리)를 얼마나 소비할 것인지에 대한 예측값이다. 

5. 옵티마이저를 돕는 방법 (튜닝의 시작)

옵티마이저가 아무리 똑똑해도 개발자가 도와줘야 할 때가 있다. 

A. 힌트 (Hint) 사용하기

옵티마이저가 데이터 분포의 특이성을 모르거나 실수를 할 때, 개발자가 직접 경로를 지시할 수 있다. 

-- Oracle 예시: idx_user_name 인덱스를 강제로 타게 함
SELECT /*+ INDEX(u idx_user_name) */ 
    name, email 
FROM users u 
WHERE name = '홍길동';

주의: 힌트는 강력하지만, 데이터 상황이 바뀌면 오히려 독이 될 수 있다. 힌트는 '지시'보다는 '최후의 수단'으로 사용해야 한다.

B. 바인드 변수 사용

하드 파싱(Hard Parsing) 부하를 줄여 옵티마이저가 매번 경로를 계산하지 않도록한다. 

C. SQL 단순화

너무 복잡한 서브쿼리나 중첩된 뷰는 옵티마이저가 해석하기 어렵다. 가독성이 좋은 SQL이 튜닝하기도 좋고 옵티마이저에도 좋다.

6. 결론: 실행 계획(Explain Plan)을 친구처럼

주니어 시절엔 쿼리 결과만 나오면 커밋하고 퇴근했었다. 하지만 시간이 지난 시점 실행 계획(Explain Plan)을 확인하는 습관이 생겼다.

옵티마이저는 완벽하지 않다. 하지만 데이터베이스 시스템에서 가장 스마트한 친구는 맞는 것 같다.