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

一、MongoDB如何選擇索引

如果我們在Collection建瞭5個index,那麼當我們查詢的時候,MongoDB會根據查詢語句的篩選條件、sort排序等來定位可以使用的index作為候選索引;然後MongoDB會創建對應數量的查詢計劃,並分別使用不同線程執行查詢計劃,最終會選擇一個執行最快的index;但是這個選擇也不是一成不變的,後續還會有一段時間根據實際執行情況動態調整;

二、數據準備

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索引選擇策略內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!

推薦閱讀: