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

# =>
SELECT "users".* FROM "users"
SELECT "posts".* FROM "posts"  WHERE "posts"."user_id" IN (1)

在默认情况下，includes也是这么加载数据的

但问题是，这种情况无法对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

和preload是类似的，但Includes智能一些，我们替换上面报错的代码

User.includes(:posts).where('posts.desc = "ruby is awesome"').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 (posts.desc = "ruby is awesome")

这里Includes通过连表操作(left join)，只执行了一条SQL，但是如果没有where条件的话，还是执行了两个SQL，但如果你仍想强制执行一个SQL的话，需要使用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"

可以看到这相当于includes在where或者order时执行的操作，只有一个SQL

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