M201: MongoDB Performance chapter 3 Index Operations学习记录
运行环境
操作系统：windows 10 家庭中文版
Mongodb ：Mongodb 3.4
Mongodb安装路径：E:>MongoDB\Server\3.4\bin\
Mongodb存储路径：E:>MongoDB\data
课后问题
lab 3.1 Explain Output
In this lab you’re going to determine which index was used to satisfy a query given its explain output.
The following query was ran:
> var exp = db.restaurants.explain("executionStats")
> exp.find({ "address.state": "NY", stars: { $gt: 3, $lt: 4 } }).sort({ name: 1 }).hint(REDACTED)Which resulted in the following output:
{
"queryPlanner": {
"plannerVersion": 1,
"namespace": "m201.restaurants",
"indexFilterSet": false,
"parsedQuery": "REDACTED",
"winningPlan": {
"stage": "SORT",
"sortPattern": {
"name": 1
},
"inputStage": {
"stage": "SORT_KEY_GENERATOR",
"inputStage": {
"stage": "FETCH",
"inputStage": { "stage": "IXSCAN", "keyPattern": "REDACTED", "indexName": "REDACTED", "isMultiKey": false, "isUnique": false, "isSparse": false, "isPartial": false, "indexVersion": 1, "direction": "forward", "indexBounds": "REDACTED" } }
}
},
"rejectedPlans": [ ]
},
"executionStats": {
"executionSuccess": true,
"nReturned": 3335,
"executionTimeMillis": 20,
"totalKeysExamined": 3335,
"totalDocsExamined": 3335,
"executionStages": "REDACTED"
},
"serverInfo": "REDACTED",
"ok": 1
}Given the redacted explain output above, select the index that was passed to hint.
Note: The hint() method is used to force the query planner to select a particular index for a given query. You can learn more about hint by visiting its documentation.
Choose the best answer:
- { “address.state”: 1, “name”: 1 }
- { “address.state”: 1, “name”: 1, “stars”: 1 }
- { “address.state”: 1, “stars”: 1, “name”: 1}
- { “address.state”: 1 }
解答
下载json包restaurants.json.zip
并解压至路径E:\MongoDB\m201\chapter_3_index_operations\
开mongod
C:\Users\Shinelon>e:
E:\>MongoDB\Server\3.4\bin\mongod.exe --dbpath MongoDB\data导入json
E:\>MongoDB\Server\3.4\bin\mongoimport.exe -d m201 -c restaurants MongoDB\m201\chapter_3_index_operations\restaurants.json\restaurants.json
2018-04-22T22:16:12.279+0800 connected to: localhost
2018-04-22T22:16:15.270+0800 [####....................] m201.restaurants 29.3MB/144MB (20.4%)
2018-04-22T22:16:18.270+0800 [#########...............] m201.restaurants 59.5MB/144MB (41.4%)
2018-04-22T22:16:21.271+0800 [##############..........] m201.restaurants 89.7MB/144MB (62.4%)
2018-04-22T22:16:24.270+0800 [###################.....] m201.restaurants 118MB/144MB (82.3%)
2018-04-22T22:16:26.805+0800 [########################] m201.restaurants 144MB/144MB (100.0%)
2018-04-22T22:16:26.805+0800 imported 1000000 documents第一个：
> db.restaurants.createIndex({ "address.state": 1, "name": 1 })
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 2,
"numIndexesAfter" : 3,
"ok" : 1
}
> var exp = db.restaurants.explain("executionStats")
> exp.find({ "address.state": "NY", stars: { $gt: 3, $lt: 4 } }).sort({ name: 1 }).hint({ "address.state": 1, "name": 1 })
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "m201.restaurants",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"address.state" : {
"$eq" : "NY"
}
},
{
"stars" : {
"$lt" : 4
}
},
{
"stars" : {
"$gt" : 3
}
}
]
},
"winningPlan" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"stars" : {
"$lt" : 4
}
},
{
"stars" : {
"$gt" : 3
}
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"address.state" : 1,
"name" : 1
},
"indexName" : "address.state_1_name_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"address.state" : [ ],
"name" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"address.state" : [
"[\"NY\", \"NY\"]"
],
"name" : [
"[MinKey, MaxKey]"
]
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 3335,
"executionTimeMillis" : 53,
"totalKeysExamined" : 19578,
"totalDocsExamined" : 19578,
"executionStages" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"stars" : {
"$lt" : 4
}
},
{
"stars" : {
"$gt" : 3
}
}
]
},
"nReturned" : 3335,
"executionTimeMillisEstimate" : 51,
"works" : 19579,
"advanced" : 3335,
"needTime" : 16243,
"needYield" : 0,
"saveState" : 153,
"restoreState" : 153,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 19578,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 19578,
"executionTimeMillisEstimate" : 20,
"works" : 19579,
"advanced" : 19578,
"needTime" : 0,
"needYield" : 0,
"saveState" : 153,
"restoreState" : 153,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"address.state" : 1,
"name" : 1
},
"indexName" : "address.state_1_name_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"address.state" : [ ],
"name" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"address.state" : [
"[\"NY\", \"NY\"]"
],
"name" : [
"[MinKey, MaxKey]"
]
},
"keysExamined" : 19578,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
},
"serverInfo" : {
"host" : "DESKTOP-MP9NVQ7",
"port" : 27017,
"version" : "3.4.6",
"gitVersion" : "c55eb86ef46ee7aede3b1e2a5d184a7df4bfb5b5"
},
"ok" : 1
}进行的取值查询（FETCH），未使用索引进行排序，不正确
第二个：
> db.restaurants.createIndex({ "address.state": 1, "name": 1, "stars": 1 })
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 3,
"numIndexesAfter" : 4,
"ok" : 1
}
> var exp = db.restaurants.explain("executionStats")
> exp.find({ "address.state": "NY", stars: { $gt: 3, $lt: 4 } }).sort({ name: 1 }).hint({ "address.state": 1, "name": 1, "stars": 1 })
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "m201.restaurants",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"address.state" : {
"$eq" : "NY"
}
},
{
"stars" : {
"$lt" : 4
}
},
{
"stars" : {
"$gt" : 3
}
}
]
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"address.state" : 1,
"name" : 1,
"stars" : 1
},
"indexName" : "address.state_1_name_1_stars_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"address.state" : [ ],
"name" : [ ],
"stars" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"address.state" : [
"[\"NY\", \"NY\"]"
],
"name" : [
"[MinKey, MaxKey]"
],
"stars" : [
"(3.0, 4.0)"
]
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 3335,
"executionTimeMillis" : 12,
"totalKeysExamined" : 3536,
"totalDocsExamined" : 3335,
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 3335,
"executionTimeMillisEstimate" : 0,
"works" : 3536,
"advanced" : 3335,
"needTime" : 200,
"needYield" : 0,
"saveState" : 27,
"restoreState" : 27,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 3335,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 3335,
"executionTimeMillisEstimate" : 0,
"works" : 3536,
"advanced" : 3335,
"needTime" : 200,
"needYield" : 0,
"saveState" : 27,
"restoreState" : 27,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"address.state" : 1,
"name" : 1,
"stars" : 1
},
"indexName" : "address.state_1_name_1_stars_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"address.state" : [ ],
"name" : [ ],
"stars" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"address.state" : [
"[\"NY\", \"NY\"]"
],
"name" : [
"[MinKey, MaxKey]"
],
"stars" : [
"(3.0, 4.0)"
]
},
"keysExamined" : 3536,
"seeks" : 201,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
},
"serverInfo" : {
"host" : "DESKTOP-MP9NVQ7",
"port" : 27017,
"version" : "3.4.6",
"gitVersion" : "c55eb86ef46ee7aede3b1e2a5d184a7df4bfb5b5"
},
"ok" : 1
}索引的顺序错了，同样进行的取值查询（FETCH），未使用索引进行排序
第三个：
> db.restaurants.createIndex({ "address.state": 1, "stars": 1, "name": 1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 4,
"numIndexesAfter" : 5,
"ok" : 1
}
> var exp = db.restaurants.explain("executionStats")
> exp.find({ "address.state": "NY", stars: { $gt: 3, $lt: 4 } }).sort({ name: 1 }).hint({ "address.state": 1, "stars": 1, "name": 1})
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "m201.restaurants",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"address.state" : {
"$eq" : "NY"
}
},
{
"stars" : {
"$lt" : 4
}
},
{
"stars" : {
"$gt" : 3
}
}
]
},
"winningPlan" : {
"stage" : "SORT",
"sortPattern" : {
"name" : 1
},
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"address.state" : 1,
"stars" : 1,
"name" : 1
},
"indexName" : "address.state_1_stars_1_name_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"address.state" : [ ],
"stars" : [ ],
"name" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"address.state" : [
"[\"NY\", \"NY\"]"
],
"stars" : [
"(3.0, 4.0)"
],
"name" : [
"[MinKey, MaxKey]"
]
}
}
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 3335,
"executionTimeMillis" : 22,
"totalKeysExamined" : 3335,
"totalDocsExamined" : 3335,
"executionStages" : {
"stage" : "SORT",
"nReturned" : 3335,
"executionTimeMillisEstimate" : 21,
"works" : 6673,
"advanced" : 3335,
"needTime" : 3337,
"needYield" : 0,
"saveState" : 52,
"restoreState" : 52,
"isEOF" : 1,
"invalidates" : 0,
"sortPattern" : {
"name" : 1
},
"memUsage" : 632385,
"memLimit" : 33554432,
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"nReturned" : 3335,
"executionTimeMillisEstimate" : 21,
"works" : 3337,
"advanced" : 3335,
"needTime" : 1,
"needYield" : 0,
"saveState" : 52,
"restoreState" : 52,
"isEOF" : 1,
"invalidates" : 0,
"inputStage" : {
"stage" : "FETCH",
"nReturned" : 3335,
"executionTimeMillisEstimate" : 11,
"works" : 3336,
"advanced" : 3335,
"needTime" : 0,
"needYield" : 0,
"saveState" : 52,
"restoreState" : 52,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 3335,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 3335,
"executionTimeMillisEstimate" : 0,
"works" : 3336,
"advanced" : 3335,
"needTime" : 0,
"needYield" : 0,
"saveState" : 52,
"restoreState" : 52,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"address.state" : 1,
"stars" : 1,
"name" : 1
},
"indexName" : "address.state_1_stars_1_name_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"address.state" : [ ],
"stars" : [ ],
"name" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"address.state" : [
"[\"NY\", \"NY\"]"
],
"stars" : [
"(3.0, 4.0)"
],
"name" : [
"[MinKey, MaxKey]"
]
},
"keysExamined" : 3335,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
}
}
},
"serverInfo" : {
"host" : "DESKTOP-MP9NVQ7",
"port" : 27017,
"version" : "3.4.6",
"gitVersion" : "c55eb86ef46ee7aede3b1e2a5d184a7df4bfb5b5"
},
"ok" : 1
}完美的符合，这个查询最适合的索引构建方式
第四个：
> db.restaurants.createIndex({"address.state":1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
> var exp = db.restaurants.explain("executionStats")
> exp.find({ "address.state": "NY", stars: { $gt: 3, $lt: 4 } }).sort({ name: 1 }).hint({ "address.state": 1})
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "m201.restaurants",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"address.state" : {
"$eq" : "NY"
}
},
{
"stars" : {
"$lt" : 4
}
},
{
"stars" : {
"$gt" : 3
}
}
]
},
"winningPlan" : {
"stage" : "SORT",
"sortPattern" : {
"name" : 1
},
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"stars" : {
"$lt" : 4
}
},
{
"stars" : {
"$gt" : 3
}
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"address.state" : 1
},
"indexName" : "address.state_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"address.state" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"address.state" : [
"[\"NY\", \"NY\"]"
]
}
}
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 3335,
"executionTimeMillis" : 48,
"totalKeysExamined" : 19578,
"totalDocsExamined" : 19578,
"executionStages" : {
"stage" : "SORT",
"nReturned" : 3335,
"executionTimeMillisEstimate" : 40,
"works" : 22916,
"advanced" : 3335,
"needTime" : 19580,
"needYield" : 0,
"saveState" : 179,
"restoreState" : 179,
"isEOF" : 1,
"invalidates" : 0,
"sortPattern" : {
"name" : 1
},
"memUsage" : 632385,
"memLimit" : 33554432,
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"nReturned" : 3335,
"executionTimeMillisEstimate" : 40,
"works" : 19580,
"advanced" : 3335,
"needTime" : 16244,
"needYield" : 0,
"saveState" : 179,
"restoreState" : 179,
"isEOF" : 1,
"invalidates" : 0,
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"stars" : {
"$lt" : 4
}
},
{
"stars" : {
"$gt" : 3
}
}
]
},
"nReturned" : 3335,
"executionTimeMillisEstimate" : 30,
"works" : 19579,
"advanced" : 3335,
"needTime" : 16243,
"needYield" : 0,
"saveState" : 179,
"restoreState" : 179,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 19578,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 19578,
"executionTimeMillisEstimate" : 0,
"works" : 19579,
"advanced" : 19578,
"needTime" : 0,
"needYield" : 0,
"saveState" : 179,
"restoreState" : 179,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"address.state" : 1
},
"indexName" : "address.state_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"address.state" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"address.state" : [
"[\"NY\", \"NY\"]"
]
},
"keysExamined" : 19578,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
}
}
},
"serverInfo" : {
"host" : "DESKTOP-MP9NVQ7",
"port" : 27017,
"version" : "3.4.6",
"gitVersion" : "c55eb86ef46ee7aede3b1e2a5d184a7df4bfb5b5"
},
"ok" : 1
}需要回表，totalKeysExamined比给出的结果高出许多
明显一致的是第三个
- { “address.state”: 1, “stars”: 1, “name”: 1}
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
