RubyKaigi 2023 参加記 #6 - Fix SQL N+1 queries with RuboCop

日本最大の Ruby に関するカンファレンスである RubyKaigi に Wantedly がスポンサードし、Wantedly のエンジニアも参加しています。また、Wantedly はスポンサーブースを設けていて、アンケートに回答してくださった方には RubyKaigi のために書き下ろした Techbook をプレゼントしているので参加されている方はぜひお立ち寄りください！

この記事では 2日目に行われた sue445 さんの "Fix SQL N+1 queries with RuboCop" について紹介させていただきます。

Fix SQL N+1 queries with RuboCop

発表資料: https://speakerdeck.com/sue445/fix-sql-n-plus-one-queries-with-rubocop

このセッションでは ISUCON での典型的な改善の 1つである N+1 問題検知、オートコレクトする Cop が含まれる Gem rubocop-isucon の紹介と、その Gem を作る過程で得た知見を話してくれました。

前提

今回紹介される gem はISUCON のために作られています。ISUCON の参考実装は ActiveRecord を使うのではなくクエリを文字列で与えるケースが多いため、それを前提とした部分があります。

rubocop-isucon

gem: https://github.com/sue445/rubocop-isucon

この Gem はその名の通り ISUCON に特化した Rubocop で、N+1 クエリの検出以外にも以下のような Cop が紹介されていました。

Isucon/Sinatra/ServeStaticFile
- https://sue445.github.io/rubocop-isucon/RuboCop/Cop/Isucon/Sinatra/ServeStaticFile.html
- 静的ファイルを Sinatra アプリケーションからサーブしている場合に検知され、nginx のようなフロントサーバから配信するように警告されます
Isucon/Mysql2/WhereWithoutIndex
- https://sue445.github.io/rubocop-isucon/RuboCop/Cop/Isucon/Mysql2/WhereWithoutIndex.html
- MySQL でインデックスが張られていないカラムで where をしようとしたときに検知され、インデックスを張るように警告される
- テーブルの情報は ActiveRecord から得ている
Isucon/Mysql2/JoinWithoutIndex
- 上記と同じようにインデックスが張られていないカラムで join をしようとしたときに検知され、インデックスを張るように警告される
Isucon/Mysql2/SelectAsterisk
- `select *` を使っていると検知され、すべてのカラムを明示的に select するようにオートコレクトされます。これによって不必要なカラムを削除する作業が簡単になります。

Isucon/Mysql2/NPlusOneQuery

ここからが本題の N+1 クエリの Cop の話です。

検知

検知は rubocop-performance gem の Performance/CollectionLiteralInLoop Cop のコードが参考になり簡単に実装することができたそうです。

オートコレクト

以下の手順でオートコレクトを可能にしました。

SQL 文字列をパースし SQL AST を取得
SQL AST を Rubocop で使えるようにする
機械的に修正可能な N+1 クエリパターンを検索
誤検出を防ぐ

SQL 文字列をパースし SQL AST を取得

gda gem を使って解決していました。ただしこの gem は SQL の location infomation を返してくれないため、どこで違反があるかを表示することが難しかったようです。

この問題は sue445 さん自身で実装することで解決したようです。その実装は以下にあります。

SQL AST を Rubocop で使えるようにする

基本的には Ruby と SQL の location infomation を追加すればよいが、Ruby には様々な文字列の持ち方があり、それぞれ考慮する必要があります。

今回考慮したパターン以下の 4つでした。

1行の文字列
Heredoc (<<, <<-)
Heredoc (<<~)
行末に “\” を置いて文字列を連結

機械的に修正可能な N+1 クエリパターンの検索

一般的に Cop の検出するだけなら簡単だが、オートコレクトするのは難しいため実装するまでに時間がかかってしまいます。

今回の N+1 クエリの解決では、なんとどのようなSQL N+1クエリがRuboopで自動的に修正できるかを理解するのに2ヶ月、思いついてから実際に実行するまでに1ヶ月を要したようです。

誤検知を防ぐ

安全にオートコレクトをするために以下の場合のみ検出するようにしました。

SELECT ステートメントである
SQL で1つのテーブルしか存在しない。(UNION、JOIN、サブクエリがない)
GROUP BY 句が存在しない
SELECT 句に集計関数（例：COUNT、MAX、MIN、SUM、AVG）が SELECT 句に存在しない
WHERE 句の条件が1つだけで、その条件が現れるカラムがプライマリーキーか単一カラムである
WHERE 句の条件が1つだけで、その条件が現れるカラムが主キーまたは1カラムのユニークキーである

まとめ

意外と N+1 クエリの検出は簡単で、オートコレクトは 10% ほど可能だったようです。Cop からデータベースに接続することで無限の可能性が広がります！

「ISUCON は総合格闘技」

感想

昔 ISUCON に出たときに N+1 を必死に潰す作業をしていた記憶があり、この gem があれば... と聞きながら思いました。ただ発表の中でも言っていた通り「ISUCON は総合格闘技」なのでそれだけではスコアは十分に伸びなかったのですが...
Cop 作成はあまり経験がないので Cop 作成の知見としてもためになる発表でした。 Cop 作成して業務でもイイカンジにスピードアップしたい！