MongoDB聚合查询添加Sort阶段后性能劣化，如何通过索引优化？

我使用MongoDB聚合函数从onu_profile集合获取全部数据，原本查询运行正常，但添加$sort阶段后耗时大幅增加且导致代码超时。曾尝试为created_on字段创建索引，但无效果，请问需要为哪些字段创建索引以提升该查询性能？

onu_profile集合样本文档 #

{
  "_id": {
    "$oid": "5f056023324dc0369a9b9c60"
  },
  "serial": "UBNT29F59750\t",
  "profile_name": "",
  "hostname": "",
  "status": "inactive",
  "tcont": {},
  "gemport": {},
  "portvlan": {},
  "setting_details": [],
  "device_mac": null,
  "onu_authlist_gpon_port": null,
  "onu_authlist_profile_id": null,
  "created_on": {
    "$date": {
      "$numberLong": "1594211211178"
    }
  },
  "updated_on": {
    "$date": {
      "$numberLong": "1594211211178"
    }
  },
  "created_by": "ye.htun",
  "updated_by": "ye.htun"
}

dbm_status集合样本文档 #

{
  "_id": {
    "$oid": "63b4493585b12c2da3c7e50a"
  },
  "hostname": "122X01ZMTY-B03-P16-SCP004779",
  "device_type": "ONU",
  "last_seen": "2023-07-18T14:00:29.220006582+06:30",
  "status": 1,
  "gpon_name": "CA1-MYEX08ZMTY-B",
  "last_checked_on": "2023-07-18T14:00:29.210486574+06:30",
  "last_seen_on": "2023-07-18T14:00:29.220006582+06:30",
  "olt_hostname": "OVF-123X01ZMTY",
  "olt_ip": "172.28.22.224",
  "onu_profile_id": "75",
  "phase_state": "Working",
  "pon_port": "2",
  "serial": "SCSOACA0A488",
  "signal": "-22.520"
}

执行的MongoDB查询语句 #

db.onu_profile.aggregate([
    {
        $match: {
            $or:[{'serial': {'$regex': '(?i)(.*)(.*)'}}, {'hostname': {'$regex': '(?i)(.*)(.*)'}}, {'device_mac': {'$regex': '(?i)(.*)(.*)'}}]
        }
    },
    {
        $lookup: {
            from: "dbm_status",
            localField: "serial",
            foreignField: "serial",
            as: "dbm_status"
        }
    },
    {
        $addFields:{
            dbm_status : { "$arrayElemAt" : ["$dbm_status", 0]}
        },
        $sort:{created_on:-1}
    }
])

优化方案与索引建议 #

1. 调整聚合阶段顺序，让$sort利用索引 #

你当前的$sort放在$lookup之后，此时MongoDB无法使用created_on字段的索引——因为lookup后的文档是合并了dbm_status数据的新结构，不是原onu_profile集合的文档。

把$sort移到$match之后、$lookup之前，这样MongoDB可以直接利用created_on的索引对原集合文档排序，再执行关联查询，避免对大量已关联的数据排序：

db.onu_profile.aggregate([
    {
        $match: {
            $or:[{'serial': {'$regex': '(?i)(.*)(.*)'}}, {'hostname': {'$regex': '(?i)(.*)(.*)'}}, {'device_mac': {'$regex': '(?i)(.*)(.*)'}}]
        }
    },
    { $sort:{created_on:-1} }, // 移到lookup之前
    {
        $lookup: {
            from: "dbm_status",
            localField: "serial",
            foreignField: "serial",
            as: "dbm_status"
        }
    },
    {
        $addFields:{
            dbm_status : { "$arrayElemAt" : ["$dbm_status", 0]}
        }
    }
])

此时你之前创建的created_on: -1索引就能生效，大幅降低排序耗时。

2. 优化$match条件与对应索引 #

你的$match条件里的正则(?i)(.*)(.*)完全是匹配所有文档，相当于可以直接去掉这个$match阶段。如果是实际业务中需要模糊搜索某个关键词，建议：

• 用文本索引替代正则查询，性能提升明显：

  db.onu_profile.createIndex({serial: "text", hostname: "text", device_mac: "text"})
  

  然后修改$match为文本查询：

  $match: { $text: { $search: "你的搜索关键词" } }
  

• 如果必须用正则，且是前缀匹配（比如/^keyword/i），可以给serial、hostname、device_mac分别建不区分大小写的单字段索引（MongoDB 4.2+支持）：

  db.onu_profile.createIndex({serial: 1}, {collation: {locale: "en", strength: 2}})
  db.onu_profile.createIndex({hostname: 1}, {collation: {locale: "en", strength: 2}})
  db.onu_profile.createIndex({device_mac: 1}, {collation: {locale: "en", strength: 2}})
  

  查询时指定相同的collation：

  db.onu_profile.aggregate([...]).collation({locale: "en", strength: 2})
  

3. 优化$lookup的关联索引 #

给dbm_status集合的serial字段建单字段索引，加速关联查询：

db.dbm_status.createIndex({serial: 1})

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