MySQL EXPLAIN ANALYZE 설명: 실행 계획 읽기 및 쿼리 최적화 (8.0 가이드)

1. 소개

실행 계획: 데이터베이스 성능 최적화에 필수적

웹 애플리케이션 및 비즈니스 시스템에서 데이터베이스 성능은 전체 응답 시간에 직접적인 영향을 미치는 중요한 요소입니다. 특히 MySQL을 사용할 때 “실행 계획”을 이해하는 것은 쿼리 효율성을 평가하는 데 필수적입니다. 기존 EXPLAIN 명령은 SQL 문을 실행하기 전에 실행 계획을 표시하며 오랫동안 개발자에게 유용한 인사이트를 제공해 왔습니다.

MySQL 8.0에 도입된 “EXPLAIN ANALYZE”

MySQL 8.0.18에 도입된 EXPLAIN ANALYZE는 기존 EXPLAIN을 강력하게 확장한 기능입니다. EXPLAIN이 “이론적인 계획”만 제공한다면, EXPLAIN ANALYZE는 실제로 쿼리를 실행하고 실행 시간 및 처리된 행 수와 같은 측정된 데이터를 반환합니다. 이를 통해 병목 현상을 보다 정확히 파악하고 쿼리 최적화 결과를 검증할 수 있습니다.

EXPLAIN ANALYZE가 중요한 이유

예를 들어, JOIN 순서, 인덱스 사용 여부, 필터링 조건은 실행 시간에 크게 영향을 미칩니다. EXPLAIN ANALYZE를 사용하면 SQL 문이 어떻게 수행되는지 시각적으로 확인하고 비효율이 존재하는 위치와 최적화해야 할 부분을 판단할 수 있습니다. 이는 대용량 데이터셋이나 복잡한 쿼리를 다룰 때 특히 필수적입니다.

이 글의 목적 및 대상 독자

이 글에서는 MySQL의 EXPLAIN ANALYZE 기본 개념부터 출력 해석 방법, 실전 최적화 기법 적용까지 모두 다룹니다. MySQL을 정기적으로 사용하는 개발자와 인프라 엔지니어, 그리고 성능 튜닝에 관심 있는 엔지니어를 대상으로 합니다. 초보자도 이해하기 쉽도록 용어 설명과 구체적인 예제를 곳곳에 포함했습니다.

2. EXPLAIN과 EXPLAIN ANALYZE의 차이점

EXPLAIN의 역할 및 기본 사용법

MySQL의 EXPLAIN은 SQL 문(특히 SELECT 문)이 사전에 어떻게 실행될지를 이해하기 위한 분석 도구입니다. 인덱스 사용 여부, 조인 순서, 검색 범위 등 실행 계획을 확인할 수 있게 해 줍니다.

예시:

EXPLAIN SELECT * FROM users WHERE age > 30;

이 명령을 실행하면 MySQL은 쿼리를 실제로 실행하지는 않으며, 대신 테이블 형태로 처리 계획을 표시합니다. 출력에는 사용된 인덱스(key), 접근 방식(type), 추정 행 수(rows)와 같은 정보가 포함됩니다.

EXPLAIN ANALYZE의 역할 및 특징

반면 MySQL 8.0.18에 도입된 EXPLAIN ANALYZE는 쿼리를 실제로 실행하고 측정된 값을 기반으로 실행 계획을 표시합니다. 이를 통해 기존 EXPLAIN에서는 볼 수 없었던 실제 처리 시간 및 실제 처리된 행 수와 같은 세부 정보를 확인할 수 있습니다.

예시:

EXPLAIN ANALYZE SELECT * FROM users WHERE age > 30;

이 명령은 쿼리를 실행하고 다음과 같은 정보를 포함한 출력을 반환합니다.

• 각 단계별 실행 시간(예: 0.0022 sec)
• 실제 읽은 행 수(rows)
• 처리 구조( TREE 형식으로 시각화하기 쉬움)

주요 차이점 요약

어느 것을 사용해야 할까?

• 쿼리 구조를 빠르게 확인하고 싶다면 EXPLAIN을 사용합니다.
• 실행 시간 및 쿼리 비용에 대한 구체적인 세부 정보가 필요하다면 EXPLAIN ANALYZE를 사용합니다.

특히 성능 튜닝 상황에서는 EXPLAIN ANALYZE가 추정값이 아닌 실제 실행 데이터를 기반으로 최적화를 가능하게 하여 매우 강력한 도구가 됩니다.

3. EXPLAIN ANALYZE의 출력 형식

세 가지 출력 형식: TRADITIONAL, JSON, TREE

MySQL의 EXPLAIN ANALYZE는 목적에 따라 결과를 다양한 형식으로 출력할 수 있습니다. MySQL 8.0 이후에는 다음 세 가지 형식이 제공됩니다.

각 형식의 차이를 자세히 살펴보겠습니다.

TRADITIONAL 형식 (기본)

TRADITIONAL 출력은 고전 EXPLAIN 스타일과 유사하며 익숙한 형태로 실행 계획을 검토할 수 있게 합니다. EXPLAIN ANALYZE를 형식 지정 없이 실행하면 결과는 일반적으로 이 형식으로 표시됩니다.

예시 출력 (발췌):

-> Filter: (age > 30)  (cost=0.35 rows=10) (actual time=0.002..0.004 rows=8 loops=1)

• cost : 추정 비용
• actual time : 측정된 시간
• rows : 처리된 행 수 추정치 (실행 전)
• loops : 루프 횟수 (특히 JOIN에 중요)

TRADITIONAL 형식은 사람이 스캔하고 이해하기 쉬워 초보자와 빠른 확인에 적합합니다.

JSON Format

JSON 형식은 더 자세하고 프로그래밍적으로 다루기 쉽습니다. 출력은 구조화되어 있으며 각 노드는 중첩 객체로 표현됩니다.

명령어:

EXPLAIN ANALYZE FORMAT=JSON SELECT * FROM users WHERE age > 30;

출력의 일부 (예쁘게 출력된 형태):

{
  "query_block": {
    "table": {
      "table_name": "users",
      "access_type": "range",
      "rows_examined_per_scan": 100,
      "actual_rows": 80,
      "filtered": 100,
      "cost_info": {
        "query_cost": "0.35"
      },
      "timing": {
        "start_time": 0.001,
        "end_time": 0.004
      }
    }
  }
}

이 형식은 시각적으로 읽기 어려울 수 있지만, 데이터를 파싱하여 분석 도구나 대시보드에 전달하려는 경우 매우 편리합니다.

TREE Format (Readable and Great for Visualizing Structure)

TREE 형식은 쿼리 실행 구조를 트리 형태로 표시하여 JOIN 및 서브쿼리 처리 순서를 이해하기 쉽게 합니다.

명령어:

EXPLAIN ANALYZE FORMAT=TREE SELECT * FROM users WHERE age > 30;

예시 출력 (단순화된 형태):

-> Table scan on users  (actual time=0.002..0.004 rows=8 loops=1)

복잡한 쿼리의 경우, 중첩이 다음과 같이 나타날 수 있습니다:

-> Nested loop join
    -> Table scan on users
    -> Index lookup on orders using idx_user_id

TREE 형식은 많은 JOIN이나 복잡한 중첩이 있는 쿼리에서 처리 흐름을 파악해야 할 때 특히 유용합니다.

Which Format Should You Use?

목표에 가장 적합한 형식을 선택하고, 가장 읽기 쉽고 분석 가능한 스타일로 실행 계획을 검토하세요.

4. How to Interpret Execution Plans

Why You Need to Read Execution Plans

MySQL 쿼리 성능은 데이터 양과 인덱스 가용성에 따라 크게 달라질 수 있습니다. EXPLAIN ANALYZE의 실행 계획 출력을 올바르게 해석함으로써 작업이 낭비되는 지점과 개선이 필요한 부분을 객관적으로 파악할 수 있습니다. 이 기술은 특히 대용량 데이터셋이나 복잡한 조인을 처리하는 쿼리의 성능 튜닝에 있어 핵심 요소입니다.

Basic Structure of an Execution Plan

EXPLAIN ANALYZE의 출력에는 다음과 같은 정보가 포함됩니다 (여기서는 TRADITIONAL 스타일 출력을 기준으로 설명합니다):

-> Filter: (age > 30)  (cost=0.35 rows=10) (actual time=0.002..0.004 rows=8 loops=1)

이 한 줄에는 여러 중요한 필드가 포함되어 있습니다.

How to Read Key Fields

1. cost vs. actual time

• cost는 MySQL이 계산한 내부 추정치이며 상대적 평가에 사용됩니다.
• actual time은 실제 경과 시간을 반영하며 성능 분석에 더 중요합니다.

예를 들어:

(cost=0.35 rows=100) (actual time=0.002..0.004 rows=100)

추정치와 측정값이 거의 일치하면 실행 계획이 정확할 가능성이 높습니다. 차이가 크게 벌어지면 테이블 통계가 부정확할 수 있습니다.

2. rows vs. actual rows

• rows는 MySQL이 읽을 것으로 예측한 행 수입니다.
• actual rows는 실제로 읽은 행 수이며 (TRADITIONAL 스타일 출력에서 괄호 안에 포함됩니다).

큰 차이가 있다면 통계를 새로 고치거나 인덱스 설계를 재검토해야 할 수 있습니다.

3. loops

If loops=1이면 단계가 한 번 실행됩니다. JOIN이나 서브쿼리를 사용할 경우 loops=10 또는 loops=1000과 같은 값을 볼 수 있습니다. 값이 클수록 중첩 루프가 무거운 처리를 일으키고 있을 가능성이 높습니다.

실행 계획의 중첩 구조 이해하기

여러 테이블을 조인하면 실행 계획이 트리 형태로 표시됩니다 (특히 TREE 형식에서 명확히 보입니다).

예시:

-> Nested loop join
    -> Table scan on users
    -> Table scan on orders

문제

• 두 테이블이 모두 전체 스캔되어 높은 조인 비용이 발생합니다.

대응책

• users.age에 인덱스를 추가하고, 조인 작업량을 줄이기 위해 더 일찍 필터링합니다.

성능 병목 현상을 식별하는 방법

다음 항목에 집중하면 병목 현상을 더 쉽게 찾을 수 있습니다:

• 실제 시간이 길고 행 수가 많은 노드 : 실행 시간의 대부분을 차지합니다
• 전체 테이블 스캔이 발생하는 위치 : 인덱스가 없거나 사용되지 않을 가능성이 높습니다
• 루프가 많은 단계 : 비효율적인 JOIN 순서 또는 중첩을 나타냅니다
• 예상 행 수와 실제 행 수 사이의 큰 차이 : 통계가 부정확하거나 과도한 데이터 접근을 의미합니다

다음 섹션에서 소개하는 “쿼리 최적화” 기법의 기반으로 이 인사이트를 활용하십시오.

5. 실용적인 쿼리 최적화 예시

쿼리 최적화란 무엇인가?

쿼리 최적화는 SQL 문을 검토하고 개선하여 보다 효율적으로 실행될 수 있도록 하는 것을 의미합니다. MySQL이 내부적으로 쿼리를 처리하는 방식(실행 계획)을 기반으로 인덱스 추가, 조인 순서 조정, 불필요한 처리 제거와 같은 개선을 적용합니다.

여기서는 EXPLAIN ANALYZE를 활용하여 구체적인 예시로 쿼리를 개선하는 방법을 보여줍니다.

예시 1: 인덱스를 활용한 속도 향상

최적화 전

SELECT * FROM users WHERE email = 'example@example.com';

실행 계획 (발췌)

-> Table scan on users  (cost=10.5 rows=100000) (actual time=0.001..0.230 rows=1 loops=1)

문제

• 출력에 Table scan이 표시되며, 이는 전체 테이블 스캔이 수행된다는 의미입니다. 대용량 데이터셋에서는 상당한 지연을 초래합니다.

해결책: 인덱스 추가

CREATE INDEX idx_email ON users(email);

최적화 후 실행 계획

-> Index lookup on users using idx_email  (cost=0.1 rows=1) (actual time=0.001..0.002 rows=1 loops=1)

결과

• 실행 시간이 크게 단축되었습니다.
• 인덱스를 사용하여 전체 테이블 스캔을 피했습니다.

예시 2: 조인 순서 최적화

최적화 전

SELECT * FROM orders
JOIN users ON orders.user_id = users.id
WHERE users.age > 30;

실행 계획 (발췌)

-> Nested loop join
    -> Table scan on orders
    -> Table scan on users

문제

• 두 테이블이 모두 전체 스캔되어 높은 조인 비용이 발생합니다.

해결책

• users.age에 인덱스를 추가하고, 먼저 필터링하여 조인 대상 크기를 줄입니다.

  CREATE INDEX idx_age ON users(age);
  

최적화 후 실행 계획

-> Nested loop join
    -> Index range scan on users using idx_age
    -> Index lookup on orders using idx_user_id

결과

• 조인 대상이 먼저 필터링되어 전체 처리 부하가 감소합니다.

예시 3: 서브쿼리 재작성

최적화 전

SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

문제

• 서브쿼리가 반복적으로 평가되어 성능이 저하될 수 있습니다.

해결책: JOIN으로 재작성

SELECT DISTINCT users.*
FROM users
JOIN orders ON users.id = orders.user_id
WHERE orders.total > 1000;

결과

• 실행 계획이 JOIN 처리에 최적화되어 인덱스가 사용될 가능성이 높아집니다.

전후 비교의 중요성

Using EXPLAIN ANALYZE, you can 실제 측정값으로 최적화 결과를 검증할 수 있습니다. 개선 전후의 실행 시간과 행 수를 비교함으로써 튜닝 작업이 가정이 아닌 실제 성능 향상에 기반함을 보장합니다.

최적화 시 중요한 고려사항

• 너무 많은 인덱스를 추가하면 역효과가 날 수 있습니다 (INSERT/UPDATE 성능 저하).
• 실행 계획은 데이터 양과 통계에 따라 달라집니다, 따라서 환경별 검증이 필요합니다.
• 하나의 최적화로 모든 문제가 해결되지는 않습니다. 병목 분석이 먼저입니다.

6. 주의사항 및 모범 사례

EXPLAIN ANALYZE 사용 시 중요한 주의사항

EXPLAIN ANALYZE는 매우 강력하지만, 부적절하게 사용하면 오해를 불러일으키거나 운영 위험을 초래할 수 있습니다. 다음 사항을 기억하면 안전하고 효과적인 쿼리 분석을 보장할 수 있습니다.

1. 프로덕션에서 무분별하게 실행하지 않기

EXPLAIN ANALYZE는 실제로 쿼리를 실행하므로, 수정문(INSERT/UPDATE/DELETE)과 함께 실수로 사용하면 데이터가 변경될 수 있습니다.

• 일반적으로 SELECT 문에만 사용합니다.
• 프로덕션보다 스테이징이나 테스트 환경에서 실행하는 것을 권장합니다.

2. 캐시 영향 고려하기

같은 쿼리를 반복 실행하면 MySQL이 캐시된 결과를 반환할 수 있습니다. 따라서 EXPLAIN ANALYZE가 보고하는 실행 시간이 실제 환경과 다를 수 있습니다.

대응 방안:

• 실행 전에 캐시를 비웁니다 (RESET QUERY CACHE;).
• 여러 번 실행하고 평균값을 기준으로 평가합니다.

3. 통계 최신 상태 유지

MySQL은 테이블 및 인덱스 통계를 기반으로 실행 계획을 수립합니다. 통계가 오래되면 EXPLAIN과 EXPLAIN ANALYZE 모두 오해를 일으킬 수 있습니다.

대량 INSERT 또는 DELETE 작업 후에는 ANALYZE TABLE을 사용해 통계를 업데이트하십시오.

ANALYZE TABLE users;

4. 인덱스가 만능은 아니다

인덱스는 성능을 향상시키지만, 너무 많은 인덱스는 쓰기 작업을 느리게 합니다.

복합 인덱스와 단일 컬럼 인덱스 중 선택도 중요합니다. 쿼리 패턴과 사용 빈도에 따라 인덱스를 신중히 설계하세요.

5. 실행 시간만으로 판단하지 말 것

EXPLAIN ANALYZE 결과는 단일 쿼리의 성능만을 반영합니다. 실제 애플리케이션에서는 네트워크 지연이나 백엔드 처리 등이 실제 병목일 수 있습니다.

따라서 전체 시스템 아키텍처를 고려하여 쿼리를 분석해야 합니다.

모범 사례 요약

7. 자주 묻는 질문 (FAQ)

Q1. EXPLAIN ANALYZE는 어느 버전부터 사용할 수 있나요?

A.
MySQL의 EXPLAIN ANALYZE는 버전 8.0.18 이후에 도입되었습니다. 8.0 이전 버전에서는 지원되지 않으므로 사용 전에 MySQL 버전을 확인해야 합니다.

Q2. EXPLAIN ANALYZE를 실행하면 데이터가 변경될 수 있나요?

A.
EXPLAIN ANALYZE는 내부적으로 쿼리를 실행합니다.
SELECT 문과 함께 사용할 경우 데이터가 변경되지 않습니다.

따라서 SELECT 문과 함께 사용할 경우 데이터가 변경되지 않습니다.

하지만 INSERT, UPDATE, DELETE와 함께 실수로 사용하면 일반 쿼리와 동일하게 데이터가 변경됩니다.

안전을 위해 프로덕션이 아닌 테스트 또는 스테이징 데이터베이스에서 분석을 실행하는 것이 권장됩니다.

Q3. EXPLAIN만으로 충분하지 않나요?

A.
EXPLAIN은 “예상” 실행 계획을 검토하는 데 충분합니다. 하지만 실제 실행 시간이나 실제 행 수와 같은 측정값은 제공하지 않습니다.

심도 있는 쿼리 튜닝이나 최적화 효과를 검증하려면 EXPLAIN ANALYZE가 더 유용합니다.

Q4. “loops”와 “actual time” 같은 값은 얼마나 정확한가요?

A.
actual time과 loops와 같은 값은 MySQL이 내부적으로 측정한 실제 실행 메트릭입니다. 다만 OS 상태, 캐시 상황, 서버 부하 등에 따라 약간 변동될 수 있습니다.

For this reason, do not rely on a single measurement. Instead, run the query multiple times and evaluate trends.

Q5. “cost”는 정확히 무엇을 나타내나요?

A.
cost는 MySQL 내부 비용 모델에 의해 계산된 추정값입니다. 이는 CPU 및 I/O 비용의 상대적 평가를 반영합니다. 초 단위로 표현되지 않습니다.

For example, if you see (cost=0.3) and (cost=2.5), the latter is estimated to be more expensive in relative terms.

예를 들어, (cost=0.3)와 (cost=2.5)를 보면, 후자는 상대적으로 더 높은 비용으로 추정됩니다.

Q6. JSON 또는 TREE 형식을 사용할 때의 장점은 무엇인가요?

A.

• JSON 형식 : 프로그래밍적으로 파싱하기 쉬운 구조화된 출력입니다. 자동화 도구와 대시보드에 유용합니다.
• TREE 형식 : 실행 흐름과 중첩을 시각적으로 명확하게 보여줍니다. 복잡한 쿼리와 JOIN 순서를 이해하는 데 이상적입니다.

Choose the format that best fits your purpose.

목적에 가장 적합한 형식을 선택하세요.

Q7. 실행 계획을 검토한 후에도 성능을 개선할 수 없을 때는 어떻게 해야 하나요?

A.
Consider additional approaches such as:

다음과 같은 추가 접근 방식을 고려해 보세요:

• 인덱스 재설계 (복합 인덱스 또는 커버링 인덱스)
• 쿼리 재작성 (서브쿼리 → JOIN, 불필요한 SELECT 컬럼 제거)
• 뷰 또는 임시 테이블 사용
• MySQL 설정 검토 (버퍼 크기, 메모리 할당 등)

Performance tuning rarely succeeds with a single technique. A comprehensive and iterative approach is essential.

성능 튜닝은 단일 기술만으로 성공하기 어렵습니다. 포괄적이고 반복적인 접근이 필수적입니다.