Ch09 옵티마이저와 힌트

한 줄 정의

옵티마이저는 SQL을 받아 가장 비용이 적은 실행 계획을 선택 하는 MySQL의 두뇌이며, 힌트는 그 판단이 틀렸을 때 개발자가 개입할 수 있는 마지막 수단 입니다.

쉽게 말하면

옵티마이저는 내비게이션 앱 과 같습니다.

출발지(테이블)와 목적지(결과)를 정해주면, 가능한 경로(실행 계획)를 모두 나열합니다
각 경로의 예상 시간(비용)을 계산합니다
가장 빠른 경로를 골라 운전자(스토리지 엔진)에게 안내합니다

힌트는 “이 길로 가지 마라” / “이 길로 꼭 가라” 같은 운전자의 직접 지시입니다. 평소엔 내비를 믿고 따라가지만, 도로 사정을 더 잘 알 때만 개입합니다.

왜 중요한가?

옵티마이저는 MySQL 성능의 80%를 결정 합니다. 같은 SQL이라도 옵티마이저가 어떻게 해석하느냐에 따라 응답 시간이 1ms와 10초로 갈립니다.

인덱스를 만들어도 안 타는 경우 → 옵티마이저가 풀 스캔이 더 빠르다고 판단
JOIN 순서가 이상한 경우 → 옵티마이저의 통계 정보가 부정확
갑자기 쿼리가 느려진 경우 → 데이터 분포 변화로 실행 계획이 바뀜

이런 현상을 진단하려면 옵티마이저가 어떻게 결정하는지 를 알아야 하고, 결정이 틀렸을 때 힌트로 어떻게 교정하는지 를 알아야 합니다.

핵심 내용

9.1 개요

서브노트	핵심 주제
Ch09-1 옵티마이저의 역할	규칙 기반(RBO) vs 비용 기반(CBO), MySQL의 CBO 동작, 통계 정보

옵티마이저는 단순히 “빠른 길을 찾는다”가 아니라 수많은 경로를 비용 함수로 평가 합니다. 통계 정보가 그 입력값입니다.

9.2 기본 데이터 처리

서브노트	핵심 주제
Ch09-2 기본 데이터 처리	풀 스캔, ORDER BY/GROUP BY/DISTINCT 처리, 내부 임시 테이블, Filesort

Using filesort, Using temporary 같은 실행 계획 항목의 정체와, 이들이 발생하는 원리·회피 방법을 다룹니다.

9.3 고급 최적화

서브노트	핵심 주제
Ch09-3 고급 최적화	옵티마이저 스위치, 조인 알고리즘(BNL/BKA/Hash Join), ICP, MRR, 인덱스 머지, 컨디션 팬아웃, 인비저블 인덱스

MySQL 8.0의 수십 개 옵티마이저 기능 을 정리합니다. 각 기능이 언제 켜지고, 어떤 효과가 있고, 언제 꺼야 하는지가 핵심입니다.

9.4 쿼리 힌트

서브노트	핵심 주제
Ch09-4 쿼리 힌트	인덱스 힌트(USE/FORCE/IGNORE INDEX), 옵티마이저 힌트(`/+ ... /`), 힌트 사용 원칙

힌트는 양날의 검 입니다. 잘 쓰면 즉시 효과를 보지만, 통계가 바뀌어도 힌트는 그대로 남아 장기적으로 발목을 잡습니다.

정리

옵티마이저 전체 지도

flowchart TD
    SQL["SQL 입력"] --> PARSE["Step 1: 파서<br/>SQL을 파스 트리로 변환"]
    PARSE --> PREP["Step 2: 전처리기<br/>객체 존재·권한 확인"]
    PREP --> OPT["Step 3: 옵티마이저<br/>실행 계획 결정"]

    subgraph OPT_DETAIL["옵티마이저 내부 단계"]
        direction TB
        T1["통계 정보 수집"]
        T2["가능한 경로 나열<br/>인덱스/조인 순서/조인 알고리즘"]
        T3["각 경로의 비용 계산"]
        T4["최적 경로 선택"]
        T1 --> T2 --> T3 --> T4
    end

    OPT --> T1
    T4 --> EXEC["Step 4: 실행 엔진"]
    EXEC --> SE["Step 5: 스토리지 엔진<br/>InnoDB 등"]

옵티마이저 vs 힌트 의사결정 흐름

상황	1순위	2순위	3순위
쿼리가 느림	EXPLAIN으로 실행 계획 확인	인덱스 추가/조정	통계 갱신(ANALYZE TABLE)
인덱스가 있는데 안 탐	카디널리티 확인	통계 갱신	옵티마이저 힌트
조인 순서가 이상	통계 갱신	JOIN_ORDER 힌트	STRAIGHT_JOIN
임시로 강제 필요	옵티마이저 힌트 (`/+ ... /`)	인덱스 힌트	마지막 수단

옵티마이저 스위치 핵심 옵션

옵션	기본값	설명
`index_condition_pushdown`	on	인덱스 컨디션 푸시다운
`mrr`	on	멀티 레인지 리드
`block_nested_loop`	on	블록 네스티드 루프 조인
`batched_key_access`	off	배치 키 액세스 조인
`hash_join`	on	해시 조인 (8.0.18+)
`index_merge`	on	인덱스 머지
`condition_fanout_filter`	on	컨디션 팬아웃
`use_invisible_indexes`	off	인비저블 인덱스 사용

내 생각

옵티마이저는 점점 똑똑해지지만, 통계가 거짓말을 하면 모든 게 무너집니다. MySQL 8.0의 가장 큰 변화 중 하나가 히스토그램 도입 인 이유입니다. 카디널리티가 왜곡되기 쉬운 칼럼(예: status, type 같은 enum류)에는 히스토그램을 적극 만들어둬야 옵티마이저가 제대로 판단합니다.
힌트를 쓰기 전에 항상 통계부터 갱신 하는 게 정석입니다. ANALYZE TABLE이 무료로 할 수 있는 가장 강력한 튜닝입니다. 힌트로 강제하면 그 순간엔 빨라지지만, 6개월 후 데이터 분포가 바뀌었을 때 그 힌트가 오히려 발목을 잡습니다.
인덱스 힌트(USE INDEX)는 레거시 입니다. 8.0 이후엔 옵티마이저 힌트(/*+ ... */)가 정식 권장 방식입니다. 인덱스 힌트는 인덱스 이름이 바뀌면 무용지물이 되지만, 옵티마이저 힌트는 더 선언적이고 안정적입니다.
“ORDER BY를 인덱스로 처리하느냐 / Filesort로 처리하느냐”는 체감 성능 차이가 가장 극적인 항목 입니다. EXPLAIN의 Extra 칼럼에 Using filesort가 보이면 일단 의심하고, 인덱스 정렬을 활용할 수 있는지 점검합니다.

출처

Real MySQL 8.0 (1권), Ch09 옵티마이저와 힌트

Dev Wiki

탐색기