explain is used to understand execution detail
In this case,"totalDocsExamined" : 1380558: shows "query by username" is quite insufficient
db.users.find({"username": "user101"}).explain("executionStats")
> db.users.find({"name":"user1"}).explain("executionStats")
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.users",
"indexFilterSet" : false,
"parsedQuery" : {
"name" : {
"$eq" : "user1"
}
},
"winningPlan" : {
"stage" : "COLLSCAN",
"filter" : {
"name" : {
"$eq" : "user1"
}
},
"direction" : "forward"
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 4,
"executionTimeMillis" : 767,
"totalKeysExamined" : 0,
"totalDocsExamined" : 1380558,
"executionStages" : {
"stage" : "COLLSCAN",
"filter" : {
"name" : {
"$eq" : "user1"
}
},
"nReturned" : 4,
"executionTimeMillisEstimate" : 75,
"works" : 1380560,
"advanced" : 4,
"needTime" : 1380555,
"needYield" : 0,
"saveState" : 1380,
"restoreState" : 1380,
"isEOF" : 1,
"direction" : "forward",
"docsExamined" : 1380558
}
},
"serverInfo" : {
"host" : "c38be4173edc",
"port" : 27017,
"version" : "4.4.1",
"gitVersion" : "ad91a93a5a31e175f5cbf8c69561e788bbc55ce1"
},
"ok" : 1
}
After createIndex on "name" attribute, query will be efficient
> db.users.createIndex({"name":1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
// "totalDocsExamined" change from 1380558 to 4
> db.users.find({"name":"user1"}).explain("executionStats")
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.users",
"indexFilterSet" : false,
"parsedQuery" : {
"name" : {
"$eq" : "user1"
}
},
"winningPlan" : {
"stage" : "FETCH",
"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" : [
"[\"user1\", \"user1\"]"
]
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 4,
"executionTimeMillis" : 1,
"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,
"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,
"keyPattern" : {
"name" : 1
},
"indexName" : "name_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"name" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"name" : [
"[\"user1\", \"user1\"]"
]
},
"keysExamined" : 4,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0
}
}
},
"serverInfo" : {
"host" : "c38be4173edc",
"port" : 27017,
"version" : "4.4.1",
"gitVersion" : "ad91a93a5a31e175f5cbf8c69561e788bbc55ce1"
},
"ok" : 1
}
db.currentOp is used to check current operation
This command can be used to check internal status once createIndex spend too much time
> db.currentOp()
{
"inprog" : [
{
"type" : "op",
"host" : "c38be4173edc:27017",
"desc" : "WTJournalFlusher",
"active" : true,
"currentOpTime" : "2020-09-29T02:35:50.754+00:00",
"opid" : 19797,
"op" : "none",
"ns" : "",
"command" : {
},
"numYields" : 0,
"locks" : {
},
"waitingForLock" : false,
"lockStats" : {
},
"waitingForFlowControl" : false,
"flowControlStats" : {
}
},
{
"type" : "op",
"host" : "c38be4173edc:27017",
"desc" : "conn1",
"connectionId" : 1,
"client" : "127.0.0.1:39646",
"appName" : "MongoDB Shell",
"clientMetadata" : {
"application" : {
"name" : "MongoDB Shell"
},
"driver" : {
"name" : "MongoDB Internal Client",
"version" : "4.4.1"
},
"os" : {
"type" : "Linux",
"name" : "Ubuntu",
"architecture" : "x86_64",
"version" : "18.04"
}
},
"active" : true,
"currentOpTime" : "2020-09-29T02:35:50.755+00:00",
"opid" : 19798,
"lsid" : {
"id" : UUID("7808c7a9-ca46-4d72-9657-dbda3b00dfab"),
"uid" : BinData(0,"47DEQpj8HBSa+/TImW+5JCeuQeRkm5NMpJWZG3hSuFU=")
},
"secs_running" : NumberLong(0),
"microsecs_running" : NumberLong(308),
"op" : "command",
"ns" : "admin.$cmd.aggregate",
"command" : {
"currentOp" : 1,
"lsid" : {
"id" : UUID("7808c7a9-ca46-4d72-9657-dbda3b00dfab")
},
"$db" : "admin"
},
"numYields" : 0,
"locks" : {
},
"waitingForLock" : false,
"lockStats" : {
},
"waitingForFlowControl" : false,
"flowControlStats" : {
}
},
{
"type" : "op",
"host" : "c38be4173edc:27017",
"desc" : "WTCheckpointThread",
"active" : true,
"currentOpTime" : "2020-09-29T02:35:50.755+00:00",
"opid" : 19625,
"op" : "none",
"ns" : "",
"command" : {
},
"numYields" : 0,
"locks" : {
},
"waitingForLock" : false,
"lockStats" : {
},
"waitingForFlowControl" : false,
"flowControlStats" : {
}
}
],
"ok" : 1
}
Compound Index
// to optimize sort query
db.users.find().sort({“age”:1, “name”:1})
// need create index start from age
> db.users.createIndex({“age":1,”name":1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 3,
"numIndexesAfter" : 4,
"ok" : 1
}
沒有留言:
張貼留言