mongodb - Mongo Distinct Index for Dot Notated

Question

1. 我有一个托管在 Atlas 上的 mongodb v4.2.2。有一个集合 objects和两个字段：metadata作为对象数组，每个对象都有一个字段key和第二个字段，名为model. 将复合索引添加 model->metadata.key为常规索引。从控制台运行 db 命令 db.objects.explain('executionStats').distinct('metadata.key', {model: ObjectId('5e18aff58a5aaffdc3d6f26d')})时，它不使用我的model->metadata.key索引，也不使用 DISTINCT_SCAN。

2. 但是，如果我将数据移动到另一个集合，可以说objectKeys有两个字段，例如key，model并再次添加常规复合索引model->key并运行命令 db.objectKeys.explain('executionStats').distinct('key', {model: ObjectId('5e18aff58a5aaffdc3d6f26d')})，它成功应用 DISTINCT_SCAN 并使用索引。

3. 问题。如何强制 MongoDB 在不同操作期间使用点标记字段的索引？

更新详情：

1.

db.objects.getIndexes();
{
  "v" : 2,
  "key" : {
    "model" : 1,
    "metadata.key" : 1,
    "metadata.value" : 1
  },
  "name" : "model_1_metadata.key_1_metadata.value_1",
  "ns" : "my_db.objects",
  "background" : true
}

db.objects.explain('executionStats').distinct('metadata.key', {model: ObjectId('5e18aff58a5aaffdc3d6f26d')})

"executionStages" : {
  "stage" : "FETCH",
    "nReturned" : 0,
    "executionTimeMillisEstimate" : 0,
    "works" : 1,
    "advanced" : 0,
    "needTime" : 0,
    "needYield" : 0,
    "saveState" : 0,
    "restoreState" : 0,
    "isEOF" : 1,
    "docsExamined" : 0,
    "alreadyHasObj" : 0,
    "inputStage" : {
    "stage" : "IXSCAN",
      "nReturned" : 0,
      "executionTimeMillisEstimate" : 0,
      "works" : 1,
      "advanced" : 0,
      "needTime" : 0,
      "needYield" : 0,
      "saveState" : 0,
      "restoreState" : 0,
      "isEOF" : 1,
      "keyPattern" : {
      "model" : 1,
        "metadata.key" : 1,
        "metadata.value" : 1
    },
    "indexName" : "model_1_metadata.key_1_metadata.value_1",
      "isMultiKey" : true,
      "multiKeyPaths" : {
      "model" : [ ],
        "metadata.key" : [
        "metadata"
      ],
        "metadata.value" : [
        "metadata"
      ]
    },
    "isUnique" : false,
      "isSparse" : false,
      "isPartial" : false,
      "indexVersion" : 2,
      "direction" : "forward",
      "indexBounds" : {
      "model" : [
        "[ObjectId('5e18aff58a5aaffdc3d6f26d'), ObjectId('5e18aff58a5aaffdc3d6f26d')]"
      ],
        "metadata.key" : [
        "[MinKey, MaxKey]"
      ],
        "metadata.value" : [
        "[MinKey, MaxKey]"
      ]
    },
    "keysExamined" : 0,
      "seeks" : 1,
      "dupsTested" : 0,
      "dupsDropped" : 0
  }
}

2.

db.object_keys.getIndexes();

{
  "v" : 2,
  "key" : {
    "models" : 1,
    "key" : 1
  },
  "name" : "models_1_key_1",
  "ns" : "my_db.object_keys",
  "background" : true
},

db.object_keys.explain('executionStats').distinct('key', {models: ObjectId('5e18aff58a5aaffdc3d6f26d')})

"executionStages" : {
  "stage" : "PROJECTION_COVERED",
    "nReturned" : 0,
    "executionTimeMillisEstimate" : 0,
    "works" : 1,
    "advanced" : 0,
    "needTime" : 0,
    "needYield" : 0,
    "saveState" : 0,
    "restoreState" : 0,
    "isEOF" : 1,
    "transformBy" : {
    "_id" : 0,
      "key" : 1
  },
  "inputStage" : {
    "stage" : "DISTINCT_SCAN",
      "nReturned" : 0,
      "executionTimeMillisEstimate" : 0,
      "works" : 1,
      "advanced" : 0,
      "needTime" : 0,
      "needYield" : 0,
      "saveState" : 0,
      "restoreState" : 0,
      "isEOF" : 1,
      "keyPattern" : {
      "models" : 1,
        "key" : 1
    },
    "indexName" : "models_1_key_1",
      "isMultiKey" : true,
      "multiKeyPaths" : {
      "models" : [
        "models"
      ],
        "key" : [ ]
    },
    "isUnique" : false,
      "isSparse" : false,
      "isPartial" : false,
      "indexVersion" : 2,
      "direction" : "forward",
      "indexBounds" : {
      "models" : [
        "[ObjectId('5e18aff58a5aaffdc3d6f26d'), ObjectId('5e18aff58a5aaffdc3d6f26d')]"
      ],
        "key" : [
        "[MinKey, MaxKey]"
      ]
    },
    "keysExamined" : 0
  }
}

因此，您可以在第一种情况下看到它使用 IXSCAN 表示索引扫描，而在第二种情况下，它使用 DISTINCT_SCAN 被认为更快。不同之处在于，在第一种情况下，该metadata字段是对象数组，而在第二种情况下，该字段key是字符串

标签： mongodbindexingdistinct