サブクエリで一気に遅くしてしまった件

SQLを書いていて、大きなデータをいままで扱ってこなかったためクエリの速度を考えて書いたことはなかった。

そのため今回ちょっとやらかしています。

SQLが一気に遅くなってしまい、困ってたりします。

サブクエリで遅くなったっぽい

大量に検索データがある際にはWHEREやLIMITをする必要があるが、FROMやJOINをサブクエリで作成した場合に注意です。

大元のSQLにはWHEREを指定しているが、サブクエリの方にWHERE文を指定していなかったため、常にすべてのデータを処理して結合している結果になった。

JOINで指定する際には「ON」で指定すれば絞られたデータで結合される。

WHEREやONはとっても大切なのである。

SQLのパフォーマンス・チューニング

処理が遅いと感じた時にはまず切り分けが必要ではある。

SQLの際には、重いSQLの抽出なども必要になってくる。

時間のかかっているSQLをログとして出力する方法もある。

PostgreSQLでの設定の参考

Linuxトラブルシューティング探偵団（3）：PostgreSQLを遅くしている犯人はどこだ？ (2/3) - ＠IT

lets.postgresql.jp

テーブル結合で注意

テーブルを結合している場合には 結合するテーブルの行数は少ないほうがいい。

EXPLAINでSQLの実行結果の調査は可能である。

EXPLAINにて「rows」の部分が処理している行数になる。この値が数万、数十万とかの数値になっていたら怪しいと思ったほうがいい。

その数値が期待通りならいいが、確実に多いと感じたらWHEREやON句で絞込が必要になる。

EXPLAIN ANALYZE も数値としてみるには重要である。

どれだけ時間がかかっているか確認することができる。こっちのほうが、直感的であるため最近使っている。

どのSQLで時間がかかっているかを理解するためには少し経験が必要だが調査方法がわかるだけでも時間の短縮になる。

IN句よりEXISTS句が早い場合がある

IN句よりEXISTS句がダントツに早いことがある。

IN句を使用していて重い場合にはEXISTS句を使用してみることをおすすめする。

使い分けがわかっていないが、遅いなぁー。と思った時に試している。

早い方を採用する。

SQLは基本だけでいけるが、書き方によるところが大きい

SQLはほとんど基本のSELECT,FROM,WHERE,JOIN,GROUP BY,ORDER BYとかでいいと思っている。

他にCASEやサブクエリがわかるとさらにできることが増える。

だが自己結合とか書き方の仕方でできることがある。

この書き方は、いろんな問題をとくことで経験として身に付けるしかないと思う。

常に書き続けるしかないなぁー。

SQLパズル 第2版 プログラミングが変わる書き方/考え方