在Rails/Postgres中批量更新记录以减少数据库UPDATE调用的方法？

你当前逐行调用update的方式会产生N次数据库请求，1万条数据的场景可以通过以下方案优化到仅需1次数据库请求，性能提升非常明显：

方案1：构造CASE语句批量更新（全Rails版本兼容） #

该方案无需编写原生SQL，适配所有常见数据库，1万条数据的场景完全够用：

collection = Collection.find(collection_id)
# 仅拉取排序后的资产ID，不加载全量ActiveRecord对象，内存占用极低
ordered_asset_ids = collection.assets.order(elo_rating: :desc).pluck(:id)
# 批量生成CASE匹配逻辑
when_clause = ordered_asset_ids.each_with_index.map do |asset_id, index|
  "WHEN id = #{asset_id} THEN #{index + 1}"
end.join(' ')
# 单次调用完成全量更新
collection.assets.update_all("rank = CASE #{when_clause} END")

方案2：数据库窗口函数更新（性能最优，推荐） #

直接在数据库层完成rank计算，不需要把数据拉到Ruby层处理，哪怕十万级以上的数据量也能快速处理：

PostgreSQL 版本 #

collection = Collection.find(collection_id)
Asset.connection.execute(<<~SQL.squish)
  UPDATE assets
  SET rank = ranked_asset.rank
  FROM (
    SELECT id, ROW_NUMBER() OVER (ORDER BY elo_rating DESC) AS rank
    FROM assets
    WHERE collection_id = #{collection.id}
  ) ranked_asset
  WHERE assets.id = ranked_asset.id
SQL

MySQL 版本 #

collection = Collection.find(collection_id)
Asset.connection.execute(<<~SQL.squish)
  UPDATE assets a
  JOIN (
    SELECT id, ROW_NUMBER() OVER (ORDER BY elo_rating DESC) AS rank
    FROM assets
    WHERE collection_id = #{collection.id}
  ) ranked_asset ON a.id = ranked_asset.id
  SET a.rank = ranked_asset.rank
SQL

注意事项 #

• 建议将更新操作包裹在数据库事务中，避免中途出错导致部分数据rank异常
• 如果存在elo_rating相同的记录，可以在排序规则中增加第二个排序字段（比如ORDER BY elo_rating DESC, id ASC），保证rank分配的唯一性和确定性
• 以上方案均不需要实例化全量Asset对象，内存占用和执行效率都远高于原始的逐行更新方案

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