MySQL標準のEXPLAIN(format=TRADITIONAL)とEXPLAIN format=TREEを並べて見つめてみる

いつまで経ってもformat=TREEに慣れないので、メモしつつ勉強してみる。

• MySQL :: MySQL 8.0 リファレンスマニュアル :: 13.8.2 EXPLAIN ステートメント

  mysql80 8> EXPLAIN SELECT * FROM t1, t2, (SELECT SUBSTRING(val, 1, 1) AS f, SUM(num) FROM t3 GROUP BY f) AS tmp_t3 WHERE t1.num IN (SELECT num FROM t5 WHERE num = (SELECT MAX(num) FROM t4) AND val LIKE '%');
  +----+-------------+------------+------------+-------+---------------+------+---------+-------+--------+----------+-------------------------------+
  | id | select_type | table      | partitions | type  | possible_keys | key  | key_len | ref   | rows   | filtered | Extra                         |
  +----+-------------+------------+------------+-------+---------------+------+---------+-------+--------+----------+-------------------------------+
  |  1 | PRIMARY     | t1         | NULL       | const | num           | num  | 8       | const |      1 |   100.00 | NULL                          |
  |  1 | PRIMARY     | t5         | NULL       | const | num           | num  | 8       | const |      1 |   100.00 | NULL                          |
  |  1 | PRIMARY     | t2         | NULL       | index | NULL          | val  | 131     | NULL  | 982049 |   100.00 | Using index                   |
  |  1 | PRIMARY     | <derived2> | NULL       | ALL   | NULL          | NULL | NULL    | NULL  | 996250 |   100.00 | Using join buffer (hash join) |
  |  4 | SUBQUERY    | NULL       | NULL       | NULL  | NULL          | NULL | NULL    | NULL  |   NULL |     NULL | Select tables optimized away  |
  |  2 | DERIVED     | t3         | NULL       | index | val           | val  | 131     | NULL  | 996250 |   100.00 | Using index; Using temporary  |
  +----+-------------+------------+------------+-------+---------------+------+---------+-------+--------+----------+-------------------------------+
  6 rows in set, 1 warning (0.00 sec)
  

いわゆるフツーのEXPLAIN。

• t3を含んだサブクエリ (SELECT SUBSTRING(val, 1, 1) AS f, SUM(num) FROM t3 GROUP BY f) AS tmp_t3 が最初に処理
• 【2022/4/7 16:33】
• Derivedの遅延実体化が5.6で入った関係で、これは間違いだった
• 日々の覚書: MySQL標準のEXPLAINと、実際にテーブルにアクセスする順番と
• SELECT MAX(num) FROM t4 のサブクエリがテーブルアクセス抜き ( Select tables optimized away )で処理(optimized awayが発生するのはMIXまたはMAXなのでテーブル名まで決まり)
• SUBQUERYが1か所しかないので、 (SELECT num FROM t5 WHERE num = (ここは処理済み) AND val LIKE '%') はJOINに書き換えられている
• t1から1行フェッチして SELECT num FROM t5 WHERE num = (ここは処理済み) AND val LIKE '%' が1行フェッチされる -> t2が1行フェッチされる -> (SELECT SUBSTRING(val, 1, 1) AS f, SUM(num) FROM t3 GROUP BY f) AS tmp_t3 （これがid:2なので と表示される) が1行フェッチされる
• ↑のステップがt1の全レコードが終わるまでループ

「idの数字の大小に関係なく、最後の行から解釈する（ただしDEPENDENT SUBQUERYが例外）」
「idが同じものはNested Loop Joinで、idが同じ行のうち先頭の行から解釈する」

あたりで一般化できるか。
OK、これは読める、日本語で説明できた。じゃあ format=TREE。

mysql80 8> EXPLAIN format=TREE SELECT * FROM t1, t2, (SELECT SUBSTRING(val, 1, 1) AS f, SUM(num) FROM t3 GROUP BY f) AS tmp_t3 WHERE t1.num IN (SELECT num FROM t5 WHERE num = (SELECT MAX(num) FROM t4) AND val LIKE '%')\G
*************************** 1. row ***************************
EXPLAIN: 
-> Inner hash join (no condition)  (cost=6556388.62 rows=0)
    -> Table scan on tmp_t3  (cost=2.50..2.50 rows=0)
        -> Materialize  (cost=2.50..2.50 rows=0)
            -> Table scan on <temporary>
                -> Aggregate using temporary table
                    -> Index scan on t3 using val  (cost=103631.25 rows=996250)
    -> Hash
        -> Index scan on t2 using val  (cost=104645.03 rows=982049)

1 row in set (0.00 sec)

全然読めない…が、t3を含んだサブクエリが最初なので、最初だったけどOracle流の読み方としてはここから読むので正しいらしい…（がそうするとますますMySQLのformat=TREEって間違ってるんじゃ…？） Index scan on t3 using val (cost=103631.25 rows=996250) が最初、これはderivedになるので Aggregate using temporary table と Table scan on <temporary> と Materialize が Using temporary の意味か。

Table scan on tmp_t3 はTRADITIONALの table: <derived2>, type: ALL に相当しそう。

あとはもう Inner hash join (no condition) でt1, t5に関する言及は無くてひとまとまり、 Index scan on t2 using val でt2に言及はあるけど、ここから t1, t5, t2, tmp_t3の順だと字面だけで判断するのは俺には無理な気がする…。

（t1, t5を自明なInnser hash joinだと割り切れば、tmp_t3とHashが同じインデント位置にいるから後ろから読めば良いってことなのかな…）

mysql80 18> EXPLAIN ANALYZE SELECT * FROM t1, t2, (SELECT SUBSTRING(val, 1, 1) AS f, SUM(num) FROM t3 GROUP BY f) AS tmp_t3 WHERE t1.num IN (SELECT num FROM t5 WHERE num = (SELECT MAX(num) FROM t4) AND val LIKE '%')\G
*************************** 1. row ***************************
EXPLAIN: -> Inner hash join (no condition)  (cost=6556388.62 rows=0) (actual time=4058.502..5291.463 rows=16000000 loops=1)
    -> Table scan on tmp_t3  (cost=2.50..2.50 rows=0) (actual time=0.001..0.004 rows=16 loops=1)
        -> Materialize  (cost=2.50..2.50 rows=0) (actual time=3334.956..3334.961 rows=16 loops=1)
            -> Table scan on <temporary>  (actual time=0.004..0.008 rows=16 loops=1)
                -> Aggregate using temporary table  (actual time=3334.335..3334.340 rows=16 loops=1)
                    -> Covering index scan on t3 using val  (cost=103631.25 rows=996250) (actual time=1.751..893.790 rows=1000000 loops=1)
    -> Hash
        -> Covering index scan on t2 using val  (cost=104645.03 rows=982049) (actual time=2.126..543.544 rows=1000000 loops=1)

1 row in set (8.78 sec)

EXPLAIN ANALYZEならテーブル名も出るかなと思ったけどそんなことは無かった。