先日は久しぶりに面談受けました。相手から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 | する | 場合による |