Go to Qiita Advent Calendar 2024 Top
LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@uuuDi_97722

N+1問題を解決する

Last updated at Posted at 2024-01-04

1.はじめに

投稿一覧画面にて無駄なクエリが発生していたので解決方法を備考録として記事に残します。

2.環境

Ruby (2.6.4)
Rails (6.1.7)
gem kaminariを導入している

3.N+1とはなにか

ざっくりいうと不必要なSQLを何回も発生させてしまう現象のことです。
それによりDBへの問い合わせ処理が複数回走るためパフォーマンスが悪化します。

例えば、スーパーにりんご、みかん、いちごを買いに行こうとしていたとします。
そのとき、何度も自宅とスーパーと往復せず、まとめて購入するでしょう。

N+1問題は、この例でいくと自宅とスーパーと何度も往復することにあたります。
1往復1食材しか持っていかなかったら効率悪いです。

1往復で買い物を終わらせようというのがN+1問題を解決するということです。

4.おこなったこと

修正前

page(params[:page])について
gem kaminariを導入しているのでこのような記述になっていますが、page(params[:page])　≒　all:　と考えてください。

controller
def index
    @posts = Post.page(params[:page]).reverse_order
end

今回問題となる箇所が色が変わっている部分です。
ループ処理の中でpostに紐づくデータ(favorites,user,post_images,comments)を都度取得しにいってしまっています。

html
    <% @posts.each do |post| %>
      <%= link_to post_path(post) do %>
      <div class="one-post mt-3 card bg-light">
        <p class="caption"><%= post.location %></p>
+       <% post.post_images.first(1).each do |image| %>
        <%= attachment_image_tag image, :image, format: 'jpeg', size: '200x133' %>
        <% end %>
        <p class="caption">
+         <%= attachment_image_tag post.user, :profile_image, format: 'jpeg', fallback: "logo.png", size: '30x30', class: "rounded-circle" %>
+         <%= post.user.name %>
        </p>
        <div class="count">
+         <p class="caption"><%= "♥#{post.favorites.size}" %></p>
+         <p class="caption"><i class="fa-regular fa-comment"></i><%= "#{post.comments.size}" %></p>
        </div>
        <% end %>
      </div>
    <% end %>

実際に発行されているSQLが以下になります。
postの数だけ何度もSQLを呼び出しています。
これがN+1です。

web_1  |   User Load (0.9ms)  SELECT "users".* FROM "users" WHERE "users"."id" = $1 ORDER BY "users"."id" ASC LIMIT $2  [["id", 4], ["LIMIT", 1]]
web_1  |   Rendering layout layouts/application.html.erb
web_1  |   Rendering posts/index.html.erb within layouts/application
web_1  |   Post Load (0.8ms)  SELECT "posts".* FROM "posts" ORDER BY "posts"."id" DESC LIMIT $1 OFFSET $2  [["LIMIT", 8], ["OFFSET", 0]]
web_1  |   ↳ app/views/posts/index.html.erb:7
web_1  |   PostImage Load (0.6ms)  SELECT "post_images".* FROM "post_images" WHERE "post_images"."post_id" = $1 ORDER BY "post_images"."id" ASC LIMIT $2  [["post_id", 25], ["LIMIT", 1]]
web_1  |   ↳ app/views/posts/index.html.erb:11
web_1  |   User Load (0.6ms)  SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2  [["id", 1], ["LIMIT", 1]]
web_1  |   ↳ app/views/posts/index.html.erb:15
web_1  |    (1.2ms)  SELECT COUNT(*) FROM "favorites" WHERE "favorites"."post_id" = $1  [["post_id", 25]]
web_1  |   ↳ app/views/posts/index.html.erb:19
web_1  |    (1.2ms)  SELECT COUNT(*) FROM "comments" WHERE "comments"."post_id" = $1  [["post_id", 25]]
web_1  |   ↳ app/views/posts/index.html.erb:20
web_1  |   PostImage Load (1.0ms)  SELECT "post_images".* FROM "post_images" WHERE "post_images"."post_id" = $1 ORDER BY "post_images"."id" ASC LIMIT $2  [["post_id", 24], ["LIMIT", 1]]
web_1  |   ↳ app/views/posts/index.html.erb:11
web_1  |   CACHE User Load (0.0ms)  SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2  [["id", 1], ["LIMIT", 1]]
web_1  |   ↳ app/views/posts/index.html.erb:15
web_1  |    (1.2ms)  SELECT COUNT(*) FROM "favorites" WHERE "favorites"."post_id" = $1  [["post_id", 24]]
web_1  |   ↳ app/views/posts/index.html.erb:19
web_1  |    (1.0ms)  SELECT COUNT(*) FROM "comments" WHERE "comments"."post_id" = $1  [["post_id", 24]]
web_1  |   ↳ app/views/posts/index.html.erb:20
web_1  |   PostImage Load (1.1ms)  SELECT "post_images".* FROM "post_images" WHERE "post_images"."post_id" = $1 ORDER BY "post_images"."id" ASC LIMIT $2  [["post_id", 23], ["LIMIT", 1]]
web_1  |   ↳ app/views/posts/index.html.erb:11
web_1  |   CACHE User Load (0.0ms)  SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2  [["id", 1], ["LIMIT", 1]]
web_1  |   ↳ app/views/posts/index.html.erb:15
web_1  |    (1.1ms)  SELECT COUNT(*) FROM "favorites" WHERE "favorites"."post_id" = $1  [["post_id", 23]]
web_1  |   ↳ app/views/posts/index.html.erb:19
web_1  |    (0.8ms)  SELECT COUNT(*) FROM "comments" WHERE "comments"."post_id" = $1  [["post_id", 23]]
web_1  |   ↳ app/views/posts/index.html.erb:20
web_1  |   PostImage Load (0.8ms)  SELECT "post_images".* FROM "post_images" WHERE "post_images"."post_id" = $1 ORDER BY "post_images"."id" ASC LIMIT $2  [["post_id", 22], ["LIMIT", 1]]
web_1  |   ↳ app/views/posts/index.html.erb:11
web_1  |   CACHE User Load (0.0ms)  SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2  [["id", 1], ["LIMIT", 1]]
web_1  |   ↳ app/views/posts/index.html.erb:15
web_1  |    (0.9ms)  SELECT COUNT(*) FROM "favorites" WHERE "favorites"."post_id" = $1  [["post_id", 22]]
web_1  |   ↳ app/views/posts/index.html.erb:19
web_1  |    (0.8ms)  SELECT COUNT(*) FROM "comments" WHERE "comments"."post_id" = $1  [["post_id", 22]]
web_1  |   ↳ app/views/posts/index.html.erb:20
web_1  |   PostImage Load (1.6ms)  SELECT "post_images".* FROM "post_images" WHERE "post_images"."post_id" = $1 ORDER BY "post_images"."id" ASC LIMIT $2  [["post_id", 12], ["LIMIT", 1]]
web_1  |   ↳ app/views/posts/index.html.erb:11
web_1  |   CACHE User Load (0.0ms)  SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2  [["id", 1], ["LIMIT", 1]]
web_1  |   ↳ app/views/posts/index.html.erb:15
web_1  |    (1.4ms)  SELECT COUNT(*) FROM "favorites" WHERE "favorites"."post_id" = $1  [["post_id", 12]]
web_1  |   ↳ app/views/posts/index.html.erb:19
web_1  |    (1.1ms)  SELECT COUNT(*) FROM "comments" WHERE "comments"."post_id" = $1  [["post_id", 12]]
web_1  |   ↳ app/views/posts/index.html.erb:20
web_1  |   PostImage Load (0.9ms)  SELECT "post_images".* FROM "post_images" WHERE "post_images"."post_id" = $1 ORDER BY "post_images"."id" ASC LIMIT $2  [["post_id", 10], ["LIMIT", 1]]
web_1  |   ↳ app/views/posts/index.html.erb:11
web_1  |   User Load (1.2ms)  SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2  [["id", 2], ["LIMIT", 1]]
web_1  |   ↳ app/views/posts/index.html.erb:15
web_1  |    (1.3ms)  SELECT COUNT(*) FROM "favorites" WHERE "favorites"."post_id" = $1  [["post_id", 10]]
web_1  |   ↳ app/views/posts/index.html.erb:19
web_1  |    (0.7ms)  SELECT COUNT(*) FROM "comments" WHERE "comments"."post_id" = $1  [["post_id", 10]]
web_1  |   ↳ app/views/posts/index.html.erb:20
web_1  |   PostImage Load (0.5ms)  SELECT "post_images".* FROM "post_images" WHERE "post_images"."post_id" = $1 ORDER BY "post_images"."id" ASC LIMIT $2  [["post_id", 9], ["LIMIT", 1]]
web_1  |   ↳ app/views/posts/index.html.erb:11
web_1  |   CACHE User Load (0.0ms)  SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2  [["id", 2], ["LIMIT", 1]]
web_1  |   ↳ app/views/posts/index.html.erb:15
web_1  |    (0.8ms)  SELECT COUNT(*) FROM "favorites" WHERE "favorites"."post_id" = $1  [["post_id", 9]]
web_1  |   ↳ app/views/posts/index.html.erb:19
web_1  |    (0.8ms)  SELECT COUNT(*) FROM "comments" WHERE "comments"."post_id" = $1  [["post_id", 9]]
web_1  |   ↳ app/views/posts/index.html.erb:20
web_1  |   PostImage Load (0.8ms)  SELECT "post_images".* FROM "post_images" WHERE "post_images"."post_id" = $1 ORDER BY "post_images"."id" ASC LIMIT $2  [["post_id", 8], ["LIMIT", 1]]
web_1  |   ↳ app/views/posts/index.html.erb:11
web_1  |   CACHE User Load (0.0ms)  SELECT "users".* FROM "users" WHERE "users"."id" = $1 LIMIT $2  [["id", 2], ["LIMIT", 1]]
web_1  |   ↳ app/views/posts/index.html.erb:15
web_1  |    (1.0ms)  SELECT COUNT(*) FROM "favorites" WHERE "favorites"."post_id" = $1  [["post_id", 8]]
web_1  |   ↳ app/views/posts/index.html.erb:19
web_1  |    (0.9ms)  SELECT COUNT(*) FROM "comments" WHERE "comments"."post_id" = $1  [["post_id", 8]]
web_1  |   ↳ app/views/posts/index.html.erb:20
web_1  |    (0.9ms)  SELECT COUNT(*) FROM "posts"
web_1  |   ↳ app/views/posts/index.html.erb:26
web_1  |   Rendered posts/index.html.erb within layouts/application (Duration: 1232.2ms | Allocations: 201943)
web_1  | [Webpacker] Everything's up-to-date. Nothing to do
web_1  |   Rendered layout layouts/application.html.erb (Duration: 1298.3ms | Allocations: 208375)
web_1  | Completed 200 OK in 1396ms (Views: 1273.5ms | ActiveRecord: 43.9ms | Allocations: 226381)
web_1  | 
web_1  |

修正後

N+1問題を解決するためにincludesメソッドを採用しました。
「モデルA.include(:モデルB)」のように記述すると、Aに基づくBを取得できます。
今回は、Postに紐付くデータを取得したいので以下のように書きます。
これで、１度に必要なデータを持ってくることができます。

def index
    @posts = Post.page(params[:page]).includes(:favorites, :user, :post_images, :comments).reverse_order
end

実際に発行されるSQLが以下になります。
必要なデータを一度に取得し、発行されるSQLが減り、処理時間も修正前に比べ短くなっています。

web_1  |   User Load (1.5ms)  SELECT "users".* FROM "users" WHERE "users"."id" = $1 ORDER BY "users"."id" ASC LIMIT $2  [["id", 4], ["LIMIT", 1]]
web_1  |   Rendering layout layouts/application.html.erb
web_1  |   Rendering posts/index.html.erb within layouts/application
web_1  |   Post Load (0.6ms)  SELECT "posts".* FROM "posts" ORDER BY "posts"."id" DESC LIMIT $1 OFFSET $2  [["LIMIT", 8], ["OFFSET", 0]]
web_1  |   ↳ app/views/posts/index.html.erb:7
web_1  |   Favorite Load (1.5ms)  SELECT "favorites".* FROM "favorites" WHERE "favorites"."post_id" IN ($1, $2, $3, $4, $5, $6, $7, $8)  [["post_id", 25], ["post_id", 24], ["post_id", 23], ["post_id", 22], ["post_id", 12], ["post_id", 10], ["post_id", 9], ["post_id", 8]]
web_1  |   ↳ app/views/posts/index.html.erb:7
web_1  |   User Load (0.9ms)  SELECT "users".* FROM "users" WHERE "users"."id" IN ($1, $2)  [["id", 1], ["id", 2]]
web_1  |   ↳ app/views/posts/index.html.erb:7
web_1  |   PostImage Load (1.0ms)  SELECT "post_images".* FROM "post_images" WHERE "post_images"."post_id" IN ($1, $2, $3, $4, $5, $6, $7, $8)  [["post_id", 25], ["post_id", 24], ["post_id", 23], ["post_id", 22], ["post_id", 12], ["post_id", 10], ["post_id", 9], ["post_id", 8]]
web_1  |   ↳ app/views/posts/index.html.erb:7
web_1  |   Comment Load (1.1ms)  SELECT "comments".* FROM "comments" WHERE "comments"."post_id" IN ($1, $2, $3, $4, $5, $6, $7, $8)  [["post_id", 25], ["post_id", 24], ["post_id", 23], ["post_id", 22], ["post_id", 12], ["post_id", 10], ["post_id", 9], ["post_id", 8]]
web_1  |   ↳ app/views/posts/index.html.erb:7
web_1  |    (1.0ms)  SELECT COUNT(*) FROM "posts"
web_1  |   ↳ app/views/posts/index.html.erb:26
web_1  |   Rendered posts/index.html.erb within layouts/application (Duration: 173.8ms | Allocations: 30270)
web_1  | [Webpacker] Everything's up-to-date. Nothing to do
web_1  |   Rendered layout layouts/application.html.erb (Duration: 226.2ms | Allocations: 35717)
web_1  | Completed 200 OK in 313ms (Views: 223.4ms | ActiveRecord: 17.3ms | Allocations: 51668)

速度も1396ms→313msとはやくなりました。

修正前
web_1  | Completed 200 OK in 1396ms (Views: 1273.5ms | ActiveRecord: 43.9ms | Allocations: 226381)

修正後
web_1  | Completed 200 OK in 313ms (Views: 223.4ms | ActiveRecord: 17.3ms | Allocations: 51668)

5.おわりに

N＋１解決のために、今回はincludesを採用しましたが状況に応じて対応できるようにならないといけないなと感じました。
なにか他にいい方法があれば教えていただけると幸いです。

今回は使用しておりませんが、N+１問題が発生している箇所を特定できるgemのbulletの導入もおすすめです。

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?