MySQL EXPLAIN 완전 정복: 실행 계획 읽는 방법과 SQL 쿼리 최적화

1. MySQL EXPLAIN이란? 왜 사용해야 할까요?

EXPLAIN이란? 실행 계획을 시각화하는 명령

MySQL에서 EXPLAIN은 SQL 쿼리가 어떻게 실행되는지를 시각화하는 명령입니다. 특히 SELECT 문에서 데이터가 어떻게 조회되는지를 이해하는 데 도움이 되며, 쿼리의 실행 계획을 표시합니다.

예를 들어 SELECT * FROM users WHERE age > 30 와 같은 쿼리를 실행할 때, EXPLAIN은 MySQL이 어떤 인덱스를 사용하고 테이블을 어떤 순서로 스캔하는지와 같은 내부 세부 정보를 보여줍니다.

사용법은 간단합니다 — 쿼리 앞에 EXPLAIN을 추가하기만 하면 됩니다.

EXPLAIN SELECT * FROM users WHERE age > 30;

이와 같이 작성하면 쿼리 실행 계획을 설명하는 여러 컬럼이 표시됩니다. 각 항목은 다음 섹션에서 자세히 설명됩니다.

왜 사용해야 할까요: 느린 쿼리 원인을 가시화하기

많은 개발자가 “SQL이 동작하면 문제 없다고” 가정하는 흔한 실수가 있습니다. 그러나 느린 쿼리 실행은 전체 애플리케이션 성능에 부정적인 영향을 미칠 수 있습니다.

대용량 데이터를 처리하는 시스템에서는 단 하나의 비효율적인 쿼리라도 병목이 되어 서버에 큰 부하를 줄 수 있습니다.

이때 EXPLAIN이 매우 유용합니다. 실행 계획을 검토하면 전체 테이블 스캔이 수행되는지, 인덱스가 제대로 활용되는지를 명확히 확인할 수 있습니다.

즉, EXPLAIN을 사용하면 성능 병목을 식별하고 최적화 방법을 결정할 수 있습니다. 특히 인덱스 효율성은 EXPLAIN 출력 분석을 통해 훨씬 명확해집니다.

EXPLAIN이 지원하는 SQL 문 (SELECT, UPDATE 등)

EXPLAIN은 SELECT 문뿐만 아니라 다음과 같은 SQL 문에서도 사용할 수 있습니다:

• SELECT
• DELETE
• INSERT
• REPLACE
• UPDATE

예를 들어 대용량 데이터셋에 대해 DELETE 문을 실행할 때 인덱스가 제대로 사용되지 않으면 MySQL이 전체 테이블 스캔을 수행하여 실행 시간이 크게 늘어날 수 있습니다. 이러한 문제를 방지하려면 DELETE 또는 UPDATE 문을 실행하기 전에 EXPLAIN으로 실행 계획을 확인하는 것이 매우 효과적입니다.

MySQL 버전에 따라 EXPLAIN ANALYZE를 사용할 수도 있으며, 이는 보다 상세한 실행 정보를 제공합니다. 이 내용은 기사 후반부에서 다룹니다.

2. EXPLAIN 출력 컬럼 이해하기 (이미지 포함)

기본 출력 컬럼 목록 및 설명

EXPLAIN 출력에는 다음과 같은 컬럼이 포함됩니다( MySQL 버전에 따라 약간 차이가 있음):

이 중 성능 튜닝에 가장 중요한 네 가지 컬럼은 type / key / rows / Extra 입니다.

네 가지 핵심 컬럼 읽는 방법: type / key / rows / Extra

1. type (접근 방법)

이 컬럼은 MySQL이 테이블에 접근하는 방식을 나타냅니다. 성능에 직접적인 영향을 미칩니다.

type = ALL이면 인덱스를 사용하지 않고 모든 행을 스캔한다는 의미이며, 가장 느린 접근 방법입니다. 이상적으로는 ref 또는 const 쪽으로 쿼리를 최적화해야 합니다.

2. key (사용된 인덱스)

이 컬럼은 실제로 사용된 인덱스의 이름을 표시합니다.
값이 표시되지 않으면 쿼리가 인덱스를 사용하지 않을 가능성이 높습니다.

3. rows (예상 스캔 행 수)

MySQL이 스캔할 것으로 예상하는 행 수를 보여줍니다. 숫자가 클수록 실행 시간이 길어지는 경향이 있습니다. 목표는 rows 값을 가능한 1에 가깝게 만드는 것입니다.

4. Extra (추가 정보)

Extra 컬럼에는 정렬 작업이나 임시 테이블 사용과 같은 추가 세부 정보가 포함됩니다.

Using temporary 또는 Using filesort가 표시되면 SQL 문이나 인덱스 설계를 다시 검토해야 합니다.

[Illustration] 샘플 EXPLAIN 출력

EXPLAIN SELECT * FROM users WHERE age > 30;

이 예시에서는 인덱스(age_index)가 존재하지만 실제로 사용되지 않아 ALL(전체 테이블 스캔)이 발생했습니다. 이는 최적화 여지가 있음을 나타냅니다.

3. 예제로 배우기: EXPLAIN 사용법과 결과 해석

예제 1: 간단한 SELECT 쿼리에 대한 EXPLAIN 출력 (설명 포함)

단일 테이블에 대한 간단한 SELECT 쿼리로 시작해 보겠습니다.

EXPLAIN SELECT * FROM users WHERE age > 30;

EXPLAIN 출력이 다음과 같다고 가정해 보겠습니다:

설명:

• type: ALL → 전체 테이블 스캔. 인덱스가 사용되지 않음.
• key: NULL → 실제로 인덱스가 사용되지 않음.
• rows: 5000 → MySQL은 약 5,000행을 스캔할 것으로 추정합니다.

개선 방법:

age 열에 인덱스를 추가하면 쿼리 성능을 크게 향상시킬 수 있습니다.

CREATE INDEX idx_age ON users(age);

EXPLAIN을 다시 실행하면 type이 range 또는 ref로 변경되는 것을 볼 수 있으며, 이는 인덱스가 이제 사용되고 있음을 확인합니다.

예제 2: JOIN이 포함된 쿼리에 대한 EXPLAIN 출력 분석

다음으로, 여러 테이블을 JOIN하는 예를 살펴보겠습니다.

EXPLAIN
SELECT orders.id, users.name
FROM orders
JOIN users ON orders.user_id = users.id
WHERE users.age > 30;

예제 출력:

설명:

• users 테이블이 전체 스캔( ALL )을 수행하고 있으므로, 이 부분을 개선해야 합니다.
• 반면, orders 테이블은 ref로 인덱스를 사용하며, 이는 효율적입니다.

최적화 포인트:

• users.age에 인덱스를 추가하면 users 테이블 스캔을 가속화할 수 있습니다.
• 핵심은 JOIN 전에 WHERE 절이 행을 필터링할 수 있도록 인덱스를 설계하는 것입니다.

인덱스가 사용되지 않을 때 (나쁜 예제 → 좋은 예제)

나쁜 예제: 함수를 사용하는 WHERE 절

SELECT * FROM users WHERE DATE(created_at) = '2024-01-01';

이러한 쿼리에서는 DATE() 함수가 열 값을 변환하여 MySQL이 인덱스를 효율적으로 사용할 수 없게 되므로 인덱스가 사용 불가능해집니다.

개선된 예제: 함수 없이 범위 지정

SELECT * FROM users
WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02';

이것은 created_at 열의 인덱스를 활성화하여 MySQL이 데이터를 효율적으로 검색할 수 있게 합니다.

결론: 성능 진단을 위한 실제 EXPLAIN 예제 사용

실제 쿼리의 EXPLAIN 출력을 분석함으로써 병목 현상이 어디에 있는지와 이를 최적화하는 방법을 명확히 식별할 수 있습니다.

• ALL → 전체 스캔. 인덱스 추가 또는 조정 고려.
• key = NULL → 인덱스 미사용. 조사 필요.
• Extra에 Using temporary 포함 → 성능 경고.
• 조건에서 함수나 계산을 사용하면 인덱스 사용이 비활성화될 수 있습니다.

이러한 점을 염두에 두면 EXPLAIN을 사용하여 쿼리 성능을 지속적으로 개선할 수 있습니다.

4. EXPLAIN 결과에 기반한 실용적인 쿼리 최적화 기법

“type: ALL”을 피하기 위한 인덱스 설계 기본

EXPLAIN에 type: ALL이 표시되면 MySQL이 전체 테이블 스캔을 수행하고 있음을 의미합니다. 이는 매우 비용이 많이 드는 작업이며, 수천에서 수백만 행을 포함하는 테이블에서 주요 병목 현상이 됩니다.

피하는 방법:

• WHERE 절에서 사용되는 열에 인덱스 추가

  CREATE INDEX idx_age ON users(age);
  

• 여러 조건이 있는 경우 복합 인덱스 고려

  CREATE INDEX idx_status_created ON orders(status, created_at);
  

• 접두사로 시작하지 않는 LIKE 패턴 피하기

  -- Bad example (index won’t work)
  WHERE name LIKE '%tanaka%'
  
  -- Good example (index may work)
  WHERE name LIKE 'tanaka%'
  

“Extra: Using temporary”의 의미와 수정 방법

Extra 열에 “Using temporary”가 표시되면 MySQL이 쿼리를 처리하기 위해 내부적으로 임시 테이블을 생성하고 있음을 의미합니다. 이는 GROUP BY나 ORDER BY와 같은 작업이 인덱스만으로는 처리할 수 없을 때 자주 발생하며, MySQL이 데이터를 수동으로 정리하기 위해 임시 저장소를 사용해야 합니다.

수정 방법:

• GROUP BY와 ORDER BY에서 사용되는 열에 인덱스 적용

  CREATE INDEX idx_group_col ON sales(department_id);
  

• 불필요한 정렬이나 GROUP BY를 제거하세요
• LIMIT 또는 서브쿼리를 사용해 대상 데이터를 줄이세요

“rows”와 “key”가 알려주는 성능 개선 방법 이해

rows 열은 MySQL이 테이블에서 읽어야 할 것으로 예측하는 행 수를 나타냅니다. 예를 들어 rows = 100000이라고 표시된 쿼리는 성능에 큰 영향을 미칠 수 있습니다.

값이 크게 나오면 스캔되는 행 수를 줄이는 인덱스를 적용하거나 조건을 다시 작성해야 할 가능성이 높습니다.

반면 key 열은 실제로 사용된 인덱스를 보여줍니다. NULL이면 인덱스가 전혀 사용되지 않고 있다는 경고입니다.

최적화 체크리스트:

• rows가 큰 경우 → 필터가 효과적인가? 인덱스가 제대로 사용되고 있는가?
• key = NULL인 경우 → WHERE/JOIN 절에 인덱스 사용을 방해하는 패턴이 있는가?

EXPLAIN과 최적화를 습관으로 만들기

쿼리를 효과적으로 튜닝하려면 기본 접근 방식은 다음 사이클을 반복하는 것입니다: 작성 → EXPLAIN으로 확인 → 개선 → 다시 확인.

이 워크플로우를 기억하세요:

1. 쿼리를 일반적으로 작성
2. EXPLAIN으로 실행 계획 확인
3. type, key, rows, Extra 검토
4. 병목 현상이 있으면 인덱스를 수정하거나 쿼리를 다시 작성
5. 개선 여부를 확인하기 위해 EXPLAIN을 다시 실행

쿼리 성능은 인덱스뿐만 아니라 쿼리 자체가 어떻게 작성되었는가에 의해 좌우됩니다. 함수 대신 단순 비교를 사용하고 조건을 직관적으로 작성하면 놀라울 정도로 효과적일 수 있습니다.

5. MySQL Workbench Visual EXPLAIN을 활용한 시각적 분석

GUI 도구로 실행 계획을 시각적으로 확인하기

MySQL Workbench는 MySQL 관리 및 개발에 특화된 GUI 도구입니다. 가장 큰 장점 중 하나는 시각적으로 실행 계획을 표시할 수 있다는 점이며, 이는 터미널 출력에서 읽기 어려운 경우가 많습니다.

Visual EXPLAIN을 사용하면 트리 구조로 다음 정보를 검토할 수 있습니다:

• 각 테이블의 접근 순서
• 사용된 JOIN 유형
• 인덱스 사용 현황
• 전체 테이블 스캔 여부
• 데이터 필터링 및 정렬 작업

계획이 그래픽으로 표시되기 때문에 초보자도 성능 병목이 어디에 있는지 더 쉽게 파악할 수 있습니다.

[With Images] Visual EXPLAIN 사용 및 읽는 방법 (단계별)

Visual EXPLAIN을 사용하기 위한 단계는 다음과 같습니다:

1. MySQL Workbench를 실행하고 데이터베이스 연결을 엽니다 → 연결이 미리 설정되어 있는지 확인합니다.
2. SQL 편집기에 대상 쿼리를 입력합니다

   SELECT * FROM users WHERE age > 30;
   

3. EXPLAIN 버튼 옆에 있는 “EXPLAIN VISUAL” 아이콘을 클릭합니다 → 또는 오른쪽 클릭 후 메뉴에서 “Visual Explain”을 선택합니다.
4. 실행 계획이 시각적으로 표시됩니다 각 노드(테이블)를 클릭하면 다음과 같은 상세 정보가 나타납니다:

• 접근 방식 (ALL, ref, range 등)
• 사용된 인덱스
• 추정 행 수 (rows)
• 필터 조건 및 JOIN 방식

Note:
Visual EXPLAIN에서는 노드 색상과 아이콘이 무거운 연산이나 비효율적인 부분을 강조합니다.
특히 빨간색으로 표시된 노드에 주의하세요. 일반적으로 성능 문제가 있음을 나타냅니다.

초보자도 쉽게 병목을 찾을 수 있음

텍스트 기반 EXPLAIN 출력은 처음에 압도적으로 느껴질 수 있지만 Visual EXPLAIN은 문제 영역을 시각적으로 부각시켜 줍니다.

예를 들어 다음을 쉽게 식별할 수 있습니다:

• type: ALL을 사용하는 테이블
• Using temporary가 표시된 쿼리 블록
• 불필요한 JOIN 패턴
• 인덱스가 사용되지 않는 테이블

GUI 인터페이스 덕분에 최적화 가설을 빠르게 세울 수 있으며, 팀 내에서 SQL 성능을 공유하고 검토하는 데도 유용합니다.

Visual EXPLAIN은 초급에서 중급 SQL 사용자에게 특히 가치가 높습니다.
EXPLAIN 결과 해석이 어려운 경우 이 기능을 활용해 보세요.

6. Frequently Asked Questions (FAQ)

Q1. 언제 EXPLAIN을 사용해야 하나요?

A. 쿼리 실행 속도가 확신이 서지 않을 때, 특히 쿼리가 “느리게 느껴질” 때는 EXPLAIN을 사용해야 합니다. 또한 새로 만든 쿼리가 인덱스를 올바르게 사용하고 있는지 확인할 때도 유용합니다.

By checking the execution plan before deployment, you can identify performance risks early.

Q2. The output shows type = ALL. What should I do?

A. type: ALL은 MySQL이 전체 테이블 스캔을 수행하고 있음을 의미합니다. 이는 비용이 높은 작업이며 특히 대용량 테이블에서 성능을 크게 저하시킬 수 있습니다.

Consider the following actions:

• WHERE 절에 사용되는 컬럼에 인덱스를 추가
• 인덱스 사용을 방해하는 함수나 연산을 피함
• SELECT *를 피하고 필요한 컬럼만 조회

Q3. Is “Using temporary” in the Extra column a problem?

A. Using temporary는 MySQL이 쿼리를 처리하기 위해 내부적으로 임시 테이블을 생성하고 있음을 나타냅니다. 이는 주로 GROUP BY 또는 ORDER BY와 함께 발생하며 메모리 및 디스크 I/O 비용을 증가시킬 수 있습니다.

Possible solutions include:

• GROUP BY / ORDER BY에 사용되는 컬럼에 인덱스를 추가
• 불필요한 정렬이나 집계를 줄임
• LIMIT 또는 서브쿼리를 사용해 데이터 양을 감소

Q4. How do I use Visual EXPLAIN?

A. 공식 MySQL 도구인 “MySQL Workbench”를 사용하면 GUI에서 EXPLAIN 결과를 손쉽게 시각화할 수 있습니다. 쿼리를 입력하고 “Visual Explain” 버튼을 클릭하면 됩니다.

This is particularly recommended for:

• 텍스트 기반 EXPLAIN 출력이 읽기 어려운 사용자
• 복잡한 JOIN을 시각적으로 이해하고 싶은 사람
• 팀 단위로 SQL 성능을 검토하는 경우

Q5. Why isn’t my index being used even though it exists?

A. 인덱스가 존재하더라도 MySQL이 항상 사용하는 것은 아닙니다. 다음과 같은 경우 인덱스가 무시될 수 있습니다:

• WHERE 절에 함수나 표현식을 사용함 (예: WHERE YEAR(created_at) = 2024 )
• 카디널리티가 낮아 전체 스캔이 더 빠르다고 판단되는 경우
• 컬럼 순서가 복합 인덱스 정의와 일치하지 않는 경우

To confirm whether an index is being used correctly, always check the key column in EXPLAIN.

7. Summary: Use EXPLAIN to Discover SQL Optimization Opportunities

Performance tuning in MySQL is not just about adding indexes.
The essential tool for identifying which queries are bottlenecks, why they are slow, and how to fix them is EXPLAIN.

In this article, we covered the following key points:

✅ The Role and Basic Usage of EXPLAIN

• 쿼리 앞에 EXPLAIN을 간단히 추가하면 실행 계획을 확인할 수 있습니다
• 전체 스캔(ALL)이나 Using temporary와 같은 문제를 확인할 수 있습니다

✅ How to Read Output Columns and Evaluate Performance

• 가장 중요한 네 컬럼은 type, key, rows, Extra입니다
• 전체 테이블 스캔을 피하고 적절한 인덱스 활용을 목표로 합니다
• Using temporary 또는 Using filesort가 보이면 주의하십시오

✅ Practical Diagnosis and Optimization Through Real Examples

• 인덱스를 추가하는 것뿐만 아니라 SQL 문법을 개선하는 것도 중요합니다
• JOIN이나 서브쿼리가 포함된 복잡한 쿼리도 EXPLAIN으로 분석할 수 있습니다
• 실행 계획을 기반으로 쿼리를 지속적으로 다듬는 것이 성능을 향상시키는 가장 빠른 방법입니다

✅ Use GUI Tools for Visual Confirmation

• MySQL Workbench의 “Visual EXPLAIN”을 사용해 실행 계획을 그래픽으로 확인하세요
• 초보자가 병목을 시각적으로 파악하기 쉬워집니다
• 팀 토론 및 SQL 성능 리뷰에 유용합니다

✅ FAQ Coverage for Real-World Scenarios

• type=ALL 및 key=NULL과 같은 문제의 원인과 해결책을 설명했습니다
• 인덱스가 사용되지 않을 수 있는 이유에 대한 예시를 제공했습니다

✍️ Make EXPLAIN a Habit to Improve Your SQL Skills

SQL을 작성할 때마다 EXPLAIN으로 쿼리를 확인하는 습관을 들이면 자연스럽게 더 빠르고 효율적인 쿼리를 작성하게 됩니다.

This is not just a technical trick — it is part of developing professional SQL literacy.

• 쿼리를 작성한 직후에 EXPLAIN을 실행하세요
• 의심스러운 실행 계획을 즉시 수정하세요
• 효율적인 인덱스를 신중하게 설계하세요

이 사이클을 마스터하면 MySQL 실력이 꾸준히 향상됩니다.

이 글이 더 나은 쿼리 최적화를 위한 첫 걸음이 되길 바랍니다.

질문이 있거나 추가로 다루었으면 하는 주제가 있다면, 언제든지 댓글을 남겨 주세요!