WITH句かサブクエリか
SQLにおいて、サブクエリは可読性下がるからWITH句を使えという話をしばしば聞く。
ただ、最近あえてサブクエリで記述している人がいたので WITH句とサブクエリで何が違うか について考えてみた。
同じ抽出内容だが片方はWITH句、片方はサブクエリで書いた以下のSQLをベースに話す。
WITH句
WITH sub1 AS ( SELECT aaa ,bbb FROM tbl1 ) ,sub2 AS ( SELECT xxx ,yyy FROM tbl2 ) SELECT sub1.aaa ,sub1.bbb ,sub2.xxx ,sub2.yyy FROM sub1 INNER JOIN sub2 ON sub1.aaa = sub2.xxx ;
サブクエリ
SELECT sub1.aaa ,sub1.bbb ,sub2.xxx ,sub2.yyy FROM (SELECT aaa ,bbb FROM tbl1 ) sub1 INNER JOIN (SELECT xxx ,yyy FROM tbl2 sub2 ) sub2 ON sub1.aaa = sub2.xxx ;
可読性
サブクエリはWITH句と比較すると、可読性が低くなる。
これは上記SQLを見たらわかると思うが、引用するテーブルが入れ子で()内に記載されるので非常に見づらい。これが二重になると更に可読性が低くなる。
SELECT xxx ,yyy FROM (SELECT xxx ,yyy FROM (SELECT xxx ,yyy FROM tbl2 sub2 ) ) ;
また、冒頭のSQLで sub1
とされているWITH句/サブクエリを使った新たな抽出 sub3
を同クエリ内でおこないたいとする。
その場合、WITH句ではそのまま sub1
を引用して記載すればいいが、サブクエリSQLでは同様のSQLを再度書く必要がある。
WITH句
WITH sub1 AS ( SELECT aaa ,bbb FROM tbl1 ) ,sub2 AS ( SELECT xxx ,yyy FROM tbl2 ) SELECT sub1.aaa ,sub1.bbb ,sub2.xxx ,sub2.yyy ,sub3.ccc FROM sub1 INNER JOIN sub2 ON sub1.aaa = sub2.xxx INNER JOIN sub1 AS sub3 -- sub1を再度利用 ON sub2.xxx >= sub3.aaa ;
サブクエリ
SELECT sub1.aaa ,sub1.bbb ,sub2.xxx ,sub2.yyy FROM (SELECT aaa ,bbb FROM tbl1 ) sub1 INNER JOIN (SELECT xxx ,yyy FROM tbl2 ) sub2 ON sub1.aaa = sub2.xxx INNER JOIN (SELECT aaa ,bbb FROM tbl1 ) sub3 -- sub1と同じ ON sub2.xxx >= sub3.aaa ;
この場合、まずsub1とsub3が同じものなのかどうかサブクエリの方ではコメントで判断しないとわかりづらい。また、sub1に修正があったときにsub3も合わせてちゃんと修正しないといけなくなりミスにつながるという再利用性の問題にもつながる。
パフォーマンス
再利用するとき
WITH句で作ったデータ(sub1, sub2...)は一度その部分が作成されるとそのクエリ内セッションではメモリ(尽きたらディスク)に保存される。そのため、先程のsub3のようにsub1を再度使う場合でもメモリからデータを呼び出すだけで再度DBに問い合わせが起きてデータは作成をするコストは発生しない。
一方で、サブクエリの場合はsub3を作成する際は内容はsub1と同じだけれどもメモリなどに保存されているわけではないため、再度作成の必要性が発生する。
つまり、サブクエリでは同じ問い合わせが発生したときにWITH句よりもパフォーマンスが悪くなる(同じ問い合わせがない場合はパフォーマンスは同じ)。
メモリ観点
以下のようなSQLを考える(...は省略部分)。
WITH sub1 AS ( ... ), sub2 AS ( ... ), sub3 AS ( SELECT sub1.* ,sub2.* FROM sub1 INNER JOIN sub2 ON ... ), sub4 AS ( SELECT ... FROM sub3 WHERE ... ), sub5 AS ( ...
このとき、sub1, sub2で作成したデータはsub3で使った以降は使われないにも関わらず一連のクエリが終了するまでメモリに保持されることになる。そのため、メモリを使い過ぎるレベルで大容量のデータを作りすぎる(保持しすぎる)とメモリがどんどん圧迫されてパフォーマンスが落ちる。
一方で、同様の内容をサブクエリで作成する場合は必要になった瞬間に都度データを作成するためWITH句よりも圧迫されない。
まとめ
基本的にWITH句を使えばいいが、どうしても大容量データを作成し続けるようなクエリを作る場合のみパフォーマンスの観点からサブクエリを使った方がいいということになる。
ただし、そのような場合は、メモリに保持されるのはあくまでそのクエリセッション中のみなので分割したCREAT TABLEにするのが普通。前述の例でいえば、sub1, sub2, sub3で1つのCREATE TABLEにして sub4以降で再度別のCREATE TABLEを作成して切り離す。また、この場合はIndexも貼れるためより高速になる。
なお、本論とは別件ではあるがTEMP TABLEを作り過ぎても同様にメモリを圧迫するので適当なところでいらないTEMP TABLEはDROPした方がよい。
つまり、「サブクエリは使わないで基本的にWITH句を使えばよい(メモリやディスクが圧迫されるなら分割する)」という結論になる。
未だにサブクエリを使う人がいるのは、MySQLはWITH句が長らく使えなかった(去年くらいのメジャーアップデートから使えるらしい?MySQLベースのAuroraは未対応。)のでその癖で使い続けている人が多いだけ、というのが現状な気がする。
2022/02/16追記
Twitterを見てると以下のツイートが流れてきました。
レバレジーズさんとこの記事に「withを使用して、サブクエリの使用は避ける」とありますが、確かにサブクエリは避けたほうが(絶対)いいんですが、withよりモダンな書き方として、create temp tableを使うと最適化できますhttps://t.co/eeiNd5I7Zv https://t.co/p6drToy4mS pic.twitter.com/i8rzWD0AdH
— (あんちべ! 俺がS式だ) (@AntiBayesian) 2022年2月16日
ツイートのキャプチャ2枚目(1枚目の出典はレバレジーズブログでもBQサイトでもないので不明)にあるように、 少なくともBQのWITH句はその句を参照する度に計算が走るようです。つまり、上述のパフォーマンスとメモリ観点では同じな気がします。Redshitではどうか調べていないのでなんとも言えないのですが、諸々違和感があるのでそのうち調べます。