はじめに
現在、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を使い、重複を排除する。
最後に
最後までお読みいただきありがとうございました。