Ch09-3 고급 최적화

한 줄 정의

고급 최적화는 optimizer_switch 시스템 변수로 켜고 끌 수 있는 옵티마이저 기능들의 집합 이며, 조인 알고리즘·인덱스 머지·푸시다운 등 MySQL의 모든 “똑똑한 동작”이 여기서 결정됩니다.

쉽게 말하면

옵티마이저는 여러 가지 특수 기술 을 가진 요리사입니다.

• 평소엔 기본 조리법으로 요리합니다 (단순 조인, 인덱스 레인지 스캔)
• 상황에 따라 특수 기술 을 꺼냅니다 (해시 조인, 인덱스 머지, MRR 등)
• 어떤 기술을 켜고 끌지는 optimizer_switch라는 메뉴판 에서 결정합니다

각 기술은 특정 패턴에서만 효과적 이므로, “언제 켜고 언제 끄는지”의 판단이 중요합니다.

왜 중요한가?

MySQL 8.0에 들어 옵티마이저 기능이 폭발적으로 늘었습니다.

• 해시 조인 (8.0.18+) — 대규모 조인의 게임 체인저
• 인비저블 인덱스 (8.0+) — 인덱스 삭제 전 안전한 테스트
• 컨디션 팬아웃 — 다중 조건의 정확한 비용 추정

이 기능들을 모르면 8.0의 진짜 가치를 활용 못 합니다. 또한 갑자기 쿼리가 느려졌을 때 “어떤 옵티마이저 기능이 잘못 작동하는가”를 진단할 수 있어야 합니다.

핵심 내용

옵티마이저 스위치 (optimizer_switch)

옵티마이저의 각 기능을 on/off 로 제어하는 콤마 구분 문자열입니다.

-- 현재 설정 확인
SELECT @@optimizer_switch;
 
-- 특정 기능 끄기
SET SESSION optimizer_switch = 'index_merge=off';
 
-- 특정 기능 켜기 + 다른 기능 끄기
SET SESSION optimizer_switch = 'mrr=on,batched_key_access=off';

주요 스위치 항목

스위치	기본값	설명
index_merge	on	인덱스 머지 (여러 인덱스 동시 사용)
index_merge_intersection	on	머지 - AND
index_merge_union	on	머지 - OR
index_merge_sort_union	on	머지 - 정렬 후 OR
index_condition_pushdown	on	ICP
mrr	on	멀티 레인지 리드
mrr_cost_based	on	MRR 비용 기반 결정
block_nested_loop	on	BNL 조인
batched_key_access	off	BKA 조인
hash_join	on	해시 조인 (8.0.18+)
condition_fanout_filter	on	컨디션 팬아웃
derived_merge	on	파생 테이블 머지
use_invisible_indexes	off	인비저블 인덱스 사용
prefer_ordering_index	on	ORDER BY 인덱스 우선 (8.0.21+)

조인 최적화 알고리즘

네스티드 루프 조인 — 기본

가장 단순한 조인 방식입니다.

flowchart LR
    OUT["외부 테이블 한 행"] --> IN["내부 테이블 인덱스 탐색"]
    IN --> NEXT["다음 외부 행"]
    NEXT --> IN

내부 테이블에 조인 칼럼 인덱스가 있을 때 효율적입니다. 인덱스가 없으면 외부 행마다 내부 풀 스캔 → 재앙입니다.

블록 네스티드 루프 조인 (BNL)

내부 테이블에 인덱스가 없을 때 사용되는 변형입니다.

flowchart LR
    OUT["외부 행을 join_buffer에 모음"] --> SCAN["내부 테이블 1회 풀 스캔"]
    SCAN --> MATCH["버퍼의 모든 외부 행과 한 번에 매치"]

• 내부 풀 스캔 횟수를 외부 행 수 → 1회로 감소
• 단, 여전히 풀 스캔이라 대용량에서는 느림
• MySQL 8.0.20+ 에서 해시 조인이 BNL을 대체 하기 시작했습니다

배치 키 액세스 조인 (BKA)

내부 테이블에 인덱스가 있지만 랜덤 I/O가 많을 때 사용합니다.

flowchart LR
    OUT["외부 행을 join_buffer에 모음"] --> SORT["키 정렬"]
    SORT --> MRR_R["MRR로 내부 테이블 접근<br/>랜덤 I/O를 순차 I/O로 전환"]

• MRR과 결합되어 동작
• 기본 off → 켜려면 optimizer_switch='batched_key_access=on,mrr=on,mrr_cost_based=off'
• 디스크 I/O가 병목인 환경에서 효과적

해시 조인 (Hash Join) — MySQL 8.0.18+

대규모 조인의 게임 체인저입니다.

flowchart LR
    BUILD["작은 쪽 테이블"] --> HT["메모리에 해시 테이블 빌드"]
    PROBE["큰 쪽 테이블"] --> LOOKUP["각 행을 해시 테이블에서 조회"]
    HT --> LOOKUP

• 두 테이블 모두 인덱스 없이 조인 가능
• 메모리 한도 (join_buffer_size) 초과 시 디스크로 분할 처리 (Grace Hash Join)
• 등가 조인(=)에만 적용 가능, 부등호/범위 조인은 불가
• 분석/배치 쿼리에서 매우 효과적

조인 알고리즘 선택 흐름

flowchart TD
    JOIN["조인 만남"] --> Q1{내부 테이블에<br/>조인 인덱스?}
    Q1 -->|Yes| Q2{랜덤 I/O 많은가?}
    Q1 -->|No| Q3{등가 조인?}

    Q2 -->|Yes, BKA on| BKA["BKA 조인"]
    Q2 -->|No| NL["네스티드 루프"]

    Q3 -->|Yes| HJ["해시 조인 (8.0.18+)"]
    Q3 -->|No| BNL["BNL 조인 (구버전)"]

인덱스 컨디션 푸시다운 (ICP)

WHERE 조건의 일부를 스토리지 엔진까지 푸시다운 해서, 데이터 파일에 접근하기 전에 필터링합니다.

flowchart LR
    subgraph Without["ICP 없을 때"]
        SE1["스토리지: 인덱스 키만 매치"] --> ME1["MySQL 엔진: 데이터 가져와서 필터"]
    end

    subgraph With["ICP 적용 시"]
        SE2["스토리지: 인덱스 키 매치 + 추가 조건 평가"] --> ME2["MySQL 엔진: 통과한 행만 받음"]
    end

적용 예

-- 인덱스: (last_name, first_name)
SELECT * FROM employees
WHERE last_name = 'Choi' AND first_name LIKE '%kim';

• last_name = 'Choi' → 인덱스 사용
• first_name LIKE '%kim' → 인덱스 사용 불가지만, 인덱스에 first_name이 있으므로 스토리지 엔진이 인덱스 단계에서 미리 평가
• Extra: Using index condition 표시

멀티 레인지 리드 (MRR)

세컨더리 인덱스로 PK를 얻은 후 PK를 정렬해서 일괄 접근 하는 최적화입니다.

flowchart LR
    SEC["세컨더리 인덱스 스캔"] --> COLLECT["PK 값 수집"]
    COLLECT --> SORT["PK 정렬"]
    SORT --> ACCESS["PK 순서대로 데이터 접근<br/>랜덤 I/O를 순차 I/O로"]

• 결과 순서가 인덱스 순이 아닌 PK 순 으로 바뀜에 주의
• 디스크 I/O가 많은 대량 조회에서 효과적
• BKA 조인의 핵심 메커니즘

인덱스 머지 (Index Merge)

여러 인덱스를 동시에 사용 해 결과를 결합합니다.

세 가지 변형

flowchart TD
    QUERY["WHERE a=1 AND b=2"] --> ISECT["방식 1: 교집합 intersection"]

    QUERY2["WHERE a=1 OR b=2"] --> UNION["방식 2: 합집합 union"]

    QUERY3["WHERE a > 10 OR b > 20"] --> SORT_UNION["방식 3: 정렬 합집합 sort_union"]

머지 방식	사용 상황
Intersection	AND 조건이 각각 다른 인덱스
Union	OR 조건이 각각 다른 인덱스 (정렬 결과 보장)
Sort Union	OR 조건의 결과가 정렬되지 않을 때

인덱스 머지의 한계

• 복합 인덱스가 없을 때의 차선책 입니다
• 일반적으로 두 인덱스 사용 < 잘 설계된 복합 인덱스 1개 가 더 빠릅니다
• 인덱스 머지가 자주 보이면 → 복합 인덱스 설계를 재검토할 신호

컨디션 팬아웃 (Condition Fanout Filter)

여러 WHERE 조건의 개별 선택도를 곱해 비용을 더 정확히 추정합니다.

SELECT * FROM employees
WHERE first_name = 'Matt' AND birth_date >= '1965-01-01';

• 옛날엔 first_name='Matt'만으로 비용 추정
• 8.0의 컨디션 팬아웃은 birth_date >= ...의 선택도까지 곱해 더 작은 행 수 로 추정
• 히스토그램이 있으면 더 정확

인덱스 스킵 스캔 (Skip Scan) — 8.0+

복합 인덱스 (A, B)에서 A 조건이 없어도 인덱스를 활용합니다.

-- 인덱스: (gender, birth_date)
SELECT * FROM employees WHERE birth_date >= '1965-02-01';
-- → gender의 유니크 값(M/F)마다 내부 for-loop

• 선행 칼럼의 유니크 값이 적을 때만 효과적
• 많으면 풀 스캔과 다름없음

파생 테이블 머지 (Derived Merge)

FROM (SELECT ...) AS sub 형태의 서브쿼리를 외부 쿼리와 통합 합니다.

-- 변환 전
SELECT * FROM (SELECT * FROM employees WHERE dept_no = 'd001') AS e
WHERE e.hire_date > '2000-01-01';
 
-- 변환 후 (옵티마이저 내부)
SELECT * FROM employees
WHERE dept_no = 'd001' AND hire_date > '2000-01-01';

derived_merge=on (기본). 통합되면 임시 테이블을 만들지 않으므로 훨씬 빠릅니다.

인비저블 인덱스 (Invisible Index) — 8.0+

옵티마이저에게 “이 인덱스는 없는 척” 하라고 알리는 기능입니다.

-- 인덱스 숨기기 (실제로 유지되지만 옵티마이저는 무시)
ALTER TABLE employees ALTER INDEX ix_firstname INVISIBLE;
 
-- 다시 보이게
ALTER TABLE employees ALTER INDEX ix_firstname VISIBLE;

활용 시나리오

• 인덱스 삭제 전 영향 평가 (성능 회귀 발생 시 즉시 복원)
• 새 인덱스 도입 전 시뮬레이션
• 특정 세션에서만 보고 싶을 때 → optimizer_switch='use_invisible_indexes=on'

안티 세미 조인 (Antijoin) 변환

NOT IN, NOT EXISTS 같은 부정 서브쿼리를 안티 조인 으로 변환합니다.

-- 변환 전
SELECT * FROM employees
WHERE emp_no NOT IN (SELECT emp_no FROM dept_manager);
 
-- 변환 후 (옵티마이저 내부)
SELECT e.* FROM employees e
LEFT JOIN dept_manager d ON e.emp_no = d.emp_no
WHERE d.emp_no IS NULL;

서브쿼리 반복 실행을 단일 조인 으로 바꿔 비약적 성능 향상.

정리

조인 알고리즘 비교

알고리즘	인덱스 필요	적합한 상황	특이사항
네스티드 루프	내부 테이블에 필요	작은 결과 / OLTP	기본
BNL	불필요	인덱스 없는 조인	8.0.20+ 에서 해시 조인이 대체
BKA	필요	랜덤 I/O 많은 환경	기본 off
Hash Join (8.0.18+)	불필요	대규모 분석 / 등가 조인	메모리 부족 시 디스크 분할

옵티마이저 스위치 튜닝 우선순위

순위	작업
1	통계 갱신 (ANALYZE TABLE)
2	히스토그램 추가
3	인덱스 재설계 (인덱스 머지가 자주 보이면 복합 인덱스로)
4	옵티마이저 스위치 조정 (마지막 수단)

Extra 칼럼으로 본 고급 최적화 흔적

Extra	적용된 최적화
Using index condition	ICP
Using join buffer (hash join)	해시 조인
Using join buffer (Block Nested Loop)	BNL
Using join buffer (Batched Key Access)	BKA
Using intersect(...)	인덱스 머지 - 교집합
Using union(...)	인덱스 머지 - 합집합
Using sort_union(...)	인덱스 머지 - 정렬 합집합
Using index for skip scan	인덱스 스킵 스캔

내 생각

• 해시 조인의 등장은 MySQL 8.0의 가장 큰 사건 입니다. 그동안 MySQL은 분석성 쿼리에 약하다는 평을 받았는데, 해시 조인 덕분에 수백만 건 × 수백만 건 조인 도 합리적인 시간에 처리됩니다. 8.0.18 이상으로 올리면 일부 배치 작업이 자동으로 빨라지는 이유입니다.

• 인덱스 머지는 차선책일 뿐 입니다. EXPLAIN에 Using intersect나 Using union이 보이면, “여기에 잘 설계된 복합 인덱스가 있어야 하는데 없구나”의 신호로 봐야 합니다. 머지가 가능하다고 좋아할 게 아닙니다.

• 인비저블 인덱스는 인덱스 정리 작업의 안전벨트 입니다. 사용하지 않는 것 같은 인덱스를 발견했을 때, 바로 DROP하지 말고 일단 INVISIBLE로 만들어 일주일 정도 모니터링한 뒤 삭제하면 사고를 막을 수 있습니다.

• 옵티마이저 스위치를 세션 단위로 조정할 수 있다는 점이 중요합니다. 특정 배치 작업에서만 BKA를 켜는 식으로, 전역에 영향 주지 않고 부분 최적화가 가능합니다. SET SESSION optimizer_switch = '...'를 적극 활용해야 합니다.

• ICP는 거의 모든 환경에서 켜둬야 합니다. 꺼서 좋아지는 케이스가 거의 없습니다. 만약 꺼야 한다면 그건 옵티마이저 버그를 의심해야 할 상황입니다.

관련 개념

• Ch09-1 옵티마이저의 역할 — 비용 모델이 알고리즘 선택의 기반
• Ch09-2 기본 데이터 처리 — 임시 테이블/Filesort를 줄이는 기능들
• Ch09-4 쿼리 힌트 — 옵티마이저 스위치를 쿼리 단위로 적용하는 힌트
• Ch08-3 B-Tree 인덱스 — ICP, 인덱스 머지의 기반

출처

• Real MySQL 8.0 (1권), 9.3 고급 최적화