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

问题描述

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

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

我想查询指定类型和partNumbers的文档，所以我写了这个查询

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

和索引

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

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

查询非常快（需要 1 毫秒），但如果我交换“part”和“part1”，则需要很长时间（在我的数据集上超过 2 秒）。

看起来 mongodb 仅在我在查询的“$all”函数中传递的第一个元素上使用索引。

这是执行快速查询的 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

}

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

标签： mongodbindexing

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

问题描述

解决方案

推荐阅读