javascript - 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
			}
		}
	}

对于游标中第一个带有 .explain() 的帖子:

"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,

对比

//find
"totalDocsExamined" : 0,

查找查询完全在索引上运行，不读取任何单个文档，而计数查询实际上从数据库中读取每个文档。

原因是，您的查找查询使用了 lean() 选项。据 Mongoose doc :

Documents returned from queries with the lean option enabled are plain javascript objects, not MongooseDocuments. They have no save method, getters/setters or other Mongoose magic applied.

最重要的是，在您的精益查找查询中，您只是 select()ing id 和 status，这似乎是预计的("transformBy"...) 这样整个查询就变成了一个 covered query 并且无需阅读任何文档即可满足请求。