SQL转MongoDB：单文档排名计算高效实现方案求助

问题背景 #

需要将一段计算排名的SQL查询转换为MongoDB实现，原SQL用于统计比指定基准条目排名更靠前的条目数量，最终得到基准条目的排名。

基准参数如下：

base_user_id = 1
base_primary = 123
base_secondary = 456789
base_update_ms = 9999999

原SQL代码：

SELECT @rank:=@rank+1 AS rank FROM scores, ( SELECT @rank:=0 ) r WHERE (
        ( primary > base_primary ) OR
        ( primary = base_primary AND secondary > base_secondary ) OR
        ( primary = base_primary AND secondary = base_secondary AND last_update_ms <= base_update_ms )
) AND user_id != base_user_id ORDER BY rank DESC LIMIT 1

已通过MongoDB Java驱动实现排序逻辑，可支持分页等分组查询：

Sorts.orderBy(
    Sorts.descending("primary", "secondary"),
    Sorts.ascending("last_update_ms")
)

当前遇到的问题：使用非聚合方式计算单文档排名时，在200万条数据场景下性能较差，现寻求MongoDB聚合方案的实现思路。

补充的非聚合实现代码：

Bson betterSameSecondaryFilter = Filters.or(
        Filters.lt("last_update", d.lastUpdate()), // 更新时间越早排名越靠前
        Filters.and(Filters.eq("last_update"), // 更新时间相同（概率极低）
                Filters.lt("_id", d._id())) // 文档ID越小排名越靠前
);

Bson betterSecondaryFilter = Filters.or( // 仅在primary值相等时触发
        Filters.gt("secondary", d.secondary()), // secondary值更高，排名更靠前
        Filters.and(Filters.eq("secondary", d.secondary()), // secondary值相等时，比较更新时间
                betterSameSecondaryFilter)
);

Bson betterRankFilter = Filters.or(
        Filters.gt("primary", d.primary()), // primary值更高，排名更靠前
        Filters.and(Filters.eq("primary", d.primary()), // primary值相等时，比较secondary值
                betterSecondaryFilter)
);

long rank = 1 + hiscores.countDocuments(Filters.and(
        Filters.eq("key", d.key()),
        Filters.ne("user_id", userDoc.id()),
        betterRankFilter
));

聚合方案实现思路 #

核心逻辑 #

利用MongoDB聚合管道的过滤、统计能力，结合索引优化，替代全表扫描式的计数操作，提升大数据集下的性能。核心思路和非聚合实现一致：统计所有比基准条目排名靠前的文档数量，基准条目的排名即为数量+1。

具体步骤 #

1. 前置过滤
   用$match阶段先筛选出符合条件的文档：

   • 匹配基准条目的key
   • 排除当前user_id
   • 保留排名更靠前的文档（复用之前定义的betterRankFilter逻辑）

2. 统计数量
   通过$group阶段直接统计过滤后文档的总数，再通过$project计算最终排名（总数+1）。

3. 索引优化
   创建复合索引{key:1, primary:-1, secondary:-1, last_update_ms:1, _id:1}，让$match阶段直接命中索引，避免全表扫描。

示例聚合管道（MongoDB Shell语法） #

db.scores.aggregate([
  // 过滤符合条件的文档
  {
    $match: {
      key: "基准的key值",
      user_id: { $ne: 1 },
      $or: [
        { primary: { $gt: 123 } },
        {
          $and: [
            { primary: 123 },
            { secondary: { $gt: 456789 } }
          ]
        },
        {
          $and: [
            { primary: 123 },
            { secondary: 456789 },
            { last_update_ms: { $lte: 9999999 } }
          ]
        }
      ]
    }
  },
  // 统计总数
  {
    $group: {
      _id: null,
      count: { $sum: 1 }
    }
  },
  // 计算基准条目排名
  {
    $project: {
      _id: 0,
      rank: { $add: ["$count", 1] }
    }
  }
])

Java驱动适配代码 #

List<Bson> pipeline = Arrays.asList(
        Aggregates.match(Filters.and(
                Filters.eq("key", d.key()),
                Filters.ne("user_id", userDoc.id()),
                betterRankFilter // 复用已定义的过滤条件
        )),
        Aggregates.group(null, Accumulators.sum("count", 1L)),
        Aggregates.project(Projections.fields(
                Projections.excludeId(),
                Projections.computed("rank", new Document("$add", Arrays.asList("$count", 1)))
        ))
);

AggregateIterable<Document> result = hiscores.aggregate(pipeline);
Document rankDoc = result.first();
long rank = rankDoc != null ? rankDoc.getLong("rank") : 1;

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