09. 옵티마이저와 힌트 (Optimizer and Hints)

옵티마이저는 사용자가 요청한 쿼리를 실행하는 데 최소의 비용이 소요되는 최적의 실행 방법을 결정하는 DBMS의 두뇌 역할을 담당한다.

9.1 옵티마이저 개요 및 원리

1. 쿼리 실행 절차

MySQL 서버에서 쿼리가 실행되는 과정은 크게 세 단계로 구분된다

1. SQL 파싱 (Parsing): 요청된 SQL 문장을 최소 단위(토큰)로 분리하여 파스 트리(Parse Tree)를 생성하고 문법 오류를 확인한다. MySQL은 SQL 문장 자체가 아닌 이 파스 트리를 이용하여 쿼리를 실행한다.
2. 최적화 및 실행 계획 수립: 옵티마이저가 파스 트리를 기반으로 다음과 같은 핵심 결정을 내리고 실행 계획을 수립한다
   • 불필요한 조건 제거 및 복잡한 연산 단순화
   • 여러 테이블 조인 시 어떤 순서로 테이블을 읽을지 결정 (조인 순서)
   • 사용된 조건과 인덱스 통계 정보를 이용해 사용할 인덱스 결정
   • 임시 테이블 사용 여부 결정
3. 실행: 수립된 실행 계획에 따라 MySQL 엔진과 스토리지 엔진이 협력하여 데이터를 읽고 가공한다

2. 옵티마이저의 종류

현재 MySQL을 포함한 대부분의 RDBMS는 비용 기반 최적화(Cost-based Optimizer, CBO) 방식을 채택한다.

• 비용 기반 최적화 (CBO): 쿼리 처리를 위한 여러 가능한 방법을 생성하고, 각 단위 작업의 비용 정보와 대상 테이블의 통계 정보(레코드 건수, 칼럼 분포도 등)를 활용하여 실행 계획별 비용을 산출한다. 이 중 최소 비용이 소요되는 처리 방식을 최종적으로 선택한다.
• 규칙 기반 최적화 (RBO): 통계 정보를 고려하지 않고 옵티마이저에 내장된 우선순위에 따라 실행 계획을 수립하는 방식이다. (현재 거의 사용되지 않음)

3. 통계 정보의 중요성

CBO에서 통계 정보는 가장 중요하며, 통계 정보가 부정확할 경우 옵티마이저는 잘못된 실행 계획(예: 인덱스 스캔 대신 느린 풀 테이블 스캔)을 선택하여 심각한 성능 저하를 초래할 수 있다.

• 히스토그램 (MySQL 8.0+): 기존의 인덱스 통계 정보의 부족함을 보완하기 위해, 인덱스되지 않은 칼럼에 대해서도 데이터 분포도를 수집하여 활용하는 히스토그램 정보가 도입되었다. 히스토그램은 조인 순서를 결정할 때 옵티마이저가 더 정확히 판단할 수 있게 돕는다.
• 코스트 모델 (Cost Model): MySQL 서버는 쿼리 비용 계산을 위해 디스크 읽기, 메모리 읽기, 인덱스 키 비교 등 다양한 단위 작업의 비용을 코스트 모델로 관리한다. MySQL 5.7부터 이 비용을 DBMS 관리자가 조정할 수 있게 개선되었다.

9.2 기본 데이터 처리 (Basic Data Processing)

1. 풀 테이블 스캔 및 인덱스 스캔

풀 테이블 스캔 (ALL)은 인덱스를 사용하지 않고 테이블 데이터를 처음부터 끝까지 읽는 방식이다. 옵티마이저는 다음 조건에서 FTS를 선택할 수 있다:

• 테이블 크기: 레코드 건수가 너무 적어 (일반적으로 페이지 1개로 구성된 경우) 인덱스를 통하는 것보다 FTS가 빠를 때
• 조건 부재: WHERE 절이나 ON 절에 인덱스를 활용할 수 있는 적절한 조건이 없을 때
• 손익 분기점: 인덱스를 사용해도 옵티마이저가 예측한 조건 일치 레코드 건수가 전체 레코드의 20~25%를 초과할 때
• 원리: 리드 어헤드 (Read Ahead): InnoDB는 FTS 실행 시 연속적인 페이지 읽기가 감지되면 백그라운드 스레드가 리드 어헤드를 시작하여 최대 64개 페이지를 버퍼 풀에 미리 적재함으로써 디스크 I/O 성능을 최적화한다.

2. 정렬 (ORDER BY 처리) 및 Filesort

정렬(ORDER BY)은 인덱스 이용 또는 Filesort를 통해 처리된다.

• Filesort 원리: 인덱스를 사용할 수 없을 때 발생한다. 정렬할 레코드가 메모리 할당 영역인 소트 버퍼(Sort Buffer) 크기보다 클 경우 디스크에 임시 저장되고 여러 조각을 병합하는 멀티 머지(Multi-merge) 작업이 반복되어 디스크 I/O가 발생한다.
  • 실무적 위험 (소트 버퍼): 소트 버퍼는 세션 메모리 영역이므로, 동시 커넥션이 많을 때 소트 버퍼 크기를 과도하게 설정하면 서버 메모리 부족을 유발하고, 운영체제의 OOM-Killer에 의해 MySQL 서버가 강제 종료될 위험이 있다.
• 정렬 알고리즘 (원리):
  • 싱글 패스 (Single-pass): 레코드의 모든 칼럼을 소트 버퍼에 담아 정렬한다. 레코드 크기가 작을 때 빠르다.
  • 투 패스 (Two-pass): 정렬 대상 칼럼과 프라이머리 키(PK)만 소트 버퍼에 담아 정렬하고, 나중에 PK로 테이블을 다시 읽어 나머지 칼럼을 가져온다. 레코드 크기가 크거나 BLOB, TEXT 타입이 포함될 때 사용된다.
• 스트리밍 vs. 버퍼링 (실무): 인덱스를 이용한 정렬만 스트리밍 방식으로 처리되어 첫 레코드 응답 속도가 빠르고 LIMIT 조건을 효율적으로 활용할 수 있다. Filesort를 포함한 나머지 정렬은 버퍼링 방식으로, 전체 정렬이 완료된 후에야 클라이언트로 결과를 전송하므로 응답 시간이 느리다.

3. 내부 임시 테이블 활용 (원리 및 실무)

MySQL 엔진은 데이터를 가공하기 위해 명시적이지 않은 내부 임시 테이블을 사용한다. 실행 계획의 Extra 칼럼에 "Using temporary"가 표시되면 사용되었음을 의미한다.

• 임시 테이블이 필요한 경우: ORDER BY와 GROUP BY 칼럼이 다를 때, UNION 또는 UNION DISTINCT (중복 제거) 쿼리 등에서 필요하다.
• MySQL 8.0 변화 (원리):
  • 메모리 임시 테이블은 TempTable 스토리지 엔진을 사용한다.
  • 디스크 임시 테이블은 InnoDB 스토리지 엔진을 사용한다. (이전 버전에서는 MEMORY/MyISAM 사용).
  • 임시 테이블이 BLOB이나 TEXT 칼럼을 가지거나 크기 제한(temptable_max_ram)을 초과하면 디스크로 전환된다..

9.3 고급 최적화 (Advanced Optimization)

1. 조인 최적화

MySQL은 기본적으로 네스티드 루프 조인(Nested Loop Join, NLJ)을 사용한다.

• BKA (Batched Key Access) 조인: NLJ의 단점을 보완하기 위해 도입되었다. 드라이빙 테이블의 레코드를 조인 버퍼(Join Buffer)에 모은 후, 드리븐 테이블 검색을 일괄 요청하여 스토리지 엔진의 디스크 접근을 최소화한다.
• 해시 조인 (Hash Join) (MySQL 8.0.18+): 조인 조건에 인덱스가 없는 경우 NLJ의 차선책으로 사용된다.
  • 원리: 작은 테이블(빌드 테이블)을 메모리에 해시 테이블로 구축하고, 큰 테이블(프로브 테이블)을 탐색하여 조인한다.
  • 실무 관점: NLJ는 첫 레코드 응답이 빨라 최고 응답 속도 (OLTP)에 유리하고, 해시 조인은 전체 처리 시간이 짧아 최고 스루풋 (분석/배치)에 적합하다. MySQL 서버는 OLTP 서비스에 중점을 두었으므로 해시 조인은 비효율적인 NLJ의 차선책으로 사용된다.

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

인덱스만으로는 검색 범위를 좁히기 어려운 조건(필터링 조건)이 인덱스에 포함된 경우, 해당 필터링 작업을 MySQL 엔진이 아닌 스토리지 엔진으로 넘겨 처리하는 최적화 기법다. 이를 통해 MySQL 엔진으로 전송되는 불필요한 레코드 수를 줄이고 디스크 I/O를 감소시킨다. 실행 계획 Extra 칼럼에 "Using index condition"이 표시된다.

3. 세미 조인 최적화 (IN / EXISTS Subqueries)

IN (subquery) 또는 EXISTS (subquery) 형태의 쿼리(세미 조인 쿼리)를 최적화하기 위한 전략들이 있다.

• Table Pull-out: 서브쿼리 테이블을 아우터 쿼리로 끌어내어 조인 형태로 재작성한다. 이 전략은 항상 가장 좋은 성능을 보장하기 때문에 별도의 옵티마이저 힌트로 제어되지 않는다.
• First Match: 서브쿼리에서 일치하는 첫 번째 레코드를 찾으면 더 이상 검색하지 않고 다음 레코드로 넘어가는 방식이다.
• Materialization (구체화): 서브쿼리 결과를 내부 임시 테이블로 만들어 아우터 쿼리와 조인하는 방식이다.
• Duplicate Weed-out: 조인 결과에서 중복 레코드를 제거하기 위해 내부 임시 테이블을 사용하는 방식이다. 실행 계획 Extra 칼럼에 "Start temporary"와 "End temporary"가 표시된다.

9.4 쿼리 힌트 (Query Hints)

쿼리 힌트는 옵티마이저가 통계 정보 부족이나 오판으로 인해 비효율적인 실행 계획을 선택했을 때, 개발자나 DBA가 실행 계획 수립에 개입하여 강제하는 방법이다. 힌트는 최후의 수단으로 사용해야 한다

1. 인덱스 힌트

예전 버전부터 사용되던 힌트로, 특정 인덱스를 사용하거나 사용하지 않도록 유도한다

• USE INDEX: 특정 인덱스 사용을 옵티마이저에게 제안한다
• FORCE INDEX: 특정 인덱스를 강제로 사용하도록 한다
• IGNORE INDEX: 특정 인덱스를 사용하지 못하도록 한다

2. 옵티마이저 힌트 (MySQL 5.6+)

MySQL 8.0부터는 쿼리 블록, 테이블 등 영향 범위에 따라 세밀하게 최적화 전략을 제어할 수 있는 다양한 힌트가 제공된다.

힌트	이름 역햘 영향	범위	실무적 고려
SET_VAR	쿼리 실행 기간 동안 시스템 변수(예: sort_buffer_size)의 값을 일시적으로 변경	글로벌	대량 정렬이 필요한 특정 쿼리의 소트 버퍼 크기를 임시 증설 시 유용
JOIN_ORDER	FROM 절에 명시된 테이블 순서대로 조인을 실행하도록 강제	쿼리 블록	조인 조건 인덱스가 없는 경우, 레코드 건수가 적은 테이블을 드라이빙 테이블로 지정할 때 유용
BNL / NO_BNL	블록 네스티드 루프 조인(BNL) 사용 여부를 제어. (MySQL 8.0.20부터는 해시 조인 사용 여부 제어)	쿼리 블록, 테이블	NLJ가 비효율적일 때 해시 조인을 강제하거나 비활성화할 때 사용
SEMIJOIN	세미 조인 최적화 전략(FIRSTMATCH, MATERIALIZATION 등)을 세부적으로 제어	쿼리 블록	옵티마이저가 서브쿼리 최적화를 잘못 선택했을 때 개입하여 성능을 개선
NO_ICP	인덱스 컨디션 푸시다운(ICP) 전략을 비활성화	테이블, 인덱스	ICP가 오히려 오버헤드를 유발할 때 사용 가능