mongodb 优化

1、按照条件分页查询

条件是status，添加了索引

db.serialNumber.createIndex({"status": 1})

页面相应需要10S or more

感觉很慢，

在mongodb里面查询，按照java里面写一下语句

db.getCollection('serialNumber').find({"status":"NSO"}).skip(0).limit(10).sort({"createdDate":-1});

 

db.getCollection('serialNumber').find({"status":"NSO"}).skip(0).limit(10).sort({"createdDate":1}).explain("executionStats");

{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "saas_ferrero.serialNumber",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "status" : {
                "$eq" : "NSO"
            }
        },
        "winningPlan" : {
            "stage" : "SORT",
            "sortPattern" : {
                "createdDate" : 1.0
            },
            "limitAmount" : 10,
            "inputStage" : {
                "stage" : "SORT_KEY_GENERATOR",
                "inputStage" : {
                    "stage" : "FETCH",
                    "inputStage" : {
                        "stage" : "IXSCAN",
                        "keyPattern" : {
                            "status" : -1.0
                        },
                        "indexName" : "status_-1",
                        "isMultiKey" : false,
                        "multiKeyPaths" : {
                            "status" : []
                        },
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : 2,
                        "direction" : "forward",
                        "indexBounds" : {
                            "status" : [ 
                                "[\"NSO\", \"NSO\"]"
                            ]
                        }
                    }
                }
            }
        },
        "rejectedPlans" : [ 
            {
                "stage" : "LIMIT",
                "limitAmount" : 10,
                "inputStage" : {
                    "stage" : "FETCH",
                    "filter" : {
                        "status" : {
                            "$eq" : "NSO"
                        }
                    },
                    "inputStage" : {
                        "stage" : "IXSCAN",
                        "keyPattern" : {
                            "createdDate" : -1.0
                        },
                        "indexName" : "createdDate_-1",
                        "isMultiKey" : false,
                        "multiKeyPaths" : {
                            "createdDate" : []
                        },
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : 2,
                        "direction" : "backward",
                        "indexBounds" : {
                            "createdDate" : [ 
                                "[MinKey, MaxKey]"
                            ]
                        }
                    }
                }
            }
        ]
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 2,
        "executionTimeMillis" : 0,
        "totalKeysExamined" : 2,
        "totalDocsExamined" : 2,
        "executionStages" : {
            "stage" : "SORT",
            "nReturned" : 2,
            "executionTimeMillisEstimate" : 0,
            "works" : 8,
            "advanced" : 2,
            "needTime" : 4,
            "needYield" : 0,
            "saveState" : 0,
            "restoreState" : 0,
            "isEOF" : 1,
            "invalidates" : 0,
            "sortPattern" : {
                "createdDate" : 1.0
            },
            "memUsage" : 1219,
            "memLimit" : 235544320,
            "limitAmount" : 10,
            "inputStage" : {
                "stage" : "SORT_KEY_GENERATOR",
                "nReturned" : 2,
                "executionTimeMillisEstimate" : 0,
                "works" : 4,
                "advanced" : 2,
                "needTime" : 1,
                "needYield" : 0,
                "saveState" : 0,
                "restoreState" : 0,
                "isEOF" : 1,
                "invalidates" : 0,
                "inputStage" : {
                    "stage" : "FETCH",
                    "nReturned" : 2,
                    "executionTimeMillisEstimate" : 0,
                    "works" : 3,
                    "advanced" : 2,
                    "needTime" : 0,
                    "needYield" : 0,
                    "saveState" : 0,
                    "restoreState" : 0,
                    "isEOF" : 1,
                    "invalidates" : 0,
                    "docsExamined" : 2,
                    "alreadyHasObj" : 0,
                    "inputStage" : {
                        "stage" : "IXSCAN",
                        "nReturned" : 2,
                        "executionTimeMillisEstimate" : 0,
                        "works" : 3,
                        "advanced" : 2,
                        "needTime" : 0,
                        "needYield" : 0,
                        "saveState" : 0,
                        "restoreState" : 0,
                        "isEOF" : 1,
                        "invalidates" : 0,
                        "keyPattern" : {
                            "status" : -1.0
                        },
                        "indexName" : "status_-1",
                        "isMultiKey" : false,
                        "multiKeyPaths" : {
                            "status" : []
                        },
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : 2,
                        "direction" : "forward",
                        "indexBounds" : {
                            "status" : [ 
                                "[\"NSO\", \"NSO\"]"
                            ]
                        },
                        "keysExamined" : 2,
                    }
                }
            }
        }
    },

}

 

IXSCAN：是索引扫描

然后发现需要10s，然后排序改成1 发现只要001S

然后将索引删除

创建了 -1的索引，再查询就很快了

db.serialNumber.createIndex({"status": -1})

 

 注意点，排序的字段与顺序和索引最好保持一致