mongoDB-索引
先插几条数据
db.good.insert({name:"apple",classify:{classifyId:1,classifyName:"fruit"}})
WriteResult({ "nInserted" : 1 })
db.good.insert({name:"banana",classify:{classifyId:1,classifyName:"fruit"}})
WriteResult({ "nInserted" : 1 })
..查看
db.good.find().pretty() { "_id" : ObjectId("5b72c435ead7a34e0b701077"), "name" : "apple", "classify" : { "classifyId" : 1, "classifyName" : "fruit" } } { "_id" : ObjectId("5b72c476ead7a34e0b701078"), "name" : "banana", "classify" : { "classifyId" : 1, "classifyName" : "fruit" } }
..创建简单索引（升序：1；降序：-1）
db.good.createIndex({name:1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
explain查看是否利用了索引
db.good.explain("executionStats").find({name:"apple"})
还可以创建嵌入字段的索引
db.good.createIndex({"classify.classifyId":1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 2,
"numIndexesAfter" : 3,
"ok" : 1
}
explain查看是否利用了索引
db.good.explain("executionStats").find({"classify.classifyId":1})
复合索引
插入三条数据
db.good.insert({name:"apple",price:1.5,classify:["fruit","food"]})
db.good.insert({name:"banana",price:2,classify:["fruit","food"]})
db.good.insert({name:"peach",price:2.5,classify:["fruit","food"]})
创建索引
db.good.createIndex({"name":1,"price":1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 3,
"numIndexesAfter" : 4,
"ok" : 1
}
验证
db.good.explain("executionStats").find({name:"apple"})
db.good.explain("executionStats").find({name:"apple",price: {$lt:2}})
复合索引满足最左前缀规则
–复合索引与排序
1. 查询：sort({name:1,price:1})
db.good.find().sort({name:1,price:1}) { "_id" : ObjectId("5b72c435ead7a34e0b701077"), "name" : "apple", "classify" : { "classifyId" : 1, "classifyName" : "fruit" } } { "_id" : ObjectId("5b73856dead7a34e0b701079"), "name" : "apple", "price" : 1.5, "classify" : [ "fruit", "food" ] } { "_id" : ObjectId("5b72c476ead7a34e0b701078"), "name" : "banana", "classify" : { "classifyId" : 1, "classifyName" : "fruit" } } { "_id" : ObjectId("5b7395edead7a34e0b70107a"), "name" : "banana", "price" : 2, "classify" : [ "fruit", "food" ] } { "_id" : ObjectId("5b7395f3ead7a34e0b70107b"), "name" : "peach", "price" : 2.5, "classify" : [ "fruit", "food" ] }
explain
db.good.explain("executionStats").find().sort({name:1,price:1})
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.good",
"indexFilterSet" : false,
"parsedQuery" : {
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"name" : 1,
"price" : 1
},
"indexName" : "name_1_price_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"name" : [ ],
"price" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"name" : [
"[MinKey, MaxKey]"
],
"price" : [
"[MinKey, MaxKey]"
]
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 5,
"executionTimeMillis" : 0,
"totalKeysExamined" : 5,
"totalDocsExamined" : 5,
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 5,
"executionTimeMillisEstimate" : 0,
"works" : 6,
"advanced" : 5,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 5,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 5,
"executionTimeMillisEstimate" : 0,
"works" : 6,
"advanced" : 5,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"name" : 1,
"price" : 1
},
"indexName" : "name_1_price_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"name" : [ ],
"price" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"name" : [
"[MinKey, MaxKey]"
],
"price" : [
"[MinKey, MaxKey]"
]
},
"keysExamined" : 5,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
},
"serverInfo" : {
"host" : "--",
"port" : 27017,
"version" : "3.6.5",
"gitVersion" : "a20ecd3e3a174162052ff99913bc2ca9a839d618"
},
"ok" : 1
}
2. 查询：sort({name:-1,price:-1})
db.good.find().sort({name:-1,price:-1}) { "_id" : ObjectId("5b7395f3ead7a34e0b70107b"), "name" : "peach", "price" : 2.5, "classify" : [ "fruit", "food" ] } { "_id" : ObjectId("5b7395edead7a34e0b70107a"), "name" : "banana", "price" : 2, "classify" : [ "fruit", "food" ] } { "_id" : ObjectId("5b72c476ead7a34e0b701078"), "name" : "banana", "classify" : { "classifyId" : 1, "classifyName" : "fruit" } } { "_id" : ObjectId("5b73856dead7a34e0b701079"), "name" : "apple", "price" : 1.5, "classify" : [ "fruit", "food" ] } { "_id" : ObjectId("5b72c435ead7a34e0b701077"), "name" : "apple", "classify" : { "classifyId" : 1, "classifyName" : "fruit" } }
explain
db.good.explain("executionStats").find().sort({name:-1,price:-1})
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.good",
"indexFilterSet" : false,
"parsedQuery" : {
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"name" : 1,
"price" : 1
},
"indexName" : "name_1_price_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"name" : [ ],
"price" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "backward",
"indexBounds" : {
"name" : [
"[MaxKey, MinKey]"
],
"price" : [
"[MaxKey, MinKey]"
]
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 5,
"executionTimeMillis" : 0,
"totalKeysExamined" : 5,
"totalDocsExamined" : 5,
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 5,
"executionTimeMillisEstimate" : 0,
"works" : 6,
"advanced" : 5,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 5,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 5,
"executionTimeMillisEstimate" : 0,
"works" : 6,
"advanced" : 5,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"name" : 1,
"price" : 1
},
"indexName" : "name_1_price_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"name" : [ ],
"price" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "backward",
"indexBounds" : {
"name" : [
"[MaxKey, MinKey]"
],
"price" : [
"[MaxKey, MinKey]"
]
},
"keysExamined" : 5,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
},
"serverInfo" : {
"host" : "--",
"port" : 27017,
"version" : "3.6.5",
"gitVersion" : "a20ecd3e3a174162052ff99913bc2ca9a839d618"
},
"ok" : 1
}
3. 查询：sort({name:1,price:-1})
db.good.find().sort({name:1,price:-1}) { "_id" : ObjectId("5b73856dead7a34e0b701079"), "name" : "apple", "price" : 1.5, "classify" : [ "fruit", "food" ] } { "_id" : ObjectId("5b72c435ead7a34e0b701077"), "name" : "apple", "classify" : { "classifyId" : 1, "classifyName" : "fruit" } } { "_id" : ObjectId("5b7395edead7a34e0b70107a"), "name" : "banana", "price" : 2, "classify" : [ "fruit", "food" ] } { "_id" : ObjectId("5b72c476ead7a34e0b701078"), "name" : "banana", "classify" : { "classifyId" : 1, "classifyName" : "fruit" } } { "_id" : ObjectId("5b7395f3ead7a34e0b70107b"), "name" : "peach", "price" : 2.5, "classify" : [ "fruit", "food" ] }
explain：没用到索引
db.good.explain("executionStats").find().sort({name:1,price:-1})
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.good",
"indexFilterSet" : false,
"parsedQuery" : {
},
"winningPlan" : {
"stage" : "SORT",
"sortPattern" : {
"name" : 1,
"price" : -1
},
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "COLLSCAN",
"direction" : "forward"
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 5,
"executionTimeMillis" : 0,
"totalKeysExamined" : 0,
"totalDocsExamined" : 5,
"executionStages" : {
"stage" : "SORT",
"nReturned" : 5,
"executionTimeMillisEstimate" : 0,
"works" : 14,
"advanced" : 5,
"needTime" : 8,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"sortPattern" : {
"name" : 1,
"price" : -1
},
"memUsage" : 515,
"memLimit" : 33554432,
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"nReturned" : 5,
"executionTimeMillisEstimate" : 0,
"works" : 8,
"advanced" : 5,
"needTime" : 2,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"inputStage" : {
"stage" : "COLLSCAN",
"nReturned" : 5,
"executionTimeMillisEstimate" : 0,
"works" : 7,
"advanced" : 5,
"needTime" : 1,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"direction" : "forward",
"docsExamined" : 5
}
}
}
},
"serverInfo" : {
"host" : "--",
"port" : 27017,
"version" : "3.6.5",
"gitVersion" : "a20ecd3e3a174162052ff99913bc2ca9a839d618"
},
"ok" : 1
}
4. 查询：sort({name:-1,price:1})
db.good.find().sort({name:-1,price:1}) { "_id" : ObjectId("5b7395f3ead7a34e0b70107b"), "name" : "peach", "price" : 2.5, "classify" : [ "fruit", "food" ] } { "_id" : ObjectId("5b72c476ead7a34e0b701078"), "name" : "banana", "classify" : { "classifyId" : 1, "classifyName" : "fruit" } } { "_id" : ObjectId("5b7395edead7a34e0b70107a"), "name" : "banana", "price" : 2, "classify" : [ "fruit", "food" ] } { "_id" : ObjectId("5b72c435ead7a34e0b701077"), "name" : "apple", "classify" : { "classifyId" : 1, "classifyName" : "fruit" } } { "_id" : ObjectId("5b73856dead7a34e0b701079"), "name" : "apple", "price" : 1.5, "classify" : [ "fruit", "food" ] }
explain：没用到索引
db.good.explain("executionStats").find().sort({name:-1,price:1})
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.good",
"indexFilterSet" : false,
"parsedQuery" : {
},
"winningPlan" : {
"stage" : "SORT",
"sortPattern" : {
"name" : -1,
"price" : 1
},
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "COLLSCAN",
"direction" : "forward"
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 5,
"executionTimeMillis" : 0,
"totalKeysExamined" : 0,
"totalDocsExamined" : 5,
"executionStages" : {
"stage" : "SORT",
"nReturned" : 5,
"executionTimeMillisEstimate" : 0,
"works" : 14,
"advanced" : 5,
"needTime" : 8,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"sortPattern" : {
"name" : -1,
"price" : 1
},
"memUsage" : 515,
"memLimit" : 33554432,
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"nReturned" : 5,
"executionTimeMillisEstimate" : 0,
"works" : 8,
"advanced" : 5,
"needTime" : 2,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"inputStage" : {
"stage" : "COLLSCAN",
"nReturned" : 5,
"executionTimeMillisEstimate" : 0,
"works" : 7,
"advanced" : 5,
"needTime" : 1,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"direction" : "forward",
"docsExamined" : 5
}
}
}
},
"serverInfo" : {
"host" : "--",
"port" : 27017,
"version" : "3.6.5",
"gitVersion" : "a20ecd3e3a174162052ff99913bc2ca9a839d618"
},
"ok" : 1
}
大概可以得出结论：如果你创建的复合索引类似于{“name”:1,”price”:1}，那么想利用索引进行sort的话，必须{“name”:1,”price”:1}或者{“name”:-1,”price”:-1}，而不能是{“name”:1,”price”:-1}，{“name”:-1,”price”:1}
先删除当前索引
db.good.dropIndex({"name":1,"price":1})
{ "nIndexesWas" : 4, "ok" : 1 }
也可以利用索引名进行删除
db.good.dropIndex("索引名")
再添加一个新的
db.good.createIndex({"name":1,"price":-1}) { "createdCollectionAutomatically" : false, "numIndexesBefore" : 3, "numIndexesAfter" : 4, "ok" : 1 }
1. 查询：sort({name:1,price:1})，没用到索引
db.good.find().sort({name:1,price:1}) { "_id" : ObjectId("5b72c435ead7a34e0b701077"), "name" : "apple", "classify" : { "classifyId" : 1, "classifyName" : "fruit" } } { "_id" : ObjectId("5b73856dead7a34e0b701079"), "name" : "apple", "price" : 1.5, "classify" : [ "fruit", "food" ] } { "_id" : ObjectId("5b72c476ead7a34e0b701078"), "name" : "banana", "classify" : { "classifyId" : 1, "classifyName" : "fruit" } } { "_id" : ObjectId("5b7395edead7a34e0b70107a"), "name" : "banana", "price" : 2, "classify" : [ "fruit", "food" ] } { "_id" : ObjectId("5b7395f3ead7a34e0b70107b"), "name" : "peach", "price" : 2.5, "classify" : [ "fruit", "food" ] } db.good.explain("executionStats").find().sort({name:1,price:1}) { "queryPlanner" : { "plannerVersion" : 1, "namespace" : "test.good", "indexFilterSet" : false, "parsedQuery" : { }, "winningPlan" : { "stage" : "SORT", "sortPattern" : { "name" : 1, "price" : 1 }, "inputStage" : { "stage" : "SORT_KEY_GENERATOR", "inputStage" : { "stage" : "COLLSCAN", "direction" : "forward" } } }, "rejectedPlans" : [ ] }, "executionStats" : { "executionSuccess" : true, "nReturned" : 5, "executionTimeMillis" : 0, "totalKeysExamined" : 0, "totalDocsExamined" : 5, "executionStages" : { "stage" : "SORT", "nReturned" : 5, "executionTimeMillisEstimate" : 0, "works" : 14, "advanced" : 5, "needTime" : 8, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "invalidates" : 0, "sortPattern" : { "name" : 1, "price" : 1 }, "memUsage" : 515, "memLimit" : 33554432, "inputStage" : { "stage" : "SORT_KEY_GENERATOR", "nReturned" : 5, "executionTimeMillisEstimate" : 0, "works" : 8, "advanced" : 5, "needTime" : 2, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "invalidates" : 0, "inputStage" : { "stage" : "COLLSCAN", "nReturned" : 5, "executionTimeMillisEstimate" : 0, "works" : 7, "advanced" : 5, "needTime" : 1, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "invalidates" : 0, "direction" : "forward", "docsExamined" : 5 } } } }, "serverInfo" : { "host" : "--", "port" : 27017, "version" : "3.6.5", "gitVersion" : "a20ecd3e3a174162052ff99913bc2ca9a839d618" }, "ok" : 1 }
2. 查询：sort({name:-1,price:-1})，没用到索引.
db.good.find().sort({name:-1,price:-1}) { "_id" : ObjectId("5b7395f3ead7a34e0b70107b"), "name" : "peach", "price" : 2.5, "classify" : [ "fruit", "food" ] } { "_id" : ObjectId("5b7395edead7a34e0b70107a"), "name" : "banana", "price" : 2, "classify" : [ "fruit", "food" ] } { "_id" : ObjectId("5b72c476ead7a34e0b701078"), "name" : "banana", "classify" : { "classifyId" : 1, "classifyName" : "fruit" } } { "_id" : ObjectId("5b73856dead7a34e0b701079"), "name" : "apple", "price" : 1.5, "classify" : [ "fruit", "food" ] } { "_id" : ObjectId("5b72c435ead7a34e0b701077"), "name" : "apple", "classify" : { "classifyId" : 1, "classifyName" : "fruit" } } db.good.explain("executionStats").find().sort({name:-1,price:-1}) { "queryPlanner" : { "plannerVersion" : 1, "namespace" : "test.good", "indexFilterSet" : false, "parsedQuery" : { }, "winningPlan" : { "stage" : "SORT", "sortPattern" : { "name" : -1, "price" : -1 }, "inputStage" : { "stage" : "SORT_KEY_GENERATOR", "inputStage" : { "stage" : "COLLSCAN", "direction" : "forward" } } }, "rejectedPlans" : [ ] }, "executionStats" : { "executionSuccess" : true, "nReturned" : 5, "executionTimeMillis" : 0, "totalKeysExamined" : 0, "totalDocsExamined" : 5, "executionStages" : { "stage" : "SORT", "nReturned" : 5, "executionTimeMillisEstimate" : 0, "works" : 14, "advanced" : 5, "needTime" : 8, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "invalidates" : 0, "sortPattern" : { "name" : -1, "price" : -1 }, "memUsage" : 515, "memLimit" : 33554432, "inputStage" : { "stage" : "SORT_KEY_GENERATOR", "nReturned" : 5, "executionTimeMillisEstimate" : 0, "works" : 8, "advanced" : 5, "needTime" : 2, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "invalidates" : 0, "inputStage" : { "stage" : "COLLSCAN", "nReturned" : 5, "executionTimeMillisEstimate" : 0, "works" : 7, "advanced" : 5, "needTime" : 1, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "invalidates" : 0, "direction" : "forward", "docsExamined" : 5 } } } }, "serverInfo" : { "host" : "--", "port" : 27017, "version" : "3.6.5", "gitVersion" : "a20ecd3e3a174162052ff99913bc2ca9a839d618" }, "ok" : 1 }
3. 查询：sort({name:1,price:-1})，用到了索引
db.good.find().sort({name:1,price:-1}) { "_id" : ObjectId("5b73856dead7a34e0b701079"), "name" : "apple", "price" : 1.5, "classify" : [ "fruit", "food" ] } { "_id" : ObjectId("5b72c435ead7a34e0b701077"), "name" : "apple", "classify" : { "classifyId" : 1, "classifyName" : "fruit" } } { "_id" : ObjectId("5b7395edead7a34e0b70107a"), "name" : "banana", "price" : 2, "classify" : [ "fruit", "food" ] } { "_id" : ObjectId("5b72c476ead7a34e0b701078"), "name" : "banana", "classify" : { "classifyId" : 1, "classifyName" : "fruit" } } { "_id" : ObjectId("5b7395f3ead7a34e0b70107b"), "name" : "peach", "price" : 2.5, "classify" : [ "fruit", "food" ] } db.good.explain("executionStats").find().sort({name:1,price:-1}) { "queryPlanner" : { "plannerVersion" : 1, "namespace" : "test.good", "indexFilterSet" : false, "parsedQuery" : { }, "winningPlan" : { "stage" : "FETCH", "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "name" : 1, "price" : -1 }, "indexName" : "name_1_price_-1", "isMultiKey" : false, "multiKeyPaths" : { "name" : [ ], "price" : [ ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "name" : [ "[MinKey, MaxKey]" ], "price" : [ "[MaxKey, MinKey]" ] } } }, "rejectedPlans" : [ ] }, "executionStats" : { "executionSuccess" : true, "nReturned" : 5, "executionTimeMillis" : 0, "totalKeysExamined" : 5, "totalDocsExamined" : 5, "executionStages" : { "stage" : "FETCH", "nReturned" : 5, "executionTimeMillisEstimate" : 0, "works" : 6, "advanced" : 5, "needTime" : 0, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "invalidates" : 0, "docsExamined" : 5, "alreadyHasObj" : 0, "inputStage" : { "stage" : "IXSCAN", "nReturned" : 5, "executionTimeMillisEstimate" : 0, "works" : 6, "advanced" : 5, "needTime" : 0, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "invalidates" : 0, "keyPattern" : { "name" : 1, "price" : -1 }, "indexName" : "name_1_price_-1", "isMultiKey" : false, "multiKeyPaths" : { "name" : [ ], "price" : [ ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "name" : [ "[MinKey, MaxKey]" ], "price" : [ "[MaxKey, MinKey]" ] }, "keysExamined" : 5, "seeks" : 1, "dupsTested" : 0, "dupsDropped" : 0, "seenInvalidated" : 0 } } }, "serverInfo" : { "host" : "--", "port" : 27017, "version" : "3.6.5", "gitVersion" : "a20ecd3e3a174162052ff99913bc2ca9a839d618" }, "ok" : 1 }
4. 查询：sort({name:-1,price:1})，用到了索引
db.good.find().sort({name:-1,price:1}) { "_id" : ObjectId("5b7395f3ead7a34e0b70107b"), "name" : "peach", "price" : 2.5, "classify" : [ "fruit", "food" ] } { "_id" : ObjectId("5b72c476ead7a34e0b701078"), "name" : "banana", "classify" : { "classifyId" : 1, "classifyName" : "fruit" } } { "_id" : ObjectId("5b7395edead7a34e0b70107a"), "name" : "banana", "price" : 2, "classify" : [ "fruit", "food" ] } { "_id" : ObjectId("5b72c435ead7a34e0b701077"), "name" : "apple", "classify" : { "classifyId" : 1, "classifyName" : "fruit" } } { "_id" : ObjectId("5b73856dead7a34e0b701079"), "name" : "apple", "price" : 1.5, "classify" : [ "fruit", "food" ] } db.good.explain("executionStats").find().sort({name:-1,price:1}) { "queryPlanner" : { "plannerVersion" : 1, "namespace" : "test.good", "indexFilterSet" : false, "parsedQuery" : { }, "winningPlan" : { "stage" : "FETCH", "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "name" : 1, "price" : -1 }, "indexName" : "name_1_price_-1", "isMultiKey" : false, "multiKeyPaths" : { "name" : [ ], "price" : [ ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "backward", "indexBounds" : { "name" : [ "[MaxKey, MinKey]" ], "price" : [ "[MinKey, MaxKey]" ] } } }, "rejectedPlans" : [ ] }, "executionStats" : { "executionSuccess" : true, "nReturned" : 5, "executionTimeMillis" : 0, "totalKeysExamined" : 5, "totalDocsExamined" : 5, "executionStages" : { "stage" : "FETCH", "nReturned" : 5, "executionTimeMillisEstimate" : 0, "works" : 6, "advanced" : 5, "needTime" : 0, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "invalidates" : 0, "docsExamined" : 5, "alreadyHasObj" : 0, "inputStage" : { "stage" : "IXSCAN", "nReturned" : 5, "executionTimeMillisEstimate" : 0, "works" : 6, "advanced" : 5, "needTime" : 0, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "invalidates" : 0, "keyPattern" : { "name" : 1, "price" : -1 }, "indexName" : "name_1_price_-1", "isMultiKey" : false, "multiKeyPaths" : { "name" : [ ], "price" : [ ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "backward", "indexBounds" : { "name" : [ "[MaxKey, MinKey]" ], "price" : [ "[MinKey, MaxKey]" ] }, "keysExamined" : 5, "seeks" : 1, "dupsTested" : 0, "dupsDropped" : 0, "seenInvalidated" : 0 } } }, "serverInfo" : { "host" : "--", "port" : 27017, "version" : "3.6.5", "gitVersion" : "a20ecd3e3a174162052ff99913bc2ca9a839d618" }, "ok" : 1 }
所以，如果你创建的复合索引类似于{“name”:1,”price”:-1}，那么想利用索引进行sort的话，必须{“name”:1,”price”:-1}或者{“name”:-1,”price”:1}，而不能是{“name”:1,”price”:1}，{“name”:-1,”price”:-1}
唯一索引
db.good.createIndex({name:1},{unique:true})
稀疏索引(只有包含某些索引键值的文档才会出现)
db.good.createIndex({price:1},{unique:false,sparse:true})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 4,
"numIndexesAfter" : 5,
"ok" : 1
}
查询
db.good.find({price:{$gt:0}}) { "_id" : ObjectId("5b73856dead7a34e0b701079"), "name" : "apple", "price" : 1.5, "classify" : [ "fruit", "food" ] } { "_id" : ObjectId("5b7395edead7a34e0b70107a"), "name" : "banana", "price" : 2, "classify" : [ "fruit", "food" ] } { "_id" : ObjectId("5b7395f3ead7a34e0b70107b"), "name" : "peach", "price" : 2.5, "classify" : [ "fruit", "food" ] } db.good.explain("executionStats").find({price:{$gt:0}}) { "queryPlanner" : { "plannerVersion" : 1, "namespace" : "test.good", "indexFilterSet" : false, "parsedQuery" : { "price" : { "$gt" : 0 } }, "winningPlan" : { "stage" : "FETCH", "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "price" : 1 }, "indexName" : "price_1", "isMultiKey" : false, "multiKeyPaths" : { "price" : [ ] }, "isUnique" : false, "isSparse" : true, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "price" : [ "(0.0, inf.0]" ] } } }, "rejectedPlans" : [ ] }, "executionStats" : { "executionSuccess" : true, "nReturned" : 3, "executionTimeMillis" : 0, "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, "invalidates" : 0, "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, "invalidates" : 0, "keyPattern" : { "price" : 1 }, "indexName" : "price_1", "isMultiKey" : false, "multiKeyPaths" : { "price" : [ ] }, "isUnique" : false, "isSparse" : true, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "price" : [ "(0.0, inf.0]" ] }, "keysExamined" : 3, "seeks" : 1, "dupsTested" : 0, "dupsDropped" : 0, "seenInvalidated" : 0 } } }, "serverInfo" : { "host" : "--", "port" : 27017, "version" : "3.6.5", "gitVersion" : "a20ecd3e3a174162052ff99913bc2ca9a839d618" }, "ok" : 1 }
查看索引大小
db.good.totalIndexSize() 143360
小提示：重建索引（如果应用对数据库执行大量更新和删除操作，会产生索引碎片。碎片会导致使用更多的内存空间。而重建索引会占用写入锁）
查看集合信息也包括了索引信息
db.good.stats()
多键索引（针对于数组）
添加几条数据
db.good.insert([{name:"apple",ratings:[5,3,10,9]},{name:"banana",ratings:[10,10,9,10]},{name:"orange",ratings:[2,7,5,9]}])
BulkWriteResult({
"writeErrors" : [ ],
"writeConcernErrors" : [ ],
"nInserted" : 3,
"nUpserted" : 0,
"nMatched" : 0,
"nModified" : 0,
"nRemoved" : 0,
"upserted" : [ ]
})
创建索引
db.good.createIndex({ratings:1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 5,
"numIndexesAfter" : 6,
"ok" : 1
}
查询
db.good.find({ratings:{$eq:10}}) { "_id" : ObjectId("5b73c4f9ead7a34e0b70107c"), "name" : "apple", "ratings" : [ 5, 3, 10, 9 ] } { "_id" : ObjectId("5b73c4f9ead7a34e0b70107d"), "name" : "banana", "ratings" : [ 10, 10, 9, 10 ] } db.good.find({ratings:{$gte:2,$lte:5}}) { "_id" : ObjectId("5b73c4f9ead7a34e0b70107e"), "name" : "orange", "ratings" : [ 2, 7, 5, 9 ] } { "_id" : ObjectId("5b73c4f9ead7a34e0b70107c"), "name" : "apple", "ratings" : [ 5, 3, 10, 9 ] }
explain
db.good.explain("executionStats").find({ratings:{$gte:2,$lte:5}})
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "test.good",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"ratings" : {
"$lte" : 5
}
},
{
"ratings" : {
"$gte" : 2
}
}
]
},
"winningPlan" : {
"stage" : "FETCH",
"filter" : {
"ratings" : {
"$gte" : 2
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"ratings" : 1
},
"indexName" : "ratings_1",
"isMultiKey" : true,
"multiKeyPaths" : {
"ratings" : [
"ratings"
]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"ratings" : [
"[-inf.0, 5.0]"
]
}
}
},
"rejectedPlans" : [
{
"stage" : "FETCH",
"filter" : {
"ratings" : {
"$lte" : 5
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"ratings" : 1
},
"indexName" : "ratings_1",
"isMultiKey" : true,
"multiKeyPaths" : {
"ratings" : [
"ratings"
]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"ratings" : [
"[2.0, inf.0]"
]
}
}
}
]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 2,
"executionTimeMillis" : 0,
"totalKeysExamined" : 4,
"totalDocsExamined" : 2,
"executionStages" : {
"stage" : "FETCH",
"filter" : {
"ratings" : {
"$gte" : 2
}
},
"nReturned" : 2,
"executionTimeMillisEstimate" : 0,
"works" : 6,
"advanced" : 2,
"needTime" : 2,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 2,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 2,
"executionTimeMillisEstimate" : 0,
"works" : 5,
"advanced" : 2,
"needTime" : 2,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"ratings" : 1
},
"indexName" : "ratings_1",
"isMultiKey" : true,
"multiKeyPaths" : {
"ratings" : [
"ratings"
]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"ratings" : [
"[-inf.0, 5.0]"
]
},
"keysExamined" : 4,
"seeks" : 1,
"dupsTested" : 4,
"dupsDropped" : 2,
"seenInvalidated" : 0
}
}
},
"serverInfo" : {
"host" : "--",
"port" : 27017,
"version" : "3.6.5",
"gitVersion" : "a20ecd3e3a174162052ff99913bc2ca9a839d618"
},
"ok" : 1
}
类比：多键索引类似于Lucene的倒排索引（数组中的元素指向对应的文档）
多键索引的相交边界
比如给定两个界限[3,+无穷],[-无穷,6],那么显而易见，交集是[3,6]
对于多键索引，如果使用$elemMatch连接谓词，MongoDB可以交叉多键索引边界。
添加数据：
db.good.insert([{name:"AAA",ratings:[82,85,88]},{name:"BBB",ratings:[75,88,89]}])
BulkWriteResult({
"writeErrors" : [ ],
"writeConcernErrors" : [ ],
"nInserted" : 2,
"nUpserted" : 0,
"nMatched" : 0,
"nModified" : 0,
"nRemoved" : 0,
"upserted" : [ ]
})
查询
db.good.find({ratings:{$elemMatch:{$gte:80,$lte:85}}}) { "_id" : ObjectId("5b73d7a6ead7a34e0b701083"), "name" : "AAA", "ratings" : [ 82, 85, 88 ] } 这个查询表示：要求数组中至少包含一个满足大于等于80,、小于等于85条件的元素，因为使用了$elemMatch，所以可以交叉边界到[80,85]
db.good.find({ratings:{$gte:80,$lte:85}}) { "_id" : ObjectId("5b73d7a6ead7a34e0b701083"), "name" : "AAA", "ratings" : [ 82, 85, 88 ] } { "_id" : ObjectId("5b73d7a6ead7a34e0b701084"), "name" : "BBB", "ratings" : [ 75, 88, 89 ] } 如果不使用$elemMatch，就不会交叉边界，MongoDB不能保证它选择两种边界的哪一个 如果只有一个查询条件，那么$elemMatch不是必须的
多键索引的复合边界
复合边界是指对复合索引的多个键使用边界。
添加数据：
db.good.insert([{manager:"EEE",score:[82,85,88]},{manager:"EEE",score:[75,88,89]}])
BulkWriteResult({
"writeErrors" : [ ],
"writeConcernErrors" : [ ],
"nInserted" : 2,
"nUpserted" : 0,
"nMatched" : 0,
"nModified" : 0,
"nRemoved" : 0,
"upserted" : [ ]
})
创建索引
db.good.createIndex({manager:1,score:1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 6,
"numIndexesAfter" : 7,
"ok" : 1
}
查询：
db.good.find({manager: "EEE", score : { $elemMatch: { $gte: 80, $lte: 85 } }}) { "_id" : ObjectId("5b7410edead7a34e0b701089"), "manager" : "EEE", "score" : [ 82, 85, 88 ] } db.good.explain("executionStats").find({manager: "EEE", score : { $elemMatch: { $gte: 80, $lte: 85 } }}) { "queryPlanner" : { "plannerVersion" : 1, "namespace" : "test.good", "indexFilterSet" : false, "parsedQuery" : { "$and" : [ { "score" : { "$elemMatch" : { "$lte" : 85, "$gte" : 80 } } }, { "manager" : { "$eq" : "EEE" } } ] }, "winningPlan" : { "stage" : "FETCH", "filter" : { "score" : { "$elemMatch" : { "$lte" : 85, "$gte" : 80 } } }, "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "manager" : 1, "score" : 1 }, "indexName" : "manager_1_score_1", "isMultiKey" : true, "multiKeyPaths" : { "manager" : [ ], "score" : [ "score" ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "manager" : [ "[\"EEE\", \"EEE\"]" ], "score" : [ "[80.0, 85.0]" ] } } }, "rejectedPlans" : [ ] }, "executionStats" : { "executionSuccess" : true, "nReturned" : 1, "executionTimeMillis" : 11, "totalKeysExamined" : 2, "totalDocsExamined" : 1, "executionStages" : { "stage" : "FETCH", "filter" : { "score" : { "$elemMatch" : { "$lte" : 85, "$gte" : 80 } } }, "nReturned" : 1, "executionTimeMillisEstimate" : 0, "works" : 3, "advanced" : 1, "needTime" : 1, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "invalidates" : 0, "docsExamined" : 1, "alreadyHasObj" : 0, "inputStage" : { "stage" : "IXSCAN", "nReturned" : 1, "executionTimeMillisEstimate" : 0, "works" : 3, "advanced" : 1, "needTime" : 1, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "invalidates" : 0, "keyPattern" : { "manager" : 1, "score" : 1 }, "indexName" : "manager_1_score_1", "isMultiKey" : true, "multiKeyPaths" : { "manager" : [ ], "score" : [ "score" ] }, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "forward", "indexBounds" : { "manager" : [ "[\"EEE\", \"EEE\"]" ], "score" : [ "[80.0, 85.0]" ] }, "keysExamined" : 2, "seeks" : 1, "dupsTested" : 2, "dupsDropped" : 1, "seenInvalidated" : 0 } } }, "serverInfo" : { "host" : "--", "port" : 27017, "version" : "3.6.5", "gitVersion" : "a20ecd3e3a174162052ff99913bc2ca9a839d618" }, "ok" : 1 }
全文索引（一个集合只能有一个Text索引）
添加数据
db.good.insert([{manager:"admin",article:"A woman\'s heart is a deep ocean of secrets."},{manager:"admin",article:"I was born intelligent - education ruined me. --Bernard Shaw"}])
创建索引
db.good.createIndex({article:"text"})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 7,
"numIndexesAfter" : 8,
"ok" : 1
}
如果你索引所有字段：
db.good.createIndex({"$**":"text"},{name:"my_text_index"})
查询：
db.good.find({$text:{$search:"woman"}}) { "_id" : ObjectId("5b7414e5ead7a34e0b70108b"), "manager" : "admin", "article" : "A woman\'s heart is a deep ocean of secrets." } db.good.explain("executionStats").find({$text:{$search:"woman"}}) { "queryPlanner" : { "plannerVersion" : 1, "namespace" : "test.good", "indexFilterSet" : false, "parsedQuery" : { "$text" : { "$search" : "woman", "$language" : "english", "$caseSensitive" : false, "$diacriticSensitive" : false } }, "winningPlan" : { "stage" : "TEXT", "indexPrefix" : { }, "indexName" : "article_text", "parsedTextQuery" : { "terms" : [ "woman" ], "negatedTerms" : [ ], "phrases" : [ ], "negatedPhrases" : [ ] }, "textIndexVersion" : 3, "inputStage" : { "stage" : "TEXT_MATCH", "inputStage" : { "stage" : "FETCH", "inputStage" : { "stage" : "OR", "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "_fts" : "text", "_ftsx" : 1 }, "indexName" : "article_text", "isMultiKey" : true, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "backward", "indexBounds" : { } } } } } }, "rejectedPlans" : [ ] }, "executionStats" : { "executionSuccess" : true, "nReturned" : 1, "executionTimeMillis" : 0, "totalKeysExamined" : 1, "totalDocsExamined" : 1, "executionStages" : { "stage" : "TEXT", "nReturned" : 1, "executionTimeMillisEstimate" : 0, "works" : 2, "advanced" : 1, "needTime" : 0, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "invalidates" : 0, "indexPrefix" : { }, "indexName" : "article_text", "parsedTextQuery" : { "terms" : [ "woman" ], "negatedTerms" : [ ], "phrases" : [ ], "negatedPhrases" : [ ] }, "textIndexVersion" : 3, "inputStage" : { "stage" : "TEXT_MATCH", "nReturned" : 1, "executionTimeMillisEstimate" : 0, "works" : 2, "advanced" : 1, "needTime" : 0, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "invalidates" : 0, "docsRejected" : 0, "inputStage" : { "stage" : "FETCH", "nReturned" : 1, "executionTimeMillisEstimate" : 0, "works" : 2, "advanced" : 1, "needTime" : 0, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "invalidates" : 0, "docsExamined" : 1, "alreadyHasObj" : 0, "inputStage" : { "stage" : "OR", "nReturned" : 1, "executionTimeMillisEstimate" : 0, "works" : 2, "advanced" : 1, "needTime" : 0, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "invalidates" : 0, "dupsTested" : 1, "dupsDropped" : 0, "recordIdsForgotten" : 0, "inputStage" : { "stage" : "IXSCAN", "nReturned" : 1, "executionTimeMillisEstimate" : 0, "works" : 2, "advanced" : 1, "needTime" : 0, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "invalidates" : 0, "keyPattern" : { "_fts" : "text", "_ftsx" : 1 }, "indexName" : "article_text", "isMultiKey" : true, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "backward", "indexBounds" : { }, "keysExamined" : 1, "seeks" : 1, "dupsTested" : 1, "dupsDropped" : 0, "seenInvalidated" : 0 } } } } } }, "serverInfo" : { "host" : "--", "port" : 27017, "version" : "3.6.5", "gitVersion" : "a20ecd3e3a174162052ff99913bc2ca9a839d618" }, "ok" : 1 }
指定权重（这些权重表示索引字段之间的相对重要性）
添加数据
db.good.insertMany([{title:"God Had to Be Fair",content:"We always knew our daughter Kendall was going be a performer of some sort."},{title:"God Had to Be Fair",content:"When Kendall was five, we began to notice that she was blinking a lot and clearing her throat frequently."}])
{
"acknowledged" : true,
"insertedIds" : [
ObjectId("5b74da5cead7a34e0b70108d"),
ObjectId("5b74da5cead7a34e0b70108e")
]
}
db.good.insertMany([{title:"The Doll and the White Rose",content:"I hurried into the local department store to grab1 some last minute Chirsmas gifts."},{title:"The Doll and the White Rose",content:"But I hurried the best I could through all the people to the toy department. "}])
{
"acknowledged" : true,
"insertedIds" : [
ObjectId("5b74da71ead7a34e0b70108f"),
ObjectId("5b74da71ead7a34e0b701090")
]
}
创建索引
db.good.createIndex({title:"text",content:"text"},{weights:{title:5,content:10},name:"title_content_text"})
{
"ok" : 0,
"errmsg" : "Index: { v: 2, key: { _fts: \"text\", _ftsx: 1 }, name: \"title_content_text\", ns: \"test.good\", weights: { content: 10, title: 5 }, default_language: \"english\", language_override: \"language\", textIndexVersion: 3 } already exists with different options: { v: 2, key: { _fts: \"text\", _ftsx: 1 }, name: \"article_text\", ns: \"test.good\", weights: { article: 1 }, default_language: \"english\", language_override: \"language\", textIndexVersion: 3 }",
"code" : 85,
"codeName" : "IndexOptionsConflict"
}
可以看出，一个集合不允许出现多个text索引
db.good.dropIndex("article_text")
{ "nIndexesWas" : 8, "ok" : 1 }
db.good.createIndex({title:"text",content:"text"},{weights:{title:5,content:10},name:"title_content_text"})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 7,
"numIndexesAfter" : 8,
"ok" : 1
}
查询
db.good.find({$text:{$search:"Kendall"}}) { "_id" : ObjectId("5b74da5cead7a34e0b70108d"), "title" : "God Had to Be Fair", "content" : "We always knew our daughter Kendall was going be a performer of some sort." } { "_id" : ObjectId("5b74da5cead7a34e0b70108e"), "title" : "God Had to Be Fair", "content" : "When Kendall was five, we began to notice that she was blinking a lot and clearing her throat frequently." } db.good.find({$text:{$search:"God"}}) { "_id" : ObjectId("5b74da5cead7a34e0b70108e"), "title" : "God Had to Be Fair", "content" : "When Kendall was five, we began to notice that she was blinking a lot and clearing her throat frequently." } { "_id" : ObjectId("5b74da5cead7a34e0b70108d"), "title" : "God Had to Be Fair", "content" : "We always knew our daughter Kendall was going be a performer of some sort." } db.good.find({$text:{$search:"hurried"}}) { "_id" : ObjectId("5b74da71ead7a34e0b701090"), "title" : "The Doll and the White Rose", "content" : "But I hurried the best I could through all the people to the toy department. " } { "_id" : ObjectId("5b74da71ead7a34e0b70108f"), "title" : "The Doll and the White Rose", "content" : "I hurried into the local department store to grab1 some last minute Chirsmas gifts." } db.good.explain("executionStats").find({$text:{$search:"Kendall"}}) { "queryPlanner" : { "plannerVersion" : 1, "namespace" : "test.good", "indexFilterSet" : false, "parsedQuery" : { "$text" : { "$search" : "Kendall", "$language" : "english", "$caseSensitive" : false, "$diacriticSensitive" : false } }, "winningPlan" : { "stage" : "TEXT", "indexPrefix" : { }, "indexName" : "title_content_text", "parsedTextQuery" : { "terms" : [ "kendal" ], "negatedTerms" : [ ], "phrases" : [ ], "negatedPhrases" : [ ] }, "textIndexVersion" : 3, "inputStage" : { "stage" : "TEXT_MATCH", "inputStage" : { "stage" : "FETCH", "inputStage" : { "stage" : "OR", "inputStage" : { "stage" : "IXSCAN", "keyPattern" : { "_fts" : "text", "_ftsx" : 1 }, "indexName" : "title_content_text", "isMultiKey" : true, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "backward", "indexBounds" : { } } } } } }, "rejectedPlans" : [ ] }, "executionStats" : { "executionSuccess" : true, "nReturned" : 2, "executionTimeMillis" : 6, "totalKeysExamined" : 2, "totalDocsExamined" : 2, "executionStages" : { "stage" : "TEXT", "nReturned" : 2, "executionTimeMillisEstimate" : 0, "works" : 3, "advanced" : 2, "needTime" : 0, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "invalidates" : 0, "indexPrefix" : { }, "indexName" : "title_content_text", "parsedTextQuery" : { "terms" : [ "kendal" ], "negatedTerms" : [ ], "phrases" : [ ], "negatedPhrases" : [ ] }, "textIndexVersion" : 3, "inputStage" : { "stage" : "TEXT_MATCH", "nReturned" : 2, "executionTimeMillisEstimate" : 0, "works" : 3, "advanced" : 2, "needTime" : 0, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "invalidates" : 0, "docsRejected" : 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" : "OR", "nReturned" : 2, "executionTimeMillisEstimate" : 0, "works" : 3, "advanced" : 2, "needTime" : 0, "needYield" : 0, "saveState" : 0, "restoreState" : 0, "isEOF" : 1, "invalidates" : 0, "dupsTested" : 2, "dupsDropped" : 0, "recordIdsForgotten" : 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" : { "_fts" : "text", "_ftsx" : 1 }, "indexName" : "title_content_text", "isMultiKey" : true, "isUnique" : false, "isSparse" : false, "isPartial" : false, "indexVersion" : 2, "direction" : "backward", "indexBounds" : { }, "keysExamined" : 2, "seeks" : 1, "dupsTested" : 2, "dupsDropped" : 0, "seenInvalidated" : 0 } } } } } }, "serverInfo" : { "host" : "--", "port" : 27017, "version" : "3.6.5", "gitVersion" : "a20ecd3e3a174162052ff99913bc2ca9a839d618" }, "ok" : 1 }
限制Text扫描的数量
例如文档：{type:”animal”,content:”Animal world”},{type:”fruit”,content:”Fruit world”}
建立复合索引：createIndex({type:1,content:”text”})
查询：find(type:”animal”,$text:{$search:”Animal”})
这样，只会在type为animal的文档中去搜索关键字，而不会在所有文档中搜索。
复合text索引不能包括其它特殊索引类型：比如多键索引
地理空间索引：2dsphere索引（还有一个2d索引，适用于MongoDB2.2以及更早版本，这里不再赘述）
GeoJSON例子：
GeoJSON Point: <field>: { type: <GeoJSON type> , coordinates: <coordinates> } Example: location: { type: "Point", coordinates: [-73.856077, 40.848447] }
添加数据
db.area.insertMany([{local : { type: "Point", coordinates: [ -73.97, 40.77 ] },name: "Great Barrier Reef",category : "Reef"},{local : { type: "Point", coordinates: [ -60.27, 50.66 ] },name: "The Grand Canyon",category : "Canyon"}])
Grand Canyon",category : "Canyon"}])
{
"acknowledged" : true,
"insertedIds" : [
ObjectId("5b7514d7081367733746b097"),
ObjectId("5b7514d7081367733746b098")
]
}
创建索引：与2d索引不同，复合2dsphere索引不需要将位置字段作为第一个索引字段
db.area.createIndex({name:1,local:"2dsphere"})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
查询
找到在多边形范围内的所有点和形状 db.area.find( { local :{ $geoWithin :{ $geometry :{ type : "Polygon" ,coordinates : [ [[ -61 , -61 ] ,[ -61 , 61 ] ,[ 61 , 61 ] ,[ 61 , -61 ],[ -61 , -61 ]] ]} } } } ) { "_id" : ObjectId("5b7514d7081367733746b098"), "local" : { "type" : "Point", "coordinates" : [ -60.27, 50.66 ] }, "name" : "The Grand Canyon", "category" : "Canyon" }
查询与指定的GeoJSON对象相交的位置 db.area.find( { local :{ $geoIntersects :{ $geometry :{ type : "Polygon" ,coordinates: [ [[ 0 , 0 ] ,[ -73.97 , 40.77 ] ,[ -60 , -31 ] ,[ 0 , 0 ]] ]} } } } ) { "_id" : ObjectId("5b7514d7081367733746b097"), "local" : { "type" : "Point", "coordinates" : [ -73.97, 40.77 ] }, "name" : "Great Barrier Reef", "category" : "Reef" } db.area.find( { local :{ $geoIntersects :{ $geometry :{ type : "Polygon" ,coordinates: [ [[ 0 , 0 ] ,[ -75 , 60 ] ,[ -60 , -31 ] ,[ 0 , 0 ]] ]} } } } ) { "_id" : ObjectId("5b7514d7081367733746b098"), "local" : { "type" : "Point", "coordinates" : [ -60.27, 50.66 ] }, "name" : "The Grand Canyon", "category" : "Canyon" }
查询离定义点最近的点，并按距离对结果进行排序。GeoJSON数据上的接近查询需要2dsphere索引。 db.area.find( { local: { $near : { $geometry: { type: "Point", coordinates: [ <longitude> , <latitude> ] }, $minDistance: <单位是米>, $maxDistance: <单位是米> } } } ) db.area.createIndex({local:"2dsphere"}) { "createdCollectionAutomatically" : false, "numIndexesBefore" : 2, "numIndexesAfter" : 3, "ok" : 1 } db.area.find({local:{ $near :{$geometry: { type: "Point", coordinates: [ -73.97, 40.77 ] },$minDistance: 10000,$maxDistance: 5000000}}}) { "_id" : ObjectId("5b7514d7081367733746b098"), "local" : { "type" : "Point", "coordinates" : [ -60.27, 50.66 ] }, "name" : "The Grand Canyon", "category" : "Canyon" }
..
以定义的点为中心，查询指定范围内的点【查询指定经纬度，10000英里范围内的文档】 db.area.find( { local :{ $geoWithin :{ $centerSphere :[ [ -50 , 30 ] , 10000 / 3963.2 ]} } } ) { "_id" : ObjectId("5b7514d7081367733746b097"), "local" : { "type" : "Point", "coordinates" : [ -73.97, 40.77 ] }, "name" : "Great Barrier Reef", "category" : "Reef" } { "_id" : ObjectId("5b7514d7081367733746b098"), "local" : { "type" : "Point", "coordinates" : [ -60.27, 50.66 ] }, "name" : "The Grand Canyon", "category" : "Canyon" }
地理空间索引：geoHaystack索引
geoHaystack索引包括一个geoHaystack索引键和一个非地理空间索引键
geoHaystack索引只支持简单的二进制比较，不支持排序。
索引必须引用两个字段:location字段和第二个字段。第二个字段用于精确匹配
要构建haystack索引，在创建索引时必须指定桶大小选项。桶大小为5的索引将定位值分组到指定经度和纬度的5个单位内。桶大小还决定了索引的粒度。
添加数据
db.hay_area.insertMany([{pos: { lng : 126.9, lat : 35.2 } , type : "restaurant"},{pos: { lng : 128.0, lat : 36.7 } , type : "national park"}])
{
"acknowledged" : true,
"insertedIds" : [
ObjectId("5b74ee5eead7a34e0b701091"),
ObjectId("5b74ee5eead7a34e0b701092")
]
}
创建索引
db.hay_area.createIndex({pos:"geoHaystack",type:1},{bucketSize:1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}
要查询haystack索引，请使用geoSearch(类似：geoNear)命令。必须同时指定坐标和要进行地理搜索的附加字段.Haystack索引不适合查询最接近特定位置的完整文档列表
db.runCommand( { geoSearch : "hay_area" ,search : { type: "restaurant" } ,near : [125.9, 34.6] ,maxDistance : 10 } )
{
"results" : [
{
"_id" : ObjectId("5b74ee5eead7a34e0b701091"),
"pos" : {
"lng" : 126.9,
"lat" : 35.2
},
"type" : "restaurant"
}
],
"stats" : {
"time" : 1,
"btreeMatches" : 1,
"n" : 1
},
"ok" : 1
}
TTL索引（MongoDB可以使用它在特定的时间之后自动从集合中删除文档）
使用该索引的字段必须是时间字段，或者包含日期的数组。并且TTL索引是单键索引。 db.demo.createIndex({"updateTime":1},{expireAfterSeconds: 3600}) db.log_events.insert( { "createdAt": new Date(), "logEvent": 2, "logMessage": "Success!" } )
后台完成创建索引的工作
db.people.createIndex( { zipcode: 1 }, { background: true } )
版权声明：本文为LUA123原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。