database - $or 查询时不使用 Mongodb 索引

Question

我添加了 first 创建了一个集合并创建了索引；

db.first.createIndex({a:1, b:1, c:1, d:1, e:1, f:1});

然后插入数据

db.first.insert({a:1, b:2, c:3, d:4, e:5, f:6});
db.first.insert({a:1, b:6});

当查询时

db.first.find({f: 6, a:1, c:3}).sort({b: -1}).explain();

索引被使用(IXSCAN)

{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "myproject.first",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$and" : [
                {
                    "a" : {
                        "$eq" : 1
                    }
                },
                {
                    "c" : {
                        "$eq" : 3
                    }
                },
                {
                    "f" : {
                        "$eq" : 6
                    }
                }
            ]
        },
        "winningPlan" : {
            "stage" : "FETCH",
            "inputStage" : {
                "stage" : "IXSCAN",
                "keyPattern" : {
                    "a" : 1,
                    "b" : 1,
                    "c" : 1,
                    "d" : 1,
                    "e" : 1,
                    "f" : 1
                },
                "indexName" : "a_1_b_1_c_1_d_1_e_1_f_1",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                    "a" : [ ],
                    "b" : [ ],
                    "c" : [ ],
                    "d" : [ ],
                    "e" : [ ],
                    "f" : [ ]
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "backward",
                "indexBounds" : {
                    "a" : [
                        "[1.0, 1.0]"
                    ],
                    "b" : [
                        "[MaxKey, MinKey]"
                    ],
                    "c" : [
                        "[3.0, 3.0]"
                    ],
                    "d" : [
                        "[MaxKey, MinKey]"
                    ],
                    "e" : [
                        "[MaxKey, MinKey]"
                    ],
                    "f" : [
                        "[6.0, 6.0]"
                    ]
                }
            }
        },
        "rejectedPlans" : [
            {
                "stage" : "SORT",
                "sortPattern" : {
                    "b" : -1
                },
                "inputStage" : {
                    "stage" : "SORT_KEY_GENERATOR",
                    "inputStage" : {
                        "stage" : "FETCH",
                        "filter" : {
                            "$and" : [
                                {
                                    "c" : {
                                        "$eq" : 3
                                    }
                                },
                                {
                                    "f" : {
                                        "$eq" : 6
                                    }
                                }
                            ]
                        },
                        "inputStage" : {
                            "stage" : "IXSCAN",
                            "keyPattern" : {
                                "a" : 1
                            },
                            "indexName" : "a_1",
                            "isMultiKey" : false,
                            "multiKeyPaths" : {
                                "a" : [ ]
                            },
                            "isUnique" : false,
                            "isSparse" : false,
                            "isPartial" : false,
                            "indexVersion" : 2,
                            "direction" : "forward",
                            "indexBounds" : {
                                "a" : [
                                    "[1.0, 1.0]"
                                ]
                            }
                        }
                    }
                }
            }
        ]
    },
    "serverInfo" : {
        "host" : "Manishs-MacBook-Pro.local",
        "port" : 27017,
        "version" : "3.6.4",
        "gitVersion" : "d0181a711f7e7f39e60b5aeb1dc7097bf6ae5856"
    },
    "ok" : 1
}

但是当我使用或者查询的时候

db.first.find({ $or: [{f: 6}, {a:1}]}).explain();

不使用索引而是扫描列(COLLSCAN)

{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "myproject.first",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$or" : [
                {
                    "a" : {
                        "$eq" : 1
                    }
                },
                {
                    "f" : {
                        "$eq" : 6
                    }
                }
            ]
        },
        "winningPlan" : {
            "stage" : "SUBPLAN",
            "inputStage" : {
                "stage" : "COLLSCAN",
                "filter" : {
                    "$or" : [
                        {
                            "a" : {
                                "$eq" : 1
                            }
                        },
                        {
                            "f" : {
                                "$eq" : 6
                            }
                        }
                    ]
                },
                "direction" : "forward"
            }
        },
        "rejectedPlans" : [ ]
    },
    "serverInfo" : {
        "host" : "Manishs-MacBook-Pro.local",
        "port" : 27017,
        "version" : "3.6.4",
        "gitVersion" : "d0181a711f7e7f39e60b5aeb1dc7097bf6ae5856"
    },
    "ok" : 1
}

如果我做错了什么，请告诉我。

Answer 1

您拥有复合索引的事实是索引未与 $or 一起使用的原因。

When evaluating the clauses in the $or expression, MongoDB either performs a collection scan or, if all the clauses are supported by indexes, MongoDB performs index scans. That is, for MongoDB to use indexes to evaluate an $or expression, all the clauses in the $or expression must be supported by indexes. Otherwise, MongoDB will perform a collection scan.

When using indexes with $or queries, each clause of an $or can use its own index. Consider the following query:

db.inventory.find( { $or: [ { quantity: { $lt: 20 } }, { price: 10 } ] } )

To support this query, rather than a compound index, you would create one index on quantity and another index on price:

db.inventory.createIndex( { quantity: 1 } )
db.inventory.createIndex( { price: 1 } )

$or Clauses and Indexes

---

因此只需为字段 f 和 a 添加单独的索引即可；

db.first.createIndex({a:1});
db.first.createIndex({f:1});

会让你的

db.first.find({ $or: [{f: 6}, {a:1}]})

查询使用索引。