RailsのN+1問題対策includes、preload、eager_loadについて

先日は久しぶりに面談受けました。相手からActiveRecordのN+1問題の対策を聞かれました。
緊張してincludesのみ答えました、その後相手からpreload、eager_loadとかご存知ですかと聞かれました。
緊急して慌てて「忘れました」と答えました。ここでincludes,preload, eager_loadについて改めて調べてまとめてみることにしました。
# N＋１問題
　ActiveRecordのみでけじゃなくて、他のORMも使うなら共同問題です。
# preload、eager_loadとincludes
　簡単に言うと、preloadは指定したアソシエーションを別クエリで取得してキャッシュし、eager_loadは指定したアソシエーションをleft joinで取得しキャッシュします。includesは使い方により変わります。

# preload

```
Order.preload(:cards).where(sex: "male")
発行されるクエリ
SELECT `orders`.* FROM `orders` WHERE `orders`.`sex` = 'male'
SELECT `cards`.* FROM `cards` WHERE `cards`.`oid` IN (1, 2)
```
なので、ordersテーブル以外のテーブルのカラムを絞り込み条件に使用すると、エラーとなります。

# eager load 
 指定したアソシエーションをLEFT OUTER JOINを使って1つのクエリで取得しキャッシュします。

```
Order.eager_load(:cards).where(sex:"male")
発行されるクエリ
SELECT `orders`.`id` AS t0_r0, `orders`.`oid` AS t0_r1, `orders`.`name` AS t0_r2, `orders`.`email` AS t0_r3, `orders`.`phone` AS t0_r4, `orders`.`option` AS t0_r5, `orders`.`order_status` AS t0_r6, `orders`.`order_type` AS t0_r7, `orders`.`start_at` AS t0_r8, `orders`.`end_at` AS t0_r9, `orders`.`birth_year` AS t0_r10, `orders`.`birth_month` AS t0_r11, `orders`.`birth_day` AS t0_r12, `orders`.`sex` AS t0_r13, `orders`.`amount` AS t0_r14, `orders`.`price` AS t0_r15, `orders`.`created_at` AS t0_r16, `orders`.`updated_at` AS t0_r17, `cards`.`id` AS t1_r0, `cards`.`oid` AS t1_r1, `cards`.`card_no` AS t1_r2, `cards`.`card_rank` AS t1_r3, `cards`.`ordered_at` AS t1_r4, `cards`.`linked_at` AS t1_r5, `cards`.`delivered_at` AS t1_r6, `cards`.`used_at` AS t1_r7, `cards`.`card_status` AS t1_r8, `cards`.`created_at` AS t1_r9, `cards`.`updated_at` AS t1_r10 FROM `orders` LEFT OUTER JOIN `cards` ON `cards`.`oid` = `orders`.`id` WHERE `orders`.`sex` = 'male'
```
# includes
```
Order.includes(:cards).where(sex:"male")
発行されてクエリ
SELECT `orders`.* FROM `orders` WHERE `orders`.`sex` = 'male'
SELECT `cards`.* FROM `cards` WHERE `cards`.`oid` IN (1, 2)
# は
Order.preload(:cards).where(sex:"male")
# と同じ挙動
```

```
Order.includes(:cards).where(cards:{card_rank: "gold"})
発行されたクエリ
SELECT `orders`.`id` AS t0_r0, `orders`.`oid` AS t0_r1, `orders`.`name` AS t0_r2, `orders`.`email` AS t0_r3, `orders`.`phone` AS t0_r4, `orders`.`option` AS t0_r5, `orders`.`order_status` AS t0_r6, `orders`.`order_type` AS t0_r7, `orders`.`start_at` AS t0_r8, `orders`.`end_at` AS t0_r9, `orders`.`birth_year` AS t0_r10, `orders`.`birth_month` AS t0_r11, `orders`.`birth_day` AS t0_r12, `orders`.`sex` AS t0_r13, `orders`.`amount` AS t0_r14, `orders`.`price` AS t0_r15, `orders`.`created_at` AS t0_r16, `orders`.`updated_at` AS t0_r17, `cards`.`id` AS t1_r0, `cards`.`oid` AS t1_r1, `cards`.`card_no` AS t1_r2, `cards`.`card_rank` AS t1_r3, `cards`.`ordered_at` AS t1_r4, `cards`.`linked_at` AS t1_r5, `cards`.`delivered_at` AS t1_r6, `cards`.`used_at` AS t1_r7, `cards`.`card_status` AS t1_r8, `cards`.`created_at` AS t1_r9, `cards`.`updated_at` AS t1_r10 FROM `orders` LEFT OUTER JOIN `cards` ON `cards`.`oid` = `orders`.`id` WHERE `cards`.`card_rank` = 'gold'
# は
Order.eager_load(:cards).where('cards.card_rank = "gold"')
# と同じ挙動
Order.includes(:cards).where('cards.card_rank="gold"').references(:cards)
# と同じ挙動
```

最後

```
Order.includes(:cards).where(sex:"male").references(:cards)
# は
Order.eager_load(:cards).where(sex:"male")
# と同じ挙動
```

# まとめ

| メソッド | キャッシュ | クエリ | 
| ---- | ---- | ---- | 
| eager_load | する | 単数 | 
| preload | する | 複数 | 
| includes | する | 場合による | |
# 参考
- https://qiita.com/nagata03/items/b9a7ec6c218445d12c7d
- https://qiita.com/k0kubun/items/80c5a5494f53bb88dc58
- https://moneyforward.com/engineers_blog/2019/04/02/activerecord-includes-preload-eagerload/