【Rails】eager_load, preloadの解説と、それに伴うスロークエリの改善【SQL Server】

概要

業務でRailsにてAPI開発をしているのですが、そこでとあるエンドポイントがタイムアウトになり始め、チームで問題となったのでその調査を行ったときの備忘録的な記事になります。

主な話題としてはActiveRecordのeager_loadとpreloadの解説と、スロークエリとなった部分をpreloadにて改善した記事になります。

また、実行環境は以下になります
注意： DBとしてSQLServer, アダプタでactiverecord-sqlserver-adapterを使っているので、違う環境だと結果が変わる可能性があります

• ruby 2.7.2p137
• Rails 6.0.4.4
• activerecord-sqlserver-adapter 6.0.0
• activerecord 6.0.0

eager_load, preload, includesの復習

eager_loadとは

eager_loadは、LEFT_OUTER_JOINで指定したデータを結合し、関連テーブルのデータ配列を取得してキャッシュする。
引数として渡した関連先の要素で絞り込みが可能。

INNER_JOINしたい場合はjoins + eager_loadで可能

preloadとは

preloadは、指定した関連テーブル毎に別クエリを作成、関連テーブルのデータ配列を取得し、キャッシュする。
eager_loadと違い、関連先の要素で絞り込みを行なった場合は例外を投げる。

includesとは

デフォルトではpreloadと同じ挙動、関連先のテーブルの要素で絞り込みを行った場合などはeager_loadと同じ挙動。

それぞれのメリット

preload

• has_many関連を持つデータの事前読み込みを行う場合
• 複数の関連先の事前読み込みを行う場合
  • eager_loadでは常に結合処理を行うので、データ量が大きい場合はスロークエリになる可能性あり。
  • この場合は分割してSELECTするpreloadのほうが早くなる可能性がある。

eager_load

• 関連先の要素で絞り込みを行いたい場合
• has_one, belongs_to関連など1クエリでデータを取得した方が効率が良いと考えられる場合
  • このような場合、外部結合しても取得するレコード数に変わりは無く、1クエリでデータが取得出来るためpreloadより効率が良い可能性がある。

「preloadとeager_loadのレスポンスタイムの違い」をわかりやすく検証している記事があったので貼ります。
https://qiita.com/ryosuketter/items/097556841ec8e1b2940f#%E6%A4%9C%E8%A8%BCpreload%E3%81%A8eager_load%E3%81%AE%E3%83%AC%E3%82%B9%E3%83%9D%E3%83%B3%E3%82%B9%E3%82%BF%E3%82%A4%E3%83%A0%E3%81%AE%E9%81%95%E3%81%84

検証による結論
eager_loadは、1回のSQLでJOINした全データを取得するので、データ量の増加に合わせてレスポンスタイムは長くなる
preloadも、データ量の増加に合わせてレスポンスタイムは長くなるが、eager_loadほどではないとグラフを見てわかる
（preloadはJOINせず、SQLを分割して取得するので）
なので、preloadはeager_loadよりも高速なレスポンスが期待できる
（ただし、本記事の結論で述べた、preloadを使用した方がいい場面、できないことなどの条件に合致していれば）
補足
どの環境でも同等の結果になることは保証しない

タイムアウトになった原因の調査

ここから本題

今回問題となったエンドポイントでは、アイテムの詳細が格納されているitem_detailテーブルとそれに関連するデータをJOINして返す必要がありました。

また、要件として、item_detailテーブルの関連先であるitemテーブルのis_hogeフラグがtrueのもののみ取得したい（要は関連先で絞り込みを行いたい）ため、この機能を実現しようとすると必然的にeager_loadを使うという選択肢になるかなと思います。

実際のコードでも使っていて、以下のような感じになっています。

ItemDetail.joins(:item_detail_categories, :item)
  .eager_load(:item_detail_categories, item: [:hoge, :fuga, :item_category])
  .where(detail_id: detail_ids, flag: true).merge(Item.where(is_hoge: true))

で、調べてみたところ、（具体的な数は避けますが）この二つの関連テーブル(item_detailとitem_detail_category)は相当数レコードがある状態だったので、「じゃあこの二つのテーブルの結合がタイムアウトの直接的な原因か〜」と思ったのですが、実際にこの二つのテーブルを結合してみてもあまり遅くはありませんでした。

改めてeager_loadで吐かれるクエリを見てみると、SELECT句に全てのテーブルのカラムが列挙されているのがわかります。（要はSELECT * FROM ...と同じになる）

以下のような感じ

SELECT
  [ItemDetail].[id] AS t0_r0,
  [ItemDetail].[name] AS t0_r1,
  [ItemDetail].[body] AS t0_r2,
  [ItemDetail].[status] AS t0_r3,
  [ItemDetail].[hoge] AS t0_r4,
  [ItemDetail].[fuga] AS t0_r5,
  ...
  [ItemDetailCategory].[id] AS t1_r0,
  [ItemDetailCategory].[name] AS t1_r1,
  [ItemDetailCategory].[item_detail_id] AS t1_r2,
  [ItemDetailCategory].[hoge] AS t1_r3,
  [ItemDetailCategory].[fuga] AS t1_r4,
  ...
FROM
  [ItemDetail]
  INNER JOIN [ItemDetailCategory] ON [ItemDetailCategory].[item_detail_id] = [ItemDetail].[id]

今回のエンドポイントでは7テーブルを結合していたので、SELECT句に指定されたカラム数は130カラムほどありました。
例えばこれを全件取得する場合は総レコード数*130カラムとなり、取得するフィールド数が大変なことになる可能性があります。
実際には絞り込みがあるのでもっと少ないと思いますが、取得するデータ量が増えたのが原因の可能性もありそうなのがわかりました。
（実際に、SELECT句で指定するカラムを絞ったらスロークエリとならない場合があったが、一概にこれが直接的な原因とまでは言えない。複合的な要因でスロークエリになっていそう）

解決

とりあえず、eager_loadではスロークエリとなってしまっていたので、これを改善します。

修正前のコード(再掲)

ItemDetail.joins(:item_detail_categories, :item)
  .eager_load(:item_detail_categories, item: [:hoge, :fuga, :item_category])
  .where(detail_id: detail_ids, flag: true).merge(Item.where(is_hoge: true))

これを以下のように修正します。

修正後のコード

ItemDetail.where(detail_id: detail_ids, flag: true)
  .joins(:item_detail_categories, :item)
  .merge(Item.where(is_hoge: true))
  .distinct
  .preload(:item_detail_categories, item: [:hoge, :fuga, :item_category])

• まず絞り込みを行いたい関連先のテーブルのみを結合し、絞り込む
• その後、キャッシュしたいテーブルをpreloadに指定する

こうすることで、preloadは絞り込まれた左側のテーブルのidで関連先のテーブルに対してSELECTをかけるので、より小さいテーブル結合とクエリで実現することが可能となります。

また、途中でdistinctを挟んでいますが、これは必要な処理になります。(後述）

なぜDISTNCT？

eager_loadの仕様ではSQLの結果と違い、左側テーブルの各レコードは1件しか含まれません。

言葉ではよくわからないのでコードで。

以下のようなレコードがあったとき...

ItemDetail

id
1
2

ItemDetailCategory

id	item_detail_id
1	1
2	1
3	2
4	2

これを結合すると

SELECT
  [ItemDetail].[id],
  [ItemDetailCategory].[id],
FROM
  [ItemDetail]
  INNER JOIN [ItemDetailCategory] ON [ItemDetailCategory].[item_detail_id] = [ItemDetail].[id]

SQLでは以下のような結果が返ってきます。

ItemDetail.id	ItemDetailCategory.id
1	1
1	2
2	3
2	4

見て分かる通り、左側テーブル(ItemDetail)のIDレコードが4件になります。

モデル上でのeager_loadを用いた結合ではこうはならず、左側テーブルの各レコードは重複せず、それぞれ1件しか含まれません。
感覚としては以下のようになります。（ORM上で日頃使っていると思うので何を今更、という話ではありますが...）

ItemDetail.id	ItemDetailCategory.id
1	1
	2
2	3
	4

これはeager_loadを実行したとき、ActiveRecordがうまいことオブジェクトをマッピング（左側をDISTINCT）してくれる仕様になっているので起こる挙動になっています。
この挙動はeager_load + COUNTを実行すると実際に可視化されます。

ItemDetail.eager_load(:item_detail_categories).count
#=> SELECT COUNT(DISTINCT [ItemDetail].[id]) FROM [ItemDetail] ...

で、今回なぜDISTINCTを入れているのかについてですが、今回のように絞り込みを行うeager_loadから絞り込みを行うjoins + preloadに置き換えを行う場合のみ、この挙動にならず、自分でDISTINCTを挿入する必要があります。

ちゃんとまとめると...

メソッド	DISTINCTされるか	説明
joins	×	SQLと同じ挙動
eager_load	○	ARがうまいことマッピングしてくれる
preload	○	クエリが分かれている(結合しない)のでそもそも重複しない
joins + eager_load	○	eager_loadにjoinsを足す場合は、外部結合から内部結合に切り替わるだけ
joins + preload	×	最初にjoinsにて結合するため、重複が発生する

という挙動になります。