[MySQL] EXPLAIN으로 인덱스 성능 튜닝하는 법 (실행계획 완전 분석)

MySQL에서 쿼리 성능을 개선하려면 실행계획을 정확히 이해하고 분석하는 능력이 필요하다. 이때 핵심 도구가 바로 EXPLAIN이다. 인덱스가 잘 적용되고 있는지, 테이블 풀 스캔이 발생하는지 등을 눈으로 확인할 수 있다.

실무에서 성능 튜닝이 필요한 모든 순간, EXPLAIN은 강력한 무기가 된다.

🛠️ EXPLAIN 기본 문법

EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

또는

EXPLAIN FORMAT=TRADITIONAL SELECT ...
EXPLAIN FORMAT=JSON SELECT ...

→ FORMAT=JSON을 활용하면 좀 더 상세한 실행계획을 확인할 수 있다.

반응형

📊 EXPLAIN 결과 컬럼 설명

컬럼 이름	설명
id	쿼리 실행 순서를 의미함
select_type	SELECT의 유형 (SIMPLE, PRIMARY, SUBQUERY 등)
table	액세스되는 테이블 이름
type	조인의 유형 (ALL, index, range, ref, eq_ref 등)
possible_keys	사용할 수 있는 인덱스 목록
key	실제로 선택된 인덱스
key_len	사용된 인덱스 키 길이
ref	어떤 컬럼/값이 인덱스를 사용했는지
rows	예측되는 처리 행 수
Extra	추가 정보 (Using index, Using where, Using temporary 등)

 

🔎 type(접근 방식) 단계별 성능 순위

성능이 좋은 순서대로 정리하면 아래와 같다.

• system (최고속, 단 1행)
• const (거의 정적 상수)
• eq_ref (Primary Key 조인)
• ref (일반적인 인덱스 조인)
• range (범위 조건 사용)
• index (풀 인덱스 스캔)
• ALL (테이블 전체 스캔 → 가장 느림)

TIP : type이 ALL이면 성능 개선 여지가 있는 것으로 판단하고 인덱스를 검토해야 한다.

반응형

💡 실전 예제: 인덱스 없는 테이블

SELECT * FROM users WHERE email = 'test@example.com';

만약 users 테이블에 인덱스가 없다면 EXPLAIN 결과는 다음과 같다.

• type: ALL
• rows: 전체 건수
• key: NULL
• Extra: Using where

→ 풀스캔 발생 중. 인덱스 필요하다.

✅ 인덱스 적용 후 EXPLAIN 확인

CREATE INDEX idx_email ON users(email);

다시 실행하면:

• type: ref
• key: idx_email
• rows: 훨씬 적음
• Extra: Using where

→ 인덱스를 사용하며 성능이 개선된 것을 확인할 수 있다.

🧠 복합 인덱스와 selectivity 분석

복합 인덱스 예시:

CREATE INDEX idx_name_email ON users(name, email);

SELECT * FROM users WHERE name = 'John';

→ 인덱스 사용 가능 (type: ref)
하지만 email = 'john@test.com'만 WHERE에 있으면 인덱스 사용 못함.
복합 인덱스는 선두 컬럼(name)부터 조건이 있어야 활용된다.

🔄 JOIN 쿼리 튜닝: 조인 순서와 인덱스 확인

SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status = 'paid';

→ EXPLAIN 결과에서 users → orders 순으로 조인이 일어나고,
orders.user_id 또는 orders.status에 인덱스가 없으면 풀스캔이 발생할 수 있다.

해결법

CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_user_id ON orders(user_id);

조인 성능도 인덱스로 튜닝 가능하다.

반응형

📌 Extra 필드 체크 포인트

Extra 내용	의미 및 조치
Using where	WHERE 조건이 있음
Using index	커버링 인덱스 사용
Using temporary	임시 테이블 생성 → 성능 저하 원인
Using filesort	정렬 시 디스크 사용 → 인덱스 필요 가능성

 

→ Using temporary, Using filesort는 주의해서 튜닝 필요하다.

🧪 FORMAT=JSON으로 깊이 있는 분석

EXPLAIN FORMAT=JSON SELECT ...

→ 테이블별 비용(cost), 정렬 여부, 임시 테이블 사용 여부 등 더 풍부한 정보를 제공한다.

예시 키포인트:

• "using_index": true
• "using_temporary_table": true
• "sort_key" 필드 확인

📈 쿼리 튜닝 실전 팁 요약

1. EXPLAIN으로 현재 성능 병목 구간 확인
2. type이 ALL이면 인덱스 필요성 검토
3. possible_keys에 인덱스가 있고 key가 NULL이면 인덱스가 무시된 것
4. 복합 인덱스는 컬럼 순서와 WHERE 조건 순서 일치 여부 점검
5. Extra에 불필요한 임시 정렬 또는 테이블 생성 여부 확인

🧾 공식문서 링크

MySQL EXPLAIN 문서
https://dev.mysql.com/doc/refman/8.0/en/explain-output.html