MongoDB 使用索引搜索空的 objectId 字段的有效方法？

Question

我正在尝试寻找一种有效的方法来搜索特定字段为 null 的项目。

在 MongoDB 中，我有 folder 模式，它有其父文件夹的 ObjectId 和 parent 的 parent 字段被编入索引。对于根文件夹，父字段为 null。

当我尝试使用 parent:null 查找所有根文件夹时，explain 显示 indexOnly: false

db.folders.find({parent: null}, {parent: 1, _id: 0}).explain()
{
    "cursor" : "BtreeCursor parent_1",
    "isMultiKey" : false,
    "n" : 126,
    "nscannedObjects" : 126,
    "nscanned" : 126,
    "nscannedObjectsAllPlans" : 126,
    "nscannedAllPlans" : 126,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 1,
    "nChunkSkips" : 0,
    "millis" : 0,
    "indexBounds" : {
        "parent" : [ 
            [ 
                null, 
                null
            ]
        ]
    },
    "server" : "c268.candidate.36:10268",
    "filterSet" : false,
    "stats" : {
        "type" : "PROJECTION",
        "works" : 128,
        "yields" : 1,
        "unyields" : 1,
        "invalidates" : 0,
        "advanced" : 126,
        "needTime" : 0,
        "needFetch" : 0,
        "isEOF" : 1,
        "children" : [ 
            {
                "type" : "KEEP_MUTATIONS",
                "works" : 128,
                "yields" : 1,
                "unyields" : 1,
                "invalidates" : 0,
                "advanced" : 126,
                "needTime" : 1,
                "needFetch" : 0,
                "isEOF" : 1,
                "children" : [ 
                    {
                        "type" : "FETCH",
                        "works" : 128,
                        "yields" : 1,
                        "unyields" : 1,
                        "invalidates" : 0,
                        "advanced" : 126,
                        "needTime" : 1,
                        "needFetch" : 0,
                        "isEOF" : 1,
                        "alreadyHasObj" : 0,
                        "forcedFetches" : 0,
                        "matchTested" : 126,
                        "children" : [ 
                            {
                                "type" : "IXSCAN",
                                "works" : 127,
                                "yields" : 1,
                                "unyields" : 1,
                                "invalidates" : 0,
                                "advanced" : 126,
                                "needTime" : 1,
                                "needFetch" : 0,
                                "isEOF" : 1,
                                "keyPattern" : "{ parent: 1.0 }",
                                "isMultiKey" : 0,
                                "boundsVerbose" : "field #0['parent']: [null, null]",
                                "yieldMovedCursor" : 0,
                                "dupsTested" : 0,
                                "dupsDropped" : 0,
                                "seenInvalidated" : 0,
                                "matchTested" : 0,
                                "keysExamined" : 126,
                                "children" : []
                            }
                        ]
                    }
                ]
            }
        ]
    }
}

我尝试使用 $type 来查找父文件夹不是 ObjectId 的任何文件夹。但仍然是 indexOnly:false。

db.folders.find({parent: {$ne: {$type: 7}}}, {parent: 1, _id: 0}).explain()
“仅索引”:假

有没有办法只使用索引来搜索空值？如果不是，是否有更好的值来存储而不是 null 以便能够使用索引进行搜索？

---

额外的

根文件夹示例

{
  "_id" : ObjectId("55a04a2d754971030059b7ad"),
  "active" : true,
  "modified" : ISODate("2016-02-05T22:30:08.053Z"),
  "created" : ISODate("2015-07-10T22:41:49.009Z"),
  "user" : ObjectId("54d3ae187a738c0300f59e61"),
  "name" : "2nd Grade",
  "parent" : null,
  "clientModified" : ISODate("2016-02-05T22:30:07.872Z"),
  "userCreated" : ISODate("2015-07-10T22:41:48.328Z"),
  "ancestors" : [],
  "__v" : 2
}

子文件夹示例

{
  "_id" : ObjectId("56d0b4edb6f05e03009bcabc"),
  "active" : true,
  "modified" : ISODate("2016-02-26T20:26:21.328Z"),
  "created" : ISODate("2016-02-26T20:26:21.328Z"),
  "user" : ObjectId("54d3ae187a738c0300f59e61"),
  "name" : "music",
  "parent" : ObjectId("55a04a2d754971030059b7ad"),
  "clientModified" : ISODate("2016-02-26T20:26:20.398Z"),
  "userCreated" : ISODate("2016-02-26T20:26:20.398Z"),
  "ancestors" : [ 
    ObjectId("55a04a2d754971030059b7ad")
  ],
  "__v" : 0
}

db.folders.getIndexes()

{
    "0" : {
        "v" : 1,
        "key" : {
            "_id" : 1
        },
        "name" : "_id_",
        "ns" : "app29099188.folders"
    },
    "1" : {
        "v" : 1,
        "key" : {
            "user" : 1,
            "_fts" : "text",
            "_ftsx" : 1
        },
        "name" : "user_1_name_text",
        "ns" : "app29099188.folders",
        "background" : true,
        "safe" : null,
        "weights" : {
            "name" : 1
        },
        "default_language" : "english",
        "language_override" : "language",
        "textIndexVersion" : 2
    },
    "2" : {
        "v" : 1,
        "key" : {
            "user" : 1,
            "parent" : 1
        },
        "name" : "user_1_parent_1",
        "ns" : "app29099188.folders",
        "background" : true,
        "safe" : null
    },
    "3" : {
        "v" : 1,
        "key" : {
            "parent" : 1.0000000000000000
        },
        "name" : "parent_1",
        "ns" : "app29099188.folders"
    }
}

Answer 1

评论后-更新:消除 docScan 的方法是在 parent 字段中有一个值。可以将 objectId 归零或仅将“/”作为根。

db.satoko.insert({"test":"sdsf", parent: "/"})   
db.satoko.insert({"test":"sds33f", parent: "/"})         
db.satoko.insert({"parent":ObjectId("56d8b2879bd059e7247a6096"), "test":"sdsf"})

explain results:

db.satoko.find({parent:{$eq:"/"} }, {parent: 1, _id: 0}).explain("allPlansExec
ution")
{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "test.satoko",
                "indexFilterSet" : false,
                "parsedQuery" : {
                        "parent" : {
                                "$eq" : "/"
                        }
                },
                "winningPlan" : {
                        "stage" : "PROJECTION",
                        "transformBy" : {
                                "parent" : 1,
                                "_id" : 0
                        },
                        "inputStage" : {
                                "stage" : "IXSCAN",
                                "keyPattern" : {
                                        "parent" : 1
                                },
                                "indexName" : "parent_1",
                                "isMultiKey" : false,
                                "isUnique" : false,
                                "isSparse" : false,
                                "isPartial" : false,
                                "indexVersion" : 1,
                                "direction" : "forward",
                                "indexBounds" : {
                                        "parent" : [
                                                "[\"/\", \"/\"]"
                                        ]
                                }
                        }
                },
                "rejectedPlans" : [ ]
        },
        "executionStats" : {
                "executionSuccess" : true,
                "nReturned" : 2,
                "executionTimeMillis" : 0,
                "totalKeysExamined" : 2,
                "totalDocsExamined" : 0,
                "executionStages" : {
                        "stage" : "PROJECTION",
                        "nReturned" : 2,
                        "executionTimeMillisEstimate" : 0,
                        "works" : 3,
                        "advanced" : 2,
                        "needTime" : 0,
                        "needYield" : 0,
                        "saveState" : 0,
                        "restoreState" : 0,
                        "isEOF" : 1,
                        "invalidates" : 0,
                        "transformBy" : {
                                "parent" : 1,
                                "_id" : 0
                        },
                        "inputStage" : {
                                "stage" : "IXSCAN",
                                "nReturned" : 2,
                                "executionTimeMillisEstimate" : 0,
                                "works" : 3,
                                "advanced" : 2,
                                "needTime" : 0,
                                "needYield" : 0,
                                "saveState" : 0,
                                "restoreState" : 0,
                                "isEOF" : 1,
                                "invalidates" : 0,
                                "keyPattern" : {
                                        "parent" : 1
                                },
                                "indexName" : "parent_1",
                                "isMultiKey" : false,
                                "isUnique" : false,
                                "isSparse" : false,
                                "isPartial" : false,
                                "indexVersion" : 1,
                                "direction" : "forward",
                                "indexBounds" : {
                                        "parent" : [
                                                "[\"/\", \"/\"]"
                                        ]
                                },
                                "keysExamined" : 2,
                                "dupsTested" : 0,
                                "dupsDropped" : 0,
                                "seenInvalidated" : 0
                        }
                },
                "allPlansExecution" : [ ]
        },
        "serverInfo" : {
                "host" : "greg",
                "port" : 27017,
                "version" : "3.2.3",
                "gitVersion" : "b326ba837cf6f49d65c2f85e1b70f6f31ece7937"
        },
        "ok" : 1
}