为什么mongodb不使用完整索引

Question

我有一个包含4个关键复合索引的集合：

> db.event.getIndexes()
[
    {
        "v" : 2,
        "key" : {
            "_id" : 1
        },
        "name" : "_id_",
    },
    {
        "v" : 2,
        "key" : {
            "epochWID" : 1,
            "category" : 1,
            "mos.types" : 1,
            "mos.name" : 1
        },
        "name" : "epochWID_category_motype_movalue",
    }
]

查询如下：

> db.event.explain().find({ "epochWID": 1510456188087, "category": 6, "mos.types": 9, "mos.name": "ctx_1" })
{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$and" : [
                {
                    "category" : {
                        "$eq" : 6
                    }
                },
                {
                    "epochWID" : {
                        "$eq" : 1510456188087
                    }
                },
                {
                    "mos.name" : {
                        "$eq" : "ctx_1"
                    }
                },
                {
                    "mos.types" : {
                        "$eq" : 9
                    }
                }
            ]
        },
        "winningPlan" : {
            "stage" : "FETCH",
            "filter" : {
                "mos.name" : {
                    "$eq" : "ctx_1"
                }
            },
            "inputStage" : {
                "stage" : "IXSCAN",
                "keyPattern" : {
                    "epochWID" : 1,
                    "category" : 1,
                    "mos.types" : 1,
                    "mos.name" : 1
                },
                "indexName" : "epochWID_category_motype_movalue",
                "isMultiKey" : true,
                "multiKeyPaths" : {
                    "epochWID" : [ ],
                    "category" : [ ],
                    "mos.types" : [
                        "mos",
                        "mos.types"
                    ],
                    "mos.name" : [
                        "mos"
                    ]
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "epochWID" : [
                        "[1510456188087.0, 1510456188087.0]"
                    ],
                    "category" : [
                        "[6.0, 6.0]"
                    ],
                    "mos.types" : [
                        "[9.0, 9.0]"
                    ],
                    "mos.name" : [
                        "[MinKey, MaxKey]"
                    ]
                }
            }
        },
        "rejectedPlans" : [ ]
    },
    "serverInfo" : {
        "version" : "3.4.9",
    },
    "ok" : 1
}

现在，如果您看一下计划的indexBounds:它使用的是前3个键，而不是第4个mos.name，为什么？

                "indexBounds" : {
                    "epochWID" : [
                        "[1510456188087.0, 1510456188087.0]"
                    ],
                    "category" : [
                        "[6.0, 6.0]"
                    ],
                    "mos.types" : [
                        "[9.0, 9.0]"
                    ],
                    "mos.name" : [
                        "[MinKey, MaxKey]"
                    ]
                }

Answer 1

基于https://docs.mongodb.com/manual/core/index-multikey/#compound-multikey-indexes，我们需要使用$elemMatch，因此下面的查询使用完整的索引

> db.event.explain().find({ "epochWID": 1510456188087, "category": 6, "mos": { $elemMatch: {"types": 9, "name": "ctx_1"} } })
{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$and" : [
                {
                    "mos" : {
                        "$elemMatch" : {
                            "$and" : [
                                {
                                    "name" : {
                                        "$eq" : "ctx_1"
                                    }
                                },
                                {
                                    "types" : {
                                        "$eq" : 9
                                    }
                                }
                            ]
                        }
                    }
                },
                {
                    "category" : {
                        "$eq" : 6
                    }
                },
                {
                    "epochWID" : {
                        "$eq" : 1510456188087
                    }
                }
            ]
        },
        "winningPlan" : {
            "stage" : "FETCH",
            "filter" : {
                "mos" : {
                    "$elemMatch" : {
                        "$and" : [
                            {
                                "types" : {
                                    "$eq" : 9
                                }
                            },
                            {
                                "name" : {
                                    "$eq" : "ctx_1"
                                }
                            }
                        ]
                    }
                }
            },
            "inputStage" : {
                "stage" : "IXSCAN",
                "keyPattern" : {
                    "epochWID" : 1,
                    "category" : 1,
                    "mos.types" : 1,
                    "mos.name" : 1
                },
                "indexName" : "epochWID_category_motype_movalue",
                "isMultiKey" : true,
                "multiKeyPaths" : {
                    "epochWID" : [ ],
                    "category" : [ ],
                    "mos.types" : [
                        "mos",
                        "mos.types"
                    ],
                    "mos.name" : [
                        "mos"
                    ]
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "epochWID" : [
                        "[1510456188087.0, 1510456188087.0]"
                    ],
                    "category" : [
                        "[6.0, 6.0]"
                    ],
                    "mos.types" : [
                        "[9.0, 9.0]"
                    ],
                    "mos.name" : [
                        "[\"ctx_1\", \"ctx_1\"]"
                    ]
                }
            }
        },
        "rejectedPlans" : [ ]
    },
    "serverInfo" : {
        "version" : "3.4.9",
    },
    "ok" : 1
}

编辑:我联系了MongoDb支持部门。对于多键索引和数组字段- tl;dr是-一个索引是好的，只要索引字段中只有一个包含数组值(在我的例子中是正确的)。筑巢水平并不重要。由于需要笛卡尔乘积，问题确实是平行阵列。

Answer 2

多键索引不能跨文档中的多个数组。参见文档https://docs.mongodb.com/manual/core/index-multikey/#compound-multikey-indexes中的限制和推理