优化100GB MongoDB中$cond查询及新增end字段解决超时问题

场景与问题 #

管理100GB的bill_ladings集合，通过聚合管道中嵌套过深的$cond逻辑计算a、b、c等字段时频繁超时，现需新增end字段（最终结算金额）并优化整体查询性能，核心思路是拆分复杂逻辑为多个轻量查询阶段，复用现有条件逻辑。

核心优化思路 #

• 将单个复杂聚合阶段拆分为多个$addFields阶段，分步计算中间字段，降低MongoDB单阶段计算压力
• 提前通过$match过滤无关文档，减少后续处理的数据量
• 复用现有条件逻辑，避免重复编写判断规则
• 配合复合索引加速过滤与匹配操作

优化后代码实现 #

1. 重构条件计算方法 #

将原getConditionField改为返回独立的聚合阶段，每个阶段只计算一个中间字段：

public function getFieldCalculationStage($field)
{
    $statusConditions = [
        'field_d_finish' => ['field_d' => ['$in' => [status_b, status_a]]],
        'field_d_return' => ['field_d' => ['$in' => [status_c, status_d, status_e, status_a]]],
    ];

    $stages = [
        'calculated_o' => [
            '$addFields' => [
                'calculated_o' => [
                    '$cond' => [
                        'if' => [
                            '$or' => [
                                ['field_i' => 0, 'field_a' => 0],
                                ['field_i' => 0, 'field_b' => 0, 'field_a' => 0, ...$statusConditions['field_d_finish']],
                                ['field_i' => ['$gt' => 0], 'field_a' => 0],
                                ['field_i' => ['$gt' => 0], 'field_b' => ['$ne' => 0], 'field_a' => 0, ...$statusConditions['field_d_finish']],
                                ['field_i' => ['$gt' => 0], 'field_o' => ['$gt' => 0], 'field_d' => status_a, 'field_b' => 0, 'field_a' => ['$gt' => 0], 'field_c' => 0],
                            ]
                        ],
                        'then' => '$field_o',
                        'else' => 0
                    ]
                ]
            ]
        ],
        'calculated_i' => [
            '$addFields' => [
                'calculated_i' => [
                    '$cond' => [
                        'if' => [
                            '$or' => [
                                [
                                    '$and' => [
                                        ['field_i' => ['$gt' => 0]],
                                        [
                                            '$or' => [
                                                ['field_d' => ['$nin' => [status_b, status_a]]],
                                                ['field_d' => ['$in' => [status_b, status_a]], 'field_a' => ['$ne' => 0], 'field_b' => 0],
                                                ['field_d' => ['$in' => [status_b, status_a]], 'field_a' => 0, 'field_b' => 0],
                                            ]
                                        ],
                                        ['field_b' => ['$ne' => 0], 'field_a' => 0, 'field_d' => ['$nin' => [status_b, status_a]]],
                                        ['field_o' => ['$gt' => 0], 'field_d' => ['$nin' => [status_c, status_d, status_e, status_a]]],
                                    ]
                                ],
                                ['field_b' => 0, 'field_d' => ['$nin' => [status_c, status_d, status_e, status_a]]],
                            ]
                        ],
                        'then' => '$field_i',
                        'else' => 0
                    ]
                ]
            ]
        ],
        'calculated_x' => [
            '$addFields' => [
                'calculated_x' => [
                    '$cond' => [
                        'if' => ['field_y' => 0, 'field_u' => Record::field_u_PAID],
                        'then' => '$field_x',
                        'else' => 0
                    ]
                ]
            ]
        ],
        'calculated_z' => [
            '$addFields' => [
                'calculated_z' => [
                    '$cond' => [
                        'if' => ['field_a' => 0],
                        'then' => [
                            '$cond' => [
                                'if' => ['field_b' => ['$gt' => 0]],
                                'then' => ['$multiply' => ['$field_c', -1]],
                                'else' => '$field_z'
                            ]
                        ],
                        'else' => 0
                    ]
                ]
            ]
        ],
        'calculated_c' => [
            '$addFields' => [
                'calculated_c' => [
                    '$cond' => [
                        'if' => [
                            '$or' => [
                                ['field_i' => 0, 'field_a' => 0, 'field_b' => 0],
                                ['field_i' => 0, ...$statusConditions['field_d_finish'], 'field_a' => ['$ne' => 0], 'field_c' => ['$gt' => 0], 'field_xfield_o_fee_value' => 0, 'field_b' => 0],
                                ['field_i' => ['$gt' => 0], 'field_a' => 0, 'field_b' => 0],
                                ['field_i' => ['$gt' => 0], 'field_a' => ['$ne' => 0], 'field_c' => ['$gt' => 0], 'field_xfield_o_fee_value' => 0],
                                ['field_b' => 0, '$or' => [['field_o' => 0], ['field_a' => 0]]],
                            ]
                        ],
                        'then' => '$field_c',
                        'else' => 0
                    ]
                ]
            ]
        ],
        'calculated_end' => [
            '$addFields' => [
                'end' => [
                    '$add' => ['$calculated_o', '$calculated_i', '$calculated_x', '$calculated_z', '$calculated_c']
                ]
            ]
        ]
    ];

    return $stages[$field] ?? [];
}

2. 构建聚合管道并执行查询 #

public function getEndAmountAggregation()
{
    // 初始化管道，先过滤无关文档（按需调整匹配条件）
    $pipeline = [
        ['$match' => ['field_d' => ['$in' => [status_a, status_b, status_c, status_d, status_e]]]]
    ];

    // 依次添加各字段计算阶段
    $calculationFields = ['calculated_o', 'calculated_i', 'calculated_x', 'calculated_z', 'calculated_c', 'calculated_end'];
    foreach ($calculationFields as $field) {
        $stage = $this->getFieldCalculationStage($field);
        if (!empty($stage)) {
            $pipeline[] = $stage;
        }
    }

    // 只保留需要的字段，减少数据传输量
    $pipeline[] = [
        '$project' => [
            'end' => 1,
            'bill_id' => 1 // 按需保留其他业务字段
        ]
    ];

    // 执行聚合查询
    return BillLading::raw()->aggregate($pipeline);
}

额外性能优化建议 #

• 创建复合索引：针对field_d、field_i、field_a、field_b等高频过滤字段创建复合索引，加速$match阶段
• 预计算视图：将常用中间字段的计算逻辑写入MongoDB视图，减少实时计算压力
• 分片集群：针对100GB的大集合，采用MongoDB分片策略分散查询负载
• 批量处理：如果不需要实时结果，可采用批量分段计算的方式避免超时

内容的提问来源于stack exchange，提问作者Mộc Uyển Thanh