Rails ActiveRecord 三种数据预加载形式 includes preload eager_load 2019

includes preload eager_load rails源码实现

module ActiveRecord
  module QueryMethods    
    # Specify relationships to be included in the result set. For
    # example:
    #
    #   users = User.includes(:address)
    #   users.each do |user|
    #     user.address.city
    #   end
    #
    # allows you to access the +address+ attribute of the +User+ model without
    # firing an additional query. This will often result in a
    # performance improvement over a simple join.
    #
    # You can also specify multiple relationships, like this:
    #
    #   users = User.includes(:address, :friends)
    #
    # Loading nested relationships is possible using a Hash:
    #
    #   users = User.includes(:address, friends: [:address, :followers])
    #
    # === conditions
    #
    # If you want to add conditions to your included models you'll have
    # to explicitly reference them. For example:
    #
    #   User.includes(:posts).where('posts.name = ?', 'example')
    #
    # Will throw an error, but this will work:
    #
    #   User.includes(:posts).where('posts.name = ?', 'example').references(:posts)
    #
    # Note that #includes works with association names while #references needs
    # the actual table name.
    def includes(*args)
      check_if_method_has_arguments!(:includes, args)
      spawn.includes!(*args)
    end

    # Forces eager loading by performing a LEFT OUTER JOIN on +args+:
    #
    #   User.eager_load(:posts)
    #   # SELECT "users"."id" AS t0_r0, "users"."name" AS t0_r1, ...
    #   # FROM "users" LEFT OUTER JOIN "posts" ON "posts"."user_id" =
    #   # "users"."id"
    def eager_load(*args)
      check_if_method_has_arguments!(:eager_load, args)
      spawn.eager_load!(*args)
    end


    # Allows preloading of +args+, in the same way that #includes does:
    #
    #   User.preload(:posts)
    #   # SELECT "posts".* FROM "posts" WHERE "posts"."user_id" IN (1, 2, 3)
    def preload(*args)
      check_if_method_has_arguments!(:preload, args)
      spawn.preload!(*args)
    end

  end
end

做一个测试：

class User < ApplicationRecord
  has_many :users_roles, dependent: :destroy
  has_many :roles, through: :users_roles
end

class Role < ApplicationRecord
  has_many :users_roles
  has_many :users, through: :users_roles
end

class UsersRole < ApplicationRecord
  belongs_to :user
  belongs_to :role
end

在不使用includes预加载情况下，没查询一次user.roles 就会产生一次查询 

2.5.1 :068 > User.joins(:roles).map { |user| user.roles.pluck(:name) }
  User Load (1.2ms)  SELECT "users".* FROM "users" INNER JOIN "users_roles" ON "users_roles"."user_id" = "users"."id" AND "users_roles"."deleted_at" IS NULL INNER JOIN "roles" ON "roles"."id" = "users_roles"."role_id" WHERE "users"."deleted_at" IS NULL
   (0.4ms)  SELECT "roles"."name" FROM "roles" INNER JOIN "users_roles" ON "roles"."id" = "users_roles"."role_id" WHERE "users_roles"."deleted_at" IS NULL AND "users_roles"."user_id" = $1  [["user_id", 2]]
   (0.3ms)  SELECT "roles"."name" FROM "roles" INNER JOIN "users_roles" ON "roles"."id" = "users_roles"."role_id" WHERE "users_roles"."deleted_at" IS NULL AND "users_roles"."user_id" = $1  [["user_id", 2]]
   (0.2ms)  SELECT "roles"."name" FROM "roles" INNER JOIN "users_roles" ON "roles"."id" = "users_roles"."role_id" WHERE "users_roles"."deleted_at" IS NULL AND "users_roles"."user_id" = $1  [["user_id", 1]]
 => [["采购", "管理员"], ["采购", "管理员"], ["超级管理员"]] 

使用includes

2.5.1 :069 > User.includes(:roles).map { |user| user.roles.pluck(:name) }
  User Load (0.7ms)  SELECT "users".* FROM "users" WHERE "users"."deleted_at" IS NULL
  UsersRole Load (0.4ms)  SELECT "users_roles".* FROM "users_roles" WHERE "users_roles"."deleted_at" IS NULL AND "users_roles"."user_id" IN ($1, $2)  [["user_id", 2], ["user_id", 1]]
  Role Load (0.3ms)  SELECT "roles".* FROM "roles" WHERE "roles"."id" IN ($1, $2, $3)  [["id", 1], ["id", 8], ["id", 2]]
 => [["采购", "管理员"], ["超级管理员"]]

使用eager_load

2.5.1 :070 > User.eager_load(:roles).map { |user| user.roles.pluck(:name) }
  SQL (0.9ms)  SELECT "users"."id" AS t0_r0, "users"."email" AS t0_r1, "users"."encrypted_password" AS t0_r2, "users"."reset_password_token" AS t0_r3, "users"."reset_password_sent_at" AS t0_r4, "users"."remember_created_at" AS t0_r5, "users"."sign_in_count" AS t0_r6, "users"."current_sign_in_at" AS t0_r7, "users"."last_sign_in_at" AS t0_r8, "users"."current_sign_in_ip" AS t0_r9, "users"."last_sign_in_ip" AS t0_r10, "users"."created_at" AS t0_r11, "users"."updated_at" AS t0_r12, "users"."username" AS t0_r13, "users"."phone_number" AS t0_r14, "users"."is_admin" AS t0_r15, "users"."address" AS t0_r16, "users"."corporate_name" AS t0_r17, "users"."default_address_id" AS t0_r18, "users"."account_finance" AS t0_r19, "users"."remark" AS t0_r20, "users"."contact" AS t0_r21, "users"."agent_id" AS t0_r22, "users"."deleted_at" AS t0_r23, "users"."role" AS t0_r24, "users"."effective" AS t0_r25, "users"."full_name" AS t0_r26, "roles"."id" AS t1_r0, "roles"."name" AS t1_r1, "roles"."description" AS t1_r2, "roles"."slug" AS t1_r3, "roles"."created_at" AS t1_r4, "roles"."updated_at" AS t1_r5, "roles"."is_manager" AS t1_r6 FROM "users" LEFT OUTER JOIN "users_roles" ON "users_roles"."user_id" = "users"."id" AND "users_roles"."deleted_at" IS NULL LEFT OUTER JOIN "roles" ON "roles"."id" = "users_roles"."role_id" WHERE "users"."deleted_at" IS NULL
 => [["采购", "管理员"], ["超级管理员"]] 

使用preload

2.5.1 :071 > User.preload(:roles).map { |user| user.roles.pluck(:name) }
  User Load (0.8ms)  SELECT "users".* FROM "users" WHERE "users"."deleted_at" IS NULL
  UsersRole Load (0.4ms)  SELECT "users_roles".* FROM "users_roles" WHERE "users_roles"."deleted_at" IS NULL AND "users_roles"."user_id" IN ($1, $2)  [["user_id", 2], ["user_id", 1]]
  Role Load (0.3ms)  SELECT "roles".* FROM "roles" WHERE "roles"."id" IN ($1, $2, $3)  [["id", 1], ["id", 8], ["id", 2]]
 => [["采购", "管理员"], ["超级管理员"]] 

incldues eager_load preload 在没有查询条件时，输出结果是一样的

如果加入条件查询呢？

首先我们看一下，角色是admin的user, 查出来只有一个

2.5.1 :034 > User.joins(:roles).where(roles: {slug: 'admin'})
  User Load (0.9ms)  SELECT  "users".* FROM "users" INNER JOIN "users_roles" ON "users_roles"."user_id" = "users"."id" AND "users_roles"."deleted_at" IS NULL INNER JOIN "roles" ON "roles"."id" = "users_roles"."role_id" WHERE "users"."deleted_at" IS NULL AND "roles"."slug" = $1 LIMIT $2  [["slug", "admin"], ["LIMIT", 11]]
 => #<ActiveRecord::Relation [#<User id: 2, email: "", created_at: "2019-03-08 06:47:11", updated_at: "2019-03-08 06:47:18", username: "caigou", phone_number: "12345678900", is_admin: false, address: nil, corporate_name: nil, default_address_id: nil, account_finance: 0.0, remark: nil, contact: nil, agent_id: nil, deleted_at: nil, role: nil, effective: true, full_name: "采购">]>

我们看一下这个user有哪些roles, 查出来是两个角色，‘采购’和‘管理员’

2.5.1 :046 > u = User.joins(:roles).where(roles: {slug: 'admin'}).first
  User Load (0.7ms)  SELECT  "users".* FROM "users" INNER JOIN "users_roles" ON "users_roles"."user_id" = "users"."id" AND "users_roles"."deleted_at" IS NULL INNER JOIN "roles" ON "roles"."id" = "users_roles"."role_id" WHERE "users"."deleted_at" IS NULL AND "roles"."slug" = $1 ORDER BY "users"."id" ASC LIMIT $2  [["slug", "admin"], ["LIMIT", 1]]
 => #<User id: 2, email: "", created_at: "2019-03-08 06:47:11", updated_at: "2019-03-08 06:47:18", username: "caigou", phone_number: "12345678900", is_admin: false, address: nil, corporate_name: nil, default_address_id: nil, account_finance: 0.0, remark: nil, contact: nil, agent_id: nil, deleted_at: nil, role: nil, effective: true, full_name: "采购"> 
2.5.1 :047 > u.roles.pluck(:name)
   (0.5ms)  SELECT "roles"."name" FROM "roles" INNER JOIN "users_roles" ON "roles"."id" = "users_roles"."role_id" WHERE "users_roles"."deleted_at" IS NULL AND "users_roles"."user_id" = $1  [["user_id", 2]]
 => ["采购", "管理员"]

那么我们使用includes

2.5.1 :014 > User.includes(:roles).where(roles: {slug: 'admin'}).map { |user| user.roles.pluck(:name) }
  SQL (1.4ms)  SELECT "users"."id" AS t0_r0, "users"."email" AS t0_r1, "users"."encrypted_password" AS t0_r2, "users"."reset_password_token" AS t0_r3, "users"."reset_password_sent_at" AS t0_r4, "users"."remember_created_at" AS t0_r5, "users"."sign_in_count" AS t0_r6, "users"."current_sign_in_at" AS t0_r7, "users"."last_sign_in_at" AS t0_r8, "users"."current_sign_in_ip" AS t0_r9, "users"."last_sign_in_ip" AS t0_r10, "users"."created_at" AS t0_r11, "users"."updated_at" AS t0_r12, "users"."username" AS t0_r13, "users"."phone_number" AS t0_r14, "users"."is_admin" AS t0_r15, "users"."address" AS t0_r16, "users"."corporate_name" AS t0_r17, "users"."default_address_id" AS t0_r18, "users"."account_finance" AS t0_r19, "users"."remark" AS t0_r20, "users"."contact" AS t0_r21, "users"."agent_id" AS t0_r22, "users"."deleted_at" AS t0_r23, "users"."role" AS t0_r24, "users"."effective" AS t0_r25, "users"."full_name" AS t0_r26, "roles"."id" AS t1_r0, "roles"."name" AS t1_r1, "roles"."description" AS t1_r2, "roles"."slug" AS t1_r3, "roles"."created_at" AS t1_r4, "roles"."updated_at" AS t1_r5, "roles"."is_manager" AS t1_r6 FROM "users" LEFT OUTER JOIN "users_roles" ON "users_roles"."user_id" = "users"."id" AND "users_roles"."deleted_at" IS NULL LEFT OUTER JOIN "roles" ON "roles"."id" = "users_roles"."role_id" WHERE "users"."deleted_at" IS NULL AND "roles"."slug" = $1  [["slug", "admin"]]
 => [["管理员"]] 

使用eager_load

User.eager_load(:roles).where(roles: {slug: 'admin'}).map { |user| user.roles.pluck(:name) }
  SQL (1.0ms)  SELECT "users"."id" AS t0_r0, "users"."email" AS t0_r1, "users"."encrypted_password" AS t0_r2, "users"."reset_password_token" AS t0_r3, "users"."reset_password_sent_at" AS t0_r4, "users"."remember_created_at" AS t0_r5, "users"."sign_in_count" AS t0_r6, "users"."current_sign_in_at" AS t0_r7, "users"."last_sign_in_at" AS t0_r8, "users"."current_sign_in_ip" AS t0_r9, "users"."last_sign_in_ip" AS t0_r10, "users"."created_at" AS t0_r11, "users"."updated_at" AS t0_r12, "users"."username" AS t0_r13, "users"."phone_number" AS t0_r14, "users"."is_admin" AS t0_r15, "users"."address" AS t0_r16, "users"."corporate_name" AS t0_r17, "users"."default_address_id" AS t0_r18, "users"."account_finance" AS t0_r19, "users"."remark" AS t0_r20, "users"."contact" AS t0_r21, "users"."agent_id" AS t0_r22, "users"."deleted_at" AS t0_r23, "users"."role" AS t0_r24, "users"."effective" AS t0_r25, "users"."full_name" AS t0_r26, "roles"."id" AS t1_r0, "roles"."name" AS t1_r1, "roles"."description" AS t1_r2, "roles"."slug" AS t1_r3, "roles"."created_at" AS t1_r4, "roles"."updated_at" AS t1_r5, "roles"."is_manager" AS t1_r6 FROM "users" LEFT OUTER JOIN "users_roles" ON "users_roles"."user_id" = "users"."id" AND "users_roles"."deleted_at" IS NULL LEFT OUTER JOIN "roles" ON "roles"."id" = "users_roles"."role_id" WHERE "users"."deleted_at" IS NULL AND "roles"."slug" = $1  [["slug", "admin"]]
 => [["管理员"]] 

使用preload

2.5.1 :017 > User.preload(:roles).where(roles: {slug: 'admin'}).map { |user| user.roles.pluck(:name) }
Traceback (most recent call last):
        2: from (irb):17
        1: from (irb):17:in `map'
ActiveRecord::StatementInvalid (PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "roles")
LINE 1: ...OM "users" WHERE "users"."deleted_at" IS NULL AND "roles"."s...
                                                             ^
: SELECT "users".* FROM "users" WHERE "users"."deleted_at" IS NULL AND "roles"."slug" = $1

直接报错了，需要joins(:roles)进行查询

2.5.1 :020 > User.joins(:roles).where(roles: {slug: 'admin'}).preload(:roles).map { |user| user.roles.pluck(:name) }
  User Load (0.5ms)  SELECT "users".* FROM "users" INNER JOIN "users_roles" ON "users_roles"."user_id" = "users"."id" AND "users_roles"."deleted_at" IS NULL INNER JOIN "roles" ON "roles"."id" = "users_roles"."role_id" WHERE "users"."deleted_at" IS NULL AND "roles"."slug" = $1  [["slug", "admin"]]
  UsersRole Load (0.3ms)  SELECT "users_roles".* FROM "users_roles" WHERE "users_roles"."deleted_at" IS NULL AND "users_roles"."user_id" = $1  [["user_id", 2]]
  Role Load (1.9ms)  SELECT "roles".* FROM "roles" WHERE "roles"."id" IN ($1, $2)  [["id", 8], ["id", 2]]
 => [["采购", "管理员"]] 

可以看出，三者输出结果是不一样的，

可以看出includes和eager输出的user的roles是被查询条件过滤过的，

而preload 在输出用户角色时候，并没有加入查询条件，而是输出该user所有roles, 我们可以看到console是输出一条新的sql

Role Load (1.9ms)  SELECT "roles".* FROM "roles" WHERE "roles"."id" IN ($1, $2)  [["id", 8], ["id", 2]]

所以如果你在页面上想展示该user的所有角色（roles）时候，使用includes明显是错误的，而是使用preload