MongoDB百万数据聚合统计缓慢

感谢大佬们抽空能看我的问题，问题如下：

目前库中有 133w 左右的数据。我需要统计本月 appName 出现的次数。我的脚本如下:

db.UserBehaviorOnApp.aggregate([
{ $match: {“accessTime” : { “$gte” : ISODate(“2022-12-06T00:00:00Z”), “$lt” : ISODate(“2023-01-05T23:59:59Z”)}}},
{ $group: { _id: “$appName”, count: { $sum: 1 } } },
{ $sort: {count: -1} },
{ $limit : 5 },
])

目前查询耗费的时间大约为 4.6 ~ 5.04 s。目前的索引为 accessTime_1_appName_1。

explain 结果如下：

{
“explainVersion” : “1”,
“stages” : [
{
“$cursor” : {
“queryPlanner” : {
“namespace” : “user_behavior_log.UserBehaviorOnApp”,
“indexFilterSet” : false,
“parsedQuery” : {
“$and” : [
{
“accessTime” : {
“$lt” : ISODate(“2023-01-06T07:59:59.000+08:00”)
}
},
{
“accessTime” : {
“$gte” : ISODate(“2022-12-06T08:00:00.000+08:00”)
}
}
]
},
“queryHash” : “BDCC37AF”,
“planCacheKey” : “F8798B58”,
“maxIndexedOrSolutionsReached” : false,
“maxIndexedAndSolutionsReached” : false,
“maxScansToExplodeReached” : false,
“winningPlan” : {
“stage” : “PROJECTION_COVERED”,
“transformBy” : {
“appName” : 1,
“_id” : 0
},
“inputStage” : {
“stage” : “IXSCAN”,
“keyPattern” : {
“accessTime” : 1,
“appName” : 1
},
“indexName” : “accessTime_1_appName_1”,
“isMultiKey” : false,
“multiKeyPaths” : {
“accessTime” : [ ],
“appName” : [ ]
},
“isUnique” : false,
“isSparse” : false,
“isPartial” : false,
“indexVersion” : 2,
“direction” : “forward”,
“indexBounds” : {
“accessTime” : [ “[new Date(1670284800000), new Date(1672963199000))” ],
“appName” : [ “[MinKey, MaxKey]” ]
}
}
},
“rejectedPlans” : [
{
“stage” : “PROJECTION_SIMPLE”,
“transformBy” : {
“appName” : 1,
“_id” : 0
},
“inputStage” : {
“stage” : “FETCH”,
“inputStage” : {
“stage” : “IXSCAN”,
“keyPattern” : {
“accessTime” : 1,
“deptIdOfOperator” : 1
},
“indexName” : “accessTime_1_deptIdOfOperator_1”,
“isMultiKey” : false,
“multiKeyPaths” : {
“accessTime” : [ ],
“deptIdOfOperator” : [ ]
},
“isUnique” : false,
“isSparse” : false,
“isPartial” : false,
“indexVersion” : 2,
“direction” : “forward”,
“indexBounds” : {
“accessTime” : [ “[new Date(1670284800000), new Date(1672963199000))” ],
“deptIdOfOperator” : [ “[MinKey, MaxKey]” ]
}
}
}
}
]
},
“executionStats” : {
“executionSuccess” : true,
“nReturned” : 1333199,
“executionTimeMillis” : 4836,
“totalKeysExamined” : 1333199,
“totalDocsExamined” : 0,
“executionStages” : {
“stage” : “PROJECTION_COVERED”,
“nReturned” : 1333199,
“executionTimeMillisEstimate” : 140,
“works” : 1333200,
“advanced” : 1333199,
“needTime” : 0,
“needYield” : 0,
“saveState” : 1380,
“restoreState” : 1380,
“isEOF” : 1,
“transformBy” : {
“appName” : 1,
“_id” : 0
},
“inputStage” : {
“stage” : “IXSCAN”,
“nReturned” : 1333199,
“executionTimeMillisEstimate” : 94,
“works” : 1333200,
“advanced” : 1333199,
“needTime” : 0,
“needYield” : 0,
“saveState” : 1380,
“restoreState” : 1380,
“isEOF” : 1,
“keyPattern” : {
“accessTime” : 1,
“appName” : 1
},
“indexName” : “accessTime_1_appName_1”,
“isMultiKey” : false,
“multiKeyPaths” : {
“accessTime” : [ ],
“appName” : [ ]
},
“isUnique” : false,
“isSparse” : false,
“isPartial” : false,
“indexVersion” : 2,
“direction” : “forward”,
“indexBounds” : {
“accessTime” : [ “[new Date(1670284800000), new Date(1672963199000))” ],
“appName” : [ “[MinKey, MaxKey]” ]
},
“keysExamined” : 1333199,
“seeks” : 1,
“dupsTested” : 0,
“dupsDropped” : 0,
“indexDef” : {
“indexName” : “accessTime_1_appName_1”,
“isMultiKey” : false,
“multiKeyPaths” : {
“accessTime” : [ ],
“appName” : [ ]
},
“keyPattern” : {
“accessTime” : 1,
“appName” : 1
},
“isUnique” : false,
“isSparse” : false,
“isPartial” : false,
“direction” : “forward”
}
}
}
}
},
“nReturned” : 1333199,
“executionTimeMillisEstimate” : 1516
},
{
“$group” : {
“_id” : “$appName”,
“count” : {
“$sum” : {
“$const” : 1
}
}
},
“maxAccumulatorMemoryUsageBytes” : {
“count” : 74767392
},
“totalOutputDataSizeBytes” : 237801844,
“usedDisk” : false,
“nReturned” : 1038436,
“executionTimeMillisEstimate” : 4709
},
{
“$sort” : {
“sortKey” : {
“count” : -1
},
“limit” : 5
},
“totalDataSizeSortedBytesEstimate” : 11515,
“usedDisk” : false,
“nReturned” : 5,
“executionTimeMillisEstimate” : 4835
}
],
“serverInfo” : {
“host” : “02a8a2b6c8dc”,
“port” : 27017,
“version” : “5.0.5”,
“gitVersion” : “d65fd89df3fc039b5c55933c0f71d647a54510ae”
},
“serverParameters” : {
“internalQueryFacetBufferSizeBytes” : 104857600,
“internalQueryFacetMaxOutputDocSizeBytes” : 104857600,
“internalLookupStageIntermediateDocumentMaxSizeBytes” : 104857600,
“internalDocumentSourceGroupMaxMemoryBytes” : 104857600,
“internalQueryMaxBlockingSortMemoryUsageBytes” : 104857600,
“internalQueryProhibitBlockingMergeOnMongoS” : 0,
“internalQueryMaxAddToSetBytes” : 104857600,
“internalDocumentSourceSetWindowFieldsMaxMemoryBytes” : 104857600
},
“command” : {
“aggregate” : “UserBehaviorOnApp”,
“pipeline” : [
{
“$match” : {
“accessTime” : {
“$gte” : ISODate(“2022-12-06T08:00:00.000+08:00”),
“$lt” : ISODate(“2023-01-06T07:59:59.000+08:00”)
}
}
},
{
“$group” : {
“_id” : “$appName”,
“count” : {
“$sum” : 1
}
}
},
{
“$sort” : {
“count” : -1
}
},
{
“$limit” : 5
}
],
“cursor” : {

},
“$db” : “user_behavior_log”
},
“ok” : 1,
“$clusterTime” : {
“clusterTime” : Timestamp(1673059361, 1),
“signature” : {
“hash” : BinData(0,”AAAAAAAAAAAAAAAAAAAAAAAAAAA=”),
“keyId” : 0
}
},
“operationTime” : Timestamp(1673059361, 1)
}

文档结构如下:

{
“_id” : ObjectId(“63b529269d0f784a1fb33187”),
“appId” : “20”,
“appName” : “面积”,
“userIdOfOperator” : “1”,
“userCNNameOfOperator” : “测试用户”,
“accessTime” : ISODate(“2023-01-04T15:22:14.094+08:00”),
“accessType” : “使用”,
“professionOfApp” : “阀门”,
“deptIdOfOperator” : “-2”,
“deptNameOfOperator” : “根部门”,
“userIdOfAuthor” : “1”,
“userCNNameOfAuthor” : “测试用户”,
“deptIdOfAuthor” : “-2”,
“deptNameOfAuthor” : “根部门”,
}

我尝试给不同字段添加索引，但是效果并不明显。我查阅资料说是 group 会使得索引失效，不会使用索引。因此每次插叙都在4.6 ~ 5.04 s 这个区间内。但我觉得是我自己学才疏浅，没有更好的方案，还请各位大佬不吝赐教，感激不尽。