MySQL8.0.18新機能「EXPLAIN ANALYZE」で外部結合の実行計画が取れなかった件
祝：8.0.18リリース！
今回のメンテナンスリリースは盛りだくさんです。詳細は、リリースノートを読んでいただくか、今度の9/21(月)に更新されるであろうMySQL Weeklyを確認してください。
リリース前から話題沸騰だったのは、「NestedLooop一本勝負っだったMySQLにもとうとうHashJoinがやってきた！」って話ですが、ほかにもたくさん機能がでています。
そんな新機能の中には、PostgreSQLのEXPLAINのパクリリスペクトな出力が魅力の「EXPLAIN ANALYZE」も含まれてます。
で、記念にさっそく叩いてみたら、表題のような結果になりまして。
以下、実験の記録です。
環境
VirtualBoxにCentOS7をMinimalで入れてyum updateしてfirewalldとSELinux止めてmysqlのyum.repoファイル仕込んでyumからmysql8.0.18をインストールするところまで省略。
使ったテーブル＆データは以下の通り。
create database testdb;
use testdb;
create table test1(
col1 varchar(10) PRIMARY KEY NOT NULL,
col2 varchar(10)
);
create table test2(
col1 varchar(10) PRIMARY KEY NOT NULL,
col2 varchar(10)
);
INSERT INTO test1 values ('1','a'),('2','b');
INSERT INTO test2 values ('1','x'),('2','y');
commit;
実験
・テーブルの中身チェック
・EXPLAINなしの内部結合、外部結合クエリ実行
・従来のEXPLAINによる内部結合、外部結合クエリ実行
・EXPLAIN ANALYZEを使った内部結合、外部結合クエリ実行
です。
ポイントは「ERROR 1235 (42000): This version of MySQL doesn't yet support 'EXPLAIN ANALYZE on this query'」というメッセージです。
「まだ、そのクエリはEXPLAIN ANALYZEでサポートしてないよ」っていう。
mysql> select * from test1;
+------+------+
| col1 | col2 |
+------+------+
| 1 | a |
| 2 | b |
+------+------+
2 rows in set (0.00 sec)
mysql>
mysql> select * from test2;
+------+------+
| col1 | col2 |
+------+------+
| 1 | x |
| 2 | y |
+------+------+
2 rows in set (0.01 sec)
mysql>
mysql> select * from test1 t1 join test2 t2 on t1.col1 = t2.col1;
+------+------+------+------+
| col1 | col2 | col1 | col2 |
+------+------+------+------+
| 1 | a | 1 | x |
| 2 | b | 2 | y |
+------+------+------+------+
2 rows in set (0.01 sec)
mysql>
mysql> select * from test1 t1 left outer join test2 t2 on t1.col1 = t2.col1;
+------+------+------+------+
| col1 | col2 | col1 | col2 |
+------+------+------+------+
| 1 | a | 1 | x |
| 2 | b | 2 | y |
+------+------+------+------+
2 rows in set (0.01 sec)
mysql>
mysql> explain select * from test1 t1 join test2 t2 on t1.col1 = t2.col1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 2
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t2
partitions: NULL
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 2
filtered: 50.00
Extra: Using where; Using join buffer (Block Nested Loop)
2 rows in set, 1 warning (0.00 sec)
mysql>
mysql> explain select * from test1 t1 left outer join test2 t2 on t1.col1 = t2.col1\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: t2
partitions: NULL
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 2
filtered: 100.00
Extra: Using where; Using join buffer (Block Nested Loop)
2 rows in set, 1 warning (0.00 sec)
mysql>
mysql> explain analyze select * from test1 t1 join test2 t2 on t1.col1 = t2.col1\G
*************************** 1. row ***************************
EXPLAIN: -> Inner hash join (t2.col1 = t1.col1) (cost=1.10 rows=2) (actual time=0.170..0.187 rows=2 loops=1)
-> Table scan on t2 (cost=0.18 rows=2) (actual time=0.020..0.027 rows=2 loops=1)
-> Hash
-> Table scan on t1 (cost=0.45 rows=2) (actual time=0.040..0.095 rows=2 loops=1)
1 row in set (0.00 sec)
mysql>
mysql> explain analyze select * from test1 t1 left outer join test2 t2 on t1.col1 = t2.col1\G
ERROR 1235 (42000): This version of MySQL doesn't yet support 'EXPLAIN ANALYZE on this query'
もう一つ追いかける
EXPLAIN ANALYZEの出力形式は、MySQL 8.0.16でリリースされた「TREE」形式のみ、ということになっています。
The query execution information is displayed using the TREE output format, in which nodes represent iterators. EXPLAIN ANALYZE always uses the TREE output format and does not accept a format specifier. It also cannot be used with FOR CONNECTION.
EXPLAIN ANALYZE can be used only with SELECT statements.
MySQL :: MySQL 8.0 Reference Manual :: 13.8.2 EXPLAIN Syntax
なお、さらっと「EXPLAIN ANALYZEはSELECTステートメントだけよっ」って書いてありますが、外部結合がダメとは書いてません。
で、EXPLAIN ANALYZEとTREE形式の話に戻って、「EXPLAIN ANALYZE」じゃなくて「EXPLAIN FORMAT=TREE」でやったらどうなるの？の結果がこちら。
mysql> explain format=tree select * from test1 t1 join test2 t2 on t1.col1 = t2.col1\G
*************************** 1. row ***************************
EXPLAIN: -> Inner hash join (t2.col1 = t1.col1) (cost=1.10 rows=2)
-> Table scan on t2 (cost=0.18 rows=2)
-> Hash
-> Table scan on t1 (cost=0.45 rows=2)
1 row in set (0.01 sec)
mysql>
mysql> explain format=tree select * from test1 t1 left outer join test2 t2 on t1.col1 = t2.col1\G
*************************** 1. row ***************************
EXPLAIN: <not executable by iterator executor>
1 row in set (0.00 sec)はい。こちらもダメでした。ていうか、こっち（FORMAT=TREE）が外部結合に対応しきれていないから、EXPLAIN ANALYZEもダメ、ってことなんじゃないかな。
MySQL 8.0.16のリリースノートには、以下のような記述があります。
Added an experimental tree format for EXPLAIN output, which prints the generated iterator tree, and is intended to help users understand how execution was actually set up. EXPLAIN FORMAT=TREE is currently unsupported in production and both its syntax and output are subject to change in subsequent versions of MySQL.
MySQL :: MySQL 8.0 Release Notes :: Changes in MySQL 8.0.16 (2019-04-25, General Availability)
「EXPLAIN FORMAT=TREE is currently unsupported in production 」ですってよ。
で、8.0.17、8.0.18のリリースノートで「supported in production」的な話は見当たらなかったので、まだ「unsupported in production」なステータスなのでしょう。
8.0.18のリリースノート内のHash Joinの説明のところで、「EXPLAIN FORMAT=TREE or EXPLAIN ANALYZE使えばHASH JOINが選択されているのがはっきり見えるぜ（ヒャッハー」って感じの記述があるのですが、まさかの「unsupported in production」？
You can see whether hash joins have been used to optimize a query in the output of EXPLAIN FORMAT=TREE or EXPLAIN ANALYZE.
MySQL :: MySQL 8.0 Release Notes :: Changes in MySQL 8.0.18 (2019-10-14, General Availability)
