主页 / dba / 问题 / 341332
Accepted
DaPeda
Asked: 2024-07-31 22:29:38 +0800 CST

总计解除后的索引使用情况

  • 772

我们的 MongoDB 中有一个集合，由于用例的改变，到目前为止，一个小查询现在真的很慢。所讨论的聚合有点像这样：

db.collection.aggregate([
  {
    $match: {
      "_id": {
        $in: [ list of 1000 ids ]
      }
    }
  },
  {
    $unwind: "array_field"
  },
  {
    $match: {
      "array_field.sub_field": 123456789
    }
  }
])

{"_id": 1, "array_field.sub_field": 1}我现在的问题是，这里是否会考虑类似的索引，或者是否有必要让开发人员将第二个$match索引移到第一个索引？

遗憾的是，在可预见的未来，数据模型的重新设计还未实现。

index

1 个回答

  1. Best Answer

    您可以构建一个小型 PoC 来验证索引的使用情况。该索引将得到利用，因为它是按照 ESR 规则构建的。

    解释输出：

    {
  "$clusterTime": {
    "clusterTime": Timestamp(1722446531, 2),
    "signature": {
      "hash": BinData(0, "1UsZLM1VfzhxUTxn/sF3YXXOCh8="),
      "keyId": NumberLong(7360702457824935938)
    }
  },
  "command": {
    "$db": "f75abdd274be932b64a3fd04bcb14d27",
    "aggregate": "collection",
    "cursor": {
      "batchSize": 1000
    },
    "maxTimeMS": NumberLong(20000),
    "pipeline": [
      {
        "$match": {
          "_id": {
            "$in": [
              0,
              1,
              2,
              3,
              4,
              5,
              6,
              7,
              8,
              9
            ]
          }
        }
      },
      {
        "$unwind": "$array_field"
      },
      {
        "$match": {
          "array_field.sub_field": "a"
        }
      }
    ]
  },
  "explainVersion": "1",
  "operationTime": Timestamp(1722446531, 2),
  "serverParameters": {
    "internalDocumentSourceGroupMaxMemoryBytes": 104857600,
    "internalDocumentSourceSetWindowFieldsMaxMemoryBytes": 104857600,
    "internalLookupStageIntermediateDocumentMaxSizeBytes": 104857600,
    "internalQueryFacetBufferSizeBytes": 104857600,
    "internalQueryFacetMaxOutputDocSizeBytes": 104857600,
    "internalQueryMaxAddToSetBytes": 104857600,
    "internalQueryMaxBlockingSortMemoryUsageBytes": 104857600,
    "internalQueryProhibitBlockingMergeOnMongoS": 0
  },
  "stages": [
    {
      "$cursor": {
        "executionStats": {
          "executionStages": {
            "advanced": 10,
            "alreadyHasObj": 0,
            "docsExamined": 10,
            "executionTimeMillisEstimate": 0,
            "inputStage": {
              "advanced": 10,
              "direction": "forward",
              "dupsDropped": 0,
              "dupsTested": 0,
              "executionTimeMillisEstimate": 0,
              "indexBounds": {
                "_id": [
                  "[0.0, 0.0]",
                  "[1.0, 1.0]",
                  "[2.0, 2.0]",
                  "[3.0, 3.0]",
                  "[4.0, 4.0]",
                  "[5.0, 5.0]",
                  "[6.0, 6.0]",
                  "[7.0, 7.0]",
                  "[8.0, 8.0]",
                  "[9.0, 9.0]"
                ]
              },
              "indexName": "_id_",
              "indexVersion": 2,
              "isEOF": 1,
              "isMultiKey": false,
              "isPartial": false,
              "isSparse": false,
              "isUnique": true,
              "keyPattern": {
                "_id": 1
              },
              "keysExamined": 10,
              "multiKeyPaths": {
                "_id": []
              },
              "nReturned": 10,
              "needTime": 0,
              "needYield": 0,
              "restoreState": 1,
              "saveState": 1,
              "seeks": 1,
              "stage": "IXSCAN",
              "works": 11
            },
            "isEOF": 1,
            "nReturned": 10,
            "needTime": 0,
            "needYield": 0,
            "restoreState": 1,
            "saveState": 1,
            "stage": "FETCH",
            "works": 12
          },
          "executionSuccess": true,
          "executionTimeMillis": 0,
          "nReturned": 10,
          "totalDocsExamined": 10,
          "totalKeysExamined": 10
        },
        "queryPlanner": {
          "indexFilterSet": false,
          "maxIndexedAndSolutionsReached": false,
          "maxIndexedOrSolutionsReached": false,
          "maxScansToExplodeReached": false,
          "namespace": "f75abdd274be932b64a3fd04bcb14d27.collection",
          "parsedQuery": {
            "_id": {
              "$in": [
                0,
                1,
                2,
                3,
                4,
                5,
                6,
                7,
                8,
                9
              ]
            }
          },
          "planCacheKey": "D97631C1",
          "queryHash": "772EC04C",
          "rejectedPlans": [
            {
              "inputStage": {
                "direction": "forward",
                "indexBounds": {
                  "_id": [
                    "[0.0, 0.0]",
                    "[1.0, 1.0]",
                    "[2.0, 2.0]",
                    "[3.0, 3.0]",
                    "[4.0, 4.0]",
                    "[5.0, 5.0]",
                    "[6.0, 6.0]",
                    "[7.0, 7.0]",
                    "[8.0, 8.0]",
                    "[9.0, 9.0]"
                  ],
                  "array_field.sub_field": [
                    "[MinKey, MaxKey]"
                  ]
                },
                "indexName": "ix",
                "indexVersion": 2,
                "isMultiKey": true,
                "isPartial": false,
                "isSparse": false,
                "isUnique": false,
                "keyPattern": {
                  "_id": 1,
                  "array_field.sub_field": 1
                },
                "multiKeyPaths": {
                  "_id": [],
                  "array_field.sub_field": [
                    "array_field"
                  ]
                },
                "stage": "IXSCAN"
              },
              "stage": "FETCH"
            }
          ],
          "winningPlan": {
            "inputStage": {
              "direction": "forward",
              "indexBounds": {
                "_id": [
                  "[0.0, 0.0]",
                  "[1.0, 1.0]",
                  "[2.0, 2.0]",
                  "[3.0, 3.0]",
                  "[4.0, 4.0]",
                  "[5.0, 5.0]",
                  "[6.0, 6.0]",
                  "[7.0, 7.0]",
                  "[8.0, 8.0]",
                  "[9.0, 9.0]"
                ]
              },
              "indexName": "_id_",
              "indexVersion": 2,
              "isMultiKey": false,
              "isPartial": false,
              "isSparse": false,
              "isUnique": true,
              "keyPattern": {
                "_id": 1
              },
              "multiKeyPaths": {
                "_id": []
              },
              "stage": "IXSCAN"
            },
            "stage": "FETCH"
          }
        }
      },
      "executionTimeMillisEstimate": NumberLong(0),
      "nReturned": NumberLong(10)
    },
    {
      "$unwind": {
        "path": "$array_field"
      },
      "executionTimeMillisEstimate": NumberLong(0),
      "nReturned": NumberLong(20)
    },
    {
      "$match": {
        "array_field.sub_field": {
          "$eq": "a"
        }
      },
      "executionTimeMillisEstimate": NumberLong(0),
      "nReturned": NumberLong(5)
    }
  ]
}

    您可以看到IXSCAN的存在，这意味着该指数是有杠杆的。

    蒙戈游乐场

    不过，仍然建议开发人员$match尽早移动该阶段，因为这有助于最小化中间结果的大小，从而提高性能。

    db.collection.aggregate([
  {
    $match: {
      "_id": {
        $in: [
          0,
          1,
          2,
          3,
          4,
          5,
          6,
          7,
          8,
          9
        ]
      },
      "array_field.sub_field": "a"
    }
  },
  {
    $unwind: "$array_field"
  }
]).explain("executionStats")

    Mongo 游乐场

    • 0

相关问题