MySQL 쿼리가 느릴 때 — 옵티마이저부터 EXPLAIN까지

목차

1. 옵티마이저란?
2. 통계란?
3. 드라이빙 테이블이란?
4. 힌트 조인이란?
5. EXPLAIN 읽는 법
6. 실무 튜닝 순서 정리

1. 옵티마이저란?

SQL을 작성하면 MySQL이 그냥 바로 실행하는 게 아닙니다.
내부적으로 "어떻게 실행할지 계획을 먼저 세우는 엔진" 이 있는데, 이게 바로 옵티마이저입니다.

네비게이션에 비유하면 이렇습니다.

목적지 (= SQL 결과)는 정해져 있다
근데 가는 길 (= 실행 방법)은 여러 가지다

네비게이션 → 최적 경로 자동 선택
옵티마이저 → 최적 실행 방법 자동 선택

옵티마이저가 결정하는 것들:

• 어떤 테이블을 먼저 읽을까?
• 인덱스를 쓸까 말까?
• 어떤 조인 방식을 쓸까?

2. 통계란?

옵티마이저는 실제로 데이터를 읽어보지 않고 "통계" 를 보고 판단합니다.

통계 = 테이블에 대한 요약 정보
- 전체 행 수
- 조건 필터 후 예상 행 수
- 인덱스 분포도 등

문제는 통계가 오래될 수 있다는 것

현실                    통계 (6개월 전 기준)
dept_id=10 → 10건       dept_id=10 → 1만건으로 기록됨

옵티마이저 판단:
"employees가 1만건이나 되니까 orders를 먼저 읽어야겠다"
→ 잘못된 판단 → 쿼리 느려짐

통계 갱신 시점

-- 통계 수동 갱신
ANALYZE TABLE post;
ANALYZE TABLE post, comment, category;

통계 갱신 후 결과 확인

ANALYZE TABLE 실행 후 아래처럼 나오면 정상입니다.

⚠️ 대용량 테이블일수록 10% 기준이 너무 높아서 자동갱신만 믿으면 안됩니다.
대량 배치 작업 후에는 습관적으로 ANALYZE TABLE을 실행하는 것이 좋습니다.

3. 드라이빙 테이블이란?

조인 시 가장 먼저 읽히는 테이블을 드라이빙 테이블이라고 합니다.

동작 원리

① 드라이빙 테이블에서 행을 하나 읽는다
② 그 행으로 드리븐 테이블에서 매칭 행을 찾는다
③ ①~② 반복

건수 차이날 때 성능 차이

employees = 10건 (WHERE 조건 후)
orders    = 500만 건

✅ employees(10건)가 드라이빙
   10번 루프 × 인덱스 탐색 = 빠름

❌ orders(500만 건)가 드라이빙
   500만번 루프 × 인덱스 탐색 = 매우 느림

드라이빙 테이블 선택 기준

❗ "작은 테이블이 드라이빙"은 오해입니다.
정확히는 WHERE 조건 적용 후 결과 행이 적은 쪽이 드라이빙이어야 합니다.

3-1. 드리븐 테이블이란?

드라이빙 테이블이 "먼저 읽히는 테이블"이라면,
드리븐 테이블은 "나중에 읽히는 테이블" = 탐색 당하는 쪽입니다.

둘의 관계

드라이빙 테이블        드리븐 테이블
(루프 바깥)           (루프 안쪽)

employees 행 1   →   orders에서 매칭 탐색
employees 행 2   →   orders에서 매칭 탐색
employees 행 3   →   orders에서 매칭 탐색
    ↑                     ↑
 한 번만 순회         매 행마다 반복 탐색

핵심 차이점

드리븐 테이블에 인덱스가 없으면?

employees 10건이 드라이빙
orders 500만 건이 드리븐 + 인덱스 없음

→ 10번 루프 × 500만 건 풀스캔
= 총 5000만 건 탐색 ❌ 최악

orders에 인덱스 있으면
→ 10번 루프 × 인덱스로 빠르게 탐색 ✅

⚠️ 드리븐 테이블에 인덱스가 없는 상태에서 힌트로 조인 순서만 바꿔봤자 큰 효과가 없습니다.
드리븐 테이블 인덱스 유무 확인이 힌트 조인보다 우선입니다.

4. 힌트 조인이란?

옵티마이저가 잘못된 실행계획을 세울 때, 개발자가 직접 실행 방식을 강제하는 것입니다.

힌트 종류 (MySQL 기준)

STRAIGHT_JOIN — 조인 순서 강제

-- ❌ 옵티마이저가 orders를 드라이빙으로 잘못 선택
SELECT e.name, o.order_date
FROM orders o
JOIN employees e ON o.emp_id = e.id
WHERE e.dept_id = 10;

-- ✅ STRAIGHT_JOIN으로 employees를 드라이빙으로 강제
SELECT STRAIGHT_JOIN e.name, o.order_date
FROM employees e        -- ← FROM 절 첫 번째 = 드라이빙
JOIN orders o ON o.emp_id = e.id
WHERE e.dept_id = 10;

STRAIGHT_JOIN 핵심 주의사항

• 키워드만 추가하면 안됩니다
• 드라이빙으로 쓸 테이블을 FROM 절 맨 앞에 배치해야 합니다

FORCE INDEX / USE INDEX

-- 특정 인덱스 강제 사용
SELECT * FROM post FORCE INDEX (idx_created_at)
WHERE created_at >= '2024-01-01';

-- 특정 인덱스 제외
SELECT * FROM post IGNORE INDEX (idx_title)
WHERE created_at >= '2024-01-01';

⚠️ 힌트 조인은 임시방편입니다.
근본 원인(통계 갱신, 인덱스 재설계)을 먼저 해결하고,
그래도 안 될 때 힌트를 최후 수단으로 사용하세요.

5. EXPLAIN 읽는 법

EXPLAIN
SELECT p.title, c.name
FROM post p
JOIN category c ON p.category_id = c.id
WHERE c.name = '개발';

핵심 컬럼

type 컬럼 — 좋은 순서

const    → PK/UNIQUE로 1건 조회     ✅ 최상
eq_ref   → 조인에서 PK 1건 매칭    ✅ 좋음
ref      → 인덱스로 여러 건 조회   ✅ 양호
range    → 인덱스 범위 스캔        🔶 보통
index    → 인덱스 풀스캔           🔶 주의
ALL      → 테이블 풀스캔           ❌ 최악

Extra 컬럼 위험 신호

EXPLAIN 결과 읽기 예시

id | table    | type | key         | rows    | Extra
──────────────────────────────────────────────────────────
 1 | category | ref  | idx_name    |    1    | Using where  ✅
 1 | post     | ALL  | NULL        |   66    | ❌ 인덱스 없음!

• 위에 나온 테이블 = 드라이빙 테이블
• type=ALL → 인덱스 추가 필요 신호
• key=NULL → 인덱스를 전혀 못 쓰고 있다는 신호

6. 실무 튜닝 순서 정리

쿼리가 느리다
    ↓
EXPLAIN으로 실행계획 확인
    ↓
type=ALL 있나? → 인덱스 추가 검토
    ↓
Extra에 Using temporary / Using filesort 있나? → 인덱스 or 쿼리 수정
    ↓
통계가 오래됐나? → ANALYZE TABLE 실행
    ↓
그래도 이상하다 → 힌트 조인 적용 (최후 수단)
    ↓
힌트 적용 후 EXPLAIN 재확인

💡 실무 팁: type=ALL과 Extra=Using temporary 이 두 가지만 없애도
대부분의 성능 문제는 해결됩니다.

마치며

이 글에서 다룬 내용 요약:

• 옵티마이저 = SQL 실행계획 자동으로 짜주는 MySQL 두뇌
• 통계 = 옵티마이저의 판단 근거, 오래되면 갱신 필요
• 드라이빙 테이블 = 조인 시 먼저 읽히는 테이블, 행 수가 적은 쪽이 유리
• 드리븐 테이블 = 나중에 반복 탐색되는 테이블, 인덱스가 필수
• 힌트 조인 = 옵티마이저 판단 오류 시 개발자가 직접 강제하는 것
• EXPLAIN = 실행계획을 눈으로 확인하는 도구

다음 글에서는 인덱스 기초와 설계를 다뤄볼 예정입니다.

참고: 모든 예시는 MySQL 8.0 기준입니다.