MongoDB超大规模集合聚合查询超时问题排查与优化咨询

问题背景 #

有一个包含约2亿条文档的MongoDB集合，单条文档体积较小，但执行聚合查询时频繁超时，需定位问题根源并优化，核心需求是获取每个(global_campaign_id, device_id, partner_id)分组的最新文档。

当前聚合管道 #

[
  {
    $match: {
      global_campaign_id: {$in: [3151 ,3207 ,3208 ,3209 ,3210 , ... (共30个ID)]}
    }
  },
  {
    $group: {
      _id: {
        global_campaign_id: "$global_campaign_id", 
        device_id: "$device_id", 
        partner_id: "$partner_id"
      },
      date_last: {
        $max: "$date_created"
      },
      partner_id: {$first: "$partner_id"},
      campaign_id: {$first: "$global_campaign_id"},
      device_id: {$first: "$device_id"}
    }
  }
]

已创建的索引 #

// 单字段索引
{ _id: 1 }
{ date_created: 1 }
{ global_campaign_id: 1 }

// 复合索引
{ global_campaign_id: 1, date_created: 1 }
{ global_campaign_id: 1, device_id: 1, partner_id: 1, date_created: 1 }

Explain执行结果 #

{
        "stages" : [
                {
                        "$cursor" : {
                                "queryPlanner" : {
                                        "plannerVersion" : 1,
                                        "namespace" : "lurity.impressions",
                                        "indexFilterSet" : false,
                                        "parsedQuery" : {
                                                "global_campaign_id" : {
                                                        "$in" : [
                                                                3151,
                                                                3207,
                                                                3208,
                                                                3209,
                                                                3210
                                                        ]
                                                }
                                        },
                                        "queryHash" : "901B446C",
                                        "planCacheKey" : "06CC82FF",
                                        "winningPlan" : {
                                                "stage" : "PROJECTION_COVERED",
                                                "transformBy" : {
                                                        "date_created" : 1,
                                                        "device_id" : 1,
                                                        "global_campaign_id" : 1,
                                                        "partner_id" : 1,
                                                        "_id" : 0
                                                },
                                                "inputStage" : {
                                                        "stage" : "IXSCAN",
                                                        "keyPattern" : {
                                                                "global_campaign_id" : 1,
                                                                "device_id" : 1,
                                                                "partner_id" : 1,
                                                                "date_created" : 1
                                                        },
                                                        "indexName" : "global_campaign_id_1_device_id_1_partner_id_1_date_created_1",
                                                        "isMultiKey" : false,
                                                        "multiKeyPaths" : {
                                                                "global_campaign_id" : [ ],
                                                                "device_id" : [ ],
                                                                "partner_id" : [ ],
                                                                "date_created" : [ ]
                                                        },
                                                        "isUnique" : false,
                                                        "isSparse" : false,
                                                        "isPartial" : false,
                                                        "indexVersion" : 2,
                                                        "direction" : "forward",
                                                        "indexBounds" : {
                                                                "global_campaign_id" : [
                                                                        "[3151.0, 3151.0]",
                                                                        "[3207.0, 3207.0]",
                                                                        "[3208.0, 3208.0]",
                                                                        "[3209.0, 3209.0]",
                                                                        "[3210.0, 3210.0]"
                                                                ],
                                                                "device_id" : [
                                                                        "[MinKey, MaxKey]"
                                                                ],
                                                                "partner_id" : [
                                                                        "[MinKey, MaxKey]"
                                                                ],
                                                                "date_created" : [
                                                                        "[MinKey, MaxKey]"
                                                                ]
                                                        }
                                                }
                                        },
                                        "rejectedPlans" : [
                                                {
                                                        "stage" : "PROJECTION_SIMPLE",
                                                        "transformBy" : {
                                                                "date_created" : 1,
                                                                "device_id" : 1,
                                                                "global_campaign_id" : 1,
                                                                "partner_id" : 1,
                                                                "_id" : 0
                                                        },
                                                        "inputStage" : {
                                                                "stage" : "FETCH",
                                                                "inputStage" : {
                                                                        "stage" : "IXSCAN",
                                                                        "keyPattern" : {
                                                                                "global_campaign_id" : 1
                                                                        },
                                                                        "indexName" : "global_campaign_id_1",
                                                                        "isMultiKey" : false,
                                                                        "multiKeyPaths" : {
                                                                                "global_campaign_id" : [ ]
                                                                        },
                                                                        "isUnique" : false,
                                                                        "isSparse" : false,
                                                                        "isPartial" : false,
                                                                        "indexVersion" : 2,
                                                                        "direction" : "forward",
                                                                        "indexBounds" : {
                                                                                "global_campaign_id" : [
                                                                                        "[3151.0, 3151.0]",
                                                                                        "[3207.0, 3207.0]",
                                                                                        "[3208.0, 3208.0]",
                                                                                        "[3209.0, 3209.0]",
                                                                                        "[3210.0, 3210.0]"
                                                                                ]
                                                                        }
                                                                }
                                                        }
                                                },
                                                {
                                                        "stage" : "PROJECTION_SIMPLE",
                                                        "transformBy" : {
                                                                "date_created" : 1,
                                                                "device_id" : 1,
                                                                "global_campaign_id" : 1,
                                                                "partner_id" : 1,
                                                                "_id" : 0
                                                        },
                                                        "inputStage" : {
                                                                "stage" : "FETCH",
                                                                "inputStage" : {
                                                                        "stage" : "IXSCAN",
                                                                        "keyPattern" : {
                                                                                "global_campaign_id" : 1,
                                                                                "date_created" : 1
                                                                        },
                                                                        "indexName" : "global_campaign_id_1_date_created_1",
                                                                        "isMultiKey" : false,
                                                                        "multiKeyPaths" : {
                                                                                "global_campaign_id" : [ ],
                                                                                "date_created" : [ ]
                                                                        },
                                                                        "isUnique" : false,
                                                                        "isSparse" : false,
                                                                        "isPartial" : false,
                                                                        "indexVersion" : 2,
                                                                        "direction" : "forward",
                                                                        "indexBounds" : {
                                                                                "global_campaign_id" : [
                                                                                        "[3151.0, 3151.0]",
                                                                                        "[3207.0, 3207.0]",
                                                                                        "[3208.0, 3208.0]",
                                                                                        "[3209.0, 3209.0]",
                                                                                        "[3210.0, 3210.0]"
                                                                                ],
                                                                                "date_created" : [
                                                                                        "[MinKey, MaxKey]"
                                                                                ]
                                                                        }
                                                                }
                                                        }
                                                }
                                        ]
                                },
                                "executionStats" : {
                                        "executionSuccess" : true,
                                        "nReturned" : 304244,
                                        "executionTimeMillis" : 1363,
                                        "totalKeysExamined" : 304246,
                                        "totalDocsExamined" : 0,
                                        "executionStages" : {
                                                "stage" : "PROJECTION_COVERED",
                                                "nReturned" : 304244,
                                                "executionTimeMillisEstimate" : 112,
                                                "works" : 304246,
                                                "advanced" : 304244,
                                                "needTime" : 1,
                                                "needYield" : 0,
                                                "saveState" : 322,
                                                "restoreState" : 322,
                                                "isEOF" : 1,
                                                "transformBy" : {
                                                        "date_created" : 1,
                                                        "device_id" : 1,
                                                        "global_campaign_id" : 1,
                                                        "partner_id" : 1,
                                                        "_id" : 0
                                                },
                                                "inputStage" : {
                                                        "stage" : "IXSCAN",
                                                        "nReturned" : 304244,
                                                        "executionTimeMillisEstimate" : 73,
                                                        "works" : 304246,
                                                        "advanced" : 304244,
                                                        "needTime" : 1,
                                                        "needYield" : 0,
                                                        "saveState" : 322,
                                                        "restoreState" : 322,
                                                        "isEOF" : 1,
                                                        "keyPattern" : {
                                                                "global_campaign_id" : 1,
                                                                "device_id" : 1,
                                                                "partner_id" : 1,
                                                                "date_created" : 1
                                                        },
                                                        "indexName" : "global_campaign_id_1_device_id_1_partner_id_1_date_created_1",
                                                        "isMultiKey" : false,
                                                        "multiKeyPaths" : {
                                                                "global_campaign_id" : [ ],
                                                                "device_id" : [ ],
                                                                "partner_id" : [ ],
                                                                "date_created" : [ ]
                                                        },
                                                        "isUnique" : false,
                                                        "isSparse" : false,
                                                        "isPartial" : false,
                                                        "indexVersion" : 2,
                                                        "direction" : "forward",
                                                        "indexBounds" : {
                                                                "global_campaign_id" : [
                                                                        "[3151.0, 3151.0]",
                                                                        "[3207.0, 3207.0]",
                                                                        "[3208.0, 3208.0]",
                                                                        "[3209.0, 3209.0]",
                                                                        "[3210.0, 3210.0]"
                                                                ],
                                                                "device_id" : [
                                                                        "[MinKey, MaxKey]"
                                                                ],
                                                                "partner_id" : [
                                                                        "[MinKey, MaxKey]"
                                                                ],
                                                                "date_created" : [
                                                                        "[MinKey, MaxKey]"
                                                                ]
                                                        },
                                                        "keysExamined" : 304246,
                                                        "seeks" : 2,
                                                        "dupsTested" : 0,
                                                        "dupsDropped" : 0
                                                }
                                        }
                                }
                        },
                        "nReturned" : NumberLong(304244),
                        "executionTimeMillisEstimate" : NumberLong(803)
                },
                {
                        "$group" : {
                                "_id" : {
                                        "global_campaign_id" : "$global_campaign_id",
                                        "device_id" : "$device_id",
                                        "partner_id" : "$partner_id"
                                },
                                "date_last" : {
                                        "$max" : "$date_created"
                                },
                                "partner_id" : {
                                        "$first" : "$partner_id"
                                },
                                "campaign_id" : {
                                        "$first" : "$global_campaign_id"
                                },
                                "device_id" : {
                                        "$first" : "$device_id"
                                }
                        },
                        "nReturned" : NumberLong(84),
                        "executionTimeMillisEstimate" : NumberLong(1358)
                }
        ],
        "serverInfo" : {
                "host" : "nosql-lurity",
                "port" : 27017,
                "version" : "4.4.13",
                "gitVersion" : "df25c71b8674a78e17468f48bcda5285decb9246"
        },
        "ok" : 1
}

问题根源分析 #

1. 索引使用无问题：查询已命中覆盖索引，无需回表（totalDocsExamined:0），扫描30多万条索引键耗时约1.3秒，这部分效率达标。
2. 超时核心在$group阶段：$group阶段处理30多万条数据，耗时占比接近总耗时（预估1358毫秒），因为要在内存中维护每个分组的状态，当匹配的文档量随30个ID翻倍增长时，内存占用和计算开销会急剧上升，最终导致超时。
3. 集合规模间接放大压力：2亿条的总规模意味着匹配$in条件的文档数远大于当前测试的30万，数据量增长会让$group的性能瓶颈更明显。

$sort阶段是否有用？ #

直接在$group前加普通$sort作用不大，甚至会拖慢速度——当前索引是升序排列，额外排序会增加计算资源消耗。但调整索引顺序+针对性排序可以大幅优化：

将索引改为{global_campaign_id:1, device_id:1, partner_id:1, date_created:-1}（date_created降序），然后在$match后添加对应排序阶段，MongoDB可直接利用索引的有序性，让每个分组的第一条就是最新文档，此时$group无需计算$max，直接取$first即可，能大幅降低计算开销。

优化建议 #

1. 调整索引结构：创建{global_campaign_id:1, device_id:1, partner_id:1, date_created:-1}的复合索引，同时满足过滤和有序性需求。
2. 重构聚合管道：利用索引有序性直接取每组最新文档：

[
  {
    $match: {
      global_campaign_id: {$in: [3151 ,3207 ,3208 ,3209 ,3210 , ...]}
    }
  },
  {
    $sort: {
      global_campaign_id:1,
      device_id:1,
      partner_id:1,
      date_created:-1
    }
  },
  {
    $group: {
      _id: {
        global_campaign_id: "$global_campaign_id", 
        device_id: "$device_id", 
        partner_id: "$partner_id"
      },
      latest_doc: {$first: "$$ROOT"}
    }
  },
  {
    $replaceRoot: {newRoot: "$latest_doc"}
  }
]

3. 拆分查询：将30个ID拆分为多个小批量查询（比如每次查10个），在应用层合并结果，避免单次聚合处理过多数据。
4. 升级MongoDB版本：当前使用4.4.13，升级到5.0+版本后，聚合的内存管理和并行处理能力有明显提升，能更好应对大数据量分组操作。

内容的提问来源于stack exchange，提问作者Čamo