MySQL EXPLAIN 실행 계획을 통한 쿼리 최적화

해당 포스팅은 MySQL의 EXPLAIN 명령어를 통해 쿼리의 실행 계획을 확인하고 이 계획을 바탕으로 쿼리 최적화를 진행해보며 정리한 내용입니다.

 

 

1. EXPLAIN을 통한 쿼리 최적화를 진행하게 된 이유

프로젝트가 어느 정도 마무리되고 솔루션화를 진행하는 과정에서 기존에 짜여진 테이블 구조 중 마음에 들지 않는 부분들을 리팩토링하기 위한 계획을 세우고 있었습니다.

 

결론적으로 리팩토링 계획에 대한 피드백은 '기능상 문제가 있는 거라면 수정하는 것이 당연하겠지만, 정상적으로 작동하고 있다면 테이블 구조를 변경했을 때 발생하는 사이드 이펙트도 충분히 고려되어야 한다.'였습니다.

(구조가 바뀌면 해당 테이블을 사용하는 모든 쿼리에 영향을 주게 되고, 결국 해당 부분에 대한 테스트까지 다 다시 진행되어야 합니다.)

'아직 배워가고 있는 단계이다 보니 지금 테이블 구조를 변경한다고 해도 나중에 다시 돌아보면 부족한 부분이 있을 것이다.'라는 말도 일리가 있었습니다.

그래서 우선은 EXPLAIN 실행 계획을 통해 현재 쿼리들이 잘 작동하고 있는지를 확인하고 성능에 영향을 주는 Slow Query를 찾아 수정하는 쿼리 튜닝을 권유받아 진행하며 관련 내용을 정리하게 되었습니다.

 

 

 

2. Optimizer와 EXLPAIN

Optimizer

MySQL에서는 최적의 쿼리를 실행하기 위해 각 테이블의 데이터가 어떤 분포로 저장되어 있는지를 참조하고, 해당 데이터를 기반으로 최적의 실행 계획을 수립해주는 옵티마이저(Optimizer)가 내부적으로 동작하는데요.

 

explain
select m.name, m.contact, m.address, t.transaction_id, t.price, t.state
from member m 
inner join transaction t 
on t.member_idx = m.idx
where t.state = 'success';

MySQL EXLPAIN 이란 이때 수립되어 동작하는 실행 계획(Query Plan)을 사용자가 알아보기 쉽게 DB 결과 셋으로 보여주는 명령어로 EXPLAIN을 활용하면 기존의 쿼리 튜닝을 할 수 있을 뿐만 아니라 성능 분석, 인덱스 전략 수집 등과 같이 쿼리 최적화에 대한 전반적인 업무를 처리할 수 있습니다.

 

EXPLAIN 명령어는 위 예시와 같이 EXPLAIN 뒤에 확인하고 싶은 SELECT 쿼리문을 적으면 됩니다.

(INSERT, UPDATE, DELETE 문장에 대해서는 실행 방법을 확인할 수 없기 때문에 WHERE 조건절만 같은 SELECT 문으로 만들어서 대략적인 계획을 확인해볼 수 있습니다.)

 

 

 

3. 인덱싱 전과 후 EXPLAIN 결과

예시로 사용된 테이블 구조

(예시로 사용된 member, transaction 테이블 구조는 테스트용으로 간단하게 넣어본 거라 참고만 해주시면 될 것 같습니다.)

 

mysql> explain select m.name, m.contact, m.address, t.transaction_id, t.price, t.state from transaction t inner join member m on m.idx = t.member_idx where t.state = 'failure';
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                  | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | ALL    | member_idx    | NULL    | NULL    | NULL                 |  300 |    10.00 | Using where |
|  1 | SIMPLE      | m     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test_db.t.member_idx |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

mysql> create index state on transaction (state);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select m.name, m.contact, m.address, t.transaction_id, t.price, t.state from transaction t inner join member m on m.idx = t.member_idx where t.state = 'failure';
+----+-------------+-------+------------+--------+------------------+---------+---------+----------------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys    | key     | key_len | ref                  | rows | filtered | Extra |
+----+-------------+-------+------------+--------+------------------+---------+---------+----------------------+------+----------+-------+
|  1 | SIMPLE      | t     | NULL       | ref    | member_idx,state | state   | 137     | const                |  100 |   100.00 | NULL  |
|  1 | SIMPLE      | m     | NULL       | eq_ref | PRIMARY          | PRIMARY | 4       | test_db.t.member_idx |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+------------------+---------+---------+----------------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

transaction table의 state 필드에 인덱싱을 적용하기 전과 후의 explain 결과입니다. (간단한 예시)

 

아직 각 필드에 대한 자세한 내용은 모르지만 첫 번째 row의 type 값이 ALL -> ref로 바뀌었고, key가 null -> state로 바뀌었으며 ref, rows, filterd, Extra 등의 값이 바뀐 것을 확인할 수 있는데요. 아래 내용을 통해 실행 결과 각 필드의 의미를 살펴보겠습니다.

 

 

 

4. 실행 결과의 각 필드 별 의미

id

행이 어떤 SELECT 구문을 나타내는지 알려주는 번호입니다.

쿼리 구분에 서브 쿼리나 UNION이 없다면 SELECT는 하나밖에 없기 때문에 수행되는 실행 단위는 1개가 되고 따라서 모든 행에 1이라는 id 값이 부여됩니다.

하지만 이 외의 경우에는 원 구문에서 순서에 따라 각 SELECT 문들에 순차적으로 번호가 부여됩니다.

 

 

select_type

 

구분	설명
SIMPLE	Union 이나 Sub Query가 없는 단순한 SELECT문
PRIMARY	가장 밖에 있는 SELECT문 Sub Query를 사용할 경우 Sub Query의 외부에 있는 쿼리(첫 번째 쿼리), Union을 사용할 경우 Union의 첫 번째 쿼리
UNION	Union 쿼리에서 PRIMARY를 제외한 나머지 SELECT문
DEPENDENT_UNION	UNION과 동일하나 바깥 쿼리에 의존성을 가진 Union의 SELECT문
UNION_RESULT	Union 쿼리의 결과물
SUBQUERY	가장 밖에 있는 SELECT문의 Sub Query Sub Query 또는 Sub Query를 구성하는 여러 쿼리 중 첫 번째 SELECT문
DEPENDENT_SUBQUERY	SUBQUERY와 동일하나 바깥 쿼리에 의존성을 가진 Sub Query의 SELECT문
DERIVED	SELECT로 추출된 테이블(FROM 절에서의 Sub Query 또는 Inline View)
UNCACHEABLE_SUBQUERY	SUBQUERY와 동일하지만 공급되는 모든 값에 대해 Sub Query를 재처리 바깥 쿼리에서 공급되는 값이 동일하더라도 Cache된 결과를 사용할 수 없음
UNCACHEABLE_UNION	UNION과 동일하지만 공급되는 모든 값에 대하여 UNION 쿼리를 재처리

 

 

table

어떤 테이블에 대한 접근을 표시하고 있는지 표시되는 필드입니다.

 

 

type

테이블에서 어떻게 행 데이터를 가지고 올 것인가에 대한 접근 방식을 표시하는 필드입니다.

접근 방식은 대상 테이블로의 접근이 효율적인지 여부를 판단하는데 아주 중요한 항목이 됩니다.

(어떤 식으로 쿼리가 튜닝되어야 하는지에 대한 insight를 제공합니다.)

구분	설명
system	테이블에서 1행 밖에 없는 경우의 특수한 접근 방식입니다.
const	SELECT에서 Primary Key 또는 Unique Key를 통해 조회하는 방식으로 조회 결과는 최대 1건이 됩니다. 단 Primary Key 또는 Unique Key의 범위 검색이 되는 경우는 const가 되지 않습니다.
ALL	테이블을 처음부터 끝까지 검색하는 경우로 '테이블 풀스캔'이라고 합니다.
index	인덱스를 처음부터 끝까지 검색하는 경우로 '인덱스 풀스캔'이라고 합니다.
eq_ref	조인이 내부 테이블로 접근할 때 Primary Key 또는 Unique Key에 의한 조회가 일어납니다. const와 비슷하지만 조인의 내부 테이블에 접근한다는 점이 다릅니다.
ref	조인을 할 때 Primary Key 혹은 Unique Key가 아닌 Key로 매칭하는 경우입니다.
ref_or_null	ref와 같지만 null이 추가되어 검색되는 경우입니다.
range	특정 범위 내에서 인덱스를 사용하여 원하는 데이터를 추출하는 경우로 데이터가 방대하지 않다면 단순 SELECT에서 나쁘지 않은 결과를 도출합니다.
index_merge	두 개 이상의 인덱스가 병합되어 검색이 이루어지는 경우입니다.
unique_subquery	IN절 안의 Sub Query에서 Primary Key를 사용하는 방식으로, 이 방식은 쓸데없는 오버헤드를 줄여 상당히 빠릅니다.
index_subquery	unique_subquery와 비슷하지만 Primery Key가 아닌 인덱스가 사용되는 경우입니다. 이 방식 역시 상당히 빠른 편입니다.

 

 

possible_keys

쿼리에서 접근하는 컬럼들과 사용된 비교 연산자들을 바탕으로 어떤 인덱스를 사용할 수 있는지를 표시해주는 필드입니다.

 

 

key

인덱스로 사용할 수 있는 필드를 보여주는 possible_keys 필드가 있다면 key 필드는 실제로 옵티마이저가 최적화 검색에 사용한 인덱스가 표시되는 필드입니다.

 

 

key_len

key_len 필드는 선택된 인덱스의 길이를 의미합니다. 중요한 필드는 아니지만 인덱스가 너무 긴 경우에는 비효율적일 수 있습니다.

 

 

ref

key 컬럼에 나와있는 인덱스에서 값을 찾기 위해 선행 테이블의 어떤 컬럼이 비교되었는지를 나타냅니다.

 

 

rows

결과 산출에 있어서 접근되는 행의 예측값입니다. 조인이나 서브 쿼리 최적화에 있어서 중요한 항목이 됩니다.

 

 

Extra

Extra 필드는 옵티마이저가 동작하는 방식에 대한 부가적인 정보를 보여줍니다.

구분	설명
using index	테이블에 접근하지 않고 인덱스로만 접근하는 것을 의미합니다. 커버링 인덱스로 처리되며 'index only scan'이라고도 불립니다.
using where	where 조건으로 데이터를 추출하고 추가적으로 검색 조건을 적용해 행의 범위를 축소한 것을 표시합니다.
using filesort	order by 인덱스로 해결하지 못하고 MySQL의 quick sort인 filesort로 행을 정렬한 것을 나타냅니다.
using temporary	임시 테이블을 사용한다는 것을 의미합니다. order by 또는 group by를 할 때 주로 사용됩니다.
using join buffer (Block Nested Loop)	조인에 적절한 인덱스가 없어 버퍼를 이용했음을 표시합니다.

 

 

 

5. 추가적으로

JSON 형식의 EXPLAIN

explain format = json
select 
...

MySQL 5.6 버전부터는 위 예시와 같이 EXPLAIN 결과를 JSON 형태로도 출력할 수 있게 되었는데요. JSON 형태의 결과는 기본적인 EXPLAIN 보다 제공되는 데이터가 몇 가지 더 있습니다.

 

 

EXPLAIN ANALYZE

explain analyze
select 
...

8.0.18 버전부터 추가된 EXPLAIN ANALYZE 기능은 쿼리 실행에 소비되는 시간과 수행되는 rows 그리고 측정한 결과 건수 등에 대해 수행된 SQL의 Actual rows 정보를 추가로 보여주게 됩니다.

 

(해당되는 두 기능은 추가적으로 다루게 되면 내용이 길어지기 때문에 이러한 기능이 있다 정도만 남긴 점 참고 부탁드립니다.)

 

 

 

< 참고 자료 >

https://nomadlee.com/mysql-explain-sql/

https://cheese10yun.github.io/mysql-explian/