EXPLAIN과 EXPLAIN ANALYZE 차이는?

EXPLAIN은 옵티마이저가 세운 **예상** 계획입니다. EXPLAIN ANALYZE(8.0.18+)는 실제 실행까지 하며 **실측** 시간·행 수를 보여 줍니다.

인덱스를 더 달았는데 느려졌어요

쓰기·갱신 비용, 카디널리티 부족, 잘못된 컬럼 순서로 인해 옵티마이저가 안 쓸 수 있습니다. 통계 갱신과 실제 계획 재확인이 필요합니다.

슬로우 쿼리 로그는 어디서 켜나요?

`long_query_time`, `log_error_verbosity` 등과 함께 Performance Schema의 statements_summary로도 병행하는 경우가 많습니다. 운영 정책에 맞게 설정합니다.

MySQL EXPLAIN으로 느린 쿼리 잡기 | 실행 계획·인덱스 튜닝 실전

2026년 3월 30일 · 28분 읽기 · 수정 2026년 3월 31일 중급 튜토리얼

이 글의 핵심

EXPLAIN과 EXPLAIN ANALYZE로 실행 계획을 읽고 type·rows·Extra를 기준으로 인덱스·쿼리 형태를 조정하는 절차를 단계별로 정리합니다.

들어가며

MySQL에서 응답 지연이 나면 원인은 잘못된 인덱스, 부적절한 조인 순서, 과도한 풀 스캔, 통계 오래됨 등으로 압축됩니다. MySQL EXPLAIN 인덱스 튜닝은 추측이 아니라 실행 계획을 읽고, 가장 비싼 단계를 줄이는 작업입니다.

이 글은 InnoDB·MySQL 8.x를 기준으로 EXPLAIN 출력 필드를 해석하고, 인덱스 추가·쿼리 재작성·통계 갱신의 순서를 제시합니다. ORM을 쓰더라도 최종 SQL에 대해 같은 절차를 적용할 수 있습니다.

이 글을 읽으면

EXPLAIN / EXPLAIN ANALYZE 결과에서 type, key, rows, Extra를 해석할 수 있습니다
복합 인덱스 컬럼 순서와 커버링 인덱스 개념을 적용할 수 있습니다
통계·히스토그램 갱신과 슬로우 쿼리 수집을 운영 루틴에 넣을 수 있습니다

개념 설명

실행 계획이란

옵티마이저는 통계·비용 모델로 어떤 인덱스를 탈지, 조인 순서, 접근 방식(range/ref/eq_ref 등)을 정합니다. EXPLAIN은 그 결정을 사람이 읽을 수 있게 펼친 것입니다.

주요 EXPLAIN 컬럼 (요약)

컬럼	의미
id	SELECT 식별자 (서브쿼리·UNION 구분)
select_type	SIMPLE, PRIMARY, SUBQUERY, DERIVED 등
table	접근하는 테이블 이름
type	접근 방식—`ALL`(풀 스캔)이 가장 무거운 편, `const`·`eq_ref`·`range`는 상대적으로 유리
possible_keys	사용 가능한 인덱스 목록
key	실제 선택된 인덱스 이름(NULL이면 인덱스 미사용)
key_len	사용된 인덱스 길이 (복합 인덱스 일부만 사용 시 확인)
ref	인덱스와 비교되는 컬럼/상수
rows	예상 검사 행 수(작을수록 좋은 경향, 단 추정치)
filtered	WHERE 조건으로 필터링될 비율 (%)
Extra	`Using filesort`, `Using temporary`, `Using index` 등 부가 동작 힌트

type 접근 방식 순서 (빠름 → 느림)

type	설명	예시
const	PK 또는 유니크 인덱스로 단일 행	`WHERE id = 1`
eq_ref	조인 시 PK/유니크로 단일 행 매칭	`JOIN users ON orders.user_id = users.id`
ref	비유니크 인덱스로 여러 행	`WHERE user_id = 42`
range	인덱스 범위 스캔	`WHERE created_at > '2026-01-01'`
index	인덱스 풀 스캔	인덱스 전체 읽기
ALL	테이블 풀 스캔	인덱스 미사용

목표: ALL을 range 이상으로 개선

실전 구현

1) 테스트 데이터 준비

CREATE TABLE orders (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  product_id BIGINT NOT NULL,
  status VARCHAR(16) NOT NULL,
  amount DECIMAL(10, 2) NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  INDEX idx_user (user_id),
  INDEX idx_created (created_at)
) ENGINE=InnoDB;

-- 테스트 데이터 삽입
INSERT INTO orders (user_id, product_id, status, amount, created_at, updated_at)
SELECT 
  FLOOR(RAND() * 10000) + 1,
  FLOOR(RAND() * 1000) + 1,
  ELT(FLOOR(RAND() * 4) + 1, 'pending', 'paid', 'shipped', 'cancelled'),
  RAND() * 1000,
  DATE_ADD('2026-01-01', INTERVAL FLOOR(RAND() * 90) DAY),
  NOW()
FROM 
  (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t1,
  (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t2,
  (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t3,
  (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t4,
  (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t5,
  (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) t6;
-- 약 4096행 삽입

2) 느린 쿼리 분석

예제 쿼리 1: 단일 조건

EXPLAIN
SELECT *
FROM orders
WHERE user_id = 42;

출력:

+----+-------------+--------+------+---------------+----------+---------+-------+------+-------+
| id | select_type | table  | type | possible_keys | key      | key_len | ref   | rows | Extra |
+----+-------------+--------+------+---------------+----------+---------+-------+------+-------+
|  1 | SIMPLE      | orders | ref  | idx_user      | idx_user | 8       | const |    5 | NULL  |
+----+-------------+--------+------+---------------+----------+---------+-------+------+-------+

분석:

type: ref (인덱스 사용)
key: idx_user (예상대로)
rows: 5 (예상 검사 행 수)
Extra: NULL (추가 처리 없음)

결론: 인덱스 잘 사용됨

예제 쿼리 2: 복합 조건 (인덱스 미사용)

EXPLAIN
SELECT *
FROM orders
WHERE user_id = 42 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

출력:

+----+-------------+--------+------+---------------+----------+---------+-------+------+-----------------------------+
| id | select_type | table  | type | possible_keys | key      | key_len | ref   | rows | Extra                       |
+----+-------------+--------+------+---------------+----------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | orders | ref  | idx_user      | idx_user | 8       | const |    5 | Using where; Using filesort |
+----+-------------+--------+------+---------------+----------+---------+-------+------+-----------------------------+

분석:

type: ref (인덱스 사용)
key: idx_user (user_id 인덱스만 사용)
Extra: Using where; Using filesort
- Using where: status 조건은 인덱스 미사용 (필터링)
- Using filesort: ORDER BY created_at을 위해 정렬 필요

문제점:

status 조건이 인덱스 미사용
정렬을 위한 filesort 발생

3) 복합 인덱스 추가

-- user_id, status, created_at 순서로 복합 인덱스
ALTER TABLE orders
  ADD INDEX idx_user_status_created (user_id, status, created_at DESC);

인덱스 컬럼 순서 규칙:

동등 조건 (=) 먼저
범위 조건 (>, <, BETWEEN) 다음
정렬 컬럼 마지막

재확인

EXPLAIN
SELECT *
FROM orders
WHERE user_id = 42 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

출력:

+----+-------------+--------+------+---------------------------+---------------------------+---------+-------------+------+-------+
| id | select_type | table  | type | possible_keys             | key                       | key_len | ref         | rows | Extra |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------------+------+-------+
|  1 | SIMPLE      | orders | ref  | idx_user,idx_user_status_created | idx_user_status_created | 74      | const,const |    2 | NULL  |
+----+-------------+--------+------+---------------------------+---------------------------+---------+-------------+------+-------+

개선 사항:

key: idx_user_status_created (복합 인덱스 사용)
rows: 5 → 2 (예상 행 수 감소)
Extra: NULL (filesort 사라짐)

4) EXPLAIN ANALYZE (실제 실행)

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE user_id = 42 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20\G

출력:

-> Limit: 20 row(s)  (cost=2.25 rows=2) (actual time=0.123..0.145 rows=2 loops=1)
    -> Index lookup on orders using idx_user_status_created (user_id=42, status='paid'), with index condition: (orders.`status` = 'paid')  (cost=2.25 rows=2) (actual time=0.121..0.143 rows=2 loops=1)

분석:

cost: 2.25 (옵티마이저 비용 추정)
actual time: 0.123..0.145ms (실제 실행 시간)
rows: 2 (실제 반환 행 수)
loops: 1 (실행 횟수)

결론: 인덱스 최적화 성공

5) 커버링 인덱스 (Covering Index)

시나리오: id, user_id, status만 필요한 쿼리

-- 기존 쿼리
EXPLAIN
SELECT id, user_id, status
FROM orders
WHERE user_id = 42 AND status = 'paid';

출력:

Extra: NULL

커버링 인덱스 추가:

-- 필요한 컬럼을 모두 인덱스에 포함
ALTER TABLE orders
  ADD INDEX idx_covering (user_id, status, id);

재확인:

EXPLAIN
SELECT id, user_id, status
FROM orders
WHERE user_id = 42 AND status = 'paid';

출력:

Extra: Using index

분석:

Using index: 인덱스만으로 쿼리 완료 (테이블 접근 불필요)
성능 향상: 클러스터 인덱스 접근 생략

6) 통계 갱신

-- 테이블 통계 갱신
ANALYZE TABLE orders;

-- 히스토그램 생성 (8.0+)
ANALYZE TABLE orders UPDATE HISTOGRAM ON user_id, status;

-- 히스토그램 확인
SELECT * FROM information_schema.COLUMN_STATISTICS
WHERE TABLE_NAME = 'orders';

언제 실행:

대량 INSERT/UPDATE 후
인덱스 추가 후
쿼리 계획이 이상할 때

고급 활용

1) Optimizer Hints

강제 인덱스 사용:

SELECT *
FROM orders USE INDEX (idx_user_status_created)
WHERE user_id = 42 AND status = 'paid';

인덱스 무시:

SELECT *
FROM orders IGNORE INDEX (idx_user)
WHERE user_id = 42;

조인 순서 힌트:

SELECT /*+ JOIN_ORDER(orders, users) */ *
FROM orders
JOIN users ON orders.user_id = users.id
WHERE orders.status = 'paid';

주의사항:

힌트는 최후의 수단
옵티마이저가 잘못 선택하는 이유 먼저 파악
통계 갱신으로 해결 가능한 경우 많음

2) 슬로우 쿼리 로그

설정

-- 슬로우 쿼리 로그 활성화
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 1초 이상 쿼리
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- 로그 파일 위치 확인
SHOW VARIABLES LIKE 'slow_query_log_file';

분석 (pt-query-digest)

# Percona Toolkit 설치
sudo apt-get install percona-toolkit

# 슬로우 쿼리 로그 분석
pt-query-digest /var/log/mysql/slow.log > report.txt

출력 예시:

# Query 1: 0.52 QPS, 1.23s avg, ID 0x1234ABCD
# Time range: 2026-03-30 00:00:00 to 23:59:59
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         10    1500
# Exec time     45   1845s    0.5s    5.2s   1.23s   2.1s   0.45s   1.1s
# Rows sent      8  30000      20      20      20      20       0      20
# Rows examine  92  15M      10k     20k     10k     15k    2.5k     10k

SELECT * FROM orders WHERE user_id = ? AND status = ? ORDER BY created_at DESC LIMIT 20\G

분석:

이 쿼리가 전체 실행 시간의 45% 차지
평균 10k 행 검사 → 인덱스 개선 필요

3) Performance Schema

-- Performance Schema 활성화 (my.cnf)
[mysqld]
performance_schema = ON

-- 느린 쿼리 상위 10개
SELECT 
  DIGEST_TEXT,
  COUNT_STAR as exec_count,
  AVG_TIMER_WAIT / 1000000000000 as avg_sec,
  SUM_ROWS_EXAMINED as total_rows_examined
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

성능·비교

접근 방식 비교

type	예상 비용	인덱스 사용	시나리오
const	매우 낮음	PK/유니크	`WHERE id = 1`
eq_ref	낮음	PK/유니크 조인	`JOIN ON pk`
ref	낮음~중간	비유니크 인덱스	`WHERE user_id = 42`
range	중간	인덱스 범위	`WHERE created_at > '2026-01-01'`
index	높음	인덱스 풀 스캔	`SELECT id FROM orders` (커버링)
ALL	매우 높음	없음	`WHERE YEAR(created_at) = 2026`

벤치마크 예시

테스트 환경:

테이블: 100만 행
쿼리: WHERE user_id = 42 AND status = 'paid' ORDER BY created_at DESC LIMIT 20

인덱스	type	rows	실행 시간
없음	ALL	1,000,000	1.2s
idx_user	ref	100	150ms
idx_user_status_created	ref	5	8ms

결론: 복합 인덱스로 150배 개선

실무 사례

사례 1: 목록 API - 풀 스캔 제거

Before:

SELECT *
FROM orders
ORDER BY created_at DESC
LIMIT 20;

EXPLAIN 출력:

type: ALL
rows: 1000000
Extra: Using filesort

문제점:

인덱스 미사용 (WHERE 조건 없음)
전체 테이블 스캔 후 정렬

After:

-- created_at 인덱스 추가
ALTER TABLE orders
  ADD INDEX idx_created_desc (created_at DESC);

-- 쿼리 재실행
SELECT *
FROM orders
ORDER BY created_at DESC
LIMIT 20;

EXPLAIN 출력:

type: index
key: idx_created_desc
rows: 20
Extra: NULL

개선 사항:

type: ALL → index
rows: 1,000,000 → 20
Extra: filesort 사라짐
실행 시간: 1.2s → 5ms

사례 2: OR 조건 - UNION 분할

Before:

EXPLAIN
SELECT *
FROM orders
WHERE user_id = 42 OR product_id = 100;

EXPLAIN 출력:

type: ALL
key: NULL
rows: 1000000
Extra: Using where

문제점:

OR 조건으로 인덱스 미사용
풀 스캔 발생

After:

-- UNION으로 분할
EXPLAIN
SELECT * FROM orders WHERE user_id = 42
UNION
SELECT * FROM orders WHERE product_id = 100;

EXPLAIN 출력:

-- 첫 번째 SELECT
type: ref
key: idx_user
rows: 5

-- 두 번째 SELECT
type: ref
key: idx_product
rows: 3

개선 사항:

각 SELECT가 인덱스 사용
실행 시간: 1.5s → 12ms

사례 3: 함수로 감싼 컬럼 - 범위 조건 변환

Before:

EXPLAIN
SELECT *
FROM orders
WHERE DATE(created_at) = '2026-03-30';

EXPLAIN 출력:

type: ALL
key: NULL
rows: 1000000
Extra: Using where

문제점:

DATE() 함수로 인덱스 미사용
풀 스캔 발생

After:

-- 범위 조건으로 변환
EXPLAIN
SELECT *
FROM orders
WHERE created_at >= '2026-03-30 00:00:00'
  AND created_at < '2026-03-31 00:00:00';

EXPLAIN 출력:

type: range
key: idx_created
rows: 150
Extra: Using index condition

개선 사항:

type: ALL → range
key: idx_created 사용
실행 시간: 1.8s → 15ms

사례 4: 조인 최적화

Before:

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

EXPLAIN 출력:

-- orders 테이블
type: ALL
key: NULL
rows: 1000000
Extra: Using where

-- users 테이블
type: eq_ref
key: PRIMARY
rows: 1

문제점:

orders 테이블 풀 스캔
status 인덱스 미사용

After:

-- status 인덱스 추가
ALTER TABLE orders
  ADD INDEX idx_status (status);

-- 쿼리 재실행
EXPLAIN
SELECT o.*, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'paid';

EXPLAIN 출력:

-- orders 테이블
type: ref
key: idx_status
rows: 5000

-- users 테이블
type: eq_ref
key: PRIMARY
rows: 1

개선 사항:

type: ALL → ref
rows: 1,000,000 → 5,000
실행 시간: 2.5s → 80ms

트러블슈팅

문제 1: 인덱스를 만들었는데 key가 NULL

증상:

ALTER TABLE orders ADD INDEX idx_status (status);

EXPLAIN SELECT * FROM orders WHERE status = 'paid';
-- key: NULL (인덱스 미사용)

원인 1: 통계 오래됨

ANALYZE TABLE orders;

원인 2: 카디널리티 부족

-- status 값 분포 확인
SELECT status, COUNT(*) as cnt
FROM orders
GROUP BY status;

-- 결과: 대부분 'paid' (선택도 낮음)
-- paid: 950000
-- pending: 30000
-- shipped: 15000
-- cancelled: 5000

해결: 선택도가 낮으면 옵티마이저가 풀 스캔 선택 가능 (정상)

원인 3: 데이터 타입 불일치

-- status는 VARCHAR인데 숫자로 비교
WHERE status = 1  -- 암시적 변환 → 인덱스 미사용

-- 해결
WHERE status = '1'

문제 2: rows가 실제와 크게 다름

증상:

EXPLAIN SELECT * FROM orders WHERE user_id = 42;
-- rows: 5 (예상)

-- 실제 실행
SELECT COUNT(*) FROM orders WHERE user_id = 42;
-- 결과: 500 (실제)

원인: 통계 오래됨

해결:

-- 통계 갱신
ANALYZE TABLE orders;

-- 히스토그램 생성 (8.0+)
ANALYZE TABLE orders UPDATE HISTOGRAM ON user_id;

문제 3: Using temporary 발생

증상:

EXPLAIN
SELECT user_id, COUNT(*) as cnt
FROM orders
GROUP BY user_id
ORDER BY cnt DESC
LIMIT 10;

-- Extra: Using temporary; Using filesort

원인: GROUP BY와 ORDER BY가 다른 컬럼

해결 1: 서브쿼리

SELECT user_id, cnt
FROM (
  SELECT user_id, COUNT(*) as cnt
  FROM orders
  GROUP BY user_id
) t
ORDER BY cnt DESC
LIMIT 10;

해결 2: 인덱스 최적화

-- user_id 인덱스 확인
ALTER TABLE orders ADD INDEX idx_user (user_id);

문제 4: 로컬에선 빠른데 운영만 느림

원인 1: 데이터 양 차이

-- 로컬: 1000행
-- 운영: 100만 행

-- 해결: 운영 데이터 샘플로 로컬 테스트

원인 2: 버퍼 풀 워밍업

-- 버퍼 풀 상태 확인
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

-- 버퍼 풀 크기 조정 (my.cnf)
[mysqld]
innodb_buffer_pool_size = 8G

원인 3: 동시 쿼리

-- 현재 실행 중인 쿼리 확인
SHOW PROCESSLIST;

-- 또는 Performance Schema
SELECT * FROM performance_schema.threads
WHERE TYPE = 'FOREGROUND';

마무리

MySQL EXPLAIN 인덱스 튜닝은 한 번의 계획 읽기로 끝나지 않고, 스키마 변경 → 통계 갱신 → EXPLAIN ANALYZE 재측정을 반복하는 과정입니다.

튜닝 체크리스트

측정
- ✅ EXPLAIN으로 실행 계획 확인
- ✅ EXPLAIN ANALYZE로 실제 시간 측정
- ✅ 슬로우 쿼리 로그 수집
분석
- ✅ type이 ALL인가? → 인덱스 추가
- ✅ Using filesort? → 정렬 컬럼 인덱스 추가
- ✅ Using temporary? → GROUP BY/ORDER BY 최적화
- ✅ rows가 많은가? → 복합 인덱스 검토
개선
- ✅ 복합 인덱스 컬럼 순서: 동등 → 범위 → 정렬
- ✅ 커버링 인덱스 검토
- ✅ 함수 제거 (범위 조건 변환)
- ✅ OR → UNION 분할
검증
- ✅ ANALYZE TABLE 실행
- ✅ EXPLAIN ANALYZE 재확인
- ✅ 실제 응답 시간 측정

다음 단계

PostgreSQL 비교: PostgreSQL vs MySQL
Node.js 통합: Node.js 데이터베이스 가이드
프로덕션 배포: Node.js Docker Compose 프로덕션

MySQL 쿼리 최적화는 측정 → 분석 → 개선 → 검증의 반복입니다. EXPLAIN은 그 시작점입니다.

이 글의 핵심

들어가며

이 글을 읽으면

목차

개념 설명

실행 계획이란

주요 EXPLAIN 컬럼 (요약)

type 접근 방식 순서 (빠름 → 느림)

실전 구현

1) 테스트 데이터 준비

2) 느린 쿼리 분석

예제 쿼리 1: 단일 조건

예제 쿼리 2: 복합 조건 (인덱스 미사용)

3) 복합 인덱스 추가

재확인

4) EXPLAIN ANALYZE (실제 실행)

5) 커버링 인덱스 (Covering Index)

6) 통계 갱신

고급 활용

1) Optimizer Hints

2) 슬로우 쿼리 로그

설정

분석 (pt-query-digest)

3) Performance Schema

성능·비교

접근 방식 비교

벤치마크 예시

실무 사례

사례 1: 목록 API - 풀 스캔 제거

사례 2: OR 조건 - UNION 분할

사례 3: 함수로 감싼 컬럼 - 범위 조건 변환

사례 4: 조인 최적화

트러블슈팅

문제 1: 인덱스를 만들었는데 key가 NULL

문제 2: rows가 실제와 크게 다름

문제 3: Using temporary 발생

문제 4: 로컬에선 빠른데 운영만 느림

마무리

튜닝 체크리스트

다음 단계