실행 계획 분석과 쿼리 최적화

애플리케이션이 성장할수록 쿼리 한 줄의 성능 차이가 전체 서비스 응답 시간을 좌우합니다. 입문편에서 EXPLAIN의 기본 출력을 살펴봤다면, 이번 레슨에서는 그 결과를 실무 수준으로 해석하고 실제 실행 시간까지 측정하는 방법을 다룹니다.

단순히 "인덱스를 타지 않는다"는 진단을 넘어, 옵티마이저가 어떤 근거로 실행 계획을 선택했는지 추적하고, 슬로우 쿼리 로그와 분석 도구로 프로덕션 환경의 병목을 찾아내는 실전 워크플로우를 익히게 됩니다.

학습 목표

• EXPLAIN의 type, rows, Extra 컬럼을 정밀하게 해석하고 개선 포인트를 도출한다.
• EXPLAIN ANALYZE로 예측 행 수와 실제 실행 시간의 차이를 확인한다.
• optimizer_trace로 옵티마이저의 의사결정 과정을 직접 읽는다.
• 조인 순서와 NLJ·BNL·Hash Join 알고리즘의 차이를 이해하고 튜닝한다.
• 키셋 페이징으로 OFFSET 기반 페이지네이션의 성능 문제를 해결한다.

---

EXPLAIN type·rows·Extra 컬럼 정밀 해석

EXPLAIN의 출력 중 성능을 판단하는 데 가장 중요한 세 컬럼인 type, rows, Extra의 의미를 심층적으로 살펴봅니다.

type 컬럼 — 접근 방식의 비용 순서

type	설명	비고
system / const	PK·유니크 인덱스로 단 1건 조회	가장 빠름
eq_ref	조인에서 PK·유니크 인덱스로 1건 매칭	조인 최적
ref	일반 인덱스로 여러 건 매칭	양호
range	인덱스 범위 스캔 (BETWEEN, >, <, IN)	허용 범위
index	인덱스 전체 스캔 (풀 스캔이지만 인덱스만)	주의 필요
ALL	테이블 풀 스캔	즉시 개선 대상

ref 이상이면 일반적으로 허용되지만, 대용량 테이블에서 range가 수백만 건을 스캔한다면 여전히 느릴 수 있습니다. rows와 함께 봐야 합니다.

rows 컬럼 — 옵티마이저의 예측 행 수

rows는 실제 반환 행 수가 아니라 옵티마이저가 스캔할 것으로 예측하는 행 수입니다. 통계 정보(SHOW TABLE STATUS, 인덱스 카디널리티)를 바탕으로 계산되므로, 통계가 오래됐다면 크게 빗나갈 수 있습니다.

-- 통계 강제 갱신
ANALYZE TABLE orders;

-- 인덱스 카디널리티 확인
SHOW INDEX FROM orders;

Extra 컬럼 — 실행 방식의 세부 단서

Extra 값	의미	대응
Using index	커버링 인덱스로 테이블 접근 없음	이상적
Using where	인덱스 후 추가 필터 수행	인덱스 컬럼 재검토
Using filesort	ORDER BY를 인덱스로 해결 못 해 정렬	정렬 컬럼 인덱스 추가
Using temporary	집계·정렬에 임시 테이블 사용	GROUP BY 인덱스 활용
Using index condition	ICP(Index Condition Pushdown) 적용	스토리지 엔진 레벨 필터

⚠️ 주의 Using filesort와 Using temporary가 동시에 나타나면 GROUP BY + ORDER BY 조합이 인덱스를 전혀 활용하지 못하는 상태입니다. 쿼리 구조나 인덱스를 즉시 재검토하세요.

-- ❌ 풀 스캔 + filesort 유발 패턴
SELECT user_id, COUNT(*) AS cnt
FROM orders
WHERE status = 'pending'
GROUP BY user_id
ORDER BY cnt DESC;

-- ✅ (user_id, status) 복합 인덱스 추가 후 GROUP BY 개선
ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);

---

EXPLAIN ANALYZE와 실제 실행 시간 측정

MySQL 8.0.18부터 지원되는 EXPLAIN ANALYZE는 쿼리를 실제로 실행한 뒤 각 노드의 예측값과 실측값을 함께 보여 줍니다.

EXPLAIN ANALYZE
SELECT o.id, o.amount, u.name
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.created_at >= '2024-01-01'
ORDER BY o.created_at DESC
LIMIT 20;

출력 예시 (트리 형식):

-> Limit: 20 row(s)  (actual time=12.5..12.5 rows=20 loops=1)
    -> Sort: o.created_at DESC  (actual time=12.4..12.4 rows=20 loops=1)
        -> Nested loop inner join  (actual time=0.3..11.9 rows=3200 loops=1)
            -> Index range scan on o using idx_created  (actual time=0.2..4.1 rows=3200 loops=1)
            -> Single-row index lookup on u using PRIMARY (id=o.user_id)  (actual time=0.002..0.002 rows=1 loops=3200)

출력 해석 포인트:

• actual time=시작..끝 — 첫 번째 행을 꺼내는 시간과 마지막 행까지의 총 시간
• rows=N (actual) — 실제 처리된 행 수. EXPLAIN의 예측 rows와 비교
• loops=N — 해당 노드가 반복된 횟수 (중첩 루프 조인에서 중요)

💡 TIP actual rows가 EXPLAIN rows보다 수십 배 크다면 통계 정보가 오래된 것입니다. ANALYZE TABLE로 통계를 갱신하거나 히스토그램을 생성하세요.

-- 히스토그램 생성 (MySQL 8.0+)
ANALYZE TABLE orders UPDATE HISTOGRAM ON status, created_at WITH 100 BUCKETS;

-- 히스토그램 확인
SELECT * FROM information_schema.column_statistics
WHERE table_name = 'orders'\G

---

옵티마이저 트레이스(optimizer_trace)로 선택 근거 읽기

EXPLAIN만으로는 "왜 이 인덱스를 선택했는가"를 알 수 없습니다. optimizer_trace를 활성화하면 옵티마이저가 각 경로의 비용을 계산하고 최종 계획을 고른 전 과정을 JSON으로 볼 수 있습니다.

-- 트레이스 활성화 (세션 범위)
SET optimizer_trace = 'enabled=on';
SET optimizer_trace_max_mem_size = 1048576;  -- 1 MB

-- 분석할 쿼리 실행
SELECT * FROM orders
WHERE user_id = 42 AND status = 'shipped'
ORDER BY created_at DESC
LIMIT 10;

-- 트레이스 결과 조회
SELECT trace FROM information_schema.optimizer_trace\G

-- 트레이스 비활성화
SET optimizer_trace = 'enabled=off';

트레이스 JSON에서 주목할 섹션:

"considered_execution_plans": [
  {
    "plan_prefix": [],
    "table": "orders",
    "best_access_path": {
      "considered_access_paths": [
        {
          "access_type": "ref",
          "index": "idx_user_status",
          "rows": 120,
          "cost": 13.2,
          "chosen": true
        },
        {
          "access_type": "range",
          "index": "idx_created",
          "rows": 5000,
          "cost": 510.0,
          "chosen": false
        }
      ]
    }
  }
]

cost 값이 낮은 경로가 선택됩니다. 만약 예상과 다른 인덱스가 선택됐다면, 통계가 부정확하거나 인덱스 설계가 최적이 아닌 것입니다.

⚠️ 주의 optimizer_trace는 프로덕션 환경에서 세션 범위로만 활성화하세요. 전역 활성화는 CPU·메모리 오버헤드를 유발합니다.

---

조인 순서와 조인 알고리즘 이해

옵티마이저는 조인 대상 테이블의 순서와 알고리즘을 자동으로 선택합니다. 하지만 그 선택이 항상 최적은 아니므로 원리를 이해하고 직접 제어할 수 있어야 합니다.

Nested Loop Join (NLJ)

드라이빙 테이블의 각 행마다 내부 테이블을 인덱스로 조회합니다. 내부 테이블에 적합한 인덱스가 있을 때 최고 성능을 냅니다.

-- 드라이빙 테이블을 명시적으로 지정 (힌트)
SELECT /*+ JOIN_ORDER(o, u) */ o.id, u.name
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'pending';

Block Nested Loop (BNL) — MySQL 8.0.20 이전

인덱스가 없는 조인에서 드라이빙 테이블 행을 join_buffer_size 크기의 버퍼에 담아 한 번에 비교합니다. 버퍼가 클수록 내부 테이블 스캔 횟수가 줄어듭니다.

-- BNL 적용 여부 확인 (Extra: Using join buffer (Block Nested Loop))
EXPLAIN SELECT o.id, p.name
FROM orders o
JOIN products p ON p.category_id = o.category_id;  -- 인덱스 없을 때

-- join_buffer_size 조정 (세션)
SET join_buffer_size = 4 * 1024 * 1024;  -- 4 MB

Hash Join — MySQL 8.0.18+

BNL을 대체하는 알고리즘으로, 작은 테이블을 해시 테이블로 빌드한 뒤 큰 테이블을 스캔하며 프로브합니다. 등가 조인(=)에서만 사용됩니다.

-- Hash Join 강제 활성화 여부 확인
SELECT @@optimizer_switch LIKE '%hash_join=on%';

-- EXPLAIN에서 Hash Join 확인 (Extra: Using join buffer (hash join))
EXPLAIN FORMAT=TREE
SELECT o.id, p.name
FROM orders o
JOIN products p ON p.category_id = o.category_id;

💡 TIP MySQL 8.0.20 이후 BNL은 Hash Join으로 자동 대체됩니다. Extra에 Using join buffer (hash join)이 나타나면 정상입니다.

조인 순서 제어

-- ✅ 작은 결과 집합이 드라이버가 되도록 힌트
SELECT /*+ JOIN_ORDER(small_result, large_table) */
       s.id, l.value
FROM small_result s
JOIN large_table l ON l.ref_id = s.id;

-- ❌ 옵티마이저가 대형 테이블을 드라이버로 선택한 경우 강제 수정
SELECT /*+ JOIN_FIXED_ORDER() */ ...
FROM large_table l
STRAIGHT_JOIN small_result s ON s.id = l.ref_id;

---

서브쿼리 최적화: 세미조인과 머터리얼라이제이션

입문편에서 서브쿼리 작성법을 배웠다면, 여기서는 옵티마이저가 서브쿼리를 어떻게 변환하고 실행하는지를 다룹니다.

세미조인(Semijoin) 최적화

IN (SELECT ...) 패턴에서 옵티마이저는 서브쿼리를 세미조인으로 변환해 중복 계산을 제거합니다.

-- ❌ 비효율적: 상관 서브쿼리 (rows마다 서브쿼리 실행)
SELECT id, name FROM users u
WHERE id IN (
    SELECT user_id FROM orders
    WHERE amount > 100000
      AND created_at >= '2024-01-01'
);

-- ✅ 옵티마이저가 세미조인으로 자동 변환함 (EXPLAIN에서 FirstMatch 또는 Duplicate Weedout 확인)
-- 직접 JOIN으로 재작성하면 더 명확히 제어 가능
SELECT DISTINCT u.id, u.name
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.amount > 100000
  AND o.created_at >= '2024-01-01';

머터리얼라이제이션(Materialization)

서브쿼리 결과를 임시 테이블에 한 번 저장하고 재사용합니다. 서브쿼리가 독립적(비상관)이고 결과 집합이 중간 크기일 때 효율적입니다.

EXPLAIN SELECT u.name, sub.order_count
FROM users u
JOIN (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    GROUP BY user_id
) sub ON sub.user_id = u.id;
-- Extra: Using temporary (파생 테이블 머터리얼라이제이션)

EXPLAIN에서 select_type이 DERIVED인 행이 나타나면 파생 테이블이 구체화된 것입니다. 이 임시 테이블에 인덱스가 자동 생성되는지 여부는 EXPLAIN의 key 컬럼으로 확인합니다.

💡 TIP MySQL 8.0은 CTE(WITH)와 파생 테이블을 기본적으로 머터리얼라이즈(구체화)합니다. 같은 CTE를 쿼리 안에서 여러 번 참조해도 임시 테이블은 한 번만 만들어져 재사용되므로, 반복 계산을 피하고 싶을 때 유용합니다. 단순 파생 테이블은 derived_merge로 병합될 수 있고, 조건 푸시다운은 /*+ DERIVED_CONDITION_PUSHDOWN() */ 힌트로 제어할 수 있습니다.

---

슬로우 쿼리 로그와 pt-query-digest

슬로우 쿼리 로그 설정

-- 현재 설정 확인
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 런타임 활성화 (재시작 없이)
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;           -- 1초 이상 소요 쿼리 기록
SET GLOBAL log_queries_not_using_indexes = 'ON';  -- 인덱스 미사용 쿼리도 기록

my.cnf 영구 설정:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
min_examined_row_limit = 1000   # 1000행 미만 스캔은 제외

pt-query-digest로 패턴 분석

Percona Toolkit의 pt-query-digest는 슬로우 쿼리 로그에서 쿼리 패턴을 집계해 총 실행 시간·빈도·최악 케이스를 한눈에 보여 줍니다.

# 설치 (Ubuntu/Debian)
sudo apt-get install percona-toolkit

# 기본 분석 — 상위 10개 쿼리 패턴 출력
pt-query-digest /var/log/mysql/slow.log

# 특정 데이터베이스만 필터
pt-query-digest --filter '$event->{db} eq "myapp"' /var/log/mysql/slow.log

# 최근 1시간 로그만 분석
pt-query-digest --since 3600 /var/log/mysql/slow.log

# 특정 리포트 섹션만 출력 (--report-format 허용 값: rusage,date,hostname,files,header,profile,query_report,prepared)
pt-query-digest --report-format=profile,query_report \
    /var/log/mysql/slow.log > report.txt
# pt-query-digest는 HTML 출력을 지원하지 않으므로, HTML이 필요하면
# 위 텍스트 출력을 별도 도구로 후처리해야 합니다.

출력 예시 해석:

# Query 1: 42 QPS, 0.38x concurrency, ID 0xAB12...
# This item is included in the report because it matches --limit.
#              pct   total    min    max     avg     95%  stddev  median
# Count        18    5040
# Exec time    37%    870s    0s    12s   173ms   521ms   310ms    89ms
# Rows sent     3%   15120     0    100      3      8       9      1
# Rows examine 89% 2100000     0  10000    416    1500    812    200

Rows examine / Rows sent 비율이 클수록 쓸데없이 많은 행을 스캔하는 것입니다. 이 값이 100 이상이라면 즉각 인덱스를 검토해야 합니다.

---

페이지네이션 성능 문제와 키셋 페이징

OFFSET 기반 페이지네이션의 함정

-- ❌ 페이지가 뒤로 갈수록 점점 느려지는 전형적인 패턴
SELECT id, title, created_at
FROM articles
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000;

OFFSET 100000이면 MySQL은 인덱스로 100,020개를 읽은 뒤 앞 100,000개를 버립니다. 페이지가 뒤로 갈수록 처리 비용이 선형으로 증가합니다.

EXPLAIN ANALYZE로 확인하면 실제 rows가 LIMIT + OFFSET 전체임을 알 수 있습니다.

키셋(Keyset) 페이징으로 해결

마지막으로 본 행의 키를 기준점으로 사용해 항상 인덱스의 특정 위치부터 LIMIT만큼만 읽습니다.

-- ✅ 첫 페이지
SELECT id, title, created_at
FROM articles
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- ✅ 다음 페이지 — 이전 페이지 마지막 행의 (created_at, id)를 전달
SELECT id, title, created_at
FROM articles
WHERE (created_at, id) < ('2024-03-15 10:00:00', 9843)
ORDER BY created_at DESC, id DESC
LIMIT 20;

(created_at DESC, id DESC) 복합 인덱스가 있으면 항상 range 스캔으로 정확히 20행만 읽습니다. 페이지 위치와 무관하게 응답 시간이 일정합니다.

⚠️ 주의 키셋 페이징은 "특정 페이지 번호로 바로 이동"하는 UI에는 적합하지 않습니다. 무한 스크롤이나 "다음 페이지" 방식의 UI에 어울립니다.

대용량 OFFSET이 불가피한 경우 — 지연 조인

페이지 번호 UI를 유지해야 한다면, 지연 조인(Deferred Join)으로 불필요한 컬럼 로딩 비용을 줄입니다.

-- ❌ 모든 컬럼을 100,020행 읽고 버림
SELECT id, title, content, author_id, created_at
FROM articles
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000;

-- ✅ 지연 조인: 인덱스로 id만 100,020개 읽고 20개만 테이블 조인
SELECT a.id, a.title, a.content, a.author_id, a.created_at
FROM (
    SELECT id
    FROM articles
    ORDER BY created_at DESC
    LIMIT 20 OFFSET 100000
) AS paged
JOIN articles a ON a.id = paged.id;

서브쿼리는 커버링 인덱스(id, created_at)만으로 처리되므로 데이터 페이지 접근을 최소화합니다.

---

요약

• EXPLAIN의 type은 접근 방식의 비용을 나타내며, ALL·index는 즉각 개선 대상이다. rows와 함께 실제 스캔 규모를 판단해야 한다.
• EXPLAIN ANALYZE는 쿼리를 실제 실행해 예측과 실측의 차이를 드러내므로, 통계 오류와 느린 노드를 정확히 찾을 수 있다.
• optimizer_trace는 옵티마이저가 각 경로의 비용을 계산하고 실행 계획을 선택하는 근거를 JSON으로 보여 준다.
• 조인 알고리즘은 NLJ(인덱스 있을 때), Hash Join(인덱스 없는 등가 조인) 순으로 선택되며, 조인 힌트로 순서를 수동 제어할 수 있다.
• 슬로우 쿼리 로그와 pt-query-digest를 조합하면 프로덕션 환경에서 가장 비용이 큰 쿼리 패턴을 빠르게 식별할 수 있다.
• OFFSET 기반 페이지네이션은 페이지가 뒤로 갈수록 선형으로 느려진다. 키셋 페이징 또는 지연 조인으로 대체해야 한다.

---

연습문제

1. 아래 쿼리의 EXPLAIN 결과에 type: ALL, Extra: Using filesort가 나타납니다. 문제를 진단하고 개선하세요.

   SELECT user_id, SUM(amount) AS total
   FROM orders
   WHERE status = 'completed'
   GROUP BY user_id
   ORDER BY total DESC;
   

   힌트 (status, user_id) 또는 (user_id, status) 중 어느 순서가 이 쿼리에 더 적합한지 생각해 보세요.

2. 다음 서브쿼리를 포함한 쿼리를 EXPLAIN ANALYZE로 분석했을 때 loops=50000이 나타났습니다. 이 수치가 의미하는 문제를 설명하고 쿼리를 재작성하세요.

   SELECT id, name
   FROM products p
   WHERE price > (
       SELECT AVG(price)
       FROM products
       WHERE category_id = p.category_id
   );
   

   힌트 상관 서브쿼리를 파생 테이블(인라인 뷰) 또는 CTE로 분리하면 AVG를 한 번만 계산할 수 있습니다.

3. 10만 번째 페이지에 해당하는 데이터를 조회하는 쿼리가 응답하는 데 3초 이상 걸립니다. 현재 쿼리와 cursor 값이 주어졌을 때 키셋 페이징으로 변환하세요.

   -- 현재 쿼리
   SELECT id, title, view_count, published_at
   FROM posts
   ORDER BY published_at DESC, id DESC
   LIMIT 20 OFFSET 2000000;
   
   -- 이전 페이지 마지막 행: published_at = '2023-06-01 09:00:00', id = 55321
   

   힌트 (published_at, id) 조건을 행 값 비교로 작성하면 됩니다.

4. pt-query-digest 출력에서 특정 쿼리 패턴의 Rows examine 값이 Rows sent의 500배입니다. 이 현상의 원인을 두 가지 이상 설명하고 각 원인에 맞는 해결 방법을 제시하세요.

   힌트 인덱스 선택도(카디널리티)와 WHERE 절 컬럼의 인덱스 포함 여부를 함께 생각해 보세요.

---