帰ってきた！ 標準はあるにはあるが癖の多いSQL #6 - Hash Joinの結合ツリーにも癖がでる Tweet
本エントリーは、MySQL Advent Calendar 2023 シリーズ2 / Day 9とPostgreSQL Advent Calendar 2023 シリーズ2 / Day 9へのクロスポスト、および、JPOUG Advent Calendar 2023 / Day 9 の裏番組 （ADVENTARはシリーズ増やせないので） エントリーで、12/17日向けの 予習 その２ という位置付けのエントリーです。
予習 その１は、以下。
帰ってきた！ 標準はあるにはあるが癖の多いSQL #5 - Optimizer Hint でも癖が多い
少し前に、MySQLでもHash Joinになるんだね。といいうエントリーを書きました。覚えてますか？
MySQL 8.0.32では NLJに使えるINDEXが存在していても、Hash Joinをヒントで強制することができる！（オプティマイザが選択することもある！）
その時点では、
MySQLのHash Joinを強制するオプティマイザーヒントも廃止されているようだし、Build/Probeの制御もできないよね？ ヒントないし。
とか、
オプティマイザーヒントで強制できないけど、オプティマイザーが勝手に選んじゃうこともあるのか〜、NLJに利用できる索引あっても。。
勝手に選ばれることはあってもヒントではもろもろ制御できない。最初に戻る。
という状況ループ状態でしたw
そこで、閃いたのが、MySQLで、Hash Joinが自動発動する条件。その条件に合う状況にすればいいじゃん。と！
Hash Joinを強制するヒントはなくても、Nested Loop Joinに使ってる主索引（MySQLの場合は主キーはクラスター索引）を、オプティマイザーヒントで無効化してしまえば、勝手に、Hash Joinするよね！？
という確認ばかりに目が行ってしまって、気づきませんでした！！！！
MySQLのHash Joinの実行計画ツリーを見て、Oracle/PostgreSQLと違う点、何か気づきませんか？
みなさん！ これ試験に出ますよw（嘘
答え合わせは後半でw
それでは、環境づくりから（少々長めですw）
それぞれの DB（ Oracle Database 21c / PostgreSQL 13.4 with pg_hint_plan 1.3.9 / MySQL 8.0.32 ）で、そのまま実行できるDDLにしてあります。
表と索引作成（各DB共通）、データ登録、そして、統計取得まで行っています。
Oracle/PostgreSQL/MySQL共通DDL
cre_advent_tabs.sql
-- 10 rows
CREATE TABLE t1
(
id INT NOT NULL
, t1_c1 INT NOT NULL
, CONSTRAINT t1_pk PRIMARY KEY (id)
);
-- 50 rows
CREATE TABLE t2
(
id INT NOT NULL
, s_id INT NOT NULL
, t2_c1 INT NOT NULL
, CONSTRAINT t2_pk PRIMARY KEY (id, s_id)
);
-- 100 rows
CREATE TABLE t3
(
id INT NOT NULL
, b_id INT NOT NULL
, t3_c1 INT NOT NULL
, CONSTRAINT t3_pk PRIMARY KEY (id, b_id)
);
-- 500 rows
CREATE TABLE t4
(
id INT NOT NULL
, a_id INT NOT NULL
, c_id INT NOT NULL
, t4_c1 INT NOT NULL
, CONSTRAINT t4_pk PRIMARY KEY (id, a_id, c_id)
);
-- 1000 rows
CREATE TABLE t0
(
id INT NOT NULL
, m_id INT NOT NULL
, t0_c1 INT NOT NULL
, CONSTRAINT t0_pk PRIMARY KEY (id, m_id)
);
Oracle 21c向けデータ登録と統計取得からデータ件数確認までのスクリプトと実行例
make_data4oracle.sql
-- for t1
BEGIN
FOR i IN 1..10 LOOP
INSERT INTO t1 VALUES(i,i);
END LOOP;
COMMIT;
DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT', tabname=>'T1', cascade=>true, no_invalidate=>false);
END;
/
SELECT COUNT(1) FROM t1;
-- for t2
BEGIN
FOR i IN 1..10 LOOP
FOR j IN 1..5 LOOP
INSERT INTO t2 VALUES(i,j,i+j);
END LOOP;
END LOOP;
COMMIT;v DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT', tabname=>'T2', cascade=>true, no_invalidate=>false);
END;
/
SELECT COUNT(1) FROM t2;
-- for t3
BEGINv FOR i IN 1..10 LOOP
FOR j IN 1..10 LOOP
INSERT INTO t3 VALUES(i,j,i+j);
END LOOP;
END LOOP;
COMMIT;
DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT', tabname=>'T3', cascade=>true, no_invalidate=>false);
END;
/
SELECT COUNT(1) FROM t3;
-- for t4
BEGIN
FOR i IN 1..10 LOOP
FOR j IN 1..10 LOOP
FOR k IN 1..5 LOOP
INSERT INTO t4 VALUES(i,j,k,i+j);
END LOOP;
END LOOP;
COMMIT;
END LOOP;
DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT', tabname=>'T4', cascade=>true, no_invalidate=>false);
END;
/
SELECT COUNT(1) FROM t4;
-- for t0
BEGIN
FOR i IN 1..10 LOOP
FOR j IN 1..100 LOOP
INSERT INTO t0 VALUES(i,j,i+j);
END LOOP;
COMMIT;
END LOOP;
DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT', tabname=>'T0', cascade=>true, no_invalidate=>false);
END;
/
SELECT COUNT(1) FROM t0;
SCOTT@orclpdb1> @make_data4oracle
PL/SQLプロシージャが正常に完了しました。
COUNT(1)
----------
10
PL/SQLプロシージャが正常に完了しました。
COUNT(1)
----------
50
PL/SQLプロシージャが正常に完了しました。
COUNT(1)
----------
100
PL/SQLプロシージャが正常に完了しました。
COUNT(1)
----------
500
PL/SQLプロシージャが正常に完了しました。
COUNT(1)
----------
1000
PostgreSQL 13.4向けデータ登録と統計取得からデータ件数確認までのスクリプトと実行例
perftestdb=> \i cre_advent_tabs.sql
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
perftestdb=>
PostgreSQL向けデータ登録と統計取得からデータ件数確認までのスクリプトと実行例
make_data4postgresql.sql
-- for t1
DO $$
BEGIN
FOR i IN 1..10 LOOP
INSERT INTO t1 VALUES(i,i);
END LOOP;
COMMIT;
END
$$
;
VACUUM VERBOSE ANALYZE t1;
SELECT COUNT(1) FROM t1;
-- for t2
DO $$
BEGIN
FOR i IN 1..10 LOOP
FOR j IN 1..5 LOOP
INSERT INTO t2 VALUES(i,j,i+1);
END LOOP;
END LOOP;
COMMIT;
END
$$
;
VACUUM VERBOSE ANALYZE t2;
SELECT COUNT(1) FROM t2;
-- for t3
DO $$
BEGIN
FOR i IN 1..10 LOOP
FOR j IN 1..10 LOOP
INSERT INTO t3 VALUES(i,j,i+1);
END LOOP;
END LOOP;
COMMIT;
END
$$
;
VACUUM VERBOSE ANALYZE t3;
SELECT COUNT(1) FROM t3;
-- for t4
DO $$
BEGIN
FOR i IN 1..10 LOOP
FOR j IN 1..10 LOOP
FOR k IN 1..5 LOOP
INSERT INTO t4 VALUES(i,j,k,i+j);
END LOOP;
END LOOP;
COMMIT;
END LOOP;
END
$$
;
VACUUM VERBOSE ANALYZE t4;
SELECT COUNT(1) FROM t4;
-- for t0
DO $$
BEGIN
FOR i IN 1..10 LOOP
FOR j IN 1..100 LOOP
INSERT INTO t0 VALUES(i,j,i+j);
END LOOP;
COMMIT;
END LOOP;
END
$$
;
VACUUM VERBOSE ANALYZE t0;
SELECT COUNT(1) FROM t0;
perftestdb=> \i make_data4postgresql.sql
DO
psql:make_data4postgresql.sql:12: INFO: vacuuming "public.t1"
psql:make_data4postgresql.sql:12: INFO: "t1": found 0 removable, 10 nonremovable row versions in 1 out of 1 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 530026
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
psql:make_data4postgresql.sql:12: INFO: analyzing "public.t1"
psql:make_data4postgresql.sql:12: INFO: "t1": scanned 1 of 1 pages, containing 10 live rows and 0 dead rows; 10 rows in sample, 10 estimated total rows
VACUUM
count
-------
10
(1 行)
DO
psql:make_data4postgresql.sql:30: INFO: vacuuming "public.t2"
psql:make_data4postgresql.sql:30: INFO: "t2": found 0 removable, 50 nonremovable row versions in 1 out of 1 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 530028
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
psql:make_data4postgresql.sql:30: INFO: analyzing "public.t2"
psql:make_data4postgresql.sql:30: INFO: "t2": scanned 1 of 1 pages, containing 50 live rows and 0 dead rows; 50 rows in sample, 50 estimated total rows
VACUUM
count
-------
50
(1 行)
DO
psql:make_data4postgresql.sql:48: INFO: vacuuming "public.t3"
psql:make_data4postgresql.sql:48: INFO: "t3": found 0 removable, 100 nonremovable row versions in 1 out of 1 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 530030
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
psql:make_data4postgresql.sql:48: INFO: analyzing "public.t3"
psql:make_data4postgresql.sql:48: INFO: "t3": scanned 1 of 1 pages, containing 100 live rows and 0 dead rows; 100 rows in sample, 100 estimated total rows
VACUUM
count
-------
100
(1 行)
DO
psql:make_data4postgresql.sql:67: INFO: vacuuming "public.t4"
psql:make_data4postgresql.sql:67: INFO: "t4": found 0 removable, 500 nonremovable row versions in 3 out of 3 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 530041
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
psql:make_data4postgresql.sql:67: INFO: analyzing "public.t4"
psql:make_data4postgresql.sql:67: INFO: "t4": scanned 3 of 3 pages, containing 500 live rows and 0 dead rows; 500 rows in sample, 500 estimated total rows
VACUUM
count
-------
500
(1 行)
DO
psql:make_data4postgresql.sql:85: INFO: vacuuming "public.t0"
psql:make_data4postgresql.sql:85: INFO: "t0": found 0 removable, 1000 nonremovable row versions in 6 out of 6 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 530052
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
psql:make_data4postgresql.sql:85: INFO: analyzing "public.t0"
psql:make_data4postgresql.sql:85: INFO: "t0": scanned 6 of 6 pages, containing 1000 live rows and 0 dead rows; 1000 rows in sample, 1000 estimated total rows
VACUUM
count
-------
1000
(1 行)
MySQL 8.0.32向けデータ登録と統計取得からデータ件数確認までのスクリプトと実行例
mysql> \. cre_advent_tabs.sql
Query OK, 0 rows affected (0.06 sec)
Query OK, 0 rows affected (0.05 sec)
Query OK, 0 rows affected (0.05 sec)
Query OK, 0 rows affected (0.04 sec)
Query OK, 0 rows affected (0.07 sec)
mysql>
MySQL向けデータ登録と統計取得からデータ件数確認までのスクリプトと実行例
make_data4mysql.sql
DELIMITER //
CREATE PROCEDURE make_data4mysql()
BEGIN
DECLARE i, j, k INT;
-- for t1
SET i = 1;
WHILE i <= 10 DO
INSERT INTO t1 VALUES(i,i);
SET i = i + 1;
END WHILE;
COMMIT;
-- for t2
SET i = 1;
WHILE i <= 10 DO
SET j = 1;
WHILE j <= 5 DO
INSERT INTO t2 VALUES(i,j,i+1);
SET j = j + 1;
END WHILE;
SET i = i + 1;
END WHILE;
COMMIT;
-- for t3
SET i = 1;
WHILE i <= 10 DO
SET j = 1;
WHILE j <= 10 DO
INSERT INTO t3 VALUES(i,j,i+1);
SET j = j + 1;
END WHILE;
SET i = i + 1;
END WHILE;
COMMIT;
-- for t4
SET i = 1;
WHILE i <= 10 DO
SET j = 1;
WHILE j <= 10 DO
SET k = 1;
WHILE k <= 5 DO
INSERT INTO t4 VALUES(i,j,k,i+j);
SET k = k + 1;
END WHILE;
SET j = j + 1;
END WHILE;
SET i = i + 1;
COMMIT;
END WHILE;
-- for t0
SET i = 1;
WHILE i <= 10 DO
SET j = 1;
WHILE j <= 100 DO
INSERT INTO t0 VALUES(i,j,i+j);
SET j = j + 1;
END WHILE;
SET i = i + 1;
COMMIT;
END WHILE;
END
//
DELIMITER ;
CALL make_data4mysql();
ANALYZE TABLE t1, t2, t3, t4, t0;
SELECT COUNT(1) FROM t1;
SELECT COUNT(1) FROM t2;
SELECT COUNT(1) FROM t3;
SELECT COUNT(1) FROM t4;
SELECT COUNT(1) FROM t0;
DROP PROCEDURE make_data4mysql;
準備できた！
mysql> \. make_data4mysql.sql
Query OK, 0 rows affected (0.03 sec)
Query OK, 0 rows affected (11.20 sec)
+---------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------+---------+----------+----------+
| perftestdb.t1 | analyze | status | OK |
| perftestdb.t2 | analyze | status | OK |
| perftestdb.t3 | analyze | status | OK |
| perftestdb.t4 | analyze | status | OK |
| perftestdb.t0 | analyze | status | OK |
+---------------+---------+----------+----------+
5 rows in set (0.07 sec)
+----------+
| COUNT(1) |
+----------+
| 10 |
+----------+
1 row in set (0.01 sec)
+----------+
| COUNT(1) |
+----------+
| 50 |
+----------+
1 row in set (0.00 sec)
+----------+
| COUNT(1) |
+----------+
| 100 |
+----------+
1 row in set (0.02 sec)
+----------+
| COUNT(1) |v+----------+
| 500 |
+----------+
1 row in set (0.01 sec)
+----------+
| COUNT(1) |
+----------+
| 1000 |
+----------+
1 row in set (0.02 sec)
Query OK, 0 rows affected (0.01 sec)
mysql>
実行計画を理解するための参考
（Oracleのマニュアルで解説されているBushy Join Treeは、Zigzag Join Treeと言われている結合ツリーに見えますよね。Oracleは、Bushy/Zigzagは区別してなさそう）
参考）
Hash JoinのBuild/Probeって何？ という方は、以下も読んでおくと良いです
Oracle Database Release 23 / SQL Tuning Guide - 9.2.2.2.2 Hash Join: Basic Steps
https://docs.oracle.com/en/database/oracle/oracle-database/23/tgsql/joins.html#GUID-9EE5CD4C-B90C-4E61-83DC-BD585D79635C
結合ツリーついて
Oracle Database Release 23 / SQL Tuning Guide - 9.1.1 Join Trees
https://docs.oracle.com/en/database/oracle/oracle-database/23/tgsql/joins.html#GUID-31B0F249-A5AA-41E9-AE98-A484FC5C487C
では最初に、Oracle Database 21cから。
それっぽいの形にしたHash Joinの実行計画をお見せします。
これは、Oracleのマニュアルで言うと、Bushy Join Tree です。
SCOTT@orclpdb1> @ora_sql_hj.sql
1 SELECT
2 /*+
3 MONITOR
4 USE_HASH(t1 t2 t3 t4)
5 */
6 t1.id
7 , t1.t1_c1
8 , t2.s_id
9 , t2.t2_c1
10 , t3.b_id
11 , t3.t3_c1
12 , t4.a_id
13 , t4.c_id
14 , t4.t4_c1
15 FROM
16 t1
17 INNER JOIN t2
18 ON
19 t1.id = t2.id
20 INNER JOIN t3
21 ON
22 t1.id = t3.id
23 INNER JOIN t4
24 ON
25* t1.id = t4.id
...略...
SQL Plan Monitoring Details (Plan Hash Value=122725940)
===================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) |
===================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 25000 | | | . | | |
| 1 | HASH JOIN | | 25000 | 12 | 1 | +0 | 1 | 25000 | | | 1MB | | |
| 2 | TABLE ACCESS FULL | T4 | 500 | 3 | 1 | +0 | 1 | 500 | 2 | 49152 | . | | |
| 3 | HASH JOIN | | 500 | 9 | 1 | +0 | 1 | 500 | | | 1MB | | |
| 4 | HASH JOIN | | 50 | 6 | 1 | +0 | 1 | 50 | | | 1MB | | |
| 5 | TABLE ACCESS FULL | T1 | 10 | 3 | 1 | +0 | 1 | 10 | 2 | 49152 | . | | |
| 6 | TABLE ACCESS FULL | T2 | 50 | 3 | 1 | +0 | 1 | 50 | 2 | 49152 | . | | |
| 7 | TABLE ACCESS FULL | T3 | 100 | 3 | 1 | +0 | 1 | 100 | 2 | 49152 | . | | |
===================================================================================================================================================
上記の解説
Id=5 T1をTable Full Scan (Build表)
Id=6 T2をTable Full Scan (Probe表).
Id=4 T1とT2をHash Join (on memoryでHash Join完了）
Id=4 Id=5,6のHash Joinの結果(Build表)
Id=7 T3をTable Full Scan (Probe表)
Id=3 Id=4の結合結果とT3をHash Join (on memoryでHash Join完了）
Id=2 T4をTable Full Scan (Build表)
Id=3 Id=4の結合結果とT3の結合結果 (Probe表)
Id=2 T4とId=3の結合結果をHash Join (On memoryでHash Join完了）
という順序なので、
引用したOracleのマニュアルで言う、Bushy Join Tree になっています。
Oracleのマニュアルでは、Bushy Join Treeとなっていますが、他では、Zigzag Join Treeとして分類されていたりするツリーです。。LeftとRight Deep Join TreeがMixされた形ですよね、これ。
これを結合ツリーで書くと、以下のようになります。
次は、PostgreSQL 13.4 with pg_hint_plan 1.3.9で同じくHash Joinになるようにヒントで強制した実行計画です。
この結合ツリーは、Bushy Join Treeですね。
perftestdb=> \! cat pg_sql_hj.sql
explain (analyze)
SELECT
/*+
HashJoin(t1 t2 t3 t4)
*/
t1.id
, t1.t1_c1
, t2.s_id
, t2.t2_c1
, t3.b_id
, t3.t3_c1
, t4.a_id
, t4.c_id
, t4.t4_c1
FROM
t1
INNER JOIN t2
ON
t1.id = t2.id
INNER JOIN t3
ON
t1.id = t3.id
INNER JOIN t4
ON
t1.id = t4.id
;
perftestdb=> \i pg_sql_hj.sql
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Id=1 Hash Join (cost=17.48..309.84 rows=25000 width=36) (actual time=3.405..10.466 rows=25000 loops=1)
Hash Cond: (t1.id = t2.id)
Id=2 -> Hash Join (cost=1.23..11.09 rows=500 width=24) (actual time=1.696..1.989 rows=500 loops=1)
Hash Cond: (t4.id = t1.id)
Id=3 -> Seq Scan on t4 (cost=0.00..8.00 rows=500 width=16) (actual time=0.907..0.993 rows=500 loops=1)
Id=4 -> Hash (cost=1.10..1.10 rows=10 width=8) (actual time=0.721..0.722 rows=10 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
Id=5 -> Seq Scan on t1 (cost=0.00..1.10 rows=10 width=8) (actual time=0.705..0.707 rows=10 loops=1)
Id=6 -> Hash (cost=10.00..10.00 rows=500 width=24) (actual time=1.687..1.687 rows=500 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 36kB
Id=7 -> Hash Join (cost=2.12..10.00 rows=500 width=24) (actual time=1.400..1.554 rows=500 loops=1)
Hash Cond: (t3.id = t2.id)
Id=8 -> Seq Scan on t3 (cost=0.00..2.00 rows=100 width=12) (actual time=0.701..0.712 rows=100 loops=1)
Id=9 -> Hash (cost=1.50..1.50 rows=50 width=12) (actual time=0.679..0.680 rows=50 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 11kB
Id=10 -> Seq Scan on t2 (cost=0.00..1.50 rows=50 width=12) (actual time=0.648..0.656 rows=50 loops=1)
解説しやすいように実行計画のノードにIdを振っています:)
Oracleの実行計画と大きく違うのは、Build表とProbe表の順序です。Oracleの場合、同一階層の上位が、Build表で、その下にProbe表がリストされますが、
PostgreSQL(MySQLも同様）では、同一階層上で、Probe表が上位に、その下に Hash（ハッシュ表を作成している操作。Oracleでは実行計画上この操作は現れません）、さらにその下位階層にBuild表のTable Full scanという形で表現されています。
階層の一番深い部分から実行されるので、上記例だと、Id=16のt2（Build表）のSeq Scan (Oracleで言うTable Full Scanが最初に実行され、次に、Id=15のHashが行われていることがわかります。
Id=10 t2 Seq Scan (Oracleで言うTable Full Scan)
Id=9 Id=10の結果を元にHash表作成 （つまり、t2がBuild)
Id=8 t3 Seq Scan (Probe)
Id=7 t2, t3をHash Join
Id=6 Id=7(t2, t3のHash Join)の結果を元にHash表作成（Build)
Id=5 t1 Seq Scan
Id=4 Id=5の結果を元にHash表作成（Build）
Id=3 t4 Seq Scan (Probe)
Id=2 t1, t4をHash Join
Id=1 Id=6の結果(Build), Id=2の結果（Probe)としてHash Join
こうやって1Step毎に追っていくと、OracleとPostgreSQLの実行計画のTree listで、Hash JoinのBuildとProbeの位置が異なっていることに気づきやすいですよね。（ちなみに、MySQLのTree formatも同様です）
これを結合ツリーで書くと、以下のようになります。
最後に、MySQL 8.0.32です。
Hash Joinを強制する直接的なヒントも、Budling/Probe表を制御するヒントも方法存在しませんが、Hash Joinになるよう結合順を指定した上で、Nested Loop Joinになってしまうのを抑止するために、Nested Loop Joindで利用する主キー索引の利用を抑止しました。
（MySQLの主キーはクラスター索引なので、主キーがNested Loop Joinに利用される場合、索引の利用を抑止するには、NO_JOIN_INDEXヒントで primary を指定する必要があります）
なお、JOIN_ORDERヒントで結合順を指定した理由は、結合順を指定することでNested Loop Joinに利用される主キー(primary)を、NO_JOIN_INDEXヒントで確実抑止したかったためです。
ちなみに、この結合ツリーは、Left Deep Join Treeですね。
mysql> \! cat my_sql_leftdj.sql
explain analyze
SELECT
/*+
JOIN_ORDER(t1,t2,t3,t4)
NO_JOIN_INDEX(t2 primary)
NO_JOIN_INDEX(t3 primary)
NO_JOIN_INDEX(t4 primary)
*/
t1.id
, t1.t1_c1
, t2.s_id
, t2.t2_c1
, t3.b_id
, t3.t3_c1
, t4.a_id
, t4.c_id
, t4.t4_c1
FROM
t1
INNER JOIN t2
ON
t1.id = t2.id
INNER JOIN t3
ON
t1.id = t3.id
INNER JOIN t4
ON
t1.id = t4.id
;
mysql> \. my_sql_leftdj.sql
+-----------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-----------------------------------------------------------------------------------------------------------------------+
Id=1 | -> Inner hash join (t4.id = t1.id) (cost=25552.55 rows=25000) (actual time=0.643..5.045 rows=25000 loops=1)
Id=2 -> Table scan on t4 (cost=0.01 rows=500) (actual time=0.052..0.346 rows=500 loops=1)
Id=3 -> Hash
Id=4 -> Inner hash join (t3.id = t1.id) (cost=551.75 rows=500) (actual time=0.216..0.380 rows=500 loops=1)
Id=5 -> Table scan on t3 (cost=0.03 rows=100) (actual time=0.041..0.069 rows=100 loops=1)
Id=6 -> Hash
Id=7 -> Inner hash join (t2.id = t1.id) (cost=51.50 rows=50) (actual time=0.100..0.141 rows=50 loops=1)
Id=8 -> Table scan on t2 (cost=0.08 rows=50) (actual time=0.026..0.041 rows=50 loops=1)
Id=9 -> Hash
Id=10 -> Table scan on t1 (cost=1.25 rows=10) (actual time=0.039..0.047 rows=10 loops=1)
|
+-----------------------------------------------------------------------------------------------------------------------+
既にお分かりだと思いますが、答え合わせです。:)
OracleのRight Deep Joinに見慣れている皆さん（私も含むw）が、Right Deep Joinと勘違いしちゃうケースはありますが、Build表とProbe表の現れる位置が、Oracleの実行計画と逆なんですよね。
（何度見ても、OracleでRight Deep Join Treeにした実行計画の形とダブってしまって、慣れないw）
このような形になっているHash Joinの実行計画って、PostgreSQLも含め、Left Deep Join Treeなんですよね。
参考）MySQLが、Left Deep Join Treeにしかならない理由はこれ。
Chapter 4. Query Performance Optimization The execution plan
"MySQL always begins with one table and finds matching rows in the next table. Thus, MySQL’s query execution plans always take the form of a left-deep tree"とあります。
Nested Loop Joinって、Left Deep Join Treeになるから、MySQLだとHash Joinでも、 Left Deep Join Treeなのかなぁ。と。ただ、一時期、クラウド方面にあるMySQL互換で、HASH_JOIN_BUILDINGとかいうヒントが加えられていたこともありました。NewSQL系でも拡張されているケースもあるようです。
前述のMySQLの実行計画を追っかけてみると、、、
Id=10 t1 Table Scan
Id=11 Id=10で読み込んだ結果からHash表を作成（つまり、t1がBuild）
Id=7,8 T2 Table Scan (Probe）してId=11とHash Join
Id=6 Id=7のHash Joinの結果からHash表作成 (Build）
Id=4,5 T3 Table Scan (Probe)してId=6とHash Join
Id=3 Id=4のHash Joinの結果からHash表作成（Build）
Id=1,2 T4 Table Scan (Probe)しId=3とHash Join
これを結合ツリーで書くと、以下のように Left Deep Treeになりまよね。
（表示されているExlplainの実行計画を見ていると、OracleのHash JoinでRight Deep Join Treeに錯覚してしまうのなんとかならんかねぇ。と思いつつ、慣れるしかないな。と言う答えしか出てこなかった。PostgreSQLも同じ表示方法だし。慣れろ→自分w）
ということで、
MySQLのHash Joinの実行計画ツリーを見て、何か気づきませんか？
みなさん！ これ試験に出ますよw（嘘
の答えは、
MySQLは、Left Deep Join Tree しか、しない。でした！
ざっくりとしたまとめ的なやつ
Oracle/PostgreSQL/MySQLそれぞれ、Hash Joinだけを強制して結合順はオプティマイザー任せにした場合。
Oracle/PostgreSQLは、Bushy Join Tree
データ次第では、Left/Right Deep Join Treeの可能性もあるでしょうけども。今回のケースでは、Bushy Join Tree, 細かめに分類すると、Oracleの結合ツリーは、Zigzagですね。ちなみに、Oracleには、BUSHY_JOINというヒントがあったりします（使ったことないけどw）。
MySQLは、Left Deep Join Tree にしかならんよ。と。
以下の方針が変更されない限り、変わることはなさそう。
再掲
https://www.oreilly.com/library/view/high-performance-mysql/9780596101718/ch04.html#how_mysql_joins_multiple_table
"MySQL always begins with one table and finds matching rows in the next table. Thus, MySQL’s query execution plans always take the form of a left-deep tree"
予習、その３へ続く。
では、また。:)
関連エントリー
・標準はあるにはあるが癖の多いSQL 全部俺 #1 Pagination
・標準はあるにはあるが癖の多いSQL 全部俺 #2 関数名は同じでも引数が逆の罠！
・標準はあるにはあるが癖の多いSQL 全部俺 #3 データ型確認したい時あるんです
・標準はあるにはあるが癖の多いSQL 全部俺 #4 リテラル値での除算の内部精度も違うのよ!
・標準はあるにはあるが癖の多いSQL 全部俺 #5 和暦変換機能ある方が少数派
・標準はあるにはあるが癖の多いSQL 全部俺 #6 時間厳守！
・標準はあるにはあるが癖の多いSQL 全部俺 #7 期間リテラル！
・標準はあるにはあるが癖の多いSQL 全部俺 #8 翌月末日って何日？
・標準はあるにはあるが癖の多いSQL 全部俺 #9 部分文字列の扱いでも癖が出る＞＜
・標準はあるにはあるが癖の多いSQL 全部俺 #10 文字列連結の罠（有名なやつ）
・標準はあるにはあるが癖の多いSQL 全部俺 #11 デュエル、じゃなくて、デュアル
・標準はあるにはあるが癖の多いSQL 全部俺 #12 文字[列]探すにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #13 あると便利ですが意外となかったり
・標準はあるにはあるが癖の多いSQL 全部俺 #14 連番の集合を返すにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #15 SQL command line client
・標準はあるにはあるが癖の多いSQL 全部俺 #16 SQLのレントゲンを撮る方法
・標準はあるにはあるが癖の多いSQL 全部俺 #17 その空白は許されないのか？
・標準はあるにはあるが癖の多いSQL 全部俺 #18 (+)の外部結合は方言
・標準はあるにはあるが癖の多いSQL 全部俺 #19 帰ってきた、部分文字列の扱いでも癖w
・標準はあるにはあるが癖の多いSQL 全部俺 #20 結果セットを単一列に連結するにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #21 演算結果にも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #22 集合演算にも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #23 複数行INSERTにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #24 乱数作るにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #25 SQL de Fractalsにも癖がある:)
・標準はあるにはあるが癖の多いSQL 全部俺 おまけ SQL de 湯婆婆やるにも癖がでるw
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #1 SQL de ROT13 やるにも癖が出るw・帰ってきた！ 標準はあるにはあるが癖の多いSQL #2 Actual Plan取得中のキャンセルでも癖が出る・帰ってきた！ 標準はあるにはあるが癖の多いSQL #3 オプティマイザの結合順評価テーブル数上限にも癖が出る
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #4 Optimizer Traceの取得でも癖がでる
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #5 - Optimizer Hint でも癖が多い
| 固定リンク | 0
コメント