MySQL optimizer_switch 튜닝은 쿼리 캐시 성능을 향상시키는 핵심 설정입니다.
이 글에서는 optimizer_switch의 주요 옵션, 실제 튜닝 사례, 쿼리 캐시(Query Cache)와의 연관성을 중심으로 Aurora MySQL과 MariaDB 환경에서 활용할 수 있는 실무 기준의 튜닝 가이드를 다룹니다.
⚙️ 1. optimizer_switch란 무엇인가?
MySQL optimizer_switch 튜닝 **MySQL 옵티마이저(Optimizer)**의 내부 동작 방식을 제어하는 시스템 변수입니다.
쿼리를 실행할 때 어떤 최적화 기능을 켜거나 끌지 세밀하게 지정할 수 있어,
복잡한 SQL 성능 문제를 해결할 때 매우 유용합니다.
설정 확인은 아래 명령어로 가능합니다:
SHOW VARIABLES LIKE 'optimizer_switch';
🧩 2. MySQL optimizer_switch 옵션 정리
| 옵션 | 설명 | 기본값 |
|---|---|---|
index_merge | 여러 인덱스를 동시에 사용 | ON |
derived_merge | 서브쿼리 병합 최적화 | ON |
index_condition_pushdown | 인덱스 조건을 스토리지 엔진 레벨로 전달 | ON |
mrr | Multi-Range Read 최적화 | ON |
batched_key_access | 조인 시 배치 키 접근 방식 | OFF |
💡 대부분의 환경에서는 기본 설정이 최적화되어 있지만,
특정 쿼리에서는 기능을 비활성화하면 오히려 속도가 빨라지기도 합니다.
🔍 3. MySQL optimizer_switch 튜닝 실무 적용 예시
예를 들어, 복잡한 JOIN이나 서브쿼리에서 옵티마이저가 잘못된 실행 계획을 선택할 때
특정 기능을 끄면 성능이 개선되는 경우가 있습니다.
SET optimizer_switch='index_merge=off,derived_merge=off';
EXPLAIN SELECT * FROM orders WHERE user_id=123 AND order_date>'2025-01-01';
이렇게 하면 옵티마이저가 불필요하게 여러 인덱스를 병합하거나 서브쿼리를 병합하지 않게 되어
쿼리 실행 경로가 단순화됩니다.
⚡ 4. MySQL optimizer_switch Aurora / MariaDB 환경에서의 차이점
- Aurora MySQL: optimizer_switch 설정은 파라미터 그룹(Parameter Group) 에서 관리해야 합니다.
콘솔 → Parameter groups →optimizer_switch항목 수정 후 재시작 없이 적용됩니다. - MariaDB: optimizer_switch의 옵션 구성이 MySQL보다 다양하며,
일부 옵션(join_cache_level,subquery_cache)은 MariaDB 전용으로 제공됩니다.
🧠 5. 쿼리 캐시(Query Cache)와의 연계 튜닝
쿼리 캐시는 실행된 SQL의 결과를 메모리에 저장해, 같은 요청이 다시 올 때 즉시 반환하는 기능입니다.
하지만 MySQL 8.0 이후에는 공식적으로 쿼리 캐시가 제거되었고,
Aurora나 MariaDB에서는 엔진 내부 캐시 구조(Adaptive Hash Index) 로 대체되었습니다.
✅ 따라서 MySQL optimizer_switch 튜닝과 함께 다음을 고려하세요:
- 반복 조회가 많은 쿼리는 프로그래밍 레벨 캐싱(Redis, ElastiCache) 로 대체
- JOIN 중심의 쿼리는 인덱스 푸시다운 + optimizer_switch 제어 조합으로 성능 확보
📊 6. 실행 계획(EXPLAIN)으로 튜닝 검증하기
optimizer_switch를 조정했다면 EXPLAIN 명령어로 실제 실행 계획을 반드시 확인해야 합니다.
EXPLAIN SELECT o.id, u.name FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'PAID';
type, possible_keys, key, rows, Extra 항목을 비교해
옵티마이저의 실행 계획이 바뀌었는지 검증합니다.
특히 Using index나 Using where 상태는 정상적인 최적화 신호입니다.
💡 7. 실무에서의 튜닝 팁 요약
- 불필요한 병합(index_merge)은 OFF로 조정
- JOIN이 많은 쿼리는 batched_key_access=ON 고려
- 서브쿼리 병합(derived_merge)은 데이터 양이 많을 때 비활성화
- 분석 전에는 항상 EXPLAIN으로 실행 계획 확인
- 변경 전후의 응답 시간을 CloudWatch 혹은 Performance Schema로 측정
🔗 8. 참고 문서
- 👉 참고:
- MySQL 공식 문서: https://dev.mysql.com/
- AWS 공식 문서: https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Reference.html
💬 다음 글 예고
👉 다음 글에서는 MySQL JOIN 최적화와 실행 계획 분석 실무 가이드 (2025) 를 다룹니다.
이전 글 MySQL Optimizer Hints 튜닝 가이드 (2025) 도 함께 참고하세요.
👉 이전 글 보기: MySQL Optimizer Hints 튜닝 가이드