如何优化maatwebsite/excel导出，突破2.2万行限制至10万行？

我使用maatwebsite/excel:^3.1开发了导出Pvp数据的代码，当前仅能导出最多22000行数据，超出则触发内存限制。已尝试使用chunkSize、map方法，以及将格式从XLSX改为CSV，但问题仍未解决。请问如何优化代码，实现5万至10万行数据的导出？

原代码：

<?php

namespace App\Exports;

use App\Models\Pvp;
use Illuminate\Contracts\Queue\ShouldQueue;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Excel;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\ShouldAutoSize;
use Maatwebsite\Excel\Concerns\WithStyles;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use Maatwebsite\Excel\Concerns\WithMapping;
use Maatwebsite\Excel\Concerns\WithChunkReading;
use Maatwebsite\Excel\Concerns\FromCollection;


class PvpExport implements FromQuery, WithHeadings, ShouldAutoSize, WithMapping, WithStyles, WithChunkReading

{
    use Exportable;

    private string $writerType = Excel::XLSX;

    protected $startDate;
    protected $endDate;
    protected $mode;

    public function __construct($startDate, $endDate, $mode)
    {
        $this->startDate = $startDate;
        $this->endDate = $endDate;
        $this->mode = $mode;
    }


    public function query()
    {
        $query = Pvp::query();

        if ($this->mode !== 'all') {
            $query->where('type', $this->mode);
        }

        if ($this->startDate) {
            $query->where('created_at', '>=', $this->startDate);
        }

        if ($this->endDate) {
            $query->where('created_at', '<=', $this->endDate);

        }
        return $query;
    }




    public function map($row): array
    {
        return [
            $row['id'],
            $row['match_id'],
            $row['player_id_1'],
            $row['player_id_2'],
            $row['winner_id'],
            $row['room_id'],
            $row['type'],
            $row['rake']
        ];
    }


    public function headings(): array
    {
        return [
            'ID',
            'Match ID',
            'Player ID 1',
            'Player ID 2',
            'Winner ID',
            'Room ID',
            'Type',
            'Rake'
        ];
    }

    public function styles(Worksheet $sheet)
    {
        return [
            1 => ['font' => ['bold' => true]],
        ];
    }

    public function chunkSize(): int
    {
        return 5000;
    }
}

核心优化步骤 #

1. 启用异步队列导出 #

你的类已经引入ShouldQueue接口但未实现，开启异步后，导出任务会放到队列后台执行，彻底避免前端请求周期内的内存溢出问题。

修改类实现：

class PvpExport implements FromQuery, WithHeadings, WithMapping, WithStyles, WithChunkReading, ShouldQueue

注意：需确保Laravel队列配置正常（如Redis、数据库队列），并启动队列worker。

2. 精简查询字段 #

当前查询会加载Pvp模型的所有字段，但map里仅用到8个字段，多余字段会浪费大量内存。在query中明确指定需要的字段：

public function query()
{
    $query = Pvp::select(['id', 'match_id', 'player_id_1', 'player_id_2', 'winner_id', 'room_id', 'type', 'rake']);

    if ($this->mode !== 'all') {
        $query->where('type', $this->mode);
    }

    if ($this->startDate) {
        $query->where('created_at', '>=', $this->startDate);
    }

    if ($this->endDate) {
        $query->where('created_at', '<=', $this->endDate);
    }
    return $query;
}

3. 移除高消耗的Excel特性 #

• ShouldAutoSize：该特性会遍历所有单元格计算最佳宽度，大数据量下内存消耗极高，建议直接移除，或手动设置固定列宽度替代。
• CSV格式下禁用样式：如果使用CSV导出，WithStyles完全无效，直接去掉该接口和方法，减少不必要的处理。

4. 调整Chunk Size #

将chunkSize调整为更合理的数值（1000-2000），更小的chunk能降低单次处理的内存占用：

public function chunkSize(): int
{
    return 1500;
}

5. 服务器配置辅助优化（可选） #

临时调整PHP内存限制（仅作为辅助方案，优先代码优化）：
在导出控制器中添加：

ini_set('memory_limit', '256M'); // 根据服务器情况调整，如512M

最终优化后的代码示例 #

<?php

namespace App\Exports;

use App\Models\Pvp;
use Illuminate\Contracts\Queue\ShouldQueue;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Excel;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithStyles;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use Maatwebsite\Excel\Concerns\WithMapping;
use Maatwebsite\Excel\Concerns\WithChunkReading;

class PvpExport implements FromQuery, WithHeadings, WithMapping, WithStyles, WithChunkReading, ShouldQueue
{
    use Exportable;

    private string $writerType = Excel::XLSX;

    protected $startDate;
    protected $endDate;
    protected $mode;

    public function __construct($startDate, $endDate, $mode)
    {
        $this->startDate = $startDate;
        $this->endDate = $endDate;
        $this->mode = $mode;
    }

    public function query()
    {
        // 只查询需要的字段
        $query = Pvp::select(['id', 'match_id', 'player_id_1', 'player_id_2', 'winner_id', 'room_id', 'type', 'rake']);

        if ($this->mode !== 'all') {
            $query->where('type', $this->mode);
        }

        if ($this->startDate) {
            $query->where('created_at', '>=', $this->startDate);
        }

        if ($this->endDate) {
            $query->where('created_at', '<=', $this->endDate);
        }
        return $query;
    }

    public function map($row): array
    {
        return [
            $row['id'],
            $row['match_id'],
            $row['player_id_1'],
            $row['player_id_2'],
            $row['winner_id'],
            $row['room_id'],
            $row['type'],
            $row['rake']
        ];
    }

    public function headings(): array
    {
        return [
            'ID',
            'Match ID',
            'Player ID 1',
            'Player ID 2',
            'Winner ID',
            'Room ID',
            'Type',
            'Rake'
        ];
    }

    public function styles(Worksheet $sheet)
    {
        return [
            1 => ['font' => ['bold' => true]],
        ];
    }

    public function chunkSize(): int
    {
        return 1500;
    }
}

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