MongoDB ——explain查询分析

程振濂
2023-12-01

测试数据:

/* 1 */
{
    "_id" : ObjectId("5ca0538159aa1df98e31c634"),
    "numIndex" : 0.0
}

/* 2 */
{
    "_id" : ObjectId("5ca0538159aa1df98e31c635"),
    "numIndex" : 1.0
}
// 写入了三万条这样的numIndex自增1的数据

分析语句:

db.getCollection('student').find({numIndex:{"$gt":50,"$lt":55}}).explain("executionStats")

分析结果:

/* 1 */
{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "testdb.student",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$and" : [ 
                {
                    "numIndex" : {
                        "$lt" : 55.0
                    }
                }, 
                {
                    "numIndex" : {
                        "$gt" : 50.0
                    }
                }
            ]
        },
        "winningPlan" : {
            "stage" : "COLLSCAN",
            "filter" : {
                "$and" : [ 
                    {
                        "numIndex" : {
                            "$lt" : 55.0
                        }
                    }, 
                    {
                        "numIndex" : {
                            "$gt" : 50.0
                        }
                    }
                ]
            },
            "direction" : "forward"
        },
        "rejectedPlans" : []
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 4,
        "executionTimeMillis" : 20,
        "totalKeysExamined" : 0,
        "totalDocsExamined" : 30000,
        "executionStages" : {
            "stage" : "COLLSCAN",
            "filter" : {
                "$and" : [ 
                    {
                        "numIndex" : {
                            "$lt" : 55.0
                        }
                    }, 
                    {
                        "numIndex" : {
                            "$gt" : 50.0
                        }
                    }
                ]
            },
            "nReturned" : 4,
            "executionTimeMillisEstimate" : 10,
            "works" : 30002,
            "advanced" : 4,
            "needTime" : 29997,
            "needYield" : 0,
            "saveState" : 234,
            "restoreState" : 234,
            "isEOF" : 1,
            "invalidates" : 0,
            "direction" : "forward",
            "docsExamined" : 30000
        }
    },
    "serverInfo" : {
        "host" : "localhost",
        "port" : 27017,
        "version" : "4.0.0",
        "gitVersion" : "3b07af3d4f471ae89e8186d33bbb1d5259597d51"
    },
    "ok" : 1.0
}

感觉mongo的查询分析比sql的好看一点儿。上面是具体的查询的语句,下面是执行结果集服务器信息。
totalDocsExamined:检索总行数
totalKeysExamined:显示整个扫描的索引数量
nReturned:返回了几行

之后我们再来创建索引看看:

db.getCollection('student').createIndex({numIndex:1})

看下我们现在都有哪些索引:

db.getCollection('student').getIndexes()

返回结果:

[
    {
        "v" : 2,
        "key" : {
            "_id" : 1
        },
        "name" : "_id_",
        "ns" : "testdb.student"
    },
    {
        "v" : 2,
        "key" : {
            "numIndex" : 1.0
        },
        "name" : "numIndex_1",
        "ns" : "testdb.student"
    }
]

再执行下刚才的查询:

{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "testdb.student",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$and" : [ 
                {
                    "numIndex" : {
                        "$lt" : 55.0
                    }
                }, 
                {
                    "numIndex" : {
                        "$gt" : 50.0
                    }
                }
            ]
        },
        "winningPlan" : {
            "stage" : "FETCH",
            "inputStage" : {
                "stage" : "IXSCAN",
                "keyPattern" : {
                    "numIndex" : 1.0
                },
                "indexName" : "numIndex_1",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                    "numIndex" : []
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "numIndex" : [ 
                        "(50.0, 55.0)"
                    ]
                }
            }
        },
        "rejectedPlans" : []
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 4,
        "executionTimeMillis" : 5,
        "totalKeysExamined" : 4,
        "totalDocsExamined" : 4,
        "executionStages" : {
            "stage" : "FETCH",
            "nReturned" : 4,
            "executionTimeMillisEstimate" : 0,
            "works" : 5,
            "advanced" : 4,
            "needTime" : 0,
            "needYield" : 0,
            "saveState" : 0,
            "restoreState" : 0,
            "isEOF" : 1,
            "invalidates" : 0,
            "docsExamined" : 4,
            "alreadyHasObj" : 0,
            "inputStage" : {
                "stage" : "IXSCAN",
                "nReturned" : 4,
                "executionTimeMillisEstimate" : 0,
                "works" : 5,
                "advanced" : 4,
                "needTime" : 0,
                "needYield" : 0,
                "saveState" : 0,
                "restoreState" : 0,
                "isEOF" : 1,
                "invalidates" : 0,
                "keyPattern" : {
                    "numIndex" : 1.0
                },
                "indexName" : "numIndex_1",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                    "numIndex" : []
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "numIndex" : [ 
                        "(50.0, 55.0)"
                    ]
                },
                "keysExamined" : 4,
                "seeks" : 1,
                "dupsTested" : 0,
                "dupsDropped" : 0,
                "seenInvalidated" : 0
            }
        }
    },
    "serverInfo" : {
        "host" : "localhost",
        "port" : 27017,
        "version" : "4.0.0",
        "gitVersion" : "3b07af3d4f471ae89e8186d33bbb1d5259597d51"
    },
    "ok" : 1.0
}

这时候,我们使用上了索引:"indexName" : "numIndex_1" ,executionTimeMillis 从20降低到了5。

 类似资料: