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
- 테이블 구조 정보 확인
- 쿼리 실행 계획 정보 확인 (최적화에 주로 사용됨)
- 쿼리 실행 분석 정보 확인 (튜닝 시에는 크게 사용되지 않으므로 간단히 설명)
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 수
등을 결괏값으로 표시하고 있다.
사실상 쿼리 튜닝에는 크게 많이 이용되지 않으므로 생략하고, 다음에 쿼리 실행 분석 정보 확인에 대하여 설명을 쓰도록 하겠다.
'IT Study > 데이터베이스' 카테고리의 다른 글
MySQL 운영 노하우(Know-how) II - DATA TYPE 알고 쓰자! (2) | 2023.07.18 |
---|---|
MySQL 운영 노하우(Know-how) I (0) | 2023.07.16 |
MySQL에서 위도,경도 활용하여 거리 계산하기 (0) | 2020.11.06 |
MySQL 쿼리 튜닝 은 어떻게 할까? 기초편 (INDEX) (0) | 2020.10.31 |