微信开发中 ACCESS TOKEN 过期失效的解决方案详解
344
2024-01-01
MongoDB中索引怎么用
MongoDB中索引怎么用
小编给大家分享一下MongoDB中索引怎么用，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！
MongoDB数据库里面依然会存在两种索引创建：一种是自动创建，另外一种是手工创建。
范例：重新准备一个新的简单集合。
db.emp.drop();
db.emp.insert({"name":"赵一","sex":"男","age":30,"sal":1000,"loc":"北京"});
db.emp.insert({"name":"钱二","sex":"女","age":22,"sal":5000,"loc":"上海"});
db.emp.insert({"name":"孙三","sex":"男","age":40,"sal":2000,"loc":"深圳"});
db.emp.insert({"name":"李四","sex":"女","age":30,"sal":7000,"loc":"北京"});
db.emp.insert({"name":"周五","sex":"女","age":30,"sal":6400,"loc":"北京"});
db.emp.insert({"name":"吴六","sex":"男","age":30,"sal":2500,"loc":"重庆"});
db.emp.insert({"name":"郑七","sex":"女","age":50,"sal":4700,"loc":"成都"});
db.emp.insert({"name":"王八","sex":"男","age":35,"sal":8000,"loc":"北京"});
此时在emp的集合上并没有去设置任何的索引，可以通过getIndexes()函数来观察在emp集合中的索引。
范例：查询默认状态下emp集合的索引内容
> db.emp.getIndexes();
[
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "hr.emp"
}
]
现在发现会存在有一个“_id”列的索引内容。但是如果要想创建自己的索引，则可以使用如下的语法：
索引创建：db.集合名称.createIndex({列:1})
设置的1表示索引按照升序的方式进行排序，如果使用降序则使用“-1”
范例：创建一个索引，在age字段上设置一个降序索引
> db.emp.createIndex({"age":-1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
此时并没有设置索引的名字，所以名字是自动命名的。命名的规范：“字段名称_索引的排序模式”
> db.emp.getIndexes();
[
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "hr.emp"
},
{
"v" : 2,
"key" : {
"age" : -1
},
"name" : "age_-1",
"ns" : "hr.emp"
}
]
范例：针对于当前的age字段上的索引做一个分析
> db.emp.find({"age":30}).explain("executionStats");
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "hr.emp",
"indexFilterSet" : false,
"parsedQuery" : {
"age" : {
"$eq" : 30
}
},
"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" : [
"[30.0, 30.0]"
]
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 4,
"executionTimeMillis" : 0,
"totalKeysExamined" : 4,
"totalDocsExamined" : 4,
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 4,
"executionTimeMillisEstimate" : 0,
"works" : 5,
"advanced" : 4,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 4,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 4,
"executionTimeMillisEstimate" : 0,
"works" : 5,
"advanced" : 4,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"age" : -1
},
"indexName" : "age_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"age" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"age" : [
"[30.0, 30.0]"
]
},
"keysExamined" : 4,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
},
"serverInfo" : {
"host" : "D2-LZY245",
"port" : 27017,
"version" : "3.4.7",
"gitVersion" : "cf38c1b8a0a8dca4a11737581beafef4fe120bcd"
},
"ok" : 1
}
此时的查询使用了索引的技术，但是下面再来观察一个查询，不使用索引字段
范例：针对于sal字段进行查询
> db.emp.find({"sal":{"$gt":5000}}).explain("executionStats");
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "hr.emp",
"indexFilterSet" : false,
"parsedQuery" : {
"sal" : {
"$gt" : 5000
}
},
"winningPlan" : {
"stage" : "COLLSCAN",
"filter" : {
"sal" : {
"$gt" : 5000
}
},
"direction" : "forward"
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 3,
"executionTimeMillis" : 0,
"totalKeysExamined" : 0,
"totalDocsExamined" : 8,
"executionStages" : {
"stage" : "COLLSCAN",
"filter" : {
"sal" : {
"$gt" : 5000
}
},
"nReturned" : 3,
"executionTimeMillisEstimate" : 0,
"works" : 10,
"advanced" : 3,
"needTime" : 6,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"direction" : "forward",
"docsExamined" : 8
}
},
"serverInfo" : {
"host" : "D2-LZY245",
"port" : 27017,
"version" : "3.4.7",
"gitVersion" : "cf38c1b8a0a8dca4a11737581beafef4fe120bcd"
},
"ok" : 1
}
此时在sal字段上并没有设置索引，所以当前的索引形式就变成了全集合扫描的模式。
但是如果说，现在换一种形式，年龄和薪水一起执行查询：
> db.emp.find({"age":30,"sal":7000}).explain("executionStats")
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "hr.emp",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"age" : {
"$eq" : 30
}
},
{
"sal" : {
"$eq" : 7000
}
}
]
},
"winningPlan" : {
"stage" : "FETCH",
"filter" : {
"sal" : {
"$eq" : 7000
}
},
"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" : [
"[30.0, 30.0]"
]
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 1,
"executionTimeMillis" : 0,
"totalKeysExamined" : 4,
"totalDocsExamined" : 4,
"executionStages" : {
"stage" : "FETCH",
"filter" : {
"sal" : {
"$eq" : 7000
}
},
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
"works" : 5,
"advanced" : 1,
"needTime" : 3,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 4,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 4,
"executionTimeMillisEstimate" : 0,
"works" : 5,
"advanced" : 4,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"age" : -1
},
"indexName" : "age_-1",
"isMultiKey" : false,
"multiKeyPaths" : {
"age" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"age" : [
"[30.0, 30.0]"
]
},
"keysExamined" : 4,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
},
"serverInfo" : {
"host" : "D2-LZY245",
"port" : 27017,
"version" : "3.4.7",
"gitVersion" : "cf38c1b8a0a8dca4a11737581beafef4fe120bcd"
},
"ok" : 1
}
这个时候虽然age字段上面存在索引，由于sal字段上面没有索引，所以依然使用的是全表扫描操作。为了解决此时的问题，可以使用一个复合索引。
> db.emp.createIndex({"age":-1,"sal":-1},{name:"age_-1_sal_-1_index"})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 2,
"numIndexesAfter" : 3,
"ok" : 1
}
范例：默认使用索引
> db.emp.find({"age":30,"sal":7000}).explain("executionStats")
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "hr.emp",
"indexFilterSet" : false,
"parsedQuery" : {
"$and" : [
{
"age" : {
"$eq" : 30
}
},
{
"sal" : {
"$eq" : 7000
}
}
]
},
"winningPlan" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"age" : -1,
"sal" : -1
},
"indexName" : "age_-1_sal_-1_index",
"isMultiKey" : false,
"multiKeyPaths" : {
"age" : [ ],
"sal" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"age" : [
"[30.0, 30.0]"
],
"sal" : [
"[7000.0, 7000.0]"
]
}
}
},
"rejectedPlans" : [
{
"stage" : "FETCH",
"filter" : {
"sal" : {
"$eq" : 7000
}
},
"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" : [
"[30.0, 30.0]"
]
}
}
}
]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 1,
"executionTimeMillis" : 0,
"totalKeysExamined" : 1,
"totalDocsExamined" : 1,
"executionStages" : {
"stage" : "FETCH",
"nReturned" : 1,
"executionTimeMillisEstimate" : 0,
"works" : 3,
"advanced" : 1,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"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,
"invalidates" : 0,
"keyPattern" : {
"age" : -1,
"sal" : -1
},
"indexName" : "age_-1_sal_-1_index",
"isMultiKey" : false,
"multiKeyPaths" : {
"age" : [ ],
"sal" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"age" : [
"[30.0, 30.0]"
],
"sal" : [
"[7000.0, 7000.0]"
]
},
"keysExamined" : 1,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
},
"serverInfo" : {
"host" : "D2-LZY245",
"port" : 27017,
"version" : "3.4.7",
"gitVersion" : "cf38c1b8a0a8dca4a11737581beafef4fe120bcd"
},
"ok" : 1
}
但是如果换到了以下条件：
> db.emp.find({"$or":[{"age":{"$gt":30}},{"sal":{"$gt":5000}}]}).explain("executionStats")
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "hr.emp",
"indexFilterSet" : false,
"parsedQuery" : {
"$or" : [
{
"age" : {
"$gt" : 30
}
},
{
"sal" : {
"$gt" : 5000
}
}
]
},
"winningPlan" : {
"stage" : "SUBPLAN",
"inputStage" : {
"stage" : "COLLSCAN",
"filter" : {
"$or" : [
{
"age" : {
"$gt" : 30
}
},
{
"sal" : {
"$gt" : 5000
}
}
]
},
"direction" : "forward"
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 5,
"executionTimeMillis" : 0,
"totalKeysExamined" : 0,
"totalDocsExamined" : 8,
"executionStages" : {
"stage" : "SUBPLAN",
"nReturned" : 5,
"executionTimeMillisEstimate" : 0,
"works" : 10,
"advanced" : 5,
"needTime" : 4,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"inputStage" : {
"stage" : "COLLSCAN",
"filter" : {
"$or" : [
{
"age" : {
"$gt" : 30
}
},
{
"sal" : {
"$gt" : 5000
}
}
]
},
"nReturned" : 5,
"executionTimeMillisEstimate" : 0,
"works" : 10,
"advanced" : 5,
"needTime" : 4,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"direction" : "forward",
"docsExamined" : 8
}
}
},
"serverInfo" : {
"host" : "D2-LZY245",
"port" : 27017,
"version" : "3.4.7",
"gitVersion" : "cf38c1b8a0a8dca4a11737581beafef4fe120bcd"
},
"ok" : 1
}
现在发现并没有使用索引，所以这个时候看能否强制使用一次索引：hint()函数为强制使用索引操作。
范例：强制使用索引
> db.emp.find({"$or":[{"age":{"$gt":50}},{"sal":{"$gt":3000}}]}).hint("age_-1_sal_-1_index").explain("executionStats")
{
"queryPlanner" : {
"plannerVersion" : 1,
"namespace" : "hr.emp",
"indexFilterSet" : false,
"parsedQuery" : {
"$or" : [
{
"age" : {
"$gt" : 50
}
},
{
"sal" : {
"$gt" : 3000
}
}
]
},
"winningPlan" : {
"stage" : "FETCH",
"filter" : {
"$or" : [
{
"age" : {
"$gt" : 50
}
},
{
"sal" : {
"$gt" : 3000
}
}
]
},
"inputStage" : {
"stage" : "IXSCAN",
"keyPattern" : {
"age" : -1,
"sal" : -1
},
"indexName" : "age_-1_sal_-1_index",
"isMultiKey" : false,
"multiKeyPaths" : {
"age" : [ ],
"sal" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"age" : [
"[MaxKey, MinKey]"
],
"sal" : [
"[MaxKey, MinKey]"
]
}
}
},
"rejectedPlans" : [ ]
},
"executionStats" : {
"executionSuccess" : true,
"nReturned" : 5,
"executionTimeMillis" : 0,
"totalKeysExamined" : 8,
"totalDocsExamined" : 8,
"executionStages" : {
"stage" : "FETCH",
"filter" : {
"$or" : [
{
"age" : {
"$gt" : 50
}
},
{
"sal" : {
"$gt" : 3000
}
}
]
},
"nReturned" : 5,
"executionTimeMillisEstimate" : 0,
"works" : 9,
"advanced" : 5,
"needTime" : 3,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"docsExamined" : 8,
"alreadyHasObj" : 0,
"inputStage" : {
"stage" : "IXSCAN",
"nReturned" : 8,
"executionTimeMillisEstimate" : 0,
"works" : 9,
"advanced" : 8,
"needTime" : 0,
"needYield" : 0,
"saveState" : 0,
"restoreState" : 0,
"isEOF" : 1,
"invalidates" : 0,
"keyPattern" : {
"age" : -1,
"sal" : -1
},
"indexName" : "age_-1_sal_-1_index",
"isMultiKey" : false,
"multiKeyPaths" : {
"age" : [ ],
"sal" : [ ]
},
"isUnique" : false,
"isSparse" : false,
"isPartial" : false,
"indexVersion" : 2,
"direction" : "forward",
"indexBounds" : {
"age" : [
"[MaxKey, MinKey]"
],
"sal" : [
"[MaxKey, MinKey]"
]
},
"keysExamined" : 8,
"seeks" : 1,
"dupsTested" : 0,
"dupsDropped" : 0,
"seenInvalidated" : 0
}
}
},
"serverInfo" : {
"host" : "D2-LZY245",
"port" : 27017,
"version" : "3.4.7",
"gitVersion" : "cf38c1b8a0a8dca4a11737581beafef4fe120bcd"
},
"ok" : 1
}
如果正常来讲，这个代码根本就不可能调用默认的索引执行，如果需要可以使用hint()函数强制MongoDB必须使用索引。
如果在一个集合里面设置了过多的索引，实际上会导致性能下降。那么可以删除索引。
范例：删除一个索引
> db.emp.dropIndex("age_-1_sal_-1_index")
{ "nIndexesWas" : 3, "ok" : 1 }
> db.emp.getIndexes();
[
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "hr.emp"
},
{
"v" : 2,
"key" : {
"age" : -1
},
"name" : "age_-1",
"ns" : "hr.emp"
}
]
范例：删除非"_id"的索引，也就是自定义的索引
> db.emp.dropIndexes();
{
"nIndexesWas" : 2,
"msg" : "non-_id indexes dropped for collection",
"ok" : 1
}
> db.emp.getIndexes();
[
{
"v" : 2,
"key" : {
"_id" : 1
},
"name" : "_id_",
"ns" : "hr.emp"
}
]
以上是“MongoDB中索引怎么用”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注行业资讯频道！
版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。
相关文章
发表评论
最近发表
推荐文章
热评文章
暂时没有评论，来抢沙发吧~