iT邦幫忙

2021 iThome 鐵人賽

DAY 20
0
自我挑戰組

初級紅寶石魔法師心得分享。系列 第 20

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

經典老問題。

為何產生N+1?

因為語法沒寫好
N+1是當我們對DB需要查詢一些具有associated data時,會產生的一種狀況。
Rails在關聯性建立好之後,調閱parent-child之間資料非常方便,但當我們在Viewcontroller寫下如下方類似語法時。

#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+對小資料來說還真的還好,但當如果我把controllerlimit去掉,role有上萬上千把sword或是道具時,那少掉的請求就不是3~4條而已了。


預先加載

原理其實就很簡單,當我們自己在view知道會出現有associated data時,就在controller時把associated data預先查好,這樣在view時不需要做這個動作。

N+1衍生的常會被問includespreloadeager_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。

includespreloadeager_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 keyrole的資料先加載。

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都有需要去處理,所以在建立controllerview時就養成良好習慣使用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

收工


上一篇
D-11, Ruby 正規表達式(三) 字符 && Regular Expression Matching
下一篇
D-9. Rails API-Only 實作 && House Robber
系列文
初級紅寶石魔法師心得分享。30

尚未有邦友留言

立即登入留言