mongodb - 即使有索引，MongoDB 也发现查询非常慢

Question

我不明白为什么下面的查询这么慢，解释显示它需要 74+ 秒才能完成，尽管存在索引。

dev_id我可以向您保证，两者_id都已编入索引，这似乎根本没有帮助。

db.DeviceLoginLog.find({"dev_id": "xxx"}).skip(0).limit(10).sort({"_id": -1}).explain("executionStats");

谁能帮我解释解释输出并建议我如何加快速度？

{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "example.DeviceLoginLog",
                "indexFilterSet" : false,
                "parsedQuery" : {
                        "dev_id" : {
                                "$eq" : "xxx"
                        }
                },
                "winningPlan" : {
                        "stage" : "SORT",
                        "sortPattern" : {
                                "_id" : -1
                        },
                        "limitAmount" : 10,
                        "inputStage" : {
                                "stage" : "KEEP_MUTATIONS",
                                "inputStage" : {
                                        "stage" : "FETCH",
                                        "inputStage" : {
                                                "stage" : "IXSCAN",
                                                "keyPattern" : {
                                                        "dev_id" : 1
                                                },
                                                "indexName" : "dev_id_1",
                                                "isMultiKey" : false,
                                                "direction" : "forward",
                                                "indexBounds" : {
                                                        "dev_id" : [
                                                                "[\"xxx\", \"xxx\"]"
                                                        ]
                                                }
                                        }
                                }
                        }
                },
                "rejectedPlans" : [
                        {
                                "stage" : "LIMIT",
                                "limitAmount" : 4,
                                "inputStage" : {
                                        "stage" : "FETCH",
                                        "filter" : {
                                                "dev_id" : {
                                                        "$eq" : "xxx"
                                                }
                                        },
                                        "inputStage" : {
                                                "stage" : "IXSCAN",
                                                "keyPattern" : {
                                                        "_id" : 1
                                                },
                                                "indexName" : "_id_",
                                                "isMultiKey" : false,
                                                "direction" : "backward",
                                                "indexBounds" : {
                                                        "_id" : [
                                                                "[MaxKey, MinKey]"
                                                        ]
                                                }
                                        }
                                }
                        }
                ]
        },
        "executionStats" : {
                "executionSuccess" : true,
                "nReturned" : 10,
                "executionTimeMillis" : 74867,
                "totalKeysExamined" : 9203,
                "totalDocsExamined" : 9203,
                "executionStages" : {
                        "stage" : "SORT",
                        "nReturned" : 10,
                        "executionTimeMillisEstimate" : 49860,
                        "works" : 9537,
                        "advanced" : 10,
                        "needTime" : 9204,
                        "needFetch" : 321,
                        "saveState" : 1864,
                        "restoreState" : 1864,
                        "isEOF" : 1,
                        "invalidates" : 1064,
                        "sortPattern" : {
                                "_id" : -1
                        },
                        "memUsage" : 1890,
                        "memLimit" : 33554432,
                        "limitAmount" : 10,
                        "inputStage" : {
                                "stage" : "KEEP_MUTATIONS",
                                "nReturned" : 9203,
                                "executionTimeMillisEstimate" : 49820,
                                "works" : 9525,
                                "advanced" : 9203,
                                "needTime" : 0,
                                "needFetch" : 321,
                                "saveState" : 1864,
                                "restoreState" : 1864,
                                "isEOF" : 1,
                                "invalidates" : 1064,
                                "inputStage" : {
                                        "stage" : "FETCH",
                                        "nReturned" : 9203,
                                        "executionTimeMillisEstimate" : 49820,
                                        "works" : 9525,
                                        "advanced" : 9203,
                                        "needTime" : 0,
                                        "needFetch" : 321,
                                        "saveState" : 1864,
                                        "restoreState" : 1864,
                                        "isEOF" : 1,
                                        "invalidates" : 1064,
                                        "docsExamined" : 9203,
                                        "alreadyHasObj" : 0,
                                        "inputStage" : {
                                                "stage" : "IXSCAN",
                                                "nReturned" : 9203,
                                                "executionTimeMillisEstimate" : 10,
                                                "works" : 9204,
                                                "advanced" : 9203,
                                                "needTime" : 0,
                                                "needFetch" : 0,
                                                "saveState" : 1864,
                                                "restoreState" : 1864,
                                                "isEOF" : 1,
                                                "invalidates" : 1064,
                                                "keyPattern" : {
                                                        "dev_id" : 1
                                                },
                                                "indexName" : "dev_id_1",
                                                "isMultiKey" : false,
                                                "direction" : "forward",
                                                "indexBounds" : {
                                                        "dev_id" : [
                                                                "[\"xxx\", \"xxx\"]"
                                                        ]
                                                },
                                                "keysExamined" : 9203,
                                                "dupsTested" : 0,
                                                "dupsDropped" : 0,
                                                "seenInvalidated" : 0,
                                                "matchTested" : 0
                                        }
                                }
                        }
                }
        },
        "serverInfo" : {
                "host" : "iZ231ear7c9Z",
                "port" : 27017,
                "version" : "3.0.3",
                "gitVersion" : "b40106b36eecd1b4407eb1ad1af6bc60593c6105"
        },
        "ok" : 1
}

先谢谢了！</p>

标签： mongodbexplain

Answer 1

该查询使用索引{dev_id: 1}来查找，然后必须将匹配的文档提取到内存中以便在不使用索引的情况下进行排序。创建一个复合索引{dev_id: 1, _id: -1}将提高性能。

请参阅sort-and-non-prefix-subset-of-an-index。