이 글은 책 Real Mysql 8.0 10장, 업무에 바로 쓰는 SQL 튜닝 3장과 Github repo의 예제, 하단 Reference 참고했습니다. 저작권 문제 발생 시 게시물이 비공개될 수 있습니다. 잘못된 내용이 있다면 편하게 말씀해주세요.
실행 계획(explain)이란?
실행 계획(explain)은 클라이언트가 MySQL 서버에 요청한 SQL문을 어떻게 데이터를 불러올 것인지에 관한 계획, 경로를 의미한다. 실행 계획 정보를 활용하여 SQL 튜닝을 할 수 있다. 단, 실제 수행 순서가 아닌 MySQL의 통계 정보를 기반으로 계산한 예측값이다. 실제값으로 실행 결과를 보려면 옵션을 추가해야한다.
실행 계획 예제 설명
아래에서 살펴볼 예제는 책 업무에 바로 쓰는 SQL 튜닝 3장의 저장소를 참고하였다.
실행 계획 출력
먼저 특정 SQL문의 실행 계획을 출력하는 명령어들을 살펴보자.
예제 SQL 문
예제 SQL 문은 사원 테이블에서 사원 번호가 100001 ~ 200000 인 사원의 모든 열을 조회한다.
SELECT *
FROM 사원
WHERE 사원번호 BETWEEN 100001 AND 200000;
+--------------+--------------+----------+----------------+--------+--------------+
| 사원번호 | 생년월일 | 이름 | 성 | 성별 | 입사일자 |
+--------------+--------------+----------+----------------+--------+--------------+
| 100001 | 1953-02-07 | Jasminko | Antonakopoulos | M | 1994-12-25 |
| 100002 | 1957-03-04 | Claudi | Kolinko | F | 1988-02-20
생략
| 200000 | 1960-01-11 | Selwyn | Koshiba | M | 1987-06-05 |
+--------------+--------------+----------------+------------------+--------+--------------+
10025 rows in set (0.01 sec)기본 EXPLAIN
예제 SQL 문의 MySQL 서버에서 실행 계획은 EXPLAIN, DESCRIBE, DESC 명령어로 출력할 수 있다. 3가지 모두 동일한 결과가 나온다.
EXPLAIN SQL문;
DESCRIBE SQL문;
DESC SQL문;
# 실행 , EXPLAIN 대신 DESCRIBE, DESC으로 변경해도 동일한 결과가 나온다.
EXPLAIN
SELECT *
FROM 사원
WHERE 사원번호 BETWEEN 100001 AND 200000;
# 결과
+----+-------------+--------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
| 1 | SIMPLE | 사원 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 20080 | 100.00 | Using where |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)EXPLAIN Format Option
MySQL 8.0 부터 EXPLAIN 명령에 FORMAT 옵션을 사용해 실행 계획의 결과를 단순 Table, JSON, TREE 으로 지정할 수 있다.
# Defalut는 Table View로 나온다.
EXPLAIN
SELECT *
FROM 사원
WHERE 사원번호 BETWEEN 100001 AND 200000;
+----+-------------+--------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
| 1 | SIMPLE | 사원 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 20080 | 100.00 | Using where |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
# 끝에 '\G' - 세로(?) view
EXPLAIN
SELECT *
FROM 사원
WHERE 사원번호 BETWEEN 100001 AND 200000\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: 사원
partitions: NULL
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 20080
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.01 sec)
# TREE View
EXPLAIN FORMAT = TREE
SELECT *
FROM 사원
WHERE 사원번호 BETWEEN 100001 AND 200000;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (`사원`.`사원번호` between 100001 and 200000) (cost=4021.24 rows=20080)
-> Index range scan on 사원 using PRIMARY over (100001 <= 사원번호 <= 200000) (cost=4021.24 rows=20080)
|
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
# JSON View
EXPLAIN FORMAT = JSON
SELECT *
FROM 사원
WHERE 사원번호 BETWEEN 100001 AND 200000;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "4021.24"
},
"table": {
"table_name": "사원",
"access_type": "range",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"사원번호"
],
"key_length": "4",
"rows_examined_per_scan": 20080,
"rows_produced_per_join": 20080,
"filtered": "100.00",
"cost_info": {
"read_cost": "2013.24",
"eval_cost": "2008.00",
"prefix_cost": "4021.24",
"data_read_per_join": "1M"
},
"used_columns": [
"사원번호",
"생년월일",
"이름",
"성",
"성별",
"입사일자"
],
"attached_condition": "(`tuning`.`사원`.`사원번호` between 100001 and 200000)"
}
}
} |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
EXPLAIN ANALYZE
위에서 살펴본 실행계획은 실제 SQL문이 실행된 뒤 나온 계획이 아니라 MySQL 서버가 가지고 있는 통계정보(히스토그램,...)들을 활용한 예측된 결과이다. 만약 실제 실행된 소요 시간, 비용을 측정하여 실행 계획 정보를 출력하고 싶다면 ANALYZE 키워드를 사용한다.
EXPLAIN ANALYZE
SELECT *
FROM 사원
WHERE 사원번호 BETWEEN 100001 AND 200000;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (`사원`.`사원번호` between 100001 and 200000) (cost=4021.24 rows=20080) (actual time=0.066..209.340 rows=10025 loops=1)
-> Index range scan on 사원 using PRIMARY over (100001 <= 사원번호 <= 200000) (cost=4021.24 rows=20080) (actual time=0.035..77.344 rows=10025 loops=1)
|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
실행 계획 결과의 컬럼들 설명
실행 계획 결과는 Table View 기준이다.
EXPLAIN 키워드로 실행 계획을 조회하면 Table 형태로 여러 컬럼의 결과가 나온다.
EXPLAIN
SELECT *
FROM 사원
WHERE 사원번호 BETWEEN 100001 AND 200000;
# 결과
+----+-------------+--------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
| 1 | SIMPLE | 사원 | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 20080 | 100.00 | Using where |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)컬럼은 (id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra)으로 구성되어 있다. 각 컬럼들을 해석하여 실행 계획을 알 수 있고 이 정보를 가지고 SQL 문이 효율적/비효율적으로 작동하는지 분석하여 튜닝 방향을 잡을 수 있다.
출력된 컬럼들을 하나씩 살펴본다. (보다 자세한 내용은 MySQL 8.0 docs : 8.8.2 EXPLAIN Output Format 참고)
id
id는 실행 순서이다. SQL 문이 수행되는 순서를 나타낸다.
id가 작을수록 먼저 수행된 것이다. id가 같은 값이라면 두 개 테이블의 조인이 되었다고 볼 수 있다.
아래 SQL 문은 FROM 절을 살펴보면 사원, 급여 테이블을 join 하고 있다. 또한 SELECT 절에서 카운트라는 서브쿼리가 존재한다.
실행 계획 결과의 1,2 번째 행이 id가 1인 것을 볼 수 있다. 이는 사원, 급여 테이블이 join으로 첫 번째에 수행된 것을 확인할 수 있다. 3번째 행은 id가 2로 join이 수행 뒤 SELECT 절의 카운트라는 서브쿼리가 수행된 것을 확인할 수 있다.
EXPLAIN
SELECT 사원.사원번호, 사원.이름, 사원.성, 급여.연봉,
(SELECT MAX(부서번호)
FROM 부서사원_매핑 as 매핑 WHERE 매핑.사원번호 = 사원.사원번호) 카운트
FROM 사원, 급여
WHERE 사원.사원번호 = 10001
AND 사원.사원번호 = 급여.사원번호;
+----+--------------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+
| 1 | PRIMARY | 사원 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 1 | PRIMARY | 급여 | NULL | ref | PRIMARY | PRIMARY | 4 | const | 17 | 100.00 | NULL |
| 2 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+--------------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+
select_type
SELECT 문의 유형이다. FROM에 위치하는지, 서브쿼리인지, UNION 절로 묶인 SELECT 인지를 나타낸다.
select_type는 종류가 많다.(SIMPLE, PRIMARY, SUBQUERY, DEPENDENT SUBQUERY, UNION,...) 그중 몇 가지를 살펴보자.
select_type - SIMPLE
UNION, 내부 쿼리가 없는 SELECT 문이다. (단순한 SELECT 구문)
EXPLAIN
SELECT * FROM 사원 WHERE 사원번호 = 100000;
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | 사원 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
select_type - PRIMARY
서브쿼리가 포함된 SQL문에서 첫 번째 SELECT 문에 표현된다. 서브쿼리를 감싸는 외부 쿼리이거나 UNION이 포함된 SQL 문의 첫 번째 SELECT에 표시된다.
아래 예는 사원 테이블에 스칼라 서브쿼리(카운트)의 예이다. 첫 번째 SELECT 되는 사원 테이블에 PRIMARY가 표시된다.
EXPLAIN
SELECT 사원.사원번호, 사원.이름, 사원.성,
(SELECT MAX(부서번호)
FROM 부서사원_매핑 as 매핑 WHERE 매핑.사원번호 = 사원.사원번호) 카운트
FROM 사원
WHERE 사원.사원번호 = 100001;
+----+--------------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+
| 1 | PRIMARY | 사원 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 2 | DEPENDENT SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+--------------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+
아래 예는 UNION ALL 구문이다. 첫 번째 테이블 사원1에 PRIMARY가 표시된다.
EXPLAIN
SELECT 사원1.사원번호, 사원1.이름, 사원1.성
FROM 사원 as 사원1
WHERE 사원1.사원번호 = 100001
UNION ALL
SELECT 사원2.사원번호, 사원2.이름, 사원2.성
FROM 사원 as 사원2
WHERE 사원2.사원번호 = 100002;
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | PRIMARY | 사원1 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
| 2 | UNION | 사원2 | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
select_type - SUBQUERY
독립적으로 수행되는 서브쿼리다. 스칼라 서브쿼리와 WHERE 절의 중첩 서브쿼리일 때 표시된다.
EXPLAIN
SELECT (SELECT COUNT(*)
FROM 부서사원_매핑 as 매핑
) as 카운트,
(SELECT MAX(연봉)
FROM 급여
) as 급여;
+----+-------------+--------+------------+-------+---------------+----------------+---------+------+---------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+----------------+---------+------+---------+----------+----------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 3 | SUBQUERY | 급여 | NULL | ALL | NULL | NULL | NULL | NULL | 2838065 | 100.00 | NULL |
| 2 | SUBQUERY | 매핑 | NULL | index | NULL | I_부서번호 | 12 | NULL | 331143 | 100.00 | Using index |
+----+-------------+--------+------------+-------+---------------+----------------+---------+------+---------+----------+----------------+
select_type - DEPENDENT SUBQUERY
UNION 또는 UNION ALL을 사용하는 서브쿼리가 메인테이블에 영향을 받는 경우이다.
아래 예는 UNION으로 연결된 첫 번째 테이블(사원1)이 독립적으로 수행하지 못하고 메인테이블(사원)로부터 값을 하나씩 받아 (AND 사원1.사원번호 = 관리자. 사원번호) 받는다. 성능적으로 불리한 SQL 문이다.
EXPLAIN
SELECT 관리자.부서번호,
( SELECT 사원1.이름
FROM 사원 AS 사원1
WHERE 성별= 'F'
AND 사원1.사원번호 = 관리자.사원번호
UNION ALL
SELECT 사원2.이름
FROM 사원 AS 사원2
WHERE 성별= 'M'
AND 사원2.사원번호 = 관리자.사원번호
) AS 이름
FROM 부서관리자 AS 관리자;
+----+--------------------+-----------+------------+--------+----------------------+----------------+---------+-------------------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+-----------+------------+--------+----------------------+----------------+---------+-------------------------------+------+----------+-------------+
| 1 | PRIMARY | 관리자 | NULL | index | NULL | I_부서번호 | 12 | NULL | 24 | 100.00 | Using index |
| 2 | DEPENDENT SUBQUERY | 사원1 | NULL | eq_ref | PRIMARY,I_성별_성 | PRIMARY | 4 | tuning.관리자.사원번호 | 1 | 50.00 | Using where |
| 3 | DEPENDENT UNION | 사원2 | NULL | eq_ref | PRIMARY,I_성별_성 | PRIMARY | 4 | tuning.관리자.사원번호 | 1 | 50.00 | Using where |
+----+--------------------+-----------+------------+--------+----------------------+----------------+---------+-------------------------------+------+----------+-------------+
table
테이블명을 표시한다.
실행 계획 정보에서 테이블명이나 alias를 출력하고, 서브쿼리나 임시테이블을 만들 때는 <subquery#> 나 <derived#>라고 출력한다.
EXPLAIN
SELECT 사원.사원번호, 급여.연봉
FROM 사원,
(SELECT 사원번호, MAX(연봉) as 연봉
FROM 급여
WHERE 사원번호 BETWEEN 10001 AND 20000
GROUP BY 사원번호 ) as 급여
WHERE 사원.사원번호 = 급여.사원번호;
+----+-------------+------------+------------+--------+------------------------+---------+---------+---------------------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+--------+------------------------+---------+---------+---------------------+--------+----------+-------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 184756 | 100.00 | NULL |
| 1 | PRIMARY | 사원 | NULL | eq_ref | PRIMARY | PRIMARY | 4 | 급여.사원번호 | 1 | 100.00 | Using index |
| 2 | DERIVED | 급여 | NULL | range | PRIMARY,I_사용여부 | PRIMARY | 4 | NULL | 184756 | 100.00 | Using where |
+----+-------------+------------+------------+--------+------------------------+---------+---------+---------------------+--------+----------+-------------+
partitions
파티션을 나눈 경우 표시된다.
type
테이블의 데이터를 어떻게 찾을지에 관한 정보다. 테이블 풀 스캔할지, 인덱스 탈 지 등 중요한 정보다.
type - system
테이블에 데이터가 없거나 한 개만 있는 경우 표시된다. 성능이 가장 좋다.
type - const
고유 인덱스나 기본 키를 사용하여 단 1건의 데이터만 접근하여 얻은 경우 표시된다. 인덱스를 사용하여 1건만 나왔기에 성능이 좋다.
ststem은 실제 데이터가 1건이고, const는 많은 데이터 중 인덱스 타고 검색했을 때 경우이다.
EXPLAIN
SELECT *
FROM 사원
WHERE 사원번호 = 10001;
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | 사원 | NULL | **const** | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
type - eq_ref
조인 시 드라이빙 테이블이 드리븐 테이블 접근하여 고유 인덱스나 기본 키를 사용하여 단 1건의 데이터를 조회할 때 표시된다.
조인 키가 드리븐 테이블에 유일하므로 조인 시 성능이 가장 좋다.
EXPLAIN
SELECT 매핑.사원번호, 부서.부서번호, 부서.부서명
FROM 부서사원_매핑 as 매핑,
부서
WHERE 매핑.부서번호 = 부서.부서번호
AND 매핑.사원번호 BETWEEN 100001 AND 100010;
+----+-------------+--------+------------+--------+------------------------+---------+---------+----------------------------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+--------+------------------------+---------+---------+----------------------------+------+----------+--------------------------+
| 1 | SIMPLE | 매핑 | NULL | range | PRIMARY,I_부서번호 | PRIMARY | 4 | NULL | 12 | 100.00 | Using where; Using index |
| 1 | SIMPLE | 부서 | NULL | **eq_ref** | PRIMARY | PRIMARY | 12 | tuning.매핑.부서번호 | 1 | 100.00 | NULL |
+----+-------------+--------+------------+--------+------------------------+---------+---------+----------------------------+------+----------+--------------------------+
type - ref
eq_ref와 유사하다. 조인할 때 드리븐 테이블의 데이터 접근 범위가 2개 이상일 경우 또는 WHERE 절의 비교 연산자(<, >, =)에 표시된다.
드리븐 테이블에 접근 데이터가 2개 이상인 경우는 드라이빙-드리븐가 일대다 관계이다.
EXPLAIN
SELECT *
FROM 사원
WHERE 입사일자 = '1985-11-21';
+----+-------------+--------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | 사원 | NULL | ref | I_입사일자 | I_입사일자 | 3 | const | 119 | 100.00 | NULL |
+----+-------------+--------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
type - ref_or_null
ref와 유사하다. SQL 문의 IS NULL 구문을 수행 시 인덱스 활용할 때 표시된다.
type - range
테이블 내 연속된 데이터 조회할 때(비교 연산자, IS NULL, BETWEEN, IN 등 범위 스캔) 표시된다.
type - fulltext, index_merge 생략
type - index
인덱스 풀 스캔이다. 물리적인 인덱스 블록을 처음부터 끝까지 훑는다. 인덱스 블록은 보통 테이블보다 작으므로 테이블 풀 스캔 방식보다 빠를 수 있다.
EXPLAIN
SELECT 사원번호
FROM 직급
WHERE 직급명 = 'Manager';
+----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------+
| 1 | SIMPLE | 직급 | NULL | index | PRIMARY | PRIMARY | 159 | NULL | 442248 | 10.00 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------+
type - ALL
테이블 풀 스캔이다. 활용할 인덱스가 없거나 인덱스 사용이 비효율적이라고 옵티마이저의 판단 시 사용된다. (일반적으로 ALL 일 때 인덱스를 활용하도록 하면 더 빠르다. 하지만 전체 데이터 10~20% 조회할 땐 오히려 ALL 성능이 더 좋을 수 있다.)
EXPLAIN
SELECT * FROM 사원;
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------+
| 1 | SIMPLE | 사원 | NULL | ALL | NULL | NULL | NULL | NULL | 299335 | 100.00 | NULL |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------+
possible_keys
옵티마지어가 사용할 수 있는 인덱스 목록 표시한다. 실제 사용된 것이 아닌 poosible 인덱스이다.
key
실제로 사용한 인덱스(기본키 또는 인덱스명)이다. 비효율적인 인덱스를 사용했거나 인덱스 사용하지 않은 경우(NULL) 튜닝 대상이다.
EXPLAIN
SELECT 사원번호
FROM 직급
WHERE 직급명 = 'Manager';
+----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------+
| 1 | SIMPLE | 직급 | NULL | index | **PRIMARY** | PRIMARY | 159 | NULL | 442248 | 10.00 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------+
# type : ALL, key : NULL 테이블 풀 스캔이다.
EXPLAIN
SELECT * FROM 사원;
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------+
| 1 | SIMPLE | 사원 | NULL | ALL | NULL | NULL | NULL | NULL | 299335 | 100.00 | NULL |
+----+-------------+--------+------------+------+---------------+------+---------+------+--------+----------+-------+
key_len
사용한 인덱스 바이트 수이다. (UTF-8 기준 INT는 4바이트, VARCHAR는 3바이트)
사용한 인덱스가 INT라면 4로 나온다. 복합 인덱스라면 인덱스를 모두 더한 값이 나온다.
ref
조인할 때 어떤 테이블에 액세스 되었는지 표시된다.
rows
SQL 문을 수행할 때 접근한 데이터의 모든 행(row) 수를 나타내는 예측 항목이다. (맨 처음도 말했지만 EXPLAIN ANALYZE를 제외하곤 EXPLAIN은 MYSQL 통계 정보를 가지고 예측한 값이다. 실제로 SQL 문을 실행한 뒤의 실행 계획이 아니다.)
SQL 문의 실제 결과와 rows가 차이가 큰 경우 MySQL 엔진에서 데이터를 많이 가져왔다는 뜻으로 튜닝 대상이 될 수 있다.
filtered
SQL 문을 통해 MySQL 엔진이 스토리지 엔진으로부터 가져온 데이터를 필터 조건에 따라 제거된 비율이다. (단위 %)
스토리지 엔진으로부터 100건 가져왔고 이후 MySQL 엔진의 필터 조건을 거쳐 10건이 필터링된다면 filtered 값은 10.00이다.
Extra
SQL 문이 어떻게 수행될지 추가 정보를 준다. 세미콜론(;)으로 구분하며 30여 가지가 있다.
몇 가지만 살펴보자.
Extra - Distinct
중복이 제거되어 유일한 값 찾은 경우, distinct 키워드나 union 구문이 포함된 경우 표시된다.
Extra - Using where
WHERE 절의 필터 조건 사용해 MySQL 엔진으로 가져온 데이터를 추출할 경우 표시된다.
Extra - Using temporary
임시 테이블 생성하겠다는 의미이다.
데이터 가져와 저장한 뒤 정렬 작업 수행하거나 중복 제거 작업 수행할 때 표시된다.
다시 말해 distinct, group by, order by 구문이 포함된 경우 표시된다.
임시 테이블 메모리 생성하거나 메모리 영역 초과하여 디스크에 임시 테이블 생성하면 Using temporary는 성능 저하의 원인 될 수 있고, 이 항목 정보 출력되는 쿼리는 SQL 튜닝 대상이다.
Extra - Using index
인덱스만 읽어서 SQL 문의 요청사항 처리한 경우이다. (커버링 인덱스(covering index) 방식이라고도 함)
아래 예는 사원 번호 100,000인 직원의 직급명 구할 때, 직급 테이블 기본키 사원번호, 직급명, 시작일자 순서 기본기만 활용해 가져오는 경우이다.
explain
select 직급명
from 직급
where 사원번호 = 10000
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | 직급 | NULL | ref | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | **Using index** |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
⛓ Reference
Real MySQL 8.0 8.0 1권, 10장 실행계획 - 백은빈, 이성욱 저
업무에 바로 쓰는 SQL 튜닝, 3장 SQL 튜닝의 실행 계획 파헤치기 - 양바른 저
업무에 바로 쓰는 SQL 튜닝 3장 소스코드(공식 Github 저장소)
코드스쿼드 - 호눅스 마스터클래스 SQL 최적화
MySQL 8.0 docs : 8.8.2 EXPLAIN Output Format
MySQL 8.0 Docs : 7.5 Tutorial: Using Explain to Improve Query Performance
'🏢 DB > 🔑 RDB' 카테고리의 다른 글
| 인덱스와 관련된 몇 가지 SQL 튜닝 예제 (결합 인덱스, 인덱스 사용할 때 느린 경우, 인덱스가 없는 경우 등) (0) | 2022.05.19 |
|---|---|
| MySQL Server 아키텍처와 스레딩 (1) | 2022.05.12 |
댓글