EXPLAIN 사용 방법

살려줘 고영희

 

데이터베이스 성능 최적화를 위해 EXPLAIN을 사용하여 SQL 실행 계획을 분석하는 방법을 정리하고자 합니다.

---

1. EXPLAIN 기본 개념 

EXPLAIN은 SQL 실행 계획을 분석하고 최적화할 수 있도록 도와주는 명령어입니다.

EXPLAIN SELECT * FROM 테이블명 WHERE 조건;

 

또는 실행 시간을 포함한 상세한 분석을 원하면 ANALYZE를 사용할 수 있습니다.

(ANALYZE 사용 가능 버전은 MySQL 8.0+, MariaDB 10.5+ 입니다.😢)

EXPLAIN ANALYZE SELECT * FROM 테이블명 WHERE 조건;

 

---

2. EXPLAIN 결과 컬럼 설명

EXPLAIN 쿼리를 수행하면 아래와 같은 컬럼들을 조회할 수 있습니다.

컬럼명	설명
id	실행 단계 (조인 시 여러 개의 ID가 존재 가능)
select_type	쿼리 유형 (SIMPLE, SUBQUERY, UNION 등)
table	조회 대상 테이블
type	조인 및 조회 방식 (ALL, index, range, ref, const 등)
possible_keys	사용 가능한 인덱스
key	실제 사용된 인덱스
key_len	사용된 인덱스의 길이 (바이트 단위)
ref	인덱스를 통해 비교하는 값
rows	예상 검색 행 개수 (작을수록 좋음)
filtered	해당 테이블에서 조건을 만족할 것으로 MySQL이 예측하는 행(row)의 비율(%)
Extra	추가 실행 정보 (Using index, Using filesort, Using temporary 등)

---

3. EXPLAIN 결과 해석

☑️ 예시 1: 기본 SELECT 문 

✔️ EXPLAIN 쿼리 예시

EXPLAIN SELECT * FROM tbl_user_test WHERE user_id=1;

 

✔️ EXPLAIN 실행 결과

 

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	tbl_user	const	PRIMARY	PRIMARY	8	const	1

 

✔️ 해석

• type = const → PRIMARY KEY(idx)를 이용한 최적화된 단일 행 조회
• rows = 1 → 1개의 행만 조회되므로 성능 최적화 필요 없음

 

☑️ 예제 2: 풀 테이블 스캔 발생

✔️ EXPLAIN 쿼리 예시

EXPLAIN SELECT * FROM tbl_user_test WHERE username='user1';

 

✔️ EXPLAIN 실행 결과

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	tbl_user_test	ALL	NULL	NULL	NULL	NULL	10000	Using Where

 

✔️ 해석

• type = ALL → 풀 테이블 스캔 발생 (비효율적)
• possible_keys = NULL → 인덱스 사용 안 됨
• rows = 10000 → 조회 예상 행 수가 많음 → 성능 저하

✔️ 해결 방법 

조회 조건의 index를 생성 한 후, 다시 EXPLAIN 실행하면 type = ref로 바뀌며 성능 개선이 됩니다.

 

인덱스 조회 및 생성 방법 (MariaDB)

CREATE INDEX idx_username ON tbl_user_test(username); -- index 생성
SHOW INDEX FROM tbl_user_test; -- index 조회

 

다시 조회한 결과

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	tbl_user_test	ref	idx_username	idx_username	152	const	1	Using index condition

---

4. EXPLAIN으로 JOIN 최적화 하기

JOIN을 하게 되면 어떻게 결과가 나올지 확인해보겠습니다.

 

✔️ EXPLAIN 쿼리 예시  

SELECT tot.order_id, tut.username, tot.order_date, tot.total_amount, tot.status 
FROM tbl_orders_test tot JOIN tbl_user_test tut 
ON tot.user_id = tut.id
WHERE tot.user_id=1;

 

✔️ EXPLAIN 실행 결과

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	tut	const	PRIMARY	PRIMARY	8	const	1
1	SIMPLE	tot	ref	user_id	user_id	8	const	1

 

✔️ 해석

• type = const → 가장 빠른 조회 방식 사용
• tut 테이블에서 PRIMARY KEY를 기준으로 단 하나의 행만 조회 (rows=1)
• 이미 최적화된 상태로 성능 최적화가 필요 없음

---

5. EXPLAIN으로 쿼리 최적화 여부 확인하는 방법

정리하자면, 아래 항목을 확인해서 쿼리 최적화 여부를 확인할 수 있습니다.

• type이 const, ref → PK 및 인덱스를 활용한 빠른 조회
• key가 NULL이 아님 → 인덱스를 올바르게 활용 중
• rows 값이 1~100 이내 → 불필요한 데이터 스캔 없음
• Extra에 성능 저하 요소 없음 → 추가 최적화 불필요

좀 더 자세히 알아보겠습니다.

 

1) type이 최적화된 상태인지 확인

type 컬럼의 값이 아래 순서대로 좋은 조회 방식을 나타냅니다. 

최적화 정도	조회 방식 (type)	설명	해결 방법
최적 😊	const	PK 기반 단일 행 조회 (가장 빠름)
좋음	eq_ref	PK 또는 Unique Key 기반 조인
보통	ref	인덱스를 사용한 조인 (성능 양호)	WHERE 절의 컬럼에 인덱스 추가
비효율적	range	범위 검색 (데이터가 많아지면 느려질 가능성 있음)	인덱스 최적화 필요
문제 있음	index	인덱스 전체 스캔 (불필요한 데이터 접근)	필요한 컬럼만 조회 (SELECT * 안돼요)
최악 😣	ALL	풀 테이블 스캔 (인덱스 사용 안 함)	인덱스 추가, WHERE 최적화

 

2) key 컬럼이 NULL이 아닌지 확인

• key는 실제 사용된 인덱스를 나타냅니다.
• NULL이면 인덱스가 사용되지 않은 것이라서 최적화가 필요합니다.

 

 

3) rows 값이 불필요하게 크지 않은지 확인

• rows 값이 클수록 많은 데이터를 스캔해야 하므로 성능 저하 가능성이 있음
• rows 값이 1~100 정도면 최적화된 상태
• rows 값이 10000 이상이면 인덱스 추가 고려

 

4) Extra 컬럼에서 성능 저하 요소 확인

Extra 컬럼에 비효율적인 실행 계획이 포함되어 있는지 확인해야 합니다.

친절한 분들입니다.

 

Extra 값	설명	해결 방법
(없음)	최적화 완료	필요 없음
Using index	인덱스만으로 조회됨 (좋음)	문제 없음
Using where	WHERE 필터링 중 (보통)	인덱스 추가 고려
Using filesort	ORDER BY 최적화 필요	인덱스 추가
Using temporary	임시 테이블 생성 (비효율적)	쿼리 최적화 필요

 

---

6. 최적화 체크리스트

체크 항목	최적화 상태	조치 필요 여부
type이 좋은 값(const, eq_ref, ref)인지?
key가 NULL이 아닌지? (인덱스 사용 여부)
rows 값이 너무 크지 않은지?
Extra에 성능 저하 요소가 있는지? (Using filesort, Using temporary)

 

---

추가) filtered 컬럼

filtered 컬럼은 해당 테이블에서 조건을 만족할 것으로 MySQL이 예측하는 행(row)의 비율(%)을 의미합니다.

 

예를 들어, filtered = 50.00이면, MySQL은 전체 행의 50% 정도가 WHERE 조건을 만족할 것이라고 판단하는겁네다.

아래와 같은 쿼리가 있을 경우, filtered가 가장 낮은 테이블을 먼저 조인하는게 더 좋게 됩니다.

SELECT
    a.col_a,
    a.col_b,
    SUM(a.col_c) AS totalAmount,
    COUNT(*) AS totalCount
FROM
    table_1 t1
    JOIN table_2 t2 ON t1.id = t2.fk1
    JOIN table_3 t3 ON t2.id = t3.fk2
    JOIN table_4 a ON a.fk3 = t3.id
WHERE
    t1.id = 25
GROUP BY
    a.col_a;

 

id	select_type	table	type	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	const	PRIMARY	8	const	1	100.00	Using index; Using temporary
1	SIMPLE	t3	index	PRIMARY, fk2_idx	8	(null)	3	100.00	Using index
1	SIMPLE	t2	eq_ref	PRIMARY	8	t3.fk2	1	66.67	Using where
1	SIMPLE	a	ref	fk3_idx	8	t3.id	4	100.00	(없음)

 

 

계산 방식

최종 사용될 행 수 = rows × (filtered / 100)

 

 

• rows = 1000, filtered = 10.00 → 1000 × 0.1 = 100개 사용될 것으로 예측
• rows = 5, filtered = 100.00 → 5개 모두 사용

 

filtered	값 의미	일반적 해석
100.00	모든 행이 조건에 해당	인덱스 또는 WHERE 조건이 없거나 무효
낮음 (예: 10.00)	대부분의 행이 걸러짐	좋은 필터링 조건이 있음
중간 (예: 66.67)	일부가 걸러짐	조건이 있긴 하나 필터링 효과가 적음

하지만, 낮다고 항상 좋은 건 아니고, 조인 순서, 조건 위치, 인덱스 사용 여부와 함께 봐야합니다...!

그렇다면, 3번째 행에서 사용된 테이블의 JOIN 순서가 무조건 앞 순서로 가야할까?

는 아닙니다.

 

filtered와 JOIN 순서

• filtered 값이 낮은 테이블을 먼저 읽는 것이 좋을 수 있음
• 하지만 WHERE 조건이 직접 걸린 테이블이 우선적으로 효율적임
• 옵티마이저는 filtered, rows, key, type 등 여러 요소를 함께 고려하여 조인 순서를 결정