[Rails] N+1 Queries Problem

什麼是N+1 Query?

N+1 Query 會在當有 parent-child 關係的情況下(one-to-many),載入children(many)時發生。因爲大部分的 ORM 預設使用lazy-loading,一筆 child 資料就會產生一筆 query,拖累了資料庫的效能。

舉例來說,當我們要找出最近的5篇文章並顯示標題和作者時,會使用:

# controller
@recent_articles = Article.order(published_at: :desc).limit(5)
# view
@recent_articles.each do |article|
Title: <%= article.title %>
Author:<%= article.author.name %>
end

此時就會產生6筆query:

Article Load (0.9ms) SELECT 'articles'.* FROM 'articles'
Author Load (0.4ms) SELECT 'authors'.* FROM 'authors' WHERE 'authors'.'id' = ? ORDER BY 'authors'.'id' ASC LIMIT 1 [["id", 1]]
Author Load (0.3ms) SELECT 'authors'.* FROM 'authors' WHERE 'authors'.'id' = ? ORDER BY 'authors'.'id' ASC LIMIT 1 [["id", 2]]
Author Load (0.4ms) SELECT 'authors'.* FROM 'authors' WHERE 'authors'.'id' = ? ORDER BY 'authors'.'id' ASC LIMIT 1 [["id", 3]]
Author Load (0.3ms) SELECT 'authors'.* FROM 'authors' WHERE 'authors'.'id' = ? ORDER BY 'authors'.'id' ASC LIMIT 1 [["id", 4]]
Author Load (0.4ms) SELECT 'authors'.* FROM 'authors' WHERE 'authors'.'id' = ? ORDER BY 'authors'.'id' ASC LIMIT 1 [["id", 5]]

解決方法 — Eager Loading

利用 ActiveRecord的includes方法改寫:

@recent_articles = Article.order(published_at: :desc).includes(:authors).limit(5)

就只會產生2筆query

Article Load (0.4ms) SELECT 'articles'.* FROM 'articles'
Author Load (0.4ms) SELECT 'authors'.* FROM 'authors' WHERE 'authors'.'id' IN (1,2,3,4,5)

偵測 N+1 Queries問題

The Bullet Gem可以在開發時偵測N+1 queries問題,並提供不同的方式提醒(alert / rails logger / growl …)。

安裝:

group :development do
gem 'bullet'
end

設定:

# config/environments/development.rb
config.after_initialize do
# 啓用bullet
Bullet.enable = true
# 使用growl提醒
Bullet.growl = true
end

來源:The (Silver) Bullet for the N+1 Problem

One clap, two clap, three clap, forty?

By clapping more or less, you can signal to us which stories really stand out.