Rails批量更新性能优化：800条数据更新耗时2分钟如何改进？

嘿，针对你遇到的800条条目更新耗时2分钟的问题，咱们从减少查询次数和降低IO开销两个核心点来优化，原代码的主要问题是N+1查询和逐行更新的低效操作，下面给你具体的解决方案：

一、先分析原代码的性能瓶颈 #

• N+1查询问题：先查询所有Entry（1次查询），然后循环每个Entry再查询对应的Selection（800次查询），总共801次数据库请求，这是性能慢的主要原因之一。
• 逐行更新开销大：循环里逐个更新Selection的sort字段，会产生数百条单独的UPDATE语句，数据库IO成本极高。

二、最优解决方案：用数据库窗口函数直接处理（推荐） #

如果你的数据库支持窗口函数（比如PostgreSQL 9.4+、MySQL 8.0+），直接在数据库层面完成排序和更新，不需要把数据拉到Ruby层处理，速度会提升一个量级。

执行SQL批量更新 #

在Rails里可以直接执行原生SQL：

ActiveRecord::Base.connection.execute(<<-SQL)
  UPDATE selections s
  SET sort = sub.sort_rank
  FROM (
    SELECT 
      s.id,
      ROW_NUMBER() OVER (PARTITION BY s.entry_id ORDER BY g.score ASC) AS sort_rank
    FROM selections s
    JOIN golfers g ON s.golfer_id = g.id
  ) sub
  WHERE s.id = sub.id;
SQL

这个SQL的逻辑是：

1. 子查询通过PARTITION BY s.entry_id按entry_id分组，每组内按golfers.score升序生成排序号sort_rank
2. 直接把排序号更新到selections表的sort字段

三、兼容旧数据库的优化方案 #

如果你的数据库版本较低不支持窗口函数，就用预加载关联+批量更新的方式来优化：

1. 一次性预加载所有需要的数据，避免N+1 #

# 一次性获取所有Selection并按entry_id分组，同时预加载关联的golfer，避免额外查询
sorted_selections_by_entry = Selection.joins(:golfer)
                                      .order('golfers.score ASC')
                                      .group_by(&:entry_id)

2. 批量生成更新数据，减少IO操作 #

方式1：使用activerecord-import gem批量更新

先在Gemfile添加：

gem 'activerecord-import'

然后执行批量更新：

update_batch = []
sorted_selections_by_entry.each do |_entry_id, selections|
  selections.each_with_index do |selection, idx|
    update_batch << { id: selection.id, sort: idx + 1 }
  end
end

# 批量更新，重复键时更新sort字段
Selection.import update_batch, on_duplicate_key_update: [:sort]

方式2：原生Rails批量更新（无需额外gem）

如果不想加gem，可以分批次生成CASE语句来批量更新：

update_batch = []
sorted_selections_by_entry.each do |_entry_id, selections|
  selections.each_with_index do |selection, idx|
    update_batch << { id: selection.id, sort: idx + 1 }
  end
end

# 每100条为一批次更新，避免单条SQL过长
update_batch.each_slice(100) do |batch|
  case_clauses = batch.map { |attrs| "WHEN id = #{attrs[:id]} THEN #{attrs[:sort]}" }.join(' ')
  ids = batch.map { |attrs| attrs[:id] }.join(',')
  Selection.update_all("sort = CASE #{case_clauses} END WHERE id IN (#{ids})")
end

四、额外优化：添加必要索引 #

确保以下字段有索引，能大幅提升查询和排序的速度：

• selections.entry_id：加速按entry分组的查询
• selections.golfer_id：加速关联golfer的查询
• golfers.score：加速按score排序的操作

在迁移文件里添加索引：

add_index :selections, :entry_id
add_index :selections, :golfer_id
add_index :golfers, :score

内容的提问来源于stack exchange，提问作者Jeff Wellings