mongodb--count（）比find（）慢得多？

Question

我正在使用mongoose来计算匹配某个查询的文档数量。此查询的索引为:{createdat:-1，status:-1，oid:-1}

Mongo版本为3.2，收藏文档数量约为175万。

model.find({
                        createdAt: {'$gte': threeMonths, '$lt': today},
                        status: {'$in': model.STATUS_SET}
                    }).select({_id: 0, status: 1}).count().then((c) => result[alias] = c)

需要2分多钟。但如果我这么做了：

model.find({
                createdAt: {'$gte': threeMonths, '$lt': today},
                status: {'$in': model.STATUS_SET}
            }).select({_id: 0, status: 1}).lean().then((c) => result[alias] = c.length)

然后大约需要2.5秒。

我做错什么了吗？我能做些什么来加快速度吗？

编辑：解释日志。

计数：

"executionStats" : {
		"executionSuccess" : true,
		"nReturned" : 0,
		"executionTimeMillis" : 82671,
		"totalKeysExamined" : 1749689,
		"totalDocsExamined" : 1643722,
		"executionStages" : {
			"stage" : "COUNT",
			"nReturned" : 0,
			"executionTimeMillisEstimate" : 80960,
			"works" : 1750066,
			"advanced" : 0,
			"needTime" : 1749689,
			"needFetch" : 376,
			"saveState" : 14662,
			"restoreState" : 14662,
			"isEOF" : 1,
			"invalidates" : 0,
			"nCounted" : 1643722,
			"nSkipped" : 0,
			"inputStage" : {
				"stage" : "FETCH",
				"nReturned" : 1643722,
				"executionTimeMillisEstimate" : 80890,
				"works" : 1750065,
				"advanced" : 1643722,
				"needTime" : 105967,
				"needFetch" : 376,
				"saveState" : 14662,
				"restoreState" : 14662,
				"isEOF" : 1,
				"invalidates" : 0,
				"docsExamined" : 1643722,
				"alreadyHasObj" : 0,
				"inputStage" : {
					"stage" : "IXSCAN",
					"nReturned" : 1643722,
					"executionTimeMillisEstimate" : 3800,
					"works" : 1749689,
					"advanced" : 1643722,
					"needTime" : 105967,
					"needFetch" : 0,
					"saveState" : 14662,
					"restoreState" : 14662,
					"isEOF" : 1,
					"invalidates" : 0,
					"keyPattern" : {
						"createdAt" : -1,
						"status" : -1,
						"oId" : -1
					},
					"indexName" : "moderatedContent",
					"isMultiKey" : false,
					"direction" : "forward",
					"indexBounds" : {
						"createdAt" : [
							"(new Date(1467195213000), new Date(1459246413000)]"
						],
						"status" : [
							"[\"UNDECIDED\", \"UNDECIDED\"]",
							"[\"APPROVED\", \"APPROVED\"]"
						],
						"oId" : [
							"[MaxKey, MinKey]"
						]
					},
					"keysExamined" : 1749689,
					"dupsTested" : 0,
					"dupsDropped" : 0,
					"seenInvalidated" : 0,
					"matchTested" : 0
				}
			}
		},
		"allPlansExecution" : [ ]
	}

为了找到。

"executionStats" : {
		"executionSuccess" : true,
		"nReturned" : 1643722,
		"executionTimeMillis" : 1216,
		"totalKeysExamined" : 1749689,
		"totalDocsExamined" : 0,
		"executionStages" : {
			"stage" : "PROJECTION",
			"nReturned" : 1643722,
			"executionTimeMillisEstimate" : 1080,
			"works" : 1749690,
			"advanced" : 1643722,
			"needTime" : 105967,
			"needFetch" : 0,
			"saveState" : 13669,
			"restoreState" : 13669,
			"isEOF" : 1,
			"invalidates" : 0,
			"transformBy" : {
				"_id" : 0,
				"status" : 1
			},
			"inputStage" : {
				"stage" : "IXSCAN",
				"nReturned" : 1643722,
				"executionTimeMillisEstimate" : 920,
				"works" : 1749690,
				"advanced" : 1643722,
				"needTime" : 105967,
				"needFetch" : 0,
				"saveState" : 13669,
				"restoreState" : 13669,
				"isEOF" : 1,
				"invalidates" : 0,
				"keyPattern" : {
					"createdAt" : -1,
					"status" : -1,
					"oId" : -1
				},
				"indexName" : "moderatedContent",
				"isMultiKey" : false,
				"direction" : "forward",
				"indexBounds" : {
					"createdAt" : [
						"(new Date(1467195213000), new Date(1459246413000)]"
					],
					"status" : [
						"[\"UNDECIDED\", \"UNDECIDED\"]",
						"[\"APPROVED\", \"APPROVED\"]"
					],
					"oId" : [
						"[MaxKey, MinKey]"
					]
				},
				"keysExamined" : 1749689,
				"dupsTested" : 0,
				"dupsDropped" : 0,
				"seenInvalidated" : 0,
				"matchTested" : 0
			}
		}
	}

"executionStats" : {
		"executionSuccess" : true,
		"nReturned" : 0,
		"executionTimeMillis" : 89191,
		"totalKeysExamined" : 1749689,
		"totalDocsExamined" : 1643722,
		"executionStages" : {
			"stage" : "COUNT",
			"nReturned" : 0,
			"executionTimeMillisEstimate" : 83400,
			"works" : 1751709,
			"advanced" : 0,
			"needTime" : 1749689,
			"needFetch" : 2019,
			"saveState" : 15648,
			"restoreState" : 15648,
			"isEOF" : 1,
			"invalidates" : 0,
			"nCounted" : 1643722,
			"nSkipped" : 0,
			"inputStage" : {
				"stage" : "FETCH",
				"nReturned" : 1643722,
				"executionTimeMillisEstimate" : 83260,
				"works" : 1751708,
				"advanced" : 1643722,
				"needTime" : 105967,
				"needFetch" : 2019,
				"saveState" : 15648,
				"restoreState" : 15648,
				"isEOF" : 1,
				"invalidates" : 0,
				"docsExamined" : 1643722,
				"alreadyHasObj" : 0,
				"inputStage" : {
					"stage" : "IXSCAN",
					"nReturned" : 1643722,
					"executionTimeMillisEstimate" : 8290,
					"works" : 1749689,
					"advanced" : 1643722,
					"needTime" : 105967,
					"needFetch" : 0,
					"saveState" : 15648,
					"restoreState" : 15648,
					"isEOF" : 1,
					"invalidates" : 0,
					"keyPattern" : {
						"createdAt" : -1,
						"status" : -1,
						"oId" : -1
					},
					"indexName" : "moderatedContent",
					"isMultiKey" : false,
					"direction" : "forward",
					"indexBounds" : {
						"createdAt" : [
							"(new Date(1467195213000), new Date(1459246413000)]"
						],
						"status" : [
							"[\"UNDECIDED\", \"UNDECIDED\"]",
							"[\"APPROVED\", \"APPROVED\"]"
						],
						"oId" : [
							"[MaxKey, MinKey]"
						]
					},
					"keysExamined" : 1749689,
					"dupsTested" : 0,
					"dupsDropped" : 0,
					"seenInvalidated" : 0,
					"matchTested" : 0
				}
			}
		}
	}

Answer 1

你答案的关键是

//count
"totalDocsExamined" : 1643722,

VS

//find
"totalDocsExamined" : 0,

find查询完全对索引进行操作，不读取任何单个文档，而count查询实际上读取数据库中的每个文档。

除此之外，在精益查找查询中，只有select()ingid和status，这似乎是投影的(“transformby”...)，因此整个查询成为一个覆盖查询，不需要读取任何文档来为请求服务。