Rails N+1

Rails N+1 问题

是什么？

# Get all posts
posts = Post.all

# For each post, a new author query will be triggered
posts.each do |post|
  post.author.name
end

我先获取了所有的帖子，然后想知道每个帖子的作者名称，实际执行时相当于每个帖子都执行了一遍SQL查询这个帖子的作者信息。如果有1000个帖子，则会执行1000条SQL

在什么时候发生？

当我们使用Rails的ORM框架操作聚合关系的操作，比如：has_many | belongs_to

这极大的影响了我的系统性能

如何解决？

三种方式：Preload，includes，Eager load，joins

Preload

User.preload(:posts).to_a

# =>
# 查找所有user数据
SELECT "users".* FROM "users"
# 根据上面的users的所有id，再获取对应的posts缓存起来
SELECT "posts".* FROM "posts"  WHERE "posts"."user_id" IN (1)

但问题是，这种情况无法对post(关联查询的表)加上where条件

User.preload(:posts).where("posts.desc='ruby is awesome'")

# =>
SQLite3::SQLException: no such column: posts.desc:
SELECT "users".* FROM "users"  WHERE (posts.desc='ruby is awesome')

可以给User中添加：

User.preload(:posts).where("users.name='Neeraj'")

# =>
SELECT "users".* FROM "users"  WHERE (users.name='Neeraj')
SELECT "posts".* FROM "posts"  WHERE "posts"."user_id" IN (3)

可以看到，这里是先对User进行筛选，然后再查询posts，也就是说在Preload时，where条件是加在第一个查询语句的，Preload是执行了两个查询语句。这对于两个表数据量很大时，联表可能性差时，用这个会好一些

Includes

默认会进行预加载关联表数据，但是where条件中也不能包含关联表的筛选，如果要加的话需要使用references

User.includes(:posts).references(:posts).to_a

# =>
SELECT "users"."id" AS t0_r0, "users"."name" AS t0_r1, "posts"."id" AS t1_r0,
       "posts"."title" AS t1_r1,
       "posts"."user_id" AS t1_r2, "posts"."desc" AS t1_r3
FROM "users" LEFT OUTER JOIN "posts" ON "posts"."user_id" = "users"."id"

Eager Load

User.eager_load(:posts).to_a

# =>
SELECT "users"."id" AS t0_r0, "users"."name" AS t0_r1, "posts"."id" AS t1_r0,
       "posts"."title" AS t1_r1, "posts"."user_id" AS t1_r2, "posts"."desc" AS t1_r3
FROM "users" LEFT OUTER JOIN "posts" ON "posts"."user_id" = "users"."id"

支持在关联模型中进行where查询，默认使用left outer join

Joins

与上面Left join不同，Joins执行的是inner join

User.joins(:posts)

# =>
SELECT "users".* FROM "users" INNER JOIN "posts" ON "posts"."user_id" = "users"."id"

上面是因为没有post被查询到，而且上面的查询会导致数据冗余，我们来构造一些数据

def self.setup
  User.delete_all
  Post.delete_all

  u = User.create name: 'Neeraj'
  u.posts.create! title: 'ruby', desc: 'ruby is awesome'
  u.posts.create! title: 'rails', desc: 'rails is awesome'
  u.posts.create! title: 'JavaScript', desc: 'JavaScript is awesome'

  u = User.create name: 'Neil'
  u.posts.create! title: 'JavaScript', desc: 'Javascript is awesome'

  u = User.create name: 'Trisha'
end

当我们执行：User.joins(:posts)

#<User id: 9, name: "Neeraj">
#<User id: 9, name: "Neeraj">
#<User id: 9, name: "Neeraj">
#<User id: 10, name: "Neil">

可以通过distinct来删除冗余

User.joins(:posts).select('distinct users.*').to_a

如果你想用posts中的属性，需要显示的选择

records = User.joins(:posts).select('distinct users.*, posts.title as posts_title').to_a
records.each do |user|
  puts user.name
  puts user.posts_title
end