欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MongoDB中哪几种情况下的索引选择策略

程序员文章站 2022-06-09 23:50:29
目录一、mongodb如何选择索引二、数据准备三、正则对index的使用四、$or从句对索引的利用五、sort对索引的利用六、搜索数据对索引命中的影响总结一、mongodb如何选择索引如果我们在col...

一、mongodb如何选择索引

如果我们在collection建了5个index,那么当我们查询的时候,mongodb会根据查询语句的筛选条件、sort排序等来定位可以使用的index作为候选索引;然后mongodb会创建对应数量的查询计划,并分别使用不同线程执行查询计划,最终会选择一个执行最快的index;但是这个选择也不是一成不变的,后续还会有一段时间根据实际执行情况动态调整;

MongoDB中哪几种情况下的索引选择策略

二、数据准备

for(let i = 0;i<1000000;i++){
    db.users.insertone({
        "id":i,
        "name":'user'+i,
        "age":math.floor(math.random()*120),
        "created":new date(isodate().gettime() - 1000 * 60*i)
    });
}

三、正则对index的使用

mongodb支持正则查询,在特定的情况其也是可以利用index获得查询性能的提升;

虽然mongdb执行正则会最大限度的使用index,但是不同的用法还是会影响对index的利用程度的;

执行以下普通正则表达式

从queryplanner.winningplan部分的collscan,可以看到正则表达式默认会进行全表的扫描;

从executionstats.executionstages部分可以看到collscan共扫描了1000000个文档,并返回1111个文档,总耗时794ms;

db.users.find({
    name:/user999/
    }).explain('executionstats')
    
{
    "queryplanner" : {
        "plannerversion" : 1,
        "namespace" : "test.users",
        "indexfilterset" : false,        
        "winningplan" : {
            "stage" : "collscan",
            "filter" : {
                "name" : {
                    "$regex" : "user999"
                }
            },
            "direction" : "forward"
        },
        "rejectedplans" : [ ]
    },
    "executionstats" : {
        "executionsuccess" : true,
        "nreturned" : 1111,
        "executiontimemillis" : 909,
        "totalkeysexamined" : 0,
        "totaldocsexamined" : 1000000,
        "executionstages" : {
            "stage" : "collscan",
            "filter" : {
                "name" : {
                    "$regex" : "user999"
                }
            },
            "nreturned" : 1111,
            "executiontimemillisestimate" : 794,
            "works" : 1000002,
            "advanced" : 1111,
            "needtime" : 998890,
            "needyield" : 0,
            "savestate" : 7830,
            "restorestate" : 7830,
            "iseof" : 1,
            "invalidates" : 0,
            "direction" : "forward",
            "docsexamined" : 1000000
        }
    }
}

创建一个包含name的index;

db.users.createindex({name:1})

再次执行上边的查询,可以看到使用了我们新建的name_1索引;但是从执行状态来看,还是扫描了全体的索引的key,并不能很好的利用index;

{
    "queryplanner" : {
        "plannerversion" : 1,
        "namespace" : "test.users",
        "indexfilterset" : false,
        "parsedquery" : {
            "name" : {
                "$regex" : "user999"
            }
        },
        "winningplan" : {
            "stage" : "fetch",
            "inputstage" : {
                "stage" : "ixscan",
                "filter" : {
                    "name" : {
                        "$regex" : "user999"
                    }
                },
                "keypattern" : {
                    "name" : 1
                },
                "indexname" : "name_1"                
            }
        },
        "rejectedplans" : [ ]
    },
    "executionstats" : {
        "executionsuccess" : true,
        "nreturned" : 1111,
        "executiontimemillis" : 971,
        "totalkeysexamined" : 1000000,
        "totaldocsexamined" : 1111,
        "executionstages" : {
            "stage" : "fetch",
            "nreturned" : 1111,
            "executiontimemillisestimate" : 887,            
            "docsexamined" : 1111,
            "alreadyhasobj" : 0,
            "inputstage" : {
                "stage" : "ixscan",
                "filter" : {
                    "name" : {
                        "$regex" : "user999"
                    }
                },
                "nreturned" : 1111,
                "executiontimemillisestimate" : 876,              
                "keypattern" : {
                    "name" : 1
                },
                "indexname" : "name_1",              
                "keysexamined" : 1000000
            }
        }
    }
}

使用前缀匹配的话可以最大限度的利用index,从执行状态可以看到只检测了1111个index key;

db.users.find({
    name:/^user999/
    }).explain('executionstats')
    
{
    "queryplanner" : {
        "plannerversion" : 1,
        "namespace" : "test.users",
        "indexfilterset" : false,
        "parsedquery" : {
            "name" : {
                "$regex" : "^user999"
            }
        },
        "winningplan" : {
            "stage" : "fetch",
            "inputstage" : {
                "stage" : "ixscan",
                "keypattern" : {
                    "name" : 1
                },
                "indexname" : "name_1"                
            }
        },
        "rejectedplans" : [ ]
    },
    "executionstats" : {
        "executionsuccess" : true,
        "nreturned" : 1111,
        "executiontimemillis" : 2,
        "totalkeysexamined" : 1111,
        "totaldocsexamined" : 1111,
        "executionstages" : {
            "stage" : "fetch",
            "nreturned" : 1111,
            "executiontimemillisestimate" : 0
            "docsexamined" : 1111            
            "inputstage" : {
                "stage" : "ixscan",
                "nreturned" : 1111,
                "executiontimemillisestimate" : 0,
                "indexname" : "name_1",
                "keysexamined" : 1111
            }
        }
    }
}
    

即使是前缀匹配,如果忽略大小写的话也无法充分利用index了;

db.users.find({
    name:/^user999/i
    }).explain('executionstats')
    
{
    "queryplanner" : {
        "plannerversion" : 1,
        "namespace" : "test.users",
        "indexfilterset" : false,
        "parsedquery" : {
            "name" : {
                "$regex" : "user999",
                "$options" : "i"
            }
        },
        "winningplan" : {
            "stage" : "fetch",
            "inputstage" : {
                "stage" : "ixscan",
                "filter" : {
                    "name" : {
                        "$regex" : "user999",
                        "$options" : "i"
                    }
                },
                "keypattern" : {
                    "name" : 1
                },
                "indexname" : "name_1"
            }
        },
        "rejectedplans" : [ ]
    },
    "executionstats" : {
        "executionsuccess" : true,
        "nreturned" : 1111,
        "executiontimemillis" : 943,
        "totalkeysexamined" : 1000000,
        "totaldocsexamined" : 1111,
        "executionstages" : {
            "stage" : "fetch",
            "nreturned" : 1111,
            "executiontimemillisestimate" : 833,
            "works" : 1000001,
            "inputstage" : {
                "stage" : "ixscan",
                "filter" : {
                    "name" : {
                        "$regex" : "user999",
                        "$options" : "i"
                    }
                },
                "nreturned" : 1111,
                "executiontimemillisestimate" : 833,
                "keypattern" : {
                    "name" : 1
                },
                "indexname" : "name_1"
                "keysexamined" : 1000000
            }
        }
    }
}

四、$or从句对索引的利用

mongodb执行$or从句的时候,会将所有的从句作为逻辑的整体,要不就都使用index,要不就都进行全表扫描;

执行以下的查询语句;

db.users.find({
    $or:[
        {name:/^user666/},
        {age:{$gte:80}}
    ]
    }).explain('executionstats')

在只有name_1这个index的时候,我们可以看到mongodb进行了全表扫描,全表扫描的时候进行$or从句的过滤;

{
    "queryplanner" : {
        "plannerversion" : 1,
        "namespace" : "test.users",
        "indexfilterset" : false,
        "parsedquery" : {
            "$or" : [
                {
                    "age" : {
                        "$gte" : 20
                    }
                },
                {
                    "name" : {
                        "$regex" : "^user666"
                    }
                }
            ]
        },
        "winningplan" : {
            "stage" : "subplan",
            "inputstage" : {
                "stage" : "collscan",
                "filter" : {
                    "$or" : [
                        {
                            "age" : {
                                "$gte" : 20
                            }
                        },
                        {
                            "name" : {
                                "$regex" : "^user666"
                            }
                        }
                    ]
                },
                "direction" : "forward"
            }
        },
        "rejectedplans" : [ ]
    },
    "executionstats" : {
        "executionsuccess" : true,
        "nreturned" : 833995,
        "executiontimemillis" : 576,
        "totalkeysexamined" : 0,
        "totaldocsexamined" : 1000000,
        "executionstages" : {
            "stage" : "subplan",
            "nreturned" : 833995,
            "executiontimemillisestimate" : 447,           
            "inputstage" : {
                "stage" : "collscan",
                "filter" : {
                    "$or" : [
                        {
                            "age" : {
                                "$gte" : 20
                            }
                        },
                        {
                            "name" : {
                                "$regex" : "^user666"
                            }
                        }
                    ]
                },
                "nreturned" : 833995,
                "executiontimemillisestimate" : 447,               
                "docsexamined" : 1000000
            }
        }
    }
}

我们对name字段新建一个index;

db.users.createindex({age:1})

再次执行以上的查询语句,这次可以看到每个从句都利用了index,并且每个从句会单独执行并最终进行or操作;

{
    "queryplanner" : {
        "plannerversion" : 1,
        "namespace" : "test.users",
        "indexfilterset" : false,
        "parsedquery" : {
            "$or" : [
                {
                    "age" : {
                        "$gte" : 80
                    }
                },
                {
                    "name" : {
                        "$regex" : "^user666"
                    }
                }
            ]
        },
        "winningplan" : {
            "stage" : "subplan",
            "inputstage" : {
                "stage" : "fetch",
                "inputstage" : {
                    "stage" : "or",
                    "inputstages" : [
                        {
                            "stage" : "ixscan",
                            "keypattern" : {
                                "name" : 1
                            },
                            "indexname" : "name_1",
                            "ismultikey" : false,
                            "multikeypaths" : {
                                "name" : [ ]
                            },
                            "isunique" : false,
                            "issparse" : false,
                            "ispartial" : false,
                            "indexversion" : 2,
                            "direction" : "forward",
                            "indexbounds" : {
                                "name" : [
                                    "[\"user666\", \"user667\")",
                                    "[/^user666/, /^user666/]"
                                ]
                            }
                        },
                        {
                            "stage" : "ixscan",
                            "keypattern" : {
                                "age" : 1
                            },
                            "indexname" : "age_1",
                            "ismultikey" : false,
                            "multikeypaths" : {
                                "age" : [ ]
                            },
                            "isunique" : false,
                            "issparse" : false,
                            "ispartial" : false,
                            "indexversion" : 2,
                            "direction" : "forward",
                            "indexbounds" : {
                                "age" : [
                                    "[80.0, inf.0]"
                                ]
                            }
                        }
                    ]
                }
            }
        },
        "rejectedplans" : [ ]
    },
    "executionstats" : {
        "executionsuccess" : true,
        "nreturned" : 333736,
        "executiontimemillis" : 741,
        "totalkeysexamined" : 334102,
        "totaldocsexamined" : 333736,
        "executionstages" : {
            "stage" : "subplan",
            "nreturned" : 333736,
            "executiontimemillisestimate" : 703,
            "inputstage" : {
                "stage" : "fetch",
                "nreturned" : 333736,
                "executiontimemillisestimate" : 682
                "docsexamined" : 333736,                
                "inputstage" : {
                    "stage" : "or",
                    "nreturned" : 333736,
                    "executiontimemillisestimate" : 366,
                    "inputstages" : [
                        {
                            "stage" : "ixscan",
                            "nreturned" : 1111,
                            "executiontimemillisestimate" : 0,
                            "keypattern" : {
                                "name" : 1
                            },
                            "indexname" : "name_1",
                            "indexbounds" : {
                                "name" : [
                                    "[\"user666\", \"user667\")",
                                    "[/^user666/, /^user666/]"
                                ]
                            },
                            "keysexamined" : 1112
                        },
                        {
                            "stage" : "ixscan",
                            "nreturned" : 332990,
                            "executiontimemillisestimate" : 212,                          
                            "keypattern" : {
                                "age" : 1
                            },
                            "indexname" : "age_1",                           
                            "indexbounds" : {
                                "age" : [
                                    "[80.0, inf.0]"
                                ]
                            },
                            "keysexamined" : 332990
                        }
                    ]
                }
            }
        }
    }
}

五、sort对索引的利用

如果sort操作无法利用index,则mongodb就会在内存中排序数据,并且数据量一大就会报错;

db.users.find().sort({created: -1}).explain('executionstats')

{
    "queryplanner" : {
        "plannerversion" : 1,
        "namespace" : "test.users",
        "indexfilterset" : false,
        "parsedquery" : {
            
        },
        "winningplan" : {
            "stage" : "sort",
            "sortpattern" : {
                "created" : -1
            },
            "inputstage" : {
                "stage" : "sort_key_generator",
                "inputstage" : {
                    "stage" : "collscan",
                    "direction" : "forward"
                }
            }
        },
        "rejectedplans" : [ ]
    },
    "executionstats" : {
        "executionsuccess" : false,
        "errormessage" : "exec error resulting in state failure :: caused by :: sort operation used more than the maximum 33554432 bytes of ram. add an index, or specify a smaller limit.",
        "errorcode" : 96,
        "nreturned" : 0,
        "executiontimemillis" : 959,
        "totalkeysexamined" : 0,
        "totaldocsexamined" : 361996,
        "executionstages" : {
            "stage" : "sort",
            "nreturned" : 0,
            "executiontimemillisestimate" : 922,
            "sortpattern" : {
                "created" : -1
            },
            "memusage" : 33554518,
            "memlimit" : 33554432,
            "inputstage" : {
                "stage" : "sort_key_generator",
                "nreturned" : 361996,
                "executiontimemillisestimate" : 590,
                "inputstage" : {
                    "stage" : "collscan",
                    "nreturned" : 361996,
                    "executiontimemillisestimate" : 147,
                    "direction" : "forward",
                    "docsexamined" : 361996
                }
            }
        }
    }
}

如果是单字段index,sort从两个方向都可以充分利用index;可以看到mongodb直接按照index的顺序返回结果,直接就没有sort阶段了;

db.users.find().sort({name: -1}).explain('executionstats')
      
{
    "queryplanner" : {
        "plannerversion" : 1,
        "namespace" : "test.users",
        "indexfilterset" : false,
        "parsedquery" : {
            
        },
        "winningplan" : {
            "stage" : "fetch",
            "inputstage" : {
                "stage" : "ixscan",
                "keypattern" : {
                    "name" : 1
                },
                "indexname" : "name_1",
                "direction" : "backward",
                "indexbounds" : {
                    "name" : [
                        "[maxkey, minkey]"
                    ]
                }
            }
        },
        "rejectedplans" : [ ]
    },
    "executionstats" : {
        "executionsuccess" : true,
        "nreturned" : 1000000,
        "executiontimemillis" : 1317,
        "totalkeysexamined" : 1000000,
        "totaldocsexamined" : 1000000,
        "executionstages" : {
            "stage" : "fetch",
            "nreturned" : 1000000,
            "executiontimemillisestimate" : 1180,
            "inputstage" : {
                "stage" : "ixscan",
                "nreturned" : 1000000,
                "executiontimemillisestimate" : 560,
                "keypattern" : {
                    "name" : 1
                },
                "indexname" : "name_1",
                "ismultikey" : false,
                "multikeypaths" : {
                    "name" : [ ]
                },
                "isunique" : false,
                "issparse" : false,
                "ispartial" : false,
                "indexversion" : 2,
                "direction" : "backward",
                "indexbounds" : {
                    "name" : [
                        "[maxkey, minkey]"
                    ]
                },
                "keysexamined" : 1000000,
                "seeks" : 1,
                "dupstested" : 0,
                "dupsdropped" : 0,
                "seeninvalidated" : 0
            }
        }
    }
}
  

对于复合索引,sort除了可以从整体上从两个方向利用index,也可以利用index的前缀索引和非前缀局部索引;

新建复合索引

db.users.createindex({created:-1, name:1, age:1})

按照复合索引的反方向进行整体排序;

db.users.find().sort({created:1, name:-1, age:-1}).explain('executionstats')

{
    "queryplanner" : {
        "plannerversion" : 1,
        "namespace" : "test.users",
        "indexfilterset" : false,
        "parsedquery" : {
            
        },
        "winningplan" : {
            "stage" : "fetch",
            "inputstage" : {
                "stage" : "ixscan",
                "keypattern" : {
                    "created" : -1,
                    "name" : 1,
                    "age" : 1
                },
                "indexname" : "created_-1_name_1_age_1",
                "ismultikey" : false,
                "multikeypaths" : {
                    "created" : [ ],
                    "name" : [ ],
                    "age" : [ ]
                },
                "isunique" : false,
                "issparse" : false,
                "ispartial" : false,
                "indexversion" : 2,
                "direction" : "backward",
                "indexbounds" : {
                    "created" : [
                        "[minkey, maxkey]"
                    ],
                    "name" : [
                        "[maxkey, minkey]"
                    ],
                    "age" : [
                        "[maxkey, minkey]"
                    ]
                }
            }
        },
        "rejectedplans" : [ ]
    },
    "executionstats" : {
        "executionsuccess" : true,
        "nreturned" : 1000000,
        "executiontimemillis" : 1518,
        "totalkeysexamined" : 1000000,
        "totaldocsexamined" : 1000000,
        "executionstages" : {
            "stage" : "fetch",
            "nreturned" : 1000000,
            "executiontimemillisestimate" : 1364,
            "docsexamined" : 1000000,
            "inputstage" : {
                "stage" : "ixscan",
                "nreturned" : 1000000,
                "executiontimemillisestimate" : 816,
                "keypattern" : {
                    "created" : -1,
                    "name" : 1,
                    "age" : 1
                },
                "indexname" : "created_-1_name_1_age_1",
                "ismultikey" : false,
                "multikeypaths" : {
                    "created" : [ ],
                    "name" : [ ],
                    "age" : [ ]
                },
                "isunique" : false,
                "issparse" : false,
                "ispartial" : false,
                "indexversion" : 2,
                "direction" : "backward",
                "indexbounds" : {
                    "created" : [
                        "[minkey, maxkey]"
                    ],
                    "name" : [
                        "[maxkey, minkey]"
                    ],
                    "age" : [
                        "[maxkey, minkey]"
                    ]
                },
                "keysexamined" : 1000000
            }
        }
    }
}

排序使用索引前缀,也需要保证字段的顺序,但是可以反方向排序;

db.users.find().sort({created:1, name:-1, age:-1}).explain('executionstats')

{
    "queryplanner" : {
        "plannerversion" : 1,
        "namespace" : "test.users",
        "indexfilterset" : false,
        "parsedquery" : {
            
        },
        "winningplan" : {
            "stage" : "fetch",
            "inputstage" : {
                "stage" : "ixscan",
                "keypattern" : {
                    "created" : -1,
                    "name" : 1,
                    "age" : 1
                },
                "indexname" : "created_-1_name_1_age_1",
                "ismultikey" : false,
                "multikeypaths" : {
                    "created" : [ ],
                    "name" : [ ],
                    "age" : [ ]
                },
                "isunique" : false,
                "issparse" : false,
                "ispartial" : false,
                "indexversion" : 2,
                "direction" : "backward",
                "indexbounds" : {
                    "created" : [
                        "[minkey, maxkey]"
                    ],
                    "name" : [
                        "[maxkey, minkey]"
                    ],
                    "age" : [
                        "[maxkey, minkey]"
                    ]
                }
            }
        },
        "rejectedplans" : [ ]
    },
    "executionstats" : {
        "executionsuccess" : true,
        "nreturned" : 1000000,
        "executiontimemillis" : 1487,
        "totalkeysexamined" : 1000000,
        "totaldocsexamined" : 1000000,
        "executionstages" : {
            "stage" : "fetch",
            "nreturned" : 1000000,
            "executiontimemillisestimate" : 1339,
            "works" : 1000001,
            "advanced" : 1000000,
            "needtime" : 0,
            "needyield" : 0,
            "savestate" : 7845,
            "restorestate" : 7845,
            "iseof" : 1,
            "invalidates" : 0,
            "docsexamined" : 1000000,
            "alreadyhasobj" : 0,
            "inputstage" : {
                "stage" : "ixscan",
                "nreturned" : 1000000,
                "executiontimemillisestimate" : 769,
                "works" : 1000001,
                "advanced" : 1000000,
                "needtime" : 0,
                "needyield" : 0,
                "savestate" : 7845,
                "restorestate" : 7845,
                "iseof" : 1,
                "invalidates" : 0,
                "keypattern" : {
                    "created" : -1,
                    "name" : 1,
                    "age" : 1
                },
                "indexname" : "created_-1_name_1_age_1",
                "ismultikey" : false,
                "multikeypaths" : {
                    "created" : [ ],
                    "name" : [ ],
                    "age" : [ ]
                },
                "isunique" : false,
                "issparse" : false,
                "ispartial" : false,
                "indexversion" : 2,
                "direction" : "backward",
                "indexbounds" : {
                    "created" : [
                        "[minkey, maxkey]"
                    ],
                    "name" : [
                        "[maxkey, minkey]"
                    ],
                    "age" : [
                        "[maxkey, minkey]"
                    ]
                },
                "keysexamined" : 1000000,
                "seeks" : 1,
                "dupstested" : 0,
                "dupsdropped" : 0,
                "seeninvalidated" : 0
            }
        }
    }
}

排序如果使用的是非前缀的局部字典排序,name需要保证前边的字段是等值筛选操作才行;

db.users.find({created:new date("2021-10-30t08:17:01.184z")}).sort({name:-1}).explain('executionstats')

{
    "queryplanner" : {
        "plannerversion" : 1,
        "namespace" : "test.users",
        "indexfilterset" : false,
        "parsedquery" : {
            "created" : {
                "$eq" : isodate("2021-10-30t08:17:01.184z")
            }
        },
        "winningplan" : {
            "stage" : "fetch",
            "inputstage" : {
                "stage" : "ixscan",
                "keypattern" : {
                    "created" : -1,
                    "name" : 1,
                    "age" : 1
                },
                "indexname" : "created_-1_name_1_age_1",
                "ismultikey" : false,
                "multikeypaths" : {
                    "created" : [ ],
                    "name" : [ ],
                    "age" : [ ]
                },
                "isunique" : false,
                "issparse" : false,
                "ispartial" : false,
                "indexversion" : 2,
                "direction" : "backward",
                "indexbounds" : {
                    "created" : [
                        "[new date(1635581821184), new date(1635581821184)]"
                    ],
                    "name" : [
                        "[maxkey, minkey]"
                    ],
                    "age" : [
                        "[maxkey, minkey]"
                    ]
                }
            }
        },
        "rejectedplans" : [ ]
    },
    "executionstats" : {
        "executionsuccess" : true,
        "nreturned" : 0,
        "executiontimemillis" : 0,
        "totalkeysexamined" : 0,
        "totaldocsexamined" : 0,
        "executionstages" : {
            "stage" : "fetch",
            "nreturned" : 0,
            "executiontimemillisestimate" : 0,
            "works" : 1,
            "advanced" : 0,
            "needtime" : 0,
            "needyield" : 0,
            "savestate" : 0,
            "restorestate" : 0,
            "iseof" : 1,
            "invalidates" : 0,
            "docsexamined" : 0,
            "alreadyhasobj" : 0,
            "inputstage" : {
                "stage" : "ixscan",
                "nreturned" : 0,
                "executiontimemillisestimate" : 0,
                "works" : 1,
                "advanced" : 0,
                "needtime" : 0,
                "needyield" : 0,
                "savestate" : 0,
                "restorestate" : 0,
                "iseof" : 1,
                "invalidates" : 0,
                "keypattern" : {
                    "created" : -1,
                    "name" : 1,
                    "age" : 1
                },
                "indexname" : "created_-1_name_1_age_1",
                "ismultikey" : false,
                "multikeypaths" : {
                    "created" : [ ],
                    "name" : [ ],
                    "age" : [ ]
                },
                "isunique" : false,
                "issparse" : false,
                "ispartial" : false,
                "indexversion" : 2,
                "direction" : "backward",
                "indexbounds" : {
                    "created" : [
                        "[new date(1635581821184), new date(1635581821184)]"
                    ],
                    "name" : [
                        "[maxkey, minkey]"
                    ],
                    "age" : [
                        "[maxkey, minkey]"
                    ]
                },
                "keysexamined" : 0,
                "seeks" : 1,
                "dupstested" : 0,
                "dupsdropped" : 0,
                "seeninvalidated" : 0
            }
        }
    }
}

六、搜索数据对索引命中的影响

mongodb对index的选择是受到实际场景的数据影响比较大的,即与实际数据的分布规律有关,也跟实际筛选出来的数据有关系;所以我们对索引的优化和测试都需要考虑实际的数据场景才行;

由于name的字段值筛选出来的key太多,不能充分利用index,所以mongodb拒绝了name_1并选择了age_1;

db.users.find({
        name:/^user/,
        age:{$gte:110}
    }).explain('executionstats')
    
{
    "queryplanner" : {
        "plannerversion" : 1,
        "namespace" : "test.users",
        "indexfilterset" : false,
        "parsedquery" : {
            "$and" : [
                {
                    "age" : {
                        "$gte" : 110
                    }
                },
                {
                    "name" : {
                        "$regex" : "^user"
                    }
                }
            ]
        },
        "winningplan" : {
            "stage" : "fetch",
            "filter" : {
                "name" : {
                    "$regex" : "^user"
                }
            },
            "inputstage" : {
                "stage" : "ixscan",
                "keypattern" : {
                    "age" : 1
                },
                "indexname" : "age_1",
                "ismultikey" : false,
                "multikeypaths" : {
                    "age" : [ ]
                },
                "isunique" : false,
                "issparse" : false,
                "ispartial" : false,
                "indexversion" : 2,
                "direction" : "forward",
                "indexbounds" : {
                    "age" : [
                        "[110.0, inf.0]"
                    ]
                }
            }
        },
        "rejectedplans" : [
            {
                "stage" : "fetch",
                "filter" : {
                    "age" : {
                        "$gte" : 110
                    }
                },
                "inputstage" : {
                    "stage" : "ixscan",
                    "keypattern" : {
                        "name" : 1
                    },
                    "indexname" : "name_1",
                    "ismultikey" : false,
                    "multikeypaths" : {
                        "name" : [ ]
                    },
                    "isunique" : false,
                    "issparse" : false,
                    "ispartial" : false,
                    "indexversion" : 2,
                    "direction" : "forward",
                    "indexbounds" : {
                        "name" : [
                            "[\"user\", \"uses\")",
                            "[/^user/, /^user/]"
                        ]
                    }
                }
            }
        ]
    },
    "executionstats" : {
        "executionsuccess" : true,
        "nreturned" : 83215,
        "executiontimemillis" : 246,
        "totalkeysexamined" : 83215,
        "totaldocsexamined" : 83215,
        "executionstages" : {
            "stage" : "fetch",
            "filter" : {
                "name" : {
                    "$regex" : "^user"
                }
            },
            "nreturned" : 83215,
            "executiontimemillisestimate" : 232,
            "works" : 83216,
            "advanced" : 83215,
            "needtime" : 0,
            "needyield" : 0,
            "savestate" : 658,
            "restorestate" : 658,
            "iseof" : 1,
            "invalidates" : 0,
            "docsexamined" : 83215,
            "alreadyhasobj" : 0,
            "inputstage" : {
                "stage" : "ixscan",
                "nreturned" : 83215,
                "executiontimemillisestimate" : 43,
                "works" : 83216,
                "advanced" : 83215,
                "needtime" : 0,
                "needyield" : 0,
                "savestate" : 658,
                "restorestate" : 658,
                "iseof" : 1,
                "invalidates" : 0,
                "keypattern" : {
                    "age" : 1
                },
                "indexname" : "age_1",
                "ismultikey" : false,
                "multikeypaths" : {
                    "age" : [ ]
                },
                "isunique" : false,
                "issparse" : false,
                "ispartial" : false,
                "indexversion" : 2,
                "direction" : "forward",
                "indexbounds" : {
                    "age" : [
                        "[110.0, inf.0]"
                    ]
                },
                "keysexamined" : 83215,
                "seeks" : 1,
                "dupstested" : 0,
                "dupsdropped" : 0,
                "seeninvalidated" : 0
            }
        }
    }
}
    

我们修改一下name筛选条件的值,进一步缩小命中的范围,可以看到这次mongodb选择了name_1;

db.users.find({
        name:/^user8888/,
        age:{$gte:110}
    }).explain('executionstats')
    

{
    "queryplanner" : {
        "plannerversion" : 1,
        "namespace" : "test.users",
        "indexfilterset" : false,
        "parsedquery" : {
            "$and" : [
                {
                    "age" : {
                        "$gte" : 110
                    }
                },
                {
                    "name" : {
                        "$regex" : "^user8888"
                    }
                }
            ]
        },
        "winningplan" : {
            "stage" : "fetch",
            "filter" : {
                "age" : {
                    "$gte" : 110
                }
            },
            "inputstage" : {
                "stage" : "ixscan",
                "keypattern" : {
                    "name" : 1
                },
                "indexname" : "name_1",
                "ismultikey" : false,
                "multikeypaths" : {
                    "name" : [ ]
                },
                "isunique" : false,
                "issparse" : false,
                "ispartial" : false,
                "indexversion" : 2,
                "direction" : "forward",
                "indexbounds" : {
                    "name" : [
                        "[\"user8888\", \"user8889\")",
                        "[/^user8888/, /^user8888/]"
                    ]
                }
            }
        },
        "rejectedplans" : [
            {
                "stage" : "fetch",
                "filter" : {
                    "name" : {
                        "$regex" : "^user8888"
                    }
                },
                "inputstage" : {
                    "stage" : "ixscan",
                    "keypattern" : {
                        "age" : 1
                    },
                    "indexname" : "age_1",
                    "ismultikey" : false,
                    "multikeypaths" : {
                        "age" : [ ]
                    },
                    "isunique" : false,
                    "issparse" : false,
                    "ispartial" : false,
                    "indexversion" : 2,
                    "direction" : "forward",
                    "indexbounds" : {
                        "age" : [
                            "[110.0, inf.0]"
                        ]
                    }
                }
            }
        ]
    },
    "executionstats" : {
        "executionsuccess" : true,
        "nreturned" : 10,
        "executiontimemillis" : 0,
        "totalkeysexamined" : 112,
        "totaldocsexamined" : 111,
        "executionstages" : {
            "stage" : "fetch",
            "filter" : {
                "age" : {
                    "$gte" : 110
                }
            },
            "nreturned" : 10,
            "executiontimemillisestimate" : 0,
            "works" : 114,
            "advanced" : 10,
            "needtime" : 102,
            "needyield" : 0,
            "savestate" : 1,
            "restorestate" : 1,
            "iseof" : 1,
            "invalidates" : 0,
            "docsexamined" : 111,
            "alreadyhasobj" : 0,
            "inputstage" : {
                "stage" : "ixscan",
                "nreturned" : 111,
                "executiontimemillisestimate" : 0,
                "works" : 113,
                "advanced" : 111,
                "needtime" : 1,
                "needyield" : 0,
                "savestate" : 1,
                "restorestate" : 1,
                "iseof" : 1,
                "invalidates" : 0,
                "keypattern" : {
                    "name" : 1
                },
                "indexname" : "name_1",
                "ismultikey" : false,
                "multikeypaths" : {
                    "name" : [ ]
                },
                "isunique" : false,
                "issparse" : false,
                "ispartial" : false,
                "indexversion" : 2,
                "direction" : "forward",
                "indexbounds" : {
                    "name" : [
                        "[\"user8888\", \"user8889\")",
                        "[/^user8888/, /^user8888/]"
                    ]
                },
                "keysexamined" : 112,
                "seeks" : 2,
                "dupstested" : 0,
                "dupsdropped" : 0,
                "seeninvalidated" : 0
            }
        }
    }
}

总结

到此这篇关于mongodb中哪几种情况下的索引选择策略的文章就介绍到这了,更多相关mongodb索引选择策略内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持!