RailsのN+1問題と解決策 #Rails

N+1問題とは

N+1問題とは
SQLクエリを「データ量(N) + 1回」投げてしまい、取得するデータ（N）が多くなるにつれてパフォーマンスが落ちてしまう問題。

Railsの具体例だと

レシピのアクセス数が高い順からレシピを表示するクエリを投げるとします。
テーブルは

レシピアクセステーブル
レシピテーブル
コメント
ユーザー

の４つ

環境

本当はkaminariを使っていますがここではfirst(5)とします。

 @recipes_pagenate =  RecipeAccess.order(access_count: :desc).first(5)

  RecipeAccess Load (1.0ms)  SELECT  `recipe_accesses`.* FROM `recipe_accesses` ORDER BY `recipe_accesses`.`access_count` DESC LIMIT 5 OFFSET 0


<% @recipes_pagenate.each do |recipe_pagenate| %>
  <tr>
    <td><%= link_to recipe_pagenate.recipe.title, recipe_path(recipe_pagenate.recipe.id)  %></td>
    <td><%= link_to recipe_pagenate.recipe.user.name, user_path(recipe_pagenate.recipe.user_id) %></td>
    <td>
      <% recipe_pagenate.recipe.tags.each do |tag| %>
         <p>[<%= tag.name %>]<p>
      <% end %>
    </td>
    <td><%= recipe_pagenate.recipe.cook_time %></td>
    <td><%= recipe_pagenate.recipe.dishes_count %></td>
    <td><%= recipe_pagenate.recipe.description %></td>

    <td>
      <% recipe_pagenate.recipe.comments.each  do |comment|%>
      <p>[<%= comment.content.first(10) %>]</p>
      <% end %>
    </td>
    <td><%= recipe_pagenate.access_count %></td>
  </tr>
  <% end %>

 Recipe Load (0.2ms)  SELECT  `recipes`.* FROM `recipes` WHERE `recipes`.`id` = 122 LIMIT 1
  User Load (0.3ms)  SELECT  `users`.* FROM `users` WHERE `users`.`id` = 122 LIMIT 1
  Tag Load (0.9ms)  SELECT `tags`.* FROM `tags` INNER JOIN `recipes_tags` ON `tags`.`id` = `recipes_tags`.`tag_id` WHERE `recipes_tags`.`recipe_id` = 122
  Comment Load (0.3ms)  SELECT `comments`.* FROM `comments` WHERE `comments`.`recipe_id` = 122
  Recipe Load (0.2ms)  SELECT  `recipes`.* FROM `recipes` WHERE `recipes`.`id` = 75 LIMIT 1
  User Load (0.2ms)  SELECT  `users`.* FROM `users` WHERE `users`.`id` = 75 LIMIT 1
  Tag Load (0.3ms)  SELECT `tags`.* FROM `tags` INNER JOIN `recipes_tags` ON `tags`.`id` = `recipes_tags`.`tag_id` WHERE `recipes_tags`.`recipe_id` = 75
  Comment Load (0.2ms)  SELECT `comments`.* FROM `comments` WHERE `comments`.`recipe_id` = 75
  Recipe Load (0.2ms)  SELECT  `recipes`.* FROM `recipes` WHERE `recipes`.`id` = 80 LIMIT 1
  User Load (0.2ms)  SELECT  `users`.* FROM `users` WHERE `users`.`id` = 80 LIMIT 1
  Tag Load (0.2ms)  SELECT `tags`.* FROM `tags` INNER JOIN `recipes_tags` ON `tags`.`id` = `recipes_tags`.`tag_id` WHERE `recipes_tags`.`recipe_id` = 80
  Comment Load (0.3ms)  SELECT `comments`.* FROM `comments` WHERE `comments`.`recipe_id` = 80
  Recipe Load (0.3ms)  SELECT  `recipes`.* FROM `recipes` WHERE `recipes`.`id` = 58 LIMIT 1
  User Load (0.2ms)  SELECT  `users`.* FROM `users` WHERE `users`.`id` = 58 LIMIT 1
  Tag Load (0.3ms)  SELECT `tags`.* FROM `tags` INNER JOIN `recipes_tags` ON `tags`.`id` = `recipes_tags`.`tag_id` WHERE `recipes_tags`.`recipe_id` = 58
  Comment Load (0.3ms)  SELECT `comments`.* FROM `comments` WHERE `comments`.`recipe_id` = 58
  Recipe Load (0.1ms)  SELECT  `recipes`.* FROM `recipes` WHERE `recipes`.`id` = 33 LIMIT 1
  User Load (0.1ms)  SELECT  `users`.* FROM `users` WHERE `users`.`id` = 33 LIMIT 1
  Tag Load (0.2ms)  SELECT `tags`.* FROM `tags` INNER JOIN `recipes_tags` ON `tags`.`id` = `recipes_tags`.`tag_id` WHERE `recipes_tags`.`recipe_id` = 33
  Comment Load (0.2ms)  SELECT `comments`.* FROM `comments` WHERE `comments`.`recipe_id` = 33

考察

controllerで RecipeAccessのみを読み込み、
viewで Comment,Recipe,User,Tagを読み込んでいます。

今回はfirst(5)となってるので、view側でクエリを5回投げています。
つまりfirst(5)が first(100万)になったら
100万回クエリを投げることになります。
これがN+1問題です。

解決策

RecipeAccess を読み込む際にjoinして他のテーブルの情報も先に読み込むことでクエリの回数を減らすことができ、N+1問題は解決することができます。

Railsのテーブル結合には４種類あります。

joins

ただのjoin
whereで宣言したカラムのみとってくる

今回は使いそうにないので保留

 RecipeAccess.order(access_count: :desc).joins(recipe:[:user, :tags, :comments]).first(5)


 SELECT  `recipe_accesses`.* FROM `recipe_accesses` INNER JOIN `recipes` ON `recipes`.`id` = `recipe_accesses`.`recipe_id` INNER JOIN `users` ON `users`.`id` = `recipes`.`user_id` INNER JOIN `recipes_tags` ON `recipes_tags`.`recipe_id` = `recipes`.`id` INNER JOIN `tags` ON `tags`.`id` = `recipes_tags`.`tag_id` INNER JOIN `comments` ON `comments`.`recipe_id` = `recipes`.`id` ORDER BY `recipe_accesses`.`access_count` DESC LIMIT 5;
+----+--------------+---------------------+---------------------+-----------+
| id | access_count | created_at          | updated_at          | recipe_id |
+----+--------------+---------------------+---------------------+-----------+
| 80 |          971 | 2017-05-23 07:08:30 | 2017-05-23 07:08:30 |        80 |
| 80 |          971 | 2017-05-23 07:08:30 | 2017-05-23 07:08:30 |        80 |
| 80 |          971 | 2017-05-23 07:08:30 | 2017-05-23 07:08:30 |        80 |
| 80 |          971 | 2017-05-23 07:08:30 | 2017-05-23 07:08:30 |        80 |
| 80 |          971 | 2017-05-23 07:08:30 | 2017-05-23 07:08:30 |        80 |
+----+--------------+---------------------+---------------------+-----------+
5 rows in set (0.00 sec)

preload

テーブルごとに取得するクエリを分けて、キャッシュするので

recipe_accesses
recipes
recipes_tags
tags
comments

の順番にクエリを投げている

RecipeAccess.order(access_count: :desc).includes(recipe:[:user, :tags, :comments]).first(5)

SELECT  `recipe_accesses`.* FROM `recipe_accesses` ORDER BY `recipe_accesses`.`access_count` DESC LIMIT 5;
SELECT `recipes`.* FROM `recipes` WHERE `recipes`.`id` IN (122, 75, 80, 58, 33);
SELECT `users`.* FROM `users` WHERE `users`.`id` IN (33, 58, 75, 80, 122);
SELECT `recipes_tags`.* FROM `recipes_tags` WHERE `recipes_tags`.`recipe_id` IN (33, 58, 75, 80, 122);
SELECT `tags`.* FROM `tags` WHERE `tags`.`id` IN (8, 38, 55, 59, 69, 94, 107, 115, 25, 36, 113, 20, 61, 99, 18, 42, 80, 108, 109, 22, 73, 116);
SELECT `comments`.* FROM `comments` WHERE `comments`.`recipe_id` IN (33, 58, 75, 80, 122);

eager_load

eager_loadは LEFT OUTER JOINを行い
sqlのas演算子を用いて、joinされたテーブルとjoinしたテーブルを別のテーブル名に変更してjoinされたテーブルの値をキャッシュするのでsqlの回数がpreloadより少なく済む。

 RecipeAccess.order(access_count: :desc).eager_load(recipe:[:user, :tags, :comments]).first(5)

mysql> SELECT  DISTINCT `recipe_accesses`.`id`, `recipe_accesses`.`access_count` AS alias_0 FROM `recipe_accesses` LEFT OUTER JOIN `recipes` ON `recipes`.`id` = `recipe_accesses`.`recipe_id` LEFT OUTER JOIN `users` ON `users`.`id` = `recipes`.`user_id` LEFT OUTER JOIN `recipes_tags` ON `recipes_tags`.`recipe_id` = `recipes`.`id` LEFT OUTER JOIN `tags` ON `tags`.`id` = `recipes_tags`.`tag_id` LEFT OUTER JOIN `comments` ON `comments`.`recipe_id` = `recipes`.`id` ORDER BY `recipe_accesses`.`access_count` DESC LIMIT 5 \G;                                                                                         *************************** 1. row ***************************
     id: 122
alias_0: 990
*************************** 2. row ***************************
     id: 75
alias_0: 980
*************************** 3. row ***************************
     id: 80
alias_0: 971
*************************** 4. row ***************************
     id: 58
alias_0: 966
*************************** 5. row ***************************
     id: 33
alias_0: 962
5 rows in set (0.00 sec)



mysql> SELECT `recipe_accesses`.`id` AS t0_r0, `recipe_accesses`.`access_count` AS t0_r1, `recipe_accesses`.`created_at` AS t0_r2, `recipe_accesses`.`updated_at` AS t0_r3, `recipe_accesses`.`recipe_id` AS
 t0_r4, `recipes`.`id` AS t1_r0, `recipes`.`title` AS t1_r1, `recipes`.`created_at` AS t1_r2, `recipes`.`updated_at` AS t1_r3, `recipes`.`cook_time` AS t1_r4, `recipes`.`description` AS t1_r5, `recipes`.`
user_id` AS t1_r6, `recipes`.`dishes_count` AS t1_r7, `users`.`id` AS t2_r0, `users`.`encrypted_password` AS t2_r1, `users`.`email` AS t2_r2, `users`.`name` AS t2_r3, `users`.`updated_at` AS t2_r4, `users
`.`created_at` AS t2_r5, `tags`.`id` AS t3_r0, `tags`.`name` AS t3_r1, `tags`.`updated_at` AS t3_r2, `tags`.`created_at` AS t3_r3, `comments`.`id` AS t4_r0, `comments`.`content` AS t4_r1, `comments`.`reci
pe_id` AS t4_r2, `comments`.`updated_at` AS t4_r3, `comments`.`created_at` AS t4_r4, `comments`.`user_id` AS t4_r5 FROM `recipe_accesses` LEFT OUTER JOIN `recipes` ON `recipes`.`id` = `recipe_accesses`.`r
ecipe_id` LEFT OUTER JOIN `users` ON `users`.`id` = `recipes`.`user_id` LEFT OUTER JOIN `recipes_tags` ON `recipes_tags`.`recipe_id` = `recipes`.`id` LEFT OUTER JOIN `tags` ON `tags`.`id` = `recipes_tags`
.`tag_id` LEFT OUTER JOIN `comments` ON `comments`.`recipe_id` = `recipes`.`id` WHERE `recipe_accesses`.`id` IN (122, 75, 80, 58, 33) ORDER BY `recipe_accesses`.`access_count` DESC \G
*************************** 1. row ***************************
t0_r0: 122
t0_r1: 990
t0_r2: 2017-05-23 07:08:38
t0_r3: 2017-05-23 07:08:38
t0_r4: 122
t1_r0: 122
t1_r1: 今日の飯ver58
t1_r2: 2017-05-23 07:08:38
t1_r3: 2017-05-23 07:08:38
t1_r4: 14
t1_r5: たまごを58個割ってにご飯かけるだけ
t1_r6: 122
t1_r7: 7
t2_r0: 122
t2_r1: $2a$11$UkCepJcWK8tPDLtbUh2UieyrPwpWyjrqbKhbbGELfW0OBqU1yWmKq
t2_r2: akbgdyvf@sample.jp
t2_r3: sample59
t2_r4: 2017-05-23 07:08:38
t2_r5: 2017-05-23 07:08:38
t3_r0: 22
t3_r1: えかっ
t3_r2: 2017-05-23 07:07:22
t3_r3: 2017-05-23 07:07:22
t4_r0: NULL
t4_r1: NULL
t4_r2: NULL
t4_r3: NULL
t4_r4: NULL
t4_r5: NULL
*************************** 2. row ***************************
t0_r0: 122
t0_r1: 990
t0_r2: 2017-05-23 07:08:38

~~~長すぎるの省略~~~

includes

eager_loadとpreloadどちらか
最適な方を使用し
whereで条件を設定するときはeager_loadを使用し
limitなどの場合は preload を使用する

  RecipeAccess.order(access_count: :desc).includes(:recipe).first(10)

RecipeAccess Load (0.4ms)  SELECT  `recipe_accesses`.* FROM `recipe_accesses` ORDER BY `recipe_accesses`.`access_count` DESC LIMIT 10
  Recipe Load (0.3ms)  SELECT `recipes`.* FROM `recipes` WHERE `recipes`.`id` IN (122, 75, 80, 58, 33, 116, 12, 151, 64, 61)

参考

http://qiita.com/k0kubun/items/80c5a5494f53bb88dc58
http://qiita.com/merrill/items/de6eb7e899753ad3361a