동블

그래프 뷰

01. EXPLAIN 읽는 법 상세

10 min read

이 문서를 통해 아래 질문들에 답할 수 있게 됩니다.

  • EXPLAIN은 무엇을 보여주고 무엇을 보여주지 않는가?
  • 실행 계획에서 어떤 항목을 먼저 봐야 하는가?
  • 운영에서 EXPLAIN ANALYZE를 조심해야 하는 이유는 무엇인가?

개요

실행 계획은 DB가 SQL을 어떤 방식으로 실행할지 보여주는 설명서다. PostgreSQL의 EXPLAIN, MySQL의 EXPLAIN은 느린 쿼리 분석의 출발점이다.

백엔드 개발자는 실행 계획을 DBA만 보는 도구로 생각하면 안 된다. 실제 API 지연의 상당수는 ORM 코드가 아니라 DB가 많은 행을 읽고, 정렬하고, 조인하고, 락을 기다리는 데서 생긴다. 실행 계획을 읽을 수 있어야 “코드가 느리다”와 “쿼리 계획이 나쁘다”를 구분할 수 있다.

원리

SQL은 절차형 코드처럼 위에서 아래로 실행되는 명령이 아니다. 사용자는 원하는 결과를 선언하고, DB Optimizer가 통계와 비용 모델을 바탕으로 실행 방법을 고른다.

실행 계획은 보통 트리다. 위쪽 노드는 아래쪽 노드가 만든 row를 소비한다. 그래서 plan을 읽을 때는 “최종 결과가 어디서 나오는가”만 보지 말고, leaf scan이 몇 row를 만들고, join이 얼마나 불리고, sort가 어디서 발생하는지 아래에서 위로 따라가야 한다.

EXPLAIN
SELECT id, title, created_at
FROM posts
WHERE board_id = 10
ORDER BY created_at DESC
LIMIT 20;

이 결과에서 봐야 할 핵심은 다음이다.

  • 어떤 접근 방식을 쓰는가
  • 어떤 인덱스를 쓰는가
  • 예상 row 수는 얼마나 되는가
  • 정렬이나 임시 작업이 있는가
  • 조인은 어떤 순서와 방식으로 일어나는가

PostgreSQL에서는 실제 실행까지 포함하려면 다음처럼 쓴다.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, title, created_at
FROM posts
WHERE board_id = 10
ORDER BY created_at DESC
LIMIT 20;

ANALYZE는 실제로 쿼리를 실행한다. SELECT도 부하를 만들 수 있고, UPDATE, DELETE, INSERT는 실제 변경이 일어날 수 있으므로 운영에서는 트랜잭션으로 감싸거나 읽기 쿼리 위주로 조심해서 사용해야 한다.

PostgreSQL에서 DML을 분석해야 한다면 최소한 별도 세션, 낮은 트래픽 시간대, timeout, 명시적 rollback을 검토한다.

BEGIN;
SET LOCAL statement_timeout = '5s';
SET LOCAL lock_timeout = '1s';
 
EXPLAIN (ANALYZE, BUFFERS)
UPDATE orders
SET status = 'EXPIRED'
WHERE status = 'CREATED'
  AND created_at < timestamp '2026-01-01';
 
ROLLBACK;

이렇게 해도 쿼리는 실제로 실행되고 lock, WAL, trigger, constraint 비용이 발생할 수 있다. rollback은 데이터 반영을 되돌릴 뿐, 운영 부하가 없었다는 뜻이 아니다. 운영에서는 DBA와 함께 더 안전한 샘플링이나 staging 재현을 우선 검토한다.

PostgreSQL에서 먼저 볼 것

PostgreSQL 실행 계획에서는 다음 단어를 자주 본다.

  • Seq Scan: 테이블을 순차적으로 읽는다.
  • Index Scan: 인덱스로 찾은 뒤 테이블 행을 읽는다.
  • Index Only Scan: 인덱스만으로 필요한 값을 해결할 가능성이 있다.
  • Bitmap Index Scan: 여러 인덱스 결과나 넓은 범위를 비트맵으로 모아 읽는다.
  • Sort: 별도 정렬 작업이 있다.
  • Nested Loop, Hash Join, Merge Join: 조인 방식이다.

예를 들어 다음 계획은 목록 API에서 흔한 위험 신호다.

Seq Scan on posts
  Filter: (board_id = 10)
Sort
  Sort Key: created_at DESC

게시글이 많고 자주 호출되는 API라면 board_id로 좁히고 created_at 정렬을 지원하는 인덱스가 필요할 수 있다.

CREATE INDEX idx_posts_board_created_id
ON posts (board_id, created_at DESC, id DESC);

MySQL에서 먼저 볼 것

MySQL EXPLAIN에서는 type, key, rows, Extra를 먼저 본다.

EXPLAIN
SELECT id, title, created_at
FROM posts
WHERE board_id = 10
ORDER BY created_at DESC
LIMIT 20;

중요한 신호는 다음이다.

  • type이 ALL이면 전체 스캔 가능성이 있다.
  • key가 NULL이면 사용 인덱스가 없을 수 있다.
  • rows가 크면 많은 행을 읽을 수 있다.
  • Extra에 Using filesort, Using temporary가 보이면 정렬이나 임시 작업을 의심한다.

Using filesort가 항상 장애는 아니지만, 자주 호출되는 대용량 목록 API에서는 병목 후보가 된다.

MySQL 8.0의 EXPLAIN ANALYZE는 실제 실행 정보를 제공하지만, 지원 범위와 출력 형식은 버전에 따라 다르다. PostgreSQL과 항목 이름이 다르므로 같은 단어를 찾기보다 접근 방식, rows 추정, 실제 처리량, 정렬/임시 작업 여부를 비교한다.

실제 SQL 확보

실행 계획은 실제 SQL로 봐야 한다. ORM 코드나 Repository 메서드 이름으로 추측하면 틀릴 수 있다.

@Query("""
    select p
    from Post p
    where p.board.id = :boardId
    order by p.createdAt desc
""")
List<Post> findLatestPosts(@Param("boardId") Long boardId, Pageable pageable);

이 JPQL이 실제로 어떤 SQL이 되는지 확인해야 한다. 조인, select 컬럼, limit 적용 방식, count 쿼리는 ORM과 DB 방언에 따라 달라질 수 있다.

개발 환경에서는 SQL 로그를 켠다.

logging.level.org.hibernate.SQL=debug
logging.level.org.hibernate.orm.jdbc.bind=trace

운영에서는 로그 폭증과 민감정보 노출을 주의하고, APM, slow query log, query digest, pg_stat_statements 같은 도구를 활용한다.

실전 팁

  • 실행 계획은 실제 바인딩 값으로 본다. 값에 따라 선택도가 달라진다.
  • EXPLAIN의 예상 row와 EXPLAIN ANALYZE의 실제 row 차이가 크면 통계나 조건식을 의심한다.
  • 목록 API에서는 정렬과 limit이 인덱스로 해결되는지 먼저 본다.
  • 조인 쿼리는 조인 순서와 각 단계의 row 수를 본다.
  • 실행 계획 캡처는 PR 리뷰와 장애 회고에 남길 가치가 있다.
  • plan은 아래 scan 노드부터 위로 읽으면서 row가 어디서 늘고 줄어드는지 본다.
  • 운영 plan 공유에는 SQL, bind 값, DB 버전, 데이터량, 실행 시각, EXPLAIN 옵션을 함께 남긴다.
  • EXPLAIN ANALYZE 결과는 실제 실행이므로 장애 중에는 부하와 lock 위험을 먼저 따진다.

위험 신호!

  • 실행 계획 없이 인덱스를 추가한다.
  • EXPLAIN의 cost를 실제 시간으로 이해한다.
  • 개발 데이터 100건으로 운영 1억 건 테이블의 계획을 판단한다.
  • ORM이 생성한 SQL을 확인하지 않는다.
  • 운영에서 EXPLAIN ANALYZE로 쓰기 쿼리를 그대로 실행한다.
  • 실행 계획의 맨 위 total time만 보고 아래 노드의 rows 폭증 지점을 보지 않는다.
  • EXPLAIN을 캡처했지만 어떤 bind 값으로 실행했는지 남기지 않는다.

확인 질문

  • EXPLAIN과 EXPLAIN ANALYZE의 차이는 무엇인가?
    • EXPLAIN은 예상 계획을 보여주고, EXPLAIN ANALYZE는 실제로 실행해 실제 시간과 row 수를 보여준다.
  • 실행 계획에서 인덱스 이름만 보면 부족한 이유는 무엇인가?
    • 인덱스를 사용해도 읽은 행 수가 많거나 정렬, 임시 작업, 테이블 접근 비용이 클 수 있기 때문이다.
  • ORM을 사용할 때 실행 계획 분석의 첫 단계는 무엇인가?
    • 실제 DB에 전달된 SQL과 바인딩 값을 확보하는 것이다.
  • 실행 계획을 트리로 읽어야 하는 이유는 무엇인가?
    • 상위 노드는 하위 노드 결과를 소비하므로, 실제 병목은 leaf scan, join 중간 결과, sort, aggregate 같은 하위 단계에서 만들어질 수 있기 때문이다.

참고 문서

관련 문서