이 모듈은 MySQL 8.0 기준으로 작성됐습니다. PostgreSQL은 EXPLAIN ANALYZE와 EXPLAIN (ANALYZE, BUFFERS) 문법을 사용합니다.

🚨INCIDENT ALERT

HIGH

쿼리가 느릴 때 SQL 문장만 보고는 원인을 확정할 수 없습니다. DB가 실제로 어떤 순서로 테이블을 읽고 조인하는지 실행 계획을 봐야 합니다. EXPLAIN을 읽을 줄 알면 인덱스 추가와 쿼리 수정의 근거가 생깁니다.

이번 챕터에서 배울 것

인덱스를 만드는 것과 올바른 인덱스를 만드는 것은 완전히 다른 문제입니다. 실행 계획을 읽지 못하면 인덱스를 쌓아도 성능은 나아지지 않습니다. 이 모듈은 실행 계획의 모든 출력을 해석하고, 현장에서 실제로 통하는 인덱스 전략을 익히는 것을 목표로 합니다.

1EXPLAIN vs EXPLAIN ANALYZE — 차이와 올바른 사용 방법
2실행 계획 핵심 노드 — Seq Scan, Index Scan, Nested Loop, Hash Join
3인덱스 설계 원칙 — 선두 컬럼, ICP, Covering Index
4옵티마이저가 인덱스를 무시하는 6가지 패턴
5운영 중 인덱스 추가 — Online DDL, pt-online-schema-change
6슬로우 쿼리 로그 기반 체계적 개선 워크플로

쿼리 실행 계획 & 인덱스 최적화

2019년 블랙프라이데이, 국내 중견 이커머스 A사는 오전 10시 프로모션 시작과 동시에 DB CPU가 100%로 치솟았습니다. 주문 목록 API 평균 응답시간이 200ms에서 45초로 치솟았고, 30분 만에 주문 처리가 전면 중단됐습니다. 원인은 단 하나의 쿼리였습니다. WHERE status = 'pending' AND created_at > NOW() - INTERVAL 1 DAY를 처리하는 쿼리에 (status, created_at) 복합 인덱스 대신 status 단독 인덱스만 있었고, 신규 주문이 폭증하면서 그 단독 인덱스조차 옵티마이저가 무시하기 시작했습니다.

EXPLAIN ANALYZE를 알고 있었다면, 배포 전 5분만에 이 문제를 발견할 수 있었습니다.

실습 환경 준비

SQL

-- 실습용 샘플 스키마 및 데이터 생성
CREATE DATABASE ecommerce_bench;
USE ecommerce_bench;

CREATE TABLE orders (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id     BIGINT UNSIGNED NOT NULL,
  status      ENUM('pending','paid','shipped','cancelled') NOT NULL DEFAULT 'pending',
  total_amount DECIMAL(12,2) NOT NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE order_items (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id   BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  quantity   INT NOT NULL,
  unit_price DECIMAL(10,2) NOT NULL,
  INDEX (order_id),
  INDEX (product_id)
) ENGINE=InnoDB;

-- 100만 건 샘플 데이터 삽입 (저장 프로시저 활용)
DELIMITER $$
CREATE PROCEDURE generate_orders(IN n INT)
BEGIN
  DECLARE i INT DEFAULT 0;
  WHILE i < n DO
    INSERT INTO orders (user_id, status, total_amount, created_at)
    VALUES (
      FLOOR(1 + RAND() * 50000),
      ELT(FLOOR(1 + RAND() * 4), 'pending','paid','shipped','cancelled'),
      ROUND(10 + RAND() * 990, 2),
      NOW() - INTERVAL FLOOR(RAND() * 365) DAY
    );
    SET i = i + 1;
  END WHILE;
END$$
DELIMITER ;

CALL generate_orders(1000000);

OUTPUT

실행 완료 또는 조회 결과가 표시됩니다.

🔍실행 후 확인할 것

스캔 타입—Seq Scan, Index Scan, Bitmap Scan 중 무엇이 선택됐는지 확인합니다.
예상 행 수—planner rows와 실제 데이터 규모가 크게 어긋나지 않는지 봅니다.
병목 노드—가장 비용이 큰 정렬·조인·스캔 구간을 찾습니다.

DB 클라이언트

# 슬로우 쿼리 로그 설정 확인
mysql -u root -p -e "
  SET GLOBAL slow_query_log = 'ON';
  SET GLOBAL long_query_time = 0.5;
  SET GLOBAL log_queries_not_using_indexes = 'ON';
  SHOW VARIABLES LIKE 'slow_query_log_file';
"

💡개념

EXPLAIN ANALYZE — 실행 계획의 모든 것

쿼리가 느린데 인덱스가 있는 것 같습니다. 인덱스를 걸었는데 왜 빠르지 않은지 모릅니다. EXPLAIN을 실행해봤는데 수십 줄이 나오고 어디를 봐야 할지 막막합니다. 실행 계획을 읽을 수 있어야 인덱스가 실제로 쓰이는지, 어디서 비용이 크게 발생하는지 알 수 있습니다. EXPLAIN ANALYZE는 슬로우 쿼리를 고치는 첫 번째 도구입니다.

EXPLAIN vs EXPLAIN ANALYZE 차이

EXPLAIN은 쿼리를 실행하지 않고 옵티마이저가 예상하는 실행 계획을 보여줍니다. EXPLAIN ANALYZE는 실제로 쿼리를 실행하고 예상값과 실측값을 함께 출력합니다.

SQL

-- EXPLAIN: 예상 계획만 (쿼리 미실행)
EXPLAIN
SELECT * FROM orders
WHERE user_id = 12345
  AND status = 'pending'
ORDER BY created_at DESC
LIMIT 20;

+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-----------------------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra                       |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-----------------------------+
|  1 | SIMPLE      | orders | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 996312  |     1.00 | Using where; Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+---------+----------+-----------------------------+

이 출력에서 위험 신호들: type=ALL (Full Table Scan), key=NULL (인덱스 없음), rows=996312 (100만 행 스캔), Using filesort (추가 정렬 발생).

SQL

-- EXPLAIN ANALYZE: 실제 실행 + 실측값 (MySQL 8.0.18+)
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 12345
  AND status = 'pending'
ORDER BY created_at DESC
LIMIT 20\G

-> Limit: 20 row(s)  (actual time=4823.12..4823.13 rows=20 loops=1)
    -> Sort: orders.created_at DESC  (actual time=4823.11..4823.12 rows=20 loops=1)
        -> Filter: ((orders.user_id = 12345) and (orders.`status` = 'pending'))
           (actual time=0.08..4819.44 rows=18 loops=1)
            -> Table scan on orders
               (cost=101234.50 rows=996312) (actual time=0.06..3841.22 rows=1000000 loops=1)

actual time=4823ms — 4.8초가 걸렸습니다. 100만 행 전부를 읽은 다음 18개를 필터링했습니다.

핵심 컬럼 해석 가이드

컬럼	의미	위험 신호
`type`	접근 방식	`ALL` = Full Scan, 최악
`key`	실제 사용된 인덱스	`NULL` = 인덱스 없음
`rows`	예상 스캔 행 수	실제 행 수와 크게 다르면 통계 문제
`filtered`	조건 통과 비율(%)	낮을수록 많이 버림
`Extra`	추가 처리	`Using filesort`, `Using temporary` 주의

type 컬럼의 성능 순위 (좋은 순): system > const > eq_ref > ref > range > index > ALL

인덱스 추가 후 비교

SQL

-- 복합 인덱스 생성
CREATE INDEX idx_orders_user_status_created
  ON orders (user_id, status, created_at DESC);

-- 동일 쿼리 재실행
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 12345
  AND status = 'pending'
ORDER BY created_at DESC
LIMIT 20\G

-> Limit: 20 row(s)  (actual time=0.48..0.49 rows=20 loops=1)
    -> Index scan on orders using idx_orders_user_status_created
       (reverse)  (cost=0.35 rows=18) (actual time=0.46..0.47 rows=20 loops=1)

actual time=0.49ms. 4823ms에서 0.49ms로 9,800배 개선됐습니다.

💡개념

인덱스 설계 원칙 — 복합 인덱스와 Covering Index

status와 created_at 두 컬럼에 각각 인덱스를 만들었는데 복합 조건 쿼리에서 여전히 느립니다. 복합 인덱스로 바꿨는데 컬럼 순서를 잘못 정해서 범위 조건 이후의 인덱스가 무용지물이 됩니다. 인덱스를 만드는 것과 올바르게 설계하는 것은 다릅니다. 복합 인덱스의 컬럼 순서 원칙과 Covering Index를 알면 인덱스를 선택적이고 효율적으로 활용할 수 있습니다.

복합 인덱스 컬럼 순서 결정 원칙

복합 인덱스 (A, B, C) 설계 시 컬럼 순서는 성능에 결정적 영향을 미칩니다. 원칙은 등호(=) 조건 먼저, 범위(<, >, BETWEEN) 조건 나중, 정렬 컬럼 마지막입니다.

SQL

-- 나쁜 예: 범위 조건이 중간에 있어 그 이후 컬럼은 인덱스 미사용
-- (status, created_at, user_id) 인덱스로 아래 쿼리 실행 시
WHERE status = 'paid'          -- 등호: 인덱스 사용
  AND created_at > '2024-01-01' -- 범위: 인덱스 사용 (여기서 단절)
  AND user_id = 12345           -- 등호이지만 범위 뒤라 인덱스 미사용

-- 좋은 예: 등호 조건을 앞에 배치
-- (status, user_id, created_at) 인덱스
WHERE status = 'paid'          -- 등호: 인덱스 완전 활용
  AND user_id = 12345           -- 등호: 인덱스 완전 활용
  AND created_at > '2024-01-01' -- 범위: 인덱스 사용 (마지막이므로 OK)

Covering Index — 테이블 접근 자체를 없애기

일반 인덱스는 인덱스에서 행의 위치를 찾은 다음 테이블로 다시 접근(Random I/O)합니다. Covering Index는 쿼리에 필요한 모든 컬럼이 인덱스에 포함되어 있어 테이블 접근이 불필요합니다.

SQL

-- 이 쿼리는 user_id, status, created_at, total_amount를 사용
SELECT user_id, status, created_at, total_amount
FROM orders
WHERE user_id = 12345
ORDER BY created_at DESC
LIMIT 10;

-- 일반 인덱스: (user_id, created_at)
-- → 인덱스로 위치 찾기 + 테이블에서 status, total_amount 읽기 (Random I/O 발생)

-- Covering Index: (user_id, created_at, status, total_amount)
-- → 인덱스만으로 모든 데이터 제공, 테이블 접근 0회
CREATE INDEX idx_orders_covering
  ON orders (user_id, created_at DESC, status, total_amount);

EXPLAIN Extra 컬럼에서 Using index가 표시되면 Covering Index가 적용된 것입니다.

Index Condition Pushdown (ICP)

MySQL 5.6+의 ICP는 인덱스에서 WHERE 조건을 스토리지 엔진 레벨에서 먼저 평가해 불필요한 테이블 행 읽기를 줄입니다.

SQL

-- (user_id, created_at) 인덱스 존재, status 컬럼은 인덱스에 없음
EXPLAIN SELECT * FROM orders
WHERE user_id = 12345
  AND created_at > '2024-01-01'
  AND status = 'pending'; -- 인덱스에 없는 컬럼

Extra: Using index condition
-- ICP 활성화: 스토리지 엔진이 user_id, created_at 범위 내에서
-- status 조건을 인덱스 레벨에서 먼저 필터링
-- MySQL 서버 레이어로 올라오는 행 수 최소화

💡개념

옵티마이저가 인덱스를 무시하는 6가지 패턴

실무에서 "인덱스 만들었는데 왜 Full Scan이 나요?"라는 질문이 자주 나옵니다. 6가지 대표 패턴을 알면 바로 진단할 수 있습니다.

SQL

-- 패턴 1: 컬럼에 함수 적용
-- 인덱스: idx_created_at ON orders(created_at)
SELECT * FROM orders WHERE YEAR(created_at) = 2024;   -- 인덱스 무력화
SELECT * FROM orders WHERE DATE(created_at) = '2024-01-15'; -- 인덱스 무력화

-- 수정: 함수 없이 범위 조건으로
SELECT * FROM orders
WHERE created_at >= '2024-01-01'
  AND created_at < '2025-01-01';  -- 인덱스 사용 가능

-- 패턴 2: 암묵적 타입 변환
-- phone_number VARCHAR(20), 인덱스 존재
SELECT * FROM users WHERE phone_number = 01012345678; -- 숫자로 비교 → 타입 변환 → 인덱스 무력화
SELECT * FROM users WHERE phone_number = '01012345678'; -- 문자열로 정확히 비교

-- 패턴 3: OR 조건 (서로 다른 컬럼)
-- user_id와 email에 각각 인덱스가 있어도
SELECT * FROM orders WHERE user_id = 1 OR email = 'a@b.com'; -- Full Scan 가능
-- 수정: UNION ALL로 분리
SELECT * FROM orders WHERE user_id = 1
UNION ALL
SELECT * FROM orders WHERE email = 'a@b.com' AND user_id != 1;

-- 패턴 4: LIKE 앞 와일드카드
SELECT * FROM products WHERE name LIKE '%노트북%'; -- 인덱스 무력화 (풀 스캔)
SELECT * FROM products WHERE name LIKE '노트북%';  -- 앞에 와일드카드 없으면 인덱스 사용

-- 패턴 5: NULL 비교 (IS NULL은 인덱스 사용 가능, 설계에 따라 다름)
SELECT * FROM orders WHERE coupon_code != NULL;   -- 항상 False, 잘못된 쿼리
SELECT * FROM orders WHERE coupon_code IS NOT NULL; -- 인덱스 활용 가능

-- 패턴 6: 낮은 Cardinality + 대량 데이터
-- status 값이 4개뿐이고 'paid'가 전체의 70%인 경우
-- 옵티마이저가 스스로 Full Scan을 선택
SELECT * FROM orders WHERE status = 'paid'; -- 인덱스 무시될 수 있음
-- 강제로 인덱스 사용 시 (힌트, 테스트 목적)
SELECT * FROM orders USE INDEX (idx_status) WHERE status = 'paid';

실습 — 슬로우 쿼리 진단부터 개선까지

아래 시나리오는 실제 운영 환경에서 흔히 마주치는 패턴입니다.

SQL

-- Step 1: 슬로우 쿼리 식별
-- performance_schema로 가장 느린 쿼리 TOP 5 확인
SELECT
  DIGEST_TEXT,
  COUNT_STAR              AS exec_count,
  ROUND(AVG_TIMER_WAIT/1e9, 2) AS avg_ms,
  ROUND(MAX_TIMER_WAIT/1e9, 2) AS max_ms,
  ROUND(SUM_TIMER_WAIT/1e9, 2) AS total_ms
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME = 'ecommerce_bench'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 5;

+-------------------------------------------------------------------+------------+--------+--------+----------+
| DIGEST_TEXT                                                       | exec_count | avg_ms | max_ms | total_ms |
+-------------------------------------------------------------------+------------+--------+--------+----------+
| SELECT * FROM orders WHERE user_id = ? AND status = ? ORDER BY.. |       8432 |  286.4 | 4823.1 | 2415781  |
+-------------------------------------------------------------------+------------+--------+--------+----------+

SQL

-- Step 2: 대상 쿼리 실행 계획 분석
EXPLAIN ANALYZE
SELECT o.id, o.status, o.total_amount, o.created_at,
       COUNT(oi.id) AS item_count
FROM orders o
LEFT JOIN order_items oi ON o.id = oi.order_id
WHERE o.user_id = 12345
  AND o.status IN ('pending', 'paid')
ORDER BY o.created_at DESC
LIMIT 20\G

-- Step 3: 현재 인덱스 확인
SHOW INDEX FROM orders;
SHOW INDEX FROM order_items;

-- Step 4: 인덱스 전략 결정 및 적용
-- orders: (user_id, status, created_at) — 등호 조건인 user_id, status 먼저, 정렬 기준 created_at 마지막
CREATE INDEX idx_orders_user_status_time
  ON orders (user_id, status, created_at DESC);

-- order_items: order_id는 이미 있지만 Covering Index로 업그레이드
-- SELECT에서 order_id, id만 필요하므로 현재 인덱스로 충분

-- Step 5: 개선 확인
EXPLAIN ANALYZE
SELECT o.id, o.status, o.total_amount, o.created_at,
       COUNT(oi.id) AS item_count
FROM orders o
LEFT JOIN order_items oi ON o.id = oi.order_id
WHERE o.user_id = 12345
  AND o.status IN ('pending', 'paid')
ORDER BY o.created_at DESC
LIMIT 20\G

실행 결과:
개선 전: actual time=286ms  (평균), max 4823ms
개선 후: actual time=1.2ms  (평균), max 8.4ms
→ 239배 개선

⚠위험 명령어

운영 데이터에 적용하면 되돌리기 어려운 변경입니다. 실행 전 대상 테이블, WHERE 조건, 백업 또는 롤백 경로를 반드시 확인하세요.

SQL

-- Step 6: 운영 환경에서의 안전한 인덱스 추가 (서비스 중단 없이)
-- MySQL 8.0 Online DDL: ALGORITHM=INPLACE, LOCK=NONE
ALTER TABLE orders
  ADD INDEX idx_orders_user_status_time (user_id, status, created_at DESC),
  ALGORITHM=INPLACE,
  LOCK=NONE;

-- 인덱스 생성 진행 상황 모니터링
SELECT
  STAGE,
  EVENT_NAME,
  WORK_COMPLETED,
  WORK_ESTIMATED,
  ROUND(WORK_COMPLETED/WORK_ESTIMATED*100, 1) AS pct_done
FROM performance_schema.events_stages_current
WHERE EVENT_NAME LIKE '%alter%';

상황: 운영 중인 1억 건 테이블에 ALTER TABLE ... ADD INDEX를 실행했더니 잠시 후 The total number of locks exceeds the lock table size 에러가 발생하며 DDL이 실패했습니다.

원인: innodb_buffer_pool_size의 12.5%만큼 할당되는 innodb_lock_table이 가득 찼습니다. 대규모 ALTER 중 변경된 행마다 잠금 정보가 버퍼에 쌓이는데, 기본 설정으로는 행 수가 많을수록 금방 한계에 도달합니다.

SQL

-- 현재 lock table 크기 확인
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
-- lock table은 buffer_pool의 약 12.5%

-- 임시 해결: 세션 수준 innodb_lock_table 크기 조정은 불가
-- 서버 수준 설정 변경 필요 (재시작 불필요, 동적 변경 가능)
SET GLOBAL innodb_buffer_pool_size = 4 * 1024 * 1024 * 1024; -- 4GB로 증가

-- 근본 해결: pt-online-schema-change 사용
-- 내부적으로 트리거를 사용해 변경사항을 작은 배치로 처리
pt-online-schema-change \
  --alter "ADD INDEX idx_orders_user_status_time (user_id, status, created_at DESC)" \
  --chunk-size=5000 \
  --max-load="Threads_running=50" \
  --critical-load="Threads_running=100" \
  --execute \
  D=ecommerce_bench,t=orders

교훈: 1억 건 이상 테이블의 DDL은 반드시 pt-osc 또는 gh-ost를 사용합니다. --chunk-size와 --max-load 옵션으로 운영 영향도를 직접 제어할 수 있습니다.

상황: 배치 리포트 생성 쿼리가 정상적으로 실행되다가 어느 순간부터 이 에러로 중단됩니다.

원인: MySQL 8.0에서 max_execution_time (밀리초 단위)이 초과됐습니다. DBA가 최근 max_execution_time = 30000 (30초)을 글로벌 설정으로 추가했고, 데이터가 증가하면서 배치 쿼리가 30초를 넘기기 시작했습니다.

SQL

-- 현재 설정 확인
SHOW VARIABLES LIKE 'max_execution_time';

-- 해당 쿼리만 제한 해제 (쿼리 힌트 사용)
SELECT /*+ MAX_EXECUTION_TIME(0) */
  DATE(created_at) AS order_date,
  COUNT(*) AS total_orders,
  SUM(total_amount) AS revenue
FROM orders
WHERE created_at >= '2024-01-01'
GROUP BY DATE(created_at)
ORDER BY order_date;

-- 더 나은 해결: 인덱스 + 쿼리 최적화로 30초 안에 끝내기
-- created_at 인덱스 추가 및 GROUP BY 커버링 인덱스
CREATE INDEX idx_orders_created_amount
  ON orders (created_at, total_amount);
-- 이후 쿼리가 0.8초로 단축되어 제한 시간 내 완료

교훈: max_execution_time은 글로벌 설정보다 애플리케이션별로 세션 수준에서 관리하는 것이 안전합니다. 배치 쿼리용 DB 계정은 별도로 만들고 타임아웃을 다르게 설정하세요.

💼

실무 맥락

현업 패턴

실무 시나리오: 신규 기능 출시 전 DB 성능 리뷰

시니어 엔지니어라면 새 기능의 코드 리뷰 시 쿼리 성능을 함께 검토합니다. PR이 올라왔을 때 아래 체크리스트를 사용합니다.

새로 추가된 쿼리에 EXPLAIN 실행 — type=ALL, key=NULL이 없는지 확인
WHERE, JOIN ON, ORDER BY 컬럼에 적절한 인덱스가 있는지 확인
N+1 패턴 확인 — ORM이 루프 내에서 쿼리를 반복 실행하지 않는지
기존 테이블에 컬럼/인덱스 추가 시 Online DDL 가능 여부 확인
데이터 규모 예측 — 현재는 10만 건이지만 1년 후 1000만 건이 되면?

인터뷰 단골 질문: "슬로우 쿼리가 발생했을 때 어떻게 접근하시나요?" 모범 답변: slow_query_log 확인 → 대상 쿼리 EXPLAIN ANALYZE → 실행 계획에서 Full Scan / filesort / temporary 확인 → 인덱스 추가 또는 쿼리 재작성 → 개선 전후 성능 비교 및 문서화.

다음 모듈에서는 Master-Slave 복제 구축과 DB 고가용성(HA) 아키텍처를 다룹니다.

[Database] 쿼리 실행 계획(Execution Plan) 읽는 법과 인덱스 최적화