如何优化Active Record查询？将5次查询合并为单次查询

当前状态 #

已能正确获取所有数据，功能正常运行。

优化目标 #

将现有的5次查询合并为单次查询以提升性能。

原代码 #

class FooService
  attr_reader :foo_format

  def initialize(foo_format)
    @foo_format = foo_format
  end

  def format_for(format)
    case format
    when foo::fooBarONE
      one
    when foo::fooBarTWO
      half
    when foo::fooBarThree
      third
    when foo::fooBarFour
      quarter
    when foo::fooBarFive
      format_1000er_format
    end
  end

  def one
    foo_format.where(form_key: 0, page_format: fooBarONE )
                      .order(format_price_query).first
  end

  def two
    foo_format.where(form_key: 3, page_format: fooBarTWO)
                      .order(format_price_query).first
  end

  def three
    foo_format.where(form_key: 3, page_format: fooBarThree)
                      .order(format_price_query).first
  end

  def four
    foo_format.where(form_key: 3, page_format: fooBarFour)
                      .order(format_price_query).first
  end

  def five
    foo_format.where(form_key: 7, page_format: fooBarFive)
                      .order(format_price_query).first
  end
end

模型关联 #

belongs_to :magazines_booking_unit,
           foreign_key: :booking_unit_id,
           class_name: 'Foo::Bar'

优化方案 #

核心思路是一次性查询所有符合条件的记录，在内存中完成分组和筛选，彻底避免多次数据库请求：

class FooService
  attr_reader :foo_format, :formatted_records

  def initialize(foo_format)
    @foo_format = foo_format
    preload_records
  end

  # 统一管理格式与查询条件的映射
  FORMAT_CONDITIONS = {
    foo::fooBarONE => { form_key: 0, page_format: fooBarONE },
    foo::fooBarTWO => { form_key: 3, page_format: fooBarTWO },
    foo::fooBarThree => { form_key: 3, page_format: fooBarThree },
    foo::fooBarFour => { form_key: 3, page_format: fooBarFour },
    foo::fooBarFive => { form_key: 7, page_format: fooBarFive }
  }.freeze

  def format_for(format)
    # 直接从预加载的分组数据中取对应格式的第一条记录
    formatted_records.dig(FORMAT_CONDITIONS[format][:page_format], 0)
  end

  private

  def preload_records
    # 提取所有需要匹配的form_key和page_format集合
    target_form_keys = FORMAT_CONDITIONS.values.map { |cond| cond[:form_key] }.uniq
    target_page_formats = FORMAT_CONDITIONS.values.map { |cond| cond[:page_format] }.uniq

    # 单次查询所有符合条件的记录，按原有规则排序
    all_records = foo_format.where(form_key: target_form_keys, page_format: target_page_formats)
                            .order(format_price_query)

    # 按page_format分组，后续直接内存取值
    @formatted_records = all_records.group_by(&:page_format)
  end
end

优化说明 #

1. 单次数据库查询：初始化时仅执行1次SQL查询，替代原有的最多5次查询，大幅降低数据库IO开销
2. 内存分组操作：通过group_by将查询结果按page_format分组，后续format_for方法仅需内存操作，无额外数据库请求
3. 可维护性提升：用常量FORMAT_CONDITIONS统一管理格式与查询条件的映射，后续新增或修改格式时只需调整该常量，无需修改多个方法

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