트리 형식으로 실행계획 조회
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회인 것을 의미한다.