15.8.2 EXPLAIN Statement

15.8.2 EXPLAIN Statement

{EXPLAIN | DESCRIBE | DESC} tbl_name [col_name | wild]

{EXPLAIN | DESCRIBE | DESC} [explain_type] [INTO variable] {[schema_spec] explainable_stmt | FOR CONNECTION connection_id}

{EXPLAIN | DESCRIBE | DESC} ANALYZE [FORMAT = TREE] [schema_spec] select_statement

{EXPLAIN | DESCRIBE | DESC} ANALYZE FORMAT = JSON INTO variable [schema_spec] select_statement

explain_type: { FORMAT = format_name }

format_name: { TRADITIONAL | JSON | TREE }

explainable_stmt: { SELECT statement | TABLE statement | DELETE statement | INSERT statement | REPLACE statement | UPDATE statement }

schema_spec: FOR {SCHEMA | DATABASE} schema_name

DESCRIBE 와 EXPLAIN statement 는 동의어입니다. 실제로는 DESCRIBE keyword 가 테이블 구조에 대한 정보를 얻기 위해 더 자주 사용되는 반면, EXPLAIN 은 쿼리 실행 계획(즉, MySQL 이 쿼리를 어떻게 실행할 것인지에 대한 설명)을 얻는 데 사용됩니다.

다음 설명에서는 DESCRIBE 와 EXPLAIN keyword 를 이러한 사용에 맞게 사용하지만, MySQL 파서는 이 둘을 완전히 동의어로 처리합니다.

• Obtaining Table Structure Information

• Obtaining Execution Plan Information

• Obtaining Information with EXPLAIN ANALYZE

Obtaining Table Structure Information

DESCRIBE 는 테이블의 컬럼에 대한 정보를 제공합니다:

1mysql> DESCRIBE City;
2+------------+----------+------+-----+---------+----------------+
3| Field      | Type     | Null | Key | Default | Extra          |
4+------------+----------+------+-----+---------+----------------+
5| Id         | int(11)  | NO   | PRI | NULL    | auto_increment |
6| Name       | char(35) | NO   |     |         |                |
7| Country    | char(3)  | NO   | UNI |         |                |
8| District   | char(20) | YES  | MUL |         |                |
9| Population | int(11)  | NO   |     | 0       |                |
10+------------+----------+------+-----+---------+----------------+

DESCRIBE 는 SHOW COLUMNS 의 단축키입니다. 이들 statement 는 뷰에 대한 정보도 표시합니다. SHOW COLUMNS 에 대한 설명은 출력 컬럼에 대한 더 많은 정보를 제공합니다. 자세한 내용은 Section 15.7.7.6, “SHOW COLUMNS Statement” 를 참조하십시오.

기본적으로, DESCRIBE 는 테이블의 모든 컬럼에 대한 정보를 표시합니다. col_name 이 주어지면, 이는 테이블 내 컬럼의 이름입니다. 이 경우 statement 는 지정된 컬럼에 대한 정보만 표시합니다. wild 가 주어지면, 이는 패턴 문자열입니다. 이 문자열은 SQL % 와 _ 와일드카드 문자를 포함할 수 있습니다. 이 경우, statement 는 이름이 해당 문자열과 일치하는 컬럼에 대해서만 출력을 표시합니다. 문자열에 공백이나 기타 특수 문자가 포함되지 않는 한, 문자열을 따옴표로 둘러쌀 필요는 없습니다.

DESCRIBE statement 는 Oracle 과의 호환성을 위해 제공됩니다.

SHOW CREATE TABLE, SHOW TABLE STATUS, 그리고 SHOW INDEX statement 또한 테이블에 대한 정보를 제공합니다. Section 15.7.7, “SHOW Statements” 를 참조하십시오.

explain_format 시스템 변수는 테이블 컬럼에 대한 정보를 얻기 위해 EXPLAIN 을 사용할 때 출력에 아무런 영향을 미치지 않습니다.

Obtaining Execution Plan Information

EXPLAIN statement 는 MySQL 이 statement 를 어떻게 실행하는지에 대한 정보를 제공합니다:

• EXPLAIN 는 SELECT, DELETE, INSERT, REPLACE, UPDATE, 및 TABLE statement 와 함께 동작합니다.

• EXPLAIN 을 explainable statement 와 함께 사용하면, MySQL 은 옵티마이저로부터 statement 실행 계획에 대한 정보를 표시합니다. 즉, MySQL 은 테이블을 어떻게 조인하고 어떤 순서로 처리하는지를 포함하여, statement 를 어떻게 처리할 것인지 설명합니다. 실행 계획 정보를 얻기 위해 EXPLAIN 을 사용하는 방법에 대한 정보는 Section 10.8.2, “EXPLAIN Output Format” 을 참조하십시오.

• EXPLAIN 을 explainable statement 가 아니라 FOR CONNECTION connection_id 와 함께 사용하면, 지정된 커넥션에서 실행 중인 statement 에 대한 실행 계획을 표시합니다. Section 10.8.4, “Obtaining Execution Plan Information for a Named Connection” 를 참조하십시오.

• explainable statement 의 경우, EXPLAIN 은 SHOW WARNINGS 를 사용하여 표시할 수 있는 추가적인 실행 계획 정보를 생성합니다. Section 10.8.3, “Extended EXPLAIN Output Format” 를 참조하십시오.

• EXPLAIN 은 파티션된 테이블이 관련된 쿼리를 검사할 때 유용합니다. Section 26.3.5, “Obtaining Information About Partitions” 를 참조하십시오.

• FORMAT 옵션을 사용하여 출력 형식을 선택할 수 있습니다. TRADITIONAL 은 출력을 표 형식으로 제공합니다. 이 옵션이 없으면 이것이 기본입니다. JSON 형식은 정보를 JSON 형식으로 표시합니다. TREE 는 쿼리 처리에 대한 더 정밀한 설명을 제공하는 트리 형태의 출력을 제공하며, TRADITIONAL 형식보다 더 자세합니다. 이 형식은 해시 조인 사용을 보여 주는 유일한 형식이며( Section 10.2.1.4, “Hash Join Optimization” 참조), EXPLAIN ANALYZE 의 경우 항상 사용됩니다.

MySQL 9.5 에서는, EXPLAIN 에 FORMAT 옵션이 없을 때 사용되는 기본 출력 형식은 explain_format 시스템 변수의 값에 의해 결정됩니다. 이 변수의 정확한 효과는 이 절의 뒷부분에서 설명합니다.

MySQL 9.5 는 추가적인 INTO 옵션을 EXPLAIN FORMAT=JSON 과 함께 지원하여, 다음과 같이 JSON 형식의 출력을 사용자 변수에 저장할 수 있습니다:

1mysql> EXPLAIN FORMAT=JSON INTO @myselect
2      ->     SELECT name FROM a WHERE id = 2;
3Query OK, 0 rows affected (0.00 sec)
4
5mysql> SELECT @myselect\G
6*************************** 1. row ***************************
7@myex: {
8    "query_block": {
9      "select_id": 1,
10      "cost_info": {
11        "query_cost": "1.00"
12      },
13      "table": {
14        "table_name": "a",
15        "access_type": "const",
16        "possible_keys": [
17          "PRIMARY"
18        ],
19        "key": "PRIMARY",
20        "used_key_parts": [
21          "id"
22        ],
23        "key_length": "4",
24        "ref": [
25          "const"
26        ],
27        "rows_examined_per_scan": 1,
28        "rows_produced_per_join": 1,
29        "filtered": "100.00",
30        "cost_info": {
31          "read_cost": "0.00",
32          "eval_cost": "0.10",
33          "prefix_cost": "0.00",
34          "data_read_per_join": "408"
35        },
36        "used_columns": [
37          "id",
38          "name"
39        ]
40      }
41    }
42}
431 row in set (0.00 sec)

이는 어떤 explainable statement 와도 동작합니다 ( SELECT, TABLE, INSERT, UPDATE, REPLACE, 또는 DELETE). 여기에서는 UPDATE 와 DELETE statement 를 사용하는 예제를 보여 줍니다:

1mysql> EXPLAIN FORMAT=JSON INTO @myupdate
2      ->   UPDATE a SET name2 = "garcia" WHERE id = 3;
3Query OK, 0 rows affected (0.00 sec)
4
5mysql> EXPLAIN FORMAT=JSON INTO @mydelete
6      ->     DELETE FROM a WHERE name1 LIKE '%e%';
7Query OK, 0 rows affected (0.00 sec)
8
9mysql> SELECT @myupdate, @mydelete\G
10*************************** 1. row ***************************
11@myupdate: {
12    "query_block": {
13      "select_id": 1,
14      "table": {
15        "update": true,
16        "table_name": "a",
17        "access_type": "range",
18        "possible_keys": [
19          "PRIMARY"
20        ],
21        "key": "PRIMARY",
22        "used_key_parts": [
23          "id"
24        ],
25        "key_length": "4",
26        "ref": [
27          "const"
28        ],
29        "rows_examined_per_scan": 1,
30        "filtered": "100.00",
31        "attached_condition": "(`db`.`a`.`id` = 3)"
32      }
33    }
34}
35@mydelete: {
36    "query_block": {
37      "select_id": 1,
38      "table": {
39        "delete": true,
40        "table_name": "a",
41        "access_type": "ALL",
42        "rows_examined_per_scan": 2,
43        "filtered": "100.00",
44        "attached_condition": "(`db`.`a`.`name1` like '%e%')"
45      }
46    }
47}
481 row in set (0.00 sec)

이 값은 다른 JSON 값과 마찬가지로 MySQL JSON 함수로 다룰 수 있습니다. 다음 예제는 JSON_EXTRACT() 를 사용합니다:

1mysql> SELECT JSON_EXTRACT(@myselect, "$.query_block.table.key");
2+----------------------------------------------------+
3| JSON_EXTRACT(@myselect, "$.query_block.table.key") |
4+----------------------------------------------------+
5| "PRIMARY"                                          |
6+----------------------------------------------------+
71 row in set (0.01 sec)
8
9mysql> SELECT JSON_EXTRACT(@myupdate, "$.query_block.table.access_type") AS U_acc,
10      ->        JSON_EXTRACT(@mydelete, "$.query_block.table.access_type") AS D_acc;
11+---------+-------+
12| U_acc   | D_acc |
13+---------+-------+
14| "range" | "ALL" |
15+---------+-------+
161 row in set (0.00 sec)

복잡한 statement 의 경우, JSON 출력은 상당히 커질 수 있습니다. 특히, 이를 읽을 때 닫는 괄호와 여는 괄호를 짝짓기가 어려울 수 있습니다. JSON 구조의 key 가 있는 경우, 닫는 괄호 근처에 key 를 반복해서 표시하도록 하려면, end_markers_in_json=ON 으로 설정하십시오. 이 설정은 출력 읽기를 더 쉽게 하지만, 동시에 JSON 을 유효하지 않게 만들어 JSON 함수가 에러를 발생하게 한다는 점에 유의해야 합니다.

또한 Section 14.17, “JSON Functions” 를 참조하십시오.

FORMAT=JSON 을 명시적으로 포함하지 않고 INTO 절을 사용하려고 시도하면 EXPLAIN 이 ER_EXPLAIN_INTO_IMPLICIT_FORMAT_NOT_SUPPORTED 에 의해 거부됩니다. 이는 explain_format 시스템 변수의 현재 값과는 관계없이 적용됩니다.

INTO 절은 FOR CONNECTION 과 함께 지원되지 않습니다.

INTO 는 또한 explain_json_format_version=1 인 경우의 EXPLAIN ANALYZE 와는 함께 지원되지 않습니다.

주의

어떤 이유에서든 분석할 statement 가 거부되면, 사용자 변수는 업데이트되지 않습니다.

• MySQL 9.5 는 FOR SCHEMA 절을 지원하며, 이를 사용하면 EXPLAIN 이 분석할 statement 가 지정된 데이터베이스에서 실행된 것처럼 동작하게 할 수 있습니다. FOR DATABASE 는 동의어로 지원됩니다. 간단한 사용 예는 다음과 같습니다:

1mysql> USE b;
2Database changed
3mysql> CREATE SCHEMA s1;
4Query OK, 1 row affected (0.01 sec)
5
6mysql> CREATE SCHEMA s2;
7Query OK, 1 row affected (0.01 sec)
8
9mysql> USE s1;
10Database changed
11mysql> CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 INT NOT NULL);
12Query OK, 0 rows affected (0.04 sec)
13
14mysql> USE s2;
15Database changed
16mysql> CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 INT NOT NULL, KEY i1 (c2));
17Query OK, 0 rows affected (0.04 sec)
18
19mysql> USE b;
20Database changed
21mysql> EXPLAIN FORMAT=TREE FOR SCHEMA s1 SELECT * FROM t WHERE c2 > 50\G
22*************************** 1. row ***************************
23EXPLAIN: -> Filter: (t.c2 > 50)  (cost=0.35 rows=1)
24      -> Table scan on t  (cost=0.35 rows=1)
25
261 row in set (0.00 sec)
27
28mysql> EXPLAIN FORMAT=TREE FOR SCHEMA s2 SELECT * FROM t WHERE c2 > 50\G
29*************************** 1. row ***************************
30EXPLAIN: -> Filter: (t.c2 > 50)  (cost=0.35 rows=1)
31      -> Covering index scan on t using i1  (cost=0.35 rows=1)
32
331 row in set (0.00 sec)

데이터베이스가 존재하지 않으면, statement 는 ER_BAD_DB_ERROR 로 거부됩니다. 사용자가 statement 를 실행할 수 있는 필요한 권한을 가지고 있지 않으면, ER_DBACCESS_DENIED_ERROR 로 거부됩니다.

FOR SCHEMA 는 FOR CONNECTION 과 호환되지 않습니다.

EXPLAIN 을 사용하려면, 설명 대상 statement 를 실행하는 데 필요한 것과 동일한 권한이 필요합니다. 추가로, EXPLAIN 은 설명되는 뷰에 대해 SHOW VIEW 권한이 필요합니다. EXPLAIN ... FOR CONNECTION 은, 지정된 커넥션이 다른 사용자의 것인 경우 PROCESS 권한도 필요합니다.

explain_format 시스템 변수는 쿼리 실행 계획을 표시하기 위해 EXPLAIN 을 사용할 때 출력 형식을 결정합니다. 이 변수는 FORMAT 옵션과 함께 사용되는 값들에 TRADITIONAL 의 동의어인 DEFAULT 를 추가하여 가질 수 있습니다. 다음 예제는 world 데이터베이스의 country 테이블을 사용하며, 이 데이터베이스는 MySQL: Other Downloads 에서 얻을 수 있습니다:

1mysql> USE world; # Make world the current database
2Database changed

explain_format 의 값을 확인해 보면, 이 변수가 기본값을 가지고 있고, 따라서 EXPLAIN (FORMAT 옵션 없이)이 전통적인 표 형식 출력을 사용하고 있음을 알 수 있습니다:

1mysql> SELECT @@explain_format;
2+------------------+
3| @@explain_format |
4+------------------+
5| TRADITIONAL      |
6+------------------+
71 row in set (0.00 sec)
8
9mysql> EXPLAIN SELECT Name FROM country WHERE Code Like 'A%';
10+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
11| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
12+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
13|  1 | SIMPLE      | country | NULL       | range | PRIMARY       | PRIMARY | 12      | NULL |   17 |   100.00 | Using where |
14+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
151 row in set, 1 warning (0.00 sec)

explain_format 값을 TREE 로 설정한 후 동일한 EXPLAIN statement 를 다시 실행하면, 출력은 트리 형식을 사용합니다:

1mysql> SET @@explain_format=TREE;
2Query OK, 0 rows affected (0.00 sec)
3
4mysql> SELECT @@explain_format;
5+------------------+
6| @@explain_format |
7+------------------+
8| TREE             |
9+------------------+
101 row in set (0.00 sec)
11
12mysql> EXPLAIN SELECT Name FROM country WHERE Code LIKE 'A%';
13+--------------------------------------------------------------------------------------------------------------+
14| EXPLAIN                                                                                                      |
15+--------------------------------------------------------------------------------------------------------------+
16| -> Filter: (country.`Code` like 'A%')  (cost=3.67 rows=17)
17    -> Index range scan on country using PRIMARY over ('A' <= Code <= 'A????????')  (cost=3.67 rows=17)  |
18+--------------------------------------------------------------------------------------------------------------+
191 row in set, 1 warning (0.00 sec)

앞에서 언급했듯이, FORMAT 옵션은 이 설정을 오버라이드 합니다. 동일한 EXPLAIN statement 를 FORMAT=TREE 대신 FORMAT=JSON 을 사용하여 실행하면 이를 확인할 수 있습니다:

1mysql> EXPLAIN FORMAT=JSON SELECT Name FROM country WHERE Code LIKE 'A%';
2+------------------------------------------------------------------------------+
3| EXPLAIN                                                                      |
4+------------------------------------------------------------------------------+
5| {
6  "query_block": {
7    "select_id": 1,
8    "cost_info": {
9      "query_cost": "3.67"
10    },
11    "table": {
12      "table_name": "country",
13      "access_type": "range",
14      "possible_keys": [
15        "PRIMARY"
16      ],
17      "key": "PRIMARY",
18      "used_key_parts": [
19        "Code"
20      ],
21      "key_length": "12",
22      "rows_examined_per_scan": 17,
23      "rows_produced_per_join": 17,
24      "filtered": "100.00",
25      "cost_info": {
26        "read_cost": "1.97",
27        "eval_cost": "1.70",
28        "prefix_cost": "3.67",
29        "data_read_per_join": "16K"
30      },
31      "used_columns": [
32        "Code",
33        "Name"
34      ],
35      "attached_condition": "(`world`.`country`.`Code` like 'A%')"
36    }
37  }
38}                                                                              |
39+------------------------------------------------------------------------------+
401 row in set, 1 warning (0.00 sec)

EXPLAIN 의 기본 출력을 표 형식으로 되돌리려면, explain_format 을 TRADITIONAL 로 설정하십시오. 또는, 동일한 효과를 갖는 DEFAULT 로 설정할 수도 있습니다. 다음과 같습니다:

1mysql> SET @@explain_format=DEFAULT;
2Query OK, 0 rows affected (0.00 sec)
3
4mysql> SELECT @@explain_format;
5+------------------+
6| @@explain_format |
7+------------------+
8| TRADITIONAL      |
9+------------------+
101 row in set (0.00 sec)

MySQL 9.5 는 JSON 출력 형식의 두 가지 버전을 지원합니다. Version 1 은 MySQL 9.4 이전에서 사용되던 선형 형식입니다. JSON 출력 형식의 Version 2 는 액세스 경로를 기반으로 하며, MySQL 9.5 부터 기본값입니다. 형식을 변경하려면 explain_json_format_version 서버 시스템 변수의 값을 변경하면 되며, 다음 예제에서는 앞선 예제에서 사용한 것과 동일한 EXPLAIN statement 에 대해 이를 보여 줍니다:

1mysql> SELECT @@explain_json_format_version;
2+-------------------------------+
3| @@explain_json_format_version |
4+-------------------------------+
5|                             1 |
6+-------------------------------+
71 row in set (0.00 sec)
8
9mysql> SET @@explain_json_format_version = 2;
10Query OK, 0 rows affected (0.00 sec)
11
12mysql> SELECT @@explain_json_format_version;
13+-------------------------------+
14| @@explain_json_format_version |
15+-------------------------------+
16|                             2 |
17+-------------------------------+
181 row in set (0.00 sec)
19
20mysql> EXPLAIN FORMAT=JSON SELECT Name FROM country WHERE Code LIKE 'A%'\G
21EXPLAIN: {
22  "query": "/* select#1 */ select `world`.`country`.`Name` AS `Name` from `world`.`country` where (`world`.`country`.`Code` like 'A%')",
23  "query_plan": {
24    "inputs": [
25      {
26        "ranges": [
27          "('A' <= Code <= 'A????????')"
28        ],
29        "covering": false,\
30        "operation": "Index range scan on country using PRIMARY over ('A' <= Code <= 'A????????')",\
31        "index_name": "PRIMARY",\
32        "table_name": "country",\
33        "access_type": "index",\
34        "key_columns": [\
35          "Code"\
36        ],\
37        "schema_name": "world",\
38        "used_columns": [\
39          "Code",\
40          "Name"\
41        ],\
42        "estimated_rows": 17.0,\
43        "index_access_type": "index_range_scan",\
44        "estimated_total_cost": 3.668778400708174\
45      }\
46    ],
47    "condition": "(country.`Code` like 'A%')",
48    "operation": "Filter: (country.`Code` like 'A%')",
49    "access_type": "filter",
50    "estimated_rows": 17.0,
51    "filter_columns": [
52      "world.country.`Code`"
53    ],
54    "estimated_total_cost": 3.668778400708174
55  },
56  "query_type": "select",
57  "json_schema_version": "2.0"
58}
591 row in set, 1 warning (0.01 sec)

explain_json_format_version = 2 로 설정하면, EXPLAIN ANALYZE FORMAT=JSON 에 대한 INTO 절 지원도 가능해집니다. 이를 사용하면 JSON 출력을 사용자 변수에 저장할 수 있으며, 다음과 같습니다:

1mysql> EXPLAIN ANALYZE FORMAT=JSON INTO @v1
2    ->   SELECT Name FROM country WHERE Code LIKE 'A%'\G
3Query OK, 0 rows affected, 1 warning (0.00 sec)
4
5mysql> SELECT @v1\G
6*************************** 1. row ***************************
7@v1: {
8  "query": "/* select#1 */ select `world`.`country`.`Name` AS `Name` from `world`.`country` where (`world`.`country`.`Code` like 'A%')",
9  "query_plan": {
10    "inputs": [
11      {
12        "ranges": [
13          "('A' <= Code <= 'A????????')"
14        ],
15        "covering": false,\
16        "operation": "Index range scan on country using PRIMARY over ('A' <= Code <= 'A????????')",\
17        "index_name": "PRIMARY",\
18        "table_name": "country",\
19        "access_type": "index",\
20        "actual_rows": 17.0,\
21        "key_columns": [\
22          "Code"\
23        ],\
24        "schema_name": "world",\
25        "actual_loops": 1,\
26        "used_columns": [\
27          "Code",\
28          "Name"\
29        ],\
30        "estimated_rows": 17.0,\
31        "index_access_type": "index_range_scan",\
32        "actual_last_row_ms": 0.018502,\
33        "actual_first_row_ms": 0.015971,\
34        "estimated_total_cost": 3.668778400708174\
35      }\
36    ],
37    "condition": "(country.`Code` like 'A%')",
38    "operation": "Filter: (country.`Code` like 'A%')",
39    "access_type": "filter",
40    "actual_rows": 17.0,
41    "actual_loops": 1,
42    "estimated_rows": 17.0,
43    "filter_columns": [
44      "world.country.`Code`"
45    ],
46    "actual_last_row_ms": 0.020957,
47    "actual_first_row_ms": 0.017315,
48    "estimated_total_cost": 3.668778400708174
49  },
50  "query_type": "select",
51  "json_schema_version": "2.0"
52}
531 row in set (0.00 sec)

이 변수를 JSON 함수의 인자로 사용하여 값에서 특정 정보를 얻을 수 있습니다. 다음과 같습니다:

1mysql> SELECT JSON_EXTRACT(@v1,'$.index_name') AS iname,
2    ->        JSON_EXTRACT(@v1, '$.table_name') AS tname\G
3*************************** 1. row ***************************
4iname: "PRIMARY"
5tname: "country"
61 row in set (0.00 sec)

이 형태의 EXPLAIN ANALYZE 는 명시적인 FORMAT=JSON 절을 필요로 하며, SELECT statement 에만 지원됩니다. 선택적인 FOR SCHEMA 옵션도 지원되지만, 필수는 아닙니다. (대신 FOR DATABASE 를 사용할 수도 있습니다.) INTO 절은 explain_json_format_version 이 2 와 같을 때만 FORMAT=JSON 과 함께 지원됩니다. 그렇지 않으면 statement 는 EXPLAIN ANALYZE does not support FORMAT=JSON with explain_json_format_version=1 ( ER_EXPLAIN_ANALYZE_JSON_FORMAT_VERSION_NOT_SUPPORTED) 에러와 함께 실패합니다.

Version 2 형식을 사용한 후에는, explain_json_format_version 을 다시 기본값인 1 로 설정하여, 이후 모든 EXPLAIN FORMAT=JSON statement 의 JSON 출력이 Version 1 형식으로 되돌아가게 할 수 있습니다.

explain_json_format_version 의 값은 JSON 형식을 사용하는 모든 EXPLAIN statement 에서 사용되는 JSON 출력 형식의 버전을 결정합니다. 여기에는 특정 EXPLAIN statement 가 명시적인 FORMAT=JSON 옵션을 포함하기 때문에 JSON 형식이 사용되는 경우와, explain_format 시스템 변수가 JSON 으로 설정되어 있어 자동으로 JSON 형식이 사용되는 경우가 모두 포함됩니다.

EXPLAIN 을 사용하면, statement 가 로우를 찾기 위해 인덱스를 사용하여 더 빠르게 실행되도록 테이블에 인덱스를 추가해야 하는 위치를 확인할 수 있습니다. 또한 EXPLAIN 을 사용하여 옵티마이저가 테이블을 최적의 순서로 조인하는지 확인할 수 있습니다. 옵티마이저에게 SELECT statement 에서 테이블이 명명된 순서에 해당하는 조인 순서를 사용하도록 힌트를 주려면, statement 를 단순한 SELECT 대신 SELECT STRAIGHT_JOIN 으로 시작하십시오. (Section 15.2.13, “SELECT Statement” 를 참조하십시오.)

옵티마이저 트레이스는 때때로 EXPLAIN 의 정보와 상호 보완적인 정보를 제공할 수 있습니다. 그러나 옵티마이저 트레이스의 형식과 내용은 버전 간에 변경될 수 있습니다. 자세한 내용은 Section 10.15, “Tracing the Optimizer” 를 참조하십시오.

인덱스가 사용되어야 한다고 생각하지만 사용되지 않는 문제가 있는 경우, ANALYZE TABLE 을 실행하여 키의 카디널리티와 같이 옵티마이저의 선택에 영향을 미칠 수 있는 테이블 통계를 업데이트하십시오. Section 15.7.3.1, “ANALYZE TABLE Statement” 를 참조하십시오.

참고

MySQL Workbench 에는 EXPLAIN 출력의 시각적 표현을 제공하는 Visual Explain 기능이 있습니다. Tutorial: Using Explain to Improve Query Performance 를 참조하십시오.

Obtaining Information with EXPLAIN ANALYZE

EXPLAIN ANALYZE 는 statement 를 실행하고, 옵티마이저의 예상과 실제 실행이 어떻게 일치하는지를 보여 주는 타이밍 및 추가적인 이터레이터 기반 정보를 포함한 EXPLAIN 출력을 생성합니다. 각 이터레이터에 대해 다음 정보가 제공됩니다:

• 예상 실행 비용

  (일부 이터레이터는 비용 모델로 계측되지 않으므로 예상치에 포함되지 않습니다.)

• 예상 반환 로우 수

• 첫 번째 로우를 반환하는 데 걸린 시간

• 이 이터레이터(자식 이터레이터는 포함하지만 부모 이터레이터는 포함하지 않음)를 실행하는 데 소요된 시간(밀리초 단위)

  (루프가 여러 번인 경우, 이 값은 루프당 평균 시간을 나타냅니다.)

• 이터레이터가 반환한 로우 수

• 루프 수

쿼리 실행 정보는 노드가 이터레이터를 나타내는 TREE 출력 형식을 사용하여 표시됩니다. EXPLAIN ANALYZE 는 선택적으로 FORMAT=TREE 또는 FORMAT=JSON 으로 명시할 수 있는 출력 형식을 사용하며, 기본값은 TREE 입니다. FORMAT=JSON 은 explain_json_format_version 이 2 로 설정된 경우에만 사용할 수 있습니다.

EXPLAIN ANALYZE 는 SELECT statement, 다중 테이블 UPDATE 및 DELETE statement, 그리고 TABLE statement 와 함께 사용할 수 있습니다.

이 statement 는 KILL QUERY 또는 CTRL-C 를 사용하여 종료할 수 있습니다.

EXPLAIN ANALYZE 는 FOR CONNECTION 과 함께 사용할 수 없습니다.

출력 예:

1mysql> SELECT @@explain_format;
2+------------------+
3| @@explain_format |
4+------------------+
5| TRADITIONAL      |
6+------------------+
7
8mysql> EXPLAIN ANALYZE SELECT * FROM t1 JOIN t2 ON (t1.c1 = t2.c2)\G
9*************************** 1. row ***************************
10EXPLAIN: -> Inner hash join (t2.c2 = t1.c1)  (cost=3.5 rows=5)
11(actual time=0.121..0.131 rows=1 loops=1)
12    -> Table scan on t2  (cost=0.07 rows=5)
13(actual time=0.0126..0.0221 rows=5 loops=1)
14    -> Hash
15        -> Table scan on t1  (cost=0.75 rows=5)
16(actual time=0.0372..0.0534 rows=5 loops=1)
17
18mysql> EXPLAIN ANALYZE FORMAT=TREE SELECT * FROM t3 WHERE i > 8\G
19*************************** 1. row ***************************
20EXPLAIN: -> Filter: (t3.i > 8)  (cost=0.75 rows=1.67)
21(actual time=0.0484..0.0542 rows=1 loops=1)
22    -> Table scan on t3  (cost=0.75 rows=5)
23(actual time=0.0417..0.0494 rows=5 loops=1)
24
25mysql> EXPLAIN ANALYZE FORMAT=JSON SELECT * FROM t3 WHERE pk < 17\G
26*************************** 1. row ***************************
27EXPLAIN: {
28  "query": "/* select#1 */ select `a`.`t3`.`pk` AS `pk`,`a`.`t3`.`i` AS `i` from `a`.`t3` where (`a`.`t3`.`pk` < 17)",
29  "inputs": [
30    {
31      "ranges": [
32        "(pk < 17)"
33      ],
34      "covering": false,\
35      "operation": "Index range scan on t3 using PRIMARY over (pk < 17)",\
36      "index_name": "PRIMARY",\
37      "table_name": "t3",\
38      "access_type": "index",\
39      "actual_rows": 3.0,\
40      "key_columns": [\
41        "pk"\
42      ],\
43      "schema_name": "a",\
44      "actual_loops": 1,\
45      "used_columns": [\
46        "pk",\
47        "i"\
48      ],\
49      "estimated_rows": 3.0,\
50      "index_access_type": "index_range_scan",\
51      "actual_last_row_ms": 0.034214,\
52      "actual_first_row_ms": 0.03052,\
53      "estimated_total_cost": 0.860618301731245\
54    }\
55  ],
56  "condition": "(t3.pk < 17)",
57  "operation": "Filter: (t3.pk < 17)",
58  "query_type": "select",
59  "access_type": "filter",
60  "actual_rows": 3.0,
61  "actual_loops": 1,
62  "estimated_rows": 3.0,
63  "filter_columns": [
64    "a.t3.pk"
65  ],
66  "actual_last_row_ms": 0.038189,
67  "actual_first_row_ms": 0.033429,
68  "estimated_total_cost": 0.860618301731245
69}

예제 출력에서 사용된 테이블은 다음 statement 로 생성되었습니다:

1CREATE TABLE t1 (
2    c1 INTEGER DEFAULT NULL,
3    c2 INTEGER DEFAULT NULL
4);
5
6CREATE TABLE t2 (
7    c1 INTEGER DEFAULT NULL,
8    c2 INTEGER DEFAULT NULL
9);
10
11CREATE TABLE t3 (
12    pk INTEGER NOT NULL PRIMARY KEY,
13    i INTEGER DEFAULT NULL
14);

이 statement 출력의 actual time 에 표시된 값은 밀리초 단위로 표현됩니다.

explain_format 은 EXPLAIN ANALYZE 에 대해 다음과 같은 영향을 줍니다:

• 이 변수의 값이 TRADITIONAL 또는 TREE (또는 동의어인 DEFAULT) 인 경우, statement 에 FORMAT=JSON 이 포함되지 않는 한 EXPLAIN ANALYZE 는 TREE 형식을 사용합니다.

• explain_format 의 값이 JSON 인 경우, EXPLAIN ANALYZE 는 statement 에서 FORMAT=TREE 가 명시되지 않는 한 JSON 형식을 사용합니다.

EXPLAIN ANALYZE 와 함께 FORMAT=TRADITIONAL 또는 FORMAT=DEFAULT 를 사용하는 경우, explain_format 의 값과 관계없이 항상 에러가 발생합니다.

MySQL 9.5 에서는, EXPLAIN ANALYZE 및 EXPLAIN FORMAT=TREE 출력의 숫자가 다음 규칙에 따라 형식화됩니다:

• 0.001-999999.5 범위의 숫자는 소수 숫자로 출력됩니다.

  1000 미만의 소수 숫자는 세 자리의 유효 숫자를 가지며, 나머지는 네, 다섯 또는 여섯 자리의 유효 숫자를 가집니다.

• 0.001-999999.5 범위를 벗어난 숫자는 엔지니어링 형식으로 출력됩니다. 이러한 값의 예로는 1.23e+9 및 934e-6 이 있습니다.

• 뒤따르는 0 은 출력되지 않습니다. 예를 들어, 2.30 대신 2.3 을 출력하며, 1.20e+6 대신 1.2e+6 을 출력합니다.

• 1e-12 보다 작은 숫자는 0 으로 출력됩니다.