[rails] Latest N+1 queries 問題

這篇想更進一步介紹關於 latest N+1 queries 是什麼，可以如何解決？

以下範例，可以自行下載 rails_n_plus_1_queries_problem github repo

首先我們有個 Post / Comment 的 model，彼此是 1-to many 的關係（示意圖與程式碼如下圖）

# app/models/post.rb
class Post < ApplicationRecord
  has_many :comments

  def latest_comment
    comments.order(:created_at).last
  end
end

# app/models/comment.rb

class Comment < ApplicationRecord
  belongs_to :post
end

接下來我們想取得每個 post 的最新 comment，於是我們寫了這樣的程式碼

Post.limit(10).each do |post|
  puts post.latest_comment.body
end

這時候你會看到 rails console 實際上執行這段程式碼的方式是逐一的 SQL query 每個 post 的 comment

因此產生了 N+1 queries (10+1 個 queries)

Post Load (8.9ms)  SELECT `posts`.* FROM `posts` LIMIT 10
Comment Load (13.6ms)  SELECT `comments`.* FROM `comments` WHERE `comments`.`post_id` = 1 ORDER BY `comments`.`created_at` DESC LIMIT 1
Comment Load (1.9ms)  SELECT `comments`.* FROM `comments` WHERE `comments`.`post_id` = 2 ORDER BY `comments`.`created_at` DESC LIMIT 1
Comment Load (3.5ms)  SELECT `comments`.* FROM `comments` WHERE `comments`.`post_id` = 3 ORDER BY `comments`.`created_at` DESC LIMIT 1
Comment Load (0.4ms)  SELECT `comments`.* FROM `comments` WHERE `comments`.`post_id` = 4 ORDER BY `comments`.`created_at` DESC LIMIT 1
Comment Load (0.2ms)  SELECT `comments`.* FROM `comments` WHERE `comments`.`post_id` = 5 ORDER BY `comments`.`created_at` DESC LIMIT 1
Comment Load (0.6ms)  SELECT `comments`.* FROM `comments` WHERE `comments`.`post_id` = 6 ORDER BY `comments`.`created_at` DESC LIMIT 1
Comment Load (0.2ms)  SELECT `comments`.* FROM `comments` WHERE `comments`.`post_id` = 7 ORDER BY `comments`.`created_at` DESC LIMIT 1
Comment Load (0.2ms)  SELECT `comments`.* FROM `comments` WHERE `comments`.`post_id` = 8 ORDER BY `comments`.`created_at` DESC LIMIT 1
Comment Load (0.2ms)  SELECT `comments`.* FROM `comments` WHERE `comments`.`post_id` = 9 ORDER BY `comments`.`created_at` DESC LIMIT 1
Comment Load (1.1ms)  SELECT `comments`.* FROM `comments` WHERE `comments`.`post_id` = 10 ORDER BY `comments`.`created_at` DESC LIMIT 1

於是你嘗試了我們上篇介紹的 includes

Post.limit(10).includes(:comments).each do |post|
  puts post.latest_comment.body
end

Post Load (0.8ms)  SELECT `posts`.* FROM `posts` LIMIT 10
Comment Load (1.0ms)  SELECT `comments`.* FROM `comments` WHERE `comments`.`post_id` IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
Comment Load (1.4ms)  SELECT `comments`.* FROM `comments` WHERE `comments`.`post_id` = 1 ORDER BY `comments`.`created_at` DESC LIMIT 1
Comment Load (0.2ms)  SELECT `comments`.* FROM `comments` WHERE `comments`.`post_id` = 2 ORDER BY `comments`.`created_at` DESC LIMIT 1
Comment Load (0.6ms)  SELECT `comments`.* FROM `comments` WHERE `comments`.`post_id` = 3 ORDER BY `comments`.`created_at` DESC LIMIT 1
Comment Load (0.2ms)  SELECT `comments`.* FROM `comments` WHERE `comments`.`post_id` = 4 ORDER BY `comments`.`created_at` DESC LIMIT 1
Comment Load (5.1ms)  SELECT `comments`.* FROM `comments` WHERE `comments`.`post_id` = 5 ORDER BY `comments`.`created_at` DESC LIMIT 1
Comment Load (0.2ms)  SELECT `comments`.* FROM `comments` WHERE `comments`.`post_id` = 6 ORDER BY `comments`.`created_at` DESC LIMIT 1
Comment Load (0.2ms)  SELECT `comments`.* FROM `comments` WHERE `comments`.`post_id` = 7 ORDER BY `comments`.`created_at` DESC LIMIT 1
Comment Load (0.7ms)  SELECT `comments`.* FROM `comments` WHERE `comments`.`post_id` = 8 ORDER BY `comments`.`created_at` DESC LIMIT 1
Comment Load (0.2ms)  SELECT `comments`.* FROM `comments` WHERE `comments`.`post_id` = 9 ORDER BY `comments`.`created_at` DESC LIMIT 1
Comment Load (0.3ms)  SELECT `comments`.* FROM `comments` WHERE `comments`.`post_id` = 10 ORDER BY `comments`.`created_at` DESC LIMIT 1

接著你會發現還是發生了 N+1 queries，但若仔細點會發現原來的 comments 確實有 includes 的作用

但變成了 N+2 queries 😂

值得注意的是，你會發現在 each 裡面的 post 產生的 SQL 都帶有 order by 的語法，這也就是 includes 沒有辦法正確套用的原因，因為 order 是不一樣的，導致每個 post 仍然需要重新 query

接下來，我們來看看兩種方法調整且修正這個 N+1 queries 問題

將 has_many 加上相同的 default order
為了在 includes comments 也同步載入預期的相同 order comments

# app/models/post.rb
class Post < ApplicationRecord
  has_many :comments, -> { order(created_at: :desc) }
  has_one :latest_comment, -> { order(created_at: :desc) }, class_name: 'Comment'
end

Post Load (1.1ms)  SELECT `posts`.* FROM `posts` LIMIT 10
Comment Load (7.3ms)  SELECT `comments`.* FROM `comments` WHERE `comments`.`post_id` IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10) ORDER BY `comments`.`created_at` DESC

新增 has_one comment 的關聯至 post 上
第一種方法的優化，減少需要載入的 order comments

# app/models/post.rb
class Post < ApplicationRecord
  has_many :comments, -> { order(created_at: :desc) }

  def latest_comment
    comments.last
  end
end

改成 includes(:latest_comment)

Post.limit(10).includes(:latest_comment).each do |post|
  puts post.latest_comment.body
end

Post Load (1.1ms)  SELECT `posts`.* FROM `posts` LIMIT 10
Comment Load (7.3ms)  SELECT `comments`.* FROM `comments` WHERE `comments`.`post_id` IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10) ORDER BY `comments`.`created_at` DESC

如果想要再進一步優化，建議的方向可以往 cache 或是 after_commit 時紀錄最新的 comment id 方向

會是在遇到資料量較大時，更好的優化方法 🙂

參考資料：

https://bhserna.com/5-ways-to-fix-the-latest-comment-n-1-problem