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>
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 — show the execution plan of a statement