MongoDB数据库中索引和explain的使用教程
|
前言 本文主要给大家介绍了关于MongoDB中索引和explain使用的相关内容，分享出来供大家参考学习，下面话不多说了，来一起看看详细的介绍： mongodb 索引使用 作用
创建索引 db.collection.createIndex(keys,options) keys
options options 创建索引的选项。
查看索引 db.collection.getIndexes()
{
"v" : 1,"key" : {
"_id" : 1
},"name" : "_id_","ns" : "leyue.userdatas"
},{
"v" : 1,"key" : {
"name" : 1 //索引字段
},"name" : "name_1",//索引名称
"ns" : "leyue.userdatas"
}
删除索引
创建/查看/删除 示例 查看数据
db.userdatas.find()
{ "_id" : ObjectId("597f357a09c84cf58880e412"),"name" : "u3","age" : 32 }
{ "_id" : ObjectId("597f357a09c84cf58880e411"),"name" : "u4","age" : 30,"score" : [ 7,4,2,0 ] }
{ "_id" : ObjectId("597fcc0f411f2b2fd30d0b3f"),"age" : 20,10,9,8,7 ],"name" : "lihao" }
{ "_id" : ObjectId("597f357a09c84cf58880e413"),"name" : "u2","age" : 33,"wendang" : { "yw" : 80,"xw" : 90 } }
{ "_id" : ObjectId("5983f5c88eec53fbcd56a7ca"),"date" : ISODate("2017-08-04T04:19:20.693Z") }
{ "_id" : ObjectId("597f357a09c84cf58880e40e"),"name" : "u1","age" : 26,"address" : "中国砀山" }
{ "_id" : ObjectId("597f357a09c84cf58880e40f"),"age" : 37,"score" : [ 10,203,12,43,56,22 ] }
{ "_id" : ObjectId("597f357a09c84cf58880e410"),"name" : "u5","age" : 78,"address" : "china beijing chaoyang" }
给字段name 创建索引
// 创建索引
db.userdatas.createIndex({"name":1})
{
"createdCollectionAutomatically" : false,"numIndexesBefore" : 1,"numIndexesAfter" : 2,"ok" : 1
}
// 查看索引
db.userdatas.getIndexes()
[
{
"v" : 1,"key" : {
"_id" : 1
},"ns" : "leyue.userdatas"
},{
"v" : 1,"key" : {
"name" : 1
},"ns" : "leyue.userdatas"
}
]
给字段name 创建索引并命名为myindex
db.userdatas.createIndex({"name":1})
db.userdatas.createIndex({"name":1},{"name":"myindex"})
db.userdatas.getIndexes()
[
{
"v" : 1,"name" : "myindex","ns" : "leyue.userdatas"
}
]
给字段name 创建索引 创建的过程在后台执行 当mongodb 集合里面的数据过大时 创建索引很耗时，可以在放在后台运行。
db.userdatas.dropIndex("myindex")
db.userdatas.createIndex({"name":1},{"name":"myindex","background":true})
给age 字段创建唯一索引
db.userdatas.createIndex({"age":-1},{"name":"ageIndex","unique":true,"sparse":true})
db.userdatas.getIndexes()
[
{
"v" : 1,"key" : {
"name" : 1
},"ns" : "leyue.userdatas","background" : true
},"unique" : true,"key" : {
"age" : -1
},"name" : "ageIndex","sparse" : true
}
]
// 插入一个已存在的age
db.userdatas.insert({ "name" : "u8","age" : 32})
WriteResult({
"nInserted" : 0,"writeError" : {
"code" : 11000,"errmsg" : "E11000 duplicate key error index: leyue.userdatas.$ageIndex dup key: { : 32.0 }"
}
})
创建复合索引
db.userdatas.createIndex({"name":1,"age":-1})
db.userdatas.getIndexes()
[
{
"v" : 1,"key" : {
"name" : 1,"age" : -1
},"name" : "name_1_age_-1","ns" : "leyue.userdatas"
}
]
所有的字段都存在集合 system.indexes 中
db.system.indexes.find()
{ "v" : 1,"key" : { "_id" : 1 },"ns" : "leyue.userdatas" }
{ "v" : 1,"ns" : "leyue.scores" }
{ "v" : 1,"ns" : "leyue.test" }
{ "v" : 1,"key" : { "user" : 1,"name" : 1 },"ns" : "leyue.mycapped" }
{ "v" : 1,"key" : { "user" : 1 },"name" : "user_1","key" : { "name" : 1 },"ns" : "leyue.userdatas" }
索引总结 1:创建索引时,1表示按升序存储,-1表示按降序存储。 2:可以创建复合索引,如果想用到复合索引,必须在查询条件中包含复合索引中的前N个索引列 3: 如果查询条件中的键值顺序和复合索引中的创建顺序不一致的话, MongoDB可以智能的帮助我们调整该顺序,以便使复合索引可以为查询所用。 4: 可以为内嵌文档创建索引,其规则和普通文档创建索引是一样的。 5: 一次查询中只能使用一个索引,$or特殊,可以在每个分支条件上使用一个索引。 6: $where,$exists不能使用索引,还有一些低效率的操作符,比如:$ne,$not,$nin等。 7: 设计多个字段的索引时,应该尽量将用于精确匹配的字段放在索引的前面。 explain 使用 语法 db.collection.explain().<method(...)> explain() 可以设置参数 ：
示例
for(var i=0;i<100000;i++) {
db.test.insert({"user":"user"+i});
}
没有使用索引
db.test.explain("executionStats").find({"user":"user200000"})
{
"queryPlanner" : {
"plannerVersion" : 1,"namespace" : "leyue.test","indexFilterSet" : false,"parsedQuery" : {
"user" : {
"$eq" : "user200000"
}
},"winningPlan" : {
"stage" : "COLLSCAN","filter" : {
"user" : {
"$eq" : "user200000"
}
},"direction" : "forward"
},"rejectedPlans" : [ ]
},"executionStats" : {
"executionSuccess" : true,"nReturned" : 2,"executionTimeMillis" : 326,"totalKeysExamined" : 0,"totalDocsExamined" : 1006497,"executionStages" : {
"stage" : "COLLSCAN","executionTimeMillisEstimate" : 270,"works" : 1006499,"advanced" : 2,"needTime" : 1006496,"needYield" : 0,"saveState" : 7863,"restoreState" : 7863,"iSEOF" : 1,"invalidates" : 0,"direction" : "forward","docsExamined" : 1006497
}
},"serverInfo" : {
"host" : "lihaodeMacBook-Pro.local","port" : 27017,"version" : "3.2.1","gitVersion" : "a14d55980c2cdc565d4704a7e3ad37e4e535c1b2"
},"ok" : 1
}
totalDocsExamined 全文档扫描 理想状态：
Stage状态分析
对于普通查询，我希望看到stage的组合(查询的时候尽可能用上索引)： Fetch+IDHACK Fetch+ixscan Limit+（Fetch+ixscan） PROJECTION+ixscan SHARDING_FITER+ixscan COUNT_SCAN 不希望看到包含如下的stage： COLLSCAN(全表扫描),SORT(使用sort但是无index),不合理的SKIP,SUBPLA(未用到index的$or),COUNTSCAN(不使用index进行count) 使用索引
db.test.createIndex({"user":1},"background":true})
db.test.explain("executionStats").find({"user":"user200000"})
{
"queryPlanner" : {
"plannerVersion" : 1,"parsedQuery" : {
"user" : {
"$eq" : "user200000"
}
},"winningPlan" : {
"stage" : "FETCH","inputStage" : {
"stage" : "IXSCAN","keyPattern" : {
"user" : 1
},"indexName" : "myindex","isMultiKey" : false,"isUnique" : false,"isSparse" : false,"isPartial" : false,"indexVersion" : 1,"indexBounds" : {
"user" : [
"["user200000","user200000"]"
]
}
}
},"rejectedPlans" : [ ]
},"executionStats" : {
"executionSuccess" : true,"executionTimeMillis" : 0,"totalKeysExamined" : 2,"totalDocsExamined" : 2,"executionStages" : {
"stage" : "FETCH","executionTimeMillisEstimate" : 0,"works" : 3,"needTime" : 0,"saveState" : 0,"restoreState" : 0,"docsExamined" : 2,"alreadyHasObj" : 0,"user200000"]"
]
},"keysExamined" : 2,"dupsTested" : 0,"dupsDropped" : 0,"seenInvalidated" : 0
}
}
},"serverInfo" : {
"host" : "lihaodeMacBook-Pro.local","gitVersion" : "a14d55980c2cdc565d4704a7e3ad37e4e535c1b2"
},"ok" : 1
}
executionTimeMillis: 0 totalKeysExamined: 2 totalDocsExamined:2 nReturned:2 stage:IXSCAN 使用索引和不使用差距很大，合理使用索引，一个集合适合做 4-5 个索引。 总结 以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作能带来一定的帮助，如果有疑问大家可以留言交流，谢谢大家对编程小技巧的支持。 相关文章 http://www.mongoing.com/eshu_explain3 https://docs.mongodb.com/v3.2/reference/explain-results/#queryplanner 您可能感兴趣的文章:
（编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |