[MySQL] 실행 계획 확인하는 명령어

MySQL 옵티마이저는 사용자가 요청한 쿼리를 최소 비용으로 실행하기 위한 실행 계획을 세운다. 그러나 옵티마이저가 언제나 최적의 실행 계획을 수립할 것이라는 보장은 없으므로, MySQL 서버는 사용자가 실행 계획을 확인하고 보완할 수 있는 방법을 제공한다.

이번 시간에는 MySQL 옵티마이저가 쿼리에 대해 세운 실행 계획을 확인하는 다양한 명령어에 대해 알아보자.

EXPLAIN / DESC / DESCRIBE

EXPLAIN, DESC, DESCRIBE 명령어를 사용하면 실행계획을 테이블 형태로 확인할 수 있다.

EXPLAIN
SELECT *
FROM employees e
  INNER JOIN salaries s ON s.emp_no=e.emp_no
WHERE first_name='Matt';

실행 결과

+----+-------------+-------+------------+------+----------------------+--------------+---------+--------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys        | key          | key_len | ref                | rows | filtered | Extra |
+----+-------------+-------+------------+------+----------------------+--------------+---------+--------------------+------+----------+-------+
|  1 | SIMPLE      | e     | NULL       | ref  | PRIMARY,ix_firstname | ix_firstname | 58      | const              |  233 |   100.00 | NULL  |
|  1 | SIMPLE      | s     | NULL       | ref  | PRIMARY              | PRIMARY      | 4       | employees.e.emp_no |    9 |   100.00 | NULL  |
+----+-------------+-------+------------+------+----------------------+--------------+---------+--------------------+------+----------+-------+

FORMAT 옵션을 사용하여 테이블 형태 뿐만 아니라 TREE, JSON 포맷으로 표시할 수 있다. 

EXPLAIN FORMAT=TREE

EXPLAIN FORMAT=TREE
SELECT *
FROM employees e
  INNER JOIN salaries s ON s.emp_no=e.emp_no
WHERE first_name='Matt';

TREE 포맷으로 출력한 실행 계획

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Nested loop inner join  (cost=438 rows=2200)
    -> Index lookup on e using ix_firstname (first_name='Matt')  (cost=81.5 rows=233)
    -> Index lookup on s using PRIMARY (emp_no=e.emp_no)  (cost=0.59 rows=9.44)
 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

EXPLAIN FORMAT=JSON

EXPLAIN FORMAT=JSON
SELECT *
FROM employees e
  INNER JOIN salaries s ON s.emp_no=e.emp_no
WHERE first_name='Matt';

JSON 포맷으로 출력한 실행 계획

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "438.12"
    },
    "nested_loop": [
      {
        "table": {
          "table_name": "e",
          "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": 233,
          "rows_produced_per_join": 233,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "58.25",
            "eval_cost": "23.30",
            "prefix_cost": "81.55",
            "data_read_per_join": "30K"
          },
          "used_columns": [
            "emp_no",
            "birth_date",
            "first_name",
            "last_name",
            "gender",
            "hire_date"
          ]
        }
      },
      {
        "table": {
          "table_name": "s",
          "access_type": "ref",
          "possible_keys": [
            "PRIMARY"
          ],
          "key": "PRIMARY",
          "used_key_parts": [
            "emp_no"
          ],
          "key_length": "4",
          "ref": [
            "employees.e.emp_no"
          ],
          "rows_examined_per_scan": 9,
          "rows_produced_per_join": 2200,
          "filtered": "100.00",
          "cost_info": {
            "read_cost": "136.53",
            "eval_cost": "220.04",
            "prefix_cost": "438.12",
            "data_read_per_join": "34K"
          },
          "used_columns": [
            "emp_no",
            "salary",
            "from_date",
            "to_date"
          ]
        }
      }
    ]
  }
} |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

EXPLAIN ANALYZE

EXPLAIN 명령과 달리 실행 계획만 추출하는 것이 아닌, 실제로 쿼리를 실행하면서 사용된 실행 계획과 단계 별로 소요된 시간을 보여준다.

출력 형식은 TREE 포맷으로 고정되어 있기 때문에 FORMAT 옵션을 사용할 수는 없다.

EXPLAIN ANALYZE
SELECT *
FROM employees e
  INNER JOIN salaries s ON s.emp_no=e.emp_no
WHERE first_name='Matt';

EXPLAIN ANALYZE를 이용한 실행 계획 출력 결과

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                      |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Nested loop inner join  (cost=438 rows=2200) (actual time=5.43..22.7 rows=2221 loops=1)
    -> Index lookup on e using ix_firstname (first_name='Matt')  (cost=81.5 rows=233) (actual time=5.21..12.3 rows=233 loops=1)
    -> Index lookup on s using PRIMARY (emp_no=e.emp_no)  (cost=0.59 rows=9.44) (actual time=0.0393..0.0429 rows=9.53 loops=233)
 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

실행 계획에 포함된 속성들의 의미를 알아보자.

• actual time: 단계별로 실제 소요된 시간. 첫번째 숫자 값은 첫번째 레코드를 가져오는데 걸린 평균 시간(밀리초), 두번째 숫자 값은 마지막 레코드를 가져오는데 걸린 평균 시간(밀리초)
• loops: 작업이 반복된 횟수
• rows: 한번의 loop 반복에서 평균적으로 처리한 레코드 건수

actual time, loops, rows 속성의 의미를 참고하여 위 실행 계획을 해석하면 다음과 같다.

첫번째 줄에서 전체적인 실행 계획에 대한 정보를 확인할 수 있다. 네스티드 루프 조인 방식으로 조인이 실행되었고, 조인 테이블의 첫번째 레코드를 가져오는데 걸린 시간은 5.42밀리초, 마지막 레코드를 가져오는데 걸린 시간은 22.7밀리초이다. 한번의 루프에서 총 2221건의 레코드를 처리하였다.

두번째 줄은 네스티드 루프 조인의 첫번째 단계에 대한 실행 계획이다. employees 테이블에서 WHERE 절의 조건(first_name=’Matt’)에 해당하는 레코드들을 ix_firstname 인덱스를 사용하여 가져왔으며, 첫번째 레코드를 가져오는데 걸린 시간은 5.21밀리초, 마지막 레코드를 가져오는데 걸린 시간은 12.3밀리초이다. 한번의 루프에서 총 233건의 레코드를 처리하였다.

세번째 줄은 네스티드 루프 조인의 두번째 단계에 대한 실행 계획이다. 앞선 단계에서 employees 테이블에서 찾은 233개의 레코드 각각에 대해 조인 조건(s.emp_no=e.emp_no)을 만족하는 레코드를 salaries 테이블에서 찾는다. 따라서 작업이 233회 반복(loops=233)된다. 각 반복에서 평균적으로 9.53건의 레코드를 처리하였으며, 첫번째 레코드를 가져오는데 걸린 시간은 평균적으로 0.0393밀리초, 마지막 레코드를 가져오는데 걸린 시간은 평균적으로 0.0429밀리초이다.