博客
关于我
强烈建议你试试无所不能的chatGPT，快点击我
MongoDB 索引相关知识
阅读量：6984 次
发布时间：2019-06-27

本文共 18202 字，大约阅读时间需要 60 分钟。

背景：

      MongoDB和MySQL一样，都会产生慢查询，所以都需要对其进行优化：包括创建索引、重构查询等。现在就说明在MongoDB下的索引相关知识点，可以通过这篇文章了解MongoDB慢查询的一些特点。

执行计划分析：

      因为MongoDB也是BTree索引，所以使用上和MySQL大致一样。通过explain查看一个query的执行计划，来判断如何加索引，explain在3.0版本的时候做了一些改进，现在针对这2个版本进行分析：

3.0之前：

zjy:PRIMARY> db.newtask.find({
   "b":"CYHS1301942"}).explain(){    "cursor" : "BtreeCursor b_1_date_1", #游标类型：BasicCursor(全表扫描)、BtreeCursor(BTree索引扫描)、GeoSearchCursor(地理空间索引扫描)。    "isMultiKey" : false,    "n" : 324,  #返回的结果数,count()。    "nscannedObjects" : 324, #扫描的对象    "nscanned" : 324,        #扫描的索引数    "nscannedObjectsAllPlans" : 324, #代表所有尝试执行的计划所扫描的对象    "nscannedAllPlans" : 324,        #代表所有尝试执行的计划所扫描的索引    "scanAndOrder" : false,          #True：对文档进行排序，false：对索引进行排序    "indexOnly" : false,             #对查询的结果进行排序不需要搜索其他文档，查询和返回字段使用同一索引    "nYields" : 0,                   #为了让写操作执行而让出读锁的次数    "nChunkSkips" : 0,               #忽略文档数    "millis" : 1,                    #执行查询消耗的时间    "indexBounds" : {   #索引扫描中使用的最大/小值。        "b" : [            [                "CYHS1301942",                "CYHS1301942"            ]        ],        "date" : [            [                {                    "$minElement" : 1                },                {                    "$maxElement" : 1                }            ]        ]    },    "server" : "db-mongo1:27017"}

之后：在explain()里有三个参数："queryPlanner", "executionStats", and "allPlansExecution"，默认是：queryPlanner。具体的含义见。

zjy:PRIMARY> db.newtask.find({
   "b":"CYHS1301942"}).explain(){    "queryPlanner" : {        "plannerVersion" : 1,        "namespace" : "cde.newtask",    #集合        "indexFilterSet" : false,        "parsedQuery" : {            "b" : {                "$eq" : "CYHS1301942"            }        },        "winningPlan" : {            "stage" : "FETCH",            "inputStage" : {                "stage" : "IXSCAN",     #索引扫描，COLLSCAN表示全表扫描。                "keyPattern" : {                    "b" : 1,                    "date" : 1                },                "indexName" : "b_1_date_1", #索引名                "isMultiKey" : false,                "direction" : "forward",                "indexBounds" : {                    "b" : [                        "[\"CYHS1301942\", \"CYHS1301942\"]"                    ],                    "date" : [                        "[MinKey, MaxKey]"                    ]                }            }        },        "rejectedPlans" : [ ]    },    "serverInfo" : {        "host" : "mongo1",        "port" : 27017,        "version" : "3.0.4",        "gitVersion" : "0481c958daeb2969800511e7475dc66986fa9ed5"    },    "ok" : 1}

3.0要是查看更详细的执行计划请看其他2个参数：

zjy:PRIMARY> db.newtask.find({
    "b":"CYHS1301942"}).explain("allPlansExecution"){    "queryPlanner" : {        "plannerVersion" : 1,        "namespace" : "cde.newtask",        "indexFilterSet" : false,        "parsedQuery" : {            "b" : {                "$eq" : "CYHS1301942"            }        },        "winningPlan" : {            "stage" : "FETCH",            "inputStage" : {                "stage" : "IXSCAN",                "keyPattern" : {                    "b" : 1,                    "date" : 1                },                "indexName" : "b_1_date_1",                "isMultiKey" : false,                "direction" : "forward",                "indexBounds" : {                    "b" : [                        "[\"CYHS1301942\", \"CYHS1301942\"]"                    ],                    "date" : [                        "[MinKey, MaxKey]"                    ]                }            }        },        "rejectedPlans" : [ ]    },    "executionStats" : {        "executionSuccess" : true,        "nReturned" : 1,        "executionTimeMillis" : 0,        "totalKeysExamined" : 1,        "totalDocsExamined" : 1,        "executionStages" : {            "stage" : "FETCH",            "nReturned" : 1,            "executionTimeMillisEstimate" : 0,            "works" : 2,            "advanced" : 1,            "needTime" : 0,            "needFetch" : 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,                "needFetch" : 0,                "saveState" : 0,                "restoreState" : 0,                "isEOF" : 1,                "invalidates" : 0,                "keyPattern" : {                    "b" : 1,                    "date" : 1                },                "indexName" : "b_1_date_1",                "isMultiKey" : false,                "direction" : "forward",                "indexBounds" : {                    "b" : [                        "[\"CYHS1301942\", \"CYHS1301942\"]"                    ],                    "date" : [                        "[MinKey, MaxKey]"                    ]                },                "keysExamined" : 1,                "dupsTested" : 0,                "dupsDropped" : 0,                "seenInvalidated" : 0,                "matchTested" : 0            }        },        "allPlansExecution" : [ ]    },    "serverInfo" : {        "host" : "mongo1",        "port" : 27017,        "version" : "3.0.4",        "gitVersion" : "0481c958daeb2969800511e7475dc66986fa9ed5"    },    "ok" : 1}
View Code 
zjy:PRIMARY> db.newtask.find({
    "b":"CYHS1301942"}).explain("executionStats"){    "queryPlanner" : {        "plannerVersion" : 1,        "namespace" : "cde.newtask",        "indexFilterSet" : false,        "parsedQuery" : {            "b" : {                "$eq" : "CYHS1301942"            }        },        "winningPlan" : {            "stage" : "FETCH",            "inputStage" : {                "stage" : "IXSCAN",                "keyPattern" : {                    "b" : 1,                    "date" : 1                },                "indexName" : "b_1_date_1",                "isMultiKey" : false,                "direction" : "forward",                "indexBounds" : {                    "b" : [                        "[\"CYHS1301942\", \"CYHS1301942\"]"                    ],                    "date" : [                        "[MinKey, MaxKey]"                    ]                }            }        },        "rejectedPlans" : [ ]    },    "executionStats" : {        "executionSuccess" : true,        "nReturned" : 1,        "executionTimeMillis" : 0,        "totalKeysExamined" : 1,        "totalDocsExamined" : 1,        "executionStages" : {            "stage" : "FETCH",            "nReturned" : 1,            "executionTimeMillisEstimate" : 0,            "works" : 2,            "advanced" : 1,            "needTime" : 0,            "needFetch" : 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,                "needFetch" : 0,                "saveState" : 0,                "restoreState" : 0,                "isEOF" : 1,                "invalidates" : 0,                "keyPattern" : {                    "b" : 1,                    "date" : 1                },                "indexName" : "b_1_date_1",                "isMultiKey" : false,                "direction" : "forward",                "indexBounds" : {                    "b" : [                        "[\"CYHS1301942\", \"CYHS1301942\"]"                    ],                    "date" : [                        "[MinKey, MaxKey]"                    ]                },                "keysExamined" : 1,                "dupsTested" : 0,                "dupsDropped" : 0,                "seenInvalidated" : 0,                "matchTested" : 0            }        }    },    "serverInfo" : {        "host" : "mongo1",        "port" : 27017,        "version" : "3.0.4",        "gitVersion" : "0481c958daeb2969800511e7475dc66986fa9ed5"    },    "ok" : 1}
View Code

上面介绍了如何查看执行计划，那么下面介绍下如何管理索引。

，具体请看[权威指南第5章]

1)查看/显示集合的索引：db.collectionName.getIndexes()或则db.system.indexes.find()

zjy:PRIMARY> db.data.getIndexes()[    {        "v" : 1,        "key" : {            "_id" : 1        },        "name" : "_id_",       #索引名        "ns" : "survey.data"   #集合名    },    {        "v" : 1,        "unique" : true,       #唯一索引        "key" : {            "sid" : 1,            "user" : 1        },        "name" : "sid_1_user_1",        "ns" : "survey.data"    },    {        "v" : 1,        "key" : {            "sid" : 1,            "cdate" : -1        },        "name" : "sid_1_cdate_-1",        "ns" : "survey.data"    },    {        "v" : 1,        "key" : {            "sid" : 1,            "created" : -1        },        "name" : "sid_1_created_-1",        "ns" : "survey.data"    },    {        "v" : 1,        "key" : {            "sid" : 1,            "user" : 1,            "modified" : 1        },        "name" : "sid_1_user_1_modified_1",        "ns" : "survey.data"    }]
zjy:PRIMARY> db.system.indexes.find({
   "ns":"survey.data"}){ "v" : 1, "key" : { "_id" : 1 }, "name" : "_id_", "ns" : "survey.data" }{ "v" : 1, "unique" : true, "key" : { "sid" : 1, "user" : 1 }, "name" : "sid_1_user_1", "ns" : "survey.data" }{ "v" : 1, "key" : { "sid" : 1, "cdate" : -1 }, "name" : "sid_1_cdate_-1", "ns" : "survey.data" }{ "v" : 1, "key" : { "sid" : 1, "created" : -1 }, "name" : "sid_1_created_-1", "ns" : "survey.data" }{ "v" : 1, "key" : { "sid" : 1, "user" : 1, "modified" : 1 }, "name" : "sid_1_user_1_modified_1", "ns" : "survey.data" }

2）创建索引：db.collections.ensureIndex({...})

普通索引

zjy:PRIMARY> db.comments.ensureIndex({
   "name":1})  #name字段上创建索引，升序。倒序为-1。{    "createdCollectionAutomatically" : false,    "numIndexesBefore" : 2,    "numIndexesAfter" : 3,    "ok" : 1}zjy:PRIMARY> db.comments.ensureIndex({
   "account.name":1}) #内嵌文档上创建索引。{    "createdCollectionAutomatically" : false,    "numIndexesBefore" : 3,    "numIndexesAfter" : 4,    "ok" : 1}zjy:PRIMARY> db.comments.ensureIndex({
   "age":1},{
   "name":"idx_name"}) #指定索引名称{    "createdCollectionAutomatically" : false,    "numIndexesBefore" : 4,    "numIndexesAfter" : 5,    "ok" : 1}zjy:PRIMARY> db.comments.ensureIndex({
   "name":1,"age":1},{
   "name":"idx_name_age","background":true}) #后台创建复合索引{    "createdCollectionAutomatically" : false,    "numIndexesBefore" : 5,    "numIndexesAfter" : 6,    "ok" : 1}zjy:PRIMARY> db.comments.ensureIndex({
   "name":1,"age":1},{
   "name":"uk_name_age","background":true,"unique":true}) #后台创建唯一索引{    "createdCollectionAutomatically" : false,    "numIndexesBefore" : 1,    "numIndexesAfter" : 2,    "ok" : 1}zjy:PRIMARY> db.comments.ensureIndex({
   "name":1,"age":1},{
   "unique":true,"dropDups":true,"name":"uk_name_age"})   #删除重复数据创建唯一索引，dropDups在3.0里废弃。{    "createdCollectionAutomatically" : false,    "numIndexesBefore" : 1,    "numIndexesAfter" : 2,    "ok" : 1}

：hashed

zjy:PRIMARY> db.abc.ensureIndex({
   "a":"hashed"}){    "createdCollectionAutomatically" : false,    "numIndexesBefore" : 1,    "numIndexesAfter" : 2,    "ok" : 1}zjy:PRIMARY> db.abc.getIndexes()[    {        "v" : 1,        "key" : {            "_id" : 1        },        "name" : "_id_",        "ns" : "test.abc"    },    {        "v" : 1,        "key" : {            "a" : "hashed"        },        "name" : "a_hashed",        "ns" : "test.abc"    }]

这里还有2个比较特殊的索引：sparse）和（expireAfterSeconds）

TTL索引是一种特定的数据块，请求赋予时间范围的方式，它指定一个时间点，超过该时间点数据变成无效。

zjy:PRIMARY> db.comments.find(){ "_id" : ObjectId("55ae6b99313fd7b879b5296c"), "name" : "zhoujy", "age" : 22, "ts" : ISODate("2015-07-21T15:56:09.651Z") }{ "_id" : ObjectId("55ae6b9a313fd7b879b5296d"), "name" : "zhoujy", "age" : 22, "ts" : ISODate("2015-07-21T15:56:10.739Z") }{ "_id" : ObjectId("55ae6b9b313fd7b879b5296e"), "name" : "zhoujy", "age" : 22, "ts" : ISODate("2015-07-21T15:56:11.555Z") }{ "_id" : ObjectId("55ae6b9c313fd7b879b5296f"), "name" : "zhoujy", "age" : 22, "ts" : ISODate("2015-07-21T15:56:12.267Z") }{ "_id" : ObjectId("55ae6b9c313fd7b879b52970"), "name" : "zhoujy", "age" : 22, "ts" : ISODate("2015-07-21T15:56:12.899Z") }zjy:PRIMARY> db.comments.ensureIndex({
   "ts":1},{
   expireAfterSeconds:60})  #创建TTL索引，过期时间60秒，即60秒时间生成的数据会被删除。{    "createdCollectionAutomatically" : false,    "numIndexesBefore" : 1,    "numIndexesAfter" : 2,    "ok" : 1}zjy:PRIMARY> db.comments.find(){ "_id" : ObjectId("55ae6b99313fd7b879b5296c"), "name" : "zhoujy", "age" : 22, "ts" : ISODate("2015-07-21T15:56:09.651Z") }{ "_id" : ObjectId("55ae6b9a313fd7b879b5296d"), "name" : "zhoujy", "age" : 22, "ts" : ISODate("2015-07-21T15:56:10.739Z") }{ "_id" : ObjectId("55ae6b9b313fd7b879b5296e"), "name" : "zhoujy", "age" : 22, "ts" : ISODate("2015-07-21T15:56:11.555Z") }{ "_id" : ObjectId("55ae6b9c313fd7b879b5296f"), "name" : "zhoujy", "age" : 22, "ts" : ISODate("2015-07-21T15:56:12.267Z") }{ "_id" : ObjectId("55ae6b9c313fd7b879b52970"), "name" : "zhoujy", "age" : 22, "ts" : ISODate("2015-07-21T15:56:12.899Z") }zjy:PRIMARY> db.comments.getIndexes()[    {        "v" : 1,        "key" : {            "_id" : 1        },        "name" : "_id_",        "ns" : "test.comments"    },    {        "v" : 1,        "key" : {            "ts" : 1        },        "name" : "ts_1",        "ns" : "test.comments",        "expireAfterSeconds" : 60    }]zjy:PRIMARY> db.comments.find() #60秒之后查看，数据已经没有

最后有一类索引是text index ：更多的信息见 [MongoDB大数据处理权威指南第八章]和

测试数据：

db.comments.insert({
    "name":"abc","mem":"You can create a text index on the field or fields whose value is a string or an array of string elements","ts":new Date()})db.comments.insert({
     "name":"def","mem":"When creating a text index on multiple fields, you can specify the individual fields or you can use wildcard specifier ($**)","ts":new Date()})db.comments.insert({
     "name":"ghi","mem":"This text index catalogs all string data in the subject field and the content field, where the field value is either a string or an array of string elements.","ts":new Date()})db.comments.insert({
     "name":"jkl","mem":"To allow for text search on all fields with string content, use the wildcard specifier ($**) to index all fields that contain string content.","ts":new Date()})db.comments.insert({
     "name":"mno","mem":"The following example indexes any string value in the data of every field of every document in collection and names the index TextIndex:","ts":new Date()})
View Code

:

> db.comments.ensureIndex({
   "mem":"text"})   #创建text索引{    "createdCollectionAutomatically" : false,    "numIndexesBefore" : 1,    "numIndexesAfter" : 2,    "ok" : 1}

使用：

> db.comments.find({$text:{$search:"specifier"}}).pretty(){    "_id" : ObjectId("55aee886a782f35b366926ef"),    "name" : "jkl",    "mem" : "To allow for text search on all fields with string content, use the wildcard specifier ($**) to index all fields that contain string content.",    "ts" : ISODate("2015-07-22T00:49:10.350Z")}{    "_id" : ObjectId("55aee886a782f35b366926ed"),    "name" : "def",    "mem" : "When creating a text index on multiple fields, you can specify the individual fields or you can use wildcard specifier ($**)",    "ts" : ISODate("2015-07-22T00:49:10.346Z")}> db.comments.runCommand("text",{search:"specifier"})  #3.0之前可以使用，之后无效。{    "results" : [        {            "score" : 0.8653846153846153,            "obj" : {                "_id" : ObjectId("55aee886a782f35b366926ed"),                "name" : "def",                "mem" : "When creating a text index on multiple fields, you can specify the individual fields or you can use wildcard specifier ($**)",                "ts" : ISODate("2015-07-22T00:49:10.346Z")            }        },        {            "score" : 0.5357142857142857,            "obj" : {                "_id" : ObjectId("55aee886a782f35b366926ef"),                "name" : "jkl",                "mem" : "To allow for text search on all fields with string content, use the wildcard specifier ($**) to index all fields that contain string content.",                "ts" : ISODate("2015-07-22T00:49:10.350Z")            }        }    ],    "stats" : {        "nscanned" : NumberLong(2),        "nscannedObjects" : NumberLong(2),        "n" : 2,        "timeMicros" : 173    },    "ok" : 1}

上面大致介绍了各类索引的介绍和使用，具体的信息和注意事项可以找里查看，特别是要注意text和ttl索引的使用。

3）删除索引：dropIndex

zjy:PRIMARY> db.abc.getIndexes()    #查看索引[    {        "v" : 1,        "key" : {            "_id" : 1        },        "name" : "_id_",        "ns" : "test.abc"    },    {        "v" : 1,        "key" : {               #索引字段            "a" : "hashed"        },        "name" : "a_hashed",    #索引名        "ns" : "test.abc"    },    {        "v" : 1,        "key" : {            "b" : 1        },        "name" : "b_1",        "ns" : "test.abc"    },    {        "v" : 1,        "key" : {            "c" : 1        },        "name" : "idx_c",        "ns" : "test.abc"    }]zjy:PRIMARY> db.abc.dropIndex({
   "a" : "hashed"})  #删除索引，指定"key"{ "nIndexesWas" : 4, "ok" : 1 }zjy:PRIMARY> db.abc.dropIndex({
   "b" : 1})         #删除索引，指定"key"{ "nIndexesWas" : 3, "ok" : 1 }zjy:PRIMARY> db.abc.dropIndex("idx_c")           #删除索引，指定"name"{ "nIndexesWas" : 2, "ok" : 1 }zjy:PRIMARY> db.abc.getIndexes()[    {        "v" : 1,        "key" : {            "_id" : 1        },        "name" : "_id_",        "ns" : "test.abc"    }]zjy:PRIMARY> db.abc.dropIndex("*")              #删除索引，删除集合的全部索引{    "nIndexesWas" : 4,    "msg" : "non-_id indexes dropped for collection",    "ok" : 1}

4）重建索引：索引出现损坏需要重建。reindex

zjy:PRIMARY> db.abc.reIndex()   #执行{    "nIndexesWas" : 1,    "nIndexes" : 1,    "indexes" : [        {            "key" : {                "_id" : 1            },            "name" : "_id_",            "ns" : "test.abc"        }    ],    "ok" : 1}

5）强制使用指定索引。hint

db.abc.find({
   "c":1,"b":2}).hint("b_1")  #hint里面是"索引字段"或则"索引名"

 

总结：

      索引可以加快检索、排序等操作的效率，但是对于增删改的操作却有一定的开销，所以不要一味的加索引，在必要的字段上加合适的索引才是需要的。更多的信息请参考。

 

转载地址：http://blvpl.baihongyu.com/

你可能感兴趣的文章
研究人员发现利用Excel宏可发起跳板攻击
查看>>
绿盟科技发布OpenSSL高危漏洞技术分析与防护方案 G20成员国美国、中国、德国受影响较大...
查看>>
《VMware Virtual SAN权威指南》一2.2.4　容量层设备
查看>>
物联网发展年报显示 2016年智能家居市场快速增长
查看>>
如何在React中做到jQuery-free
查看>>
4G+宽带高歌猛进：移动双线虐杀联通
查看>>
带你了解超大规模数据中心究竟有何不同？
查看>>
用Python实现每秒处理120万次HTTP请求
查看>>
Android单元测试 - 几个重要问题
查看>>
DNS服务器不能响应的四大解决办法
查看>>
美国税局再遭攻击：原是偷来的社会安全号码作祟
查看>>
如何在Kali Linux中安装Google Chrome浏览器
查看>>
勒索软件防不胜防？ 要先从了解它开始
查看>>
大数据精准医疗解读遗传密码 未来医疗健康的变革
查看>>
神经网络基础：七种网络单元，四种层连接方式
查看>>
2014末，Surface Pro 3叫好不叫座只是价格问题？
查看>>
Arimo利用Alluxio的内存能力提升深度学习模型的结果效率(Time-to-Result)
查看>>
代号“沙尘暴”:黑客剑指日本关键基础设施
查看>>
光纤光缆市场需求高于预期 我国将迎来流量经济
查看>>
晶科能源与森源电气签订300MW光伏组件供货协议
查看>>
喝酒易醉，品茶养心，人生如梦，品茶悟道，何以解忧？唯有杜康！-- 愿君每日到此一游！
当前时间: 2025-02-05 04:56:44   当前IP: 18.191.255.180   联系邮箱:javaeecc@qq.com     Copyright © 2020 - 2022 baihongyu.com 京ICP备2021015314号-2
强烈建议你试试无所不能的CHAT-GPT，快点击我
强烈建议你试试无所不能的CHAT-GPT，快点击我