トップ > 【Rails】N+1問題について

【Rails】N+1問題について

はじめに

　N+1問題とは、ループ処理などでデータベースからデータを取り出す際に、必要以上にSQLが発行され、動作が悪くなってしまう問題のことです。

例えば

例えば、掲示板の一覧を表示したいとします。

下記の設定でboardモデルから全てのレコードを取得します。（BoardはUserモデルと紐づいているとする）

class BoardsController < ApplicationController

  def index
     @boards = Board.all 
  end

end

そして、それを表示します。

<% @boards.each do |board|  %>
     <%= board.title %><br>
     <%= board.body %>
<% end %>

すると、一つずつのレコードを読み出すために大量のSQLが発行されているのがサーバーログを見るとわかります。

Started GET "/boards" for 127.0.0.1 at 2020-11-03 11:52:33 +0900
Processing by BoardsController#index as HTML
  User Load (0.2ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 1], ["LIMIT", 1]]
  ↳ vendor/bundle/ruby/2.6.0/gems/activerecord-5.2.4.4/lib/active_record/log_subscriber.rb:98
  Rendering boards/index.html.erb within layouts/application
  Board Load (0.2ms)  SELECT "boards".* FROM "boards"
  ↳ app/views/boards/index.html.erb:16
  User Load (0.1ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 6], ["LIMIT", 1]]
  ↳ app/views/boards/_board.html.erb:19
  User Load (0.2ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 11], ["LIMIT", 1]]
  ↳ app/views/boards/_board.html.erb:19
  User Load (0.1ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 10], ["LIMIT", 1]]
  ↳ app/views/boards/_board.html.erb:19
  CACHE User Load (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 6], ["LIMIT", 1]]
  ↳ app/views/boards/_board.html.erb:19
  CACHE User Load (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 10], ["LIMIT", 1]]
  ↳ app/views/boards/_board.html.erb:19
  User Load (0.1ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 4], ["LIMIT", 1]]
  ↳ app/views/boards/_board.html.erb:19
  User Load (0.1ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 8], ["LIMIT", 1]]
  ↳ app/views/boards/_board.html.erb:19
  CACHE User Load (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 8], ["LIMIT", 1]]
  ↳ app/views/boards/_board.html.erb:19
  User Load (0.1ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 7], ["LIMIT", 1]]
  ↳ app/views/boards/_board.html.erb:19
  User Load (0.1ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 9], ["LIMIT", 1]]
  ↳ app/views/boards/_board.html.erb:19
  CACHE User Load (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 11], ["LIMIT", 1]]
  ↳ app/views/boards/_board.html.erb:19
  CACHE User Load (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 7], ["LIMIT", 1]]
  ↳ app/views/boards/_board.html.erb:19
  User Load (0.1ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 5], ["LIMIT", 1]]
  ↳ app/views/boards/_board.html.erb:19
  CACHE User Load (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 6], ["LIMIT", 1]]
  ↳ app/views/boards/_board.html.erb:19
  CACHE User Load (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 7], ["LIMIT", 1]]
  ↳ app/views/boards/_board.html.erb:19
  CACHE User Load (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 8], ["LIMIT", 1]]
  ↳ app/views/boards/_board.html.erb:19
  User Load (0.1ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 1], ["LIMIT", 1]]
  ↳ app/views/boards/_board.html.erb:19
  CACHE User Load (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 11], ["LIMIT", 1]]
  ↳ app/views/boards/_board.html.erb:19
  CACHE User Load (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 7], ["LIMIT", 1]]
  ↳ app/views/boards/_board.html.erb:19
  CACHE User Load (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 9], ["LIMIT", 1]]
  ↳ app/views/boards/_board.html.erb:19
  CACHE User Load (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 1], ["LIMIT", 1]]
  ↳ app/views/boards/_board.html.erb:19
  CACHE User Load (0.0ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 1], ["LIMIT", 1]]

これを順番で表すと、

  1. Boardを全件取得
  2. はじめのboardに紐づいているuesrを一件取得
  3. ２番目にboardに紐づいているuesrを一件取得
  4. ３番目にboardに紐づいているuesrを一件取得

boardsテーブルに対して1回　+　usersテーブルに対してレコードの回数（N）SQLが発行されています。したがって、N+１問題と言われています。

解決方法

　boardを全件取得し、それに紐づいているuserを一つずつ呼び出すのではなく、先にboardそれぞれに紐づいているuserを取得しておくことで解決します。

「includes」を使います。これは、boardを全件取得した際に、そのboardに紐づいているuserを取得してくれます。

 @boards = Board.includes(:user)

class BoardsController < ApplicationController

  def index
    @boards = Board.includes(:user)
  end

end

こうすることによって、

Started GET "/boards" for 127.0.0.1 at 2020-11-03 12:15:55 +0900
Processing by BoardsController#index as HTML
  User Load (0.2ms)  SELECT  "users".* FROM "users" WHERE "users"."id" = ? LIMIT ?  [["id", 1], ["LIMIT", 1]]
  ↳ vendor/bundle/ruby/2.6.0/gems/activerecord-5.2.4.4/lib/active_record/log_subscriber.rb:98
  Rendering boards/index.html.erb within layouts/application
  Board Load (0.2ms)  SELECT "boards".* FROM "boards"
  ↳ app/views/boards/index.html.erb:16
  User Load (0.2ms)  SELECT "users".* FROM "users" WHERE "users"."id" IN (?, ?, ?, ?, ?, ?, ?, ?, ?)  [["id", 6], ["id", 11], ["id", 10], ["id", 4], ["id", 8], ["id", 7], ["id", 9], ["id", 5], ["id", 1]]

ここまでコンパクトになります。こうすればパフォーマンスも上がります。

includes以外にも、 joins, preload, eager_loadがあるが、使い分けがまだ整理できていないので、他の記事を載せておきます。

ActiveRecordのjoinsとpreloadとincludesとeager_loadの違い - Qiita