如何解决laravel-excel异步队列导出内存溢出?

1. 运行环境

php8.2

1). 当前使用的 Laravel 版本？

laravel 11.44.7

2. 问题描述？

这边使用laravel-excel3.1 异步队列导出1万多条数据，爆内存，这边chunkSize最小设置为10也是不行。怀疑配置没生效。

3. 您期望得到的结果？

运行后报错:

  2025-05-12 03:51:38 Maatwebsite\Excel\Jobs\AppendQueryToSheet ........................................................................................................ RUNNING
PHP Fatal error:  Allowed memory size of 134217728 bytes exhausted (tried to allocate 16777248 bytes) in /www/wwwroot/his/vendor/maennchen/zipstream-php/src/File.php on line 334

   Symfony\Component\ErrorHandler\Error\FatalError

  Allowed memory size of 134217728 bytes exhausted (tried to allocate 16777248 bytes)

  at vendor/maennchen/zipstream-php/src/File.php:334
    330▕                 ($this->exactSize ?? PHP_INT_MAX) - $this->uncompressedSize,
    331▕                 self::CHUNKED_READ_BLOCK_SIZE
    332▕             );
    333▕
  ➜ 334▕             $data = fread($this->unpackStream(), $readLength);
    335▕
    336▕             if ($data === false) {
    337▕                 throw new ResourceActionException('fread', $this->unpackStream());
    338▕             }


   Whoops\Exception\ErrorException

  Allowed memory size of 134217728 bytes exhausted (tried to allocate 16777248 bytes)

  at vendor/maennchen/zipstream-php/src/File.php:334
    330▕                 ($this->exactSize ?? PHP_INT_MAX) - $this->uncompressedSize,
    331▕                 self::CHUNKED_READ_BLOCK_SIZE
    332▕             );
    333▕
  ➜ 334▕             $data = fread($this->unpackStream(), $readLength);
    335▕
    336▕             if ($data === false) {
    337▕                 throw new ResourceActionException('fread', $this->unpackStream());
    338▕             }

      +1 vendor frames

  2   [internal]:0
      Whoops\Run::handleShutdown()

下面是导出代码:

<?php

namespace App\Exports;

use Throwable;
use Carbon\Carbon;
use App\Models\ExportTask;
use Illuminate\Bus\Queueable;
use App\Models\SalesPerformance;
use Illuminate\Database\Query\Builder;

// excel
use Maatwebsite\Excel\Events\AfterSheet;
use Maatwebsite\Excel\Events\BeforeExport;
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\WithEvents;
use Maatwebsite\Excel\Concerns\WithMapping;
use Illuminate\Contracts\Queue\ShouldQueue;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithChunkReading;
use Maatwebsite\Excel\Concerns\WithColumnWidths;
use Maatwebsite\Excel\Concerns\RegistersEventListeners;
use Maatwebsite\Excel\Concerns\WithStrictNullComparison;

class SalesPerformanceExport implements ShouldQueue, WithColumnWidths, WithChunkReading, WithHeadings, FromQuery, WithEvents, WithMapping, WithStrictNullComparison
{
    use Exportable, Queueable, RegistersEventListeners;

    private string $fileName = '职工工作明细表.xlsx';

    protected ExportTask $task;

    protected array $request;

    public function __construct(array $request, ExportTask $task)
    {
        $this->task    = $task;
        $this->request = $request;
    }

    public function query()
    {
        return SalesPerformance::query()
            ->select([
                'users.name as user_name',
                'sales_performance.position',
                'sales_performance.created_at',
                'sales_performance.table_name',
                'sales_performance.reception_type',
                'customer.name as customer_name',
                'sales_performance.product_name',
                'sales_performance.goods_name',
                'sales_performance.payable',
                'sales_performance.income',
                'sales_performance.arrearage',
                'sales_performance.deposit',
                'sales_performance.rate',
                'sales_performance.amount',
                'sales_performance.remark',
            ])
            ->leftJoin('users', 'users.id', '=', 'sales_performance.user_id')
            ->leftJoin('customer', 'customer.id', '=', 'sales_performance.customer_id')
            ->when($keyword = $this->request['keyword'] ?? null, fn(Builder $query) => $query->where('customer.keyword', 'like', "%{$keyword}%"))
            ->when(isset($this->request['created_at']), fn($query) => $query->whereBetween('sales_performance.created_at', $this->request['created_at']))
            // 解析场景化搜索条件
            ->queryConditions('ReportPerformanceSales', $this->request['filters'] ?? [])
            // 根据权限过滤
//            ->when(!user()->hasAnyAccess(['superuser', 'sales_performance.view.all']), function (Builder $query) {
//                $query->whereIn('sales_performance.user_id', user()->getUserIdsForSalesPerformance());
//            })
            ->orderBy('sales_performance.created_at', 'desc');
    }

    public function map($row): array
    {
        $position   = config('setting.sales_performance.position');
        $table_name = config('setting.sales_performance.table_name');

        return [
            $row->user_name,
            $position[$row->position] ?? '',
            Carbon::parse($row->created_at)->toDateString(),
            $table_name[$row->table_name] ?? '',
            get_reception_type_name($row->reception_type),
            $row->customer_name,
            $row->product_name . $row->goods_name,
            $row->payable,
            $row->income,
            $row->arrearage,
            $row->deposit,
            $row->rate,
            $row->amount,
            $row->remark,
        ];
    }

    public function headings(): array
    {
        return [
            '员工',
            '职位',
            '工作日期',
            '业务类型',
            '接诊类型',
            '顾客姓名',
            '项目/产品名称',
            '应收金额',
            '实收金额',
            '欠款金额',
            '余额支付',
            '服务占比',
            '计提金额',
            '备注'
        ];
    }

    public function columnWidths(): array
    {
        return [
            'A' => 13,
            'B' => 13,
            'C' => 15,
            'D' => 15,
            'E' => 10,
            'F' => 20,
            'G' => 40,
            'N' => 60,
        ];
    }

    /**
     * 设置分块读取的大小
     * @return int
     */
    public function chunkSize(): int
    {
        return 10; // 这里最小设置成10也有问题
    }

    public function failed(Throwable $exception): void
    {
        $this->task->update([
            'status'        => 'failed',
            'failed_at'     => now(),
            'error_message' => $exception->getMessage(),
        ]);
    }

    public static function beforeExport(BeforeExport $event): void
    {
        $event->getConcernable()->task->update([
            'status'     => 'processing',
            'started_at' => now(),
        ]);
    }

    /**
     * 导出完成
     * @param AfterSheet $event
     * @return void
     */
    public static function afterSheet(AfterSheet $event): void
    {
        $event->getConcernable()->task->update([
            'status'       => 'completed',
            'completed_at' => now(),
        ]);
    }
}