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

들어가며

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);

인덱스 컬럼 순서 규칙:

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

재확인

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 재측정을 반복하는 과정입니다.

튜닝 체크리스트

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

다음 단계

• PostgreSQL 비교: PostgreSQL vs MySQL
• Node.js 통합: Node.js 데이터베이스 가이드
• 프로덕션 배포: Node.js Docker Compose 프로덕션 MySQL 쿼리 최적화는 측정 → 분석 → 개선 → 검증의 반복입니다. EXPLAIN은 그 시작점입니다.

---

자주 묻는 질문 (FAQ)

Q. 이 내용을 실무에서 언제 쓰나요?

A. MySQL EXPLAIN 인덱스 튜닝: 실행 계획 읽는 법, type·key·rows, ANALYZE TABLE, 커버링 인덱스까지 느린 쿼리를 줄이는 실무 순서를 정리합니다. MySQL·EXPLAIN·쿼리 최적화 … 실무에서는 위 본문의 예제와 선택 가이드를 참고해 적용하면 됩니다.

Q. 선행으로 읽으면 좋은 글은?

A. 각 글 하단의 이전 글 또는 관련 글 링크를 따라가면 순서대로 배울 수 있습니다. C++ 시리즈 목차에서 전체 흐름을 확인할 수 있습니다.

Q. 더 깊이 공부하려면?

A. cppreference와 해당 라이브러리 공식 문서를 참고하세요. 글 말미의 참고 자료 링크도 활용하면 좋습니다.

---

같이 보면 좋은 글 (내부 링크)

이 주제와 연결되는 다른 글입니다.

• SQL 쿼리 최적화 실전 가이드 | 인덱스·실행 계획
• C++ 쿼리 최적화 완벽 가이드 | 인덱스 선택·실행 계획·통계·비용 모델·프로덕션 패턴 [#49-3]
• [2026] 데이터베이스 인덱싱 심화 | B-트리·클러스터드·스캔/시크·커버링·운영 패턴

---

이 글에서 다루는 키워드 (관련 검색어)

MySQL, EXPLAIN, 쿼리 최적화, 인덱스, 성능, 튜닝 등으로 검색하시면 이 글이 도움이 됩니다.