[MySQL] Explain 사용법 및 분석

들어가기 전

해당 포스팅에서는 Explain이 무엇이며 사용하는 방법, 사용했을 때 나오는 결과를 분석하여 설명드리겠습니다.

필자는 MySQL 버전 8.0 기준으로 설명하겠습니다.

 

Explain이란?

DB 서버가 어떠한 쿼리를 실행할 것인지 실행계획을 알고 싶을 때 사용하는 명령어입니다.

Explain을 사용하면 실행시킬 쿼리의 실행계획을 알 수 있어 성능 분석을 하여 쿼리 튜닝을 할 수 있습니다.

 

사용방법

EXPLAIN [ANALYZE] SELECT (컬럼..) FROM 테이블

• 실행계획을 세울 쿼리 앞에 EXPLAIN 키워드를 사용합니다.
• ANALYZE 옵션은 쿼리 처리에 사용되는 각 반복자에 대해 TREE 형식의 SELECT 문 실행에 대한 확장 정보를 제공하고 예상 비용을 쿼리의 실제 비용과 비교 가능합니다.

● Explain과 ANALYZE 옵션을 사용했을 때 나오는 결과는 예제를 통해서 알아보겠습니다.

 

 

예제 테이블

 

/* Member 테이블 */
create table member(
id BigInt primary key,
name varchar(255)
);

/* Orders 테이블 */
create table orders(
order_id BigInt primary key,
item_name varchar(255),
price int, 
member_id BigInt,
foreign key (member_id) references member(id)
);

 

 

 

 

 

필자는 Explain과 ANAYLZE를 비교하는 걸 설명하기 위해 Member 테이블과 Orders 테이블에 더미데이터 1000개 넣었습니다.

 

 

Explain만 사용했을 때

Explain을 사용했을 때 나오는 결과물에 대해 분석하겠습니다. 

 

EXPLAIN SELECT m.*, o.* FROM MEMBER m INNER JOIN ORDERS o ON m.id = o.member_id;

 

• id : Select에 붙은 번호를 의미합니다. MySQL에서는 Join을 하나의 단위로 실행하기 때문에  위에 예제에서는 id값이 1입니다. 따라서 조인만 수행하는 쿼리에서는 id값은 항상 1이 됩니다. 서브쿼리를 사용하게 되면 id값이 바뀌게 됩니다.
• select_type : Select의 타입은 몇 가지 상황을 제외하면 항상 SIMPLE이 됩니다. 서브쿼리나 UNION을 사용하는 쿼리가 있으면 바뀌게 됩니다.
• table : 테이블 명을 나타냅니다. alias를 사용할 경우 alias로 설정한 값이 나오게 되고 그렇지 않으면 테이블 명 그대로 나오게 됩니다.
• partitions :  파티셔닝 되어 있는 경우에 사용되는 필드입니다.
• type : 접근 방식을 표시하는 필드입니다. 접근 방식은 테이블에서 어떻게 행 데이터를 가져올 것인가를 가리킵니다. 접근 방식의 종류에는 ALL, eq_ref, ref 등이 있는데 ALL, eq_ref는 조인 시 기본키나 고유키를 사용하여 하나의 값으로 접근하고 ref는 여러 개의 행을 패치할 가능성이 있는 접근을 의미합니다.
• possible_keys : 이용 가능성 있는 인덱스의 목록을 의미합니다.
• key : 이용 가능성 있는 인덱스의 목록 중에서 실제로 옵티마이저가 선택한 인덱스가 key가 됩니다. 
• key_len : 선택된 인덱스의 길이를 의미합니다. 인덱스가 길면 비효율적이기 때문에 인덱스를 사용할 경우 보시는 것을 추천드립니다.
• rows : 접근 방식을 사용해서 몇 행을 가져왔는지 표시하는 필드입니다. 최초에 접근하는 테이블에 대해서 쿼리 전체에 의해 접근하는 행 수 그 이후에 테이블에 대해서는 1행 조인으로 평균 몇 행에 접근했는가를 표시합니다.
• filtered : 행 데이터를 가져와 조건문이 적용되면 몇 행이 남는지를 표시해 줍니다.
• extra : 옵티마이저가 동작하는데 대해서 힌트를 알려줍니다. 해당 필드는 옵티마이저의 행동을 파악할 때 중요한 필드입니다.

 

Subquery + Where

 

explain select m.name from Member m where m.id in 
	(select o.member_id from Orders o where o.order_id between 100 and 200);

 

 

• subquery를 이용해서 id값이 달라진 것을 확인할 수 있습니다.
• Where 절을 사용하여 Extra값에 Using Where가 들어간 것을 확인할 수 있습니다.

 

Group By + having

 

explain select name from member  group by name having name like '%12%'

 

 

 

Explain + Analyze 사용했을 때

Explain과 Analyze 옵션을 사용했을 때 나오는 결과물에 대해 분석하겠습니다. 

 

EXPLAIN ANALYZE SELECT m.* , o.* FROM MEMBER m
	inner join ORDERS o on m.id = o.member_id;

 

 

결과

 

'-> Nested loop inner join  (cost=451.00 rows=1000) (actual time=0.308..4.170 rows=1000 loops=1)
-> Table scan on m  (cost=101.00 rows=1000) (actual time=0.133..0.477 rows=1000 loops=1)
-> Index lookup on o using member_id (member_id=m.id)  (cost=0.25 rows=1) (actual time=0.003..0.003 rows=1 loops=1000)'

 

• cost : 실행 비용을 뜻합니다. 명령문을 실행하는데 걸리는 비용을 의미합니다.
• actual time : 실행 시간을 뜻합니다. 첫 번째 시간은 시작 시간을 뜻하고 두 번째 시간은 끝난 시간을 의미합니다. 즉 위에 예제에서는 0.308이 시작시간이 되고 4.170이 끝난 시간을 의미합니다.
• rows : 예상 출력 행 개수를 의미합니다.
• loops : 반복한 횟수를 의미합니다.

 

 

해당 포스팅에서는 Explain과 Analyze 옵션에 대해서 알아봤습니다. 하지만 Explain 하여 나온 결과값 분석에 대한 설명은 생략하였습니다. 해당 포스팅에서 빠진 결과 값 분석에 대해서는 다른 포스팅에서 이어서 설명하겠습니다.