Laravel处理千条数据性能下降，foreach调用helper加载缓慢问题求助

核心性能问题 #

• 代码存在3次高度重复的12个月遍历逻辑，每次循环内重复执行Loan、Payroll等模型的数据库查询，查询次数指数级上升
• 循环内调用的GeneralHelper类方法（如loan_paid_item、loan_total_paid等）每次传入ID单独查询，属于典型的N+1查询问题，上千条数据就会产生上千次独立数据库请求
• 相同日期范围的计算逻辑重复执行3次，没有复用已有计算结果，资源浪费翻倍

具体优化步骤 #

1. 前置批量查询所有需要的数据 #

先一次性拉取12个月范围内的所有目标数据存入内存，同时提前批量计算所有依赖Helper的统计值缓存，避免重复查询和重复计算：

// 先确定查询的时间范围
$endDate = isset($request->end_date) ? \Carbon\Carbon::parse($request->end_date) : now();
$startRange = $endDate->copy()->subYear();
$branchId = session('branch_id');

// 批量拉取所有需要的原始数据
$loans = Loan::where('branch_id', $branchId)
    ->where('status', 'disbursed')
    ->whereBetween('created_at', [$startRange, $endDate])
    ->get(['id', 'year', 'month', 'principal', 'due_date']);
$payrolls = Payroll::where('branch_id', $branchId)
    ->whereBetween('created_at', [$startRange, $endDate])
    ->get(['id', 'year', 'month']);
$otherIncomes = OtherIncome::where('branch_id', $branchId)
    ->whereBetween('created_at', [$startRange, $endDate])
    ->get(['year', 'month', 'amount']);
$savingInterests = SavingTransaction::where('branch_id', $branchId)
    ->where('type', 'interest')
    ->whereBetween('created_at', [$startRange, $endDate])
    ->get(['year', 'month', 'amount']);

// 提前批量计算所有贷款相关统计值存入缓存
$loanStats = [];
foreach ($loans as $loan) {
    $loanStats[$loan->id] = [
        'interest' => GeneralHelper::loan_paid_item($loan->id, 'interest', $loan->due_date),
        'fees' => GeneralHelper::loan_paid_item($loan->id, 'fees', $loan->due_date),
        'penalty' => GeneralHelper::loan_paid_item($loan->id, 'penalty', $loan->due_date),
        'total_paid' => GeneralHelper::loan_total_paid($loan->id)
    ];
}
// 提前批量计算所有工资统计值存入缓存
$payrollStats = [];
foreach ($payrolls as $payroll) {
    $payrollStats[$payroll->id] = GeneralHelper::single_payroll_total_pay($payroll->id);
}

2. 合并三次月度循环为单次执行 #

原代码三个13次循环逻辑几乎完全一致，合并后一次性计算所有需要的统计值，避免重复计算：

$monthlyNetIncome = [];
$monthlyOperatingData = [];
$monthlyOtherExpenses = [];
$currentMonth = $startRange->copy();

for ($i = 1; $i < 14; $i++) {
    $year = $currentMonth->year;
    $month = $currentMonth->month;
    $ext = ($i == 1 || $i == 13) ? ' ' . $year : '';
    $monthLabel = $currentMonth->format('M' . $ext);

    // 从预加载数据中过滤当月数据，不用再次查库
    $monthLoans = $loans->where('year', $year)->where('month', $month);
    $monthPayrolls = $payrolls->where('year', $year)->where('month', $month);
    $monthOtherIncome = $otherIncomes->where('year', $year)->where('month', $month)->sum('amount');
    $monthSavingInterest = $savingInterests->where('year', $year)->where('month', $month)->sum('amount');
    $monthExpense = Expense::where('branch_id', $branchId)->where('year', $year)->where('month', $month)->sum('amount');

    // 计算当月营业利润
    $oProfit = 0;
    foreach ($monthLoans as $loan) {
        $stats = $loanStats[$loan->id];
        $oProfit += $stats['interest'] + $stats['fees'] + $stats['penalty'];
    }
    $oProfit = round($oProfit + $monthOtherIncome, 2);

    // 计算当月运营支出
    $oExpense = $monthExpense;
    foreach ($monthPayrolls as $payroll) {
        $oExpense += $payrollStats[$payroll->id];
    }
    $oExpense = round($oExpense, 2);

    // 计算当月其他支出
    $otExpense = 0;
    foreach ($monthLoans as $loan) {
        $otExpense += ($loan->principal - $loanStats[$loan->id]['total_paid']);
    }
    $otExpense += $monthSavingInterest;
    $otExpense = round($otExpense, 2);

    $netIncome = round($oProfit - $oExpense - $otExpense, 2);

    // 分别存入三个结果数组
    $monthlyNetIncome[] = ['month' => $monthLabel, 'amount' => $netIncome];
    $monthlyOperatingData[] = ['month' => $monthLabel, 'profit' => $oProfit, 'expenses' => $oExpense];
    $monthlyOtherExpenses[] = ['month' => $monthLabel, 'expenses' => $otExpense];

    $currentMonth->addMonth();
}

// 转JSON格式
$monthly_net_income_data = json_encode($monthlyNetIncome);
$monthly_operating_profit_expenses_data = json_encode($monthlyOperatingData);
$monthly_other_expenses_data = json_encode($monthlyOtherExpenses);

3. 长期优化建议 #

• 给Loan、Payroll、Expense等表的branch_id、year、month字段添加联合索引，可将查询速度提升数倍
• 把Helper类中的单条查询逻辑改造成支持批量查询的方法，进一步减少数据库请求次数
• 数据量更大时可以把月度统计做成定时任务预计算，结果存入专用统计表，接口请求时直接读取预计算结果，不需要实时跑数

内容的提问来源于stack exchange，提问作者dev sync