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

我必须插入大数据，比如说20k，我怀疑我写了一个优化的查询。
它的作用：
使用满足某些条件的sql获取用户记录它在merge用户记录的活动记录数组中获取超过1k-20k个用户
从1k-20k个用户的activerecord数组中批量切片100个用户
循环浏览合并用户记录并在合并用户记录中从用户模型用户用户id中查找用户
仍在循环中的用户调用方法构造用户通知来为每个用户插入用户通知。
仍在循环中查找用户的设备。
在设备中运行循环以在每个设备上发送推送通知。
循环结束
这是密码。

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秒。天知道在prod中2万用户要花多少钱。

最佳答案

一find_by在循环中
user = User.find_by(id: user.user_id)将查询20000次我们可以通过将此置于each循环之外来避免：

merge_users.each_slice(100) do |users|
  users = User.where(id: users.map(&:user_id))
  users.each do |user|
    # loop
  end
end

2记录器级别
将info更改为debug磁盘IO总是很慢。
三。construct_user_notifications功能
notification_template.user_notifications.build将分配20000个对象GC也是个问题。
请只生成属性，稍后再保存。
例如：

def self.construct_user_notifications(notification_template, user_id)
  {
      title: notification_template.title,
      subtitle: notification_template.subtitle,
      description: notification_template.description,
      merge_field: notification_template.merge_field,
      cta: notification_template.cta,
      cta_key: notification_template.cta_key,
      secondary_cta: notification_template.secondary_cta,
      secondary_cta_key: notification_template.secondary_cta_key,
      show_useful: notification_template.show_useful,
      category: notification_template.category,
      display_screen: notification_template.display_screen,
      sent_at: Time.current,
      user_id: user_id,
      filter_preferences: notification_template.filter_preferences,
      # more attributes
  }
end

四badge查询
badge = (notification_template.display_screen == "suggestion") ? user.unread_suggestion_notifications.count : user.unread_option_notifications.count
除非存在devices，否则这是不必要的。
您可以稍后查询徽章。
5个推送通知
PushNotification.notify_ios(text, device.notification_token, badge, {screen: notification_template.display_screen})
这可能有一些http请求，这相当慢。
您应该使用sidekiq或resque在后台工作中执行此操作。
6.保存user_notifications
看看activerecord-import宝石批插入更有效。
例子

merge_users = MergeField.get_user_field_values(notification_template.merge_field, scope_users) #=> **returns users 1k - 20k**

merge_users.each_slice(500) do |users|
  users = User.where(id: users.map(&:user_id))
  user_notifications = Set.new

  users.each do |user|
    text = notification_template.title
    notification_template.description = MustacheDescription.render(notification_template, user)
    text += " " + notification_template.description
    Rails.logger.debug "Merge field message: #{notification_template.description}"

    user_notifications.add construct_user_notifications(notification_template, user.user_id)

    # do this asynchronously
    push_notification(notification_template, user_id)
  end

  UserNotification.import(user_notifications.first.keys, user_notifications.to_a)
end

def self.push_notification(notification_template, user_id)
  devices = Device.where(user_id: user_id).with_notification_token.pluck(:notification_token)
  if devices.present?
    badge = (notification_template.display_screen == "suggestion") ? UnreadSuggestionNotification.where(user_id: user_id).count : UnreadOptionNotification.where(user_id: user_id).count
    devices.each do |device|
      PushNotification.notify_ios(text, device.notification_token, badge, {screen: notification_template.display_screen})
      Rails.logger.debug "Sending push to user_id #{user_id} token #{device.notification_token}"
    end
  end
end


def self.construct_user_notifications(notification_template, user_id)
  {
      title: notification_template.title,
      subtitle: notification_template.subtitle,
      description: notification_template.description,
      merge_field: notification_template.merge_field,
      cta: notification_template.cta,
      cta_key: notification_template.cta_key,
      secondary_cta: notification_template.secondary_cta,
      secondary_cta_key: notification_template.secondary_cta_key,
      show_useful: notification_template.show_useful,
      category: notification_template.category,
      display_screen: notification_template.display_screen,
      sent_at: Time.current,
      user_id: user_id,
      filter_preferences: notification_template.filter_preferences,
      # more attributes
  }
end