rails数据库查询 N + 1 查询的解决办法

schema.rb

ActiveRecord::Schema.define(version: 20150203032005) do

  create_table "addresses", force: true do |t|
    t.integer  "client_id"
    t.string   "street"
    t.string   "postcode"
    t.datetime "created_at"
    t.datetime "updated_at"
  end

  create_table "clients", force: true do |t|
    t.string   "name"
    t.string   "gender"
    t.datetime "created_at"
    t.datetime "updated_at"
  end

  create_table "infos", force: true do |t|
    t.integer  "address_id"
    t.string   "history"
    t.datetime "created_at"
    t.datetime "updated_at"
  end

end

client.rb

class Client < ActiveRecord::Base
  has_one :address
end

address.rb

class Address < ActiveRecord::Base
  belongs_to :client
  has_many :infos
end

info.rb

class Address < ActiveRecord::Base
  belongs_to :client
  has_many :infos
end

 

Client.all

  Client Load (0.4ms)  SELECT "clients".* FROM "clients"
 => #<ActiveRecord::Relation [#<Client id: 1, name: "wei", gender: "1", created_at: "2015-02-03 02:12:36", updated_at: "2015-02-03 02:12:36">, #<Client id: 2, name: "yan", gender: "1", created_at: "2015-02-03 02:48:11", updated_at: "2015-02-03 02:48:11">]> 

Address.all

SELECT "addresses".* FROM "addresses"
 => #<ActiveRecord::Relation [#<Address id: 1, client_id: 1, street: "huanyuan", postcode: "123456", created_at: "2015-02-03 02:13:45", updated_at: "2015-02-03 02:13:45">, #<Address id: 2, client_id: 2, street: "huayuan", postcode: "23456", created_at: "2015-02-03 02:50:12", updated_at: "2015-02-03 02:50:12">]> 

Info.all

SELECT "infos".* FROM "infos"
 => #<ActiveRecord::Relation 
[#<Info id: 1, address_id: 1, history: "1110", created_at: "2015-02-03 03:25:34", updated_at: "2015-02-03 03:25:34">,
 #<Info id: 2, address_id: 1, history: "1111", created_at: "2015-02-03 03:26:16", updated_at: "2015-02-03 03:26:16">, 
#<Info id: 3, address_id: 1, history: "1112", created_at: "2015-02-03 03:26:21", updated_at: "2015-02-03 03:26:21">, 
#<Info id: 4, address_id: 2, history: "2110", created_at: "2015-02-03 03:26:32", updated_at: "2015-02-03 03:26:32">, 
#<Info id: 5, address_id: 2, history: "2111", created_at: "2015-02-03 03:26:34", updated_at: "2015-02-03 03:26:34">,
 #<Info id: 6, address_id: 2, history: "2112", created_at: "2015-02-03 03:26:37", updated_at: "2015-02-03 03:26:37">, 
#<Info id: 7, address_id: 2, history: "2113", created_at: "2015-02-03 03:26:39", updated_at: "2015-02-03 03:26:39">]> 

　　

  N + 1 查询的解决办法

clients = Client.limit(2)
 
clients.each do |client|
  puts client.address.postcode
end

生成的sql语句为:

 SELECT "clients".* FROM "clients"
 => #<ActiveRecord::Relation 
[#<Client id: 1, name: "wei", gender: "1", created_at: "2015-02-03 02:12:36", updated_at: "2015-02-03 02:12:36">,
 #<Client id: 2, name: "yan", gender: "1", created_at: "2015-02-03 02:48:11", updated_at: "2015-02-03 02:48:11">]> 

而

clients = Client.includes(:address).limit(2)
 
clients.each do |client|
  puts client.address.postcode
end

生成的sql语句为:

 Client Load (0.5ms)  SELECT  "clients".* FROM "clients"  LIMIT 2
  Address Load (0.4ms)  SELECT "addresses".* FROM "addresses"  WHERE "addresses"."client_id" IN (1, 2)
 => #<ActiveRecord::Relation [#<Client id: 1, name: "wei", gender: "1", created_at: "2015-02-03 02:12:36", updated_at: "2015-02-03 02:12:36">, 
#<Client id: 2, name: "yan", gender: "1", created_at: "2015-02-03 02:48:11", updated_at: "2015-02-03 02:48:11">]> 

按需加载多个关联

clients = Client.all
clients.each do |client|
    puts client.address.infos.first.history
end

生成的sql语句:

Address Load (0.3ms)  SELECT  "addresses".* FROM "addresses"  WHERE "addresses"."client_id" = ? LIMIT 1  [["client_id", 1]]
  Info Load (0.3ms)  SELECT  "infos".* FROM "infos"  WHERE "infos"."address_id" = ?  ORDER BY "infos"."id" ASC LIMIT 1  [["address_id", 1]]
1110
  Address Load (0.1ms)  SELECT  "addresses".* FROM "addresses"  WHERE "addresses"."client_id" = ? LIMIT 1  [["client_id", 2]]
  Info Load (0.1ms)  SELECT  "infos".* FROM "infos"  WHERE "infos"."address_id" = ?  ORDER BY "infos"."id" ASC LIMIT 1  [["address_id", 2]]
2110
 => [#<Client id: 1, name: "wei", gender: "1", created_at: "2015-02-03 02:12:36", updated_at: "2015-02-03 02:12:36">, #<Client id: 2, name: "yan", gender: "1", created_at: "2015-02-03 02:48:11", updated_at: "2015-02-03 02:48:11">] 

 

Client和Address是has_one关系， Address和info是has_many关系， 想要一次性加载资源使client可以通过address获取info, 使用下面语句:

 

clients = Client.includes(address: :infos)

生成的sql语句：

 Client Load (0.4ms)  SELECT "clients".* FROM "clients"
  Address Load (0.5ms)  SELECT "addresses".* FROM "addresses"  WHERE "addresses"."client_id" IN (1, 2)
  Info Load (0.3ms)  SELECT "infos".* FROM "infos"  WHERE "infos"."address_id" IN (1, 2)
 => #<ActiveRecord::Relation [#<Client id: 1, name: "wei", gender: "1", created_at: "2015-02-03 02:12:36", updated_at: "2015-02-03 02:12:36">, #<Client id: 2, name: "yan", gender: "1", created_at: "2015-02-03 02:48:11", updated_at: "2015-02-03 02:48:11">]> 

　　

clients.each do |c|
    puts c.address.infos.first.history
end

输出:

1110
2110