rails基础-解决 n+1

N+1问题是新手常犯的一个问题，这里记录如何解决rails项目中的N+1问题

rails 支持的关联

ActiveRecord 支持6种关联

• belongs_to
• has_one
• has_many
• has_many_and_belongs_to
• has_one :thought
• has_many :thought

以为为model作为示例：

# rails中使用声明的形式来为模型添加功能，当声明了一种关联关系后，rails会维护这两个model的主键-外键
class Book < ApplicationRecord
  belongs_to :author
end

class Credit < ApplicationRecord
  belongs_to :author
end

class Author < ApplicationRecord
  has_many :books
  has_one  :credit
end

rails 支持的加载关联数据方法

preload

preload 会根据关联关系生成附加的SQL语句来加载关联关系

class User < AppilicationRecord

end

class Credit < AppilicationRecord
    belongs_to :user
end

credits = Credit.perload(:user)
# => SELECT `credits`.* FROM `credits`
     SELECT `users`.* FROM `users` WHERE `users`.`id` IN [# 这里是credit中user_id 的集合]

可以看见，preload 在查找了所有的credit记录后，又生成一条sql去加载credit关联的user记录，在根据 credits.first.user.association 时不产生新的sql语句

注意这种情况只是预加载了关联的对象，但是并没有加载关联关系(两条单独的sql)，所以想根据user的属性去查找credit是行不通的

credits.where(user: {mobile_number: 173xxxx5384})
# =>
ActiveRecord::StatementInvalid: Mysql2::Error: Unknown column 'user.mobile_number' in 'where clause': SELECT `credits`.* FROM `credits` WHERE `user`.`mobile_number` = '173xxxx5384'

eager_load

left outer joins，从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL
eager_load 会生成一条sql，并加载了所有的关联数据

Credit.eager_load(:user)
# =>
SELECT  "credits"."id" AS t0_r0, "credits"."user_id" AS t0_r1, "users"."id" AS t1_r0, "users"."name" AS t1_r1, "users"."email" AS t1_r2, "users"."nickname" AS t1_r3, "users"."introduction" AS t1_r4, "users"."password_digest" AS t1_r5, "users"."credit_id" AS t1_r6, "users"."created_at" AS t1_r7, "users"."updated_at" AS t1_r8, "users"."auth_token" AS t1_r9 FROM "credits" LEFT OUTER JOIN "users" ON "users"."id" = "credits"."user_id" LIMIT 1

eager_load 会根据关联关系生成一条SQL语句，加载关联对象也加载了关联关系，但存在一个问题就是随着关联对象的增加，SQL语句会愈加的复杂，影响SQL效率

Joins

rails 中的joins都是inner joins,取得是交集
joins 的接受者可以是model也是ActiveRecord::Relation实例

1. belongs_to

  Book.joins(:author)
  # SELECT `books`.* FROM `books` INNER JOIN `authors` ON `authors`.`id` = `books`.`author_id`

2. has_many

  Author.joins(:books)
  # sql:  SELECT `authors`.* FROM `authors` INNER JOIN `books` ON `books`.`author_id` = `authors`.`id`

可以使用joins关联多个对象

  # 以下两个关联功能一致
  Author.joins(:books, :credit)
  Author.joins(:books).joins(:credit)
  
  #sql
   SELECT `authors`.* FROM `authors` 
   INNER JOIN `books` ON `books`.`author_id` = `authors`.`id` 
   INNER JOIN `credits` ON `credits`.`author_id` = `authors`.`id`

使用joins这样链式关联多个对象会产生重复记录，可以用uniq 去除

includes

includes 单独使用时（不加条件）和preload 是一样的，根据关联关系单独生成SQL

User.includes(:credit)
# =>
SELECT `users`.* FROM `users`
SELECT `credits`.* FROM `credits` WHERE `credits`.`user_id` IN [# 这里是user的id数组]

includes 加条件（where）时，会自动转为一条sql

Credit.includes(:user).where(user_id: 1)
# => 
SELECT  "credits".* FROM "credits" WHERE "credits"."user_id" = ? LIMIT ?

在rails中有个灵活的用法 includes + reference, 功能类似于eager_load

class Credit < ApplicationRecord
  belongs_to :user
end
class User < ApplicationRecord
end

Credit.includes(:user).references(:user)

SELECT  "credits"."id" AS t0_r0, "credits"."user_id" AS t0_r1, "users"."id" AS t1_r0, "users"."name" AS t1_r1, "users"."email" AS t1_r2, "users"."nickname" AS t1_r3, "users"."introduction" AS t1_r4, "users"."password_digest" AS t1_r5, "users"."credit_id" AS t1_r6, "users"."created_at" AS t1_r7, "users"."updated_at" AS t1_r8, "users"."auth_token" AS t1_r9 FROM "credits" LEFT OUTER JOIN "users" ON "users"."id" = "credits"."user_id" LIMIT ?

总结

1. preload 和includes 都可以预加载，includes能分段查询就分段查询， 不能分段查询就自动转为一条sql查询（where条件）
2. preload 分段查询，不会加载关联关系
3. eager_load 和 includes + references 是一样的，也可以预加载