CS 스터디 (DB) 22 - 옵티마이저란 무엇인가요?

옵티마이저(Optimizer)란? 

- 옵티마이저는 SQL을 가장 빠르고 효율적으로 실행하기 위한 실행계획을 자동으로 선택하는 DBMS 내부의 핵심 엔진입니다.

- 개발자가 SQL을 작성하면, DB는 이를 곧바로 실행하지 않고, 먼저 SQL을 분석하고 어떤 인덱스를 사용할지, 어떤 테이블부터 조인할지 등을 평가합니다.

 

옵티마이저 종류 

- 요즘 대부분의 DB(MySQL, Oracle, PostgreSQL 등)는 CBO를 사용합니다. 

종류	설명
RBO (Rule-Based Optimizer)	과거 방식, 고정된 규칙(예: 인덱스 무조건 사용)에 따라 실행 계획 결정
CBO (Cost-Based Optimizer)	현재 대부분의 DBMS 사용 방식. 통계 정보 기반으로 실행 비용(cost)을 계산하여 최적 경로 선택

 

 

힌트(Hint) 

- 다만 옵티마이저는 통계 정보가 부정확하거나 데이터 특성을 잘못 해석할 경우, 비효율적인 실행 계획이 선택될 수 있습니다. 

- 이를 보완하기 위해 개발자가 직접 실행 계획을 유도하는 방법을 힌트(Hint)라고 부릅니다. 

- 힌트는 크게 인덱스 힌트와 옵티마이저 힌트가 있습니다. 

 

1) 인덱스 힌트 

- 인덱스 힌트는 USE, FORCE, IGNORE 같은 키워드를 사용하여 특정 인덱스를 권장하거나 배제할 수 있으며, SELECT, UPDATE, 멀티 테이블 DELETE 등의 문장에서 사용하여 실행 계획을 수동으로 보정할 수 있습니다. 

 

인덱스 힌트 종류와 예시 

인덱스 힌트	설명	예시
USE INDEX	해당 인덱스를 우선적으로 고려하라고 요청(권장)	FROM user USE INDEX (idx_age)
FORCE INDEX	해당 인덱스를 반드시 사용하도록 강제	FROM user FORCE INDEX (idx_age)
IGNORE INDEX	해당 인덱스를 절대 사용하지 않도록 요청	FROM user IGNORE INDEX (idx_age)

- USE INDEX는 옵티마이저가 판단할 수 있게 권유하는 수준이라, 상황에 따라 무시될 수 있습니다.

- FORCE INDEX는 옵티마이저가 무조건 따르기 때문에, 잘못 사용하면 오히려 성능이 악화될 수 있습니다. 

- 인덱스 힌트의 적용 순서: USE → FORCE → IGNORE

 

 

2) 옵티마이저 힌트 (MySQL 8.4 이후 버전부터 권장됨)

- 옵티마이저 힌트는 실행 계획에 개입해 조인 순서나 인덱스 사용을 직접 지정할 수 있습니다. 

- ex) 조인 순서가 잘못 잡혀서 풀스캔 → 조인 순서 힌트로 튜닝

SELECT /*+ JOIN_ORDER(u o p) */ * -- join 순서를 u -> o -> p 로 고정
FROM user u
JOIN orders o ON u.id = o.user_id
JOIN payment p ON o.id = p.order_id;

 

 

인덱스 힌트 사용 시점 지정 (스코프 명시)

- 인덱스 힌트는 단순히 사용할 인덱스를 지정할 뿐 아니라, 쿼리의 특정 단계(조인, 정렬, 그룹화)에서 언제 적용할지 명확히 지정할 수 있습니다.

스코프	적용 대상	설명
FOR JOIN	WHERE, JOIN 조건	조인 또는 필터링에 사용할 인덱스를 지정
FOR ORDER BY	ORDER BY절	정렬에 사용할 인덱스를 지정
FOR GROUP BY	GROUP BY절	그룹화 연산에 사용할 인덱스를 지정

 

예시1) FOR JOIN 사용 

- idx_age 인덱스를 데이터 필터링(JOIN 단계)에서 사용하도록 지정할 수 있습니다. 

SELECT * 
FROM user USE INDEX FOR JOIN (idx_age)
WHERE age > 30;

 

예시2) FOR ORDER BY 사용

- idx_created_at 인덱스를 정렬 단계에서만 사용하도록 지정할 수 있습니다. 

SELECT * 
FROM orders USE INDEX FOR ORDER BY (idx_created_at)
ORDER BY created_at DESC;

 

 

MySQL 8.4 이상에서는 인덱스 힌트가 옵티마이저 힌트로 대체 예정

- 기존에 많이 사용하던 인덱스 힌트 방식(USE INDEX 등)은 여전히 동작하지만, MySQL 8.4부터는 옵티마이저 힌트(Optimizer Hint) 방식으로 대체되는 방향으로 바뀌고 있습니다. 

- 즉 향후 USE INDEX 등은 deprecated 처리될 가능성도 있습니다. 

 

 

대체 형태 (Index-Level Optimizer Hint)

- 옵티마이저 힌트는 /*+ ... */ 주석 형식이며, 더 유연하고 명확하게 제어 가능합니다.

전통 힌트	대체 옵티마이저	힌트설명
USE INDEX (i1)	/*+ INDEX(t i1) */	특정 인덱스 사용 권장
FORCE INDEX (i1)	/*+ JOIN_INDEX(t i1) */	강제 사용
IGNORE INDEX (i1)	/*+ NO_INDEX(t i1) */	사용하지 않도록 설정

 

사용 예시

SELECT /*+ INDEX(user idx_age) */ * 
FROM user 
WHERE age > 30;

 

 

MySQL 쿼리 실행 구조의 일부 절차

- 이와 같은 최적화 과정은 MySQL 쿼리 실행 구조의 일부로 다음과 같은 순서를 따릅니다.

1. SQL 문장은 파서(Parser)에 의해 구문 분석되어 내부적으로 SQL 파스 트리(Parse Tree)를 생성합니다.
2. 이후 옵티마이저가 파스 트리를 기반으로 다양한 실행 계획을 시뮬레이션하고, 그중 비용(Cost)이 가장 낮은 계획을 선택합니다.
3. 마지막으로 MySQL 엔진과 스토리지 엔진이 협력하여 실제 데이터를 조회하고 결과를 반환합니다.

 

 

 

 

결론 

옵티마이저(Optimizer)는 SQL 쿼리를 가장 빠르고 효율적으로 실행하기 위한 실행 계획을 자동으로 생성해주는 DBMS의 핵심 엔진입니다. 개발자가 SQL을 작성하면, DB는 이를 그대로 실행하지 않고 먼저 어떤 인덱스를 사용할지, 어떤 테이블부터 조인할지 등 다양한 실행 경로를 시뮬레이션한 뒤, CBO 방식으로 비용(Cost)이 가장 낮은 경로를 선택합니다.

다만 옵티마이저가 항상 최적의 판단을 내리는 것은 아니기 때문에, 실행 계획을 직접 수동으로 보정할 수 있도록 힌트 기능이 도입되어 있습니다. 힌트는 인덱스 사용 여부를 명시하는 인덱스 힌트와, 적용 대상을 더 정밀하게 제어할 수 있는 옵티마이저 힌트로 나뉩니다.

특히 MySQL 8.4부터는 전통적인 인덱스 힌트 방식 대신 옵티마이저 힌트 사용이 권장되고 있으며, 이는 향후 deprecated될 수 있는 흐름입니다.