← Writing

Ruby on Rails: Active Record Query (下)

繼續來探究 Active Record 在抓取資料上的方法！ 繼上篇，我們已經學習了如何進行基本的資料查詢。 這篇將深入介紹一些查詢方法以及常見的解決 N+1 問題該如何解決，Let's go! Grouping 要在查詢 SQL 中應用 GROUP BY 子句，可以使用 方法。 SQL 中的 GR...

ruby on railsactive record

繼續來探究 Active Record 在抓取資料上的方法！ 繼上篇，我們已經學習了如何進行基本的資料查詢。 這篇將深入介紹一些查詢方法以及常見的解決 N+1 問題該如何解決，Let's go!

Grouping

要在查詢 SQL 中應用 GROUP BY 子句，可以使用 group 方法。

  • SQL 中的 GROUP BY 子句用於將資料庫中的記錄按照一個或多個欄位的值分組，可以對每個不同組的記錄應用聚合函數，例如計算總和 SUM() 、平均值 AVG() 或計數 COUNT()。
  • GROUP BY 子句通常與聚合函數一起使用，以便對分組的記錄執行統計操作，有助於整理和分析資料，並生成有意義的統計結果。

  • group, count 建立一個 User 模型，每個用戶都有一個 role， 表示他們的角色 (例如：admin, user, editor)，現在想要統計每個角色的用戶數量。 我們便可以使用 group, count 方法！

    User.group(:role).count

    這個查詢將返回一個 Hash，其中每個 key 代表一個不同的角色，而每個 value 代表該角色的用戶數量。將具有相同 role 值的用戶記錄合併在一起，然後計算每個組的記錄數。 生成的 SQL 查詢：SELECT role, COUNT(*) AS count FROM users GROUP BY role

  • Having Conditions

    SQL 使用 HAVING 子句，用於對 GROUP BY 子句分組後的結果集進行篩選， 並返回符合條件的分組結果。

    想在 Active Record 查詢中使用 GROUP BY 和 HAVING 條件結合時， 可以使用 group 和 having 方法！

    建立一個 User 模型，要統計每個角色的用戶數量，但只保留那些擁有超過一定數量的用戶的結果， 可以使用 group 和 having 來達到目的：

    user_counts = User.group(:role).having("COUNT(*) > ?", 3).count

    先使用 group(:role) 按照 role 分組用戶。 然後，使用 having("COUNT(*) > ?", 3) 條件，僅保留那些擁有超過3個用戶的角色。 最後，我們使用 count 方法計算每個符合條件的角色的用戶數量。 生成的 SQL 查詢：

    SELECT role, COUNT(*) AS count
FROM users
GROUP BY role
HAVING COUNT(*) > 3

    SQL 裡，HAVING 子句 與 WHERE 子句 的差別： WHERE 子句 用於對原始數據（未分組）進行篩選，並返回符合條件的個別記錄（在查詢之前應用） HAVING 子句 用於對 GROUP BY 子句分組後的結果集進行篩選，並返回符合條件的分組結果。（在查詢之後應用）

    假設有一個 orders 表， 其中包含了每筆訂單的紀錄，包括訂單金額（order_amount）和客戶 ID（customer_id）。 找出每個客戶的總訂單金額超過 1000 的客戶。

    • 使用 WHERE 子句->

      SELECT customer_id, SUM(order_amount) AS total_amount
FROM orders
GROUP BY customer_id
WHERE total_amount > 1000;

      WHERE 子句直接應用於原始數據，即每筆訂單的記錄。 但這樣的查詢會引發錯誤，因為 total_amount 是在 SELECT 子句中定義的， 而不是在 WHERE 子句中。

    • 改使用 HAVING 子句->

      SELECT customer_id, SUM(order_amount) AS total_amount
FROM orders
GROUP BY customer_id
HAVING SUM(order_amount) > 1000;

      HAVING 子句應用於已分組的結果，即每個客戶的總訂單金額。 我們在 HAVING 子句中使用 SUM(order_amount) 來篩選總訂單金額超過 1000 的客戶。

Eager Loading Associations 預先載入

Eager Loading Associations 是指在一次性查詢中預先載入一個或多個關聯模型的數據，以減少在後續操作中的查詢次數，從而提高應用程式的性能，通常用於解決 N+1 查詢問題。

N+1 查詢問題是什麼？

N+1 查詢問題是在 Active Record 查詢中常見的效能問題之一，通常出現在處理關聯數據的時候。 現在有兩個 Model User 和 Post ，他們之間有一個一對多的關係。

  • 每個用戶可以發佈多篇貼文：

    class User < ApplicationRecord
  has_many :posts
end

class Post < ApplicationRecord
  belongs_to :user
end

  • 列出所有用戶以及每個用戶的貼文。

    # app/controllers/users_controllers.rb
class UsersController < ApplicationController
  def index
    @users = User.all
  end
end

    # app/views/users/index.html.erb
<% @users.each do |user| %>
  <h2><%= user.name %></h2>
  <ul>
    <% user.posts.each do |post| %>
      <li><%= post.title %></li>
    <% end %>
  </ul>
  <hr>
<% end %>

    開始列出每個用戶的每個貼文標題時，每次呼叫 user.posts.each 時都會觸發一次額外的資料庫查詢，以獲取該用戶的貼文標題。當去查看 log 就會看到以下訊息，先找到所有 users，然後開始找每個用戶的每篇貼文，數一下就會發現總共做了 11 次查詢。這就是 N+1 查詢問題 （有 10 個用戶，就會先 1（找出 10 個用戶）+ 10（每個貼文都去找關連的用戶）= 總共 11 次查詢）

    User Load (1.3ms)  SELECT "users".* FROM "users"
  ↳ app/views/users/index.html.erb:1
  Post Load (0.1ms)  SELECT "posts".* FROM "posts" WHERE "posts"."user_id" = ?  [["user_id", 1]]
  ↳ app/views/users/index.html.erb:4
  Post Load (0.1ms)  SELECT "posts".* FROM "posts" WHERE "posts"."user_id" = ?  [["user_id", 2]]
  ↳ app/views/users/index.html.erb:4
  Post Load (0.0ms)  SELECT "posts".* FROM "posts" WHERE "posts"."user_id" = ?  [["user_id", 3]]
  ↳ app/views/users/index.html.erb:4
  Post Load (0.0ms)  SELECT "posts".* FROM "posts" WHERE "posts"."user_id" = ?  [["user_id", 4]]
  ↳ app/views/users/index.html.erb:4
  Post Load (0.0ms)  SELECT "posts".* FROM "posts" WHERE "posts"."user_id" = ?  [["user_id", 5]]
  ↳ app/views/users/index.html.erb:4
  Post Load (0.0ms)  SELECT "posts".* FROM "posts" WHERE "posts"."user_id" = ?  [["user_id", 6]]
  ↳ app/views/users/index.html.erb:4
  Post Load (0.0ms)  SELECT "posts".* FROM "posts" WHERE "posts"."user_id" = ?  [["user_id", 7]]
  ↳ app/views/users/index.html.erb:4
  Post Load (0.0ms)  SELECT "posts".* FROM "posts" WHERE "posts"."user_id" = ?  [["user_id", 8]]
  ↳ app/views/users/index.html.erb:4
  Post Load (0.0ms)  SELECT "posts".* FROM "posts" WHERE "posts"."user_id" = ?  [["user_id", 9]]
  ↳ app/views/users/index.html.erb:4
  Post Load (0.0ms)  SELECT "posts".* FROM "posts" WHERE "posts"."user_id" = ?  [["user_id", 10]]
  ↳ app/views/users/index.html.erb:4

解決 N+1 查詢問題的方法

想要解決這樣的問題就要來應用 Eager Loading Associations，可以使用 includes 方法，讓 Active Record 確保所有指定的關聯，加載的查詢減到最少。除了 includes 方法，以下會說明其他種方式，來避免 N+1 查詢問題。

  • includes （Eager Loading Associations） 將剛剛在 users_controllers.rb 的方法改寫為 @users = User.includes(:posts)

    # app/controllers/users_controllers.rb
class UsersController < ApplicationController
  def index
    @users = User.includes(:posts)
  end
end

    查看 log：

    User Load (0.2ms)  SELECT "users".* FROM "users"
↳ app/views/users/index.html.erb:1
Post Load (0.5ms)  SELECT "posts".* FROM "posts" WHERE "posts"."user_id" IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)  [["user_id", 1], ["user_id", 2], ["user_id", 3], ["user_id", 4], ["user_id", 5], ["user_id", 6], ["user_id", 7], ["user_id", 8], ["user_id", 9], ["user_id", 10]]
↳ app/views/users/index.html.erb:1

    可以發現 SQL 查詢從 posts 表中查詢的方式不同了！ 具體來說，裡面使用了 IN 來指定多個可能的 user_id， 通過 user_id 來查找屬於用戶 1、2、3、4、5、6、7、8、9 和 10 的貼文， 而這些 user_id 是通過問號（?）參數傳遞的，這種參數化的查詢是防止 SQL injection。 透過只發出一個 SQL 查詢而不是 N 個，提高效能，解決 N+1 查詢問題。

  • preload （Eager Loading Associations） 將剛剛在 users_controllers.rb 的方法改寫為 @users = User.preload(:posts)

    # app/controllers/users_controllers.rb
class UsersController < ApplicationController
  def index
    @users = User.preload(:posts)
  end
end

    查看 log：

    User Load (0.9ms)  SELECT "users".* FROM "users"
↳ app/views/users/index.html.erb:1
Post Load (0.5ms)  SELECT "posts".* FROM "posts" WHERE "posts"."user_id" IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)  [["user_id", 1], ["user_id", 2], ["user_id", 3], ["user_id", 4], ["user_id", 5], ["user_id", 6], ["user_id", 7], ["user_id", 8], ["user_id", 9], ["user_id", 10]]
↳ app/views/users/index.html.erb:1

    可以看出 preload 跟 includes 是能達到相同效果的！ 但要注意的是： preload 不能指定條件，無法針對這些關聯數據進行進一步的條件過濾。 includes 可以指定條件，以檢索符合條件的相關數據。

  • eager_load （Eager Loading Associations） 將剛剛在 users_controllers.rb 的方法改寫為 @users = User.eager_load(:posts)

    # app/controllers/users_controllers.rb
class UsersController < ApplicationController
  def index
    @users = User.eager_load(:posts)
  end
end

    查看 log：

    SQL (1.3ms)  SELECT "users"."id" AS t0_r0, "users"."name" AS t0_r1, "users"."created_at" AS t0_r2, "users"."updated_at" AS t0_r3, "posts"."id" AS t1_r0, "posts"."title" AS t1_r1, "posts"."user_id" AS t1_r2, "posts"."created_at" AS t1_r3, "posts"."updated_at" AS t1_r4 FROM "users" LEFT OUTER JOIN "posts" ON "posts"."user_id" = "users"."id"
↳ app/views/users/index.html.erb:1

    使用 eager_load，Active Record 會使用 LEFT OUTER JOIN 來載入所有指定的關聯。

    LEFT OUTER JOIN 是 SQL 中的一種 JOIN 類型，用於結合兩個或多個表中的數據，以便在結果中檢索相關數據。具體來說，LEFT OUTER JOIN 返回左側表中的所有行，並返回右側表中與左側表中的行相匹配的行。如果右側表中沒有與左側表匹配的行，則返回 NULL 值。 假設你有兩個表：Users 和 Posts，並且希望獲得每個用戶的所有貼文，即使有些用戶還沒有。

    SELECT Users.name, Posts.post_number
FROM Users
LEFT OUTER JOIN Posts ON Users.id = Posts.user_id;

    在這個查詢中，Users 表是左側表，Posts 表是右側表。 ON Users.id = Posts.user_id 指定了表之間的關聯條件。結果將返回所有用戶的名稱以及他們的貼文，如果某個用戶沒有，則貼文將顯示為 NULL。

    來比較一下 includes 和 eager_load 差異： includes:

    • includes 使用兩個 SQL 查詢， 一個用於查詢主 Model User，另一個用於查詢關聯 Model Post。 適用於需要在遍歷主 Model 的集合時搜尋關聯數據的情況，這樣他不需要額外的查詢。

    eager_load:

    • eager_load 使用一個 SQL 查詢， 將主 Model 和關聯 Model 的數據一次性載入，並使用 JOIN 連接他們。 適用於需要在遍歷主 Model 的集合之前搜尋關聯數據的情況， 這樣他可以通過 JOIN 執行一次性的複雜查詢。

參考資料

Active Record Query Interface_ Active Record 查詢 PJCHENder - [Rails] Active Record Query（SQL Query & Model 資料查詢） Preload, Eagerload, Includes and Joins Ruby on Rails - 用 Include 和 Join 避免 N+1 Query

← Back to all posts