修复Rails中的n+1查询

Question

我有图表:https://app.diagrams.net/#LVenjob在我的Rails应用程序中，我想创建最喜欢的工作特性:

控制器:

#favorite_jobs_controller.rb
class FavoriteJobsController < ApplicationController
before_action :logged_in_user
before_action :load_job, only: %i[create destroy]
def create
#code
end
def destroy
#code
end
private
def load_job
#load the job
end
end

#jobs_controller.rb
class JobsController < ApplicationController
before_action :history, only: :show
def index
if job_params.present?
search
else
@jobs = Job.sort_by_date(page: params[:page], per_page: Job::JOB_PER_PAGE)
end
end
def show
@job = Job.find_by(id: params[:id])
render partial: 'shared/job_not_found' if @job.nil?
end
end

模型

#job.rb
LATEST_JOBS_LIMIT = 5
JOB_PER_PAGE = 20
has_and_belongs_to_many :industries
has_and_belongs_to_many :cities
belongs_to :company
has_many :apply_jobs, dependent: :destroy
has_many :favorite_jobs, dependent: :destroy
has_many :history_job, dependent: :destroy
def self.sort_by_date(page: 1, per_page: 1)
includes(:favorite_jobs, :cities, :cities_jobs, :company).order(created_at: :desc).page(page).per(per_page).references(:cities)
end

#user.rb
class User < ApplicationRecord
has_many :apply_jobs, dependent: :destroy
has_many :favorite_jobs, dependent: :destroy
has_many :history_jobs, dependent: :destroy

def favorite?(job)
favorite_jobs.exists?(job_id: job.id)
end
end

#favorite_job.rb
class FavoriteJob < ApplicationRecord
belongs_to :job
belongs_to :user
end

在视图:

#index.html.slim
#jobs index
.container
.container
- @jobs.each do |job|
- if user_signed_in? && current_user.favorite?(job)
= render 'shared/unfavorite', job_id: job.id
- else
= render 'shared/favorite', job_id: job.id

如果用户未收藏此作业或未登录，则呈现'apply'按钮

rails控制台结果

↳ app/views/jobs/index.html.slim:10
User Load (1.4ms)  SELECT `users`.* FROM `users` WHERE `users`.`id` = 2 ORDER BY `users`.`id` ASC LIMIT 1
↳ app/views/jobs/index.html.slim:20
FavoriteJob Exists? (0.9ms)  SELECT 1 AS one FROM `favorite_jobs` WHERE `favorite_jobs`.`user_id` = 2 AND `favorite_jobs`.`job_id` = 244 LIMIT 1
↳ app/models/user.rb:30:in `favorite?'
Rendered shared/_favorite.html.slim (Duration: 0.3ms | Allocations: 107)
FavoriteJob Exists? (0.7ms)  SELECT 1 AS one FROM `favorite_jobs` WHERE `favorite_jobs`.`user_id` = 2 AND `favorite_jobs`.`job_id` = 240 LIMIT 1
↳ app/models/user.rb:30:in `favorite?'
Rendered shared/_unfavorite.html.slim (Duration: 0.4ms | Allocations: 116)
FavoriteJob Exists? (0.7ms)  SELECT 1 AS one FROM `favorite_jobs` WHERE `favorite_jobs`.`user_id` = 2 AND `favorite_jobs`.`job_id` = 237 LIMIT 1
↳ app/models/user.rb:30:in `favorite?'
Rendered shared/_unfavorite.html.slim (Duration: 0.3ms | Allocations: 116)
FavoriteJob Exists? (0.9ms)  SELECT 1 AS one FROM `favorite_jobs` WHERE `favorite_jobs`.`user_id` = 2 AND `favorite_jobs`.`job_id` = 234 LIMIT 1
↳ app/models/user.rb:30:in `favorite?'
Rendered shared/_favorite.html.slim (Duration: 0.4ms | Allocations: 108)
FavoriteJob Exists? (0.8ms)  SELECT 1 AS one FROM `favorite_jobs` WHERE `favorite_jobs`.`user_id` = 2 AND `favorite_jobs`.`job_id` = 233 LIMIT 1
↳ app/models/user.rb:30:in `favorite?'
Rendered shared/_favorite.html.slim (Duration: 0.3ms | Allocations: 108)
FavoriteJob Exists? (0.8ms)  SELECT 1 AS one FROM `favorite_jobs` WHERE `favorite_jobs`.`user_id` = 2 AND `favorite_jobs`.`job_id` = 229 LIMIT 1
↳ app/models/user.rb:30:in `favorite?'
Rendered shared/_unfavorite.html.slim (Duration: 0.3ms | Allocations: 116)
FavoriteJob Exists? (0.8ms)  SELECT 1 AS one FROM `favorite_jobs` WHERE `favorite_jobs`.`user_id` = 2 AND `favorite_jobs`.`job_id` = 228 LIMIT 1
↳ app/models/user.rb:30:in `favorite?'
Rendered shared/_unfavorite.html.slim (Duration: 0.3ms | Allocations: 116)
FavoriteJob Exists? (0.8ms)  SELECT 1 AS one FROM `favorite_jobs` WHERE `favorite_jobs`.`user_id` = 2 AND `favorite_jobs`.`job_id` = 227 LIMIT 1
↳ app/models/user.rb:30:in `favorite?'
Rendered shared/_unfavorite.html.slim (Duration: 0.4ms | Allocations: 116)
FavoriteJob Exists? (0.9ms)  SELECT 1 AS one FROM `favorite_jobs` WHERE `favorite_jobs`.`user_id` = 2 AND `favorite_jobs`.`job_id` = 224 LIMIT 1
↳ app/models/user.rb:30:in `favorite?'
Rendered shared/_unfavorite.html.slim (Duration: 0.6ms | Allocations: 116)
FavoriteJob Exists? (0.8ms)  SELECT 1 AS one FROM `favorite_jobs` WHERE `favorite_jobs`.`user_id` = 2 AND `favorite_jobs`.`job_id` = 220 LIMIT 1
↳ app/models/user.rb:30:in `favorite?'
Rendered shared/_unfavorite.html.slim (Duration: 0.4ms | Allocations: 116)
FavoriteJob Exists? (0.7ms)  SELECT 1 AS one FROM `favorite_jobs` WHERE `favorite_jobs`.`user_id` = 2 AND `favorite_jobs`.`job_id` = 212 LIMIT 1
↳ app/models/user.rb:30:in `favorite?'
Rendered shared/_favorite.html.slim (Duration: 0.3ms | Allocations: 108)
FavoriteJob Exists? (0.8ms)  SELECT 1 AS one FROM `favorite_jobs` WHERE `favorite_jobs`.`user_id` = 2 AND `favorite_jobs`.`job_id` = 210 LIMIT 1
↳ app/models/user.rb:30:in `favorite?'
Rendered shared/_unfavorite.html.slim (Duration: 0.5ms | Allocations: 116)
FavoriteJob Exists? (1.1ms)  SELECT 1 AS one FROM `favorite_jobs` WHERE `favorite_jobs`.`user_id` = 2 AND `favorite_jobs`.`job_id` = 208 LIMIT 1
↳ app/models/user.rb:30:in `favorite?'
Rendered shared/_unfavorite.html.slim (Duration: 0.3ms | Allocations: 116)
FavoriteJob Exists? (0.8ms)  SELECT 1 AS one FROM `favorite_jobs` WHERE `favorite_jobs`.`user_id` = 2 AND `favorite_jobs`.`job_id` = 207 LIMIT 1
↳ app/models/user.rb:30:in `favorite?'
Rendered shared/_unfavorite.html.slim (Duration: 0.4ms | Allocations: 116)
FavoriteJob Exists? (1.2ms)  SELECT 1 AS one FROM `favorite_jobs` WHERE `favorite_jobs`.`user_id` = 2 AND `favorite_jobs`.`job_id` = 206 LIMIT 1
↳ app/models/user.rb:30:in `favorite?'
Rendered shared/_favorite.html.slim (Duration: 0.3ms | Allocations: 108)
FavoriteJob Exists? (1.1ms)  SELECT 1 AS one FROM `favorite_jobs` WHERE `favorite_jobs`.`user_id` = 2 AND `favorite_jobs`.`job_id` = 201 LIMIT 1
↳ app/models/user.rb:30:in `favorite?'
Rendered shared/_favorite.html.slim (Duration: 0.4ms | Allocations: 108)
FavoriteJob Exists? (0.8ms)  SELECT 1 AS one FROM `favorite_jobs` WHERE `favorite_jobs`.`user_id` = 2 AND `favorite_jobs`.`job_id` = 200 LIMIT 1
↳ app/models/user.rb:30:in `favorite?'
Rendered shared/_favorite.html.slim (Duration: 0.4ms | Allocations: 108)
FavoriteJob Exists? (0.7ms)  SELECT 1 AS one FROM `favorite_jobs` WHERE `favorite_jobs`.`user_id` = 2 AND `favorite_jobs`.`job_id` = 199 LIMIT 1
↳ app/models/user.rb:30:in `favorite?'
Rendered shared/_favorite.html.slim (Duration: 0.3ms | Allocations: 108)
FavoriteJob Exists? (0.8ms)  SELECT 1 AS one FROM `favorite_jobs` WHERE `favorite_jobs`.`user_id` = 2 AND `favorite_jobs`.`job_id` = 197 LIMIT 1
↳ app/models/user.rb:30:in `favorite?'
Rendered shared/_favorite.html.slim (Duration: 0.3ms | Allocations: 108)
FavoriteJob Exists? (1.2ms)  SELECT 1 AS one FROM `favorite_jobs` WHERE `favorite_jobs`.`user_id` = 2 AND `favorite_jobs`.`job_id` = 195 LIMIT 1
↳ app/models/user.rb:30:in `favorite?'
.......  

我尝试包括(:favorite_jobs)和一些东西，但它不工作请指导我修复工作索引中的n+1查询

Answer 1

另一种解决问题的方法是选择子查询:

class Job < ApplicationRecord
has_many :favorite_jobs
def self.with_favorite_status(user)
select(
"jobs.*",
FavoriteJob.select(1)
.where(user_id: user.id)
.where(FavoriteJob.arel_table[:job_id].eq(arel_table[:id]))
.arel
.exists
.as("favorited")
)
end
def favorited?
favorited
end
end

从表中选择favorited和其他列:

SELECT 
jobs.*, 
EXISTS (
SELECT 1 FROM "favorite_jobs" WHERE "favorite_jobs"."user_id" = ? AND "favorite_jobs"."job_id" = "jobs"."id"
) AS favorited 
FROM "jobs"

然后您可以在此基础上构建其他作用域:

class JobsController < ApplicationController
before_action :history, only: :show
def index
@jobs = if user_signed_in?
Job.with_favorite_status(current_user)
else
Job.all
end
# ...
end
def show
@job = Job.find(params[:id])
render partial: 'shared/job_not_found' if @job.nil?
end
end

在Postgres上，你可以通过使用横向连接而不是子查询来进一步优化。

这种方法的主要优点是它不会从favorite_jobs表中加载所有相关的行，这会减慢应用程序的速度，如果作业有很多收藏夹，甚至会使应用程序崩溃。

Answer 2

问题在于这个方法:

def favorite?(job)
favorite_jobs.exists?(job_id: job.id)
end

不是单独检查每个作业，而是将所有作业id加载到内存中一次，然后在以后的所有调用中检查给定id是否包含在该列表中:

def favorite?(job)
@favorite_job_ids ||= Set.new(favorite_jobs.pluck(:job_id))
@favorite_job_ids.include?(job.id)
end

注意，只有当所有喜欢的工作列表不是太大时，这才有意义。几百张可能还可以，几千张肯定太大了。

根据你的应用程序，你可能需要添加require 'set'到你的模型。

Answer 3

问题是，循环中的每次检查都会触发DB。我建议这样写:

#index.html.slim
#jobs index
.container
.container
- cu_job_ids = current_user.favorite_jobs.pluck(:job_id)
- @jobs.each do |job|
- if user_signed_in? && cu_job_ids.include?(job.id)
= render 'shared/unfavorite', job_id: job.id
- else
= render 'shared/favorite', job_id: job.id