經典老問題。
因為語法沒寫好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
。
對應的是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
收工