はじめに

現在、GoでXクローン作成中のrikutoです。
先日、いいね、リツイート、ブックマーク、を含んだツイート一覧取得のSQLで、サブクエリを使ってしまいました。

今回の記事では、

なぜサブクエリがダメなのか？
サブクエリを使わない場合は、どのようなSQLを書けばいいのか？

という点をまとめようと思います。

同じようなポイントで「おや？」と思っている方の参考になれば幸いです。
もし内容に誤りや、もっと良い書き方があれば、ぜひコメントで教えていただけると嬉しいです！

サブクエリとは？

SELECT 
  id,
  name,
  (SELECT sub_value FROM sub_table)
FROM table;

「1件ごとに何度も聞きに行く」スタイル
メインのクエリが1行実行されるたびに、その都度サブクエリが実行される。

特徴: メインデータがN件あれば、サブクエリもN回実行されるため、データ量が増えると急激に重くなります。
使う場面: 取得件数が極めて少ない場合や、一時的な値の確認など。
イメージ: ビュッフェで「ご飯とってこよう」「次はサラダ...」のように、1品ずつ何度も往復して料理を取りに行くイメージ。

「メインデータ(1)を取得した後に、その数(N)だけサブクエリが働くこの現象はN+1問題と呼ばれ、パフォーマンス劣化の代表的な原因の1つである」

JOINとは？

SELECT 
  t.id, 
  t.content, 
  l.id AS like_id -- いいねのID
FROM tweets AS t
LEFT JOIN likes AS l ON t.id = l.tweet_id;

「あらかじめ大きなテーブルを作って一気に持ってくる」スタイル
複数のテーブルを最初に「1つの大きなテーブル」として連結し、そこから必要なデータをまとめて抽出する。

特徴: データベース内部でテーブル同士を効率よく結合するため、一度のアクセスで大量のデータを高速に集計できる。
使う場面: 「一覧表示」などの、複数のデータをまとめて取得する場合など。
イメージ: 注文した料理が全て載った「完成済みのプレート」を一気に運んでもらうようなイメージ。

問題のSQL (サブクエリ)

※$1,$2はアプリケーション側から渡される引数である。
以下のSQLはいいね、リツイート、ブックマーク、を含んだツイートの一覧を取得するSQLです。

SELECT 
  t.id, 
  t.user_id, 
  t.content, 
  t.created_at,
  
  -- 以下がサブクエリ
  (SELECT COUNT(*) FROM likes l WHERE l.tweet_id = t.id) AS like_count,
  (SELECT COUNT(*) FROM retweets r WHERE r.tweet_id = t.id) AS retweet_count,
  EXISTS (
    SELECT 1 FROM likes l 
    WHERE l.tweet_id = t.id AND l.user_id = $1
  ) AS is_liked,
  EXISTS (
    SELECT 1 FROM retweets r 
    WHERE r.tweet_id = t.id AND r.user_id = $1
  ) AS is_retweeted,
  EXISTS (
    SELECT 1 FROM bookmarks b 
    WHERE b.tweet_id = t.id AND b.user_id = $1
  ) AS is_bookmarked
FROM tweets t
WHERE t.id = $2;

問題のSQLの挙動

このSQLを実行した時、データベース内部では、以下のような「往復」が発生しています。

tweetsテーブルからツイートを1件取り出す。
取り出したツイートに対して、書かれたサブクエリの数だけ別テーブルへ問い合わせを行う。
- likesテーブルへ件数を聞きに行く
- retweetsテーブルへ件数を聞きに行く
- existsで自分の状態を確認しに行く...（以下、サブクエリ分繰り返す）
次のツイートに移り、また1〜2を繰り返す。

取得するツイートが増えれば増えるほど、この「往復回数」が雪だるま式に増えていくのが、このSQLの恐ろしいところです。

また、上記のSQLを「N+1」に当てはめてみると
「1」 はツイート一覧を取得するメインクエリ
「N」 は取得したツイートの件数
になります。

改善したSQL (JOIN)

※$1,$2はアプリケーション側から渡される引数である。
以下のSQLはいいね、リツイート、ブックマーク、を含んだツイートの一覧を取得するSQLです。

SELECT
  t.id,
  t.user_id,
  t.content,
  t.created_at,
  COUNT(DISTINCT l.id) AS like_count,
  MAX(CASE WHEN l.user_id = $1 THEN 1 ELSE 0 END)::boolean AS is_liked,
  COUNT(DISTINCT r.id) AS retweet_count,
  MAX(CASE WHEN r.user_id = $1 THEN 1 ELSE 0 END)::boolean AS is_retweeted,
  MAX(CASE WHEN b.user_id = $1 THEN 1 ELSE 0 END)::boolean AS is_bookmarked
FROM tweets t
LEFT JOIN likes l ON l.tweet_id = t.id
LEFT JOIN retweets r ON r.tweet_id = t.id
LEFT JOIN bookmarks b ON b.tweet_id = t.id
WHERE t.id = $2
GROUP BY t.id;

改善したSQLの挙動

このSQLは、実行された瞬間にデータベース内部で以下のような動きをします。

tweetsテーブルを主軸に、likes,retweets,bookmarksを全て横に連結した 「1つの一時的なテーブル」 を作ります。
連結されたテーブルから、ツイートを取得します。

なぜJOINだと速いのか？(N+1の解消)

データベースへのアクセス回数(クエリ発行数)を劇的に減らせるからです。

サブクエリ版: ツイートが20件あれば、裏側で100回以上の追加クエリが走り、通信のオーバーヘッド(往復のムダ)が積み重なります。
JOIN版: どんなにデータが多くても、発行されるクエリはたったの1回です。

掛け算現象とDISTINCTによる対策

JOINは強力ですが、1つだけ注意点があります。それが 「データの増殖」 です。
1つのツイートに複数の「いいね」や「リツイート」が紐づいている場合、結合すると行数がそれらの掛け算で増えてしまいます。

例: 「いいね3件」×「リツイート2件」= 内部的に6行(6つのツイート)に増殖

これをそのままCOUNTすると、本来「3」であるいいね数が「6」と表示されるバグに繋がります。
そこで、以下の2つの対策をセットで行うのが鉄則です。

GROUP BY t.id: データをツイート単位で1行に集約する。
COUNT(DISTINCT l.id): 「重複を除いたIDの数」だけを数えることで、正しい件数を算出する。

まとめ

「複数データ取得には、サブクエリではなくJOINを使おう」

今回の学びを3行でまとめると

サブクエリは「N+1」の温床: 1行ごとにデータを見に行くため、件数が増えると急激に重くなる。
JOINは「一括処理」: 最初に関連データを結合することで、データベースへのアクセスを最小限(1回)に抑えられる。
DISTINCTとJOINはセット: JOINによるデータの増殖(掛け算現象)は、DISTINCTを使い、重複を排除する。

最後に

最後までお読みいただきありがとうございました。