悩ませ過ぎは及ばざるがごとし （MySQL 8.0.32編） Tweet
Mac De OracleでMySQLとかPostgreSQLネタやるのって、2006年ごろの、異機種間接続サービス(Oracle Generic Connectivity)以来のような感覚になりますなw
あの頃は、
MySQL4.0.25 (MacOSX 10.4 Tiger)
MySQL4.0.26 (WindowsXP Pro)
MySQL4.1.13a (MacOSX 10.4 Tiger Server付属のもの)
PostgreSQL7.4.9 (MacOSX 10.4 Tiger)
Oracle10g R1 EE 10.1.0.3 for MacOSX
で遊んでた頃なので、みんな、それぞれ、バージョンが育ったりしてますよねwwww
では本題
Previously on Mac De Oracle
前回は、帰ってきた！ 標準はあるにはあるが癖の多いSQL #3 オプティマイザの結合順評価テーブル数上限にも癖が出る でした。
MySQLで結合数が多いとオプティマイザが考え過ぎてパース時間が伸びるケースとシステムパラメータでそれを制御する例でした。前回のエントリーでもちょっと書きましたが、似たようなケースで、Oracleではオプティマイザにそもそも頭を使わせないでパース時間を短縮させちゃうなんて、荒技でもな、テクニックがあります。それは単純、ヒントを指定してオプティマイザに該当部分で頭を使わせないようにするw
という方法です。
上記の例は、結合ではなくINリストとヒントでオプティマイザに考えさせないという方法です。
（なお、Oracleのヒントの指定方法は長年の研究によるちょっと特徴のあるヒントの指定方法ですがw 大丈夫ですよ。ちゃんと機能しますのでw 最近、OracleのSQLヒントレポートが出力できるようになったので、その辺り気にする人もいるのですけどもね。引用先のOracle SQLヒントの利用方法は、パース時間短縮専用ということではなく、一般的に私がよく使う方法でもあります。その辺のネタは次回にでもw）
では、MySQLでも効果があるのか、試してみましょう！ というのが今日のお題。
MySQL 8.0.32で、パースに時間を要するケースの再現。optimizer_search_depthパラメータはデフォルトのまま
MySQL : optimizer_search_depth optimizer_search_depth
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.32 |
+-----------+
1 row in set (0.00 sec)
>
mysql> show variables like 'optimizer_search_depth';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| optimizer_search_depth | 62 |
+------------------------+-------+
1 row in set (0.00 sec)
mysql> \. test1.sql
Database changed
+----------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------------------------------------------------------------------------+
| -> Nested loop inner join (cost=7162.25 rows=10240)
-> Nested loop inner join (cost=3578.25 rows=5120)
-> Nested loop inner join (cost=1786.25 rows=2560)
-> Nested loop inner join (cost=890.25 rows=1280)
-> Nested loop inner join (cost=442.25 rows=640)
-> Nested loop inner join (cost=218.25 rows=320)
-> Nested loop inner join (cost=106.25 rows=160)
-> Nested loop inner join (cost=50.25 rows=80)
-> Nested loop inner join (cost=22.25 rows=40)
-> Nested loop inner join (cost=8.25 rows=20)
-> Index scan on master using ix_master (cost=1.25 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 (21.37 sec)
パースに 21.37 sec 要していますね。
というところまでは、前回のエントリーと同じですが、次は、パラメータで調整するのではなく、結合順を考えさせないためにヒントで結合順を指定みましょう。どうなりますかね。。。楽しみです。
JOIN_ORDERヒントで結合順を全て指定します。OracleのLEADINGヒントに似てますよね。
[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
;
MySQLを起動して。。。試してみます。
[master@localhost ~]$ sudo service mysqld start
[sudo] master のパスワード:
Redirecting to /bin/systemctl start 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 11
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> \. 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.00 sec)
パースめちゃめちゃ早くなりました！ w オプティマイザよ、考えるな！ 感じろ！ いや、でも考えて！ オプティマイザ！w
ただ、妙なのは、見積もりだからでしょうか？？？？？？ 等価結合なのにHash Joinになってます！ MySQLってOracleとかPostgreSQLっぽくHash Join行ったりするんでしたっけ？？？？？？ 謎。。。。。
統計情報をとってあります。（前回の通りですが。。。。。。）
ちょっと気になるところがあります。前々回もそうだったので、MySQL起動直後だけ、該当表を一度もアクセスしていない場合には、なぜか、Hash Joinになるというのが再現しています！！！！！！ なになになにこれ。。。。
簡単な実験、analyzeとかしませんよ。単純に、利用する表を全表走査してみますね。そのあとで、実行計画がどうなるかみてみましょう。ヒントは結合順の指定だけですよ。
mysql>
mysql>
mysql> select * from master;
+----+--------+
| id | dummya |
+----+--------+
| 1 | 1 |
| 10 | 10 |
...中略...
| 7 | 7 |
| 8 | 8 |
| 9 | 9 |
+----+--------+
10 rows in set (0.00 sec)
mysql> select * from detail;
+----+-------+--------+
| id | subid | dummya |
+----+-------+--------+
| 1 | 1 | 11 |
| 1 | 2 | 12 |
| 2 | 1 | 21 |
| 2 | 2 | 22 |
| 3 | 1 | 31 |
...中略...
| 8 | 2 | 82 |
| 9 | 1 | 91 |
| 9 | 2 | 92 |
| 10 | 1 | 101 |
| 10 | 2 | 102 |
+----+-------+--------+
20 rows in set (0.01 sec)
どうなるか変化するかどうか、乞うご期待！！！！！
きたーーーーーー。NLJに変化しました。。。。。MySQL的に、現時点ではこの実行計画の方が自然なのでは？？？？？ 教えて、マイエスキューエルの人！
本題である、パース時間は見事に早くなってますよね！！！ 結合順を考えなくて済むわけですから、早いに決まってます！
考えるな！ 感じろ！ (でも考えて、オプティマイザ！ 大切なので2度目の雄叫びw）
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)
MySQL 8.0.32、色々と楽しませてくれますねー。。 Oracle 7.0の挙動と戯れてたときを思い出しますねぇ〜。
おまけ、念の為ヒントバリバリで固めてみましたw 結果は同じです。 NLJになってくれてます
[master@localhost ~]$ cat test1.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> \. test1.sql
Database changed
+----------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------------------------------------------------------------------------+
| -> Nested loop inner join (cost=22518.00 rows=10240)
-> Nested loop inner join (cost=11254.00 rows=5120)
-> Nested loop inner join (cost=5622.00 rows=2560)
-> Nested loop inner join (cost=2806.00 rows=1280)
-> Nested loop inner join (cost=1398.00 rows=640)
-> Nested loop inner join (cost=694.00 rows=320)
-> Nested loop inner join (cost=342.00 rows=160)
-> Nested loop inner join (cost=166.00 rows=80)
-> Nested loop inner join (cost=78.00 rows=40)
-> Nested loop inner join (cost=34.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=2.02 rows=2)
-> Index lookup on t2 using PRIMARY (id=`master`.id) (cost=2.01 rows=2)
-> Index lookup on t3 using PRIMARY (id=`master`.id) (cost=2.00 rows=2)
-> Index lookup on t4 using PRIMARY (id=`master`.id) (cost=2.00 rows=2)
-> Index lookup on t5 using PRIMARY (id=`master`.id) (cost=2.00 rows=2)
-> Index lookup on t6 using PRIMARY (id=`master`.id) (cost=2.00 rows=2)
-> Index lookup on t7 using PRIMARY (id=`master`.id) (cost=2.00 rows=2)
-> Index lookup on t8 using PRIMARY (id=`master`.id) (cost=2.00 rows=2)
-> Index lookup on t9 using PRIMARY (id=`master`.id) (cost=2.00 rows=2)
-> Index lookup on t10 using PRIMARY (id=`master`.id) (cost=2.00 rows=2)
|
+----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
なんか、Oracle7.0とか、8iとかのドキドキ感があるMySQL 8.0.x と遊ぶのもいいのですが、こればかりやってもいられない。Oraclerですし。PostgreSQLもありますしw ネタは色々湧いてきそうではありまする。。;)
では、また。
関連エントリー
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #3 オプティマイザの結合順評価テーブル数上限にも癖が出る
・悩ませ過ぎは及ばざるがごとし #7 - おまけ
yoku0825さんより、MySQL 8.0.xではoptimizerがバッファーブールに乗ってるデータを意識するようになったとの情報が寄せられました。
そこは見逃していた！ とはいえ、以前よりHash Joinの適用範囲がさらに拡大？しているような挙動もあり、色々ドキドキしますね。
とはいえ、マニュアルによれば、HASH_JOINは、 (MySQL 8.0.18 のみ。MySQL 8.0.19 以降では無効です) との記載があり現状制御しにくいですね。索引を使えなくしたら倒れてくれるだろうか。。。別途試してみよう（9/5追記）
https://twitter.com/yoku0825/status/1698140599722110976
| 固定リンク | 0
コメント