D-10.Rails N+1 queries and kill N+1

经典老问题。

为何产生N+1?

因为语法没写好
N+1是当我们对DB需要查询一些具有associated data时，会产生的一种状况。
Rails在关联性建立好之後，调阅parent-child之间资料非常方便，但当我们在View与controller写下如下方类似语法时。

#Model
class Role < ApplicationRecord
  has_many :swords
end

class Sword < ApplicationRecord
  belongs_to :role
end

#Controller
def some_action
  @swords = Sword.order(:created_at).limit(5)
  # or @sword = Sword.all
end

#view: some_action.erb.html
<% @sword.each do |sword| %>
  <%= sword.id%>
  <%= sword.role.name%>
<% end %>

当使用者点进画面，controller要给什麽资料，以及view怎麽展示画面时，在後台所产生的query会如下面这样。

#controller产生的。
2.7.3 :001 > @swords = Sword.order(:created_at).limit(5)
  Sword Load (0.8ms)  SELECT "swords".* FROM "swords" /* loading for inspect */ ORDER BY "swords"."created_at" ASC LIMIT $1  [["LIMIT", 5]]

#view产生的。
2.7.3 :002 > @swords.each do |sword|
2.7.3 :003 >   puts "Sword#{sword.id}"
2.7.3 :004 >   puts "role#{sword.role.name}"
2.7.3 :005 > end
  Sword Load (1.0ms)  SELECT "swords".* FROM "swords" ORDER BY "swords"."created_at" ASC LIMIT $1  [["LIMIT", 5]]
  Role Load (1.1ms)  SELECT "roles".* FROM "roles" WHERE "roles"."id" = $1 LIMIT $2  [["id", 3], ["LIMIT", 1]]
  Role Load (0.9ms)  SELECT "roles".* FROM "roles" WHERE "roles"."id" = $1 LIMIT $2  [["id", 4], ["LIMIT", 1]]
  Role Load (0.4ms)  SELECT "roles".* FROM "roles" WHERE "roles"."id" = $1 LIMIT $2  [["id", 4], ["LIMIT", 1]]
  Role Load (0.3ms)  SELECT "roles".* FROM "roles" WHERE "roles"."id" = $1 LIMIT $2  [["id", 4], ["LIMIT", 1]]
  Role Load (0.3ms)  SELECT "roles".* FROM "roles" WHERE "roles"."id" = $1 LIMIT $2  [["id", 5], ["LIMIT", 1]]

可以很清楚看到，为了达到view的要求，query需要多一条查询，产生了5+1(N+1)的状况。

为何需要注意N+1?

因为钱
对使用者而言，後台动作越多，画面的载入可能会变慢，影响到体验。
对网站而言，查询次数越多，服务器所需发送的请求变多，不只影响到效能，相对所消耗资源越多。

Rails在现在生态常被嫌不够快，於资料量大时更明显。可以开玩笑的说，因为Rails太好用，常常开发者在没有到精通的状态下，就可以快速开发出一个网站，到後面资料量变大时，觉得速度变慢或消耗资源太多，而不想继续使用Rails时，反而导致Rails的评价降低等，但这些使用者可能都忽略了，让网站速度变慢的凶手，常常就是N+1问题。

处理N+1。

如果被问如何处理N+1，最常见的说法就是加上一个includes就好。
的确，就这一招，就解决了大半问题。

#Controller
def some_action
  @swords = Sword.order(:created_at).includes(:role).limit(5)
end

下面是新的後台画面。

#controller
2.7.3 :001 > @swords = Sword.order(:created_at).includes(:role).limit(5)
  Sword Load (0.8ms)  SELECT "swords".* FROM "swords" /* loading for inspect */ ORDER BY "swords"."created_at" ASC LIMIT $1  [["LIMIT", 5]]
  Role Load (1.3ms)  SELECT "roles".* FROM "roles" WHERE "roles"."id" IN ($1, $2, $3)  [["id", 3], ["id", 4], ["id", 5]]

#view
2.7.3 :002 > @swords.each do |sword|
2.7.3 :003 >   puts "Sword#{sword.id}"
2.7.3 :004 >   puts "role#{sword.role.name}"
2.7.3 :005 > end
  Sword Load (1.2ms)  SELECT "swords".* FROM "swords" ORDER BY "swords"."created_at" ASC LIMIT $1  [["LIMIT", 5]]
  Role Load (1.3ms)  SELECT "roles".* FROM "roles" WHERE "roles"."id" IN ($1, $2, $3)  [["id", 3], ["id", 4], ["id", 5]]

我没有故意忽略掉controller因为includes多发出了一个查询请求，但是可以明显看到view只剩两条请求。
N+对小资料来说还真的还好，但当如果我把controller的limit去掉，role有上万上千把sword或是道具时，那少掉的请求就不是3~4条而已了。

预先加载

原理其实就很简单，当我们自己在view知道会出现有associated data时，就在controller时把associated data预先查好，这样在view时不需要做这个动作。

N+1衍生的常会被问includes，preload，eager_load还有join。

joins

对应的是SQL中的INNER JOIN语法。在我们想要对两个具有关联性的table筛选一些资料，或是或是查询某些属性时可以使用。

2.7.3 :001 > Role.joins(:swords)
  Role Load (3.0ms)  SELECT "roles".* FROM "roles" INNER JOIN "swords" ON "swords"."role_id" = "roles"."id" /* loading for inspect */ LIMIT $1  [["LIMIT", 11]]

不过joins用途就像刚刚所说，N+1问题无法处理，这边简单介绍是可以将有关连性的资料连接起来，无法连接的就会nil。

includes，preload，eager_load三者差异。

includes会依你的query判断，何时该使用preload，何时使用eager_load。

includes何时使用preload。

includes大多情况下比较多使用preload，当我们只是要单纯查询Sword有关的Role资料。(单纯查询Model的关联性Model资料。)

用这个例子来看。

2.7.3 :002 > @sword = Sword.all.includes(:role)
  Sword Load (1.0ms)  SELECT "swords".* FROM "swords" /* loading for inspect */ LIMIT $1  [["LIMIT", 11]]
  Role Load (3.2ms)  SELECT "roles".* FROM "roles" WHERE "roles"."id" IN ($1, $2, $3)  [["id", 3], ["id", 4], ["id", 5]]
2.7.3 :003 > @sword = Sword.all.preload(:role)
  Sword Load (1.4ms)  SELECT "swords".* FROM "swords" /* loading for inspect */ LIMIT $1  [["LIMIT", 11]]
  Role Load (1.8ms)  SELECT "roles".* FROM "roles" WHERE "roles"."id" IN ($1, $2, $3)  [["id", 3], ["id", 4], ["id", 5]]

都是建立WHERE``IN语法，利用foreign key把role的资料先加载。

includes何时使用eager_load。

当查询语句明确需要建立关联性资料。

2.7.3 :010 > Sword.all.includes(:role).where('roles.id = 3').references(:role)
  SQL (2.8ms)  SELECT "swords"."id" AS t0_r0, "swords"."created_at" AS t0_r1, "swords"."updated_at" AS t0_r2, "swords"."role_id" AS t0_r3, "swords"."min_damge" AS t0_r4, "swords"."max_damge" AS t0_r5, "roles"."id" AS t1_r0, "roles"."name" AS t1_r1, "roles"."job" AS t1_r2, "roles"."age" AS t1_r3, "roles"."created_at" AS t1_r4, "roles"."updated_at" AS t1_r5, "roles"."user_id" AS t1_r6, "roles"."power" AS t1_r7, "roles"."attack_power" AS t1_r8, "roles"."really_attack_power" AS t1_r9, "roles"."sword_dps" AS t1_r10 FROM "swords" LEFT OUTER JOIN "roles" ON "roles"."id" = "swords"."role_id" WHERE (roles.id = 3) /* loading for inspect */ LIMIT $1  [["LIMIT", 11]]

2.7.3 :002 > Sword.all.eager_load(:role).where('roles.id = 3')
  SQL (5.8ms)  SELECT "swords"."id" AS t0_r0, "swords"."created_at" AS t0_r1, "swords"."updated_at" AS t0_r2, "swords"."role_id" AS t0_r3, "swords"."min_damge" AS t0_r4, "swords"."max_damge" AS t0_r5, "roles"."id" AS t1_r0, "roles"."name" AS t1_r1, "roles"."job" AS t1_r2, "roles"."age" AS t1_r3, "roles"."created_at" AS t1_r4, "roles"."updated_at" AS t1_r5, "roles"."user_id" AS t1_r6, "roles"."power" AS t1_r7, "roles"."attack_power" AS t1_r8, "roles"."really_attack_power" AS t1_r9, "roles"."sword_dps" AS t1_r10 FROM "swords" LEFT OUTER JOIN "roles" ON "roles"."id" = "swords"."role_id" WHERE (roles.id = 3) /* loading for inspect */ LIMIT $1  [["LIMIT", 11]]

eager_load产生LEFT OUTER JOIN语法，并且可以发现所查询资料也将关联性明确标出。
如果这时把preload替换eager_load。

2.7.3 :013 > Sword.all.preload(:role).where('roles.id = 3')
  Sword Load (2.0ms)  SELECT "swords".* FROM "swords" WHERE (roles.id = 3) /* loading for inspect */ LIMIT $1  [["LIMIT", 11]]
#出现警讯
Traceback (most recent call last):
ActiveRecord::StatementInvalid (PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "roles")
LINE 1: SELECT "swords".* FROM "swords" WHERE (roles.id = 3) /* load...

2.7.3 :014 > Sword.all.preload(:role).where('roles.id = 3').references(:role)
  Sword Load (1.9ms)  SELECT "swords".* FROM "swords" WHERE (roles.id = 3) /* loading for inspect */ LIMIT $1  [["LIMIT", 11]]
#出现警讯
Traceback (most recent call last):
ActiveRecord::StatementInvalid (PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "roles")
LINE 1: SELECT "swords".* FROM "swords" WHERE (roles.id = 3) /* load...

就只有GG了。

所以都用includes就好吗?

用includes就一定比较快吗?

有includes对资料，就会解决N+1。
includes在产生eager_load时大多数比产生preload时慢，但includes就是为了要解决N+1这个状况，并且includes可以自动帮开发者选择适合的方法来使用，所以养成使用includes对开发者而言绝对是一件好事。

使用bullet来提醒自己有N+1。

每次都想到让子弹飞一会儿。
Github首页:https://github.com/flyerhzm/bullet

安装小指南

group :development do
  gem 'bullet', '~> 6.1', '>= 6.1.5'
end

$ bundle exec rails g bullet:install

设定小指南。
在config/environments/development.rb，至少将这四个选项加入，在开发服务器上如有N+1的状况就会在画面下有提示，浏览器的开发者工具console里也会跳出建议解决方式。

Bullet.enable = true
Bullet.bullet_logger = true
Bullet.console = true
Bullet.add_footer = true

还有可以协助储存N+1纪录的一些选项，就不多做介绍。

当然这只是提醒工具，是提醒用并不会解决你的N+1。

当专案越做越大，资料越来越多时，再回头查找N+1会非常辛苦，而也不一定所有的N+1都有需要去处理，所以在建立controller与view时就养成良好习惯使用includes，会对开发上有所帮助

今天的leetcode292. Nim Game
题目连结:https://leetcode.com/problems/nim-game/
题目重点:不要赌博，都是骗局。

# @param {Integer} n
# @return {Boolean}
def can_win_nim(n)
    
end

其实是个很简单的逻辑题，不需要去思考石头很多时怎麽拿。
题目有说到两个玩家都是高手，只需要考虑到最後一个人取石头时，剩1, 2, 3颗都是win。剩4颗时lose。

  n % 4 != 0

收工