mongodb - 为 $and 中给定查询的 mongodb 集合添加索引

Question

我有以下结构的集合。其中有一些重复的内容标题名称和值。但我需要获取确切的文档。

{
    "_id": ObjectId("573ebc7bbf50112d55c0b763"),
    "topic": "AAA",
    "contents": [{
        "headerName": "Start Year",
        "value": 1995
    }, {
        "headerName": "Program",
        "value": "AAA"
    }]
}, {
    "_id": ObjectId("573ebc7bbf50112d55c0b763"),
    "topic": "BBB",
    "contents": [{
        "headerName": "Start Year",
        "value": 1989
    }, {
        "headerName": "Program",
        "value": "BBB"
    }, {
        "headerName": "Likes",
        "value": 51
    }]
}, {
    "_id": ObjectId("573ebc7bbf50112d55c0b763"),
    "topic": "BBB",
    "contents": [{
        "headerName": "Start Year",
        "value": 1989
    }, {
        "headerName": "Program",
        "value": "BBB"
    }]
}

我需要使用以下查询获取单个文档。我如何为此添加索引。

db.collections.find({
    "$and": [{
        "topic": "BBB"
    }, {
        "contents": [{
            "headerName": "Start Year",
            "value": 1989
        }, {
            "headerName": "Program",
            "value": "BBB"
        }]
    }, {
        "contents": {
            "$size": 2
        }
    }]
})

Answer 1

在字段上创建复合索引

db.collection.createIndex( { topic:1, contents: 1 } )

请注意，与任何数据库一样，MongoDB 复合索引中的顺序很重要。如果你先用“topic”建立索引，Mongo 可以直接跳到索引中有签名主题的部分，然后从内容中进行绑定(bind)扫描

之后将您的查询更改为:

db.collection.find({
    "topic" : "BBB",        
    "contents.headerName": { "$in": [ "Start Year", "Program" ] },
    "contents.value": { "$in": [ 1989, "BBB" ] },
    "contents": { "$size": 2 }
})

输出:

{
    "_id" : ObjectId("573ee720b986a3b71e1e517b"),
    "topic" : "BBB",
    "contents" : [ 
        {
            "headerName" : "Start Year",
            "value" : 1989
        }, 
        {
            "headerName" : "Program",
            "value" : "BBB"
        }
    ]
}

---

要查看索引的性能，请运行 explain() 在查询上:

db.collection.find({
    "topic" : "BBB",        
    "contents.headerName": { "$in": [ "Start Year", "Program" ] },
    "contents.value": { "$in": [ 1989, "BBB" ] },
    "contents": { "$size": 2 }
}).explain()

输出:

/* 1 */
{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "test.collection",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$and" : [ 
                {
                    "contents" : {
                        "$size" : 2
                    }
                }, 
                {
                    "topic" : {
                        "$eq" : "BBB"
                    }
                }, 
                {
                    "contents.headerName" : {
                        "$in" : [ 
                            "Program", 
                            "Start Year"
                        ]
                    }
                }, 
                {
                    "contents.value" : {
                        "$in" : [ 
                            1989, 
                            "BBB"
                        ]
                    }
                }
            ]
        },
        "winningPlan" : {
            "stage" : "KEEP_MUTATIONS",
            "inputStage" : {
                "stage" : "FETCH",
                "filter" : {
                    "$and" : [ 
                        {
                            "contents" : {
                                "$size" : 2
                            }
                        }, 
                        {
                            "contents.headerName" : {
                                "$in" : [ 
                                    "Program", 
                                    "Start Year"
                                ]
                            }
                        }, 
                        {
                            "contents.value" : {
                                "$in" : [ 
                                    1989, 
                                    "BBB"
                                ]
                            }
                        }
                    ]
                },
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "keyPattern" : {
                        "topic" : 1,
                        "contents" : 1
                    },
                    "indexName" : "topic_1_contents_1",
                    "isMultiKey" : true,
                    "isUnique" : false,
                    "isSparse" : false,
                    "isPartial" : false,
                    "indexVersion" : 1,
                    "direction" : "forward",
                    "indexBounds" : {
                        "topic" : [ 
                            "[\"BBB\", \"BBB\"]"
                        ],
                        "contents" : [ 
                            "[MinKey, MaxKey]"
                        ]
                    }
                }
            }
        },
        "rejectedPlans" : []
    }
}