优化数据库查询以解决PDF导出超时问题

问题描述 #

加载近10K条数据导出PDF时触发超时，报错信息：

Maximum Execution time off 60 seconds exceeded

原控制器代码 #

public function export(Request $request){
    $fotoOutcomes= new FotoOutcomeCollection(FotoOutcome::with('user','outcomeCategory','paymentMethod')->select('name','cost','date','pcs')->get());  
    $pdf = PDF::loadView('FotoOutcomeExport/FotoOutcomeExport', compact('fotoOutcomes'));
    return $pdf->download('Foto-Outcome.pdf');
}

原视图代码 #

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<title>Document</title>
</head>
<body>
<div className="overflow-x-auto">
    <table className="table table-zebra w-full">
        <thead>
            <tr>
                <th>No</th>
                <th>Name</th>
                <th>Date</th>
                <th>Pcs</th>
                <th>Cost</th>
            </tr>
        </thead>
        <tbody>
            @php $i=1 @endphp
            @foreach ($fotoOutcomes as $fotoOutcome)
            
            <tr>
                <th>{{$i}}</th>
                <td>{{$fotoOutcome->name}}</td>
                <td>{{$fotoOutcome->date}}</td>
                <td>{{$fotoOutcome->pcs}}</td>
                <td>{{$fotoOutcome->cost}}</td>
            </tr>
            @php $i++; @endphp
            @endforeach
        </tbody>
    </table>
</div>

优化方案 #

1. 移除未使用的关联预加载 #

视图中未用到user、outcomeCategory、paymentMethod关联数据，保留预加载会额外增加数据库查询开销，直接移除：

public function export(Request $request){
    $fotoOutcomes = new FotoOutcomeCollection(
        FotoOutcome::select('name','cost','date','pcs')->get()
    );  
    $pdf = PDF::loadView('FotoOutcomeExport/FotoOutcomeExport', compact('fotoOutcomes'));
    return $pdf->download('Foto-Outcome.pdf');
}

2. 用惰性加载减少内存占用 #

针对10K数据量，使用cursor()代替get()实现惰性加载，避免一次性加载所有数据到内存：

public function export(Request $request){
    $fotoOutcomes = new FotoOutcomeCollection(
        FotoOutcome::select('name','cost','date','pcs')->cursor()
    );  
    $pdf = PDF::loadView('FotoOutcomeExport/FotoOutcomeExport', compact('fotoOutcomes'));
    return $pdf->download('Foto-Outcome.pdf');
}

3. 优化视图计数逻辑 #

利用Blade自带的$loop变量替代手动计数，减少PHP代码执行开销：

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta http-equiv="X-UA-Compatible" content="ie=edge">
<title>Document</title>
</head>
<body>
<div className="overflow-x-auto">
    <table className="table table-zebra w-full">
        <thead>
            <tr>
                <th>No</th>
                <th>Name</th>
                <th>Date</th>
                <th>Pcs</th>
                <th>Cost</th>
            </tr>
        </thead>
        <tbody>
            @foreach ($fotoOutcomes as $fotoOutcome)
            <tr>
                <th>{{ $loop->iteration }}</th>
                <td>{{ $fotoOutcome->name }}</td>
                <td>{{ $fotoOutcome->date }}</td>
                <td>{{ $fotoOutcome->pcs }}</td>
                <td>{{ $fotoOutcome->cost }}</td>
            </tr>
            @endforeach
        </tbody>
    </table>
</div>

4. 临时延长执行时间（应急方案） #

如果以上优化后仍超时，可临时延长脚本执行时间（不推荐作为长期解决方案）：

public function export(Request $request){
    set_time_limit(120); // 延长至120秒
    
    $fotoOutcomes = new FotoOutcomeCollection(
        FotoOutcome::select('name','cost','date','pcs')->cursor()
    );  
    $pdf = PDF::loadView('FotoOutcomeExport/FotoOutcomeExport', compact('fotoOutcomes'));
    return $pdf->download('Foto-Outcome.pdf');
}

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