如何优化MongoDB聚合查询？已建索引但扫描返回比过高

问题背景 #

已为requestedAtTimestamp_-1建立索引，但该MongoDB聚合查询耗时超5秒，Mongo Atlas提示：The ratio of documents scanned to returned exceeded 1000.0。询问是否可添加额外索引加速查询。

聚合查询语句 #

[
  {
    "$match": {
      "requestedAtTimestamp": {
        "$gte": 1730419200000
      }
    }
  },
  {
    "$sort": {
      "requestedAtTimestamp": 1
    }
  },
  {
    "$addFields": {
      "requestedAt": {
        "$toDate": "$requestedAtTimestamp"
      }
    }
  },
  {
    "$project": {
      "user": 1,
      "requestedAt": 1,
      "y": {
        "$year": "$requestedAt"
      },
      "m": {
        "$month": "$requestedAt"
      },
      "d": {
        "$dayOfMonth": "$requestedAt"
      },
      "h": {
        "$hour": "$requestedAt"
      },
      "x1": 1,
      "x2": 1
    }
  },
  {
    "$group": {
      "_id": {
        "user": "$user",
        "y": "$y",
        "m": "$m",
        "d": "$d",
        "h": "$h"
      },
      "x1": {
        "$sum": {
          "$cond": [
            "$x1",
            1,
            0
          ]
        }
      },
      "x2": {
        "$sum": {
          "$cond": [
            "$x2",
            1,
            0
          ]
        }
      },
      "total": {
        "$sum": 1
      }
    }
  }
]

执行统计信息 #

Operation Execution Time   8.29 s
Examined:Returned Ratio    6,169.51
Keys Examined              623,121
Docs Returned              101
Docs Examined              623,121
Num Yields                 693
Response Length            16,593

执行计划 #

{
  "planSummary": "IXSCAN { requestedAtTimestamp: -1 }",
  "planningTimeMicros": 1520,
  "cursorid": 7596442613226102117,
  "keysExamined": 623121,
  "docsExamined": 623121,
  "numYields": 693,
  "nreturned": 101,
  "queryHash": "08BAB40F",
  "planCacheKey": "AEE30401",
  "queryFramework": "classic",
  "reslen": 16593,
  "locks": {
    "FeatureCompatibilityVersion": {
      "acquireCount": {
        "r": 733
      }
    },
    "Global": {
      "acquireCount": {
        "r": 733
      }
    }
  },
  "readConcern": {
    "level": "local",
    "provenance": "implicitDefault"
  },
  "writeConcern": {
    "w": "majority",
    "wtimeout": 0,
    "provenance": "implicitDefault"
  },
  "storage": {
    "data": {
      "bytesRead": 2950570755,
      "timeReadingMicros": 3032242
    },
    "timeWaitingMicros": {
      "cache": 2352
    }
  },
  "cpuNanos": 5626765637,
  "remote": "3.238.26.30:53820",
  "protocol": "op_msg",
  "durationMillis": 8292,
  "v": "7.0.15",
  "isTruncated": false
}

文档结构 #

{
  "_id": {
    "$oid": "673601b421ea2eec217ce968"
  },
  "__v": {
    "$numberInt": "0"
  },
  "lastUpdatedTimestamp": {
    "$numberDouble": "1731592626011.0"
  },
  "requestedAtTimestamp": {
    "$numberDouble": "1731592624752.0"
  },
  "x1": "sadasdasdadasdasdasdasdasdasdasdasdasdasdasdsad",
  "x2": "0x7605ffddabf72cb9fa7ebba35c9c252d5cb4064cc04a314fe34ad2eea4a19c2bff90df2e1b6228ccd136a56b49c1d4e7dde039e94ccf676bbfa00e0ea216e681"
}

优化建议 #

1. 创建覆盖索引（核心优化） #

当前索引仅包含requestedAtTimestamp，MongoDB需要回表读取所有匹配文档的user、x1、x2字段，这是耗时的主要原因。创建覆盖索引可以直接从索引获取所需数据，避免全表扫描回表：

db.collection.createIndex(
  { requestedAtTimestamp: 1 }, // 与查询中的$sort方向一致，避免额外排序开销
  {
    projection: { user: 1, x1: 1, x2: 1 }, // 包含聚合所需的所有字段
    partialFilterExpression: { requestedAtTimestamp: { $gte: 0 } } // 可选：缩小索引范围，仅包含有效时间戳文档
  }
)

注意：索引方向使用1与$sort的requestedAtTimestamp:1一致，MongoDB可直接利用索引排序，省去内存排序步骤。

2. 简化聚合流程 #

去掉冗余的$addFields阶段，直接在$project中转换时间戳，减少计算步骤：
修改后的$project阶段：

{
  "$project": {
    "user": 1,
    "y": { "$year": { "$toDate": "$requestedAtTimestamp" } },
    "m": { "$month": { "$toDate": "$requestedAtTimestamp" } },
    "d": { "$dayOfMonth": { "$toDate": "$requestedAtTimestamp" } },
    "h": { "$hour": { "$toDate": "$requestedAtTimestamp" } },
    "x1": 1,
    "x2": 1
  }
}

这样可以省略$addFields阶段，减少数据处理量。

3. 验证索引效果 #

创建索引后，重新执行查询并查看执行计划，确认docsExamined是否大幅降低，planSummary是否显示使用新索引且无需回表。

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