Spring Data MongoDB查询执行时间异常过长排查求助

手动通过DataGrip/IntelliJ执行MongoDB查询时响应时间仅500-700ms，但使用Spring Data MongoDB服务执行同一查询时，响应时间长达33000-45000ms。通过.explain("executionStats")分析发现两者的执行计划数据差异显著：

• 手动执行的executionStats显示：totalKeysExamined和totalDocsExamined均为1，执行时间仅2ms
• 服务端日志显示：keysExamined和docsExamined达87182，最终仅返回1条数据，性能极差

已尝试匹配服务端Mongo驱动版本进行手动查询，问题仍未改善。以下是相关查询语句、executionStats、服务端日志及代码实现：

查询语句 #

db.fooCollection.find({
    "$or": [{
      "$and": [{
        "params": {
          "$elemMatch": {
            "name": "manufacturer",
            "val": "FooManufacturer"
          }
        }
      }, {
        "params": {
          "$elemMatch": {
            "name": "articleId",
            "val": "FOO-FOO-ARTICLE-ID-FOO"
          }
        }
      }]
    }, {
      "$and": [{
        "params": {
          "$elemMatch": {
            "name": "brand",
            "val": "FooManufacturer"
          }
        }
      }, {
        "params": {
          "$elemMatch": {
            "name": "articleId",
            "val": "FOO-FOO-ARTICLE-ID-FOO"
          }
        }
      }]
    }]
  }).collation({
    locale: 'en',
    strength: 1,
    alternate: "shifted",
    maxVariable: "punct"
  })
  .explain("executionStats")

手动执行的executionStats #

{
  "executionSuccess": true,
  "nReturned": new NumberInt("1"),
  "executionTimeMillis": new NumberInt("2"),
  "totalKeysExamined": new NumberInt("1"),
  "totalDocsExamined": new NumberInt("1"),
  "executionStages": {
    "stage": "SUBPLAN",
    "nReturned": new NumberInt("1"),
    "executionTimeMillisEstimate": new NumberInt("2"),
    "works": new NumberInt("2"),
    "advanced": new NumberInt("1"),
    "needTime": new NumberInt("0"),
    "needYield": new NumberInt("0"),
    "saveState": new NumberInt("0"),
    "restoreState": new NumberInt("0"),
    "isEOF": new NumberInt("1"),
    "inputStage": {
      "stage": "FETCH",
      "filter": {
        "$or": [{
          "$and": [{
            "params": {
              "$elemMatch": {
                "$and": [{
                  "name": {
                    "$eq": "articleId"
                  }
                }, {
                  "val": {
                    "$eq": "FOO-FOO-ARTICLE-ID-FOO"
                  }
                }]
              }
            }
          }, {
            "params": {
              "$elemMatch": {
                "$and": [{
                  "name": {
                    "$eq": "brand"
                  }
                }, {
                  "val": {
                    "$eq": "FooManufacturer"
                  }
                }]
              }
            }
          }]
        }, {
          "$and": [{
            "params": {
              "$elemMatch": {
                "$and": [{
                  "name": {
                    "$eq": "articleId"
                  }
                }, {
                  "val": {
                    "$eq": "FOO-FOO-ARTICLE-ID-FOO"
                  }
                }]
              }
            }
          }, {
            "params": {
              "$elemMatch": {
                "$and": [{
                  "name": {
                    "$eq": "manufacturer"
                  }
                }, {
                  "val": {
                    "$eq": "FooManufacturer"
                  }
                }]
              }
            }
          }]
        }]
      },
      "nReturned": new NumberInt("1"),
      "executionTimeMillisEstimate": new NumberInt("0"),
      "works": new NumberInt("2"),
      "advanced": new NumberInt("1"),
      "needTime": new NumberInt("0"),
      "needYield": new NumberInt("0"),
      "saveState": new NumberInt("0"),
      "restoreState": new NumberInt("0"),
      "isEOF": new NumberInt("1"),
      "docsExamined": new NumberInt("1"),
      "alreadyHasObj": new NumberInt("0"),
      "inputStage": {
        "stage": "IXSCAN",
        "nReturned": new NumberInt("1"),
        "executionTimeMillisEstimate": new NumberInt("0"),
        "works": new NumberInt("2"),
        "advanced": new NumberInt("1"),
        "needTime": new NumberInt("0"),
        "needYield": new NumberInt("0"),
        "saveState": new NumberInt("0"),
        "restoreState": new NumberInt("0"),
        "isEOF": new NumberInt("1"),
        "keyPattern": {
          "params.name": new NumberInt("1"),
          "params.val": new NumberInt("1")
        },
        "indexName": "params.name_1_params.val_1",
        "collation": {
          "locale": "en",
          "caseLevel": false,
          "caseFirst": "off",
          "strength": new NumberInt("1"),
          "numericOrdering": false,
          "alternate": "shifted",
          "maxVariable": "punct",
          "normalization": false,
          "backwards": false,
          "version": "57.1"
        },
        "isMultiKey": true,
        "multiKeyPaths": {
          "params.name": ["params"],
          "params.val": ["params"]
        },
        "isUnique": false,
        "isSparse": false,
        "isPartial": false,
        "indexVersion": new NumberInt("2"),
        "direction": "forward",
        "indexBounds": {
          "params.name": ["[CollationKey(0xCOLLECTION_KEY), CollationKey(0xCOLLECTION_KEY)]"],
          "params.val": ["[CollationKey(0xCOLLECTION_KEY_2), CollationKey(0xCOLLECTION_KEY_2)]"]
        },
        "keysExamined": new NumberInt("1"),
        "seeks": new NumberInt("1"),
        "dupsTested": new NumberInt("1"),
        "dupsDropped": new NumberInt("0")
      }
    }
  }
}

服务端日志 #

{
  "planSummary": "IXSCAN { params.name: 1, params.val: 1 }, IXSCAN { params.name: 1, params.val: 1 }",
  "keysExamined": 87182,
  "docsExamined": 87182,
  "cursorExhausted": true,
  "numYields": 484,
  "nreturned": 1
}

代码实现 #

private suspend fun findByParams(parameters: Set<Map<ParamName, ParamValue>>): CloseableIterator<ProductDocument> {
    val collation = Collation.of("en").strength(1).alternate("shifted").maxVariable("punct")
    val query = Query().collation(collation)
    val criterias = parameters.map { set -> 
        set.map {
            Criteria.where("parameters").elemMatch(Criteria.where("name").isEqualTo(it.key.name).and("value").isEqualTo(it.value.value))
        }
    }
    query.addCriteria(Criteria().orOperator(criterias.mapNotNull { Criteria().andOperator(it) }))
    return withContext(Dispatchers.IO) {
        mongoTemplate.stream(query, ProductDocument::class.java)
    }
}

注：代码中笔误iseEqualTo已修正为isEqualTo

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