MySQL 8.0.32では NLJに使えるINDEXが存在していても、Hash Joinをヒントで強制することができる！（オプティマイザが選択することがある！） Tweet
Previously on Mac De Oracle
前回は悩ませ過ぎは及ばざるがごとし （MySQL 8.0.32編）で、パース時間が長い場合、ヒントを使うことでパース時間を短縮させることが可能なケースもあるというという話でした。Oracleでも同様のケースがあることもお話ししました。が、その中で、MySQL 8.0.32 ではオプティマイザが、NLJで利用するINDEXが存在していても、HJを選択することがあることに気づいた！（どうやら、該当表がバッファープールに乗っているとNLJを選択しやすくなる傾向もありそう）という、予告編までw
ということで、今日は、MySQL 8.0.32 では、NLJに利用するINDEXが存在していても、オプティマイザは、HJを選択することもあるし、ヒントによってHJを矯正できるのか？ ということを確認してみました。
これまでの、MySQL 8.0.x台のHash Joinの適用範囲および挙動は以下のブログで取り上げられています。（非常に参考になりました。ありがたいですね。本当に ;)
MySQL 8.0.18のHASH JOINを試した（tom__bo’s Blog）
https://tombo2.hatenablog.com/entry/2019/10/14/212100
MySQL 8.0.20 のハッシュジョイン（Hash Join）を INDEX があるテーブルで試してみる（Qiita @hmatsu47）
https://qiita.com/hmatsu47/items/e473a3e566b910d61f5b
MySQL 8.0.20 でHASH JOINが効くケースが拡大した (mita2 database life)
https://mita2db.hateblo.jp/entry/2020/05/03/174101
と、NLJ可能な索引があるとなぜか、HJにならなかった。ついでに、HASH_JOINというヒントは、8.0.18でしか効果がない！
https://dev.mysql.com/doc/refman/8.0/ja/optimizer-hints.html#optimizer-hints-table-level
MySQL 8.0 Manual
https://dev.mysql.com/doc/refman/8.0/en/hash-joins.html
"8.2.1.4 Hash Join Optimization By default, MySQL (8.0.18 and later) employs hash joins whenever possible. It is possible to control whether hash joins are employed using one of the BNL and NO_BNL optimizer hints, or by setting block_nested_loop=on or block_nested_loop=off as part of the setting for the optimizer_switch server system variable.Note
MySQL 8.0.18 supported setting a hash_join flag in optimizer_switch, as well as the optimizer hints HASH_JOIN and NO_HASH_JOIN. In MySQL 8.0.19 and later, none of these have any effect any longer.Beginning with MySQL 8.0.18, MySQL employs a hash join for any query for which each join has an equi-join condition, and in which there are no indexes that can be applied to any join conditions, such as this one:"
ななな、なーーーん、だってーーーーーーっ！。
だが、しかし、8.0.32 になるとマニュアルの記載では特に見かけなかったが、NLJに使える索引がある場合でも等価結合でHash Joinになるではありませんか。みなさん！
まじでーーーというか、実際、そうなるもん！
以下、前回のブログ参照のこと。
悩ませ過ぎは及ばざるがごとし （MySQL 8.0.32編） (Mac De Oracle)
https://discus-hamburg.cocolog-nifty.com/mac_de_oracle/2023/09/post-bee50f.html
ただし、 HASH_JOINヒントは使えないので、どのようにヒントを書けば Hash Joinを強制できるのだろう？ 8.0.32編 ということで、試してみた！
結論から言ってしまうと
MySQL 8.0.32 では NLJに使えるINDEXが存在していても、Hash Joinを強制することができる！ （いつから変わったかは不明だが、8.0.32では可能！）
MySQL 8.0.32 では NLJに使えるINDEXが存在していても、Hash Joinを強制することができる！ （いつから変わったかは不明だが、8.0.32では可能！）
詳細は、以下の検証を。比較的気楽にヒントでHJを強制できるようです（だたしちょっと分かりずらいね。Oracleとかと比べると）
表定義等は前々回のエントリー参照のこと。帰ってきた！ 標準はあるにはあるが癖の多いSQL #3 オプティマイザの結合順評価テーブル数上限にも癖が出る
起動直後、バッファキャッシュが空だと（未検証）Hash Joinが選択されるので、まずは、オプティマイザが Hash Join (等価結合でNLJに利用できる索引が存在する状況でも）が選択されている（前回のブログのおさらい）
[master@localhost ~]$ sudo service mysqld restart
Redirecting to /bin/systemctl restart mysqld.service
[master@localhost ~]$ mysql -u scott -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.32 Source distribution
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.32 |
+-----------+
1 row in set (0.00 sec)
mysql>
mysql> \. test1.sql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
+----------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------------------------------------------------------------------------+
| -> Inner hash join (t10.id = `master`.id) (cost=20489.43 rows=10240)
-> Table scan on t10 (cost=0.00 rows=20)
-> Hash
-> Inner hash join (t9.id = `master`.id) (cost=10238.74 rows=5120)
-> Table scan on t9 (cost=0.00 rows=20)
-> Hash
-> Inner hash join (t8.id = `master`.id) (cost=5113.38 rows=2560)
-> Table scan on t8 (cost=0.00 rows=20)
-> Hash
-> Inner hash join (t7.id = `master`.id) (cost=2550.45 rows=1280)
-> Table scan on t7 (cost=0.00 rows=20)
-> Hash
-> Inner hash join (t6.id = `master`.id) (cost=1268.60 rows=640)
-> Table scan on t6 (cost=0.00 rows=20)
-> Hash
-> Inner hash join (t5.id = `master`.id) (cost=627.24 rows=320)
-> Table scan on t5 (cost=0.01 rows=20)
-> Hash
-> Inner hash join (t4.id = `master`.id) (cost=306.09 rows=160)
-> Table scan on t4 (cost=0.02 rows=20)
-> Hash
-> Inner hash join (t3.id = `master`.id) (cost=145.03 rows=80)
-> Table scan on t3 (cost=0.03 rows=20)
-> Hash
-> Inner hash join (t2.id = `master`.id) (cost=64.01 rows=40)
-> Table scan on t2 (cost=0.06 rows=20)
-> Hash
-> Inner hash join (detail.id = `master`.id) (cost=23.00 rows=20)
-> Table scan on detail (cost=0.12 rows=20)
-> Hash
-> Index scan on master using ix_master (cost=2.00 rows=10)
|
+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
関連する表をアクセスしてバッファープールに乗った状態にすると。オプティマイザは、INDEXを利用したNLJを選択するようになった。（前回のブログのおさらい）
mysql>
mysql> select * from master;
+----+--------+
| id | dummya |
+----+--------+
| 1 | 1 |
| 10 | 10 |
....中略....
| 7 | 7 |
| 8 | 8 |
| 9 | 9 |
+----+--------+
10 rows in set (0.01 sec)
mysql> select * from detail;
+----+-------+--------+
| id | subid | dummya |
+----+-------+--------+
| 1 | 1 | 11 |
| 1 | 2 | 12 |
| 2 | 1 | 21 |
| 2 | 2 | 22 |
....中略....
| 9 | 1 | 91 |
| 9 | 2 | 92 |
| 10 | 1 | 101 |
| 10 | 2 | 102 |
+----+-------+--------+
20 rows in set (0.01 sec)
mysql> \. test1.sql
Database changed
+----------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------------------------------------------------------------------------+
| -> Nested loop inner join (cost=7163.00 rows=10240)
-> Nested loop inner join (cost=3579.00 rows=5120)
-> Nested loop inner join (cost=1787.00 rows=2560)
-> Nested loop inner join (cost=891.00 rows=1280)
-> Nested loop inner join (cost=443.00 rows=640)
-> Nested loop inner join (cost=219.00 rows=320)
-> Nested loop inner join (cost=107.00 rows=160)
-> Nested loop inner join (cost=51.00 rows=80)
-> Nested loop inner join (cost=23.00 rows=40)
-> Nested loop inner join (cost=9.00 rows=20)
-> Index scan on master using ix_master (cost=2.00 rows=10)
-> Index lookup on detail using PRIMARY (id=`master`.id) (cost=0.52 rows=2)
-> Index lookup on t2 using PRIMARY (id=`master`.id) (cost=0.51 rows=2)
-> Index lookup on t3 using PRIMARY (id=`master`.id) (cost=0.51 rows=2)
-> Index lookup on t4 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t5 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t6 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t7 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t8 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t9 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t10 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
|
+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
オプティマイザがNLJを選択するようになった状況で、Hash Join となるヒントを追加（ヒント等は後半のスクリプト test1_hj.sql を参照のこと）すると、NLJで利用可能なINDEXが存在していたとしても、HJを強制できる。(8.0.20ごろまでは、HJは選択されなかったようなので、8.0.21から8.0.32の間でHJの適用範囲がさらに拡大されたのでしょうね。想像ですけども）
mysql>
mysql> \. test1_hj.sql
Database changed
+----------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------------------------------------------------------------------------+
| -> Inner hash join (t10.id = `master`.id) (cost=20468.86 rows=10240)
-> Table scan on t10 (cost=0.00 rows=20)
-> Hash
-> Inner hash join (t9.id = `master`.id) (cost=10226.19 rows=5120)
-> Table scan on t9 (cost=0.00 rows=20)
-> Hash
-> Inner hash join (t8.id = `master`.id) (cost=5104.85 rows=2560)
-> Table scan on t8 (cost=0.00 rows=20)
-> Hash
-> Inner hash join (t7.id = `master`.id) (cost=2544.11 rows=1280)
-> Table scan on t7 (cost=0.00 rows=20)
-> Hash
-> Inner hash join (t6.id = `master`.id) (cost=1263.65 rows=640)
-> Table scan on t6 (cost=0.00 rows=20)
-> Hash
-> Inner hash join (t5.id = `master`.id) (cost=623.31 rows=320)
-> Table scan on t5 (cost=0.00 rows=20)
-> Hash
-> Inner hash join (t4.id = `master`.id) (cost=303.02 rows=160)
-> Table scan on t4 (cost=0.01 rows=20)
-> Hash
-> Inner hash join (t3.id = `master`.id) (cost=142.76 rows=80)
-> Table scan on t3 (cost=0.01 rows=20)
-> Hash
-> Inner hash join (t2.id = `master`.id) (cost=62.50 rows=40)
-> Table scan on t2 (cost=0.02 rows=20)
-> Hash
-> Inner hash join (detail.id = `master`.id) (cost=22.25 rows=20)
-> Table scan on detail (cost=0.05 rows=20)
-> Hash
-> Table scan on master (cost=2.00 rows=10)
|
+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)
ついでなので、NLJになるようにがっつりヒントで固めてみた場合はどうなるか？ 想定通り、NLJに倒せますね。（前回のブログのおさらい）
mysql>
mysql> \. test1_nlj.sql
Database changed
+----------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------------------------------------------------------------------------+
| -> Nested loop inner join (cost=7173.00 rows=10240)
-> Nested loop inner join (cost=3589.00 rows=5120)
-> Nested loop inner join (cost=1797.00 rows=2560)
-> Nested loop inner join (cost=901.00 rows=1280)
-> Nested loop inner join (cost=453.00 rows=640)
-> Nested loop inner join (cost=229.00 rows=320)
-> Nested loop inner join (cost=117.00 rows=160)
-> Nested loop inner join (cost=61.00 rows=80)
-> Nested loop inner join (cost=33.00 rows=40)
-> Nested loop inner join (cost=19.00 rows=20)
-> Index scan on master using ix_master (cost=12.00 rows=10)
-> Index lookup on detail using PRIMARY (id=`master`.id) (cost=0.52 rows=2)
-> Index lookup on t2 using PRIMARY (id=`master`.id) (cost=0.51 rows=2)
-> Index lookup on t3 using PRIMARY (id=`master`.id) (cost=0.51 rows=2)
-> Index lookup on t4 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t5 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t6 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t7 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t8 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t9 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
-> Index lookup on t10 using PRIMARY (id=`master`.id) (cost=0.50 rows=2)
|
+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
検証で利用した 表と索引定義は前回のブログ参照のこと。
以下、今回検証で利用したスクリプト
オリジナル。結合順だけ JOIN_ORDERで制御。オプティマイザが、NLJ/HJをその時の気分で（嘘w）、その時の状況で判断して、NLJ/HJのいずれかを選択する（ことがわかっている 8.0.32 では）
[master@localhost ~]$ cat test1.sql
use perftestdb;
explain format=tree
select
/*+
JOIN_ORDER(master,detail,t2,t3,t4,t5,t6,t7,t8,t9,t10)
*/
*
from
master inner join detail
on master.id = detail.id
inner join detail t2
on
t2.id = detail.id
inner join detail t3
on
t3.id = t2.id
inner join detail t4
on
t4.id = t3.id
inner join detail t5
on
t5.id = t4.id
inner join detail t6
on
t6.id = t5.id
inner join detail t7
on
t7.id = t6.id
inner join detail t8
on
t8.id = t7.id
inner join detail t9
on
t9.id = t8.id
inner join detail t10
on
t10.id = t9.id
;
JOIN_ORDER/NO_INDEX/NO_JOIN_INDEXヒントを利用して、結合順と索引スキャンの抑止、そして、NLJで利用する索引の利用を抑止することで HJ になるようにしたスクリプト
[master@localhost ~]$ cat test1_hj.sql
use perftestdb;
explain format=tree
select
/*+
JOIN_ORDER(master,detail,t2,t3,t4,t5,t6,t7,t8,t9,t10)
NO_INDEX(master ix_master)
NO_JOIN_INDEX(detail primary)
NO_JOIN_INDEX(t2 primary)
NO_JOIN_INDEX(t3 primary)
NO_JOIN_INDEX(t4 primary)
NO_JOIN_INDEX(t5 primary)
NO_JOIN_INDEX(t6 primary)
NO_JOIN_INDEX(t7 primary)
NO_JOIN_INDEX(t8 primary)
NO_JOIN_INDEX(t9 primary)
NO_JOIN_INDEX(t10 primary)
*/
*
from
master inner join detail
on master.id = detail.id
inner join detail t2
on
t2.id = detail.id
inner join detail t3
on
t3.id = t2.id
inner join detail t4
on
t4.id = t3.id
inner join detail t5
on
t5.id = t4.id
inner join detail t6
on
t6.id = t5.id
inner join detail t7
on
t7.id = t6.id
inner join detail t8
on
t8.id = t7.id
inner join detail t9
on
t9.id = t8.id
inner join detail t10
on
t10.id = t9.id
;
最後に、前回も利用した NLJ に倒すためのガチガチヒントバージョンのスクリプト
[master@localhost ~]$ cat test1_nlj.sql
use perftestdb;
explain format=tree
select
/*+
JOIN_ORDER(master,detail,t2,t3,t4,t5,t6,t7,t8,t9,t10)
INDEX(master ix_master)
JOIN_INDEX(detail primary)
JOIN_INDEX(t2 primary)
JOIN_INDEX(t3 primary)
JOIN_INDEX(t4 primary)
JOIN_INDEX(t5 primary)
JOIN_INDEX(t6 primary)
JOIN_INDEX(t7 primary)
JOIN_INDEX(t8 primary)
JOIN_INDEX(t9 primary)
JOIN_INDEX(t10 primary)
*/
*
from
master inner join detail
on master.id = detail.id
inner join detail t2
on
t2.id = detail.id
inner join detail t3
on
t3.id = t2.id
inner join detail t4
on
t4.id = t3.id
inner join detail t5
on
t5.id = t4.id
inner join detail t6
on
t6.id = t5.id
inner join detail t7
on
t7.id = t6.id
inner join detail t8
on
t8.id = t7.id
inner join detail t9
on
t9.id = t8.id
inner join detail t10
on
t10.id = t9.id
;
ということで、まだまだまだ、蒸し暑いし、台風直撃しそうな、東京からお送りしました。台風に注意しつつ週末を過ごすしかなさそう。
では、また。
・悩ませ過ぎは及ばざるがごとし （MySQL 8.0.32編）
| 固定リンク | 0
コメント