Does MongoDB use indexes and how should the results of explian () be analyzed?

  mongodb, question
  1. When using MOngoDB, the query criteria and are currently required_idTo page, the database has onebirthDay_str_1_status_1_activeTime_1The index of these three fields, my query criteria are:

    db.getCollection('T_User').find({
     "birthDay_str": "0727",
     "status": "APPLY",
     "_id": {
     $gte: "570e08ca00b04947c827ea2e"
     }
     }).sort({
     "_id": 1
     }).explain()

    The results are as follows. I am not very familiar with mongoDB index. Who can answer what is the meaning of the following contents?

  2. The results returned are as follows:

{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "ossDev.T_User",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$and" : [ 
                {
                    "birthDay_str" : {
                        "$eq" : "0727"
                    }
                }, 
                {
                    "status" : {
                        "$eq" : "APPLY"
                    }
                }, 
                {
                    "_id" : {
                        "$gte" : "570e08ca00b04947c827ea2e"
                    }
                }
            ]
        },
        "winningPlan" : {
            "stage" : "SORT",
            "sortPattern" : {
                "_id" : 1.0
            },
            "inputStage" : {
                "stage" : "SORT_KEY_GENERATOR",
                "inputStage" : {
                    "stage" : "FETCH",
                    "filter" : {
                        "_id" : {
                            "$gte" : "570e08ca00b04947c827ea2e"
                        }
                    },
                    "inputStage" : {
                        "stage" : "IXSCAN",
                        "keyPattern" : {
                            "birthDay_str" : 1.0,
                            "status" : 1.0,
                            "activeTime" : 1.0
                        },
                        "indexName" : "birthDay_str_1_status_1_activeTime_1",
                        "isMultiKey" : false,
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : 1,
                        "direction" : "forward",
                        "indexBounds" : {
                            "birthDay_str" : [ 
                                "[\"0727\", \"0727\"]"
                            ],
                            "status" : [ 
                                "[\"APPLY\", \"APPLY\"]"
                            ],
                            "activeTime" : [ 
                                "[MinKey, MaxKey]"
                            ]
                        }
                    }
                }
            }
        },
        "rejectedPlans" : [ 
            {
                "stage" : "FETCH",
                "filter" : {
                    "$and" : [ 
                        {
                            "birthDay_str" : {
                                "$eq" : "0727"
                            }
                        }, 
                        {
                            "status" : {
                                "$eq" : "APPLY"
                            }
                        }
                    ]
                },
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "keyPattern" : {
                        "_id" : 1
                    },
                    "indexName" : "_id_",
                    "isMultiKey" : false,
                    "isUnique" : true,
                    "isSparse" : false,
                    "isPartial" : false,
                    "indexVersion" : 1,
                    "direction" : "forward",
                    "indexBounds" : {
                        "_id" : [ 
                            "[\"570e08ca00b04947c827ea2e\", {})"
                        ]
                    }
                }
            }, 
            {
                "stage" : "SORT",
                "sortPattern" : {
                    "_id" : 1.0
                },
                "inputStage" : {
                    "stage" : "SORT_KEY_GENERATOR",
                    "inputStage" : {
                        "stage" : "FETCH",
                        "filter" : {
                            "$and" : [ 
                                {
                                    "birthDay_str" : {
                                        "$eq" : "0727"
                                    }
                                }, 
                                {
                                    "_id" : {
                                        "$gte" : "570e08ca00b04947c827ea2e"
                                    }
                                }
                            ]
                        },
                        "inputStage" : {
                            "stage" : "IXSCAN",
                            "keyPattern" : {
                                "status" : 1.0
                            },
                            "indexName" : "status_1",
                            "isMultiKey" : false,
                            "isUnique" : false,
                            "isSparse" : false,
                            "isPartial" : false,
                            "indexVersion" : 1,
                            "direction" : "forward",
                            "indexBounds" : {
                                "status" : [ 
                                    "[\"APPLY\", \"APPLY\"]"
                                ]
                            }
                        }
                    }
                }
            }, 
            {
                "stage" : "SORT",
                "sortPattern" : {
                    "_id" : 1.0
                },
                "inputStage" : {
                    "stage" : "SORT_KEY_GENERATOR",
                    "inputStage" : {
                        "stage" : "FETCH",
                        "filter" : {
                            "$and" : [ 
                                {
                                    "birthDay_str" : {
                                        "$eq" : "0727"
                                    }
                                }, 
                                {
                                    "_id" : {
                                        "$gte" : "570e08ca00b04947c827ea2e"
                                    }
                                }
                            ]
                        },
                        "inputStage" : {
                            "stage" : "IXSCAN",
                            "keyPattern" : {
                                "status" : 1.0,
                                "userSequence" : 1.0
                            },
                            "indexName" : "status_1_userSequence_1",
                            "isMultiKey" : false,
                            "isUnique" : false,
                            "isSparse" : false,
                            "isPartial" : false,
                            "indexVersion" : 1,
                            "direction" : "forward",
                            "indexBounds" : {
                                "status" : [ 
                                    "[\"APPLY\", \"APPLY\"]"
                                ],
                                "userSequence" : [ 
                                    "[MinKey, MaxKey]"
                                ]
                            }
                        }
                    }
                }
            }
        ]
    },
    "serverInfo" : {
        "host" : "maomaodb02",
        "port" : 27017,
        "version" : "3.2.4",
        "gitVersion" : "e2ee9ffcf9f5a94fad76802e28cc978718bb7a30"
    },
    "ok" : 1.0
}

Among them:

"winningPlan" : {
 "stage" : "SORT",
 "sortPattern" : {
 "_id" : 1.0
 },
 "inputStage" : {
 "stage" : "SORT_KEY_GENERATOR",
 "inputStage" : {
 "stage" : "FETCH",
 "filter" : {
 "_id" : {
 "$gte" : "570e08ca00b04947c827ea2e"
 }
 },

The status displayed isSORTWhat do you mean, to the following state againIXSCANWhich piece of information is used to judge whether the index is used?
Colleague stage status isIXSCANFETCHSORTWhat do you mean, respectively?

In short, see{stage: "SORT"}This means that there is a stage that is being done alone.Memory sortInstead of using indexes. And seeIXSCANAnd that index use here is to use it to filter data:

"indexBounds" : {
 "birthDay_str" : [
 "[\"0727\", \"0727\"]"
 ],
 "status" : [
 "[\"APPLY\", \"APPLY\"]"
 ],
 "activeTime" : [
 "[MinKey, MaxKey]"
 ]
 }

So the query part is usedbirthDay_str_1_status_1_activeTime_1hit the targetbirthDay_str+statusTwo to filter the data, of course, your conditions also include{_id: {$gte: "570e08ca00b04947c827ea2e"}}, so although the index hit, but can only be said to have some help to your query, efficiency is not good to say (need to seeexplain("executionStats"))。 Anyway, this index is not helpful for sorting.
AgainFETCH. It means literally: grab data. Suppose you wereAThe index is set up on the field. The index is BTree, but you can think of it as a well-ordered array, so that you can search in half when doing the search, thus saving the search time and time complexity fromO(n)BecomeO(log2(n))This is also the reason why indexes improve query efficiency. But there are onlyAThe value of a field cannot and need not contain the contents of the entire document, so in addition to storing the value of a in the array, the system will additionally store a data page, a data page, which means: when you need other values than a, go here and find the entire document. In this way, there will beFETCHThis move.

I can’t explain how indexes work here, but you can search some documents to see how BTree indexes work, and I believe you will have a better understanding of how indexes work.

Finally, mention that the most suitable index to satisfy your query and ranking is:

{
 birthDay_str: 1,
 status: 1,
 _id: 1
 }

One more thing I don’t know is whether you missed your hand or didn’t pay attention. By default_idYesObjectIdInstead of a string (unless you specifically modify it), the correct wording in the condition should be:

"_id": {
 $gte: ObjectId("570e08ca00b04947c827ea2e")
 }