基于has_many through关联的单用户最高得分排行榜查询优化方案

你的核心需求是：从关联多用户的HighScore记录中生成排行榜，要求每条入选记录的所有用户都未在之前的记录中出现过，优先选择高分记录，取前10条。原方案先加载500条记录到内存再遍历去重，性能瓶颈明显，下面用Postgres的递归CTE实现纯数据库层面的高效筛选。

原代码的性能问题 #

• 一次性加载500条HighScore及关联的high_score_users，内存开销大
• Ruby层遍历检查用户交集的逻辑是O(n²)复杂度，随着已选记录增多，每次检查越来越慢
• 最后额外查询一次数据库获取关联用户，冗余且低效

优化方案：用递归CTE实现数据库端筛选 #

递归CTE可以逐步筛选符合条件的记录：每次选当前最高分且不包含已选用户的记录，直到凑够10条或无更多记录。所有逻辑在数据库内完成，避免大量数据加载和Ruby层的低效计算。

完整优化代码 #

def self.unique_leaderboard(map_name, score_type, game_mode, game_type)
  Rails.cache.fetch("leaderboards_unique/#{map_name}/#{score_type}/#{game_type}/#{game_mode}", expires_in: 10.minutes) do
    # 构建基础筛选查询，复用你已有的scope
    base_query = HighScore
    base_query = base_query.for_map(map_name) if map_name
    base_query = base_query.for_type(score_type) if score_type
    base_query = base_query.for_gametype(game_type) if game_type
    base_query = base_query.for_gamemode(game_mode) if game_mode
    base_query = base_query.current_season
    base_query = base_query.ranked(game_type) # 确保这个scope是按得分降序排序

    # 递归CTE查询，获取前10条符合条件的HighScore ID
    recursive_sql = <<~SQL
      WITH RECURSIVE leaderboard AS (
        -- 初始步骤：取第一条最高分记录，收集其关联的所有用户ID
        SELECT
          hs.id,
          ARRAY(SELECT hsu.user_id FROM high_score_users hsu WHERE hsu.high_score_id = hs.id) AS user_ids,
          1 AS rank
        FROM high_scores hs
        WHERE hs.id = (SELECT id FROM (#{base_query.to_sql}) AS filtered ORDER BY score DESC LIMIT 1)
        
        UNION ALL
        
        -- 递归步骤：从剩余记录中选下一条最高分，排除已出现过的用户
        SELECT
          hs.id,
          ARRAY(SELECT hsu.user_id FROM high_score_users hsu WHERE hsu.high_score_id = hs.id) AS user_ids,
          lb.rank + 1 AS rank
        FROM leaderboard lb
        JOIN high_scores hs ON hs.id IN (
          SELECT id FROM (#{base_query.to_sql}) AS filtered
          WHERE NOT EXISTS (
            SELECT 1 FROM high_score_users hsu
            WHERE hsu.high_score_id = filtered.id
            AND hsu.user_id = ANY(lb.user_ids)
          )
          ORDER BY score DESC LIMIT 1
        )
        WHERE lb.rank < 10 -- 取够10条就停止
      )
      SELECT id FROM leaderboard ORDER BY rank;
    SQL

    # 执行查询获取ID列表
    top10_ids = HighScore.connection.select_values(recursive_sql)

    # 加载完整记录及关联用户，保持排序
    HighScore.where(id: top10_ids).includes(:users).ranked(game_type)
  end
end

方案优势 #

1. 性能提升：所有筛选逻辑在数据库端完成，避免加载大量数据到内存，递归CTE由Postgres优化，效率远高于Ruby层遍历
2. 结果准确：不会像原方案那样受限于前500条记录，能遍历所有符合条件的记录直到选够10条
3. 减少查询次数：仅需两次数据库查询（取ID、取完整记录），比原方案更高效

额外优化建议 #

• 给high_score_users表的user_id和high_score_id字段添加联合索引，加速用户关联检查：

  # 在high_score_users的迁移文件中添加
  add_index :high_score_users, [:user_id, :high_score_id]
  add_index :high_score_users, [:high_score_id, :user_id]
  

• 确保ranked(game_type) scope明确包含order(score: :desc)，否则要在base_query末尾加上该排序
• 处理无符合条件记录的情况：递归CTE会返回空数组，最终结果为空集合，符合预期

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