mongodb:使用$all时，查询执行时间取决于输入参数的顺序

Question

我有一个玩具mongodb集合，具有以下结构

{
"operation" : {
"type" : "STACK"
},
"constraints" : [{
"partNumbers" : ["part", "part_1"]
}]
}

我想查询具有指定类型和零件号的文档，所以我编写了这个查询

db.getCollection('toy').find({
"operation.type" : "STACK",
"constraints.partNumbers": {"$all": ["part_1", "part"]}
})

和索引

db.toy.ensureIndex( { 
"operation.type": 1, 
"constraints.partNumbers": 1,
})

我创建了一个包含数百万个文档的数据集，其中几乎所有文档都有"；部分"；在partNumbers数组中

查询非常快(需要1ms(，但如果我交换"；部分"；以及"；第1部分"；，它需要很长时间(在我的数据集中超过2秒(。

看起来mongodb只对我在"$所有"；查询的函数。

这是explain((对执行快速的查询的结果

{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "factorysim.robofacturingservice",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [ 
{
"constraints" : {
"$elemMatch" : {
"$and" : [ 
{
"partNumbers" : {
"$size" : 2
}
}, 
{
"constraintType" : {
"$eq" : "PART_NUMBER_CONSTRAINT"
}
}, 
{
"partNumbers" : {
"$eq" : "part_1"
}
}, 
{
"partNumbers" : {
"$eq" : "part"
}
}
]
}
}
}, 
{
"operation.type" : {
"$eq" : "STACK"
}
}
]
},
"winningPlan" : {
"stage" : "FETCH",
"filter" : {
"constraints" : {
"$elemMatch" : {
"$and" : [ 
{
"partNumbers" : {
"$eq" : "part_1"
}
}, 
{
"partNumbers" : {
"$size" : 2
}
}, 
{
"constraintType" : {
"$eq" : "PART_NUMBER_CONSTRAINT"
}
}, 
{
"partNumbers" : {
"$eq" : "part"
}
}
]
}
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"operation.type" : 1.0,
"constraints.partNumbers" : 1.0
},
"indexName" : "operation.type_1_constraints.partNumbers_1",
"isMultiKey" : true,
"multiKeyPaths" : {
"operation.type" : [],
"constraints.partNumbers" : [ 
"constraints", 
"constraints.partNumbers"
]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"operation.type" : [ 
"["STACK", "STACK"]"
],
"constraints.partNumbers" : [ 
"["part_1", "part_1"]"
]
}
}
},
"rejectedPlans" : []
},
"serverInfo" : {
"host" : "p1",
"port" : 27017,
"version" : "3.6.8",
"gitVersion" : "8e540c0b6db93ce994cc548f000900bdc740f80a"
},
"ok" : 1.0
}

对于执行慢速的

{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "factorysim.robofacturingservice",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [ 
{
"constraints" : {
"$elemMatch" : {
"$and" : [ 
{
"partNumbers" : {
"$size" : 2
}
}, 
{
"constraintType" : {
"$eq" : "PART_NUMBER_CONSTRAINT"
}
}, 
{
"partNumbers" : {
"$eq" : "part"
}
}, 
{
"partNumbers" : {
"$eq" : "part_1"
}
}
]
}
}
}, 
{
"operation.type" : {
"$eq" : "STACK"
}
}
]
},
"winningPlan" : {
"stage" : "FETCH",
"filter" : {
"constraints" : {
"$elemMatch" : {
"$and" : [ 
{
"partNumbers" : {
"$eq" : "part"
}
}, 
{
"partNumbers" : {
"$size" : 2
}
}, 
{
"constraintType" : {
"$eq" : "PART_NUMBER_CONSTRAINT"
}
}, 
{
"partNumbers" : {
"$eq" : "part_1"
}
}
]
}
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"operation.type" : 1.0,
"constraints.partNumbers" : 1.0
},
"indexName" : "operation.type_1_constraints.partNumbers_1",
"isMultiKey" : true,
"multiKeyPaths" : {
"operation.type" : [],
"constraints.partNumbers" : [ 
"constraints", 
"constraints.partNumbers"
]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"operation.type" : [ 
"["STACK", "STACK"]"
],
"constraints.partNumbers" : [ 
"["part", "part"]"
]
}
}
},
"rejectedPlans" : []
},
"serverInfo" : {
"host" : "p1",
"port" : 27017,
"version" : "3.6.8",
"gitVersion" : "8e540c0b6db93ce994cc548f000900bdc740f80a"
},
"ok" : 1.0

}

有没有一种方法可以编写独立于输入参数顺序的查询/索引组合？

Answer 1

两个查询计划看起来是相同的，因此断言

索引的使用取决于输入数组的顺序

似乎没有证据支持。