[DB Optimization#9] 실행 계획(Execution Plan)이란?

실행 계획이란?

MySQL에서 SQL문을 실행하면, 옵티마이저(optimizer)가 쿼리를 가장 효율적으로 수행할 수 있는 방식으로 실행 계획을 수립합니다.
이때 생성되는 실행 경로를 실행 계획(Execution Plan)이라고 합니다.

실행 계획을 분석하면 다음과 같은 내용을 파악할 수 있습니다.

• 어떤 인덱스를 사용하는지
• 얼마나 많은 데이터를 읽는지
• 테이블을 어떤 방식으로 접근하는지

이러한 실행 계획을 확인하면, 비효율적인 쿼리를 진단하고 개선할 수 있는 기회가 생깁니다.

 

A Visual Explain Example 출처: https://dev.mysql.com/doc/workbench/en/wb-performance-explain.html

 

실행 계획은 아래와 같은 코드를 통해 확인할 수 있습니다.

-- 기본 실행 계획 조회
EXPLAIN [SQL문];

-- 실제 실행 시간까지 포함해서 확인 (MySQL 8.0 이상)
EXPLAIN ANALYZE [SQL문];

 

예시 테이블을 생성 후 확인해보겠습니다.

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

INSERT INTO users (name, age) VALUES
('박미나', 26),
('김미현', 23),
('김민재', 21),
('이재현', 24),
('조민규', 23),
('하재원', 22),
('최지우', 22);

EXPLAIN SELECT * 
FROM users 
WHERE age = 23;

 

결과로 출력되는 실행 계획은 아래와 같이 여러 컬럼을 포함하게 됩니다.

이때, 주요하게 살펴봐야할 항목은 아래와 같습니다.

항목	설명
type	테이블을 어떤 방식으로 탐색했는지 (ALL, index, const 등)
possible_keys	사용할 수 있는 인덱스 목록
key	실제 사용된 인덱스
rows	MySQL이 예측한 접근 행 수
Extra	추가 정보 (e.g. Using index, Using where 등)

 

더불어, EXPLAIN ANALYZE를 통해 실제 실행 시간을 확인할 수 있습니다.

EXPLAIN ANALYZE SELECT * 
FROM users 
WHERE age = 23;

-> Filter: (users.age = 23)  
   (cost=0.95 rows=1) 
   (actual time=0.0599..0.0656 rows=2 loops=1)

    -> Table scan on users  
       (cost=0.95 rows=7) 
       (actual time=0.0536..0.0605 rows=7 loops=1)

 

이러한 결과는 맨 마지막 줄부터 위로 순서대로 읽어보면 되는데 위 결과는 아래와 같이 해석할 수 있습니다.

1. Table scan on users (cost=0.95 rows=7) (actual time=0.0536..0.0605 rows=7 loops=1)
   • Table scan on users: users 테이블을 처음부터 끝까지 전부 탐색하는 방식으로 데이터를 읽었습니다. 이는 인덱스를 사용하지 않은 Full Table Scan입니다.
   • cost=0.95 rows=7: 옵티마이저가 예측한 실행 비용은 0.95이며, 약 7개의 행을 읽을 것이라고 예상했습니다.
   • actual time=0.0536..0.0605 rows=7: 실제 실행 시 첫 번째 행을 읽는 데 0.0536초, 마지막 행까지 읽는 데 0.0605초가 소요되었으며, 실제로 7개의 행을 읽었습니다.
   • loops=1: 이 작업은 단 한 번만 수행되었습니다.
2. Filter: (users.age = 23) (cost=0.95 rows=1) (actual time=0.0599..0.0656 rows=2 loops=1)
   • Filter: (users.age = 23): 위에서 읽은 7개의 행 중에서 age = 23 조건을 만족하는 행을 필터링했습니다.
   • cost=0.95 rows=1: 옵티마이저는 필터링 조건을 만족하는 행이 약 1개일 것이라고 예측했습니다.
   • actual time=0.0599..0.0656 rows=2: 실제로는 2개의 행이 조건을 만족했고, 필터링 평가에는 0.0599초부터 0.0656초까지 소요되었습니다.
   • loops=1: 마찬가지로 필터링 작업도 한 번만 수행되었습니다.

※ 각 줄에 나와있는 실행시간은 누적 시간이므로 각 task 별 실행 시간은 현재 실행시간에서 이전 task의 실행시간을 빼서 구할 수 있습니다.

ex) Filter task의 수행시간은 0.0656 - 0.0605 = 0.0051이라고 할 수 있습니다.

 

 

실행 계획에서 자주 등장하는 type 종류

EXPLAIN을 통해 조회된 실행 계획 결과에서 특히 눈여겨 봐야할 컬럼인 Type의 종류에 대해 알아보겠습니다.

 

1. ALL

ALL은 테이블 전체를 처음부터 끝까지 탐색하는 방식(Full Table Scan)을 의미합니다.
인덱스를 전혀 사용하지 못했기 때문에 성능이 낮을 수 있습니다.

출처: https://land-turtler.tistory.com/134

EXPLAIN SELECT *
FROM users
WHERE age = 23;

 

 

2. index

index는 인덱스를 처음부터 끝까지 스캔하는 방식(Full Index Scan)입니다.
테이블 전체를 읽는 것보다는 효율적이지만, 필요한 일부 값만 있어도 인덱스 전체를 읽기 때문에 아주 효율적이진 않습니다.

출처: https://land-turtler.tistory.com/134

CREATE INDEX idx_name ON users(name);

EXPLAIN SELECT * 
FROM users 
ORDER BY name 
LIMIT 10;

 

 

3. const

const는 기본키(PK) 또는 UNIQUE 인덱스를 통해 단 한 건의 데이터를 정확하게 조회할 때 나타납니다.
가장 효율적인 방식 중 하나입니다.

출처: https://land-turtler.tistory.com/134

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    account VARCHAR(100) UNIQUE
);

INSERT INTO users (account) VALUES 
('user1@example.com'),
('user2@example.com'),
('user3@example.com');

EXPLAIN SELECT * FROM users WHERE id = 3;
EXPLAIN SELECT * FROM users WHERE account = 'user3@example.com';

 

 

4. range

range는 인덱스를 사용해서 BETWEEN, <, >, IN, LIKE 등 범위 조건을 만족하는 데이터를 탐색하는 방식입니다.
인덱스를 활용하므로 효율적인 방식에 해당합니다.

출처: https://land-turtler.tistory.com/134

CREATE INDEX idx_age ON users(age);

EXPLAIN SELECT * 
FROM users
WHERE age BETWEEN 10 and 20;

 

 

 

5. ref

ref는 비고유 인덱스(UNIQUE가 아닌 컬럼의 인덱스를 사용한 경우)를 사용하여 여러 행 중 일부를 탐색하는 경우입니다.
일반적으로 WHERE 조건에 자주 등장하며, 매우 자주 확인되는 type입니다.

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100)
);

INSERT INTO users (name) VALUES 
('박재성'),
('김지현'),
('이지훈');

CREATE INDEX idx_name ON users(name);

EXPLAIN SELECT * FROM users WHERE name = '박재성';

 

 

 

 

 

 

실행 계획은 SQL 성능을 최적화하는 데 매우 중요한 도구입니다.
특히 type이 ALL로 나올 경우에는, 인덱스를 적용하거나 쿼리 구조를 개선할 여지가 충분합니다.
처음에는 주요 항목 위주로 해석하는 데 집중하고, 익숙해진 뒤에 다른 항목들도 확장해서 공부해보시는 것을 추천드립니다.