Query Layer 최적화 과정 (Optimizer 내부 동작)

MySQL Query Layer 최적화 과정 (Optimizer 내부 동작)

MySQL 옵티마이저(Optimizer)는
SQL을 실행하기 위한 최적의 경로(Execution Plan)를 찾는 핵심 엔진입니다.
성능에 직접적인 영향을 주기 때문에 심층 이해가 매우 중요합니다.

---

1. Query 최적화 전체 프로세스

1. 파싱 (Parsing)
2. 쿼리 재작성 (Query Rewrite)
3. 최적화 (Optimization)
4. 실행 계획 생성 (Execution Plan)
5. 실행 (Execution)

 

→ 오늘 포커스는 2~4단계인
"쿼리 재작성" → "최적화" → "실행 계획 생성" 입니다.

---

2. 쿼리 재작성 (Query Rewrite)

목표: 쿼리를 더 최적화하기 좋은 형태로 변환

주요 동작

• 서브쿼리 → 조인 변환 (Subquery to Join Flattening)
  • 비상관 서브쿼리를 조인으로 바꿔 성능 향상
• 뷰(View) 인라인화
  • 뷰를 내부적으로 쿼리로 풀어버림
• 반복되는 상수 제거 (Constant Folding)
  • 계산 가능한 식을 미리 계산
• Materialization 결정
  • 서브쿼리 결과를 임시 테이블에 저장할지 결정

예시

SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

 

→ 재작성 후 조인 형태로 변환 가능

---

3. 쿼리 최적화 (Optimization)

목표: 가장 "비용(Cost)이 적은" 실행 계획을 선택

주요 최적화 기법

(1) Access Path Selection

• 테이블을 읽을 때 어떤 방식으로 접근할지 결정
  • Full Table Scan
  • Index Scan
  • Range Scan
  • Ref/Const 등

(2) Join Order Optimization

• 다중 테이블 조인 시, 테이블 조합 순서 결정
• MySQL은 "Greedy Algorithm" 기반
  • 비용이 가장 낮은 조인 순서를 선택

(3) Index Usage Decision

• 어떤 인덱스를 사용할지 평가
• 여러 인덱스 후보가 있을 경우 비용 비교

(4) Condition Pushdown

• WHERE 조건을 가능한 한 빨리 적용
• Storage Engine 수준에서 필터링 유도 (ICP, Index Condition Pushdown)

---

4. 실행 계획 생성 (Execution Plan)

목표: 선택된 최적화 결과를 실제 실행할 수 있는 구조로 컴파일

실행 계획 주요 요소

• 접근 방식 (ALL, index, range, ref, const 등)
• 사용하는 인덱스
• 조인 방법 (Nested Loop, Hash Join, Block Nested Loop Join)
• 예상되는 결과 건수(Rows)
• 비용(optimizer_estimate_cost)

확인 방법

• EXPLAIN 명령어로 최종 실행 계획 확인
• EXPLAIN FORMAT=JSON 을 사용하면 비용(Cost Estimate) 와 세부 정보까지 확인 가능

예시

EXPLAIN FORMAT=JSON
SELECT * FROM employees WHERE department_id = 10;

---

Optimizer 심화 기술

기술명	설명
Hypergraph Join Optimization	8.0부터 도입. 복잡한 다중 조인 최적화를 위해 도입된 알고리즘
Derived Merge	FROM절에 있는 서브쿼리를 메인 쿼리에 병합
Histograms	통계정보를 사용해 카디널리티(Cardinality) 추정 정확도 향상
Hints	강제적으로 Optimizer를 가이드 (USE INDEX, STRAIGHT_JOIN, OPTIMIZER_HINTS)
Materialization	서브쿼리 결과를 임시 테이블로 만들어 재사용

---

Query Layer 튜닝을 위한 실전 Tip

• EXPLAIN + 실제 실행 결과 비교 (Rows, Filtered 수치 확인)
• ANALYZE FORMAT=JSON 명령어 활용 (실제 통계 기반 분석)
• OPTIMIZER_TRACE 활성화하여 최적화 결정 과정을 추적
• Force Index / Join Order 힌트를 적절히 사용 (필요시)

---

Query Layer 최적화 흐름 요약

[SQL 입력]
  ↓
[파싱 → 쿼리 재작성 → 최적화(Access Path, Join Order, Index 선택) → 실행 계획 생성]
  ↓
[Storage Engine으로 전달하여 실제 데이터 작업]