(MySQL) 실행계획(Explain)

쿼리 성능 문제를 분석할 때 가장 먼저 확인해야 하는 것이 실행계획이다. MySQL에서는 EXPLAIN을 통해 옵티마이저가 선택한 테이블 접근 방식을 확인할 수 있다.

EXPLAIN은 실제 데이터를 조회하는 명령이 아니라 쿼리가 어떤 방식으로 실행될 예정인지를 보여주는 분석 도구이다. 따라서 성능 문제를 해결하려면 쿼리를 수정하기 전에 실행계획을 읽고 분석할 수 있어야 한다.

 

---

MySQL이 쿼리를 처리하는 전체 흐름

 

MySQL은 쿼리를 입력 받으면 바로 실행하는 것이 아니라 내부적으로 여러 단계를 거친다. EXPLAIN은 이 과정 중 옵티마이저 단계에서 생성된 실행 전략을 출력한 결과이다.

 

쿼리 처리 흐름은 다음과 같다.

1. Parser(쿼리 파서)
   - SQL 문법 분석
   - Parse Tree 생성 
   - 문법 오류 검사
   
   
2. Preprocessor (전처리기)
   - 테이블 / 컬럼 존재 여부 확인
   - 권한 체크
   - 내부 쿼리 구조 정리
   
   
3. Optimizer (옵티마이저)
   - 인덱스 선택
   - 조인 순서 결정
   - 접근 방식 결정 (ALL / range / ref 등)
   - 정렬 및 임시 테이블 여부 판단
   - 비용 기반으로 최적 실행 경로 선택
   
   
4. Executor (쿼리 실행기)
   - 옵티마이저가 선택한 계획대로 실제 실행
   - Storage Engine(ex. InnoDB)에 접근

즉 EXPLAIN은 실행 결과가 아니라 옵티마이저가 결정한 “접근 전략”을 보여주는 것이다.

 

---

EXPLAIN 주요 컬럼

 

특정 쿼리에 대한 EXPLAIN을 실행하면 위와 같은 결과를 볼 수 있는데 여기에서 중요한 컬럼은 다음과 같다.

 

1. type (접근방식)

type은 MySQL이 row를 찾기 위해 테이블/인덱스를 어떤 방식으로 탐색했는지를 의미한다.

type	접근 방식 설명	인덱스 전제	예시 쿼리	성능
ALL	테이블 전체 row 스캔	orders(status) 인덱스가 없다고 가정	SELECT * FROM orders WHERE status='COMPLETE';	매우 나쁨
index	인덱스 전체 스캔	idx_orders_created_at(created_at) 존재	SELECT id FROM orders ORDER BY created_at;	나쁨
range	인덱스 범위 스캔	idx_orders_created_at(created_at) 존재	SELECT * FROM orders WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31';	보통
ref	인덱스 값 기반 조회(동일 값 다건 가능)	idx_orders_status(status) 존재 (Non-Unique)	SELECT * FROM orders WHERE status='COMPLETE';	좋음
eq_ref	조인 시 PK/Unique로 단건 매칭	users.id 가 PK	SELECT * FROM orders o JOIN users u ON o.user_id = u.id;	매우 좋음
const	PK/Unique 조건으로 단건 확정	users.id 가 PK	SELECT * FROM users WHERE id=1;	최고

 

2. possible_keys / key

• possible_keys: 사용 가능한 인덱스 후보
• key: 실제 사용된 인덱스

즉, key가 null이면 인덱스를 사용하지 않은 것이다.

 

3. rows

예상 스캔 row 수

숫자가 클수록 비효율적인 실행 가능성이 높다.

 

4. filtered (필터링 비율)

filtered 값은 WHERE 조건 적용해서 읽은 row 중 몇 %가 조건을 만족할지 예측한 값이다. (% 단위)

 

ex)
- rows = 100,000
- filtered = 10

 

위 상황에서는 약 10%만 최종 조건을 통과한다고 예측한 것이다.

실제 처리량은 아래와 같이 이해하면 된다.

 

rows × filtered / 100

 

따라서 filtered가 낮아도 rows가 크면 쿼리 속도가 느리기 때문에 EXPLAIN에서는 rows가 더 중요한 지표로 사용된다.

 

5. Extra (추가 작업 여부)

Extra 컬럼은 옵티마이저가 데이터를 처리하는 과정에서 추가적인 작업이 발생하는지를 보여주는 항목이다.

여기에 표시되는 내용은 성능 저하의 원인이 되는 경우가 많다.

 

5.1) Using where (추가 필터링 발생)

인덱스로는 row를 찾았지만 최종 조건 검사를 MySQL 서버가 다시 수행하는 경우이다.

즉, 인덱스로 완전히 필터링하지 못한 상태.

CREATE INDEX idx_status ON orders(status);
SELECT * FROM orders WHERE status = 'COMPLETE' AND total_amount > 10000;

인덱스가 이렇게 status에만 존재한다면 MySQL은 먼저 status='COMPLETE' 조건으로 인덱스를 타고 후보 row들을 찾는다.
하지만 total_amount는 인덱스에 없기 때문에, 후보 row들을 테이블에서 읽어온 뒤 MySQL 서버가 total_amount > 10000 조건을 다시 검사해야 한다.

 

5.2) Using index (커버링 인덱스)

커버링 인덱스란 쿼리에 필요한 모든 컬럼이 인덱스에 포함된 상태를 의미한다.

이 경우 MySQL은 실제 테이블을 읽지 않고 인덱스만으로 결과를 반환한다.

 

디스크 접근이 줄어들어 가장 빠른 상태이다.

 

5.3) Using filesort (정렬을 인덱스로 처리하지 못한 상태)

이건 매우 중요하다.

MySQL에서 ORDER BY는 두 가지 방식으로 처리된다.

1. 인덱스 순서를 그대로 이용
2. 데이터를 전부 모은 뒤 별도 정렬(filesort)

filesort가 뜬다는 건 2번이라는 의미다.

 

왜 인덱스가 없으면 정렬이 느려지나?

인덱스는 이미 정렬된 구조(B+Tree)이기 때문이다. 즉, 인덱스를 타면 정렬된 상태로 데이터를 바로 읽을 수 있다.

하지만 인덱스가 없으면:

• 모든 데이터를 먼저 읽는다
• 정렬을 위해 메모리(Sort Buffer)에 올린다
• 메모리에 다 못 담으면 임시 파일로 만들어 디스크 정렬을 수행한다
• 정렬 완료 후 결과를 반환한다

이 과정 전체를 filesort라고 한다.

 

데이터가 많아질수록

• 메모리 → 디스크 정렬로 전환
• I/O 증가
• 정렬 비용 급증

그래서 ORDER BY 컬럼에도 인덱스가 반드시 필요하다.

 

5.4) Using temporary (임시 테이블 생성)

GROUP BY나 DISTINCT는 데이터를 묶어서 정리하는 작업이 필요하다.

이 작업을 인덱스로 바로 처리하지 못하면 MySQL은 임시 테이블을 생성하여 중간 결과를 저장한 뒤 처리한다.

SELECT status, COUNT(*) FROM orders GROUP BY status;

orders 테이블에 인덱스가 없을 경우 MySQL의 내부 동작은 다음과 같다.

1. 데이터를 읽는다
2. status 기준으로 그루핑한다
3. 임시 테이블에 그룹 결과를 저장한다
4. 임시 테이블에서 최종 결과를 반환한다

이때 Extra에 Using temporary가 표시된다.

임시 테이블은 처음에는 메모리에 생성되지만, 그룹 결과가 커지면 디스크 기반 임시 테이블로 전환되면서 읽기/쓰기 I/O가 반복되어 성능이 급격히 저하된다.

따라서 GROUP BY 대상 컬럼에 인덱스를 생성하면 임시 테이블 없이 인덱스 순서만으로 그룹핑이 가능해진다.

 

---

실전 예제: 인덱스 전/후 실행계획 비교

 

[ 인덱스 생성 전 ]

EXPLAIN SELECT * FROM orders WHERE status='COMPLETE';

 

 

결과 특징

• type = ALL
• key = NULL
• rows = 거의 전체 스캔
• extra = Using where

 

[ 인덱스 생성 후 ]

CREATE INDEX idx_orders_status ON orders(status);
EXPLAIN SELECT * FROM orders WHERE status='COMPLETE';

 

 

결과 특징

• type = ref
• key = idx_orders_status
• rows = 절반 수준으로 급감
• extra = NULL

 

status 컬럼에 인덱스가 없을 때는 조건에 맞는 row를 찾기 위해 테이블 전체를 스캔한다(ALL).

인덱스를 생성한 이후에는 status 값에 해당하는 인덱스 구간만 조회하게 되며 접근 방식이 ref로 변경된다.

이처럼 실행계획을 통해 인덱스 적용 여부와 성능 개선 효과를 사전에 확인할 수 있다.

---

출처 : https://www.youtube.com/watch?v=usEsrsaSSuU https://www.youtube.com/watch?v=gcsu7ni3tBc https://0soo.tistory.com/235 https://zzang9ha.tistory.com/436

---