MongoDB查询优化--explain，慢日志

引入

与Mysql数据库一样，MongoDB也有自己的查询优化工具，explain和慢日志

explain

shell命令格式

db.collection.explain().<method(...)>

支持的method方法有：

aggregate()
count()
find()
remove()
update()
distinct()
findAndModify()

例如：

db.products.explain().remove( { category: "apparel" }, { justOne: true } )

 

db.collection.explain()接收的参数有："queryPlanner" (Default)、"executionStats"、"allPlansExecution"。

queryPlanner模式：查询计划模式，返回查询计划信息

executionStats模式：执行状态模式，返回查询计划信息、执行状态信息

allPlansExecution模式：返回queryPlanner和executionStats两种模式的信息和

通常executionStats模式比较常用，举例说明一下返回信息具体含义

> db.tb_uhome_house.find({"community_id":5098, "status":1,"submit":0,"resources_type":0}).explain("executionStats")
{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"namespace" : "uhome.tb_uhome_house",
		"indexFilterSet" : false,
		"parsedQuery" : {
			"$and" : [
				{
					"community_id" : {
						"$eq" : 5098
					}
				},
				{
					"resources_type" : {
						"$eq" : 0
					}
				},
				{
					"status" : {
						"$eq" : 1
					}
				},
				{
					"submit" : {
						"$eq" : 0
					}
				}
			]
		},
		"winningPlan" : {
			"stage" : "FETCH",
			"filter" : {
				"$and" : [
					{
						"resources_type" : {
							"$eq" : 0
						}
					},
					{
						"status" : {
							"$eq" : 1
						}
					},
					{
						"submit" : {
							"$eq" : 0
						}
					}
				]
			},
			"inputStage" : {
				"stage" : "IXSCAN",
				"keyPattern" : {
					"community_id" : 1
				},
				"indexName" : "community_id_1",
				"isMultiKey" : false,
				"isUnique" : false,
				"isSparse" : false,
				"isPartial" : false,
				"indexVersion" : 1,
				"direction" : "forward",
				"indexBounds" : {
					"community_id" : [
						"[5098.0, 5098.0]"
					]
				}
			}
		},
		"rejectedPlans" : [
			{
				"stage" : "FETCH",
				"filter" : {
					"$and" : [
						{
							"resources_type" : {
								"$eq" : 0
							}
						},
						{
							"status" : {
								"$eq" : 1
							}
						},
						{
							"submit" : {
								"$eq" : 0
							}
						}
					]
				},
				"inputStage" : {
					"stage" : "IXSCAN",
					"keyPattern" : {
						"community_id" : 1,
						"room_no" : 1
					},
					"indexName" : "community_id_1_room_no_1",
					"isMultiKey" : false,
					"isUnique" : false,
					"isSparse" : false,
					"isPartial" : false,
					"indexVersion" : 1,
					"direction" : "forward",
					"indexBounds" : {
						"community_id" : [
							"[5098.0, 5098.0]"
						],
						"room_no" : [
							"[MinKey, MaxKey]"
						]
					}
				}
			}
		]
	},
	"executionStats" : {
		"executionSuccess" : true,
		"nReturned" : 74345,
		"executionTimeMillis" : 880,
		"totalKeysExamined" : 74346,
		"totalDocsExamined" : 74346,
		"executionStages" : {
			"stage" : "FETCH",
			"filter" : {
				"$and" : [
					{
						"resources_type" : {
							"$eq" : 0
						}
					},
					{
						"status" : {
							"$eq" : 1
						}
					},
					{
						"submit" : {
							"$eq" : 0
						}
					}
				]
			},
			"nReturned" : 74345,
			"executionTimeMillisEstimate" : 851,
			"works" : 74347,
			"advanced" : 74345,
			"needTime" : 1,
			"needYield" : 0,
			"saveState" : 587,
			"restoreState" : 587,
			"isEOF" : 1,
			"invalidates" : 0,
			"docsExamined" : 74346,
			"alreadyHasObj" : 0,
			"inputStage" : {
				"stage" : "IXSCAN",
				"nReturned" : 74346,
				"executionTimeMillisEstimate" : 91,
				"works" : 74347,
				"advanced" : 74346,
				"needTime" : 0,
				"needYield" : 0,
				"saveState" : 587,
				"restoreState" : 587,
				"isEOF" : 1,
				"invalidates" : 0,
				"keyPattern" : {
					"community_id" : 1
				},
				"indexName" : "community_id_1",
				"isMultiKey" : false,
				"isUnique" : false,
				"isSparse" : false,
				"isPartial" : false,
				"indexVersion" : 1,
				"direction" : "forward",
				"indexBounds" : {
					"community_id" : [
						"[5098.0, 5098.0]"
					]
				},
				"keysExamined" : 74346,
				"seeks" : 1,
				"dupsTested" : 0,
				"dupsDropped" : 0,
				"seenInvalidated" : 0
			}
		}
	},
	"serverInfo" : {
		"host" : "bnode",
		"port" : 27018,
		"version" : "3.4.4",
		"gitVersion" : "888390515874a9debd1b6c5d36559ca86b44babd"
	},
	"ok" : 1
}

explain.queryPlanner中的信息：
explain.queryPlanner.winningPlan 描述被查询优化器选择的查询计划信息，其中inputstage描述选择的索引信息
explain.queryPlanner.rejectedPlan 描述被查询优化器拒绝的查询计划信息

explain.executionStats中的信息：
explain.executionStats.nReturned  返回的文档数
explain.executionStats.executionTimeMillis  执行查询的耗时(ms)
explain.executionStats.totalKeysExamined  被扫描索引的数量
explain.executionStats.totalDocsExamined  被扫描文档的数量

慢日志

查看优化日志级别：

db.getProfilingLevel()
db.getProfilingStatus()

打开优化日志：

db.setProfilingLevel(1,200) //慢日志打印耗时超过200ms的查询

查看慢日志：

db.system.profile.find()查看慢查询，可以通过条件过滤想要信息。关键字段：
op：操作类型
ns：被查的集合
commond：命令的内容
docsExamined：扫描文档数
nreturned：返回记录数
millis：耗时时间，单位毫秒
ts：命令执行时间
responseLength：返回内容长度

主要查看查询时间和返回数据长度，如果返回数据量过大，也会影响总体的查询时间。

数据量越大网络传输和客户端解析反序列化的耗时越多，所以客户端查询的时候，尽量通过projection指定返回字段信息

 

参考：

https://docs.mongodb.com/manual/reference/method/db.collection.explain/index.html

https://docs.mongodb.com/manual/reference/explain-results/#queryplanner