MongoDB 複合索引
首先，MongoDB 中的 schema 如下
> db.users.find()
{ "_id" : ObjectId("5f6f162c432d41b06263640d"), "name" : "user12", "i" : 12, "age" : 27, "createdAt" : ISODate("2020-09-26T10:21:32.660Z") }
{ "_id" : ObjectId("5f6f162c432d41b06263640e"), "name" : "user13", "i" : 13, "age" : 67, "createdAt" : ISODate("2020-09-26T10:21:32.661Z") }
檢查執行狀態。
在這個例子中，查詢正在掃描整個集合。
我們新增了上百萬筆使用者資料，所以 MongoDB 需要檢查上百萬筆文件才能找到符合的。
> db.users.find().explain("executionStats")
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.users",
"indexFilterSet" : false,
"parsedQuery" : {
},
"winningPlan" : {
// this query scan collection!!
"stage" : "COLLSCAN",
"direction" : "forward"
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 1380558,
"executionTimeMillis" : 433,
"totalKeysExamined" : 0,
// all documents need to be examined
"totalDocsExamined" : 1380558,
"executionStages" : {
"stage" : "COLLSCAN",
"nReturned" : 1380558,
"executionTimeMillisEstimate" : 27,
"works" : 1380560,
"advanced" : 1380558,
"needTime" : 1,
"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
}
用 name 查詢使用者，仍然是集合掃描，效能還是很差
> db.users.find({"name":"user164"})
{ "_id" : ObjectId("5f6f162c432d41b0626364a5"), "name" : "user164", "i" : 164, "age" : 74, "createdAt" : ISODate("2020-09-26T10:21:32.743Z") }
{ "_id" : ObjectId("5f6f16f640ac430dab030a88"), "name" : "user164", "i" : 164, "age" : 92, "createdAt" : ISODate("2020-09-26T10:24:54.869Z") }
{ "_id" : ObjectId("5f6f178608c93b291de083db"), "name" : "user164", "i" : 164, "age" : 29, "createdAt" : ISODate("2020-09-26T10:27:18.322Z") }
> db.users.find({"name":"user164"}).explain("executionStats")
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.users",
"indexFilterSet" : false,
"parsedQuery" : {
"name" : {
"$eq" : "user164"
}
},
"winningPlan" : {
"stage" : "COLLSCAN",
"filter" : {
"name" : {
"$eq" : "user164"
}
},
"direction" : "forward"
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 3,
"executionTimeMillis" : 685,
"totalKeysExamined" : 0,
// without index, find by name still need examined all documents
"totalDocsExamined" : 1380558,
"executionStages" : {
"stage" : "COLLSCAN",
"filter" : {
"name" : {
"$eq" : "user164"
}
},
"nReturned" : 3,
"executionTimeMillisEstimate" : 68,
"works" : 1380560,
"advanced" : 3,
"needTime" : 1380556,
"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
}
建立 name 的索引
> db.users.createIndex({"name":1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
> db.users.find({"name":"user164"}).explain("executionStats")
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.users",
"indexFilterSet" : false,
"parsedQuery" : {
"name" : {
"$eq" : "user164"
}
},
"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" : [
"[\"user164\", \"user164\"]"
]
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 3,
"executionTimeMillis" : 1,
"totalKeysExamined" : 3,
"totalDocsExamined" : 3,
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 3,
"executionTimeMillisEstimate" : 0,
"works" : 4,
"advanced" : 3,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"docsExamined" : 3,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 3,
"executionTimeMillisEstimate" : 0,
"works" : 4,
"advanced" : 3,
"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" : [
"[\"user164\", \"user164\"]"
]
},
"keysExamined" : 3,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0
}
}
},
"serverInfo" : {
"host" : "c38be4173edc",
"port" : 27017,
"version" : "4.4.1",
"gitVersion" : "ad91a93a5a31e175f5cbf8c69561e788bbc55ce1"
},
"ok" : 1
}
用 name 查詢後再依 age 排序。
可以看到 stage: SORT，表示 MongoDB 需要在記憶體中排序，可能會佔用大量記憶體
> db.users.find({"name":"user164"}).sort({"age":1}).explain("executionStats")
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.users",
"indexFilterSet" : false,
"parsedQuery" : {
"name" : {
"$eq" : "user164"
}
},
"winningPlan" : {
"stage" : "SORT",
"sortPattern" : {
"age" : 1
},
"memLimit" : 104857600,
"type" : "simple",
"inputStage" : {
"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" : [
"[\"user164\", \"user164\"]"
]
}
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 3,
"executionTimeMillis" : 0,
"totalKeysExamined" : 3,
"totalDocsExamined" : 3,
"executionStages" : {
"stage" : "SORT",
"nReturned" : 3,
"executionTimeMillisEstimate" : 0,
"works" : 8,
"advanced" : 3,
"needTime" : 4,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"sortPattern" : {
"age" : 1
},
"memLimit" : 104857600,
"type" : "simple",
"totalDataSizeSorted" : 297,
"usedDisk" : false,
"inputStage" : {
"stage" : "FETCH",
"nReturned" : 3,
"executionTimeMillisEstimate" : 0,
"works" : 4,
"advanced" : 3,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"docsExamined" : 3,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 3,
"executionTimeMillisEstimate" : 0,
"works" : 4,
"advanced" : 3,
"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" : [
"[\"user164\", \"user164\"]"
]
},
"keysExamined" : 3,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0
}
}
}
},
"serverInfo" : {
"host" : "c38be4173edc",
"port" : 27017,
"version" : "4.4.1",
"gitVersion" : "ad91a93a5a31e175f5cbf8c69561e788bbc55ce1"
},
"ok" : 1
}
To avoid in-memory sort, need create index for “name” and “age".
> db.users.createIndex({"name":1,"age":1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 2,
"numIndexesAfter" : 3,
"ok" : 1
}
> db.users.find({"name":"user164"}).sort({"age":1}).explain("executionStats")
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.users",
"indexFilterSet" : false,
"parsedQuery" : {
"name" : {
"$eq" : "user164"
}
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"name" : 1,
"age" : 1
},
"indexName" : "name_1_age_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"name" : [ ],
"age" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"name" : [
"[\"user164\", \"user164\"]"
],
"age" : [
"[MinKey, MaxKey]"
]
}
}
},
"rejectedPlans" : [
{
"stage" : "SORT",
"sortPattern" : {
"age" : 1
},
"memLimit" : 104857600,
"type" : "simple",
"inputStage" : {
"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" : [
"[\"user164\", \"user164\"]"
]
}
}
}
}
]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 3,
"executionTimeMillis" : 1,
"totalKeysExamined" : 3,
"totalDocsExamined" : 3,
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 3,
"executionTimeMillisEstimate" : 0,
"works" : 5,
"advanced" : 3,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"docsExamined" : 3,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 3,
"executionTimeMillisEstimate" : 0,
"works" : 4,
"advanced" : 3,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"keyPattern" : {
"name" : 1,
"age" : 1
},
"indexName" : "name_1_age_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"name" : [ ],
"age" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"name" : [
"[\"user164\", \"user164\"]"
],
"age" : [
"[MinKey, MaxKey]"
]
},
"keysExamined" : 3,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0
}
}
},
"serverInfo" : {
"host" : "c38be4173edc",
"port" : 27017,
"version" : "4.4.1",
"gitVersion" : "ad91a93a5a31e175f5cbf8c69561e788bbc55ce1"
},
"ok" : 1
}
Sort by age
> db.users.find({"age":{"$gte":10,"$lte":20}}).sort({"age":1}).explain("executionStats")
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.users",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"age" : {
"$lte" : 20
}
},
{
"age" : {
"$gte" : 10
}
}
]
},
"winningPlan" : {
"stage" : "SORT",
"sortPattern" : {
"age" : 1
},
"memLimit" : 104857600,
"type" : "simple",
"inputStage" : {
"stage" : "COLLSCAN",
"filter" : {
"$and" : [
{
"age" : {
"$lte" : 20
}
},
{
"age" : {
"$gte" : 10
}
}
]
},
"direction" : "forward"
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 126350,
"executionTimeMillis" : 1020,
"totalKeysExamined" : 0,
"totalDocsExamined" : 1380558,
"executionStages" : {
"stage" : "SORT",
"nReturned" : 126350,
"executionTimeMillisEstimate" : 146,
"works" : 1506911,
"advanced" : 126350,
"needTime" : 1380560,
"needYield" : 0,
"saveState" : 1507,
"restoreState" : 1507,
"isEOF" : 1,
"sortPattern" : {
"age" : 1
},
"memLimit" : 104857600,
"type" : "simple",
"totalDataSizeSorted" : 12857285,
"usedDisk" : false,
"inputStage" : {
"stage" : "COLLSCAN",
"filter" : {
"$and" : [
{
"age" : {
"$lte" : 20
}
},
{
"age" : {
"$gte" : 10
}
}
]
},
"nReturned" : 126350,
"executionTimeMillisEstimate" : 90,
"works" : 1380560,
"advanced" : 126350,
"needTime" : 1254209,
"needYield" : 0,
"saveState" : 1507,
"restoreState" : 1507,
"isEOF" : 1,
"direction" : "forward",
"docsExamined" : 1380558
}
}
},
"serverInfo" : {
"host" : "c38be4173edc",
"port" : 27017,
"version" : "4.4.1",
"gitVersion" : "ad91a93a5a31e175f5cbf8c69561e788bbc55ce1"
},
"ok" : 1
}
建立 age 的索引然後依 age 排序
> db.users.createIndex({"age":1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 3,
"numIndexesAfter" : 4,
"ok" : 1
}
> db.users.find({"age":{"$gte":10,"$lte":20}}).sort({"age":1}).explain("executionStats")
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.users",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"age" : {
"$lte" : 20
}
},
{
"age" : {
"$gte" : 10
}
}
]
},
"winningPlan" : {
"stage" : "FETCH",
"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" : [
"[10.0, 20.0]"
]
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 126350,
"executionTimeMillis" : 243,
"totalKeysExamined" : 126350,
"totalDocsExamined" : 126350,
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 126350,
"executionTimeMillisEstimate" : 35,
"works" : 126351,
"advanced" : 126350,
"needTime" : 0,
"needYield" : 0,
"saveState" : 126,
"restoreState" : 126,
"isEOF" : 1,
"docsExamined" : 126350,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 126350,
"executionTimeMillisEstimate" : 13,
"works" : 126351,
"advanced" : 126350,
"needTime" : 0,
"needYield" : 0,
"saveState" : 126,
"restoreState" : 126,
"isEOF" : 1,
"keyPattern" : {
"age" : 1
},
"indexName" : "age_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"age" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"age" : [
"[10.0, 20.0]"
]
},
"keysExamined" : 126350,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0
}
}
},
"serverInfo" : {
"host" : "c38be4173edc",
"port" : 27017,
"version" : "4.4.1",
"gitVersion" : "ad91a93a5a31e175f5cbf8c69561e788bbc55ce1"
},
"ok" : 1
}
依 age 和 I 排序，MongoDB 需要在記憶體中排序，因為沒有同時包含 age 和 I 的索引
> db.users.find({"age":{"$gte":10,"$lte":20}}).sort({"age":1,"i":-1}).explain("executionStats")
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.users",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"age" : {
"$lte" : 20
}
},
{
"age" : {
"$gte" : 10
}
}
]
},
"winningPlan" : {
"stage" : "SORT",
"sortPattern" : {
"age" : 1,
"i" : -1
},
"memLimit" : 104857600,
"type" : "simple",
"inputStage" : {
"stage" : "FETCH",
"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" : [
"[10.0, 20.0]"
]
}
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 126350,
"executionTimeMillis" : 491,
"totalKeysExamined" : 126350,
"totalDocsExamined" : 126350,
"executionStages" : {
"stage" : "SORT",
"nReturned" : 126350,
"executionTimeMillisEstimate" : 167,
"works" : 252702,
"advanced" : 126350,
"needTime" : 126351,
"needYield" : 0,
"saveState" : 253,
"restoreState" : 253,
"isEOF" : 1,
"sortPattern" : {
"age" : 1,
"i" : -1
},
"memLimit" : 104857600,
"type" : "simple",
"totalDataSizeSorted" : 12857285,
"usedDisk" : false,
"inputStage" : {
"stage" : "FETCH",
"nReturned" : 126350,
"executionTimeMillisEstimate" : 63,
"works" : 126351,
"advanced" : 126350,
"needTime" : 0,
"needYield" : 0,
"saveState" : 253,
"restoreState" : 253,
"isEOF" : 1,
"docsExamined" : 126350,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 126350,
"executionTimeMillisEstimate" : 29,
"works" : 126351,
"advanced" : 126350,
"needTime" : 0,
"needYield" : 0,
"saveState" : 253,
"restoreState" : 253,
"isEOF" : 1,
"keyPattern" : {
"age" : 1
},
"indexName" : "age_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"age" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"age" : [
"[10.0, 20.0]"
]
},
"keysExamined" : 126350,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0
}
}
}
},
"serverInfo" : {
"host" : "c38be4173edc",
"port" : 27017,
"version" : "4.4.1",
"gitVersion" : "ad91a93a5a31e175f5cbf8c69561e788bbc55ce1"
},
"ok" : 1
}
建立 age 和 I 的索引後再排序一次
> db.users.createIndex({"age":1,"i":1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 4,
"numIndexesAfter" : 5,
"ok" : 1
}
> db.users.find({"age":{"$gte":10,"$lte":20}}).sort({"age":1,"i":-1}).explain("executionStats")
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.users",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"age" : {
"$lte" : 20
}
},
{
"age" : {
"$gte" : 10
}
}
]
},
"winningPlan" : {
"stage" : "SORT",
"sortPattern" : {
"age" : 1,
"i" : -1
},
"memLimit" : 104857600,
"type" : "simple",
"inputStage" : {
"stage" : "FETCH",
"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" : [
"[10.0, 20.0]"
]
}
}
}
},
"rejectedPlans" : [
{
"stage" : "FETCH",
"inputStage" : {
"stage" : "SORT",
"sortPattern" : {
"age" : 1,
"i" : -1
},
"memLimit" : 104857600,
"type" : "default",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"age" : 1,
"i" : 1
},
"indexName" : "age_1_i_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"age" : [ ],
"i" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"age" : [
"[10.0, 20.0]"
],
"i" : [
"[MinKey, MaxKey]"
]
}
}
}
}
]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 126350,
"executionTimeMillis" : 939,
"totalKeysExamined" : 126350,
"totalDocsExamined" : 126350,
"executionStages" : {
"stage" : "SORT",
"nReturned" : 126350,
"executionTimeMillisEstimate" : 188,
"works" : 252702,
"advanced" : 126350,
"needTime" : 126351,
"needYield" : 0,
"saveState" : 380,
"restoreState" : 380,
"isEOF" : 1,
"sortPattern" : {
"age" : 1,
"i" : -1
},
"memLimit" : 104857600,
"type" : "simple",
"totalDataSizeSorted" : 12857285,
"usedDisk" : false,
"inputStage" : {
"stage" : "FETCH",
"nReturned" : 126350,
"executionTimeMillisEstimate" : 47,
"works" : 126351,
"advanced" : 126350,
"needTime" : 0,
"needYield" : 0,
"saveState" : 380,
"restoreState" : 380,
"isEOF" : 1,
"docsExamined" : 126350,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 126350,
"executionTimeMillisEstimate" : 26,
"works" : 126351,
"advanced" : 126350,
"needTime" : 0,
"needYield" : 0,
"saveState" : 380,
"restoreState" : 380,
"isEOF" : 1,
"keyPattern" : {
"age" : 1
},
"indexName" : "age_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"age" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"age" : [
"[10.0, 20.0]"
]
},
"keysExamined" : 126350,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0
}
}
}
},
"serverInfo" : {
"host" : "c38be4173edc",
"port" : 27017,
"version" : "4.4.1",
"gitVersion" : "ad91a93a5a31e175f5cbf8c69561e788bbc55ce1"
},
"ok" : 1
}
不過，依 I 和 age 排序又會需要記憶體內排序。
需要再建立一個 I 和 age 的索引。
> db.users.find({"age":{"$gte":10,"$lte":20}}).sort({"i":-1,"age":1}).explain("executionStats")
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.users",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"age" : {
"$lte" : 20
}
},
{
"age" : {
"$gte" : 10
}
}
]
},
"winningPlan" : {
"stage" : "SORT",
"sortPattern" : {
"i" : -1,
"age" : 1
},
"memLimit" : 104857600,
"type" : "simple",
"inputStage" : {
"stage" : "FETCH",
"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" : [
"[10.0, 20.0]"
]
}
}
}
},
"rejectedPlans" : [
{
"stage" : "FETCH",
"inputStage" : {
"stage" : "SORT",
"sortPattern" : {
"i" : -1,
"age" : 1
},
"memLimit" : 104857600,
"type" : "default",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"age" : 1,
"i" : 1
},
"indexName" : "age_1_i_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"age" : [ ],
"i" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"age" : [
"[10.0, 20.0]"
],
"i" : [
"[MinKey, MaxKey]"
]
}
}
}
}
]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 126350,
"executionTimeMillis" : 976,
"totalKeysExamined" : 126350,
"totalDocsExamined" : 126350,
"executionStages" : {
"stage" : "SORT",
"nReturned" : 126350,
"executionTimeMillisEstimate" : 225,
"works" : 252702,
"advanced" : 126350,
"needTime" : 126351,
"needYield" : 0,
"saveState" : 380,
"restoreState" : 380,
"isEOF" : 1,
"sortPattern" : {
"i" : -1,
"age" : 1
},
"memLimit" : 104857600,
"type" : "simple",
"totalDataSizeSorted" : 12857285,
"usedDisk" : false,
"inputStage" : {
"stage" : "FETCH",
"nReturned" : 126350,
"executionTimeMillisEstimate" : 68,
"works" : 126351,
"advanced" : 126350,
"needTime" : 0,
"needYield" : 0,
"saveState" : 380,
"restoreState" : 380,
"isEOF" : 1,
"docsExamined" : 126350,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 126350,
"executionTimeMillisEstimate" : 18,
"works" : 126351,
"advanced" : 126350,
"needTime" : 0,
"needYield" : 0,
"saveState" : 380,
"restoreState" : 380,
"isEOF" : 1,
"keyPattern" : {
"age" : 1
},
"indexName" : "age_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"age" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"age" : [
"[10.0, 20.0]"
]
},
"keysExamined" : 126350,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0
}
}
}
},
"serverInfo" : {
"host" : "c38be4173edc",
"port" : 27017,
"version" : "4.4.1",
"gitVersion" : "ad91a93a5a31e175f5cbf8c69561e788bbc55ce1"
},
"ok" : 1
}
三種篩選條件：
- equality filter (ex: “name” = “abc”)
- multivalue filter (ex. “grade”>5000)
- sort component of common query pattern (ex. sort({“age”}))
Tricks:
- 等值篩選的 key 應該放在最前面。
- 用於排序的 key 應該放在多值欄位之前。
- 多值篩選的 key 應該放在最後面。（例如：grade > 5000）
Covered query
表示查詢只會查看索引中包含的欄位。
Need avoid query _id field unless it’s part of index.
totalDocsExamined will be 0
> db.users.find({"name":"user16"},{"_id":0,"name":1}).explain("executionStats")
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.users",
"indexFilterSet" : false,
"parsedQuery" : {
"name" : {
"$eq" : "user16"
}
},
"winningPlan" : {
"stage" : "PROJECTION_COVERED",
"transformBy" : {
"_id" : 0,
"name" : 1
},
"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" : [
"[\"user16\", \"user16\"]"
]
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 1,
"executionTimeMillis" : 0,
"totalKeysExamined" : 1,
"totalDocsExamined" : 0,
"executionStages" : {
"stage" : "PROJECTION_COVERED",
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
"works" : 2,
"advanced" : 1,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"transformBy" : {
"_id" : 0,
"name" : 1
},
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
"works" : 2,
"advanced" : 1,
"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" : [
"[\"user16\", \"user16\"]"
]
},
"keysExamined" : 1,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0
}
}
},
"serverInfo" : {
"host" : "12c0a2efb72c",
"port" : 27017,
"version" : "4.4.1",
"gitVersion" : "ad91a93a5a31e175f5cbf8c69561e788bbc55ce1"
},
"ok" : 1
}
隱式索引
An index of {“a”:1,”b”:1}
A query of {“a”:”avalue”} can use this index as well because of prefix is covered.
// Without name index, MongoDB need examine all documents
> db.users.find({"name":"user16"}).explain("executionStats")
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.users",
"indexFilterSet" : false,
"parsedQuery" : {
"name" : {
"$eq" : "user16"
}
},
"winningPlan" : {
"stage" : "COLLSCAN",
"filter" : {
"name" : {
"$eq" : "user16"
}
},
"direction" : "forward"
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 1,
"executionTimeMillis" : 34,
"totalKeysExamined" : 0,
"totalDocsExamined" : 100003,
"executionStages" : {
"stage" : "COLLSCAN",
"filter" : {
"name" : {
"$eq" : "user16"
}
},
"nReturned" : 1,
"executionTimeMillisEstimate" : 2,
"works" : 100005,
"advanced" : 1,
"needTime" : 100003,
"needYield" : 0,
"saveState" : 100,
"restoreState" : 100,
"isEOF" : 1,
"direction" : "forward",
"docsExamined" : 100003
}
},
"serverInfo" : {
"host" : "12c0a2efb72c",
"port" : 27017,
"version" : "4.4.1",
"gitVersion" : "ad91a93a5a31e175f5cbf8c69561e788bbc55ce1"
},
"ok" : 1
}
// create index for name and age, then query by name, MongoDB also choose name and age as the index
> db.users.createIndex({"name":1,"age":1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 2,
"numIndexesAfter" : 3,
"ok" : 1
}
> db.users.find({"name":"user16"}).explain("executionStats")
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.users",
"indexFilterSet" : false,
"parsedQuery" : {
"name" : {
"$eq" : "user16"
}
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"name" : 1,
"age" : 1
},
"indexName" : "name_1_age_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"name" : [ ],
"age" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"name" : [
"[\"user16\", \"user16\"]"
],
"age" : [
"[MinKey, MaxKey]"
]
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 1,
"executionTimeMillis" : 1,
"totalKeysExamined" : 1,
"totalDocsExamined" : 1,
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
"works" : 2,
"advanced" : 1,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"docsExamined" : 1,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
"works" : 2,
"advanced" : 1,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"keyPattern" : {
"name" : 1,
"age" : 1
},
"indexName" : "name_1_age_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"name" : [ ],
"age" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"name" : [
"[\"user16\", \"user16\"]"
],
"age" : [
"[MinKey, MaxKey]"
]
},
"keysExamined" : 1,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0
}
}
},
"serverInfo" : {
"host" : "12c0a2efb72c",
"port" : 27017,
"version" : "4.4.1",
"gitVersion" : "ad91a93a5a31e175f5cbf8c69561e788bbc55ce1"
},
"ok" : 1
}
// however, it won’t benefit query by age
> db.users.find({"age":16}).explain("executionStats")
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.users",
"indexFilterSet" : false,
"parsedQuery" : {
"age" : {
"$eq" : 16
}
},
"winningPlan" : {
"stage" : "COLLSCAN",
"filter" : {
"age" : {
"$eq" : 16
}
},
"direction" : "forward"
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 826,
"executionTimeMillis" : 37,
"totalKeysExamined" : 0,
"totalDocsExamined" : 100003,
"executionStages" : {
"stage" : "COLLSCAN",
"filter" : {
"age" : {
"$eq" : 16
}
},
"nReturned" : 826,
"executionTimeMillisEstimate" : 2,
"works" : 100005,
"advanced" : 826,
"needTime" : 99178,
"needYield" : 0,
"saveState" : 100,
"restoreState" : 100,
"isEOF" : 1,
"direction" : "forward",
"docsExamined" : 100003
}
},
"serverInfo" : {
"host" : "12c0a2efb72c",
"port" : 27017,
"version" : "4.4.1",
"gitVersion" : "ad91a93a5a31e175f5cbf8c69561e788bbc55ce1"
},
"ok" : 1
}
索引內嵌文件
Data
{ "username" : "sid", "loc" : { "ip" : "1.2.3.4", "city" : "Springfield", "state" : "NY" } }
Create index
> db..createIndex({"loc.city" : 1})
Search
db.users.find({"loc" : {"ip" : "123.456.789.000", "city" : "Shelbyville", "state" : "NY"}}})).
Can not
db.users.find({"loc.city" : "Shelbyville"}).
// prepare data
> db.users.updateOne({"name":"user16"},{"$set":{"comments":{"text":"aaaa","date":new Date()}}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }
> db.users.updateOne({"name":"user14"},{"$set":{"comments":{"text":"aaaa","date":new Date()}}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }
> db.users.updateOne({"name":"user15"},{"$set":{"comments":{"text":"aaaa","date":new Date()}}})
{ "acknowledged" : true, "matchedCount" : 1, "modifiedCount" : 1 }
// create index for comments.text
> db.users.createIndex({"comments.text":1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 3,
"numIndexesAfter" : 4,
"ok" : 1
}
// query by comments.text
> db.users.find({"comments.text":"aaaa"})
{ "_id" : ObjectId("5f73048f0895feaa44434196"), "i" : 14, "name" : "user14", "age" : 27, "created" : ISODate("2020-09-29T09:55:27.798Z"), "comments" : { "text" : "aaaa", "date" : ISODate("2020-10-06T06:37:36.023Z") } }
{ "_id" : ObjectId("5f73048f0895feaa44434197"), "i" : 15, "name" : "user15", "age" : 4, "created" : ISODate("2020-09-29T09:55:27.799Z"), "comments" : { "text" : "aaaa", "date" : ISODate("2020-10-06T06:37:43.407Z") } }
{ "_id" : ObjectId("5f73048f0895feaa44434198"), "i" : 16, "name" : "user16", "age" : 77, "created" : ISODate("2020-09-29T09:55:27.799Z"), "comments" : { "text" : "aaaa", "date" : ISODate("2020-10-06T06:37:18.400Z") } }
>