Laravel中带计算逻辑的集合加载过慢问题排查与优化建议

兄弟，这个N+1查询的坑我之前也踩过！访问器里嵌DB查询简直是性能杀手，数据量一上来直接卡成狗。给你几个实用的解决方案，根据你的场景挑就行：

你现在的核心问题是每条数据触发2次独立DB查询，N条数据就会产生2N次额外请求。先把关联预加载出来，再批量统计所有需要的数据，最后分配到每个模型上，把查询次数从2N降到3次左右。

修改控制器代码（顺便修正了你原代码里的变量名错误）：

public function index() {
    // 第一步：预加载scores关联，避免N+1查询
    $data = ExampleModel::with('scores')->get();
    
    // 第二步：批量收集所有需要统计的score_id
    $allScoreIds = $data->flatMap(function($model) {
        return $model->scores->pluck('score_id');
    })->unique();
    
    // 第三步：一次性查询所有score_id对应的惩罚数，按score_id分组
    $penaltyCounts = Penalty::whereIn('score_id', $allScoreIds)
        ->selectRaw('score_id, count(*) as count')
        ->groupBy('score_id')
        ->pluck('count', 'score_id')
        ->toArray();
    
    // 第四步：遍历模型批量计算，不再触发任何DB查询
    $data->each(function($model) use ($penaltyCounts) {
        $modelScoreIds = $model->scores->pluck('score_id')->unique();
        $count_score = $modelScoreIds->count();
        
        // 累加当前模型所有score_id对应的惩罚数
        $penalties = $modelScoreIds->sum(function($id) use ($penaltyCounts) {
            return $penaltyCounts[$id] ?? 0;
        });
        
        $balance = $count_score - $penalties;
        $another_score = $count_score > 0 ? ($balance / $count_score) * 0.7 : 0;
        
        // 把计算结果赋值给模型临时属性，替代原访问器
        $model->setAttribute('calculation', [
            'field_a' => $count_score,
            'field_b' => $penalties,
            'field_c' => $balance,
            'field_d' => $another_score
        ]);
    });
    
    return view('example', ['data' => $data]);
}

之后可以把原模型里的getCalculationAttribute删掉，避免重复计算。

如果你的计算逻辑能翻译成SQL，直接在查询ExampleModel时就把所有统计字段算出来，这是性能天花板——所有计算在数据库完成，只需要1次DB查询。

示例代码：

public function index() {
    $data = ExampleModel::select('example_models.*')
        // 子查询：计算当前模型对应的有效score_id总数（field_a）
        ->selectRaw('(SELECT COUNT(DISTINCT s.score_id) FROM scores s WHERE s.example_model_id = example_models.id) as field_a')
        // 子查询：计算当前模型对应的惩罚总数（field_b）
        ->selectRaw('(SELECT COUNT(p.id) FROM penalties p JOIN scores s ON p.score_id = s.score_id WHERE s.example_model_id = example_models.id) as field_b')
        ->get()
        ->map(function($model) {
            // 基于数据库返回的字段计算剩余值
            $count_score = $model->field_a;
            $penalties = $model->field_b;
            $balance = $count_score - $penalties;
            $another_score = $count_score > 0 ? ($balance / $count_score) * 0.7 : 0;
            
            $model->calculation = [
                'field_a' => $count_score,
                'field_b' => $penalties,
                'field_c' => $balance,
                'field_d' => $another_score
            ];
            
            return $model;
        });
    
    return view('example', ['data' => $data]);
}

注意：给scores.example_model_id、scores.score_id、penalties.score_id加索引，不然子查询会很慢。这个方案适合计算逻辑固定、数据更新频繁的场景。

如果计算结果不会频繁变化（比如每天更新一次），直接把结果缓存起来，避免每次请求都重复计算。

修改模型的访问器：

use Illuminate\Support\Facades\Cache;

public function getCalculationAttribute() {
    // 用模型ID作为缓存键，设置1天有效期
    return Cache::remember("example_model_calculation_{$this->id}", now()->addDay(), function() {
        $score_ids = Score::whereIn('id', $this->scores->pluck('score_id'))->pluck('id'); 
        $count_score = $score_ids->count(); 
        $penalties = Penalty::whereIn('score_id', $score_ids->toArray())->count(); 
        $balance = $count_score - $penalties; 
        $another_score = $count_score > 0 ? ($balance / $count_score) * 0.7 : 0; 
        
        return [ 
            'field_a' => $count_score, 
            'field_b' => $penalties, 
            'field_c' => $balance, 
            'field_d' => $another_score 
        ]; 
    });
}

记得在Score或Penalty数据更新时，清除对应模型的缓存：

// 在Score模型中添加事件监听
protected static function booted() {
    static::saved(function($score) {
        // 找到所有关联的ExampleModel，清除缓存
        $exampleModels = ExampleModel::whereHas('scores', function($query) use ($score) {
            $query->where('score_id', $score->id);
        })->get();
        
        foreach($exampleModels as $model) {
            Cache::forget("example_model_calculation_{$model->id}");
        }
    });
}

如果计算逻辑复杂、需要在多个地方复用，把逻辑封装到服务类里，让代码更清晰、职责更单一：

// app/Services/ExampleCalculationService.php
namespace App\Services;

use Illuminate\Database\Eloquent\Collection;
use App\Models\Penalty;

class ExampleCalculationService {
    public function calculateForModels(Collection $models): Collection {
        $allScoreIds = $models->flatMap(function($model) {
            return $model->scores->pluck('score_id');
        })->unique();
        
        $penaltyCounts = Penalty::whereIn('score_id', $allScoreIds)
            ->selectRaw('score_id, count(*) as count')
            ->groupBy('score_id')
            ->pluck('count', 'score_id')
            ->toArray();
        
        return $models->each(function($model) use ($penaltyCounts) {
            $modelScoreIds = $model->scores->pluck('score_id')->unique();
            $count_score = $modelScoreIds->count();
            $penalties = $modelScoreIds->sum(function($id) use ($penaltyCounts) {
                return $penaltyCounts[$id] ?? 0;
            });
            
            $balance = $count_score - $penalties;
            $another_score = $count_score > 0 ? ($balance / $count_score) * 0.7 : 0;
            
            $model->calculation = [
                'field_a' => $count_score,
                'field_b' => $penalties,
                'field_c' => $balance,
                'field_d' => $another_score
            ];
        });
    }
}

控制器里调用：

public function index(\App\Services\ExampleCalculationService $service) {
    $data = ExampleModel::with('scores')->get();
    $data = $service->calculateForModels($data);
    
    return view('example', ['data' => $data]);
}

总结 #

• 数据更新频繁、追求性能：优先选方案2（数据库聚合），其次是方案1（预加载批量计算）
• 数据更新不频繁：选方案3（缓存）
• 逻辑复杂、需要复用：选方案4（服务类封装）

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