Optimizer Hints 상세 매뉴얼 (USE INDEX, FORCE INDEX, STRAIGHT_JOIN 등)

MySQL Optimizer Hints 완벽 가이드

Optimizer Hints는
MySQL 옵티마이저에게 힌트를 주어 최적화 결정을 유도하거나 강제하는 기능입니다.
쿼리 안에 명시적으로 힌트를 주어 원하는 실행 계획을 이끌어낼 수 있습니다.

---

1. Optimizer Hint 기본 문법

SELECT /*+ HINT */ ...

• /*+ HINT */ 형태로 SQL 주석 안에 작성
• 일반 주석(/* */)과는 다름. 반드시 + 기호가 있어야 함!

예시

SELECT /*+ NO_INDEX(emp) */ * FROM employees emp WHERE emp.salary > 10000;

 

→ employees 테이블에 인덱스를 사용하지 말라는 힌트

---

2. 대표 Optimizer Hints 종류

2.1 USE INDEX

특정 인덱스를 사용하도록 유도

SELECT * FROM employees USE INDEX (idx_salary) WHERE salary > 10000;

• 의미: idx_salary 인덱스를 사용하는 것을 "선호(prefer)"하도록 힌트를 줌
• 주의: 강제는 아님. 옵티마이저가 최종 판단

---

2.2 FORCE INDEX

특정 인덱스를 강제로 사용

SELECT * FROM employees FORCE INDEX (idx_salary) WHERE salary > 10000;

• 의미: 옵티마이저가 다른 경로가 더 좋더라도 무조건 해당 인덱스를 사용
• 사용 예시: 옵티마이저가 Full Scan을 선호하는 경우 억지로 인덱스 스캔하게 하고 싶을 때

---

2.3 IGNORE INDEX

특정 인덱스를 무시

SELECT * FROM employees IGNORE INDEX (idx_salary) WHERE salary > 10000;

• 의미: 지정한 인덱스는 절대 사용하지 않음
• 사용 예시: 잘못 설계된 인덱스를 사용하지 못하도록 차단할 때

---

2.4 STRAIGHT_JOIN

조인 순서 강제

SELECT STRAIGHT_JOIN * 
FROM departments d
JOIN employees e ON d.department_id = e.department_id;

• 의미: FROM 절에 명시된 순서대로 조인 강제
• 사용 예시: 옵티마이저가 비효율적인 조인 순서를 잡을 때 직접 순서를 지정

---

2.5 JOIN_ORDER Hint (8.0 이상)

더 정밀하게 조인 순서를 지정하는 힌트

SELECT /*+ JOIN_ORDER(d e) */ *
FROM departments d
JOIN employees e ON d.department_id = e.department_id;

• 의미: STRAIGHT_JOIN 대신 최신 방식으로 조인 순서를 고정하는 방법
• 장점: 부분 조합(partial order)도 가능

---

2.6 Other Useful Hints

힌트명	설명
INDEX_MERGE	여러 인덱스를 병합해서 사용하도록 요청
NO_INDEX_MERGE	인덱스 병합 최적화를 막음
QB_NAME	쿼리 블록에 이름을 부여해 서브쿼리에도 힌트를 지정할 수 있음
MATERIALIZATION	서브쿼리를 강제 Materialize (임시 테이블화)
NO_MATERIALIZATION	서브쿼리 Materialization을 막음
BKA / NO_BKA	Batched Key Access Join 최적화 활성/비활성
HASH_JOIN / NO_HASH_JOIN	Hash Join 사용 여부 제어 (8.0.18 이후)

---

3. 힌트 우선순위 (순서 중요!)

• FORCE INDEX > USE INDEX > IGNORE INDEX
• STRAIGHT_JOIN은 전체 실행 플랜에 큰 영향을 줌
• 힌트를 잘못 사용하면 오히려 성능 저하 가능성 있음

항상:
EXPLAIN → PLAN 확인 → 실제 성능 비교 (ANALYZE FORMAT=JSON 추천)

---

실전 Tips

• USE INDEX는 "권장", FORCE INDEX는 "진짜 필요한 경우만" 써야 함
• STRAIGHT_JOIN은 조인 순서 실험할 때 유용
• 힌트 남발 금지 → 계획이 하드코딩되면 MySQL 버전/데이터 변화에 따라 성능 하락
• 테스트할 때는 항상 QPS, TPS, Rows Examined 같은 실제 메트릭을 체크

---

요약

힌트설명	실전 사용	타이밍
USE INDEX	특정 인덱스 사용 선호	옵티마이저가 다른 인덱스 추천할 때
FORCE INDEX	특정 인덱스 강제 사용	Full Scan 방지 필요할 때
IGNORE INDEX	특정 인덱스 무시	성능 저하 유발 인덱스 회피
STRAIGHT_JOIN	조인 순서 강제	복잡한 조인 최적화 실패시
JOIN_ORDER	조인 순서 명시적 제어	고급 튜닝 시나리오에서