MYSQL 쿼리 튜닝(2) - EXPLAIN

https://dev.mysql.com/doc/refman/8.0/en/explain.html

EXPLAIN이란?

Explain의 기능은, MySQL 웹페이지에 이렇게 기록되어있다.

Obtaining Table Structure Information
Obtaining Execution Plan Information
Obtaining Information with EXPLAIN ANALYZE

• 테이블 구조 정보 확인
• 쿼리 실행 계획 정보 확인 (최적화에 주로 사용됨)
• 쿼리 실행 분석 정보 확인 (튜닝 시에는 크게 사용되지 않으므로 간단히 설명)

(MySQL EXPLAIN 원문 설명)

MySQL :: MySQL 8.0 Reference Manual :: 13.8.2 EXPLAIN Statement

 

1. 테이블 구조 정보 확인

EXPLAIN으로 테이블명을 실행하면, 아래와 같이 정보가 나타난다.

EXPLAIN table_name;

Field	Type	Null	Key	Default	Extra
(1) pk_col	int(11) unsigned	NO	PRI	(NULL)	auto_increment
(2) int_col	int(11)	NO		0
(3) fk_col	int(11) unsigned	NO	MUL	(NULL)
(4) enum_col	enum('A','B','C')	NO		A
(5) txt_col	text	YES		(NULL)

 

OUTPUT 은 총 6개의 콜럼으로 표시된다. 

• Field : 필드명 표시
• Type : 데이터 타입 표시
• Null : Null 이 가능한지 표시 (YES / NO)
• Key : INDEX 정보 표시
• Default : 필드 Default 값 표시
• Extra : 그 외 정보 

EXPLAIN table_name 간단한 테이블 구조를 확인할 수 있다.

 

 

 

 

2. 쿼리 실행 계획 정보 확인

EXPLAIN SELECT 하게 되면 데이터를 추출하는데 어떤 계획으로 데이터를 추출할 예정인지를 표시해준다.

EXPLIAN SELECT * FROM table1
	INNER JOIN table2 on table1.pk_no = table2.table1_pk_no
WHERE table1.pk_no < 30 AND table1.index_number LIKE 'Hello world%;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	extra
1	SIMPLE	table1	(NULL)	range	PRIMARY, ...	PRIMARY	4	(null)	971	11.11	...
1	SIMPLE	table2	(NULL)	eq_ref	PRIMARY, ...	PRIMARY	4	...	22	100.00	...

 

1) "id"

• 실행 순서에 따라 ID 부여 
• 아이디의 수가 같을 경우에는 해당 쿼리가 동시에 실행됨을 의미

2) "select_type"

• 쿼리의 종류를 나눔
• "SIMPLE" : 쿼리 절 내의 다른 쿼리 혹은 union 쿼리가 없을 경우에 표시
• "SUBQUERY" : SELECT 구문에서 나타나는 쿼리 절이 있을 경우에 표시
• "DERIVED" : FROM 구문에 나타나는 쿼리절이 있을 경우에 표시
• "UNION" : UNION을 사용한 쿼리가 있을 경우에 표시
• "UNION_RESULT" : UNION 임시 테이블을 사용할 경우에 표시

3) "table"

• 어떤 테이블을 사용 혹은 접근하는지를 표시
• 테이블 명, drived(Number) , union가 표시

설명용	id	select_type	table	partitions	type
ROW1	1	PRIMARY	derived2	..	..
ROW2	2	DERIVED	derived3	..	..
ROW3	3	DERIVED	table_name	..	..

• (테이블 설명) [ROW1]의 table 란에 표시된, derived2는, 다음 ROW2를 참조
• (테이블 설명) [ROW2]의 select_type : DERIVED, id : 2 == > derived2
• (테이블 설명) [ROW2]의 table 란에 표시된, derived3는, 다음 ROW3를 참조
• (테이블 설명) [ROW3]의 select_type : DERIVED, id : 3 == > derived3
• (테이블 설명) [ROW3]의 select_type : DERIVED, table : table_name.  table_name이라는 테이블을 참조함을 알 수 있음

4) "type"

• coulmn search (filter) 방식을 표시
• (1) ALL > (2) INDEX > (3) RANGE > (4) ref > (5) eq_ref > (6) const 순으로 색출이 빠름
• (1) ALL : 검색을 하기 위해 모든 데이터를 읽고 색출함
• (2) INDEX : 인덱스 순서로 모든 데이터를 읽고 색출함 => ALL과 동일하나, INDEX만 기록된 데이터 내에서 데이터를 읽고 색출함
• (3) RANGE : =, <, >, ISNULL, <=>, BETWEEN, IN 등 일정 범위 내에 있는 값을 읽고 색출함.
• (4) ref : =, < , > 연산자를 사용하여 인덱스 내에 색출됨을 표시. 다만 인덱스가 PRIMARY, UNIQUE 가 아닐 때 적용.
• (5) eq_ref : = 연산자를 사용하여 인덱스 내에 색출됨을 표시. PRIMARY KEY, UNIQUE 연산일 때 일반적으로 적용.
• (6) const : PRIMARY KEY , UNIQUE 인덱스가 걸려있는 모든 부분을 상수값으로 색출할 때 적용

5) "possible_keys"

• 색출할 때, 사용할 수 있는 모든 인덱스 값을 나열함
• NULL 일 경우, 해당 쿼리를 실행할 때, 사용할 수 있는 인덱스 값이 존재하지 않음을 의미
• 쿼리 실행 시, 인덱스 값을 사용하여 색출하는 것이 속도 개선에 중요한 역할을 하므로, 조건 값(WHERE) 절에서 인덱스를 활용하도록 변경하여 쿼리문을 최적화 시킴

6) "key"

• 쿼리 실행 시, 사용할 (예정인) 키를 나타냄

7) "key_len"

• 인덱스 필드에서 사용할 수 있는 최대 길이

8) "ref"

• 참조 테이블 혹은 참조 SUB QUERY를 표시

9) "row"

• 데이터 색출 시 읽게 될 데이터의 수를 표시

10) "extra"

• using index, using where, using temporary, using filesort, range checked for each record (index map:N)
• using index : 커버링 인덱스를 활용하여, 실제 테이블에 접근하지 않고 데이터를 색출
• using where : 모든 데이터를 색출 후 필터링하여 데이터를 색출
• using temporary : 쿼리 결과를 정렬하기 위해 임시로 테이블을 생성
• using filesort : 색출된 데이터를 정렬할 때, 외부 저력을 사용해야 함을 의미 (인덱스 내에서 되지 않음)
• range checked for each record (index map:N) : 인덱스를 활용할 수 없으므로, 인덱스를 다시 평가해야 함을 의미

 

 

3. 쿼리 실행 분석 정보 확인

쿼리 실행 후, 분석 정보를 표시하고 있으며, 쿼리 실행을 분석하는 것이므로, 쿼리 실행 예상을 분석하는 것과는 차이가 있다.

 

(원문)

- Estimated execution cost
   (Some iterators are not accounted for by the cost model, and so are not included in the estimate.)
- Estimated number of returned rows
- Time to return first row
- Time to return all rows (actual cost), in milliseconds
   (When there are multiple loops, this figure shows the average time per loop.)
- Number of rows returned by the iterator
- Number of loops

• 예상 실행 비용
• 예상 결과 데이터 수
• 첫 번째 열(데이터)을 색출 시간
• 전체 열(데이터) 색출 시간 (밀리초)
  (looping query가 있을 때는, 1 looping 당 1회 평균 시간)
• Iterator (looping)에 의해 색출된 열(데이터)의 수
• Looping 수

등을 결괏값으로 표시하고 있다.

사실상 쿼리 튜닝에는 크게 많이 이용되지 않으므로 생략하고, 다음에 쿼리 실행 분석 정보 확인에 대하여 설명을 쓰도록 하겠다.