laravel - 提高更新大表、Laravel 的性能

Question

我有一个应该每周运行一次的函数(cron 作业)，现在我尝试进行压力测试。

在我的请求中，我得到:

Maximum execution time of 60 seconds exceeded

protected function updateAnswerHistory(){
    $answer_statistics = AnswerStatistic::select('question_id','paragraph_id','lkp_answer_id')->get(); //about 500row

    $history = AnswerHistory::select('id', 'question_id','paragraph_id','lkp_answer_id','correct_answer_score')->get(); //about 40k rows

    foreach ($history as $row) {

        if($row->question_id){
            $lkp_answer_id = $answer_statistics->where('question_id', $row->question_id)->pluck('lkp_answer_id')->first();
            if($row->lkp_answer_id === $lkp_answer_id){
                $row->update(['correct_answer_score' => 7]);
            }else{
                $row->update(['correct_answer_score' => 4]);
            }
        }

        if($row->paragraph_id){
            $lkp_answer_id = $answer_statistics->where('paragraph_id', $row->paragraph_id)->pluck('lkp_answer_id')->first();
            if($row->lkp_answer_id === $lkp_answer_id){
                $row->update(['correct_answer_score' => 7]);
            }else{
                $row->update(['correct_answer_score' => 4]);
            }
        }
    }
}

一件坏事是 query来自 foreach这需要时间，但我不确定如何改进。

Answer 1

我不确定我是否正确理解你的数据库表结构，

但是从数据库获取数据并更新它们的成本很高

你应该以任何方式在数据库中进行更新过程......

这个代码的想法是基于 question_id 列加入两个表然后制作'wheres'然后更新，我没有机会测试它......

AnswerHistory::join('answer_statistics','answer_statistics.question_id','answer_histories.question_id')-> where('answer_histories.question_id','!=',null)->
    where('answer_histories.lkp_answer_id','=',DB::raw('answer_statistics.lkp_answer_id'))
        ->update(['correct_answer_score' => 3]);

AnswerHistory::join('answer_statistics','answer_statistics.question_id','answer_histories.question_id')-> where('answer_histories.question_id','!=',null)->
    where('answer_histories.lkp_answer_id','!=',DB::raw('answer_statistics.lkp_answer_id'))
        ->update(['correct_answer_score' => 0]);

如果有帮助，请告诉我