MySQL 실행계획(Explain) 정리

EXPLAIN이란?

EXPLAIN은 MySQL에서 SELECT, DELETE, INSERT, REPLACE, UPDATE 문 앞에 붙여 해당 쿼리의 실제 실행 계획을 미리 보여주는 도구다. 쿼리를 실행하지 않고도 옵티마이저가 어떤 인덱스를 선택하고 어떤 방식으로 조인을 수행하는지 확인할 수 있다. 이는 성능 병목을 파악하고 인덱스 튜닝이나 쿼리 구조 개선을 할 때 핵심적인 도구다.

기본 문법

EXPLAIN SELECT * FROM user WHERE email = 'abc@example.com';

MySQL 8.0 이상에서는 EXPLAIN ANALYZE 또는 XPLAIN FORMAT=JSON 등을 통해 더욱 상세한 분석도 가능하다.

EXPLAIN 출력 컬럼

아래는 EXPLAIN 실행 시 나오는 주요 컬럼과 의미이다.

컬럼	JSON 이름	설명
id	select_id	SELECT 블록 식별자 (숫자가 클수록 나중에 실행)
select_type	없음	SELECT 유형 (SIMPLE, PRIMARY, SUBQUERY 등)
table	table_name	접근 대상 테이블 또는 파생 테이블 명칭
partitions	partitions	사용된 파티션 (파티셔닝 안 쓰면 NULL)
type	access_type	조인 유형 또는 접근 방식 (성능 핵심 지표)
possible_keys	possible_keys	사용 가능성이 있는 인덱스 목록
key	key	실제 사용된 인덱스 이름
key_len	key_length	사용된 인덱스 키 길이 (바이트 단위)
ref	ref	어떤 컬럼이나 상수와 비교했는지
rows	rows	예측된 스캔 대상 행 수
filtered	filtered	조건에 부합하는 행 비율 (0~100%)
Extra	없음	추가 실행 정보 (Using where, Using index 등)

주요 컬럼 설명

id

id는 실행되는 각 SELECT 구문을 구분하기 위한 고유 식별자이다. 쿼리 내에서 실행 순서를 나타내며, 숫자가 작을수록 먼저 실행된다. 조인이 없는 단일 쿼리는 id = 1이 되며 서브쿼리나 UNION과 같이 중첩된 쿼리가 있을 경우 순서대로 번호가 부여된다. 같은 id를 가진 항목은 병렬적으로 처리되는 조인 대상 테이블로 조인이 하나의 실행 단위라는 의미다. <derivedN>, <subqueryN>처럼 id가 다른 결과를 참조하는 경우 해당 id가 먼저 실행되어 결과를 생성하고, 상위 id가 이를 사용하는 구조다.

select_type

SELECT 문이 어떤 구조를 갖는지를 나타내는 항목이다. 단순 SELECT인지, 서브쿼리인지, UNION인지 등을 식별할 수 있다.

• SIMPLE: 서브쿼리나 UNION이 없는 단순 SELECT
• PRIMARY: 서브쿼리 바깥의 최상위 SELECT (메인 쿼리)
• SUBQUERY: WHERE 절 등에 존재하는 독립적인 서브쿼리
• DEPENDENT SUBQUERY: 외부 컬럼에 의존하는 서브쿼리 (매 행마다 재실행될 수 있음)
• DERIVED: FROM절 안의 서브쿼리 (파생 테이블)
• DEPENDENT DERIVED: 외부 쿼리에 의존하는 파생 테이블
• UNION: UNION의 두 번째 SELECT 이후의 SELECT
• UNION RESULT: UNION 결과를 임시 테이블로 결합한 결과
• UNCACHEABLE SUBQUERY: 캐시되지 않고 매번 실행되는 서브쿼리 (RAND(), UUID() 등 사용)
• MATERIALIZED: IN 절 등에서 파생된 서브쿼리가 임시 테이블로 만들어졌음을 의미

type (조인 유형 = 접근 방식)

이 컬럼은 옵티마이저가 선택한 데이터 접근 방법을 의미하며, 성능 분석 시 가장 중요한 지표다. 위쪽일수록 효율이 높다.

• system: 단 하나의 레코드만 있는 테이블 (가장 빠름)
• const: 기본키 또는 유니크 인덱스를 통해 한 건만 조회
• eq_ref: 드리븐 테이블에서 PK 또는 UK와 조인되어 1건 매칭 (조인에서 가장 빠른 방식)
• ref: 조인 또는 WHERE 절에서 인덱스를 사용하지만 다수의 레코드가 일치할 수 있음
• ref_or_null: ref와 유사하지만 NULL 포함 (IS NULL 조건 포함된 경우)
• index_merge: 두 개 이상의 인덱스를 병합하여 동시에 사용
• range: 범위 검색 (BETWEEN, IN, >, <, <=, >= 등)
• index: 인덱스를 처음부터 끝까지 스캔 (커버링 인덱스일 수 있음)
• ALL: 테이블 풀스캔 (가장 느림)

key, possible_keys, key_len

• possible_keys: 옵티마이저가 고려할 수 있는 인덱스 목록이다. WHERE절과 조인 조건을 기반으로 추정된 인덱스 후보군이다.
• key: 실제로 선택된 인덱스이다. NULL이면 인덱스가 사용되지 않았다는 의미한다.
• key_len: 사용된 인덱스 키의 길이(바이트 수)이다. INT는 4바이트, VARCHAR는 최대 바이트 수를 기준으로 계산되며 복합 인덱스의 경우는 구성 컬럼들의 총합이다.

ref

현재 행에서 key 인덱스를 통해 어떤 값을 기준으로 탐색하는지를 나타낸다. 상수(const), 이전 테이블 컬럼 (db.table.column) 등으로 표시되며 조인 시 기준 컬럼이 무엇인지 파악하는 데 유용하다.

rows

MySQL이 통계 정보 기반으로 추정한 탐색 대상 행의 수이다. 실제 실행 수치가 아니라 예측값이며 EXPLAIN ANALYZE가 아니라면 정확하지 않을 수 있다. 실제 반환 건수보다 rows가 과도하게 많거나 적다면 옵티마이저 통계 정보가 부정확하거나 인덱스가 비효율적일 수 있다.

filtered

스토리지 엔진으로부터 받아온 행 중 WHERE 조건에 의해 필터링되는 행의 비율(%)이다. rows * filtered / 100 계산을 통해 실제로 결과로 나올 레코드 수를 추정할 수 있다. 이 값이 낮을수록 많은 행이 조건에서 제거되므로 조인 순서나 조건 최적화에 활용할 수 있다.

Extra

옵티마이저가 판단한 추가적인 실행 힌트를 표시한다. 다음과 같은 정보들이 포함된다

• Using where: WHERE 조건으로 필터링 발생
• Using index: 테이블 접근 없이 인덱스만으로 쿼리 처리 (커버링 인덱스)
• Using index condition: 인덱스 조건 푸시다운 (ICP), 스토리지 엔진이 WHERE 조건 필터링 수행
• Using temporary: 임시 테이블 사용 (GROUP BY, DISTINCT, ORDER BY 등)
• Using filesort: 정렬 작업 발생 (ORDER BY를 인덱스로 처리 못한 경우)
• Using join buffer: 인덱스 없이 조인 버퍼 사용 (Nested Loop Join 등)
• Not exists: 조건 만족 시 추가 탐색 생략 (LEFT JOIN, NOT EXISTS 등)
• Select tables optimized away: 집계 함수 등으로 테이블 접근이 생략된 경우 (ex. SELECT MIN(c))
• Range checked for each record: 조건에 따라 각 레코드마다 인덱스 사용 여부 결정
• Using union, Using intersect, Using sort_union: 여러 인덱스 병합 수행 방식

활용 예시

EXPLAIN SELECT u.id, o.id 
FROM user u 
JOIN orders o ON u.id = o.user_id 
WHERE u.email = 'abc@example.com';

• user 테이블: email 컬럼에 인덱스가 있을 경우 ref 방식으로 접근하며 특정 사용자 1명만 조회하므로 빠르다.
• orders 테이블: user_id가 외래키로 선언되어 있고 인덱스가 존재하면 eq_ref 방식으로 조인이다. 이는 각 사용자에 대해 정확히 하나의 order를 찾을 때 가장 효율적인 접근 방식이다.
• 조인 조건이 효율적으로 설정되어 있고 인덱스가 적절히 활용되므로 ALL이나 filesort 같은 비효율적인 방식이 나타나지 않아 좋은 실행 계획으로 평가할 수 있다.

최적화 예시

MySQL은 인덱스가 있다고 해도 다음과 같은 경우 인덱스를 사용하지 않을 수 있다.

• WHERE 절의 컬럼과 인덱스 컬럼 타입이 일치하지 않음 (INT vs VARCHAR)
• 인덱스 길이가 잘려서 일부만 사용되는 경우
• 범위 조건이 선행 인덱스를 무시하는 경우 (a, b 복합 인덱스인데 b만 WHERE 조건에 사용)

이런 경우 아래와 같이 인덱스 조건을 재정의하거나 통계 정보를 갱신해야 한다.

-- 컬럼 타입 일치시키기
ALTER TABLE tt MODIFY ActualPC VARCHAR(15);

-- 인덱스 통계 재분석
ANALYZE TABLE tt;

또는 EXPLAIN 결과에서 key가 NULL인 경우엔 옵티마이저가 사용할 인덱스가 없다고 판단한 것이므로 해당 컬럼에 적절한 인덱스를 새로 추가하는 것도 좋은 방법이다.

FORMAT=JSON 간단 소개

MySQL 8.0 이상에서는 실행 계획을 더 상세하게 구조화된 형식으로 볼 수 있도록 FORMAT=JSON 옵션을 제공한다.

EXPLAIN FORMAT=JSON
SELECT * FROM user WHERE id BETWEEN 100 AND 200;

이 출력은 아래와 같은 추가 정보를 포함한다.

• attached_condition: WHERE 절의 조건이 어떤 방식으로 적용되는지
• using_index: 커버링 인덱스 사용 여부
• rows_examined_per_scan: 한 번 스캔 시 예상되는 행 수
• cost_info: 비용 기반 옵티마이저가 평가한 총 비용
• access_type: 접근 방식 (range, ref 등)
• join_type: 조인 방식 정보
• using_join_buffer: 조인 시 사용되는 버퍼 방식 (Nested Loop, BKA 등)

JSON 포맷은 특히 복잡한 서브쿼리, 유니언, 인덱스 병합 등을 사용하는 고급 쿼리 분석 시 강력한 도구로 활용된다.

📝 배운점

이번 학습을 통해 MySQL에서 EXPLAIN이 얼마나 중요한 도구인지 명확하게 이해할 수 있었다. 단순히 인덱스가 있느냐 없느냐가 아니라 옵티마이저가 정확히 어떤 방식으로 데이터를 접근하고 얼마나 많은 행을 스캔하고 필터링이 얼마나 잘 적용되고 있는지를 파악하는 것이 핵심이다.

특히 다음 항목은 앞으로 쿼리 튜닝 시 반드시 체크할 계획이다.

• type 컬럼: ALL, index 라면 성능 저하 요인 -> 반드시 ref, range, eq_ref 로 유도
• rows, filtered: 예상 행 수와 필터링 비율 확인 -> 너무 많은 데이터를 처리하면 튜닝 필요
• Extra: Using temporary, Using filesort 등장 여부 확인 -> 정렬/임시 테이블 피하도록 설계
• EXPLAIN FORMAT=JSON: 고급 분석 시 구조화된 실행 계획 파악에 필수

참고

• MySQL 공식문서
• [DB 성능 최적화] 실행 계획(Explain)
• MySQL 실행계획(explain) 정리