前几天在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
查询
文章没有逐字翻译,如有问题,麻烦指出。