🔄
複雑なSQLクエリをモブプログラミングでリファクタリングした話

2025/03/28に公開
SQL
こんにちは。株式会社ペライチのイネイブリングチームのスクラムマスター兼バックエンドエンジニアの城戸です。

ペライチは複数の機能開発チームがあり、イネイブリングチームは複数チームにまたがって全体的な共通課題の解決を担っています。
本記事では、イネイブリングチームの活動の一環として障害の分析や振り返りを行う中、発見された複雑な SQL をモブプログラミング形式でリファクタリングした経験を共有します。

※技術的な知見の発信というより、活動レポと思ってもらえると幸いです。

 目次あらすじ：障害対応と SQL リファクタリングの経緯
対象の SQL の紹介と問題点
修正の方針
修正後の SQL の紹介：WITH 句による改善
修正後の SQL の紹介：ウィンドウ関数による改善
各アプローチの比較と考察
まとめ

 1. あらすじ：障害対応とSQLリファクタリングの経緯とある機能で、障害が発生してしまいました。事前にメール送信等の外部通信で問題が発生しうることをリスクとして捉えており、重要度の高いメールに関しては手運用でも再送信ができる状態を作っていました。そのための障害発生時のリカバリ用の資料が準備されていたため、事象の解明と対象の抽出ができる状態でした。

しかし対象を抽出する SQL が巨大かつ複雑で、実際にリカバリ処理を行う際に、その SQL の正確性を確かめるのに難航してしまいました。
後日、イネイブリングチームが主導する障害の振り返り（ポストモーテム）を行った際に、この SQL の複雑さが議題として挙がりました。今後の対策として、対象の SQL をモブプログラミング形式でリファクタリングする会を開催することになりました。複数の目で見ることで、より良い解決策を見つけられると考えたのです。
今回は、このモブプログラミングでの SQL 改善の過程と、学んだことを共有します。

 2. 対象のSQLの紹介と問題点まずは、リファクタリング前の複雑な SQL クエリを見てみましょう。このクエリは、オンラインレッスン予約システムに関連するもので、特定の期間内にレッスンリマインダーログは作成されたが、対応する通知ログがない予約を抽出するためのものです。
注意: 本記事で使用しているテーブル名やカラム名、データ構造はすべてダミーのものであり、実際のシステムとは異なります。内容の理解しやすさを優先して架空の名称に置き換えています。
SELECT*
FROM
    lesson_reminder_logs
WHERE
    booking_id in(
        SELECT
            lb1.booking_id FROM (
                SELECT
                    * FROM lesson_booking
                WHERE
                    plan_id in(
                        SELECT
                            lrl_lb.plan_id FROM (
                                SELECT
                                    lesson_booking.plan_id FROM (
                                        SELECT
                                            * FROM lesson_reminder_logs
                                        WHERE
                                            created_at >= '2024-09-15' AND created_at < '2024-09-16') AS lrl
                                    JOIN lesson_booking ON lesson_booking.booking_id = lrl.booking_id) AS lrl_lb
                                LEFT JOIN (
                                    SELECT
                                        * FROM notification_logs
                                    WHERE
                                        notification_type = 'lesson_reminder'
                                        AND created_at >= '2024-09-15' AND created_at < '2024-09-16') AS nl ON lrl_lb.plan_id = nl.plan_id
                                WHERE
                                    nl.notification_type IS NULL)) AS lb1
                            INNER JOIN (
                                SELECT
                                    booking_id, plan_id, MAX(booking_id) AS max_id FROM lesson_booking
                                WHERE
                                    plan_id in(
                                        SELECT
                                            lrl_lb.plan_id FROM (
                                                SELECT
                                                    lesson_booking.plan_id FROM (
                                                        SELECT
                                                            * FROM lesson_reminder_logs
                                                        WHERE
                                                            created_at >= '2024-09-15' AND created_at < '2024-09-16') AS lrl
                                                    JOIN lesson_booking ON lesson_booking.booking_id = lrl.booking_id) AS lrl_lb
                                                LEFT JOIN (
                                                    SELECT
                                                        * FROM notification_logs
                                                    WHERE
                                                        notification_type = 'lesson_reminder'
                                                        AND  '2024-09-15' AND created_at < '2024-09-16') AS nl ON lrl_lb.plan_id = nl.plan_id
                                                WHERE
                                                    nl.notification_type IS NULL)
                                            GROUP BY
                                                plan_id) AS lb2 ON lb1.booking_id = lb2.max_id);
すごく複雑ですね😇
初見で読み切れる人はなかなかいないんじゃないでしょうか。

 このSQLに関する主要テーブルの説明
lesson_plan: レッスンプランを管理するテーブル。レッスンプランから毎月予約が発生するため、レッスンプラン:予約=1:多の関係

lesson_booking: レッスン予約を管理するテーブル。発生元の plan_id（レッスンプランの ID）を持っている。同一の plan_id をもつ予約の中で一番大きな主キーをもつものが最新の予約

lesson_reminder_logs: レッスンリマインダー送信対象を管理するテーブル。ジョブの処理実績として管理される。対象のレッスンプランの最新の予約に紐づく

notification_logs: レッスンに関する通知全般の送信履歴。今回のケースでは notification_type = 'lesson_reminder'としてレコードが作成される。レッスンプランに紐づき plan_id を保持する
モブプログラミングのセッションでこの SQL を分析した結果、以下の問題点が明らかになりました。

深い入れ子のサブクエリ: 最大で 5 階層のサブクエリがあり、ロジックの流れを追うのが困難

重複するサブクエリ: 同じクエリブロックが複数回出現し、冗長性が高い

テーブルエイリアスの命名が不明確: lrl、lb1、lb2などの短い名前で意味が分かりにくい

クエリの意図が不明確: 何をしようとしているのかが一目で理解できない

メンテナンス性の低さ: 条件の追加や変更が難しく、バグを生みやすい構造

フォーマットの不統一: インデント、改行、キーワードの大文字小文字などに一貫性がない

 3. 修正の方針モブプログラミングでは、以下の方針で SQL のリファクタリングを進めることになりました。

 1. まず、日本語で書いてみる最初のステップとして、「どういったデータを抽出したいのか」を日本語で明確にしました。
弊社CTOも
プログラムは、人々がそれを読むために書かれるべきである。 たまたま、それが計算機で実行できるにすぎない。
と口酸っぱく言っております。（ハッカーと画家で引用されたフレーズを引用している）

最終的に人間が読める SQL を目指すのであれば、まずは日本語で簡潔にまとめてみることが重要です。
特定期間（2024-09-15 から 2024-09-16 まで）に作成された lesson_reminder_logs のレコードを対象とする
それらのレコードに紐づく予約（lesson_booking）を特定する
その予約が属するレッスンプラン（lesson_plan）を特定する
そのレッスンプランに対して、同じ期間に対応する notification_logs が存在しないものを抽出する
各レッスンプランの中で最新の予約だけを対象とする
これで、大分わかりやすくなりましたね。
やみくもに SQL を読んでいくのではなく、この SQL の目的を明確にすることで、それぞれの記述が何をしているのかを理解しやすくなりました。

 2. 冗長な部分や重複するものを取り除く特に以下の点に注目しました。
深い入れ子のサブクエリ：
入れ子になればなるほど可読性が下がるので最低限にしましょうという教訓。
SELECT
    lb1.booking_id FROM (
        SELECT
            * FROM lesson_booking
        WHERE
            plan_id in(
                SELECT
                    lrl_lb.plan_id FROM (
                        -- さらに深いサブクエリが続く...
重複するサブクエリ：
以下のようなほぼ同一のクエリブロックが複数回出現しています。

日付を変更する場合、片方の変更漏れ等も発生しやすいので、一ヵ所で表現できるのであれば、そうするのが望ましいです。
-- 1回目の出現
SELECT
    lrl_lb.plan_id FROM (
        SELECT
            lesson_booking.plan_id FROM (
                SELECT
                    * FROM lesson_reminder_logs
                WHERE
                    created_at >= '2024-09-15' AND created_at < '2024-09-16') AS lrl

-- 2回目の出現（ほぼ同じ内容）
SELECT
    lrl_lb.plan_id FROM (
        SELECT
            lesson_booking.plan_id FROM (
                SELECT
                    * FROM lesson_reminder_logs
                WHERE
                    created_at >= '2024-09-15' AND created_at < '2024-09-16') AS lrl
不明確なテーブルエイリアス：
こちらはいろんな宗派がありますが、個人的にはテーブルエイリアスは意味のある名前をつけるべきだと考えています。
-- lrl, lrl_lb, lb1, lb2などの短縮形は意味が分かりにくい
SELECT * FROM lesson_reminder_logs) AS lrl
JOIN lesson_booking ON lesson_booking.booking_id = lrl.booking_id) AS lrl_lb

 3. 意味がわかりやすいSQLに書き換える具体的な改善手法として 2 つのアプローチを検討しました。
WITH句（共通テーブル式）を使用：何度も使うサブクエリに名前をつけてテーブルとして管理する方法
WITH
    plan_latest_bookings AS (
        SELECT plan_id, MAX(booking_id) AS booking_id
        FROM lesson_booking
        WHERE plan_id IS NOT NULL
        GROUP BY plan_id
    ),
    -- 他のCTEも同様に定義...
ウィンドウ関数を活用：同一レッスンプラン内での最新予約を効率的に特定する方法
SELECT booking_id, plan_id,
   ROW_NUMBER() OVER(PARTITION BY plan_id ORDER BY booking_id desc) AS booking_rank
FROM lesson_booking
WHERE plan_id IS NOT NULL

 4. 修正後のSQLの紹介：WITH句による改善最初のアプローチとして、WITH 句（Common Table Expression = CTE）を使用して複雑なクエリを論理的な単位に分割しました。
WITH
    -- 同じレッスンプランの中で最新の予約を取得したテーブル
    plan_latest_bookings AS
        (
            SELECT plan_id, MAX(booking_id) AS booking_id
            FROM lesson_booking
            WHERE plan_id IS NOT NULL
            GROUP BY plan_id
        ),

    -- 対象期間内のlesson_reminder_logsを取得したテーブル
    target_lesson_reminder_logs AS
        (
            SELECT
                *
            FROM
                lesson_reminder_logs
            WHERE
                created_at >= '2024-09-15'
              AND created_at < '2024-09-16'
        ),

    -- 対象期間内のnotification_logsを取得したテーブル
    target_notification_logs AS
        (
            SELECT
                *
            FROM
                notification_logs
            WHERE
              -- レッスンのリマインダー通知が対象
                notification_type = 'lesson_reminder'
              AND created_at >= '2024-09-15'
              AND created_at < '2024-09-16'
        )

-- lesson_reminder_logsは存在するが、notification_logsが存在しないレコードを取得する
SELECT
    *
FROM
    target_lesson_reminder_logs
    INNER JOIN plan_latest_bookings
       ON  plan_latest_bookings.booking_id = target_lesson_reminder_logs.booking_id
    LEFT JOIN target_notification_logs
      ON plan_latest_bookings.plan_id = target_notification_logs.plan_id
WHERE
    -- 主キーがNULLでjoinができてない（=notification_logsが紐づく存在しない）を確認
    target_notification_logs.id IS NULL
;

 WITH句によるリファクタリングの特徴ちょっと長く見える印象がありますが、WITH 句を知らなくても、仮テーブルを作成しているような雰囲気が理解できます
意味のある単位で分割できるので理解しやすくなっています
各部分に明確な名前とコメントを付けることで、クエリの意図が伝わりやすくなりました
同じクエリブロックを複数回書く必要がなくなり、コードの重複が解消されました
修正や拡張が必要になった場合も、該当する部分だけを変更すれば良いため保守性が向上しました

 5. 修正後のSQLの紹介：ウィンドウ関数による改善もうひとつのアプローチとして、ウィンドウ関数（Window Function）を使用したバージョンも作成しました。
SELECT
    *
FROM
    lesson_reminder_logs
    -- 同一レッスンプランの中で最新の予約をJOINする
    INNER JOIN
        (
            -- レッスンプランの予約を取得し、同一レッスンプラン内で並べてランク付けをする
            SELECT booking_id, plan_id,
               -- plan_id毎にbooking_idの降順にして順番を付与
               ROW_NUMBER() OVER(PARTITION BY plan_id ORDER BY booking_id desc) AS booking_rank
            FROM lesson_booking
            WHERE plan_id IS NOT NULL
        ) as tmp_bookings
    ON tmp_bookings.booking_id = lesson_reminder_logs.booking_id
    -- 順番が1番目のものがbooking_idの最大(同一plan_idの中で最新)となる
    AND tmp_bookings.booking_rank = 1

    LEFT JOIN notification_logs
    -- レッスンのリマインダー通知が対象
    ON notification_logs.notification_type = 'lesson_reminder'
    AND notification_logs.created_at >= '2024-09-15'
    AND notification_logs.created_at < '2024-09-16'
    AND tmp_bookings.plan_id = notification_logs.plan_id
WHERE
    lesson_reminder_logs.created_at >= '2024-09-15'
  AND lesson_reminder_logs.created_at < '2024-09-16'
  -- 主キーがNULLでjoinができてない（=notification_logsが紐づく存在しない）を確認
  AND notification_logs.id IS NULL;

 ウィンドウ関数によるリファクタリングの特徴一見するとコードがスッキリしており、行数も少なくなっています
しかし、サブクエリの読み取りには一癖あり、ウィンドウ関数を知らないとすぐには理解できない可能性があります

ROW_NUMBER() OVER(PARTITION BY... ORDER BY...)という構文を使うことで、同一グループ内でのランク付けを効率的に行っています
条件の配置が直感的で、各テーブルに関連する条件がそれぞれの JOIN 句の近くに配置されています

 6. 各アプローチの比較と考察モブプログラミングでの議論を通じて、両方のアプローチにはそれぞれ長所と短所があることを確認しました。

 WITH句アプローチ長所：
SQL に詳しくない開発者でも理解しやすい
各ステップが明確に分かれており、ロジックが追いやすい
コメントを追加しやすく、ドキュメント化しやすい
短所：
コード量が増える傾向にある
複雑なクエリでは、WITH 句の数が増えて全体像が把握しづらくなることもある

 ウィンドウ関数アプローチ長所：
コードが簡潔になる
適切に使用するとパフォーマンスの向上が期待できる
高度な集計処理が可能
短所：
ウィンドウ関数に馴染みがない開発者には理解が難しい
複雑なロジックを表現すると可読性の低下を招くことがある

 チームでの選択チーム内での議論の結果、以下の基準で使い分けることにしました。

チームの知識レベル：ウィンドウ関数に馴染みのないメンバーが多い場合は WITH 句を優先

クエリの複雑さ：非常に複雑なロジックの場合は WITH 句で段階的に処理

パフォーマンス要件：極めて高いパフォーマンスが必要な場合は、両方のアプローチでテストして判断
今回のケースでは、チーム全体の理解しやすさを重視して、最終的に WITH 句によるアプローチを採用しました。
また、障害対応時に使用する SQL であることを考慮すると、緊急時にも理解しやすいクエリであることが重要だと判断しました。

 7. まとめ複雑な SQL クエリのリファクタリングを通じて、以下のことを学びました。
モブプログラミングの効果：
複数の視点からクエリを分析することで、より良い解決策を見つけることができました。特に、異なる経験やスキルをもつメンバーがいることで、さまざまなアプローチを検討できました。
リファクタリングの手順：
まず日本語で「何をしたいのか」を明確にする
問題のある SQL から冗長な部分や重複を取り除く
意味がわかりやすい構造に書き換える
WITH句の有用性：
複雑なクエリを論理的な単位に分割することで、可読性と保守性が大幅に向上しました。
ウィンドウ関数の可能性：
適切な状況では、ウィンドウ関数を使用することでコードを簡潔にできることがわかりました。
命名の重要性：
意味のある名前を付けることで、クエリの意図が明確になりました。
SQL のリファクタリングは、単にコードを書き換えるだけでなく、チームの知識共有や技術的な議論の機会にもなります。今回のモブプログラミングでは、チームメンバー全員が SQL の理解を深め、より良いコーディング習慣を身につけることができました。
また、障害発生時のリカバリ用 SQL といった重要なクエリほど、平時から可読性を高めておくことの重要性も再認識しました。緊急時にはクエリの理解や修正に時間を割く余裕はありません。
平時からより良い SQL 、コードを書くことで、トラブル時の対応もスムーズに行えるようになることを再認識しました。
ペライチでは、このようなモブプログラミングやペアプログラミングを積極的に行う文化があります。和気あいあいとした雰囲気の中で、エンジニアどうしが知識を共有しながら開発を進めることを大切にしています。複数の目で見ることで、より良いコードが生まれるだけでなく、チームの結束力も高まります。
ペアプロを始め、メンバー同士のコミュニケーションが活発で、困ったときに相談しやすい雰囲気ができあがっており、

とても心理的安全性が高い状態で作業できていると私は思っています。

他のメンバーがペライチの心理的安全性について振り返ってみた記事もありますので、ぜひチェックしてみてください。
余談ではありますが…

実は、このテックブログ記事は Devin（AI）を活用して執筆してみました✌️ みなさん気が付きましたでしょうか？

コードだけではなく、ドキュメント作成にも AI の活用を試みています。（まだまだま模索中です）

AI を活用した開発に興味がある方にもペライチはお勧めです。最新技術を取り入れながら、より効率的で質の高い開発を一緒に目指していきましょう！
もしこのような開発文化に興味を持たれた方は、ぜひペライチのエンジニア採用にご応募ください！

 採用情報現在エンジニア募集しています！
▼ 採用ページ

https://recruit.peraichi.co.jp/
▼ 選考をご希望の方はこちら（募集職種一覧）

https://recruit.peraichi.co.jp/careers/?utm_source=techblog&utm_medium=referral&utm_campaign=article-01j678cedqmwh3gcr0gbq15kmr
▼ まずはカジュアル面談をご希望の方はこちら

https://casual.hp.peraichi.com/?utm_source=techblog&utm_medium=referral&utm_campaign=article-01j678cedqmwh3gcr0gbq15kmr
募集中の職種についてご興味がある方は、お気軽にお申し込みください（CTO がお会いします）
Discussion

ログインするとコメントできます