Rails Scopes 预加载

前几天在rubyweekly里面看到一篇文章，感觉写得不错。拿出来分享一下。原文更精彩

scope 会造成N+1查询

作为一个Rails开发者，我们经常使用scope来做查询，以简化你的代码，如：

1
2
3
4
5

class Review < ActiveRecord::Base
  belongs_to :restaurant

  scope :positive, -> { where("rating > 3.0") }
end

1
2
3
4

irb(main):001:0> Restaurant.first.reviews.positive.count
  Restaurant Load (0.4ms)  SELECT  `restaurants`.* FROM `restaurants`  ORDER BY `restaurants`.`id` ASC LIMIT 1
   (0.6ms)  SELECT COUNT(*) FROM `reviews` WHERE `reviews`.`restaurant_id` = 1 AND (rating > 3.0)
=> 5

但是，当你一不小心，这将严重的影响你应用的性能。 为什么呢？因为使用scope进行定义的查询并不会被预加载。 假设你要查询一些restaurants所有positive reviews：

1
2
3
4
5
6
7
8
9
10

irb(main):001:0> restauraunts = Restaurant.first(5)
irb(main):002:0> restauraunts.map do |restaurant|
irb(main):003:1*   "#{restaurant.name}: #{restaurant.reviews.positive.length} positive reviews."
irb(main):004:1> end
  Review Load (0.6ms)  SELECT `reviews`.* FROM `reviews` WHERE `reviews`.`restaurant_id` = 1 AND (rating > 3.0)
  Review Load (0.5ms)  SELECT `reviews`.* FROM `reviews` WHERE `reviews`.`restaurant_id` = 2 AND (rating > 3.0)
  Review Load (0.7ms)  SELECT `reviews`.* FROM `reviews` WHERE `reviews`.`restaurant_id` = 3 AND (rating > 3.0)
  Review Load (0.7ms)  SELECT `reviews`.* FROM `reviews` WHERE `reviews`.`restaurant_id` = 4 AND (rating > 3.0)
  Review Load (0.7ms)  SELECT `reviews`.* FROM `reviews` WHERE `reviews`.`restaurant_id` = 5 AND (rating > 3.0)
=> ["Judd's Pub: 5 positive reviews.", "Felix's Nightclub: 6 positive reviews.", "Mabel's Burrito Shack: 7 positive reviews.", "Kendall's Burrito Shack: 2 positive reviews.", "Elisabeth's Deli: 15 positive reviews."]

我们可以看到，scope:positive并没有被缓存起来，这明显的是一个N+1查询。

用associations代替scopes

我们可以通过用associations代替scopes，来避免这个问题。请看下面例子：

1
2
3

class Restaurant < ActiveRecord::Base
  has_many :reviews
end

当我们查看这个文档时,我们可以看到，has_many允许我们添加自定义查询

1
2
3
4

class Restaurant < ActiveRecord::Base
  has_many :reviews
  has_many :positive_reviews, -> { where("rating > 3.0") }, class_name: "Review"
end

但我们可以这样子获取一个restaurant的所有positive_reviews

1
2
3
4

irb(main):001:0> Restaurant.first.positive_reviews.count
  Restaurant Load (0.2ms)  SELECT  `restaurants`.* FROM `restaurants`  ORDER BY `restaurants`.`id` ASC LIMIT 1
   (0.4ms)  SELECT COUNT(*) FROM `reviews` WHERE `reviews`.`restaurant_id` = 1 AND (rating > 3.0)
=> 5

然后我们就可以通过include来预加载这个关联关系了

1
2
3
4
5
6
7

irb(main):001:0> restauraunts = Restaurant.includes(:positive_reviews).first(5)
  Restaurant Load (0.3ms)  SELECT  `restaurants`.* FROM `restaurants`  ORDER BY `restaurants`.`id` ASC LIMIT 5
  Review Load (1.2ms)  SELECT `reviews`.* FROM `reviews` WHERE (rating > 3.0) AND `reviews`.`restaurant_id` IN (1, 2, 3, 4, 5)
irb(main):002:0> restauraunts.map do |restaurant|
irb(main):003:1*   "#{restaurant.name}: #{restaurant.positive_reviews.length} positive reviews."
irb(main):004:1> end
=> ["Judd's Pub: 5 positive reviews.", "Felix's Nightclub: 6 positive reviews.", "Mabel's Burrito Shack: 7 positive reviews.", "Kendall's Burrito Shack: 2 positive reviews.", "Elisabeth's Deli: 15 positive reviews."]

至此，6个Sql查询变成2个

消除重复

现在我们定义了一个scope:positive和一个关系has_many :positive_reviews，我们可以看到，它们是重复的。我们可以简单的消除这个DRY.

1
2
3
4
5
6
7
8
9
10

class Review < ActiveRecord::Base
  belongs_to :restaurant

  scope :positive, -> { where("rating > 3.0") }
end

class Restaurant < ActiveRecord::Base
  has_many :reviews
  has_many :positive_reviews, -> { positive }, class_name: "Review"
end

至此，我们可以知道scope虽然好用，但是当你发现的代码出现以上问题时，简单的修改，能减少许多sql查询

文章没有逐字翻译，如有问题，麻烦指出。

Comments

Comments