【Ruby on Rails】N+1問題

N+1問題が発生したので、原因と対応策を備忘としてまとめた。

N+1問題とは🤔

ループ処理の中で都度SQLを発行してしまった結果、大量のSQLが発行されパフォーマンスが低下してしまう問題

ソースコード

# app/controllers/users_controller.rb
# frozen_string_literal: true

class UsersController < ApplicationController
  def index
    @users = User.order(:id).page(params[:page])
  end

  def show
    @user = User.find(params[:id])
  end
end

# app/views/users/index.html.erb
<h1><%= User.model_name.human %></h1>

<table>
  <thead>
    <tr>
      <th><%= User.human_attribute_name(:avatar) %></th>
      <th><%= User.human_attribute_name(:email) %></th>
      <th><%= User.human_attribute_name(:name) %></th>
      <th><%= User.human_attribute_name(:postal_code) %></th>
      <th><%= User.human_attribute_name(:address) %></th>
      <th></th>
    </tr>
  </thead>

  <tbody>
    <% @users.each do |user| %>
      <tr>
        <td><%= image_tag user.avatar.variant(resize:'50x50').processed if user.avatar.attached? %></td>
        <td><%= user.email %></td>
        <td><%= user.name %></td>
        <td><%= user.postal_code %></td>
        <td><%= user.address %></td>
        <td><%= link_to t('views.common.show'), user %></td>
      </tr>
    <% end %>
  </tbody>
</table>

<%= paginate @users %>

N+1発生箇所

今回、ユーザー一覧（アイコンも表示）を表示するページでN+1問題が発生した。

# app/controllers/users_controller.rb
  def index
    @users = User.order(:id).page(params[:page])
  end

# app/views/users/index.html.erb
    <% @users.each do |user| %>
      <tr>
        <td><%= image_tag user.avatar.variant(resize:'50x50').processed if user.avatar.attached? %></td>
        <td><%= user.email %></td>
        <td><%= user.name %></td>
        <td><%= user.postal_code %></td>
        <td><%= user.address %></td>
        <td><%= link_to t('views.common.show'), user %></td>
      </tr>
    <% end %>

# rails.log
Started GET "/users" for ::1 at 2022-05-19 12:53:28 +0900
Processing by UsersController#index as HTML
  User Load (0.2ms)  SELECT "users".* FROM "users" WHERE "users"."id" = ? ORDER BY "users"."id" ASC LIMIT ?  [["id", 51], ["LIMIT", 1]]
  Rendering layout layouts/application.html.erb
  Rendering users/index.html.erb within layouts/application
  User Load (0.3ms)  SELECT "users".* FROM "users" ORDER BY "users"."id" ASC LIMIT ? OFFSET ?  [["LIMIT", 25], ["OFFSET", 0]]
  ↳ app/views/users/index.html.erb:16
  ActiveStorage::Attachment Load (0.1ms)  SELECT "active_storage_attachments".* FROM "active_storage_attachments" WHERE "active_storage_attachments"."record_id" = ? AND "active_storage_attachments"."record_type" = ? AND "active_storage_attachments"."name" = ? LIMIT ?  [["record_id", 1], ["record_type", "User"], ["name", "avatar"], ["LIMIT", 1]]
  ↳ app/views/users/index.html.erb:18
  ActiveStorage::Attachment Load (0.1ms)  SELECT "active_storage_attachments".* FROM "active_storage_attachments" WHERE "active_storage_attachments"."record_id" = ? AND "active_storage_attachments"."record_type" = ? AND "active_storage_attachments"."name" = ? LIMIT ?  [["record_id", 2], ["record_type", "User"], ["name", "avatar"], ["LIMIT", 1]]
  ↳ app/views/users/index.html.erb:18
  ActiveStorage::Attachment Load (0.1ms)  SELECT "active_storage_attachments".* FROM "active_storage_attachments" WHERE "active_storage_attachments"."record_id" = ? AND "active_storage_attachments"."record_type" = ? AND "active_storage_attachments"."name" = ? LIMIT ?  [["record_id", 3], ["record_type", "User"], ["name", "avatar"], ["LIMIT", 1]]
  ↳ app/views/users/index.html.erb:18
  ActiveStorage::Attachment Load (0.1ms)  SELECT "active_storage_attachments".* FROM "active_storage_attachments" WHERE "active_storage_attachments"."record_id" = ? AND "active_storage_attachments"."record_type" = ? AND "active_storage_attachments"."name" = ? LIMIT ?  [["record_id", 4], ["record_type", "User"], ["name", "avatar"], ["LIMIT", 1]]
  ↳ app/views/users/index.html.erb:18
  ActiveStorage::Attachment Load (0.1ms)  SELECT "active_storage_attachments".* FROM "active_storage_attachments" WHERE "active_storage_attachments"."record_id" = ? AND "active_storage_attachments"."record_type" = ? AND "active_storage_attachments"."name" = ? LIMIT ?  [["record_id", 5], ["record_type", "User"], ["name", "avatar"], ["LIMIT", 1]]
  ↳ app/views/users/index.html.erb:18
  ActiveStorage::Attachment Load (0.1ms)  SELECT "active_storage_attachments".* FROM "active_storage_attachments" WHERE "active_storage_attachments"."record_id" = ? AND "active_storage_attachments"."record_type" = ? AND "active_storage_attachments"."name" = ? LIMIT ?  [["record_id", 6], ["record_type", "User"], ["name", "avatar"], ["LIMIT", 1]]
  ↳ app/views/users/index.html.erb:18
  ActiveStorage::Attachment Load (0.1ms)  SELECT "active_storage_attachments".* FROM "active_storage_attachments" WHERE "active_storage_attachments"."record_id" = ? AND "active_storage_attachments"."record_type" = ? AND "active_storage_attachments"."name" = ? LIMIT ?  [["record_id", 7], ["record_type", "User"], ["name", "avatar"], ["LIMIT", 1]]
  ↳ app/views/users/index.html.erb:18
  ActiveStorage::Attachment Load (0.1ms)  SELECT "active_storage_attachments".* FROM "active_storage_attachments" WHERE "active_storage_attachments"."record_id" = ? AND "active_storage_attachments"."record_type" = ? AND "active_storage_attachments"."name" = ? LIMIT ?  [["record_id", 8], ["record_type", "User"], ["name", "avatar"], ["LIMIT", 1]]
  ↳ app/views/users/index.html.erb:18
  ActiveStorage::Attachment Load (0.1ms)  SELECT "active_storage_attachments".* FROM "active_storage_attachments" WHERE "active_storage_attachments"."record_id" = ? AND "active_storage_attachments"."record_type" = ? AND "active_storage_attachments"."name" = ? LIMIT ?  [["record_id", 9], ["record_type", "User"], ["name", "avatar"], ["LIMIT", 1]]
  ↳ app/views/users/index.html.erb:18
  ActiveStorage::Attachment Load (0.1ms)  SELECT "active_storage_attachments".* FROM "active_storage_attachments" WHERE "active_storage_attachments"."record_id" = ? AND "active_storage_attachments"."record_type" = ? AND "active_storage_attachments"."name" = ? LIMIT ?  [["record_id", 10], ["record_type", "User"], ["name", "avatar"], ["LIMIT", 1]]
  ↳ app/views/users/index.html.erb:18
  ActiveStorage::Attachment Load (0.1ms)  SELECT "active_storage_attachments".* FROM "active_storage_attachments" WHERE "active_storage_attachments"."record_id" = ? AND "active_storage_attachments"."record_type" = ? AND "active_storage_attachments"."name" = ? LIMIT ?  [["record_id", 11], ["record_type", "User"], ["name", "avatar"], ["LIMIT", 1]]
  ↳ app/views/users/index.html.erb:18
  ActiveStorage::Attachment Load (0.1ms)  SELECT "active_storage_attachments".* FROM "active_storage_attachments" WHERE "active_storage_attachments"."record_id" = ? AND "active_storage_attachments"."record_type" = ? AND "active_storage_attachments"."name" = ? LIMIT ?  [["record_id", 12], ["record_type", "User"], ["name", "avatar"], ["LIMIT", 1]]
  ↳ app/views/users/index.html.erb:18
  ActiveStorage::Attachment Load (0.1ms)  SELECT "active_storage_attachments".* FROM "active_storage_attachments" WHERE "active_storage_attachments"."record_id" = ? AND "active_storage_attachments"."record_type" = ? AND "active_storage_attachments"."name" = ? LIMIT ?  [["record_id", 13], ["record_type", "User"], ["name", "avatar"], ["LIMIT", 1]]
  ↳ app/views/users/index.html.erb:18
  ActiveStorage::Attachment Load (0.1ms)  SELECT "active_storage_attachments".* FROM "active_storage_attachments" WHERE "active_storage_attachments"."record_id" = ? AND "active_storage_attachments"."record_type" = ? AND "active_storage_attachments"."name" = ? LIMIT ?  [["record_id", 14], ["record_type", "User"], ["name", "avatar"], ["LIMIT", 1]]
  ↳ app/views/users/index.html.erb:18
  ActiveStorage::Attachment Load (0.1ms)  SELECT "active_storage_attachments".* FROM "active_storage_attachments" WHERE "active_storage_attachments"."record_id" = ? AND "active_storage_attachments"."record_type" = ? AND "active_storage_attachments"."name" = ? LIMIT ?  [["record_id", 15], ["record_type", "User"], ["name", "avatar"], ["LIMIT", 1]]
  ↳ app/views/users/index.html.erb:18
  ActiveStorage::Attachment Load (0.1ms)  SELECT "active_storage_attachments".* FROM "active_storage_attachments" WHERE "active_storage_attachments"."record_id" = ? AND "active_storage_attachments"."record_type" = ? AND "active_storage_attachments"."name" = ? LIMIT ?  [["record_id", 16], ["record_type", "User"], ["name", "avatar"], ["LIMIT", 1]]
  ↳ app/views/users/index.html.erb:18
  ActiveStorage::Attachment Load (0.1ms)  SELECT "active_storage_attachments".* FROM "active_storage_attachments" WHERE "active_storage_attachments"."record_id" = ? AND "active_storage_attachments"."record_type" = ? AND "active_storage_attachments"."name" = ? LIMIT ?  [["record_id", 17], ["record_type", "User"], ["name", "avatar"], ["LIMIT", 1]]
  ↳ app/views/users/index.html.erb:18
  ActiveStorage::Attachment Load (0.1ms)  SELECT "active_storage_attachments".* FROM "active_storage_attachments" WHERE "active_storage_attachments"."record_id" = ? AND "active_storage_attachments"."record_type" = ? AND "active_storage_attachments"."name" = ? LIMIT ?  [["record_id", 18], ["record_type", "User"], ["name", "avatar"], ["LIMIT", 1]]
  ↳ app/views/users/index.html.erb:18
  ActiveStorage::Attachment Load (0.1ms)  SELECT "active_storage_attachments".* FROM "active_storage_attachments" WHERE "active_storage_attachments"."record_id" = ? AND "active_storage_attachments"."record_type" = ? AND "active_storage_attachments"."name" = ? LIMIT ?  [["record_id", 19], ["record_type", "User"], ["name", "avatar"], ["LIMIT", 1]]
  ↳ app/views/users/index.html.erb:18
  ActiveStorage::Attachment Load (0.2ms)  SELECT "active_storage_attachments".* FROM "active_storage_attachments" WHERE "active_storage_attachments"."record_id" = ? AND "active_storage_attachments"."record_type" = ? AND "active_storage_attachments"."name" = ? LIMIT ?  [["record_id", 20], ["record_type", "User"], ["name", "avatar"], ["LIMIT", 1]]
  ↳ app/views/users/index.html.erb:18
  ActiveStorage::Attachment Load (0.1ms)  SELECT "active_storage_attachments".* FROM "active_storage_attachments" WHERE "active_storage_attachments"."record_id" = ? AND "active_storage_attachments"."record_type" = ? AND "active_storage_attachments"."name" = ? LIMIT ?  [["record_id", 21], ["record_type", "User"], ["name", "avatar"], ["LIMIT", 1]]
  ↳ app/views/users/index.html.erb:18
  ActiveStorage::Attachment Load (0.1ms)  SELECT "active_storage_attachments".* FROM "active_storage_attachments" WHERE "active_storage_attachments"."record_id" = ? AND "active_storage_attachments"."record_type" = ? AND "active_storage_attachments"."name" = ? LIMIT ?  [["record_id", 22], ["record_type", "User"], ["name", "avatar"], ["LIMIT", 1]]
  ↳ app/views/users/index.html.erb:18
  ActiveStorage::Attachment Load (0.1ms)  SELECT "active_storage_attachments".* FROM "active_storage_attachments" WHERE "active_storage_attachments"."record_id" = ? AND "active_storage_attachments"."record_type" = ? AND "active_storage_attachments"."name" = ? LIMIT ?  [["record_id", 23], ["record_type", "User"], ["name", "avatar"], ["LIMIT", 1]]
  ↳ app/views/users/index.html.erb:18
  ActiveStorage::Attachment Load (0.1ms)  SELECT "active_storage_attachments".* FROM "active_storage_attachments" WHERE "active_storage_attachments"."record_id" = ? AND "active_storage_attachments"."record_type" = ? AND "active_storage_attachments"."name" = ? LIMIT ?  [["record_id", 24], ["record_type", "User"], ["name", "avatar"], ["LIMIT", 1]]
  ↳ app/views/users/index.html.erb:18
  ActiveStorage::Attachment Load (0.1ms)  SELECT "active_storage_attachments".* FROM "active_storage_attachments" WHERE "active_storage_attachments"."record_id" = ? AND "active_storage_attachments"."record_type" = ? AND "active_storage_attachments"."name" = ? LIMIT ?  [["record_id", 25], ["record_type", "User"], ["name", "avatar"], ["LIMIT", 1]]
  ↳ app/views/users/index.html.erb:18
   (0.1ms)  SELECT COUNT(*) FROM "users"
  ↳ app/views/users/index.html.erb:29
  Rendered users/index.html.erb within layouts/application (Duration: 74.7ms | Allocations: 30676)
[Webpacker] Everything's up-to-date. Nothing to do
  Rendered layout layouts/application.html.erb (Duration: 86.1ms | Allocations: 34506)
Completed 200 OK in 92ms (Views: 83.2ms | ActiveRecord: 3.8ms | Allocations: 36494)

上記のログを見るとSQLを下記のタイミングで発行していることがわかる。
1. controller側でのUser.order(:id).page(params[:page])で呼び出し1回
1. view側でのuser毎の呼び出しN回
計26件のSQLが発行されていることが分かる。

解決方法

1. includesを使用する
   事前にデータを全件取得しておけば、<% @users.each do |user| %>の中で都度SQLを発行しなくて済む。

   # app/controllers/users_controller.rb
    def index
      @users= User.all.includes(:avatar_blob).order(:id).page(params[:page])
    end
   

   # rails.log
   Started GET "/users" for ::1 at 2022-05-19 12:54:50 +0900
   (1.0ms)  SELECT sqlite_version(*)
   (0.6ms)  SELECT "schema_migrations"."version" FROM "schema_migrations" ORDER BY "schema_migrations"."version" ASC
   Processing by UsersController#index as HTML
   User Load (0.9ms)  SELECT "users".* FROM "users" WHERE "users"."id" = ? ORDER BY "users"."id" ASC LIMIT ?  [["id", 51], ["LIMIT", 1]]
   Rendering layout layouts/application.html.erb
   Rendering users/index.html.erb within layouts/application
   User Load (1.1ms)  SELECT "users".* FROM "users" ORDER BY "users"."id" ASC LIMIT ? OFFSET ?  [["LIMIT", 25], ["OFFSET", 0]]
   ↳ app/views/users/index.html.erb:16
   ActiveStorage::Attachment Load (0.7ms)  SELECT "active_storage_attachments".* FROM "active_storage_attachments" WHERE "active_storage_attachments"."record_type" = ? AND "active_storage_attachments"."name" = ? AND "active_storage_attachments"."record_id" IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)  [["record_type", "User"], ["name", "avatar"], ["record_id", 1], ["record_id", 2], ["record_id", 3], ["record_id", 4], ["record_id", 5], ["record_id", 6], ["record_id", 7], ["record_id", 8], ["record_id", 9], ["record_id", 10], ["record_id", 11], ["record_id", 12], ["record_id", 13], ["record_id", 14], ["record_id", 15], ["record_id", 16], ["record_id", 17], ["record_id", 18], ["record_id", 19], ["record_id", 20], ["record_id", 21], ["record_id", 22], ["record_id", 23], ["record_id", 24], ["record_id", 25]]
   ↳ app/views/users/index.html.erb:16
   (0.5ms)  SELECT COUNT(*) FROM "users"
   ↳ app/views/users/index.html.erb:29
   Rendered users/index.html.erb within layouts/application (Duration: 443.2ms | Allocations: 304422)
   [Webpacker] Everything's up-to-date. Nothing to do
   Rendered layout layouts/application.html.erb (Duration: 473.5ms | Allocations: 312669)
   Completed 200 OK in 532ms (Views: 477.1ms | ActiveRecord: 5.3ms | Allocations: 325554)
   

2. with_attached_avatarを使用する
   ActiveStorageでN+1問題を解決する際は、上記のメソッドを使用すればOK

   scope :“with_attached#{name}”, -> { includes(“#{name}attachments”: :blob) }

   メソッド内でも同じようにincludesされているみたい。

参考サイト

• qiita.com

• na-ginnan.com