时间：2021-07-01 10:21:17 帮助过：2人阅读
MongoDB和MySQL一样，都会产生慢查询，所以都需要对其进行优化：包括创建索引、重构查询等。现在就说明在MongoDB下的索引相关知识点，可以通过这篇文章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" }
3.0之后：在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,