[MySQL] EXPLAIN

최근에 통계 관련 기능을 개발하기 위해 쿼리의 성능을 확인해가며 개발을 진행했다. 이때는 ORM 을 사용했기 때문에 프레임워크의 로깅 기능을 통해서 ORM 으로 작성한 쿼리에서 실제 발생하는 SQL 쿼리를 확인하면서 개발을 했다. 이번에는 한번 더 나아가서 SQL 쿼리가 실제 DB 에서 수행될 때 어떻게 수행되는지를 확인해보고자 한다.

 

SQL 의 실행계획을 보기 위해서는 EXPLAIN 명령어를 사용하면 된다. 이 글에서는 EXPLAIN 에 대해서 설명하고 EXPLAIN 사용 방법과 결과를 해석하는 방법 등에 대해서 정리한다.

1. EXPLAIN

EXPLAIN 은 MySQL 에서 쿼리를 실제 실행하기 전 MySQL 이 어떤 방식으로 작업을 실행할지 보여주는 도구이다. 쿼리의 성능 최적화를 위해서 많이 사용되며, 쿼리의 병목 지점등을 파악하는데 필수이다.

 

EXPLAIN 을 사용하면 특정 쿼리를 MySQL 에서 실행할 때의 실행계획이 표로 출력된다. 표는 해당 쿼리의 실행계획과 관련된 컬럼들로 구성되어 있다.

- EXPLAIN 사용법

EXPLAIN 을 사용하기 위해서는 아래와 같이 실행계획을 확인하려는 쿼리 앞에 EXPLAIN 을 붙여서 사용해주면 된다.

 

EXPLAIN SELECT * FROM your_table WHERE condition;

 

아래와 같이 자세한 결과 출력을 위해서 FORMAT 을 지정하여 실행할 수도 있다.

 

EXPLAIN FORMAT=TRADITIONAL SELECT ...
EXPLAIN FORMAT=JSON SELECT ...

- EXPLAIN 결과 항목

EXPLAIN 을 실행하면 실행계획에 대한 다양한 항목들이 출력된다. 이 목차에서는 EXPLAIN 을 실행했을 때 나오는 실행계획의 주요 항목들의 의미와 각 항목들에서 나올 수 있는 값들에 대해서 정리했다.

 

먼저 EXPLAIN 실행계획의 각 항목에 대한 간단한 설명이다.

1) EXPLAIN 결과 항목

id	쿼리의 각 SELECT 블럭을 구분하는 ID 서브쿼리나 조인이 있는 경우에 SELECT 블록을 구분하기 좋다.
select_type	쿼리 유형에 대한 항목 - SELECT, PRIMARY, SUBQUERY 등
table	쿼리가 접근하는 테이블의 이름
partitions	파티셔닝 테이블의 경우 사용된 파티션
type	테이블에 접근하는 방식으로 중요한 성능 지표 중 하나이다.
possible_keys	쿼리의 조건절 등에 포함되어 사용 가능한 인덱스들의 목록
key	possible_keys 중 실제 쿼리 동작에 사용된 인덱스
key_len	사용된 인덱스의 길이를 byte 단위로 표현
ref	어떤 컬럼/값이 인덱스 탐색에 사용되었는지 알려줌
rows	MySQL 이 실제로 스캔하게 될 것으로 예상되는 row 수
filtered	쿼리의 조건절을 만족할 것으로 예상되는 row 의 비율 (%)
Extra	쿼리 추가정보 - Using where, Using index, Using temporary, Using join buffer, Using filesort, Impossible WHERE 등

2) type

여러 항목 중 성능 확인 지표로 가장 중요한 역할을 하는 항목이 type 이다. type 은 데이터에 접근하는 방식, 스캔 방식을 알려주는 항목으로 type 에 출력되는 값들을 표로 정리했다.

 

system	테이블에 row 가 단 1개뿐인 경우
const	PK 또는 UNIQUE 에 해당하는 값 1건 뿐인 경우
eq_ref	JOIN 에서 정확히 1건과 일치하는 경우 (PK / Unique Key 와 join 될 때)
ref	인덱스를 동등 조건 (=) 검색으로 조회하는 경우
range	인덱스를 범위 조건으로 조회하는 경우 (<, >, BETWEEN, IN)
index	인덱스 전체를 조회하는 경우 (Index Full Scan)
ALL	테이블 전체를 조회하는 경우 (Table Full Scan)

3) select_type

쿼리가 조인, 서브쿼리등 여러 쿼리 블럭으로 구성되는 경우가 있다. select_type 에서는 각각의 쿼리가 어떤 블럭인지 출력한다.

 

SIMPLE	서브쿼리나 UNION 없는 단일 SELECT 문
PRIMARY	최상위 쿼리. 서브쿼리가 있는 경우 가장 바깥쪽의 SELECT 문을 의미한다.
SUBQUERY	SELECT 안의 서브쿼리
DERIVED	FROM 안의 서브쿼리 (파생 테이블)
UNION	UNION 의 두번째 SELECT 이후
DEPENDENT SUBQUERY	외부 쿼리에 의존적인 서브쿼리 (매번 실행됨)

2. EXPLAIN 예제

EXPLAIN 결과를 확인해보기 위해서 실제 예제 테이블을 생성하고 쿼리를 실행해보려 한다. 먼저 예제 쿼리를 실행할 데이터베이스와 테이블을 먼저 생성하고 더미데이터를 10만건 추가해준다.

 

-- 데이터베이스 생성 및 선택
CREATE DATABASE IF NOT EXISTS explain_demo;
USE explain_demo;

-- users 테이블 생성
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100),
    email VARCHAR(100),
    age INT,
    city VARCHAR(50),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 변수 초기화 및 데이터 삽입
SET @i = 0;
INSERT INTO users (username, email, age, city)
SELECT
    CONCAT('user_', @i := @i + 1),
    CONCAT('user_', @i, '@example.com'),
    FLOOR(RAND() * 60),
    ELT(FLOOR(1 + (RAND() * 5)), 'Seoul', 'Busan', 'Incheon', 'Daegu', 'Gwangju')
FROM information_schema.tables t1, information_schema.tables t2
LIMIT 100000;

 

예제 테이블 users 를 대상으로 한 조회쿼리의 실행계획을 확인해보았다.

먼저 인덱스 조건을 포함하지 않은 SELECT 문을 테스트했다.

 

-- 인덱스 없이 WHERE 조건 실행
EXPLAIN SELECT * FROM users WHERE age = 30;

 

 

인덱스 조건이 없기 때문에 테이블 전체를 스캔해야 한다. 이때문에 type 은 테이블 풀 스캔을 의미하는 ALL 이 출력되었다. 또한 특정 사용할 수 있는 인덱스 컬럼이 없기 때문에 possible_keys 나 key 는 NULL 값으로 출력된다. rows 의 경우 전체 데이터를 스캔해야 하기 때문에 100,000 건이 출력되었으며 Extra 로는 where 절을 사용하기에 Using where 로 출력되었다.

 

다음으로는 인덱스 컬럼을 조건절에 포함하여 테스트를 해보았다. 먼저 age 컬럼에 인덱스를 적용하고, 이전과 같이 age 컬럼에 조건을 건 쿼리를 실행하여 확인을 했다.

 

-- age에 인덱스 생성
CREATE INDEX idx_age ON users(age);

-- 동일 쿼리 실행 (인덱스 적용)
EXPLAIN SELECT * FROM users WHERE age = 30;

 

 

이전과 달리 type 은 인덱스 컬럼에 동등 조건 (=) 을 의미하는 ref 가 출력되었다. 그리고 possble_keys 와 key 에는 idx_age 가 출력되어 해당 쿼리가 age 컬럼의 인덱스 idx_age 를 사용한다는 것을 알 수 있다. rows 는 1,634 건으로 이전의 100,000 건보다 줄었으며, WHERE 조건의 age = 30 조건은 인덱스만으로 찾을 수 있기 때문에 Extra 는 NULL 값으로 출력되었다.

 

이번에는 두개 이상의 컬럼에 인덱스를 적용하는 복합 인덱스에 대한 테스트를 해보려 한다. city 와 age 에 복합 인덱스를 적용하여 테스트를 해보려 하는데, 먼저 인덱스 적용 전 쿼리에 대한 실행계획이다.

 

 

age 에만 인덱스가 적용되어 있기 때문에 possible_keys 와 key 에서는 idx_age 만 출력되는 것을 확인할 수 있다. 그리고 WHERE 절의 조건이 idx_age 인덱스만으로 조회할 수 없고 city 에 대한 조회도 필요하기 때문에 Extra 에서는 Using where 이 출력되는 것을 확인할 수 있다.

 

이번에는 city 와 age 두가지 컬럼에 복합인덱스를 적용하여 조회를 수행해본다.

 

-- 복합 인덱스 추가 (city, age)
CREATE INDEX idx_city_age ON users(city, age);

 

 

city 와 age 에 복합 인덱스를 추가했기 때문에 possible_keys 에 사용가능한 인덱스로 idx_age 와 idx_city_age 가 출력됐다. 그리고 실제 사용한 인덱스는 key 에서 확인할 수 있듯이 idx_city_age 이다. 이전에 idx_age 인덱스를 사용했을 때와는 달리 rows 숫자도 333 으로 줄었고, Extra 또한 idx_city_age 인덱스 조회외에 추가적인 조건이 필요하지 않기 때문에 NULL 로 출력됐다.

 

ORDER BY 가 포함된 쿼리에서의 인덱스 사용도 테스트했다.

 

 

인덱스 조건이 없기 때문에 type 은 테이블 풀 스캔, ALL 이고 possible_keys, key 도 NULL 값을 출력했다. LIMIT 10 조건이 있지만 정렬을 위해서는 모든 데이터를 조회해야 하기 때문에 rows 도 10만건에 가까이 출력됐다. 그리고 Extra 를 보면 Using filesort 라는 값이 출력됐는데, 이 값은 정렬을 위해 메모리에 데이터를 로드하고 정렬 알고리즘을 적용했다는 의미이다.

 

이번에는 created_at 컬럼에 인덱스를 적용한 후 쿼리를 실행한다. 인덱스를 생성할 때 DESC 정렬 조건을 추가하여 적용했다.

 

-- created_at 컬럼에 DESC 정렬 조건을 추가하여 인덱스 생성
CREATE INDEX idx_created_at ON users(created_at DESC);

 

 

정렬용 인덱스를 추가한 후 쿼리를 실행한 결과이다. 인덱스가 적용되었기 때문에 type 은 index, key 는 idx_created_at 을 출력했다. 반면에 possible_keys 에는 idx_created_at 인덱스가 출력되지 않았는데, 이는 optimizer 가 쿼리 최적화를 위해 사용할 인덱스를 찾지 못했기 때문이다. optimizer 는 WHERE 절이나 JOIN 등에 사용하여 쿼리 조회 성능을 향상시킬 수 있는 인덱스 후보군을 찾아서 출력시키는데, 이 쿼리에서는 조회 조건절이 없기 때문에 NULL 이 출력되었다.

 

이외에 rows 는 LIMIT 조건으로 제한한 만큼 10 건이 출력됐다. 인덱스로 정렬 조건이 해결됐기 때문에 Extra 컬럼도 NULL 이 출력됐다.