mongodb - 为什么要检查具有排序和限制的索引 mongos 查询的所有键？

Question

我有一个包含 2 个分片和以下数据的 mongos 设置:

for (var i = 1; i <= 1000; i++) { 
    db.items.insert({item: "a", i_type: "x", i_id: i, price: i * 50});
    db.items.insert({item: "a", i_type: "y", i_id: i, price: i * 50});
    db.items.insert({item: "b", i_type: "x", i_id: i, price: i * 50});
    db.items.insert({item: "b", i_type: "y", i_id: i, price: i * 50});
}
db.items.createIndex({item: 1, i_type: 1, i_id: 1}, {unique: true})
db.items.createIndex({item: 1, i_type: 1, price: 1})
sh.enableSharding("test")
sh.shardCollection("test.items", {item: 1, i_type: 1})

我运行这个:

db.items.find(
    {item: "a", i_type: {$in: ["x", "y"]}},
    {_id: 0, item: 1, i_type: 1}
).sort({price: 1}).limit(10).explain("executionStats")

并获取以下executionStats:

"nReturned" : NumberInt(10), 
"executionTimeMillis" : NumberInt(22), 
"totalKeysExamined" : NumberInt(2000), 
"totalDocsExamined" : NumberInt(2000), 
"executionStages" : {
    "stage" : "SINGLE_SHARD", 
    "nReturned" : NumberInt(10), 
    "executionTimeMillis" : NumberInt(22), 
    "totalKeysExamined" : NumberInt(2000), 
    "totalDocsExamined" : NumberInt(2000), 
    "totalChildMillis" : NumberLong(21), 
    "shards" : [
        {
            "shardName" : "rs1", 
            "executionSuccess" : true, 
            "executionStages" : {
                "stage" : "PROJECTION", 
                "nReturned" : NumberInt(10), 
                "executionTimeMillisEstimate" : NumberInt(0), 
                "works" : NumberInt(2013), 
                "advanced" : NumberInt(10), 
                "needTime" : NumberInt(2002), 
                "needFetch" : NumberInt(0), 
                "saveState" : NumberInt(47), 
                "restoreState" : NumberInt(47), 
                "isEOF" : NumberInt(1), 
                "invalidates" : NumberInt(0), 
                "transformBy" : {
                    "_id" : NumberInt(0), 
                    "item" : NumberInt(1), 
                    "i_type" : NumberInt(1)
                }, 
                "inputStage" : {
                    "stage" : "SORT", 
                    "nReturned" : NumberInt(10), 
                    "executionTimeMillisEstimate" : NumberInt(0), 
                    "works" : NumberInt(2013), 
                    "advanced" : NumberInt(10), 
                    "needTime" : NumberInt(2001), 
                    "needFetch" : NumberInt(0), 
                    "saveState" : NumberInt(47), 
                    "restoreState" : NumberInt(47), 
                    "isEOF" : NumberInt(1), 
                    "invalidates" : NumberInt(0), 
                    "sortPattern" : {
                        "price" : NumberInt(1)
                    }, 
                    "memUsage" : NumberInt(850), 
                    "memLimit" : NumberInt(33554432), 
                    "limitAmount" : NumberInt(10), 
                    "inputStage" : {
                        "stage" : "FETCH", 
                        "nReturned" : NumberInt(2000), 
                        "executionTimeMillisEstimate" : NumberInt(0), 
                        "works" : NumberInt(2001), 
                        "advanced" : NumberInt(2000), 
                        "needTime" : NumberInt(0), 
                        "needFetch" : NumberInt(0), 
                        "saveState" : NumberInt(47), 
                        "restoreState" : NumberInt(47), 
                        "isEOF" : NumberInt(1), 
                        "invalidates" : NumberInt(0), 
                        "docsExamined" : NumberInt(2000), 
                        "alreadyHasObj" : NumberInt(0), 
                        "inputStage" : {
                            "stage" : "SHARDING_FILTER", 
                            "nReturned" : NumberInt(2000), 
                            "executionTimeMillisEstimate" : NumberInt(0), 
                            "works" : NumberInt(2001), 
                            "advanced" : NumberInt(2000), 
                            "needTime" : NumberInt(0), 
                            "needFetch" : NumberInt(0), 
                            "saveState" : NumberInt(47), 
                            "restoreState" : NumberInt(47), 
                            "isEOF" : NumberInt(1), 
                            "invalidates" : NumberInt(0), 
                            "chunkSkips" : NumberInt(0), 
                            "inputStage" : {
                                "stage" : "IXSCAN", 
                                "nReturned" : NumberInt(2000), 
                                "executionTimeMillisEstimate" : NumberInt(0), 
                                "works" : NumberInt(2001), 
                                "advanced" : NumberInt(2000), 
                                "needTime" : NumberInt(0), 
                                "needFetch" : NumberInt(0), 
                                "saveState" : NumberInt(47), 
                                "restoreState" : NumberInt(47), 
                                "isEOF" : NumberInt(1), 
                                "invalidates" : NumberInt(0), 
                                "keyPattern" : {
                                    "item" : NumberInt(1), 
                                    "i_type" : NumberInt(1), 
                                    "price" : NumberInt(1)
                                }, 
                                "indexName" : "item_1_i_type_1_price_1", 
                                "isMultiKey" : false, 
                                "direction" : "forward", 
                                "indexBounds" : {
                                    "item" : [
                                        "[\"a\", \"a\"]"
                                    ], 
                                    "i_type" : [
                                        "[\"x\", \"x\"]", 
                                        "[\"y\", \"y\"]"
                                    ], 
                                    "price" : [
                                        "[MinKey, MaxKey]"
                                    ]
                                }, 
                                "keysExamined" : NumberInt(2000), 
                                "dupsTested" : NumberInt(0), 
                                "dupsDropped" : NumberInt(0), 
                                "seenInvalidated" : NumberInt(0), 
                                "matchTested" : NumberInt(0)
                            }
                        }
                    }
                }
            }
        }
    ]
}

我很难理解为什么所有键都包含 {item: "a", i_type: "x"} 和 {item: "a", i_type: "y"} 需要在分片上进行检查。解释表明它正在使用适当的索引。

如果我在文档所在的副本集上运行相同的查询，我的 totalKeysExamined 是 10。如果我在 mongos 上删除单个 i_type 的排序或查询totalKeysExamined 为 10(或 11，具体取决于自动平衡)。所有这些查询计划都有 LIMIT 的 executionStage，我在上面发布的解释中明显缺失了这一点。

根据自动平衡器决定做事的方式，层次结构中的第一个 executionStage 可能是 SHARD_MERGE_SORT 而不是 SINGLE_SHARD，但即使如果查询需要从两个分片获取信息，我假设只需要检查 20 个键(每个分片 10 个)。

这是我遇到的 mongos 的局限性，我需要改进对分片键和索引的选择，还是完全不同的情况？

Answer 1

显式 $or 以我期望的方式构建查询计划。我已经提交了 bug report与此问题相关。

db.items.find(
    {$or: [{item: "a", i_type: "x"}, {item: "a", i_type: "y"}]},
    {_id: 0, item: 1, i_type: 1}
).sort({price: 1}).limit(10).explain("executionStats")