使用$ne过滤非空数组的MongoDB关联查询性能优化问题

我是MongoDB新手，在聚合管道的$match阶段使用$ne过滤非空数组时遇到查询极慢的问题：移除{ $match: { part_number_info: { $ne: [] } } }条件时查询仅需21秒，加上该条件则耗时近2小时。目前已为EN集合的ICECAT-interface.Product.@Prod_id字段创建索引。

原查询代码 #

db.EN.aggregate([
 {
     $lookup: {
  from: 'csv_import',
  let: {pn:'$ICECAT-interface.Product.@Prod_id'},
  pipeline: [{
      $match: {
          $expr: {
              $eq: ["$$pn","$part_no"]
          }
      }
  }],
  as: 'part_number_info'
     }
 }, { $match: { part_number_info: { $ne: [] } } }
 ]).pretty();

集合结构示例 #

csv_import集合 #

{
  "_id": "ObjectId(\"6348339cc6e5c8ce0b7da5a4\")",
  "index": 23679,
  "product_id": 4019734,
  "part_no": "CP-HAR-EP-ADVANCED-REN-1Y",
  "vendor_standard": "Check Point"
}

EN集合 #

[{
  "_id": "1414",
  "ICECAT-interface": {
    "@xmlns:xsi": "http://www.w3.org/2001/XMLSchema-instance",
    "@xsi:noNamespaceSchemaLocation": "https://data.icecat.biz/xsd/ICECAT-interface_response.xsd",
    "Product": {
      "@Code": "1",
      "@HighPic": "https://images.icecat.biz/img/norm/high/1414-HP.jpg",
      "@HighPicHeight": "400",
      "@HighPicSize": "43288",
      "@HighPicWidth": "400",
      "@ID": "1414",
      "@LowPic": "https://images.icecat.biz/img/norm/low/1414-HP.jpg",
      "@LowPicHeight": "200",
      "@LowPicSize": "17390",
      "@LowPicWidth": "200",
      "@Name": "C6614NE",
      "@IntName": "C6614NE",
      "@LocalName": "",
      "@Pic500x500": "https://images.icecat.biz/img/gallery_mediums/img_1414_medium_1480667779_072_2323.jpg",
      "@Pic500x500Height": "500",
      "@Pic500x500Size": "101045",
      "@Pic500x500Width": "500",
      "@Prod_id": "C6614NE"
    }
  }
}]

优化解决方案 #

通过两个关键调整解决性能问题：

• 为csv_import集合的part_no字段创建索引
• 调整聚合关联顺序：从大集合（EN，27GB）关联小集合（csv_import，仅数MB）改为从小集合关联大集合，减少初始处理的数据量
• 用{"part_number_info.0": {$exists: true}}替代$ne: []过滤非空数组，提升匹配效率

优化后查询代码 #

db.csv_import.aggregate([
 {
     $lookup: {
  from: 'EN',
  let: {pn:'$part_no'},
  pipeline: [{
      $match: {
          $expr: {
              $eq: ["$$pn","$ICECAT-interface.Product.@Prod_id"]
          }
      }
  }],
  as: 'part_number_info'
     }
 },{$match: {"part_number_info.0": {$exists: true}}}
 ])

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