ruby-on-rails - 在 Rails 中使用插入查询遍历大数据的最佳方法是什么

Question

我必须插入大数据，比方说 20k 我怀疑我是否编写了优化查询。

它的作用:

1. 使用满足某些条件的 sql 获取用户的记录，它在 merge_user_records 的事件记录数组中获取超过 1k-20k 的用户
2. 从 1k-20k 用户的事件记录数组中批量切片 100 个用户
3. 遍历 merge_user 记录并从 merge_user_records 中的用户模型用户 user_id 中查找用户
4. 仍在循环中，用户调用方法 construct_user_notifications 为每个用户插入 user_notifications。
5. 仍在循环中查找用户的设备。
6. 在设备中运行循环以在每台设备上发送推送通知。
7. 循环结束

这是代码。

merge_users = MergeField.get_user_field_values(notification_template.merge_field, scope_users) #=> **returns users 1k - 20k**
if merge_users.present?
  merge_users.each_slice(100) do |record|
    record.each do |user_record|
      user = User.find_by(id: user_record.user_id)
      text = notification_template.title
      notification_template.description = MustacheDescription.render(notification_template, user_record)
      text += " " + notification_template.description
      Rails.logger.info "Merge field message: #{notification_template.description}"
      construct_user_notifications(notification_template, user_record.user_id) #=> **this calls another method below which actually create notifications for every user.**
      badge = (notification_template.display_screen == "suggestion") ? user.unread_suggestion_notifications.count : user.unread_option_notifications.count
      devices = user.devices.with_notification_token
      if devices.present?
        devices.each do |device|
          PushNotification.notify_ios(text, device.notification_token, badge, {screen: notification_template.display_screen})
          Rails.logger.info "Sending push to user_id #{user_record.user_id} token #{device.notification_token}"
        end
      end
    end
  end
end

def self.construct_user_notifications(notification_template, user_id)
  notification_template.user_notifications.build.tap do |user_notification|
    user_notification.title = notification_template.title
    user_notification.subtitle = notification_template.subtitle
    user_notification.description = notification_template.description
    user_notification.merge_field = notification_template.merge_field
    user_notification.cta = notification_template.cta
    user_notification.cta_key = notification_template.cta_key
    user_notification.secondary_cta = notification_template.secondary_cta
    user_notification.secondary_cta_key = notification_template.secondary_cta_key
    user_notification.show_useful = notification_template.show_useful
    user_notification.category = notification_template.category
    user_notification.display_screen = notification_template.display_screen
    user_notification.sent_at = Time.current
    user_notification.user_id = user_id
    user_notification.filter_preferences = notification_template.filter_preferences
    user_notification.save
  end
end

我已经为 100 位用户测试了这个，它需要 30-40 秒。天知道生产 2 万用户需要多少成本。

Answer 1

我建议将循环的内部内容包装在一个事务 block 中，该事务 block 将在最后一次运行所有查询，而不是零碎的。这会将每个用户的所有查询分组到一个事务中同时运行:

merge_users.each_slice(100) do |record|
  ActiveRecord::Base.transaction do
  // code
  end

  if devices.present?
    devices.each do |device|
      PushNotification.notify_ios(text,device.notification_token,badge,{screen: notification_template.display_screen})
      Rails.logger.info  "Sending push to user_id #{user_record.user_id} token #{device.notification_token}"
    end
  end
end

您可以在此处找到有关交易的更多信息:

http://api.rubyonrails.org/classes/ActiveRecord/Transactions/ClassMethods.html

最后，我建议您不要直接在 block 中执行 PushNotification.notifify_ios，而应使用 DelayedJob 或类似工具在后台运行作业。这意味着所有方法调用都将在代码本身运行后在后台处理，而不是在循环本身内处理。

那看起来像:

if devices.present?
    devices.each do |device|
      PushNotification.delay.notify_ios(text,device.notification_token,badge,{screen: notification_template.display_screen})
      Rails.logger.info  "Sending push to user_id #{user_record.user_id} token #{device.notification_token}"
    end
  end

https://github.com/collectiveidea/delayed_job