[DB] MySQL - EXPLAIN 사용해보기

EXPLAIN이란?

EXPLAIN은 SQL문이 실행될 때 MySQL이 어떤 방식으로 데이터를 처리하는지를 보여주는 명령어다.

해당 쿼리를 실행할 때 MySQL은 어떤 테이블부터 읽고, 어떤 인덱스를 사용하며, 몇 건을 탐색할 예정인지와 같은 실행 계획을 알 수 있다.

쿼리의 속도 문제 원인 파악이나, 인덱스 활용 여부 확인 등에 사용한다.

왜 사용할까?

쿼리가 느릴 때 원인 분석을 할 수 있다.

인덱스가 실제로 사용되고 있는지 확인이 가능하다.

이를 통해 접근 방식을 개선해 속도를 향상 시킬 수 있다.

INSERT에서의 사용 예시

그렇다면 EXPLAIN을 어떻게 써야하는지 한번 알아보자.

아래 테이블을 예시로 들어보겠다.

empno	ename	job	sal	deptno
7369	SMITH	CLERK	800	20
7499	ALLEN	SALESMAN	1600	30
7521	WARD	SALESMAN	1250	30
7839	KING	PRESIDENT	5000	10

1. 인덱스가 없는 상태

EXPLAIN SELECT * FROM emp WHERE deptno = 10;

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	emp	ALL	NULL	NULL	4	Using

2. 인덱스 생성 후

CREATE INDEX idx_deptno ON emp(deptno);

EXPLAIN SELECT * FROM emp WHERE deptno = 10;

id	select_type	table	type	possible_keys	key	rows	Extra
1	SIMPLE	emp	ref	idx_deptno	idx_deptno	1	Using

EXPLAIN 출력 컬럼 설명

EXPLAIN을 출력하면 나오는 컬럼들이 어떤걸 의미하는지 알아보자

컬럼명	설명	쉽게 말하면
id	쿼리의 고유 번호 (서브쿼리 포함 시 단계별로 다르게 표시)	복잡한 쿼리일수록 id가 여러 개
select_type	SELECT문의 유형 (기본/서브쿼리/UNION 등)	서브쿼리인지, 조인인지 구분
table	액세스되는 테이블 이름	어떤 테이블을 읽는 중인지
type	조인 방식 또는 데이터 액세스 방법	가장 중요한 성능 지표 중 하나
possible_keys	사용할 수 있는 인덱스 목록	후보 인덱스들
key	실제 사용된 인덱스	인덱스가 없으면 성능에 문제 있음
rows	MySQL이 읽는다고 예측한 행 수	많을수록 느릴 가능성 있음
Extra	추가 정보 (Using where, Using filesort 등)	쿼리 방식의 힌트

고급 컬럼에서의 EXPLAIN 사용 예시

TABLE 컬럼에서의 사용 예시

1. 테이블 없이 단순 조회

EXPLAIN SELECT NOW();

table

NULL

2. FROM절의 서브쿼리

EXPLAIN
SELECT *
FROM (
  SELECT empno FROM emp GROUP BY empno
) AS derived_emp,
employees e
WHERE e.empno = derived_emp.empno;

id	table	select_type	Extra
1	<derived2>	SIMPLE	Using where
2	emp	DERIVED	Using filesort

• <derived2>는 2번째 실행 단계에서 만들어진 파생 테이블을 의미한다.
• select_type이 DERIVED로 표시된다.

PARTITIONS 컬럼에서의 사용 예시

MySQL 5.7까지는 파티션 목록을 EXPLAIN PARTITION 명령을 사용해야 했으나, MySQL 8.0부터 EXPLAIN 명령만으로 파티션 관련 정보를 확인 가능하게 되었다.

1. 파티션이 있는 테이블에서의 쿼리

-- hire_date를 기준으로 RANGE 파티셔닝 된 employee 테이블이 있다고 가정
EXPLAIN
SELECT *
FROM employee
WHERE hire_date BETWEEN '1999-11-15' AND '2000-01-15';

partitions

p1999, p2000

hire_date를 기준으로 파티셔닝 되어 있기 때문에, BETWEEN 조건을 통해 필요한 파티션만 읽게 된다.

해당 과정을 파티션 프루닝이라 부른다.(성능 최적화에 매우 중요한 역할을 한다.)