🌀

2022/10/24に公開

# N+1問題とは?

「クエリが必要以上に発行されて、処理が重くなっちゃう」問題。

``````posts = Post.all
``````

``````posts.each do |post|
puts post.user.name
end
``````

postの数だけ、クエリが発行されちゃう

``````User Load (0.1ms)  SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 1], ["LIMIT", 1]]
User Load (0.1ms)  SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 1], ["LIMIT", 1]]
User Load (0.1ms)  SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 1], ["LIMIT", 1]]
User Load (0.1ms)  SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 2], ["LIMIT", 1]]
User Load (0.1ms)  SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 2], ["LIMIT", 1]]
User Load (0.1ms)  SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 2], ["LIMIT", 1]]
User Load (0.1ms)  SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 3], ["LIMIT", 1]]
User Load (0.1ms)  SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 3], ["LIMIT", 1]]
User Load (0.1ms)  SELECT "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 3], ["LIMIT", 1]]
``````

# N+1を回避するための3つのメソッド

### `eager_load`メソッド

``````Post.all.eager_load(:user)
# SELECT "posts"."id" AS t0_r0, "posts"."name" AS t0_r1, "posts"."user_id" AS t0_r2, "posts"."created_at" AS t0_r3, "posts"."updated_at" AS t0_r4, "users"."id" AS t1_r0, "users"."name" AS t1_r1, "users"."created_at" AS t1_r2, "users"."updated_at" AS t1_r3 FROM "posts" LEFT OUTER JOIN "users" ON "users"."id" = "posts"."user_id"
``````

``````Post.eager_load(:user).where(user: { id: 1 })
# SELECT "posts"."id" AS t0_r0, "posts"."name" AS t0_r1, "posts"."user_id" AS t0_r2, "posts"."created_at" AS t0_r3, "posts"."updated_at" AS t0_r4, "user"."id" AS t1_r0, "user"."name" AS t1_r1, "user"."created_at" AS t1_r2, "user"."updated_at" AS t1_r3 FROM "posts" LEFT OUTER JOIN "users" "user" ON "user"."id" = "posts"."user_id" WHERE "user"."id" = ?  [["id", 1]]
``````

### `preload`メソッド

``````Post.all.preload(:user)
# SELECT "posts".* FROM "posts"
# SELECT "users".* FROM "users" WHERE "users"."id" IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)  [["id", 1], ["id", 2], ["id", 3], ["id", 4], ["id", 5], ["id", 6], ["id", 7], ["id", 8], ["id", 9], ["id", 10]]
``````

JOINしていないので、assosiationの値で絞り込むことはできない。

``````User.preload(:posts).where(posts: { id: 1 })
# ERROR
``````

### `includes`メソッド

みたいな感じで、よしなに処理を分けてくれる。

• includesしたテーブルで、whereによる絞り込みを行っている
• includesしたassociationに対して、joinsかreferencesを呼んでいる

のどれかを満たすとき`eager_load`メソッドと同じ挙動、そうでなければ`preload`メソッドと同じ挙動になる。

# `includes`メソッドの注意点

### associationが複数あるとき、個別に最適化できない

``````Post.includes(:user, :tags)
``````

1つでも`eager_load`メソッド(`LEFT OUTER JOIN`)を使うべきassociationがあれば、すべて`LEFT OUTER JOIN`でeager_loadingされる。

# `eager_load`メソッドと`preload`メソッドの速度比較

けど、そういう時は`preload`でも十分速いので、ぶっちゃけ誤差でしかないことも多い。

# どう使い分けるか?

### 使い分け戦略1. 「基本`includes`、ときどきそれ以外」

• 基本的に`includes`メソッドを使う
• associationが複数あって、それぞれ個別に最適化する必要がある場合のみ、`eager_load`メソッドと`preload`メソッドを使う

### 使い分け戦略2. 「`includes`は使わない、`preload`で済むときは`preload`」

• `includes`メソッドは一切使わない
• `preload`メソッドで事足りる場合は、`preload`メソッドを使う
• 無理な場合は`eager_load`メソッドを使う

`preload`より`eager_load`の方が速いケースは、誤差として切り捨て、実装の楽さを優先する。

### 使い分け戦略3. 「`includes`は使わない、`eager_load`の方が速ければ`eager_load`」

• `includes`メソッドは一切使わない
• `preload`で実装でき、かつ`eager_load`よりパフォーマンスが出る場合のみ、`preload`を使う
• それ以外は`eager_load`を使う

(2022/11/11追記)

# オマケ: associationが複数ある場合の書き方のサンプル

``````Post.includes(:user, :tags)
``````

``````Post.includes(user: :country)
``````

``````Post.includes(user: {country: :prefectures})
``````

``````Post.includes(user: [:country, :comments])
``````

``````Post.includes(:tags, user: :country)
``````

※ 単数系or複数形は、紐づくレコードが1つか複数かで変わる。(1つのPostにuserは1人、tagは複数紐づく)
※ 例は`includes`だけど、`eager_load``preload`でも基本同じ書き方ができる。