Created
March 31, 2024
Created by
D
DaEun KimTags
Mysql
Property
트리 형식으로 실행계획 조회
mysql> EXPLAIN FORMAT=TREE SELECT * FROM employees JOIN salaries ON employees.emp_no = salaries.emp_no WHERE first_name = 'ABC'\G
*************************** 1. row ***************************
EXPLAIN: -> Nested loop inner join (cost=3.03 rows=10)
-> Index lookup on employees using ix_firstname (first_name='ABC') (cost=1.07 rows=1)
-> Index lookup on salaries using PRIMARY (emp_no=employees.emp_no) (cost=1.96 rows=10)
1 row in set (0.01 sec)
JSON 형식으로 실행계획 조회
mysql> EXPLAIN FORMAT=JSON SELECT * FROM employees JOIN salaries ON employees.emp_no = salaries.emp_no WHERE first_name = 'ABC'\G
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "3.03"
},
"nested_loop": [
{
"table": {
"table_name": "employees",
"access_type": "ref",
"possible_keys": [
"PRIMARY",
"ix_firstname"
],
"key": "ix_firstname",
"used_key_parts": [
"first_name"
],
"key_length": "58",
"ref": [
"const"
],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 1,
"filtered": "100.00",
"cost_info": {
"read_cost": "0.97",
"eval_cost": "0.10",
"prefix_cost": "1.07",
"data_read_per_join": "136"
},
"used_columns": [
"emp_no",
"birth_date",
"first_name",
"last_name",
"gender",
"hire_date"
]
}
},
{
"table": {
"table_name": "salaries",
"access_type": "ref",
"possible_keys": [
"PRIMARY"
],
"key": "PRIMARY",
"used_key_parts": [
"emp_no"
],
"key_length": "4",
"ref": [
"employees.employees.emp_no"
],
"rows_examined_per_scan": 9,
"rows_produced_per_join": 9,
"filtered": "100.00",
"cost_info": {
"read_cost": "1.00",
"eval_cost": "0.97",
"prefix_cost": "3.03",
"data_read_per_join": "154"
},
"used_columns": [
"emp_no",
"salary",
"from_date",
"to_date"
]
}
}
]
}
}
1 row in set, 1 warning (0.01 sec)쿼리의 실행 시간 조회
- 쿼리의 실행 시간은
EXPLAIN ANALYZE명령을 통해 조회 가능하다. EXPLAIN ANALYZE명령은 트리 형식으로만 실행 계획을 제공한다.
mysql> show index from salaries;
+----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| salaries | 0 | PRIMARY | 1 | emp_no | A | 288076 | NULL | NULL | | BTREE | | | YES | NULL |
| salaries | 0 | PRIMARY | 2 | from_date | A | 2779584 | NULL | NULL | | BTREE | | | YES | NULL |
| salaries | 1 | ix_salary | 1 | salary | A | 111504 | NULL | NULL | | BTREE | | | YES | NULL |
+----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.05 sec)mysql> show index from employees;
+-----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| employees | 0 | PRIMARY | 1 | emp_no | A | 299512 | NULL | NULL | | BTREE | | | YES | NULL |
| employees | 1 | ix_hiredate | 1 | hire_date | A | 5145 | NULL | NULL | | BTREE | | | YES | NULL |
| employees | 1 | ix_gender_birthdate | 1 | gender | A | 1 | NULL | NULL | | BTREE | | | YES | NULL |
| employees | 1 | ix_gender_birthdate | 2 | birth_date | A | 8230 | NULL | NULL | | BTREE | | | YES | NULL |
| employees | 1 | ix_firstname | 1 | first_name | A | 1304 | NULL | NULL | | BTREE | | | YES | NULL |
+-----------+------------+---------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
5 rows in set (0.00 sec)mysql> EXPLAIN ANALYZE
-> SELECT employees.emp_no, avg(s.salary)
-> FROM employees JOIN salaries AS s ON employees.emp_no = s.emp_no
-> WHERE employees.first_name = 'Matt'
-> AND s.salary > 50000
-> AND s.from_date <= '1990-01-01'
-> AND s.to_date > '1990-01-01'
-> GROUP BY employees.hire_date;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1. -> Table scan on <temporary> (actual time=0.015..0.035 rows=48 loops=1)
2. -> Aggregate using temporary table (actual time=19.953..19.998 rows=48 loops=1)
3. -> Nested loop inner join (cost=659.26 rows=125) (actual time=1.898..19.320 rows=48 loops=1)
4. -> Index lookup on employees using ix_firstname (first_name='Matt') (cost=208.57 rows=233) (actual time=1.764..4.095 rows=233 loops=1)
5. -> Filter: ((s.salary > 50000) and (s.from_date <= DATE'1990-01-01') and (s.to_date > DATE'1990-01-01')) (cost=0.97 rows=1) (actual time=0.056..0.063 rows=0 loops=233)
6. -> Index lookup on s using PRIMARY (emp_no=employees.emp_no) (cost=0.97 rows=10) (actual time=0.038..0.052 rows=10 loops=233)
|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)트리 형식의 실행 계획은 아래 기준으로 읽는다.
- 들여쓰기가 다른 레벨인 라인이 2개 이상 있으면 안쪽에 있는 라인 먼저 실행
- 들여쓰기가 같은 레벨인 라인이 2개 이상 있으면 상단에 있는 라인 먼저 실행
위 실행 계획의 경우 4 → 6 → 5 → 3 → 2 → 1 순서로 쿼리들을 수행한다.
4. Index lookup on employees using ix_firstname (first_name='Matt') (cost=208.57 rows=233) (actual time=1.764..4.095 rows=233 loops=1)- employees 테이블의
ix_firstname인덱스를 스캔하면서first_name = ‘Matt’조건을 만족하는지 확인. - employees 테이블이 드라이빙 테이블이라
ix_firstname인덱스는 1회만 스캔한다. (loops=1) - 조건을 만족하는 인덱스 레코드는 233개.
4번에서 찾은 233개의 레코드 수 만큼 6, 5번 과정을 수행한다.
6. Index lookup on s using PRIMARY (emp_no=employees.emp_no) (cost=0.97 rows=10) (actual time=0.038..0.052 rows=10 loops=233)- employees 테이블의
ix_firstname인덱스 레코드에 있는 rowID 를 통해 employees 데이터 레코드 조회. - 데이터 레코드에 있는
emp_no필드를 가지고 salaries 테이블의emp_no컬럼에 붙은 PRIMARY (emp_no, hire_date 조합의 복합) 인덱스 lookup. - PRIMARY 인덱스 레코드에는 salaries 테이블의 rowID 가 있다.
5. Filter: ((s.salary > 50000) and (s.from_date <= DATE'1990-01-01') and (s.to_date > DATE'1990-01-01')) (cost=0.97 rows=1) (actual time=0.056..0.063 rows=0 loops=233)- rowID 를 통해 찾은 salaries 데이터 레코드가
(s.from_date <= DATE'1990-01-01') and (s.to_date > DATE'1990-01-01'))조건을 만족하는 지 확인. - 루프 당 평균적으로 읽은 조건만족 레코드 갯수가 1보다 작아서
rows=0으로 표시되는 듯.
3. Nested loop inner join (cost=659.26 rows=125) (actual time=1.898..19.320 rows=48 loops=1)first_name = ‘Matt’조건을 만족하는 employees 데이터 레코드와(s.from_date <= DATE'1990-01-01') and (s.to_date > DATE'1990-01-01'))조건을 만족하는 salaries 데이터 레코드 조인.- 두 조건을 만족하는 레코드는 48개.
2. Aggregate using temporary table (actual time=19.953..19.998 rows=48 loops=1)- 조인한 레코드들을 집계하여 임시 테이블에 저장.
1. Table scan on <temporary> (actual time=0.015..0.035 rows=48 loops=1)- 임시 테이블 읽기.
EXPLAIN ANALYZE 에서 제공하는 필드들 의미
actual time = a..b
첫번째 레코드를 읽는 데에 루프 당 평균적으로 a 밀리초 소요되었고, 마지막 레코드를 읽기까지 루프 당 평균적으로 b 밀리초 소요되었음을 의미한다.
→ 실제 총 소요 시간은 루프 횟수 X actual time 으로 봐야 한다.
rows = N
루프 당 평균적으로 읽은 레코드 갯수가 N개인 것을 의미한다.
loops = N
작업이 반복된 횟수가 N회인 것을 의미한다.