mongodb索引查找索引排序
mongodb索引查找索引排序
提问于 2014-08-31 05:08:11
我想问一下关于mongodb索引的问题。我可以在查找和排序中使用不同的索引吗？举个例子，我有两个索引：
(a:-1)
(b:1,c:1)哪些索引使用了这句话？
({a:[$gt30}},{a:[$lt50}}]}.sort({c:1})回答 3
Stack Overflow用户
发布于 2014-08-31 05:19:38
我可以在查找和排序中使用不同的索引吗？
在深入阅读本文之后，您将在索引相交的文档页面的底部看到：http://docs.mongodb.org/manual/core/index-intersection/#index-intersection-and-sort
当排序()操作需要完全独立于查询谓词的索引时，
索引交集不适用。
所以，即使你创建了一个{c:1}的索引，它也不能独立地用于与{a:1}相交
哪些索引使用了这句话？
在这种情况下，将只使用{a:1}。
Stack Overflow用户
发布于 2019-06-23 04:25:40
在名为Single field Index的单个字段上创建索引。
创建多个单字段索引来提高查询和排序性能不会有多大帮助！您应该改用复合索引。
查看MongoDB上的文档：https://docs.mongodb.com/manual/core/index-compound/
如果您想了解如何为字段建立索引以及如何测量查询的性能。
并在Youtube上查看此教程：https://dplink.app/nxLgvk7lR
Stack Overflow用户
发布于 2019-10-31 22:08:43
假设我有一个persons集合，其中包含如下文档：
{
dob:
{ age : 50} ,
gender : "male" ,
phone : ""
}现在，我创建了如下索引。
1 : db.persons.createIndex({"dob.age" : -1})
2 : db.persons.createIndex({phone : 1 , gender : 1})如果我像您的一样执行以下查询，请立即执行以下查询
db.persons.explain("executionStats").find({$and：{"dob.age“：{$lt : 50} }，{"dob.age”：{$gt : 30 } ) .sort({性别:1})
我将获得以下执行统计数据：
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "college.persons",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"dob.age" : {
"$lt" : 50
}
},
{
"dob.age" : {
"$gt" : 30
}
}
]
},
"queryHash" : "22FEA299",
"planCacheKey" : "5E8F38C1",
"winningPlan" : {
"stage" : "SORT",
"sortPattern" : {
"phone" : 1
},
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"dob.age" : -1
},
"indexName" : "dob.age_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"dob.age" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"dob.age" : [
"(50.0, 30.0)"
]
}
}
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 1734,
"executionTimeMillis" : 10,
"totalKeysExamined" : 1734,
"totalDocsExamined" : 1734,
"executionStages" : {
"stage" : "SORT",
"nReturned" : 1734,
"executionTimeMillisEstimate" : 0,
"works" : 3471,
"advanced" : 1734,
"needTime" : 1736,
"needYield" : 0,
"saveState" : 27,
"restoreState" : 27,
"isEOF" : 1,
"sortPattern" : {
"phone" : 1
},
"memUsage" : 1914799,
"memLimit" : 33554432,
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"nReturned" : 1734,
"executionTimeMillisEstimate" : 0,
"works" : 1736,
"advanced" : 1734,
"needTime" : 1,
"needYield" : 0,
"saveState" : 27,
"restoreState" : 27,
"isEOF" : 1,
"inputStage" : {
"stage" : "FETCH",
"nReturned" : 1734,
"executionTimeMillisEstimate" : 0,
"works" : 1735,
"advanced" : 1734,
"needTime" : 0,
"needYield" : 0,
"saveState" : 27,
"restoreState" : 27,
"isEOF" : 1,
"docsExamined" : 1734,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 1734,
"executionTimeMillisEstimate" : 0,
"works" : 1735,
"advanced" : 1734,
"needTime" : 0,
"needYield" : 0,
"saveState" : 27,
"restoreState" : 27,
"isEOF" : 1,
"keyPattern" : {
"dob.age" : -1
},
"indexName" : "dob.age_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"dob.age" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"dob.age" : [
"(50.0, 30.0)"
]
},
"keysExamined" : 1734,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0
}
}
}
}
},
"serverInfo" : {
"host" : "RGGYSLT-0483",
"port" : 27017,
"version" : "4.2.0",
"gitVersion" : "a4b751dcf51dd249c5865812b390cfd1c0129c30"
},
"ok" : 1
}这意味着首先在单个字段上使用IXScan获取数据，然后在第三个字段上分别排序。
但是，当我将查询更改为对已为其创建索引的字段进行排序时，情况就发生了变化。现在{"dob.age“：-1}索引被拒绝。
在Mongo db中，获胜计划是可以提前获取100个文档的计划，并且Mongo db会为查询缓存该计划。现在，在1000个文档插入、索引重建、服务器重启或新的索引插入后，该缓存将被清除。
因此，使用哪个索引取决于获胜的计划。
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "college.persons",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"dob.age" : {
"$lt" : 50
}
},
{
"dob.age" : {
"$gt" : 30
}
}
]
},
"queryHash" : "DA8248FA",
"planCacheKey" : "E779554F",
"winningPlan" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"dob.age" : {
"$lt" : 50
}
},
{
"dob.age" : {
"$gt" : 30
}
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"gender" : 1,
"phone" : 1
},
"indexName" : "gender_1_phone_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"gender" : [ ],
"phone" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"gender" : [
"[MinKey, MaxKey]"
],
"phone" : [
"[MinKey, MaxKey]"
]
}
}
},
"rejectedPlans" : [
{
"stage" : "SORT",
"sortPattern" : {
"gender" : 1,
"phone" : 1
},
"inputStage" : {
"stage" : "SORT_KEY_GENERATOR",
"inputStage" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"dob.age" : -1
},
"indexName" : "dob.age_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"dob.age" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"dob.age" : [
"(50.0, 30.0)"
]
}
}
}
}
}
]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 1734,
"executionTimeMillis" : 12,
"totalKeysExamined" : 5002,
"totalDocsExamined" : 5002,
"executionStages" : {
"stage" : "FETCH",
"filter" : {
"$and" : [
{
"dob.age" : {
"$lt" : 50
}
},
{
"dob.age" : {
"$gt" : 30
}
}
]
},
"nReturned" : 1734,
"executionTimeMillisEstimate" : 0,
"works" : 5003,
"advanced" : 1734,
"needTime" : 3268,
"needYield" : 0,
"saveState" : 41,
"restoreState" : 41,
"isEOF" : 1,
"docsExamined" : 5002,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 5002,
"executionTimeMillisEstimate" : 0,
"works" : 5003,
"advanced" : 5002,
"needTime" : 0,
"needYield" : 0,
"saveState" : 41,
"restoreState" : 41,
"isEOF" : 1,
"keyPattern" : {
"gender" : 1,
"phone" : 1
},
"indexName" : "gender_1_phone_1",
"isMultiKey" : false,
"multiKeyPaths" : {
"gender" : [ ],
"phone" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"gender" : [
"[MinKey, MaxKey]"
],
"phone" : [
"[MinKey, MaxKey]"
]
},
"keysExamined" : 5002,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0
}
}
},
"serverInfo" : {
"host" : "RGGYSLT-0483",
"port" : 27017,
"version" : "4.2.0",
"gitVersion" : "a4b751dcf51dd249c5865812b390cfd1c0129c30"
},
"ok" : 1
}页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/25586782
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号