MySQLのチューニング(JOIN編)

おそらくオプティマイザの挙動が安定しないことが起因で、今まで問題なく動作していたクエリがものすごく遅くなるという事象に遭遇しました。
結果、「ばらし」て組み直すことで解消したのですがその一部始終で技術要素を色々調べられたのでメモしておきます。

やったことの結果要約

・５つあるJOIN句のなかにサブクエリが２つあったので、先行してTemporaryTableへ吐き出すようにした
　→合わせてその時にIndexも貼る
・ベースとして結合したい表をFROMの後へ置き、そのあとは結合しやすい順で並べた。
　→特に意味はないという記事もあったが念のため。元は「ほしい情報」をベースで置いていた。
・結合の条件のなかで、左に今まで出てきている項目、右が対象項目という順とした。
　→オプティマイザに効果があるとの情報
・結果、２分かかっても終わらなかったものが、6秒。これでOKとしました。

実施1)Temporary Tableを使い処理を分割

元々１クエリだったものを、分割して、事前にTemporaryを作成、そのあとにクエリでTemporaryから読むという分割をしました。

【Temporary Table作成の構文】

 CREATE TEMPORARY TABLE temptablename
(INDEX idx_temporary (a,b)) ENGINE=MEMORY
SELECT a,b,c,d,e from table1

今回はSSD環境のDBだったのでMyISAMでも良いのですが、メモリへの展開と指定しました。tmp_table_sizeの設定に依存しますが、今回は許容範囲内なので問題なし。

構文にはフィールド名、型名を指定する方法もありますが、今回はベース表のフィールド名、型をそのまま引き継ぐ形で作ればよく、省略して記載しています。

実施2)ベースの表を変える

対象レコードが少ない表をベース表とし、INNER JOINで結合していきました。
これは今まであまり気にしていませんでしたが、オプティマイザの仕組みを考えると理にかなっている方法だと思います。

実施3)結合の構文の左右を意識する

例文)

select tbl1.a , tbl2.cc from tbl1 inner join tbl2 on tbl1.a = tbl2.a

通常無意識的にメイン表を左、結合対象を右にしていましたが、今回は一部の結合条件で左右が逆でした。

ダメな例文)

select tbl1.a , tbl2.cc from tbl1 inner join tbl2 on tbl2.a = tbl1.a

これもオプティマイザの解釈を考慮すれば当然なのでしょうが、結合したい基本を左に置くことを意識し、全体を調整しました。

→結合表が多くなってきた時に無意識に間違いそうな気がします。

実施4)Temporary Tableにインデックス

これは「ひょっとしたら」で追加したのですが、効果が劇的でした。

Temporary(今回は２つ）はそれぞれUNIQUEなので結合項目としてはすべて使ってます。※先の例で言うとa,b,c,d,eのすべてが結合条件。

それにもかかわらず、aとb(抽出元表:table1のPrimary)にインデックスを貼ったことで

使ってくれて、速度も向上したのです。

知識ある人からしたら「当然だよ」かもしれませんが結果には驚きました。

その他)STRAIGHT_JOIN

結局試していないのですが、[STRAIGHT_JOIN]キーワードを使うと、JOINの順番を強制することができるようです。