欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

MongoDB中哪幾種情況下的索引選擇策略

 更新時(shí)間:2022年01月26日 11:02:54   作者:無(wú)風(fēng)聽(tīng)海  
這篇文章主要給大家介紹了關(guān)于MongoDB中哪幾種情況下的索引選擇策略的相關(guān)資料,文中通過(guò)圖文以及實(shí)例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下

一、MongoDB如何選擇索引

如果我們?cè)贑ollection建了5個(gè)index,那么當(dāng)我們查詢(xún)的時(shí)候,MongoDB會(huì)根據(jù)查詢(xún)語(yǔ)句的篩選條件、sort排序等來(lái)定位可以使用的index作為候選索引;然后MongoDB會(huì)創(chuàng)建對(duì)應(yīng)數(shù)量的查詢(xún)計(jì)劃,并分別使用不同線(xiàn)程執(zhí)行查詢(xún)計(jì)劃,最終會(huì)選擇一個(gè)執(zhí)行最快的index;但是這個(gè)選擇也不是一成不變的,后續(xù)還會(huì)有一段時(shí)間根據(jù)實(shí)際執(zhí)行情況動(dòng)態(tài)調(diào)整;

二、數(shù)據(jù)準(zhǔn)備

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)
    });
}

三、正則對(duì)index的使用

MongoDB支持正則查詢(xún),在特定的情況其也是可以利用index獲得查詢(xún)性能的提升;

雖然MongDB執(zhí)行正則會(huì)最大限度的使用index,但是不同的用法還是會(huì)影響對(duì)index的利用程度的;

執(zhí)行以下普通正則表達(dá)式

從queryPlanner.winningPlan部分的COLLSCAN,可以看到正則表達(dá)式默認(rèn)會(huì)進(jìn)行全表的掃描;

從executionStats.executionStages部分可以看到COLLSCAN共掃描了1000000個(gè)文檔,并返回1111個(gè)文檔,總耗時(shí)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
        }
    }
}

創(chuàng)建一個(gè)包含name的index;

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

再次執(zhí)行上邊的查詢(xún),可以看到使用了我們新建的name_1索引;但是從執(zhí)行狀態(tài)來(lái)看,還是掃描了全體的索引的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
            }
        }
    }
}

使用前綴匹配的話(huà)可以最大限度的利用index,從執(zhí)行狀態(tài)可以看到只檢測(cè)了1111個(gè)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
            }
        }
    }
}
    

即使是前綴匹配,如果忽略大小寫(xiě)的話(huà)也無(wú)法充分利用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從句對(duì)索引的利用

MongoDB執(zhí)行$or從句的時(shí)候,會(huì)將所有的從句作為邏輯的整體,要不就都使用index,要不就都進(jìn)行全表掃描;

執(zhí)行以下的查詢(xún)語(yǔ)句;

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

在只有name_1這個(gè)index的時(shí)候,我們可以看到MongoDB進(jìn)行了全表掃描,全表掃描的時(shí)候進(jìn)行$or從句的過(guò)濾;

{
    "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
            }
        }
    }
}

我們對(duì)name字段新建一個(gè)index;

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

再次執(zhí)行以上的查詢(xún)語(yǔ)句,這次可以看到每個(gè)從句都利用了index,并且每個(gè)從句會(huì)單獨(dú)執(zhí)行并最終進(jìn)行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對(duì)索引的利用

如果sort操作無(wú)法利用index,則MongoDB就會(huì)在內(nèi)存中排序數(shù)據(jù),并且數(shù)據(jù)量一大就會(huì)報(bào)錯(cuò);

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從兩個(gè)方向都可以充分利用index;可以看到MongoDB直接按照index的順序返回結(jié)果,直接就沒(méi)有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
            }
        }
    }
}
  

對(duì)于復(fù)合索引,sort除了可以從整體上從兩個(gè)方向利用index,也可以利用index的前綴索引和非前綴局部索引;

新建復(fù)合索引

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

按照復(fù)合索引的反方向進(jìn)行整體排序;

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
            }
        }
    }
}

六、搜索數(shù)據(jù)對(duì)索引命中的影響

MongoDB對(duì)index的選擇是受到實(shí)際場(chǎng)景的數(shù)據(jù)影響比較大的,即與實(shí)際數(shù)據(jù)的分布規(guī)律有關(guān),也跟實(shí)際篩選出來(lái)的數(shù)據(jù)有關(guān)系;所以我們對(duì)索引的優(yōu)化和測(cè)試都需要考慮實(shí)際的數(shù)據(jù)場(chǎng)景才行;

由于name的字段值篩選出來(lái)的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篩選條件的值,進(jìn)一步縮小命中的范圍,可以看到這次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
            }
        }
    }
}

總結(jié)

到此這篇關(guān)于MongoDB中哪幾種情況下的索引選擇策略的文章就介紹到這了,更多相關(guān)MongoDB索引選擇策略?xún)?nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!

相關(guān)文章

  • 詳解MongoDB管理命令

    詳解MongoDB管理命令

    MongoDB是一個(gè)NoSQL數(shù)據(jù)庫(kù)系統(tǒng):一個(gè)數(shù)據(jù)庫(kù)可以包含多個(gè)集合(Collection),每個(gè)集合對(duì)應(yīng)于關(guān)系數(shù)據(jù)庫(kù)中的表;而每個(gè)集合中可以存儲(chǔ)一組由列標(biāo)識(shí)的記錄,列是可以自由定義的,非常靈活,由一組列標(biāo)識(shí)的實(shí)體的集合對(duì)應(yīng)于關(guān)系數(shù)據(jù)庫(kù)表中的行
    2016-01-01
  • MongoDB聚合運(yùn)算符$divide詳解

    MongoDB聚合運(yùn)算符$divide詳解

    $divide聚合運(yùn)算符返回兩個(gè)表達(dá)式相除的結(jié)果,參數(shù)通過(guò)數(shù)組傳遞給$divide運(yùn)算符,這篇文章介紹了MongoDB聚合運(yùn)算符$divide的相關(guān)知識(shí),感興趣的朋友跟隨小編一起看看吧
    2024-03-03
  • Windows系統(tǒng)下安裝Mongodb 3.2.x的步驟詳解

    Windows系統(tǒng)下安裝Mongodb 3.2.x的步驟詳解

    mongodb3.x版本有好多新功能,關(guān)于這方面參考官網(wǎng)即可,下面這篇文章主要給大家介紹了在Windows系統(tǒng)下安裝Mongodb 3.2.x的詳細(xì)步驟,文中介紹的非常詳細(xì),需要的朋友們可以參考學(xué)習(xí),下面來(lái)一起看看吧。
    2017-03-03
  • 分布式文檔存儲(chǔ)數(shù)據(jù)庫(kù)之MongoDB分片集群的問(wèn)題

    分布式文檔存儲(chǔ)數(shù)據(jù)庫(kù)之MongoDB分片集群的問(wèn)題

    這篇文章主要介紹了分布式文檔存儲(chǔ)數(shù)據(jù)庫(kù)之MongoDB分片集群的問(wèn)題,本文給大家介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或工作具有一定的參考借鑒價(jià)值,需要的朋友可以參考下
    2020-11-11
  • MongoDB添加仲裁節(jié)點(diǎn)報(bào)錯(cuò):replica set IDs do not match的解決方法

    MongoDB添加仲裁節(jié)點(diǎn)報(bào)錯(cuò):replica set IDs do not match的解決方法

    這篇文章主要給大家介紹了關(guān)于MongoDB添加仲裁節(jié)點(diǎn)報(bào)錯(cuò):replica set IDs do not match的解決方法,文中通過(guò)示例代碼介紹的非常詳細(xì),需要的朋友可以參考借鑒,下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧
    2018-11-11
  • cgroup限制mongodb進(jìn)程內(nèi)存大小

    cgroup限制mongodb進(jìn)程內(nèi)存大小

    這篇文章主要介紹了cgroup限制mongodb進(jìn)程內(nèi)存大小,需要的朋友可以參考下
    2014-07-07
  • MongoDB數(shù)據(jù)更新方法干貨篇

    MongoDB數(shù)據(jù)更新方法干貨篇

    之前給大家分享了MongoDB中數(shù)據(jù)查詢(xún)的相關(guān)方法和技巧,那么下面這篇文章主要給大家介紹了MongoDB中數(shù)據(jù)更新方法的相關(guān)資料,文中給出了詳細(xì)的示例代碼,需要的朋友可以參考借鑒,下面來(lái)一起看看吧。
    2017-05-05
  • 關(guān)于MongoDB索引管理-索引的創(chuàng)建、查看、刪除操作詳解

    關(guān)于MongoDB索引管理-索引的創(chuàng)建、查看、刪除操作詳解

    本文講述了關(guān)于MongoDB索引管理包括索引的創(chuàng)建、查看索引、刪除索引各方面的命令及使用方法
    2018-03-03
  • MongoDB中的參數(shù)限制與閥值詳析

    MongoDB中的參數(shù)限制與閥值詳析

    這篇文章主要給大家介紹了關(guān)于MongoDB中參數(shù)限制與閥值的相關(guān)資料,文中通過(guò)示例代碼以及圖文介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面來(lái)一起學(xué)習(xí)學(xué)習(xí)吧
    2019-03-03
  • MongoDB的創(chuàng)建、更新和刪除

    MongoDB的創(chuàng)建、更新和刪除

    下面開(kāi)始學(xué)習(xí)MongoDB最重要也是最基礎(chǔ)的部分:C(創(chuàng)建)R(查詢(xún))U(更新)D(刪除);由于R(查詢(xún))操作相對(duì)來(lái)說(shuō)內(nèi)容比較多,也比較繁瑣,同時(shí)使用頻率也比較高,所以下一篇會(huì)拿出來(lái)單獨(dú)介紹。廢話(huà)不多說(shuō),連上服務(wù)器,我們直接進(jìn)入正題
    2017-05-05

最新評(píng)論