まずは蝋の翼から。

学んだことを書きながら確認・整理するためのメモブログ。こういうことなのかな?といったことをふわっと書いたりしていますが、理解が浅いゆえに的はずれなことも多々あると思うのでツッコミ歓迎

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
        )
    )
;

また、冒頭のSQLsub1 とされている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を見てると以下のツイートが流れてきました。

ツイートのキャプチャ2枚目(1枚目の出典はレバレジーズブログでもBQサイトでもないので不明)にあるように、 少なくともBQのWITH句はその句を参照する度に計算が走るようです。つまり、上述のパフォーマンスとメモリ観点では同じな気がします。Redshitではどうか調べていないのでなんとも言えないのですが、諸々違和感があるのでそのうち調べます。