mongo中根据数组匹配($elemMatch)时索引的建立

表数据大小

godset:PRIMARY> db.billboards.find().count()
104022

文档结构

{
	"_id" : "603823.SS-2017-01-05-有价格涨跌幅限制的日换手率达到20%",
	"buy" : [
		{
			"buy_amount" : 2367.05,
			"net_amount" : 2367.05,
			"buy_ratio" : 1.91,
			"sales_department" : "中信建投证券股份有限公司北京朝外大街证券营业部",
			"rank_tags" : [ ]
		},
		{
			"buy_amount" : 2203.37,
			"net_amount" : 2203.37,
			"buy_ratio" : 1.78,
			"sales_department" : "国泰君安证券股份有限公司上海江苏路证券营业部",
			"rank_tags" : [ ]
		},
		{
			"buy_amount" : 2110.2,
			"net_amount" : 2110.2,
			"buy_ratio" : 1.7,
			"sales_department" : "机构专用",
			"rank_tags" : [ ]
		},
		{
			"buy_amount" : 1134.72,
			"net_amount" : 1134.72,
			"buy_ratio" : 0.91,
			"sales_department" : "东方证券股份有限公司北海北海大道证券营业部",
			"rank_tags" : [ ]
		},
		{
			"buy_amount" : 916.7,
			"net_amount" : 916.7,
			"buy_ratio" : 0.74,
			"sales_department" : "招商证券股份有限公司北京建国路证券营业部",
			"rank_tags" : [
				"今卖3"
			]
		}
	],
	"buy_ratio_total" : 7.04,
	"px_change_rate" : -1.84,
	"reason" : "有价格涨跌幅限制的日换手率达到20%",
	"date" : "2017-01-05",
	"buy_amount_total" : 8732.04,
	"sell" : [
		{
			"net_amount" : -3472.15,
			"sell_amount" : 3472.15,
			"sell_ratio" : 2.8,
			"sales_department" : "华泰证券股份有限公司浙江分公司",
			"rank_tags" : [ ]
		},
		{
			"net_amount" : -1443.99,
			"sell_amount" : 1443.99,
			"sell_ratio" : 1.16,
			"sales_department" : "国联证券股份有限公司杭州中山北路证券营业部",
			"rank_tags" : [
				"前买4"
			]
		},
		{
			"net_amount" : -1388.89,
			"sell_amount" : 1388.89,
			"sell_ratio" : 1.12,
			"sales_department" : "招商证券股份有限公司北京建国路证券营业部",
			"rank_tags" : [
				"今买5"
			]
		},
		{
			"net_amount" : -1347.92,
			"sell_amount" : 1347.92,
			"sell_ratio" : 1.09,
			"sales_department" : "华泰证券股份有限公司深圳益田路荣超商务中心证券营业部",
			"rank_tags" : [ ]
		},
		{
			"net_amount" : -1256.71,
			"sell_amount" : 1256.71,
			"sell_ratio" : 1.01,
			"sales_department" : "方正证券股份有限公司重庆新南路证券营业部",
			"rank_tags" : [ ]
		}
	],
	"sell_amount_total" : 8909.65,
	"stock_name" : "百合花",
	"stock_code" : "603823",
	"sell_ratio_total" : 7.18,
	"net_amount_total" : -177.61
}

查询语法

db.billboards.find({"sell":{"$elemMatch":{"sales_department":"招商证券股份有限公司北京建国路证券营业部"}}})

未建立索引前语法分析

godset:PRIMARY> db.billboards.find({"sell":{"$elemMatch":{"sales_department":"招商证券股份有限公司北京建国路证券营业部"}}}).explain("executionStats")
{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"namespace" : "god.billboards",
		"indexFilterSet" : false,
		"parsedQuery" : {
			"sell" : {
				"$elemMatch" : {
					"sales_department" : {
						"$eq" : "招商证券股份有限公司北京建国路证券营业部"
					}
				}
			}
		},
		"winningPlan" : {
			"stage" : "COLLSCAN",
			"filter" : {
				"sell" : {
					"$elemMatch" : {
						"sales_department" : {
							"$eq" : "招商证券股份有限公司北京建国路证券营业部"
						}
					}
				}
			},
			"direction" : "forward"
		},
		"rejectedPlans" : [ ]
	},
	"executionStats" : {
		"executionSuccess" : true,
		"nReturned" : 609,
		"executionTimeMillis" : 425,
		"totalKeysExamined" : 0,
		"totalDocsExamined" : 104022,
		"executionStages" : {
			"stage" : "COLLSCAN",
			"filter" : {
				"sell" : {
					"$elemMatch" : {
						"sales_department" : {
							"$eq" : "招商证券股份有限公司北京建国路证券营业部"
						}
					}
				}
			},
			"nReturned" : 609,
			"executionTimeMillisEstimate" : 410,
			"works" : 104024,
			"advanced" : 609,
			"needTime" : 103414,
			"needYield" : 0,
			"saveState" : 812,
			"restoreState" : 812,
			"isEOF" : 1,
			"invalidates" : 0,
			"direction" : "forward",
			"docsExamined" : 104022
		}
	},
	"serverInfo" : {
		"host" : "iZbp1g0z5ggogfbvwtitwjZ",
		"port" : 37017,
		"version" : "3.2.8",
		"gitVersion" : "ed70e33130c977bda0024c125b56d159573dbaf0"
	},
	"ok" : 1
}

建立索引

db.billboards.createIndex({"sell.sales_department":1},{"background":true})

查看索引

godset:PRIMARY> db.billboards.getIndexes()
[
	{
		"v" : 1,
		"key" : {
			"_id" : 1
		},
		"name" : "_id_",
		"ns" : "god.billboards"
	},
	{
		"v" : 1,
		"key" : {
			"sell.sales_department" : 1
		},
		"name" : "sell.sales_department_1",
		"ns" : "god.billboards",
		"background" : true
	}
]

建立索引后语法分析

godset:PRIMARY> db.billboards.find({"sell":{"$elemMatch":{"sales_department":"招商证券股份有限公司北京建国路证券营业部"}}}).explain("executionStats")
{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"namespace" : "god.billboards",
		"indexFilterSet" : false,
		"parsedQuery" : {
			"sell" : {
				"$elemMatch" : {
					"sales_department" : {
						"$eq" : "招商证券股份有限公司北京建国路证券营业部"
					}
				}
			}
		},
		"winningPlan" : {
			"stage" : "FETCH",
			"filter" : {
				"sell" : {
					"$elemMatch" : {
						"sales_department" : {
							"$eq" : "招商证券股份有限公司北京建国路证券营业部"
						}
					}
				}
			},
			"inputStage" : {
				"stage" : "IXSCAN",
				"keyPattern" : {
					"sell.sales_department" : 1
				},
				"indexName" : "sell.sales_department_1",
				"isMultiKey" : true,
				"isUnique" : false,
				"isSparse" : false,
				"isPartial" : false,
				"indexVersion" : 1,
				"direction" : "forward",
				"indexBounds" : {
					"sell.sales_department" : [
						"[\"招商证券股份有限公司北京建国路证券营业部\", \"招商证券股份有限公司北京建国路证券营业部\"]"
					]
				}
			}
		},
		"rejectedPlans" : [ ]
	},
	"executionStats" : {
		"executionSuccess" : true,
		"nReturned" : 609,
		"executionTimeMillis" : 9,
		"totalKeysExamined" : 609,
		"totalDocsExamined" : 609,
		"executionStages" : {
			"stage" : "FETCH",
			"filter" : {
				"sell" : {
					"$elemMatch" : {
						"sales_department" : {
							"$eq" : "招商证券股份有限公司北京建国路证券营业部"
						}
					}
				}
			},
			"nReturned" : 609,
			"executionTimeMillisEstimate" : 10,
			"works" : 610,
			"advanced" : 609,
			"needTime" : 0,
			"needYield" : 0,
			"saveState" : 4,
			"restoreState" : 4,
			"isEOF" : 1,
			"invalidates" : 0,
			"docsExamined" : 609,
			"alreadyHasObj" : 0,
			"inputStage" : {
				"stage" : "IXSCAN",
				"nReturned" : 609,
				"executionTimeMillisEstimate" : 0,
				"works" : 610,
				"advanced" : 609,
				"needTime" : 0,
				"needYield" : 0,
				"saveState" : 4,
				"restoreState" : 4,
				"isEOF" : 1,
				"invalidates" : 0,
				"keyPattern" : {
					"sell.sales_department" : 1
				},
				"indexName" : "sell.sales_department_1",
				"isMultiKey" : true,
				"isUnique" : false,
				"isSparse" : false,
				"isPartial" : false,
				"indexVersion" : 1,
				"direction" : "forward",
				"indexBounds" : {
					"sell.sales_department" : [
						"[\"招商证券股份有限公司北京建国路证券营业部\", \"招商证券股份有限公司北京建国路证券营业部\"]"
					]
				},
				"keysExamined" : 609,
				"dupsTested" : 609,
				"dupsDropped" : 0,
				"seenInvalidated" : 0
			}
		}
	},
	"serverInfo" : {
		"host" : "iZbp1g0z5ggogfbvwtitwjZ",
		"port" : 37017,
		"version" : "3.2.8",
		"gitVersion" : "ed70e33130c977bda0024c125b56d159573dbaf0"
	},
	"ok" : 1
}

explain 方法

explain方法有个可选的参数verbose,是个字符串,他表示的是verbose的模式。一共分为3种模式: 1. queryPlanner模式,这个是默认模式。MongoDB运行查询优化器对当前的查询进行评估并选择一个最佳的查询计划

  1. executionStats模式,mongoDB运行查询优化器对当前的查询进行评估并选择一个最佳的查询计划进行执行,在执行完毕后返回这个最佳执行计划执行完成时的相关统计信息,对于写操作db.collection.explain()返回关于更新和删除操作的信息,但是并不将修改应用到数据库,对于那些被拒绝的执行计划,不返回其统计信息

  2. allPlansExecution模式,该模式是前2种模式的更细化,即会包括上述2种模式的所有信息,即按照最佳的执行计划执行以及列出统计信息,而且还会列出一些候选的执行计划,如果有多个查询计划,executionStats信息包括这些执行计划的部分统计信息

我们的目的是要记录执行find方法的耗时时间,所以用executionStats模式就可以了。返回的结果也是只关注executionStats就可以了 - nReturned:表示该查询条件下返回的文档数量。 - executionTimeMills:表示执行时间,单位毫秒 - totalDocsExamined:表示该集合总共文档数。