【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`を使うという選択肢になるかなと思います。

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

```ruby
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 ...`と同じになる）

以下のような感じ

```SQL
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`ではスロークエリとなってしまっていたので、これを改善します。

**修正前のコード(再掲)**

```ruby
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))
```

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

**修正後のコード**

```ruby
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  |

これを結合すると

```SQL
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`を実行すると実際に可視化されます。

```ruby
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にて結合するため、重複が発生する|

という挙動になります。