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实例
- belongs_to
Book.joins(:author)
# SELECT `books`.* FROM `books` INNER JOIN `authors` ON `authors`.`id` = `books`.`author_id`
- 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 ?
总结
- preload 和includes 都可以预加载，includes能分段查询就分段查询， 不能分段查询就自动转为一条sql查询（where条件）
- preload 分段查询，不会加载关联关系
- eager_load 和 includes + references 是一样的，也可以预加载
