EXPLAIN ANALYZE

explain analyze

In case of MySQL

PostgreSQLには、実際に処理を実行する、Explain Analyzeがありましたが、MySQL8.0.18からExplainに同じオプションが導入されています。ただ、PostgreSQLとMySQLの処理では差異があるので、こちらにまとめておきたいと思います。

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.30    |
+-----------+
1 row in set (0.00 sec)

Explain

mysql> explain select * from Table_A where id = 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: Table_A
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

Explainとフォーマットオプション

mysql> explain format=tree select * from Table_A where id = 10\G
*************************** 1. row ***************************
EXPLAIN: -> Rows fetched before execution  (cost=0.00..0.00 rows=1)

1 row in set (0.00 sec)

mysql> explain format=json select * from Table_A where id = 10\G
*************************** 1. row ***************************
EXPLAIN: {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1.00"
    },
    "table": {
      "table_name": "Table_A",
      "access_type": "const",
      "possible_keys": [
        "PRIMARY"
      ],
      "key": "PRIMARY",
      "used_key_parts": [
        "id"
      ],
      "key_length": "8",
      "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": "24"
      },
      "used_columns": [
        "id",
        "system_id"
      ]
    }
  }
}
1 row in set, 1 warning (0.00 sec)

mysql>

MySQL 8.0.18 では、EXPLAIN ANALYZE が導入されています。この EXPLAIN ANALYZE は、ステートメントを実行し、タイミングおよび追加のイテレータベースの情報とともに、オプティマイザの期待が実際の実行とどのように一致したかに関する EXPLAIN 出力を生成します。

13.8.2 EXPLAIN ステートメント

Explain Analyzeとアップデート処理

DMLをExplainで見ると、Explainのみであれば更新系の処理もSELECT同様に確認する事は可能です。

mysql> select * from Table_A where id = 10;
+----+-----------+
| id | system_id |
+----+-----------+
| 10 |         8 |
+----+-----------+
1 row in set (0.00 sec)

mysql> explain update Table_A set system_id = 10 where id = 10\G
*************************** 1. row ***************************
           id: 1
  select_type: UPDATE
        table: Table_A
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 8
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

mysql> select * from Table_A where id = 10;
+----+-----------+
| id | system_id |
+----+-----------+
| 10 |         8 |
+----+-----------+
1 row in set (0.00 sec)

mysql>

ExplainにAnalyzeオプションを付けて、データを変更系の処理を実施してもデータが更新される事は現段階では無さそうです。ただ、以下の様にエラーになってしまいますね。

mysql> select * from Table_A where id = 10;
+----+-----------+
| id | system_id |
+----+-----------+
| 10 |         8 |
+----+-----------+
1 row in set (0.00 sec)

mysql> explain analyze update Table_A set system_id = 10 where id = 10;
+----------------------------------------+
| EXPLAIN                                |
+----------------------------------------+
| <not executable by iterator executor>
 |
+----------------------------------------+
1 row in set (0.00 sec)

mysql> select * from Table_A where id = 10;
+----+-----------+
| id | system_id |
+----+-----------+
| 10 |         8 |
+----+-----------+
1 row in set (0.00 sec)

mysql>
Explain Analyze with MySQL

In case of PostgreSQL

POC=# select version();
                                                           version
-----------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 13.5 (Debian 13.5-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
(1 row)

Explain

POC=# \x
Expanded display is off.
POC=# explain select * from Table_A where id = 10;
                                 QUERY PLAN
----------------------------------------------------------------------------
 Index Scan using table_a_pkey on table_a  (cost=0.15..8.17 rows=1 width=8)
   Index Cond: (id = 10)
(2 rows)

Explainとフォーマットオプション

POC=# explain (FORMAT JSON) select * from Table_A where id = 10;
             QUERY PLAN
-------------------------------------
 [                                  +
   {                                +
     "Plan": {                      +
       "Node Type": "Index Scan",   +
       "Parallel Aware": false,     +
       "Scan Direction": "Forward", +
       "Index Name": "table_a_pkey",+
       "Relation Name": "table_a",  +
       "Alias": "table_a",          +
       "Startup Cost": 0.15,        +
       "Total Cost": 8.17,          +
       "Plan Rows": 1,              +
       "Plan Width": 8,             +
       "Index Cond": "(id = 10)"    +
     }                              +
   }                                +
 ]
(1 row)

POC=# explain (FORMAT TEXT) select * from Table_A where id = 10;
                                 QUERY PLAN
----------------------------------------------------------------------------
 Index Scan using table_a_pkey on table_a  (cost=0.15..8.17 rows=1 width=8)
   Index Cond: (id = 10)
(2 rows)

POC=# explain (FORMAT JSON) select * from Table_A where id = 10;
             QUERY PLAN
-------------------------------------
 [                                  +
   {                                +
     "Plan": {                      +
       "Node Type": "Index Scan",   +
       "Parallel Aware": false,     +
       "Scan Direction": "Forward", +
       "Index Name": "table_a_pkey",+
       "Relation Name": "table_a",  +
       "Alias": "table_a",          +
       "Startup Cost": 0.15,        +
       "Total Cost": 8.17,          +
       "Plan Rows": 1,              +
       "Plan Width": 8,             +
       "Index Cond": "(id = 10)"    +
     }                              +
   }                                +
 ]
(1 row)

POC=# explain (FORMAT XML) select * from Table_A where id = 10;
                        QUERY PLAN
----------------------------------------------------------
 <explain xmlns="http://www.postgresql.org/2009/explain">+
   <Query>                                               +
     <Plan>                                              +
       <Node-Type>Index Scan</Node-Type>                 +
       <Parallel-Aware>false</Parallel-Aware>            +
       <Scan-Direction>Forward</Scan-Direction>          +
       <Index-Name>table_a_pkey</Index-Name>             +
       <Relation-Name>table_a</Relation-Name>            +
       <Alias>table_a</Alias>                            +
       <Startup-Cost>0.15</Startup-Cost>                 +
       <Total-Cost>8.17</Total-Cost>                     +
       <Plan-Rows>1</Plan-Rows>                          +
       <Plan-Width>8</Plan-Width>                        +
       <Index-Cond>(id = 10)</Index-Cond>                +
     </Plan>                                             +
   </Query>                                              +
 </explain>
(1 row)

POC=# explain (FORMAT YAML) select * from Table_A where id = 10;
           QUERY PLAN
--------------------------------
 - Plan:                       +
     Node Type: "Index Scan"   +
     Parallel Aware: false     +
     Scan Direction: "Forward" +
     Index Name: "table_a_pkey"+
     Relation Name: "table_a"  +
     Alias: "table_a"          +
     Startup Cost: 0.15        +
     Total Cost: 8.17          +
     Plan Rows: 1              +
     Plan Width: 8             +
     Index Cond: "(id = 10)"
(1 row)

POC=#

Explain Analyzeとアップデート処理

DMLをExplainで見ると、Explainのみであれば更新系の処理も確認する事は可能です。データは更新されません。

POC=#  select * from Table_A where id = 10;
 id | system_id
----+-----------
 10 |         8
(1 row)

POC=# explain update Table_A set system_id = 10 where id = 10;
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Update on table_a  (cost=0.15..8.17 rows=1 width=14)
   ->  Index Scan using table_a_pkey on table_a  (cost=0.15..8.17 rows=1 width=14)
         Index Cond: (id = 10)
(3 rows)

POC=#  select * from Table_A where id = 10;
 id | system_id
----+-----------
 10 |         8
(1 row)

但し、Explain AnalyzeをPostgreSQLで実行した場合は、実際にSQLを処理して解析してくれるので、データ自体も更新されますね。ここは、運用する時に留意しておく必要ありますね。若しくは、Explain Analyzeの場合は、Transactionで処理して、確認後にRollbackする等の留意が必要ですね。

POC=# select * from Table_A where id = 10;
 id | system_id
----+-----------
 10 |         8
(1 row)

POC=# explain analyze update Table_A set system_id = 10 where id = 10;
                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Update on table_a  (cost=0.15..8.17 rows=1 width=14) (actual time=0.163..0.164 rows=0 loops=1)
   ->  Index Scan using table_a_pkey on table_a  (cost=0.15..8.17 rows=1 width=14) (actual time=0.021..0.023 rows=1 loops=1)
         Index Cond: (id = 10)
 Planning Time: 0.214 ms
 Execution Time: 0.385 ms
(5 rows)

POC=# select * from Table_A where id = 10;
 id | system_id
----+-----------
 10 |        10
(1 row)

POC=#
Explain Analyze with PostgreSQL

参照:EXPLAIN — show the execution plan of a statement

カテゴリー:

最近のコメント

表示できるコメントはありません。