mongodb - 为什么 MongoDB 不同的查询计划显示不同的 nReturned 值？

Question

我的 MongoDB 数据库中有一个集合 faults，每个文档都有这些字段:rack_name、timestamp

为了测试和比较性能，我创建了这两个索引:

机架 -> {'rack_name': 1}

和

时间 -> {'timestamp': 1}

现在我用 explain() 执行了以下查询:

db.faults.find({
    'rack_name': {
        $in: [ 'providence1', 'helena2' ]
    }, 
    'timestamp': {
        $gt: 1501548359000
    }
})
.explain('allPlansExecution') 

结果如下:

 {
"queryPlanner" : {
    "plannerVersion" : 1,
    "namespace" : "quicktester_clone.faults",
    "indexFilterSet" : false,
    "parsedQuery" : {
        "$and" : [ 
            {
                "timestamp" : {
                    "$gt" : 1501548359000.0
                }
            }, 
            {
                "rack_name" : {
                    "$in" : [ 
                        "helena2", 
                        "providence1"
                    ]
                }
            }
        ]
    },
    "winningPlan" : {
        "stage" : "FETCH",
        "filter" : {
            "timestamp" : {
                "$gt" : 1501548359000.0
            }
        },
        "inputStage" : {
            "stage" : "IXSCAN",
            "keyPattern" : {
                "rack_name" : 1
            },
            "indexName" : "rack",
            "isMultiKey" : false,
            "multiKeyPaths" : {
                "rack_name" : []
            },
            "isUnique" : false,
            "isSparse" : false,
            "isPartial" : false,
            "indexVersion" : 2,
            "direction" : "forward",
            "indexBounds" : {
                "rack_name" : [ 
                    "[\"helena2\", \"helena2\"]", 
                    "[\"providence1\", \"providence1\"]"
                ]
            }
        }
    },
    "rejectedPlans" : [ 
        {
            "stage" : "FETCH",
            "filter" : {
                "rack_name" : {
                    "$in" : [ 
                        "helena2", 
                        "providence1"
                    ]
                }
            },
            "inputStage" : {
                "stage" : "IXSCAN",
                "keyPattern" : {
                    "timestamp" : 1
                },
                "indexName" : "time",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                    "timestamp" : []
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "timestamp" : [ 
                        "(1501548359000.0, inf.0]"
                    ]
                }
            }
        }
    ]
},
"executionStats" : {
    "executionSuccess" : true,
    "nReturned" : 43,
    "executionTimeMillis" : 1512,
    "totalKeysExamined" : 221,
    "totalDocsExamined" : 219,
    "executionStages" : {
        "stage" : "FETCH",
        "filter" : {
            "timestamp" : {
                "$gt" : 1501548359000.0
            }
        },
        "nReturned" : 43,
        "executionTimeMillisEstimate" : 1431,
        "works" : 222,
        "advanced" : 43,
        "needTime" : 177,
        "needYield" : 0,
        "saveState" : 64,
        "restoreState" : 64,
        "isEOF" : 1,
        "invalidates" : 0,
        "docsExamined" : 219,
        "alreadyHasObj" : 0,
        "inputStage" : {
            "stage" : "IXSCAN",
            "nReturned" : 219,
            "executionTimeMillisEstimate" : 71,
            "works" : 221,
            "advanced" : 219,
            "needTime" : 1,
            "needYield" : 0,
            "saveState" : 64,
            "restoreState" : 64,
            "isEOF" : 1,
            "invalidates" : 0,
            "keyPattern" : {
                "rack_name" : 1
            },
            "indexName" : "rack",
            "isMultiKey" : false,
            "multiKeyPaths" : {
                "rack_name" : []
            },
            "isUnique" : false,
            "isSparse" : false,
            "isPartial" : false,
            "indexVersion" : 2,
            "direction" : "forward",
            "indexBounds" : {
                "rack_name" : [ 
                    "[\"helena2\", \"helena2\"]", 
                    "[\"providence1\", \"providence1\"]"
                ]
            },
            "keysExamined" : 221,
            "seeks" : 2,
            "dupsTested" : 0,
            "dupsDropped" : 0,
            "seenInvalidated" : 0
        }
    },
    "allPlansExecution" : [ 
        {
            "nReturned" : 2,
            "executionTimeMillisEstimate" : 31,
            "totalKeysExamined" : 221,
            "totalDocsExamined" : 221,
            "executionStages" : {
                "stage" : "FETCH",
                "filter" : {
                    "rack_name" : {
                        "$in" : [ 
                            "helena2", 
                            "providence1"
                        ]
                    }
                },
                "nReturned" : 2,
                "executionTimeMillisEstimate" : 31,
                "works" : 221,
                "advanced" : 2,
                "needTime" : 219,
                "needYield" : 0,
                "saveState" : 64,
                "restoreState" : 64,
                "isEOF" : 0,
                "invalidates" : 0,
                "docsExamined" : 221,
                "alreadyHasObj" : 0,
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "nReturned" : 221,
                    "executionTimeMillisEstimate" : 10,
                    "works" : 221,
                    "advanced" : 221,
                    "needTime" : 0,
                    "needYield" : 0,
                    "saveState" : 64,
                    "restoreState" : 64,
                    "isEOF" : 0,
                    "invalidates" : 0,
                    "keyPattern" : {
                        "timestamp" : 1
                    },
                    "indexName" : "time",
                    "isMultiKey" : false,
                    "multiKeyPaths" : {
                        "timestamp" : []
                    },
                    "isUnique" : false,
                    "isSparse" : false,
                    "isPartial" : false,
                    "indexVersion" : 2,
                    "direction" : "forward",
                    "indexBounds" : {
                        "timestamp" : [ 
                            "(1501548359000.0, inf.0]"
                        ]
                    },
                    "keysExamined" : 221,
                    "seeks" : 1,
                    "dupsTested" : 0,
                    "dupsDropped" : 0,
                    "seenInvalidated" : 0
                }
            }
        }, 
        {
            "nReturned" : 43,
            "executionTimeMillisEstimate" : 1431,
            "totalKeysExamined" : 221,
            "totalDocsExamined" : 219,
            "executionStages" : {
                "stage" : "FETCH",
                "filter" : {
                    "timestamp" : {
                        "$gt" : 1501548359000.0
                    }
                },
                "nReturned" : 43,
                "executionTimeMillisEstimate" : 1431,
                "works" : 221,
                "advanced" : 43,
                "needTime" : 177,
                "needYield" : 0,
                "saveState" : 64,
                "restoreState" : 64,
                "isEOF" : 1,
                "invalidates" : 0,
                "docsExamined" : 219,
                "alreadyHasObj" : 0,
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "nReturned" : 219,
                    "executionTimeMillisEstimate" : 71,
                    "works" : 221,
                    "advanced" : 219,
                    "needTime" : 1,
                    "needYield" : 0,
                    "saveState" : 64,
                    "restoreState" : 64,
                    "isEOF" : 1,
                    "invalidates" : 0,
                    "keyPattern" : {
                        "rack_name" : 1
                    },
                    "indexName" : "rack",
                    "isMultiKey" : false,
                    "multiKeyPaths" : {
                        "rack_name" : []
                    },
                    "isUnique" : false,
                    "isSparse" : false,
                    "isPartial" : false,
                    "indexVersion" : 2,
                    "direction" : "forward",
                    "indexBounds" : {
                        "rack_name" : [ 
                            "[\"helena2\", \"helena2\"]", 
                            "[\"providence1\", \"providence1\"]"
                        ]
                    },
                    "keysExamined" : 221,
                    "seeks" : 2,
                    "dupsTested" : 0,
                    "dupsDropped" : 0,
                    "seenInvalidated" : 0
                }
            }
        }
    ]
},
"serverInfo" : {
    "host" : "dtauto-sna01.mascorp.com",
    "port" : 27017,
    "version" : "3.4.4",
    "gitVersion" : "888390515874a9debd1b6c5d36559ca86b44babd"
},
"ok" : 1.0
}

有两点我不明白:

1. 当您查看 AllPlansExecution 时，nReturned 键有不同每个计划的值(value)。第二个方案(索引:rack)实际上是赢家计划返回43个结果，为实际返回结果整个查询，但是第一个 2 nReturned 结果是什么

2. 更多的挑战在于为什么第一个计划(索引:被拒绝计划的时间)已被报告为较少executionTimeMillis value 31 比 winner planexecutionTimeMillis 1431?

这是怎么回事？

Answer 1

这在 allPlansExecution Mode 中进行了解释文档页面。换句话说:

MongoDB runs the query optimizer to choose the winning plan and executes the winning plan to completion. In "allPlansExecution" mode, MongoDB returns statistics describing the execution of the winning plan as well as statistics for the other candidate plans captured during plan selection.

在计划选择期间，如果有多个索引可以满足查询，MongoDB 将使用所有有效计划进行试验，以确定哪一个执行得最好。参见 Query Plans有关此过程的详细信息。

从 MongoDB 3.4.6 开始，计划选择涉及在“竞赛”中并行运行候选计划，并查看哪个候选计划首先返回 101 个结果。在上面的示例中，当获胜的计划在比赛中返回 101 个结果时，失败的计划只获得了 2 个结果。获胜的计划然后被执行到完成。这就是为什么失败的计划只显示nReturned: 2的原因。在统计数据中。

执行此“竞赛”是因为如果有两个看起来相同的计划，由于 JSON 文档的灵 active (不像 SQL，其中表的结构是已知的)。当然，MongoDB 完全有可能猜测错误，并最终得到一个性能不佳的计划，因为这是一个经验过程。出于这个原因，最好创建支持您的查询的索引，这样 MongoDB 就不必猜测。否则，您可以使用 hint()告诉 MongoDB 哪个索引用于某个查询。

因此:

• 获胜方案的统计是实际查询的结果统计。
• 失败计划的统计信息仅显示查询计划试运行的统计信息。
• 计划选择包括跑一场“比赛”到 101 个结果。仅当有多个索引可以满足查询时才会执行此竞赛。

注意 1:您看到的两个计划都不是很好。中标方案显示"nReturned" : 43 , "totalKeysExamined" : 221 , 和 "totalDocsExamined" : 219 .这意味着 MongoDB 只需检查 219 个文档即可返回其中的 43 个:效率仅为 20%。理想情况下，您希望拥有 nReturned数字等于 totalDocsExamined .

注意 2:尝试创建复合索引 {'rack_name': 1, 'timestamp': 1} .使用相同的查询，您应该获得更好的效率数字。

注释 3:请注意，由于 allPlansExecution指定后，所有 统计数据将由 MongoDB 及时返回给您以确保完整性，但它与最终的 nReturned 没有任何关系。结果。这是一个被拒绝的计划，nReturned: 2数字可能会造成混淆。如果您使用 executionStats，您将看不到此统计信息环境。主要是 allPlansExecution用于微调和确定某些计划被拒绝的原因。