[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