mongodb 如何优化聚合管道的查询速度？

vsdwdz23  于 2023-01-08  发布在  Go
关注(0)|答案(2)|浏览(299)

我有一个包含130万个文档的集合，我的查询脚本如下所示：

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 },
])

我想取当前月份的数据，并按appName字段分组。脚本大约需要5秒。
我尝试将accessTime和appName上的索引添加为compoundIndex：accessTime_1_appName_1。但似乎不起作用。脚本也需要大约5秒。脚本说明如下：

{
    "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" : 4925,
                    "totalKeysExamined" : 1333199,
                    "totalDocsExamined" : 0,
                    "executionStages" : {
                        "stage" : "PROJECTION_COVERED",
                        "nReturned" : 1333199,
                        "executionTimeMillisEstimate" : 158,
                        "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" : 96,
                            "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
                        }
                    }
                }
            },
            "nReturned" : 1333199,
            "executionTimeMillisEstimate" : 1630
        },
        {
            "$group" : {
                "_id" : "$appName",
                "count" : {
                    "$sum" : {
                        "$const" : 1
                    }
                }
            },
            "maxAccumulatorMemoryUsageBytes" : {
                "count" : 74767392
            },
            "totalOutputDataSizeBytes" : 237801844,
            "usedDisk" : false,
            "nReturned" : 1038436,
            "executionTimeMillisEstimate" : 4772
        },
        {
            "$sort" : {
                "sortKey" : {
                    "count" : -1
                },
                "limit" : 5
            },
            "totalDataSizeSortedBytesEstimate" : 11515,
            "usedDisk" : false,
            "nReturned" : 5,
            "executionTimeMillisEstimate" : 4924
        }
    ],
    "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(1673062241, 1),
        "signature" : {
            "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
            "keyId" : 0
        }
    },
    "operationTime" : Timestamp(1673062241, 1)
}

似乎$group将禁用索引？那么，如何优化聚合管道的查询速度？

mongodb

2条答案

按热度按时间
vltsax25

vltsax251#

这最终是一个有点复杂的答案，希望你觉得它有用，我没有浪费最后一个小时：笑：
正如已经提到的，我不确定在不完全改变方法的情况下，您是否可以做“很多”事情来提高性能。
您遇到的最大问题是，您正在查看大量数据，您的查询匹配130万条记录（不确定您的总集合有多大）。您的分组阶段返回超过100万条记录，因此跨$appName的基数很高。然后，您对这些结果进行排序（不能使用索引），以获得前5行。
但是，当您处理如此大量的数据时，如果此查询（或类似情况）是常见事件（例如，您经常希望查看最近n天、n个月等）我建议使用$merge管道阶段的一种完全不同的方法。注意，由于您对$appName的基数很高，因此此处的性能改进不会很大。它最多能将已读文档的数量从130万减少到大约100万。
对于一个给定的最小频率（例如，1小时、1天等），您可以将UserBehaviorOnApp和$merge的输出聚合到一个新的集合中（注意，这里需要进行优化，但为了简洁起见，最好保持原样）：

db.UserBehaviorOnApp.aggregate([
{ $match: { accessTime: { $gt: lastTimeIRanThis } } },
{ $group: {
  _id: {
    frequency: "hour",
    appName: "$appName",
    startTime: { $dateTrunc: { date: "$accessTime", unit: "hour" } }
  },
  count: { $sum: 1 },
}},
{ $merge: {
  into: "UserBehaviorOnAppAggregated",
  on: "_id",
  whenNotMatched: "insert",
  whenMatched: [
    { $set: { count: {$add: ["$count", "$$new.count"] } } }
  ]
}}
])

你可以半定期地运行这个查询--比如每5分钟一次，因为它只查看最后5分钟的数据，它会非常快。需要注意的是不要两次查看相同的记录（比如使用事务），它还假设你的accessTime字段总是增加并且与你的服务器时间同步（尽管有很多方法可以绕过这个假设）。
然后，您将针对UserBehaviorOnAppAggregated集合发出查询。
进一步的优化是，您现在可以对 aggregated 数据进行聚合-例如，如果您查看30天的数据，您不关心1小时的粒度（但如果您查看最后一天的数据，您可能会关心），因此您可以发布以下聚合：

db.UserBehaviorOnAppAggregated.aggregate([
{ $match: { "_id.frequency": "hour", "_id.startTime": { $gt: lastTimeIRanThisRoundedToNearestHour } } },
{ $group: {
  _id: {
    frequency: "day",
    appName: "$_id.appName",
    startTime: { $dateTrunc: { date: "$_id.startTime", unit: "day" } }
  },
  count: { $sum: 1 },
}},
{ $merge: {
  into: "UserBehaviorOnAppAggregated",
  on: "_id",
  whenNotMatched: "insert",
  whenMatched: [
    { $set: { count: {$add: ["$count", "$$new.count"] } } }
  ]
}}
])

请注意，这里您正在阅读要合并到的同一集合！
现在，当您要查看过去30天的数据时，可以按如下方式运行聚合：

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

现在，您无需阅读130万份文档，只需阅读（最多）n * 31，其中n是appName的非重复计数。正如我前面提到的，由于appName的基数很高--单凭这一点不会有很大的改进。但如果您做出某些假设，它确实为您提供了选择。例如，你现在可以说“只包含一个appName，如果它每小时有超过1个视图”。然后使用一个以count结尾的索引，你会得到一个效率大大提高的查询（只有当你有至少5个“热门”应用程序，而且是你真正关心的应用程序时，这才有效）。
当然，这种方法也有一些折衷--当您拥有基数较低的数据时，它的工作效果会明显更好--例如，我们使用这种方法来跟踪有限的（数千）台服务器，其中我们每天对每台服务器进行数百万次访问-然后我们在短时间后丢弃原始数据-这大大减少了我们保留的数据，它还允许我们为不同的时间窗口设置不同的保留期，例如，我们将每分钟指标保留1天，每小时指标保留1周，3小时指标（用于查看超过1周的数据）保留1年。
如果数据的基数很高，您将看到相反的情况-如果您看到每个appName每天有1个视图，那么您将最终在每个视图中存储3条记录（一条是原始记录，一条用于每小时聚合，一条用于每日聚合）。

赞(0）分享  回复(0）举报  2023-01-08
mv1qrgav

mv1qrgav2#

有几种方法可以进一步优化性能：

  • 如果appName字段具有少量不同的值，您可能需要考虑使用**$sortByCount**阶段，而不是$group和$sort阶段。这样做可能会更有效，因为它使用不同的算法来计算计数和对结果进行排序。
> db.UserBehaviorOnApp.aggregate([
>     { $match: {"accessTime" : { "$gte" : ISODate("2022-12-06T00:00:00Z"), "$lt" :
> ISODate("2023-01-05T23:59:59Z")}}},
>     { $sortByCount: "$appName" },
>     { $limit : 5 }, ])
  • 如果在分片集群上运行查询，请确保accessTime字段是分片键的一部分，这将允许查询被路由到适当的分片，并可能提高性能。
赞(0）分享  回复(0）举报  2023-01-08
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com