RailsのN+1問題と解決策


## N+1問題とは

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

### Railsの具体例だと

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

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

の４つ


#### 環境
本当は[kaminari](https://github.com/kaminari/kaminari)を使っていますがここでは`first(5)`とします。

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


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


```erb

<% @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 %>
```

```erb_sql_log
 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で宣言したカラムのみとってくる

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

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

 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


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

1. recipe_accesses
2. recipes
3. recipes_tags
4. tags
5. comments

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



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

```sql
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より少なく済む。


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

```sql
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` を使用する

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


```sql
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