[SQL] MySQL 옵티마이저

MySQL 옵티마이저

MySQL 옵티마이저(Optimizer)는 SQL 쿼리를 가장 효율적으로 실행하기 위한 ‘실행 계획(Execution Plan)’을 결정하는 핵심 엔진임.

옵티마이저는 단순한 문법 검사 단계를 넘어, 내부적으로 다양한 전략(인덱스 선정, 조인 순서 결정, 쿼리 변환, 비용 추정 등)을 사용하여 쿼리 성능을 최적화함.

 

MySQL 옵티마이저의 전반적 흐름

1. 파싱(Parsing)과 전처리

클라이언트에서 쿼리가 들어오면, 먼저 SQL 파서가 문법적 검사를 수행함.

내부 구조(Parse Tree)를 만든 후, 세부적인 전처리 과정을 거쳐 옵티마이저에 전달됨.

 

2. 옵티마이저 단계(Optimization)

옵티마이저는 쿼리 실행에 대한 잠재적 실행 계획들을 생성하고 각각의 비용(Cost)을 추정하여, 가장 낮은 비용을 갖는 실행 계획을 선택함.

MySQL 8.0 이후 버전은 히스토그램(데이터 분포 정보)을 포함해 좀 더 정확한 통계 기반 최적화를 수행할 수 있음.

 

3. 실행(Execution)

최종 확정된 실행 계획을 스토리지 엔진(InnoDB 등)이 실제로 수행함.

EXPLAIN 명령어 등을 통해 사용자는 옵티마이저가 결정한 실행 계획을 확인하고, 성능 개선을 위한 인덱스, 쿼리 구조 변경 등을 검토할 수 있음.

 

쿼리 변환과 재작성

MySQL 옵티마이저는 효율적 처리를 위해 쿼리를 내부적으로 변환(Rewriting) 할 수 있음.

 

1. 서브쿼리(subquery) 최적화

예를 들어, WHERE 절에 포함된 서브쿼리를 세미 조인(Semi-Join) 또는 조인(Join) 형태로 변환해 중첩 루프 비용을 줄이거나, IN 서브쿼리를 내부적으로 EXISTS 형태로 변환 하는 등.

Materialization: FROM 절의 서브쿼리를 임시 테이블로 만들어두고 필요 시 빠르게 참조.

 

2. 조건 재배열

AND, OR 조건식을 내부적으로 분해·재결합해 인덱스 범위 검색(Index Range Scan)이 가능하도록 최적화.

 

3. 조인 순서 자동 재배열

테이블이 여러 개인 조인 쿼리에서, 옵티마이저가 테이블 간 연결(Join Condition)을 분석해 비용이 낮은 순서로 조인하도록 변경.

 

비용 기반 접근과 통계 정보

1. 비용(cost) 산정

MySQL 옵티마이저는 각 연산(테이블 접근, 인덱스 스캔, 조인 방식 등)에 대한 비용(추정 I/O 횟수, CPU 사용량, 네트워크 전송 비용 등)을 추정하고, 모든 연산을 합산해 총 비용을 계산함.

가장 비용이 낮다고 추정되는 실행 계획을 선택함.

 

2. 통계 정보(Statistics)

테이블/인덱스에 대한 통계 정보(행(row) 개수, 카디널리티, 컬럼 분포, 히스토그램 등)를 바탕으로 “조건절에서 몇 개 로우가 필터링 될 것인지”를 추정함.

InnoDB 스토리지 엔진은 InnoDB 스탯(InnoDB Persistent Statistics)을 유지해, 옵티마이저가 참조할 수 있음.

MySQL 8.0부터는 히스토그램을 수동으로 생성해 특정 컬럼의 데이터 분포를 정확히 반영하도록 할 수 있음.

 

3. 히스토그램(Histogram)

히스토그램을 통해 특정 컬럼 값이 어떻게 분포되는지 더욱 정밀하게 파악함.

예를 들어, 특정 컬럼이 극단적으로 skewed(치우침)된 경우, 전통적 통계만으로는 올바른 비용 계산이 어려울 수 있지만, 히스토그램을 활용하면 옵티마이저가 정확한 카디널리티를 추정할 수 있음.

 

MySQL에서 주로 사용하는 접근 방식 : 테이블 접근 방식

1. ALL (풀 스캔)

인덱스를 사용하지 않고 테이블 전체를 순회.

적은 데이터량이거나, 인덱스를 사용해도 큰 이점이 없다고 판단되면 옵티마이저가 채택할 수 있음.

 

2. Index (인덱스 풀 스캔)

테이블의 모든 데이터를 인덱스 순서대로 읽는 방식(커버링 인덱스가 아니라면, 인덱스로부터 다시 테이블을 조회).

정렬 없이 인덱스 순서대로 결과가 필요한 경우 사용될 수 있음.

 

3. Range (인덱스 범위 스캔)

WHERE 조건을 분석해 특정 범위에 해당하는 로우만 인덱스로부터 조회.

사용자가 가장 흔히 기대하는 “인덱스 효율”을 볼 수 있는 접근 방식.

 

4. ref/eq_ref

ref: 보통 조인 조건에서 동등 비교(=)를 이용할 때, 인덱스의 선두 컬럼 일부를 사용해 여러 로우를 매칭.

eq_ref: 기본 키 또는 유니크 인덱스의 완전 매칭을 통해 단일 로우만 찾는 조인 방식. 가장 비용이 적음.

 

MySQL에서 주로 사용하는 접근 방식 : 조인 방식

1. Nested Loop Join

MySQL은 기본적으로 Nested Loop Join을 사용함.

한 테이블을 기준으로(드라이빙 테이블), 다른 테이블을 반복(루프)하여 매칭하는 방식.

옵티마이저가 드라이빙 테이블(조인 순서)을 결정하는 것이 핵심 성능 요소.

 

2. Block Nested Loop, Batched Key Access

MySQL 5.6+ 버전에서 Batched Key Access(BKA) 또는 Multi-Range Read (MRR) 같은 최적화 기법을 사용해, 랜덤 I/O를 줄이고 시퀀셜 액세스를 늘릴 수 있음.

 

3. Hash Join / Merge Join

MySQL에는 일부 조건에서 Block Nested Loop + temp table를 통해 사실상의 해시 조인과 비슷한 방식으로 동작할 수 있습니다만, 전통적인 Hash Join, Sort-Merge Join은 (2025년 현재 시점) MySQL에서 완전하게 구현되지 않았음.

MariaDB나 다른 DBMS와의 차이점임.

 

쿼리 최적화 기법 및 사례

1. 인덱스 조건 푸시다운(Index Condition Pushdown)

MySQL 5.6 이상에서 도입된 기능으로, 스토리지 엔진 차원에서 인덱스 조건을 더 많이 걸러서 서버 레이어로 넘기는 로우 수를 줄임.

예를 들어, WHERE col1 = ? AND col2 < ? 같은 다중 컬럼 조건을 인덱스 단계에서 최대한 필터링하여 성능을 높임.

 

2. 조인 순서 재배열

여러 테이블이 조인될 때, MySQL은 테이블들의 통계 정보와 조인 조건을 분석해 최적의 순서를 찾으려 함.

쿼리에 명시된 순서가 곧 실행 순서가 되지 않음.

단, STRAIGHT_JOIN 힌트를 주면 옵티마이저가 순서 변경 없이 그대로 실행함.

 

3. 쿼리 힌트(Query Hint)

옵티마이저가 비효율적인 계획을 선택하는 경우, 힌트(HINT)를 통해 옵티마이저가 특정 인덱스를 사용하도록 강제하거나, 조인 순서를 고정할 수 있음.

예를 들어, FORCE INDEX, USE INDEX, IGNORE INDEX, STRAIGHT_JOIN, JOIN_CACHE_HINT(BKA, NO_BKA) 등을 사용함.

 

4. 서브쿼리와 EXISTS, IN 변환

MySQL 이전 버전(5.5 이하)은 서브쿼리 최적화가 미흡하여 대규모 데이터일 때 성능이 저하되는 경우가 많았음.

최근 버전에서는 서브쿼리를 효율적으로 Materialize하거나 쿼리 재작성해 성능을 개선함.

필요시, 개발자가 명시적으로 서브쿼리를 JOIN으로 변환해줄 수도 있음.

 

실행 계획 확인 : EXPLAIN

MySQL에서 EXPLAIN 키워드를 사용하면 옵티마이저가 결정한 “실행 계획”을 확인할 수 있음.

주요 컬럼(예: select_type, table, type, possible_keys, key, key_len, rows, filtered, Extra)을 통해 어느 인덱스가 사용되는지, 조인 순서는 어떤지 등을 알 수 있음.

EXPLAIN ANALYZE(MySQL 8.0+)는 실제 실행 시간, 실제 읽은 로우 수 등 런타임 정보까지 보여주어, 옵티마이저의 추정치와 실제 결과를 비교 가능하게 함.

 

옵티마이저 통계 갱신(ANALYZE TABLE) 및 유지보수

1. 통계 갱신

인덱스 통계가 오래되면 옵티마이저가 잘못된 비용을 추정할 수 있으므로, 정기적으로 ANALYZE TABLE을 수행해 통계를 갱신하는 것이 좋음.

InnoDB는 자동 통계 업데이트를 수행하지만, 때때로 수동으로 명시적 ANALYZE TABLE을 해주는 것이 더 정확할 수 있음.

 

2. 히스토그램 관리

MySQL 8.0에서는 CREATE HISTOGRAM ON table(column) 명령으로 특정 컬럼에 히스토그램을 생성 및 갱신할 수 있음.

컬럼 분포가 비정상적으로 치우쳐 있거나, 급격히 변하는 경우 이를 통해 옵티마이저 정확도를 높일 수 있음.

 

3. 테이블 파티셔닝(Partitioning)

파티션별 통계 정보를 보다 정확하게 유지할 수 있어, 대용량 테이블에서 옵티마이저가 더 나은 판단을 내리게끔 도움.

 

한계와 개선 사항

1. 복잡한 쿼리 구조

MySQL 옵티마이저는 Oracle 등 다른 대형 RDBMS에 비해 조인 전략(특히 Hash Join, Merge Join 등) 구현이 상대적으로 제한적이어서, 많은 테이블이 엮인 복잡한 쿼리에서 성능이 떨어질 수 있음.

 

2. MVCC 충돌

InnoDB의 MVCC와 관계된 락 경합이 발생하면, 옵티마이저가 예측한 비용과 실제 실행 시간이 크게 어긋날 수 있음(동시성 환경).

 

3. 실시간 코스트 모델 보정 한계

MySQL은 실행 도중(cost 구간) 재계산이나 동적 플랜 최적화를 크게 수행하지 않음(Adaptive Optimization이 제한적).

일부 DBMS처럼 런타임에 플랜을 재조정하는 기능은 제한적이므로, 정적 계획을 만드는 단계에서 통계가 중요함.

 

4. 쿼리 캐시와 혼동

과거 MySQL “쿼리 캐시” 기능은 옵티마이저와 별개로, “SQL 텍스트 동일 → 캐시된 결과 반환” 방식을 말함(MySQL 8.0에선 제거).

옵티마이저가 만들어낸 실행 계획 자체를 캐싱하는 프리페어드 스테이트먼트 등과는 다른 개념이므로 혼동하면 안 됨.

 

결론

MySQL 옵티마이저는 쿼리 파싱 이후 다양한 후보 실행 계획을 평가하고, 비용 기반으로 최적의 계획을 결정하는 핵심 모듈임.

최신 MySQL(특히 8.0)은 히스토그램, MRR, 서브쿼리 최적화 개선 등으로 이전 버전보다 훨씬 정교한 최적화를 수행함.

그러나 대규모 조인, 고도화된 조인 알고리즘(해시 조인, 병렬 쿼리 등)은 아직 제한적이므로, 테이블·인덱스 설계, 통계 관리, 적절한 쿼리 구조가 실무에서도 매우 중요함.

EXPLAIN, EXPLAIN ANALYZE, ANALYZE TABLE, Query Hint 등을 적극 활용하여 옵티마이저가 잘못된 비용 추정을 하지 않도록 조정하는 것이 MySQL 성능 튜닝의 핵심 역량임.

 

함께 고려해볼 추가 사항

1. 인덱스 설계

멀티컬럼 인덱스에서 컬럼 순서, 선택도(Selectivity)에 따라 옵티마이저 효율이 달라짐.

 

2. 데이터 분포

skewed 데이터(편향된 데이터)가 있으면 히스토그램 또는 수동 쿼리 최적화 전략 필요.

 

3. 조인 전략

필요한 경우 서브쿼리를 명시적으로 JOIN으로 바꿔주거나, 쿼리 구조를 단순화.

 

4. 하드웨어 리소스

충분한 메모리(InnoDB Buffer Pool), 스토리지 IOPS, CPU 성능 등도 옵티마이저 효과를 극대화하는 기반이 됨.

 

결론적으로, MySQL 옵티마이저는 코스트 기반 의사결정을 통해 최적화된 실행 경로를 찾지만, 통계 품질·데이터 특성·쿼리 구조 등의 영향을 많이 받음.

실무에서는 옵티마이저의 결정을 이해하고, 필요 시 튜닝 및 힌트 적용을 통해 쿼리 성능을 극대화해야 함.