1.使用复合索引
(1).索引的方向
2个字段都按照升序
{"age" : 1, "username" : 1}age按照升序，username按照降序
{"age" : 1, "username" : -1}以下2个索引是一样的
{"age" : 1, "username" : -1}
{"age" : -1, "username" : 1}(2).使用覆盖索引（COVERED INDEXES）
覆盖索引就是只使用索引字段返回数据，具体例子如下：
首先查询当前表上的索引
wang> db.users.getIndexes()
[
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_"
},
{
"v" : 2,
"key" : {
"username" : 1
},
"name" : "username_1"
},
{
"v" : 2,
"key" : {
"age" : 1,
"username" : 1
},
"name" : "age_1_username_1"
}
]
wang> 使用覆盖索引进行查询
重点关注stage为IXSCAN，在executionStats中，totalDocsExamined为0表示就是使用了覆盖索引
wang> db.users.find({age:21,username:'user997679'},{age:1,username:1,_id:0}).explain("executionStats")
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "wang.users",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"age" : {
"$eq" : 21
}
},
{
"username" : {
"$eq" : "user997679"
}
}
]
},
"winningPlan" : {
"stage" : "PROJECTION_COVERED",
"transformBy" : {
"age" : 1,
"username" : 1,
"_id" : 0
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"age" : 1,
"username" : 1
},
"indexName" : "age_1_username_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"age" : [ ],
"username" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"age" : [
"[21.0, 21.0]"
],
"username" : [
"[\"user997679\", \"user997679\"]"
]
}
}
},
"rejectedPlans" : [
{
"stage" : "PROJECTION_SIMPLE",
"transformBy" : {
"age" : 1,
"username" : 1,
"_id" : 0
},
"inputStage" : {
"stage" : "FETCH",
"filter" : {
"age" : {
"$eq" : 21
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"username" : 1
},
"indexName" : "username_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"username" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"username" : [
"[\"user997679\", \"user997679\"]"
]
}
}
}
}
]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 1,
"executionTimeMillis" : 0,
"totalKeysExamined" : 1,
"totalDocsExamined" : 0,
"executionStages" : {
"stage" : "PROJECTION_COVERED",
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
"works" : 3,
"advanced" : 1,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"transformBy" : {
"age" : 1,
"username" : 1,
"_id" : 0
},
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
"works" : 3,
"advanced" : 1,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"keyPattern" : {
"age" : 1,
"username" : 1
},
"indexName" : "age_1_username_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"age" : [ ],
"username" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"age" : [
"[21.0, 21.0]"
],
"username" : [
"[\"user997679\", \"user997679\"]"
]
},
"keysExamined" : 1,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0
}
}
},
"serverInfo" : {
"host" : "localhost.localdomain",
"port" : 27017,
"version" : "4.4.0",
"gitVersion" : "563487e100c4215e2dce98d0af2a6a5a2d67c5cf"
},
"ok" : 1
}
wang> 以下方式就是没有使用到覆盖索引（虽然stage为IXSCAN，但是totalDocsExamined不为0）
wang> db.users.find({age:21,username:'user997679'}).explain("executionStats")
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "wang.users",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"age" : {
"$eq" : 21
}
},
{
"username" : {
"$eq" : "user997679"
}
}
]
},
"winningPlan" : {
"stage" : "FETCH",
"filter" : {
"age" : {
"$eq" : 21
}
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"username" : 1
},
"indexName" : "username_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"username" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"username" : [
"[\"user997679\", \"user997679\"]"
]
}
}
},
"rejectedPlans" : [
{
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"age" : 1,
"username" : 1
},
"indexName" : "age_1_username_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"age" : [ ],
"username" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"age" : [
"[21.0, 21.0]"
],
"username" : [
"[\"user997679\", \"user997679\"]"
]
}
}
}
]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 1,
"executionTimeMillis" : 0,
"totalKeysExamined" : 1,
"totalDocsExamined" : 1,
"executionStages" : {
"stage" : "FETCH",
"filter" : {
"age" : {
"$eq" : 21
}
},
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
"works" : 3,
"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" : {
"username" : 1
},
"indexName" : "username_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"username" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"username" : [
"[\"user997679\", \"user997679\"]"
]
},
"keysExamined" : 1,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0
}
}
},
"serverInfo" : {
"host" : "localhost.localdomain",
"port" : 27017,
"version" : "4.4.0",
"gitVersion" : "563487e100c4215e2dce98d0af2a6a5a2d67c5cf"
},
"ok" : 1
}
wang> 注意：如果索引包含数组的字段，则该索引永远不能覆盖查询（由于数组的方式存储在索引中，这在“索引对象和数组”中有更深入的介绍，即使从返回的字段中排除数组字段，依然不能使用索引。
2.$操作符如何使用索引
(1).低效率的操作符
通常来说取反的效率比较低， $ne 查询可以使用索引，但是要扫描所有的索引，很低效
wang> db.users.find({age:{"$ne":21}}).explain("executionStats")
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "wang.users",
"indexFilterSet" : false,
"parsedQuery" : {
"age" : {
"$not" : {
"$eq" : 21
}
}
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"age" : 1,
"username" : 1
},
"indexName" : "age_1_username_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"age" : [ ],
"username" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"age" : [
"[MinKey, 21.0)",
"(21.0, MaxKey]"
],
"username" : [
"[MinKey, MaxKey]"
]
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 991664,
"executionTimeMillis" : 3833,
"totalKeysExamined" : 991665,
"totalDocsExamined" : 991664,
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 991664,
"executionTimeMillisEstimate" : 971,
"works" : 991666,
"advanced" : 991664,
"needTime" : 1,
"needYield" : 0,
"saveState" : 991,
"restoreState" : 991,
"isEOF" : 1,
"docsExamined" : 991664,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 991664,
"executionTimeMillisEstimate" : 397,
"works" : 991666,
"advanced" : 991664,
"needTime" : 1,
"needYield" : 0,
"saveState" : 991,
"restoreState" : 991,
"isEOF" : 1,
"keyPattern" : {
"age" : 1,
"username" : 1
},
"indexName" : "age_1_username_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"age" : [ ],
"username" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"age" : [
"[MinKey, 21.0)",
"(21.0, MaxKey]"
],
"username" : [
"[MinKey, MaxKey]"
]
},
"keysExamined" : 991665,
"seeks" : 2,
"dupsTested" : 0,
"dupsDropped" : 0
}
}
},
"serverInfo" : {
"host" : "localhost.localdomain",
"port" : 27017,
"version" : "4.4.0",
"gitVersion" : "563487e100c4215e2dce98d0af2a6a5a2d67c5cf"
},
"ok" : 1
}
wang> 这个索引查找了所有小于21和大于21的索引条目，如果索引中的值为21的条目非常多，用这个查询的效率是非常不错的，否则的话，这个查询就不得不检查几乎所有的索引条目。
$not 有时候能使用索引，但是通常它并不知道要如何使用索引，它能够对基本的范围
比如将
{"key" : {"$lt" : 7}}变成
{"key" : {"$gte" : 7}}db.users.find({age:{"$not":{"$lt":21}}}).explain("executionStats")然而，大多数使用 $not 的查询会退化为全表扫描， $nin 就总是进行全表扫描
如果必须要使用这种类型的查询，可以尝试将其过滤为一个很小的结果集，然后再使用此种方式
(2).范围查询
当索引字段为 {"age" : 1, "username" : 1} 时，使用以下查询就是高效的
db.users.find({"age" : 47,"username" : {"$gt" : "user5", "$lt" : "user8"}}).explain("executionStats")当索引字段为 {"username" : 1,"age" : 1} 时，使用以下查询就是低效的
db.users.find({"username" : {"$gt" : "user5", "$lt" : "user8"},"age" : 47}).explain("executionStats")(3).or查询
到目前为止，mongodb在一次查询中只能使用一个索引，如果在 {"x" : 1} 上有一个索引，在 {"y" : 1} 上也有一个索引。使用 {"x" : 123, "y" : 456} 条件进行查询时，mongodb会使用其中的一个索引，而不是2个一起使用，例子如下：
准备测试数据
db.inventory.insert({ "_id" : 1, "item" : "f1", type: "food", quantity: 500 })
db.inventory.insert({ "_id" : 2, "item" : "f2", type: "food", quantity: 100 })
db.inventory.insert({ "_id" : 3, "item" : "p1", type: "paper", quantity: 200 })
db.inventory.insert({ "_id" : 4, "item" : "p2", type: "paper", quantity: 150 })
db.inventory.insert({ "_id" : 5, "item" : "f3", type: "food", quantity: 300 })
db.inventory.insert({ "_id" : 6, "item" : "t1", type: "toys", quantity: 500 })
db.inventory.insert({ "_id" : 7, "item" : "a1", type: "apparel", quantity: 250 })
db.inventory.insert({ "_id" : 8, "item" : "a2", type: "apparel", quantity: 400 })
db.inventory.insert({ "_id" : 9, "item" : "t2", type: "toys", quantity: 50 })
db.inventory.insert({ "_id" : 10, "item" : "f4", type: "food", quantity: 75 })创建索引
db.inventory.createIndex( { quantity: 1 } )
db.inventory.createIndex( { type: 1 } )查询语句如下（此例子中只使用了quantity字段的索引）：
db.inventory.find({quantity: 50,type: "toys"}).explain("executionStats") "executionStats" : {
"executionSuccess" : true,
"nReturned" : 1,
"executionTimeMillis" : 0,
"totalKeysExamined" : 1,
"totalDocsExamined" : 1,
"executionStages" : {
"stage" : "FETCH",
"filter" : {
"type" : {
"$eq" : "toys"
}
},
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
"works" : 3,
"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" : {
"quantity" : 1
},
"indexName" : "quantity_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"quantity" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"quantity" : [
"[50.0, 50.0]"
]
},
"keysExamined" : 1,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0
}
}
}, $or 是个例外， $or 可以对每个子句都使用索引，因为 $or 实际上是执行两次查询然后将结果集合并，例子如下db.inventory.find( { $or: [ { quantity: { $lt: 80 } }, { type: "toys" } ] } ).explain("executionStats") "executionStats" : {
"executionSuccess" : true,
"nReturned" : 3,
"executionTimeMillis" : 0,
"totalKeysExamined" : 4,
"totalDocsExamined" : 3,
"executionStages" : {
"stage" : "SUBPLAN",
"nReturned" : 3,
"executionTimeMillisEstimate" : 0,
"works" : 6,
"advanced" : 3,
"needTime" : 2,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"inputStage" : {
"stage" : "FETCH",
"nReturned" : 3,
"executionTimeMillisEstimate" : 0,
"works" : 6,
"advanced" : 3,
"needTime" : 2,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"docsExamined" : 3,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "OR",
"nReturned" : 3,
"executionTimeMillisEstimate" : 0,
"works" : 6,
"advanced" : 3,
"needTime" : 2,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"dupsTested" : 4,
"dupsDropped" : 1,
"inputStages" : [
{
"stage" : "IXSCAN",
"nReturned" : 2,
"executionTimeMillisEstimate" : 0,
"works" : 3,
"advanced" : 2,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"keyPattern" : {
"quantity" : 1
},
"indexName" : "quantity_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"quantity" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"quantity" : [
"[-inf.0, 80.0)"
]
},
"keysExamined" : 2,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0
},
{
"stage" : "IXSCAN",
"nReturned" : 2,
"executionTimeMillisEstimate" : 0,
"works" : 3,
"advanced" : 2,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"keyPattern" : {
"type" : 1
},
"indexName" : "type_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"type" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"type" : [
"[\"toys\", \"toys\"]"
]
},
"keysExamined" : 2,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0
}
]
}
}
}
}通常来说，执行2次查询z再将结果合并的效率不如单次查询高，因此尽可能的使用 $in 而不是 $or ，如果不得不使用 $or ，mongodb需要每次检查结果集并从中移除重复的文档。
使用 $in 查询时无法控制返回文档的顺序（除非进行排序）。
标签：username,index,false,age,monodb4.4,索引,quantity,stage From： https://blog.51cto.com/u_13753753/6170229