MySQL :: MySQL 8.4 參考手冊 :: 15.8.2 EXPLAIN 陳述式

版本 8.4

8.0 日語

MySQL 8.4 參考手冊 / ... / EXPLAIN 陳述式

15.8.2 EXPLAIN 陳述式

{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_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 陳述式是同義詞。在實務上，DESCRIBE 關鍵字更常用於取得表格結構的資訊，而 EXPLAIN 則用於取得查詢執行計畫 (也就是，MySQL 將如何執行查詢的說明)。

以下討論根據這些用法使用 DESCRIBE 和 EXPLAIN 關鍵字，但 MySQL 解析器將它們視為完全同義。

取得表格結構資訊

DESCRIBE 提供表格中欄位的相關資訊

mysql> DESCRIBE City;
+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| Id         | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name       | char(35) | NO   |     |         |                |
| Country    | char(3)  | NO   | UNI |         |                |
| District   | char(20) | YES  | MUL |         |                |
| Population | int(11)  | NO   |     | 0       |                |
+------------+----------+------+-----+---------+----------------+

DESCRIBE 是 SHOW COLUMNS 的簡寫。這些語句也會顯示檢視表的資訊。SHOW COLUMNS 的說明文件提供了更多關於輸出欄位的資訊。請參閱第 15.7.7.6 節，「SHOW COLUMNS Statement」。

預設情況下，DESCRIBE 會顯示表格中所有欄位的資訊。如果給定 col_name，則它是表格中欄位的名稱。在這種情況下，該語句僅顯示指定欄位的資訊。如果給定 wild，則它是一個模式字串。它可以包含 SQL % 和 _ 萬用字元。在這種情況下，該語句僅顯示名稱與該字串匹配的欄位的輸出。除非字串包含空格或其他特殊字元，否則不需要將字串括在引號中。

提供 DESCRIBE 語句是為了與 Oracle 相容。

SHOW CREATE TABLE、SHOW TABLE STATUS 和 SHOW INDEX 語句也提供了關於表格的資訊。請參閱第 15.7.7 節，「SHOW Statements」。

explain_format 系統變數在使用 EXPLAIN 取得表格欄位資訊時沒有作用。

取得執行計畫資訊

EXPLAIN 語句提供關於 MySQL 如何執行語句的資訊

EXPLAIN 可與 SELECT、DELETE、INSERT、REPLACE、UPDATE 和 TABLE 語句一起使用。
當 EXPLAIN 與可解釋語句一起使用時，MySQL 會顯示來自最佳化工具的關於語句執行計畫的資訊。也就是說，MySQL 會解釋它將如何處理語句，包括表格如何連接以及以何種順序連接的資訊。關於使用 EXPLAIN 取得執行計畫資訊的詳細資訊，請參閱第 10.8.2 節，「EXPLAIN 輸出格式」。
當 EXPLAIN 與 FOR CONNECTION connection_id 而不是可解釋語句一起使用時，它會顯示在指定連線中執行的語句的執行計畫。請參閱第 10.8.4 節，「取得具名連線的執行計畫資訊」。
對於可解釋語句，EXPLAIN 會產生額外的執行計畫資訊，這些資訊可以使用 SHOW WARNINGS 顯示。請參閱第 10.8.3 節，「擴展的 EXPLAIN 輸出格式」。
EXPLAIN 對於檢查涉及分割表格的查詢非常有用。請參閱第 26.3.5 節，「取得關於分割區的資訊」。

FORMAT 選項可以用來選擇輸出格式。TRADITIONAL 以表格格式呈現輸出。如果沒有 FORMAT 選項，則這是預設值。JSON 格式以 JSON 格式顯示資訊。TREE 提供樹狀輸出，其中對查詢處理的描述比 TRADITIONAL 格式更精確；它是唯一顯示雜湊連接使用情況的格式（請參閱第 10.2.1.4 節，「雜湊連接最佳化」），並且始終用於 EXPLAIN ANALYZE。

在 MySQL 8.4 中，EXPLAIN 使用的預設輸出格式（也就是說，當它沒有 FORMAT 選項時）由 explain_format 系統變數的值決定。此變數的確切效果將在本節稍後描述。

MySQL 8.4 支援 EXPLAIN FORMAT=JSON 的額外 INTO 選項，該選項允許將 JSON 格式化的輸出儲存到使用者變數中，如下所示：

mysql> EXPLAIN FORMAT=JSON INTO @myselect 
    ->     SELECT name FROM a WHERE id = 2;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @myselect\G
*************************** 1. row ***************************
@myex: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1.00"
    },
    "table": {
      "table_name": "a",
      "access_type": "const",
      "possible_keys": [
        "PRIMARY"
      ],
      "key": "PRIMARY",
      "used_key_parts": [
        "id"
      ],
      "key_length": "4",
      "ref": [
        "const"
      ],
      "rows_examined_per_scan": 1,
      "rows_produced_per_join": 1,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "0.00",
        "eval_cost": "0.10",
        "prefix_cost": "0.00",
        "data_read_per_join": "408"
      },
      "used_columns": [
        "id",
        "name"
      ]
    }
  }
}
1 row in set (0.00 sec)

這適用於任何可解釋的語句（SELECT、TABLE、INSERT、UPDATE、REPLACE 或 DELETE）。這裡顯示了使用 UPDATE 和 DELETE 語句的範例

mysql> EXPLAIN FORMAT=JSON INTO @myupdate 
    ->   UPDATE a SET name2 = "garcia" WHERE id = 3;              
Query OK, 0 rows affected (0.00 sec)

mysql> EXPLAIN FORMAT=JSON INTO @mydelete 
    ->     DELETE FROM a WHERE name1 LIKE '%e%';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @myupdate, @mydelete\G
*************************** 1. row ***************************
@myupdate: {
  "query_block": {
    "select_id": 1,
    "table": {
      "update": true,
      "table_name": "a",
      "access_type": "range",
      "possible_keys": [
        "PRIMARY"
      ],
      "key": "PRIMARY",
      "used_key_parts": [
        "id"
      ],
      "key_length": "4",
      "ref": [
        "const"
      ],
      "rows_examined_per_scan": 1,
      "filtered": "100.00",
      "attached_condition": "(`db`.`a`.`id` = 3)"
    }
  }
}
@mydelete: {
  "query_block": {
    "select_id": 1,
    "table": {
      "delete": true,
      "table_name": "a",
      "access_type": "ALL",
      "rows_examined_per_scan": 2,
      "filtered": "100.00",
      "attached_condition": "(`db`.`a`.`name1` like '%e%')"
    }
  }
}
1 row in set (0.00 sec)

您可以使用 MySQL JSON 函數處理此值，就像處理任何其他 JSON 值一樣，如下列使用 JSON_EXTRACT() 的範例所示

mysql> SELECT JSON_EXTRACT(@myselect, "$.query_block.table.key");
+----------------------------------------------------+
| JSON_EXTRACT(@myselect, "$.query_block.table.key") |
+----------------------------------------------------+
| "PRIMARY"                                          |
+----------------------------------------------------+
1 row in set (0.01 sec)

mysql> SELECT JSON_EXTRACT(@myupdate, "$.query_block.table.access_type") AS U_acc,
    ->        JSON_EXTRACT(@mydelete, "$.query_block.table.access_type") AS D_acc;
+---------+-------+
| U_acc   | D_acc |
+---------+-------+
| "range" | "ALL" |
+---------+-------+
1 row in set (0.00 sec)

另請參閱第 14.17 節，「JSON 函數」。

嘗試使用 INTO 子句而不顯式包含 FORMAT=JSON 會導致 EXPLAIN 被拒絕並出現 ER_EXPLAIN_INTO_IMPLICIT_FORMAT_NOT_SUPPORTED 錯誤。無論 explain_format 系統變數的目前值為何，都是如此。

INTO 子句不支援 EXPLAIN ANALYZE；它也不支援 FOR CONNECTION。

重要

如果由於任何原因，要分析的語句被拒絕，則使用者變數不會更新。

MySQL 8.4 支援 FOR SCHEMA 子句，這會使 EXPLAIN 的行為如同要分析的語句已在指定的資料庫中執行；FOR DATABASE 作為同義詞支援。這裡顯示了一個簡單的使用範例：

mysql> USE b;
Database changed
mysql> CREATE SCHEMA s1;
Query OK, 1 row affected (0.01 sec)

mysql> CREATE SCHEMA s2;
Query OK, 1 row affected (0.01 sec)

mysql> USE s1;
Database changed
mysql> CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 INT NOT NULL);
Query OK, 0 rows affected (0.04 sec)

mysql> USE s2;
Database changed
mysql> CREATE TABLE t (c1 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, c2 INT NOT NULL, KEY i1 (c2));
Query OK, 0 rows affected (0.04 sec)

mysql> USE b;
Database changed
mysql> EXPLAIN FORMAT=TREE FOR SCHEMA s1 SELECT * FROM t WHERE c2 > 50\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (t.c2 > 50)  (cost=0.35 rows=1)
    -> Table scan on t  (cost=0.35 rows=1)

1 row in set (0.00 sec)

mysql> EXPLAIN FORMAT=TREE FOR SCHEMA s2 SELECT * FROM t WHERE c2 > 50\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (t.c2 > 50)  (cost=0.35 rows=1)
    -> Covering index scan on t using i1  (cost=0.35 rows=1)

1 row in set (0.00 sec)

如果資料庫不存在，則語句會被拒絕並出現 ER_BAD_DB_ERROR 錯誤。如果使用者沒有執行語句的必要權限，則會被拒絕並出現 ER_DBACCESS_DENIED_ERROR 錯誤。

FOR SCHEMA 與 FOR CONNECTION 不相容。

EXPLAIN 需要執行被解釋語句所需的相同權限。此外，EXPLAIN 還需要任何被解釋檢視表的 SHOW VIEW 權限。EXPLAIN ... FOR CONNECTION 如果指定的連線屬於不同的使用者，則也需要 PROCESS 權限。

explain_format 系統變數決定使用 EXPLAIN 顯示查詢執行計畫時的輸出格式。此變數可以採用與 FORMAT 選項一起使用的任何值，並額外將 DEFAULT 作為 TRADITIONAL 的同義詞。以下範例使用了來自 world 資料庫的 country 表格，該表格可以從 MySQL: 其他下載取得

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

檢查 explain_format 的值，我們看到它具有預設值，因此 EXPLAIN（沒有 FORMAT 選項）會使用傳統的表格輸出

mysql> SELECT @@explain_format;
+------------------+
| @@explain_format |
+------------------+
| TRADITIONAL      |
+------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT Name FROM country WHERE Code Like 'A%';
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | country | NULL       | range | PRIMARY       | PRIMARY | 12      | NULL |   17 |   100.00 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

如果我們將 explain_format 的值設定為 TREE，然後重新執行相同的 EXPLAIN 語句，則輸出會使用樹狀格式

mysql> SET @@explain_format=TREE;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@explain_format;
+------------------+
| @@explain_format |
+------------------+
| TREE             |
+------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT Name FROM country WHERE Code LIKE 'A%';
+--------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                      |
+--------------------------------------------------------------------------------------------------------------+
| -> Filter: (country.`Code` like 'A%')  (cost=3.67 rows=17)
    -> Index range scan on country using PRIMARY over ('A' <= Code <= 'A????????')  (cost=3.67 rows=17)  |
+--------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

如前所述，FORMAT 選項會覆蓋此設定。執行相同的 EXPLAIN 語句，使用 FORMAT=JSON 而不是 FORMAT=TREE，顯示確實如此

mysql> EXPLAIN FORMAT=JSON SELECT Name FROM country WHERE Code LIKE 'A%';
+------------------------------------------------------------------------------+
| EXPLAIN                                                                      |
+------------------------------------------------------------------------------+
| {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "3.67"
    },
    "table": {
      "table_name": "country",
      "access_type": "range",
      "possible_keys": [
        "PRIMARY"
      ],
      "key": "PRIMARY",
      "used_key_parts": [
        "Code"
      ],
      "key_length": "12",
      "rows_examined_per_scan": 17,
      "rows_produced_per_join": 17,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "1.97",
        "eval_cost": "1.70",
        "prefix_cost": "3.67",
        "data_read_per_join": "16K"
      },
      "used_columns": [
        "Code",
        "Name"
      ],
      "attached_condition": "(`world`.`country`.`Code` like 'A%')"
    }
  }
}                                                                              |
+------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

若要將 EXPLAIN 的預設輸出返回到表格格式，請將 explain_format 設定為 TRADITIONAL。或者，您可以將其設定為 DEFAULT，其效果相同，如下所示

mysql> SET @@explain_format=DEFAULT;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@explain_format;
+------------------+
| @@explain_format |
+------------------+
| TRADITIONAL      |
+------------------+
1 row in set (0.00 sec)

MySQL 8.4 支援 JSON 輸出格式的兩個版本。版本 1 是 MySQL 8.2 及更早版本中始終使用的線性格式；這仍然是 MySQL 8.4 中的預設值，並在本節中已顯示的範例中使用。JSON 輸出格式的版本 2 基於存取路徑，旨在與未來版本的 MySQL 最佳化工具相容。您可以將 explain_json_format_version 伺服器系統變數的值設定為 2，來切換到版本 2 格式，如下列用於先前範例中相同 EXPLAIN 語句的顯示

mysql> SELECT @@explain_json_format_version;
+-------------------------------+
| @@explain_json_format_version |
+-------------------------------+
|                             1 |
+-------------------------------+
1 row in set (0.00 sec)

mysql> SET @@explain_json_format_version = 2;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@explain_json_format_version;
+-------------------------------+
| @@explain_json_format_version |
+-------------------------------+
|                             2 |
+-------------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN FORMAT=JSON SELECT Name FROM country WHERE Code LIKE 'A%';
+------------------------------------------------------------------------------+
| EXPLAIN                                                                      |
+------------------------------------------------------------------------------+
| {
  "query": "/* select#1 */ select `world`.`country`.`Name` AS `Name` from `world`.`country` where (`world`.`country`.`Code` like 'A%')",
  "inputs": [
    {
      "ranges": [
        "('A' &lt;= Code &lt;= 'A????????')"
      ],
      "covering": false,
      "operation": "Index range scan on country using PRIMARY over ('A' &lt;= Code &lt;= 'A????????')",
      "index_name": "PRIMARY",
      "table_name": "country",
      "access_type": "index",
      "estimated_rows": 17.0,
      "index_access_type": "index_range_scan",
      "estimated_total_cost": 3.668778400708174
    }
  ],
  "condition": "(country.`Code` like 'A%')",
  "operation": "Filter: (country.`Code` like 'A%')",
  "access_type": "filter",
  "estimated_rows": 17.0,
  "estimated_total_cost": 3.668778400708174
}                                                                              |
+------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

在使用版本 2 格式後，您可以透過將 explain_json_format_version 設定回 1（預設值），使所有後續 EXPLAIN FORMAT=JSON 語句的 JSON 輸出恢復為版本 1 格式。

mysql> EXPLAIN ANALYZE SELECT * FROM t1 JOIN t2 ON (t1.c1 = t2.c2)\G
*************************** 1. row ***************************
EXPLAIN: -> Inner hash join (t2.c2 = t1.c1)  (cost=4.70 rows=6)
(actual time=0.032..0.035 rows=6 loops=1)
    -> Table scan on t2  (cost=0.06 rows=6)
(actual time=0.003..0.005 rows=6 loops=1)
    -> Hash
        -> Table scan on t1  (cost=0.85 rows=6)
(actual time=0.018..0.022 rows=6 loops=1)

mysql> EXPLAIN ANALYZE SELECT * FROM t3 WHERE i > 8\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (t3.i > 8)  (cost=1.75 rows=5)
(actual time=0.019..0.021 rows=6 loops=1)
    -> Table scan on t3  (cost=1.75 rows=15)
(actual time=0.017..0.019 rows=15 loops=1)

mysql> EXPLAIN ANALYZE SELECT * FROM t3 WHERE pk > 17\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (t3.pk > 17)  (cost=1.26 rows=5)
(actual time=0.013..0.016 rows=5 loops=1)
    -> Index range scan on t3 using PRIMARY  (cost=1.26 rows=5)
(actual time=0.012..0.014 rows=5 loops=1)

CREATE TABLE t1 (
    c1 INTEGER DEFAULT NULL,
    c2 INTEGER DEFAULT NULL
);

CREATE TABLE t2 (
    c1 INTEGER DEFAULT NULL,
    c2 INTEGER DEFAULT NULL
);

CREATE TABLE t3 (
    pk INTEGER NOT NULL PRIMARY KEY,
    i INTEGER DEFAULT NULL
);

mysql> SET @@explain_format=JSON;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@explain_format;
+------------------+
| @@explain_format |
+------------------+
| JSON             |
+------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN ANALYZE SELECT * FROM t3 WHERE pk > 17\G
ERROR 1235 (42000): This version of MySQL doesn't yet support 'EXPLAIN ANALYZE with JSON format'

mysql> EXPLAIN ANALYZE FORMAT=TRADITIONAL SELECT * FROM t3 WHERE pk > 17\G
ERROR 1235 (42000): This version of MySQL doesn't yet support 'EXPLAIN ANALYZE with TRADITIONAL format'

mysql> EXPLAIN ANALYZE FORMAT=TREE SELECT * FROM t3 WHERE pk > 17\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (t3.pk > 17)  (cost=1.26 rows=5)
(actual time=0.013..0.016 rows=5 loops=1)
    -> Index range scan on t3 using PRIMARY  (cost=1.26 rows=5)
(actual time=0.012..0.014 rows=5 loops=1)