MongoDB中$not搭配$elemMatch查询性能优化咨询

数据库集合中存储有数万条固定结构的文档，单文档结构示例如下：

{
  "_id": ObjectId("5ef053e819aaa00013a2bd69"),
  "approvers": [
    {
      "type": "ONE",
      "details": {
        "name": "NameOne",
        "surname": "SurnameOne"
      }
    },
    {
      "type": "TWO",
      "details": {
        "name": "NameTwo",
        "surname": "SurnameTwo"
      }
    },
    {
      "type": "THREE"
      // 该条审批人缺失details字段
    }
  ]
}

业务需要筛选满足以下任意条件的文档：

• approvers数组中不存在类型为"ONE"或"TWO"的审批人
• 数组中类型为"ONE"/"TWO"的审批人缺失details字段

最初编写的查询语句如下：

{
  $or: [
    {
      "approvers.type": {
        $not: {
          $in: ["ONE", "TWO"]
        }
      }
    }, 
   {
      approvers: {
        $not: {
          $elemMatch: {
            type: { $in: ["ONE", "TWO"]},
            details: {$exists: true}
          }
        }
      }
    }
  ]
}

该语句虽然能返回正确结果，但执行效率极低，无法命中索引，数据库引擎会执行全集合扫描，逐文档遍历校验数组内所有元素。当前业务集合共有7.5万条记录，单文档的approvers数组最多包含3个元素，需要确认是否存在查询层面的优化技巧，还是必须调整数据结构才能解决性能问题。

不需要强制调整数据结构，通过索引配合查询改写就可以解决性能问题，额外的结构调整可以进一步提升性能。

索引配置 #

首先创建数组字段的复合多键索引，覆盖查询过滤的两个核心字段：

db.collection.createIndex({
  "approvers.type": 1,
  "approvers.details": 1
})

查询语句改写 #

原查询大量使用$not取反逻辑，MongoDB查询优化器无法对这类否定逻辑做高效的索引范围裁剪，把逻辑改写为等价的正向匹配即可命中索引：

{
  $or: [
    // 匹配第一个条件：数组中不存在ONE、TWO类型的审批人
    {
      "approvers.type": {
        $nin: ["ONE", "TWO"]
      }
    },
    // 匹配第二个条件：存在ONE/TWO类型的审批人，且该条审批人缺失details字段
    {
      "approvers": {
        $elemMatch: {
          type: { $in: ["ONE", "TWO"] },
          details: { $exists: false }
        }
      }
    }
  ]
}

改写后的查询两个分支都可以直接命中之前创建的复合多键索引，不需要全表扫描：

• 第一个分支直接通过索引扫描筛除所有包含ONE/TWO类型审批人的文档
• 第二个分支直接通过索引定位到type为ONE/TWO且details不存在的文档
  由于单文档的approvers数组最多只有3个元素，索引命中后的回表校验开销可以忽略，7.5万条数据规模下查询耗时可以降到毫秒级。

可选结构优化（非必须） #

如果后续查询复杂度会持续提升，可以在文档根层级冗余一个布尔类型的状态字段hasValidRequiredApprover，在文档写入、更新时提前计算字段值：

• 当文档中同时存在ONE、TWO类型的审批人，且两类审批人都携带完整details字段时，字段值为true
• 其余所有情况字段值为false
  查询时直接匹配{ "hasValidRequiredApprover": false }即可，仅需给该字段创建单字段普通索引，查询性能比多键索引更高。由于单文档数组最多3个元素，字段值的计算逻辑非常简单，额外维护成本极低。

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