1 + N 查询

作者:周星 发布:2017-10-07

    上次分享了 rails 中的基本查询,这次我们来看一个在 rails 中非常常见的问题:1+N 查询,什么是1+N查询,我们来看这样一个例子:

# app/model/boy.rb
class Boy < ActiveRecord::Base
  attr_accessible :age, :height, :hobby, :name, :weight
  has_one :address
end

# app/model/address.rb
class Address < ActiveRecord::Base
  attr_accessible :boy_id, :email, :post_code, :street
  belongs_to :boy
end

我们要遍历所有的 boys,并且要带上每个boy 的地址;

# app/controllers/boys_controller.rb
class BoysController < ApplicationController
  def index
     @boys = Boy.limit(5)
  end
end

我们来看一下日志:

Started GET "/boys" for ::1 at 2015-10-15 17:57:30 +0800
Processing by BoysController#index as HTML
  Boy Load (0.3ms)  SELECT  `boys`.* FROM `boys` LIMIT 5
  Address Load (0.2ms)  SELECT  `addresses`.* FROM `addresses` WHERE `addresses`.`boy_id` = 1 LIMIT 1
  Address Load (0.2ms)  SELECT  `addresses`.* FROM `addresses` WHERE `addresses`.`boy_id` = 2 LIMIT 1
  Address Load (0.2ms)  SELECT  `addresses`.* FROM `addresses` WHERE `addresses`.`boy_id` = 3 LIMIT 1
  Address Load (0.2ms)  SELECT  `addresses`.* FROM `addresses` WHERE `addresses`.`boy_id` = 4 LIMIT 1
  Address Load (0.2ms)  SELECT  `addresses`.* FROM `addresses` WHERE `addresses`.`boy_id` = 5 LIMIT 1
  Rendered boys/index.html.erb within layouts/application (36.8ms)
Completed 200 OK in 72ms (Views: 49.0ms | ActiveRecord: 20.7ms)

       从上图我们可以很清楚的看到,我们进行了11次的查询:第一次查询,我们查出了10个boy;之后的查询,我们去查这10个 boy 的每一个 boy 的 address。所以一共查了 1 + 10 次,这就是 1 + N 查询。

       rails 提供了 3 种方法来解决此问题,includes, preload, eager_load,我们来逐一看一下这三种方法是怎么处理原本的 1 + N 查询的

1. 首先看 includes 方法:

@boys = Boy.includes(:address).limit(5)

产生的日志如下:

Started GET "/boys" for ::1 at 2015-10-15 17:58:14 +0800
Processing by BoysController#index as HTML
  Boy Load (0.2ms)  SELECT  `boys`.* FROM `boys` LIMIT 5
  Address Load (0.3ms)  SELECT `addresses`.* FROM `addresses` WHERE `addresses`.`boy_id` IN (1, 2, 3, 4, 5)
  Rendered boys/index.html.erb within layouts/application (43.5ms)
Completed 200 OK in 76ms (Views: 52.9ms | ActiveRecord: 21.3ms)

我们看到,rails 只做了两次查询!第一次查出10个 boy ,第二次使用了 IN 查询,一次查出10个 boy 的 address。

2.preload 方法:

@boy = Boy.preload(:address).limit(10)

日志同上图 includes 方法打出的日志。

3.eager_load 方法:

@boy = Boy.eager_load(:address).limit(5)

这次的日志如下:

Started GET "/boys" for ::1 at 2015-10-15 17:59:22 +0800
Processing by BoysController#index as HTML
  SQL (0.3ms)  SELECT  `boys`.`id` AS t0_r0, `boys`.`age` AS t0_r1, `boys`.`height` AS t0_r2, `boys`.`hobby` AS t0_r3, `boys`.`name` AS t0_r4, `boys`.`weight` AS t0_r5, `boys`.`created_at` AS t0_r6, `boys`.`updated_at` AS t0_r7, `addresses`.`id` AS t1_r0, `addresses`.`boy_id` AS t1_r1, `addresses`.`email` AS t1_r2, `addresses`.`post_code` AS t1_r3, `addresses`.`street` AS t1_r4, `addresses`.`created_at` AS t1_r5, `addresses`.`updated_at` AS t1_r6 FROM `boys` LEFT OUTER JOIN `addresses` ON `addresses`.`boy_id` = `boys`.`id` LIMIT 5
  Rendered boys/index.html.erb within layouts/application (45.1ms)
Completed 200 OK in 77ms (Views: 50.2ms | ActiveRecord: 24.7ms)

       我们看到,eager_load 方法使用了 left_join,只做了一次 SQL 查询!解决了 1+N 的问题。但是我们 一般建议使用 includes 方法** ,**那么这三个方法到底有什么区别?是否 includes 和 preload 方法是同样的效果?什么情况下应该使用 eager_load?我们将在下次分享给出答案。

扩展:

includes 方法中的参数可以为多个,支持嵌套的 model association

boys = Boy.includes(:address, :books)

会查出这些 boys 的每一个 boy 关联的 address 和 books

Boy.includes(:books => [{:comments => :guest}, :tags]).find(1)

会查出 id 为1的 boy ,并eager load 此 boy 的书,还有这些书的标签(tags)和 这些书评论与作者(听起来有点绕,多使用几次就理解了)

支付宝扫码赞助博主


评论(0)