MySQL 옵티마이저 실행계획 분석

MySQL 옵티마이저 실행계획은 쿼리를 효율적으로 실행하기 위해 MySQL이 사용하는 계획을 의미한다. 이 실행계획은 쿼리를 어떻게 처리할지에 대한 정보를 제공하며 이를 분석하면 쿼리 성능을 최적화할 수 있다.

MySQL에서 실행계획을 확인하려면 EXPLAIN 명령어를 사용한다. 이를 통해 MySQL이 쿼리를 실행하는 방식(예: 사용된 인덱스, 조인 방식, 풀스캔 여부 등)을 확인할 수 있다.

 

EXPLAIN 사용 방법

EXPLAIN [쿼리];

 

예제)

EXPLAIN SELECT * FROM employees WHERE department_id = 10;

 

EXPLAIN 출력 컬럼 설명

1. id
   • 쿼리 내 실행 단계 식별자
   • 값이 클수록 먼저 실행됨
2. select_type
   • 쿼리의 유형
   • 주요 값
     • SIMPLE : 단순 쿼리, 서브쿼리 없음
     • PRIMARY : 메인 쿼리
     • SUBQUERY : 서브쿼리
     • DERIVED : 서브쿼리의 결과가 임시 테이블로 저장됨
3. table
   • 접근하는 테이블 이름
4. partitions
   • 쿼리가 접근하는 파티션
5. type
   • 조인 유형 또는 테이블 액세스 방식
   • 성능순 : system > const > eq_ref > ref > range > index > ALL (풀스캔)
   • 주요 타입
     • const : 기본키 또는 유니크키를 조건으로 하나의 행만 조회
     • ref : 기본키/유니크키가 아닌 인덱스를 사용한 동등 조건 검색
     • range : 인덱스를 사용해 범위 조건을 조회 (예: <, >, BETWEEN, LIKE 'abc%')
     • all : 테이블을 처음부터 끝까지 탐색. 성능이 가장 낮으며 인덱스를 추가해 피해야 함.
6. possible_keys
   • 쿼리에 사용할 수 있는 인덱스 목록
7. key
   • 실제 사용된 인덱스
8. key_len
   • 사용된 인덱스의 길이(바이트 단위).
9. ref
   • 인덱스에서 비교된 컬럼 또는 상수 값.
10. rows
    • MySQL이 검색해야 한다고 예측하는 행 수
    • 값이 클수록 성능 저하
11. filtered
    • 조건을 만족하는 행의 비율(%)을 추정
12. Extra
    • 추가 정보
      • Using where : WHERE 조건을 사용
      • Using index : 인덱스만 사용하여 데이터를 검색
      • Using temporary : 쿼리 병합 시 임시 테이블 생성 및 사용
      • Using filesort : 정렬에 필요한 인덱스가 없어 별도의 정렬 작업 수행 → 성능 저하

 

실행계획 분석 예시

EXPLAIN SELECT * FROM employees WHERE department_id = 10;

 

결과

id	select_type	table	type	possible_keys	key	key_len	ref	rows	extra
1	SIMPLE	employee	ref	dept_id_index	dept_id_index	4	const	100	Using where

분석

• type : ref는 인덱스를 통해 데이터를 검색하므로 효율적
• key : dept_id_index가 사용됨
• rows : 100개의 행만 검색 예상
• Extra : Using where는 추가적인 필터링이 필요함

 

실행계획 최적화 팁

1. 인덱스 활용 : 쿼리에 사용된 조건이나 조인 컬럼에 적절한 인덱스를 생성
2. 조인 순서 변경 : 조인 순서를 바꾸어 실행계획의 type을 개선
3. 서브쿼리 최적화 : 서브쿼리를 조인으로 변환
4. LIMIT 사용 : 필요한 데이터만 가져와 불필요한 검색을 줄임
5. ANALYZE TABLE : 테이블 통계를 최신 상태로 유지

 

EXPLAIN 결과를 주기적으로 점검하며 성능을 개선할 수 있다. MySQL 옵티마이저의 동작을 이해하면 성능 튜닝에 큰 도움이 된다.