Dev Wiki

Ch09-1 옵티마이저의 역할

11 min read

한 줄 정의

옵티마이저는 사용자가 요청한 SQL을 가장 적은 비용으로 실행할 방법 을 결정하는 컴포넌트이며, MySQL은 통계에 기반한 비용 기반 옵티마이저(CBO) 를 사용합니다.

쉽게 말하면

옵티마이저는 요리사 와 같습니다.

  • 손님 주문(SQL): “스테이크 하나 주세요”
  • 요리사는 재료(테이블)와 도구(인덱스) 를 보고, 그릴/팬/오븐 중 어떤 방식이 가장 빠를지 판단합니다
  • 도구의 상태(통계)가 정확해야 올바른 결정을 내릴 수 있습니다 (오븐이 고장난 줄 모르면 헛수고)

옵티마이저의 결정은 완벽하지 않습니다. 통계가 부정확하거나, 비용 모델이 데이터의 특이점을 못 잡으면 잘못된 길을 선택할 수 있습니다.

왜 중요한가?

옵티마이저를 모르면 다음 질문에 답할 수 없습니다.

  • “왜 인덱스를 만들었는데 안 타는가?”
  • “왜 갑자기 쿼리가 느려졌는가?” (데이터는 그대로인데)
  • “왜 EXPLAIN 결과가 환경마다 다른가?”

이 질문들의 답은 모두 옵티마이저의 비용 계산 메커니즘 에 있습니다.

핵심 내용

옵티마이저의 종류

flowchart TD
    OPT["옵티마이저"]
    OPT --> RBO["규칙 기반 (RBO)<br/>Rule-Based Optimizer"]
    OPT --> CBO["비용 기반 (CBO)<br/>Cost-Based Optimizer"]

    RBO --> RBO_DESC["사전에 정의된 규칙 우선순위로 결정<br/>예: 'WHERE에 인덱스 칼럼이 있으면 무조건 인덱스 사용'"]
    CBO --> CBO_DESC["통계 정보로 비용을 계산해 최저 비용 선택<br/>실제 데이터 분포를 반영"]

    style RBO fill:#fdd
    style CBO fill:#dfd
규칙 기반 옵티마이저 (RBO)
  • 데이터 분포와 무관하게 고정된 규칙 으로 판단합니다
  • 예: “인덱스가 있으면 무조건 인덱스 사용”, “FROM 절의 첫 테이블부터 조인”
  • 단순하고 예측 가능하지만, 데이터 특성을 못 살립니다
  • 90년대 초기 DBMS에서 쓰였지만, 현재 MySQL은 RBO를 거의 사용하지 않습니다
비용 기반 옵티마이저 (CBO)
  • 통계 정보를 입력으로 각 실행 계획의 비용을 수치화 합니다
  • 가장 비용이 낮은 계획을 선택합니다
  • 데이터 특성을 반영하지만, 통계가 부정확하면 엉뚱한 선택 을 합니다
  • MySQL을 포함한 현대 DBMS의 표준 방식입니다

MySQL 옵티마이저의 동작 단계

flowchart TB
    SQL["SQL 입력"]
    SQL --> P1["Step 1: 쿼리 변환<br/>서브쿼리를 조인으로 변환 등"]
    P1 --> P2["Step 2: 가능한 실행 계획 나열<br/>조인 순서, 사용 인덱스, 조인 방식 조합"]
    P2 --> P3["Step 3: 각 계획의 비용 계산<br/>I/O 비용 + CPU 비용"]
    P3 --> P4["Step 4: 최저 비용 계획 선택"]
    P4 --> EXEC["Step 5: 실행"]
비용 계산의 근거

옵티마이저가 비용을 계산할 때 참고하는 정보들:

정보출처역할
테이블 통계mysql.innodb_table_stats행 수, 페이지 수
인덱스 통계mysql.innodb_index_stats인덱스 카디널리티
히스토그램 (8.0+)INFORMATION_SCHEMA.COLUMN_STATISTICS칼럼 값 분포
시스템 변수mysql.server_cost, mysql.engine_cost작업 단위 비용
통계 정보 갱신

InnoDB는 통계를 자동 갱신 하지만, 다음 상황에서는 부정확할 수 있습니다.

  • 대량 INSERT/UPDATE/DELETE 직후 (자동 갱신이 따라잡지 못함)
  • 데이터 분포가 급격히 바뀐 경우
  • 샘플링 페이지 수가 부족한 경우
-- 통계 강제 갱신
ANALYZE TABLE employees;
 
-- 인덱스 통계 샘플 페이지 수 증가
SET GLOBAL innodb_stats_persistent_sample_pages = 100;

비용 모델 — server_cost / engine_cost

MySQL 5.7+ 에서는 비용 모델 자체를 사용자가 조정 할 수 있습니다.

SELECT * FROM mysql.server_cost;
SELECT * FROM mysql.engine_cost;
비용 항목의미
disk_temptable_create_cost디스크 임시 테이블 생성 비용
memory_temptable_create_cost메모리 임시 테이블 생성 비용
key_compare_cost키 비교 1회 비용
row_evaluate_cost행 1건 평가 비용
io_block_read_cost디스크 블록 1개 읽기 비용
memory_block_read_cost메모리 블록 1개 읽기 비용

일반적으로 건드리지 않는 게 정답 입니다. 잘못 조정하면 옵티마이저가 시스템 전체에서 이상하게 작동할 수 있습니다.

히스토그램 — MySQL 8.0의 결정적 변화

MySQL 8.0은 칼럼별 값 분포 통계(히스토그램) 를 도입했습니다.

flowchart LR
    subgraph BEFORE["8.0 이전: 카디널리티만"]
        B1["status: 5종"] --> B2["균등 분포로 가정"]
    end

    subgraph AFTER["8.0+: 히스토그램"]
        A1["status: 5종"] --> A2["ACTIVE 90%<br/>DELETED 8%<br/>나머지 2%"]
        A2 --> A3["옵티마이저가<br/>실제 분포 인지"]
    end
히스토그램이 필요한 칼럼
  • 인덱스가 없는데 WHERE에 자주 등장하는 칼럼
  • 값 분포가 심하게 비대칭 인 칼럼 (예: status, type, country_code)
  • 카디널리티는 낮지만 특정 값이 압도적인 칼럼
-- 히스토그램 생성 (100버킷)
ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 100 BUCKETS;
 
-- 확인
SELECT * FROM INFORMATION_SCHEMA.COLUMN_STATISTICS
WHERE TABLE_NAME = 'orders';
 
-- 삭제
ANALYZE TABLE orders DROP HISTOGRAM ON status;

옵티마이저가 잘 틀리는 상황

상황원인대응
통계가 오래됨자동 갱신이 못 따라옴ANALYZE TABLE
데이터 분포 비대칭평균값으로만 추정히스토그램 생성
복잡한 다중 조인가능한 경우의 수 폭증 → 휴리스틱 적용JOIN_ORDER 힌트
함수/연산이 적용된 WHERE인덱스 활용 불가 판정함수 기반 인덱스
파라미터 sniffing 유사 현상첫 실행 계획이 캐시됨(MySQL은 PostgreSQL/Oracle 대비 영향 적음)

정리

RBO vs CBO 비교

기준RBOCBO
결정 근거사전 규칙통계 + 비용 모델
데이터 인지XO
예측 가능성높음낮음 (통계 따라 변동)
현대 DBMS거의 사용 안 함표준

옵티마이저 입력 정보 우선순위

입력영향력갱신 방법
테이블 행 수매우 큼ANALYZE TABLE
인덱스 카디널리티매우 큼ANALYZE TABLE
히스토그램큼 (8.0+)ANALYZE TABLE ... UPDATE HISTOGRAM
비용 모델보통mysql.server_cost, mysql.engine_cost (수정 비권장)

내 생각

  • “옵티마이저가 이상해요”라고 말하기 전에, 통계 정보부터 의심 해야 합니다. 실무에서 옵티마이저가 헛다리 짚는 사례의 70%는 통계 갱신 누락이 원인입니다. ANALYZE TABLE 한 줄로 해결되는 일이 많습니다.

  • 히스토그램은 인덱스의 보완재 입니다. 인덱스를 못 만드는 칼럼(중복이 너무 많거나 자주 변경되는 칼럼)에 히스토그램을 만들면, 인덱스 없이도 옵티마이저가 더 똑똑한 선택을 합니다. 특히 NoSQL 마인드로 설계된 테이블에 효과적입니다.

  • MySQL의 비용 모델은 상수 기반의 단순한 모델 입니다. PostgreSQL의 비용 모델보다 정교함은 떨어지지만, 그만큼 예측 가능 합니다. 통계가 정확하다면 거의 일관된 결정을 내립니다.

  • 옵티마이저의 한계를 이해하면 “이 쿼리는 옵티마이저가 잘 못 풀 것” 을 미리 예측할 수 있습니다. 예: 5개 이상 테이블 조인, OR가 많은 WHERE, 서브쿼리 중첩. 이런 쿼리는 처음부터 단순하게 작성하거나 분리하는 게 정답입니다.

관련 개념

출처

  • Real MySQL 8.0 (1권), 9.1 개요

그래프 뷰

백링크