Ruby China

新手问题 如何在 Rails 中避免这个 N+1 Query 查询

flowerains · 2017年07月16日 · 最后由 flowerains 回复于 2017年07月16日 · 3823 次阅读

现在已经有个这样的关联关系

# 用户
class User < ApplicationRecord
  has_many :group_users
  has_many :groups, through: :group_users
  has_many :messages
end

# 小组
class Group < ApplicationRecord
  has_many :group_users
  has_many :users ,through: :group_users
  has_many :messages
end

# 小组消息
class Message < ApplicationRecord
  belongs_to :group
  belongs_to :user
end

# 小组和成员的关联表
class GroupUser < ApplicationRecord
  belongs_to :group
  belongs_to :user
end

我在 ERB 模板里面写了这样一句，目的是获取当前这条消息所在小组的所有用户的名称，并且使用','连接他们

<% @messages.each do |message| %>
<strong>Group Chat: <%=  message.group.users.map(&:name).join(',')%></strong><br/>
<% end %>

这样子会产生大量的 N+1 query 查询

SELECT  "groups".* FROM "groups" WHERE "groups"."id" = $1 LIMIT $2  [["id", 1], ["LIMIT", 1]]
SELECT "users".* FROM "users" INNER JOIN "group_users" ON "users"."id" = "group_users"."user_id" WHERE "group_users"."group_id" = $1  [["group_id", 1]]
SELECT  "groups".* FROM "groups" WHERE "groups"."id" = $1 LIMIT $2  [["id", 2], ["LIMIT", 1]]
SELECT "users".* FROM "users" INNER JOIN "group_users" ON "users"."id" = "group_users"."user_id" WHERE "group_users"."group_id" = $1  [["group_id", 2]]

那么，请问如何避免这种 N+1 query 查询，或者最好的做法是怎样，请指教？

u1450154824 #4 2017年07月16日

给你一个 includes 的对比. order belongs_to user

不用 includes

 orders=Order.first 10
SELECT  `orders`.* FROM `orders` ORDER BY `orders`.`id` ASC LIMIT 10
 orders.each {|o| puts o.user.id}
SELECT  `users`.* FROM `users` WHERE `users`.`id` = 34 LIMIT 1
34
  User Load (0.3ms)  SELECT  `users`.* FROM `users` WHERE `users`.`id` = 60 LIMIT 1
60
  User Load (0.2ms)  SELECT  `users`.* FROM `users` WHERE `users`.`id` = 63 LIMIT 1
63
  User Load (0.1ms)  SELECT  `users`.* FROM `users` WHERE `users`.`id` = 63 LIMIT 1
63
  User Load (0.2ms)  SELECT  `users`.* FROM `users` WHERE `users`.`id` = 63 LIMIT 1
63
  User Load (0.2ms)  SELECT  `users`.* FROM `users` WHERE `users`.`id` = 63 LIMIT 1
63
  User Load (0.1ms)  SELECT  `users`.* FROM `users` WHERE `users`.`id` = 63 LIMIT 1
63
  User Load (0.2ms)  SELECT  `users`.* FROM `users` WHERE `users`.`id` = 63 LIMIT 1
63
  User Load (0.2ms)  SELECT  `users`.* FROM `users` WHERE `users`.`id` = 63 LIMIT 1
63
  User Load (0.1ms)  SELECT  `users`.* FROM `users` WHERE `users`.`id` = 73 LIMIT 1
73

用 includes

orders=Order.includes(:user).first 10
  Order Load (0.3ms)  SELECT  `orders`.* FROM `orders` ORDER BY `orders`.`id` ASC LIMIT 10
  User Load (0.3ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` IN (34, 60, 63, 73)
orders.each {|o| puts o.user.id}
34
60
63
63
63
63
63
63
63
73
flowerains #5 2017年07月16日

3Q，感谢各位~

flowerains 关闭了讨论。 07月16日 17:27
需要 登录 后方可回复, 如果你还没有账号请 注册新账号