Ruby on Rails 按日期范围统计记录数的性能优化方案咨询

核心性能瓶颈 #

现有代码的性能瓶颈来自循环产生的大量重复SQL查询：假设按1小时间隔统计1个月的数据，就会产生720次独立SQL查询，每次查询都需要扫描表、关联父表，重复开销被放大，数据量较大时必然触发超时。

最优重构方案 #

1. 替换循环查询为2次分组聚合查询 #

核心思路是先限定总时间范围，直接在数据库层完成分组统计，仅需2次查询即可拿到所有区间的计数结果，再遍历时间区间补全空值即可，代码示例（以MySQL为例，PostgreSQL仅需替换时间戳提取函数为EXTRACT(EPOCH FROM 字段名)）：

# 1. 单次查询统计所有区间的created_at计数
created_counts = recs.actions_dated('created_at', period_start, period_end)
  .group("FLOOR(UNIX_TIMESTAMP(created_at) / #{period_offset.to_i}) * #{period_offset.to_i}")
  .count
# 把时间戳键转为Time类型
created_counts.transform_keys! { |ts| Time.at(ts) }

# 2. 单次查询统计所有区间的confirmed_at计数
confirmed_counts = recs.confirmed.actions_dated('confirmed_at', period_start, period_end)
  .group("FLOOR(UNIX_TIMESTAMP(confirmed_at) / #{period_offset.to_i}) * #{period_offset.to_i}")
  .count
confirmed_counts.transform_keys! { |ts| Time.at(ts) }

# 3. 遍历时间区间生成最终结果数组，无数据的区间补0
graph_data_created = []
graph_data_confirmed = []
temp_period_start = period_start
while temp_period_start < period_end
  graph_data_created << created_counts.fetch(temp_period_start, 0)
  graph_data_confirmed << confirmed_counts.fetch(temp_period_start, 0)
  temp_period_start += period_offset
end

2. 新增必要索引加速查询 #

没有对应索引的情况下每次查询都会走全表扫描，需新增以下索引，可把单查询速度提升10~100倍：

# 数据库迁移代码
class AddChartQueryIndexes < ActiveRecord::Migration[6.1]
  def change
    add_index :recs, :created_at
    add_index :recs, :confirmed_at
    add_index :recs, :rec_parent_id # 优化joins关联速度，未加过该索引时需要加
    add_index :rec_parents, :start_flag
  end
end

3. 高阶优化（可选，适合百万级以上数据量） #

给recs表新增冗余布尔字段is_confirmed，在rec保存或关联的rec_parent更新时自动同步该字段的值，即可去掉confirmed scope中的joins关联，进一步降低查询开销。

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