👋

【SQL】WITH句かサブクエリか

2023/04/03に公開

MySQL

with

サブクエリ

tech

今週はちょっと色々あって更新ができず....不甲斐ない。。

概要

社内のPdMがWITH句を使ってデータ抽出していたのを見て、無意識にWITH句を避けていた自分に気づいた。。

可読性はいいけど、、、、自分だったら今までの経験上サブクエリにするだろうな~と。
でもそれってなんで？って言われると、「なんかメモリがどうのこうので...」くらいにしか説明できず上手く言語化できずだったので改めて調べてみた。

WITH句の何がいいの

WITH句の良さはその圧倒的な可読性の良さです。
複雑なクエリを構築しないといけない時、単に自分用だけであれば複雑かつ汚くてもまだ譲歩できます。
しかし、Metabaseなどで自分以外の人も管理する場合は、SQLといえど可読性は大事にしたいところです。

下記の例は、商品の累計売り上げの順位を算出するSQLです。
テーブルの中身が分からなくとも、ある程度なにをしているのかわかってしまうほどに可読性は良いです。

WITH ordersByProducts AS (
  SELECT
    product_id,
    SUM(orders) AS total_orders
  FROM transactions
  GROUP BY product_id
)

SELECT
  name,
  price * total_orders AS sales
FROM ordersByProducts AS o
INNER JOIN products AS p
ON o.product_id = p.id
ORDER BY sales DESC;

WITH句を使って商品ごとの注文数を管理するordersByProductsテーブルを作成

WITH句で定義したordersByProductsテーブルとproductsテーブルをJOINする

ordersByProductsテーブルのtotal_ordersカラムとproductsテーブルのpriceカラムを掛け算して売上を算出

最後にORDER BY句を使って売上が高い順に並び替える

参考：https://z-marketing.net/sql-with/

サブクエリを使った場合

一方サブクエリはどうだろうか。
先の例をサブクエリで書き換えた場合は以下のようになる。
かなり可読性が落ちる。。。

SELECT
  name,
  price * total_orders AS sales
FROM (
  SELECT
    product_id,
    SUM(orders) AS total_orders
  FROM transactions
  GROUP BY product_id
) t
INNER JOIN products AS p
ON t.product_id = p.id
ORDER BY sales DESC;

ではサブクエリはダメなのか？

２つの場合に分けて考える必要があるようだ。

パフォーマンス

WITH句で作ったデータ(sub1, sub2...）は一度その部分が作成されるとそのクエリ内セッションではメモリ（尽きたらディスク）に保存される。そのため、例えばsub1を再度使う場合でもメモリからデータを呼び出すだけで再度DBに問い合わせが起きてデータは作成をするコストは発生しない。
一方で、サブクエリの場合はsub3を作成する際は内容はsub1と同じだけれどもメモリなどに保存されているわけではないため、再度作成の必要性が発生する。
**
つまり、サブクエリでは同じ問い合わせが発生したときにWITH句よりもパフォーマンスが悪くなる（同じ問い合わせがない場合はパフォーマンスは同じ）。**

メモリの観点

先に述べたように、WITH句では作成されたデータはメモリに保持されることになる。そのため、メモリを使い過ぎるレベルで大容量のデータを作りすぎる（保持しすぎる）とメモリがどんどん圧迫されてパフォーマンスが落ちる。
つまり不要なデータがメモリを専有する可能性がある。
一方で、同様の内容をサブクエリで作成する場合は必要になった瞬間に都度データを作成するためWITH句よりも圧迫されない。

参考: https://knknkn.hatenablog.com/entry/2018/03/21/031231

結局どっちがいいの？

レバレジーズさんの規約では、サブクエリを避けて複雑化するのは避けようとある。

ただ、どうしてもWITH句はメモリを専有する可能性があるため注意は必要な気がする。

個人的には、可読性を高めることの方がメリットが高いならWITHを使い、そうでなければサブクエリにしてもいいかなとは思う。

意見をいただきたいです。