Lravel-excel 导出不到2000条数据，任务超时

由于评论区有字数限制，在这里统一回复，感谢大家的指导帮助，这个扩展包能试的方法（queue、LazyCollection、chunk_size、csv）我都试了问题还是照旧，一两百条可以导出，800 条左右偶尔成功一次，900、1000往上都是超时（任务重试时间 600s、6000s 都尝试过）。

通过记录各个部分执行的时间，得出查询速度并不慢，主要问题还是在文件写入上。

查询时间日志：

偶尔成功的一次：
虽然成功了，效率也不尽人意。

因为需求拖得时间太长了，只能暂时放弃这个包，尝试使用「box/spout」,使用这个包以后同样的数据导出csv耗时如下：

效率可能还是一般，但是总算能交作业了。

代码如下：

水平比较菜，代码比较烂，大佬们轻拍，有硬伤和需要优化的地方欢迎指正。

<?php

namespace App\Containers\Statistic\Jobs;

use App\Containers\Order\Models\Order;
use App\Containers\OrderItem\Models\OrderItem;
use App\Containers\Statistic\Models\Report;
use App\Containers\User\Models\User;
use App\Ship\Parents\Jobs\Job;
use Box\Spout\Writer\Common\Creator\WriterEntityFactory;
use Exception;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Storage;

/**
 * Class CallCenterOrdersJob
 */
class CallCenterOrdersJob extends Job
{
    private $recipients;

    public function __construct(array $recipients)
    {
        $this->recipients = $recipients;
    }

    public function handle()
    {
        Log::info('开始(电话中心)订单明细统计报表任务');

        $timeStart = \microtime(\true);

        $report = $this->recipients['report'];

        try {
            // 时间段
            $whereBetween = [$report['conditions']['date_start'], $report['conditions']['date_end']];

            // 订单搜索条件
            $where = [];
            if (isset($report['conditions']['channel_id'])) {
                $where[] = ['channel_id', $report['conditions']['channel_id']];
            }
            if (isset($report['conditions']['status'])) {
                $where[] = ['status', $report['conditions']['status']];
            }

            // 下单客服条件
            $createrWhere = [
                ['group', 'customerservice']
            ];
            if (isset($report['conditions']['department_group'])) {
                $createrWhere[] = ['department_group', $report['conditions']['department_group']];
            }

            // 订单商品条件
            $productWhere = [];
            if (isset($report['conditions']['product_name'])) {
                $productWhere[] = ['name', $report['conditions']['product_name']];
            }
            if (isset($report['conditions']['product_sn'])) {
                $productWhere[] = ['sn', $report['conditions']['product_sn']];
            }

            // 查询订单
            $query = Order::query()
                ->with([
                    'creater',
                    'member',
                    'items',
                ])
                ->whereBetween('created_at', $whereBetween)
                ->where($where)
                // 按客服条件搜索
                ->WhereIn(
                    'creater_id',
                    User::query()
                        ->select('id')
                        ->where($createrWhere)
                        ->whereHas('roles', function (Builder $query) use ($report)
                        {
                            $query->where('id', $report['conditions']['role_id']);
                        })
                        ->pluck('id')
                );

            // 订单商品条件搜索
            if (!empty($productWhere)) {
                $query->WhereIn(
                    'order_id',
                    OrderItem::query()
                        ->select('order_id')
                        ->where($productWhere)
                        ->pluck('order_id')
                );
            }
            Log::info('报表:'.$report->id.'总共有'.$query->count().'条数据');

            $path = 'uploads/media/reports/'.date('Ym/d', time()).'/'.$report->conditions['date_start'].'-'.$report->conditions['date_end'].'-订单明细统计报表(电话中心)'.date('His').'.csv';

            // 创建临时文件
            $tmpPath = 'public/'.$path;
            Storage::disk('local')->put($tmpPath, NULL);

            $writer = WriterEntityFactory::createCSVWriter()
                ->openToFile(\str_replace('storage', 'storage/app', \storage_path($tmpPath)))
                ->addRow(WriterEntityFactory::createRowFromArray([
                    '订购日期',
                    '订单号',
                    '订单状态',
                    '货号',
                    '货品名',
                    '颜色',
                    '型号',
                    '货品积分',
                    '售价',
                    '订单数量',
                    '订单金额',
                    '付款方式',
                    '会员编号',
                    '会员姓名',
                    '组别(角色)',
                    '下单客服',
                    '下单部门',
                ]));

            $query->chunk('1000', function ($orders) use ($writer)
            {
                $addRows = [];
                foreach ($orders as $order) {
                    $addRows[] = WriterEntityFactory::createRowFromArray([
                        '="'.$order->created_at.'"',
                        '="'.$order->sn.'"',
                        $order->status_str,
                        isset($order->items[0]) ? $order->items[0]->sn : '',
                        isset($order->items[0]) ? $order->items[0]->name : '',
                        isset($order->items[0]) ? $order->items[0]->product_color : '',
                        isset($order->items[0]) ? $order->items[0]->product_type : '',
                        $order->bonus_point,
                        isset($order->items[0]) ? $order->items[0]->sell_price_taxed : 0,
                        isset($order->items[0]) ? $order->items->sum('num') : 0,
                        $order->total_amount,
                        $order->is_online_pay ? '在线支付' : '线下支付',
                        $order->member ? '="'.$order->member->sn.'"' : '',
                        $order->member_name,
                        (isset($order->creater) && !empty($order->creater->roles)) ? $order->creater->roles[0]->display_name : '',
                        $order->creater_name,
                        $order->creater_department,
                    ]);
                }
                $writer->addRows($addRows);
            });

            $writer->close();

            // 上传到OSS
            Storage::put($path, Storage::disk('local')->get($tmpPath), 'private');

            // 删除本地缓存
            Storage::disk('local')->delete($tmpPath);

            // 更新报表任务状态
            $report->update([
                'file_path' => $path,
                'status'    => Report::STATUS_COMPLETED,
            ]);

        } catch (\Throwable $th) {
            // 更新报表任务状态
            $report->update([
                'status'    => Report::STATUS_FAILED,
            ]);
            Log::info('报表报错处理:'.$report->id);
            Log::info($th);
            throw $th;
        }

        $timeEnd = \microtime(\true);

        Log::info('电话中心订单明细报表任务总耗时:'.($timeEnd - $timeStart));
    }


    /**
     * 任务失败的处理过程
     *
     * @param  Exception  $exception
     * @return void
     */
    public function failed(Exception $exception)
    {
        // 更新报表任务状态
        $this->recipients['report']->update([
            'status'    => Report::STATUS_FAILED,
        ]);
        Log::info('任务失败处理:'.$this->recipients['report']->id);
        Log::info($exception);
        throw $exception;
    }
}

没错这是分割线，因为这个分割先把剪贴板写好的东西全丢了！
评论区有字数限制，写完提交不了，复制出来想编辑原文，结果复制了一条分割线，剪贴板东西就不见了。

原问题

如题使用laravel-excel 3.1的From Query with Queued导出不到2000条数据（从400万数据中关联查询），任务超时,报如下错误：

Illuminate\Queue\MaxAttemptsExceededException: Maatwebsite\Excel\Jobs\AppendQueryToSheet has been attempted too many times or run too long. The job may have previously timed out. in /var/www/lj-dg-api/vendor/laravel/framework/src/Illuminate/Queue/Worker.php:632

• Laravel Framework 6.20.7
• PHP 7.3.25-1+ubuntu18.04.1+deb.sury.org+1 (cli) (built: Dec 26 2020 10:32:33) ( NTS )
• mysql 8.0 (rds)
• redis (云服务)

测试环境少量数据（十几条）是可以成功的，当然生产环境数据量也不算大

请大佬们帮忙诊断下问题处在哪里，万分感谢！

laravel-excel 配置

<?php

use Maatwebsite\Excel\Excel;

return [
    'exports' => [

        /*
        |--------------------------------------------------------------------------
        | Chunk size
        |--------------------------------------------------------------------------
        |
        | When using FromQuery, the query is automatically chunked.
        | Here you can specify how big the chunk should be.
        |
        */
        'chunk_size'             => 100,

        /*
        |--------------------------------------------------------------------------
        | Pre-calculate formulas during export
        |--------------------------------------------------------------------------
        */
        'pre_calculate_formulas' => false,

        /*
        |--------------------------------------------------------------------------
        | Enable strict null comparison
        |--------------------------------------------------------------------------
        |
        | When enabling strict null comparison empty cells ('') will
        | be added to the sheet.
        */
        'strict_null_comparison' => false,

        /*
        |--------------------------------------------------------------------------
        | CSV Settings
        |--------------------------------------------------------------------------
        |
        | Configure e.g. delimiter, enclosure and line ending for CSV exports.
        |
        */
        'csv'                    => [
            'delimiter'              => ',',
            'enclosure'              => '"',
            'line_ending'            => PHP_EOL,
            'use_bom'                => false,
            'include_separator_line' => false,
            'excel_compatibility'    => false,
        ],

        /*
        |--------------------------------------------------------------------------
        | Worksheet properties
        |--------------------------------------------------------------------------
        |
        | Configure e.g. default title, creator, subject,...
        |
        */
        'properties'             => [
            'creator'        => '',
            'lastModifiedBy' => '',
            'title'          => '',
            'description'    => '',
            'subject'        => '',
            'keywords'       => '',
            'category'       => '',
            'manager'        => '',
            'company'        => '',
        ],
    ],

    'imports'            => [

        /*
        |--------------------------------------------------------------------------
        | Read Only
        |--------------------------------------------------------------------------
        |
        | When dealing with imports, you might only be interested in the
        | data that the sheet exists. By default we ignore all styles,
        | however if you want to do some logic based on style data
        | you can enable it by setting read_only to false.
        |
        */
        'read_only' => true,

        /*
        |--------------------------------------------------------------------------
        | Ignore Empty
        |--------------------------------------------------------------------------
        |
        | When dealing with imports, you might be interested in ignoring
        | rows that have null values or empty strings. By default rows
        | containing empty strings or empty values are not ignored but can be
        | ignored by enabling the setting ignore_empty to true.
        |
        */
        'ignore_empty' => false,

        /*
        |--------------------------------------------------------------------------
        | Heading Row Formatter
        |--------------------------------------------------------------------------
        |
        | Configure the heading row formatter.
        | Available options: none|slug|custom
        |
        */
        'heading_row' => [
            'formatter' => 'slug',
        ],

        /*
        |--------------------------------------------------------------------------
        | CSV Settings
        |--------------------------------------------------------------------------
        |
        | Configure e.g. delimiter, enclosure and line ending for CSV imports.
        |
        */
        'csv'         => [
            'delimiter'        => ',',
            'enclosure'        => '"',
            'escape_character' => '\\',
            'contiguous'       => false,
            'input_encoding'   => 'UTF-8',
        ],

        /*
        |--------------------------------------------------------------------------
        | Worksheet properties
        |--------------------------------------------------------------------------
        |
        | Configure e.g. default title, creator, subject,...
        |
        */
        'properties'  => [
            'creator'        => '',
            'lastModifiedBy' => '',
            'title'          => '',
            'description'    => '',
            'subject'        => '',
            'keywords'       => '',
            'category'       => '',
            'manager'        => '',
            'company'        => '',
        ],

    ],

    /*
    |--------------------------------------------------------------------------
    | Extension detector
    |--------------------------------------------------------------------------
    |
    | Configure here which writer/reader type should be used when the package
    | needs to guess the correct type based on the extension alone.
    |
    */
    'extension_detector' => [
        'xlsx'     => Excel::XLSX,
        'xlsm'     => Excel::XLSX,
        'xltx'     => Excel::XLSX,
        'xltm'     => Excel::XLSX,
        'xls'      => Excel::XLS,
        'xlt'      => Excel::XLS,
        'ods'      => Excel::ODS,
        'ots'      => Excel::ODS,
        'slk'      => Excel::SLK,
        'xml'      => Excel::XML,
        'gnumeric' => Excel::GNUMERIC,
        'htm'      => Excel::HTML,
        'html'     => Excel::HTML,
        'csv'      => Excel::CSV,
        'tsv'      => Excel::TSV,

        /*
        |--------------------------------------------------------------------------
        | PDF Extension
        |--------------------------------------------------------------------------
        |
        | Configure here which Pdf driver should be used by default.
        | Available options: Excel::MPDF | Excel::TCPDF | Excel::DOMPDF
        |
        */
        'pdf'      => Excel::DOMPDF,
    ],

    /*
    |--------------------------------------------------------------------------
    | Value Binder
    |--------------------------------------------------------------------------
    |
    | PhpSpreadsheet offers a way to hook into the process of a value being
    | written to a cell. In there some assumptions are made on how the
    | value should be formatted. If you want to change those defaults,
    | you can implement your own default value binder.
    |
    | Possible value binders:
    |
    | [x] Maatwebsite\Excel\DefaultValueBinder::class
    | [x] PhpOffice\PhpSpreadsheet\Cell\StringValueBinder::class
    | [x] PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder::class
    |
    */
    'value_binder' => [
        'default' => Maatwebsite\Excel\DefaultValueBinder::class,
    ],

    'cache' => [
        /*
        |--------------------------------------------------------------------------
        | Default cell caching driver
        |--------------------------------------------------------------------------
        |
        | By default PhpSpreadsheet keeps all cell values in memory, however when
        | dealing with large files, this might result into memory issues. If you
        | want to mitigate that, you can configure a cell caching driver here.
        | When using the illuminate driver, it will store each value in a the
        | cache store. This can slow down the process, because it needs to
        | store each value. You can use the "batch" store if you want to
        | only persist to the store when the memory limit is reached.
        |
        | Drivers: memory|illuminate|batch
        |
        */
        'driver'     => 'illuminate',

        /*
        |--------------------------------------------------------------------------
        | Batch memory caching
        |--------------------------------------------------------------------------
        |
        | When dealing with the "batch" caching driver, it will only
        | persist to the store when the memory limit is reached.
        | Here you can tweak the memory limit to your liking.
        |
        */
        'batch'     => [
            'memory_limit' => 60000,
        ],

        /*
        |--------------------------------------------------------------------------
        | Illuminate cache
        |--------------------------------------------------------------------------
        |
        | When using the "illuminate" caching driver, it will automatically use
        | your default cache store. However if you prefer to have the cell
        | cache on a separate store, you can configure the store name here.
        | You can use any store defined in your cache config. When leaving
        | at "null" it will use the default store.
        |
        */
        'illuminate' => [
            'store' => null,
        ],
    ],

    /*
    |--------------------------------------------------------------------------
    | Transaction Handler
    |--------------------------------------------------------------------------
    |
    | By default the import is wrapped in a transaction. This is useful
    | for when an import may fail and you want to retry it. With the
    | transactions, the previous import gets rolled-back.
    |
    | You can disable the transaction handler by setting this to null.
    | Or you can choose a custom made transaction handler here.
    |
    | Supported handlers: null|db
    |
    */
    'transactions' => [
        'handler' => 'db',
    ],

    'temporary_files' => [

        /*
        |--------------------------------------------------------------------------
        | Local Temporary Path
        |--------------------------------------------------------------------------
        |
        | When exporting and importing files, we use a temporary file, before
        | storing reading or downloading. Here you can customize that path.
        |
        */
        'local_path'          => storage_path('framework/laravel-excel'),

        /*
        |--------------------------------------------------------------------------
        | Remote Temporary Disk
        |--------------------------------------------------------------------------
        |
        | When dealing with a multi server setup with queues in which you
        | cannot rely on having a shared local temporary path, you might
        | want to store the temporary file on a shared disk. During the
        | queue executing, we'll retrieve the temporary file from that
        | location instead. When left to null, it will always use
        | the local path. This setting only has effect when using
        | in conjunction with queued imports and exports.
        |
        */
        'remote_disk'         => null,
        'remote_prefix'       => null,

        /*
        |--------------------------------------------------------------------------
        | Force Resync
        |--------------------------------------------------------------------------
        |
        | When dealing with a multi server setup as above, it's possible
        | for the clean up that occurs after entire queue has been run to only
        | cleanup the server that the last AfterImportJob runs on. The rest of the server
        | would still have the local temporary file stored on it. In this case your
        | local storage limits can be exceeded and future imports won't be processed.
        | To mitigate this you can set this config value to be true, so that after every
        | queued chunk is processed the local temporary file is deleted on the server that
        | processed it.
        |
        */
        'force_resync_remote' => null,
    ],
];

Job文件

<?php

namespace App\Containers\Statistic\Jobs;

use App\Containers\Statistic\Exports\CallCenterOrdersFromQueryExport;
use App\Containers\Statistic\Models\Report;
use App\Ship\Parents\Jobs\Job;
use Illuminate\Support\Facades\Log;

/**
 * Class CallCenterOrdersJob
 */
class CallCenterOrdersJob extends Job
{
    private $recipients;

    public function __construct(array $recipients)
    {
        $this->recipients = $recipients;
    }

    public function handle()
    {
        Log::info('开始(电话中心)订单明细统计报表任务');

        $report = $this->recipients['report'];

        try {
            // 上传数据到文件
            $path = 'uploads/media/reports/'.date('Ym/d', time()).'/'.$report->conditions['date_start'].'-'.$report->conditions['date_end'].'-订单明细统计报表(电话中心)'.date('His').'.xls';

            Log::info($path);

            (new CallCenterOrdersFromQueryExport($report))->store($path, 's3', NULL, [
                'visibility' => 'private',
            ])->chain([
                New UpdateReportAfterExportCompletedJob(['report' => $report, 'path' => $path]),
            ]);

        } catch (\Throwable $th) {
            // 更新报表任务状态
            $report->update([
                'status'    => Report::STATUS_FAILED,
            ]);
            Log::info('失败啦？');
            Log::info($th);
            throw $th;
        }

    }
}

export 文件

<?php

namespace App\Containers\Statistic\Exports;

use App\Containers\Order\Models\Order;
use App\Containers\Statistic\Models\Report;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Support\Facades\Log;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\WithColumnWidths;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\WithMapping;
use Maatwebsite\Excel\Concerns\WithStrictNullComparison;
use Throwable;

class CallCenterOrdersFromQueryExport implements FromQuery, ShouldQueue, WithColumnWidths, WithHeadings, WithStrictNullComparison, WithMapping
{
    use Exportable;

    protected $report;

    public function __construct(object $report)
    {
        Log::info('report', $report->toArray());
        $this->report = $report;
    }

    public function headings(): array
    {
        return [
            '订购日期',
            '订单号',
            '订单状态',
            '货号',
            '货品名',
            '颜色',
            '型号',
            '货品积分',
            '售价',
            '订单数量',
            '订单金额',
            '付款方式',
            '会员编号',
            '会员姓名',
            '组别(角色)',
            '下单客服',
            '下单部门',
        ];
    }

    public function columnWidths(): array
    {
        return [
            'A' => 20,
            'B' => 20,
            'C' => 10,
            'D' => 10,
            'E' => 25,
            'F' => 10,
            'G' => 10,
            'H' => 10,
            'I' => 10,
            'J' => 10,
            'K' => 10,
            'L' => 10,
            'M' => 15,
            'N' => 10,
            'O' => 20,
            'P' => 10,
            'Q' => 10,
        ];
    }

    public function query()
    {
        // 时间段
        $whereBetween = [$this->report['conditions']['date_start'], $this->report['conditions']['date_end']];

        // 订单搜索条件
        $where = [];

        if (isset($this->report['conditions']['channel_id'])) {
            $where[] = ['channel_id', $this->report['conditions']['channel_id']];
        }

        if (isset($this->report['conditions']['status'])) {
            $where[] = ['status', $this->report['conditions']['status']];
        }

        // 下单客服条件
        $createrWhere = [
            ['group', 'customerservice']
        ];
        if (isset($this->report['conditions']['department_group'])) {
            $createrWhere[] = ['department_group', $this->report['conditions']['department_group']];
        }

        // 订单商品条件
        $productWhere = [];

        if (isset($this->report['conditions']['product_name'])) {
            $productWhere[] = ['name', $this->report['conditions']['product_name']];
        }

        if (isset($this->report['conditions']['product_sn'])) {
            $productWhere[] = ['sn', $this->report['conditions']['product_sn']];
        }

        // 查询订单
        $query = Order::query()
            ->with([
                'creater',
                'member',
                'items',
            ])
            ->whereBetween('created_at', $whereBetween)
            ->where($where)
            // 按客服条件搜索
            ->whereHas('creater', function (Builder $query) use ($createrWhere)
            {
                $query->where($createrWhere);

                // 按角色
                if (isset($this->report['conditions']['role_id'])) {
                    $query->whereHas('roles', function (Builder $query)
                    {
                        $query->where('id', $this->report['conditions']['role_id']);
                    });
                }
            });

        // 订单商品条件搜索
        if (!empty($productWhere)) {
            $query->whereHas('items', function (Builder $query) use ($productWhere)
            {
                $query->where($productWhere);
            });
        }

        return $query;
    }

    /**
    * @var Service $order
    */
    public function map($order): array
    {
        return [
            $order->created_at,
            $order->sn,
            $order->status_str,
            isset($order->items[0]) ? $order->items[0]->sn : '',
            isset($order->items[0]) ? $order->items[0]->name : '',
            isset($order->items[0]) ? $order->items[0]->product_color : '',
            isset($order->items[0]) ? $order->items[0]->product_type : '',
            $order->bonus_point,
            isset($order->items[0]) ? $order->items[0]->sell_price_taxed : 0,
            isset($order->items[0]) ? $order->items->sum('num') : 0,
            $order->total_amount,
            $order->is_online_pay ? '在线支付' : '线下支付',
            $order->member ? $order->member->sn : '',
            $order->member_name,
            (isset($order->member) && !empty($order->member->roles)) ? $order->member->roles[0]->display_name : '',
            $order->creater_name,
            $order->creater_department,
        ];
    }

    public function failed(Throwable $exception): void
    {
        Log::info('报错啦:'.$exception->getMessage());
        $this->report->update([
            'status'    => Report::STATUS_FAILED,
        ]);
    }
}

任务完成后处理文件

<?php

namespace App\Containers\Statistic\Jobs;

use App\Containers\Statistic\Models\Report;
use App\Ship\Parents\Jobs\Job;
use Illuminate\Support\Facades\Log;

/**
 * Class UpdateReportAfterExportCompletedJob
 */
class UpdateReportAfterExportCompletedJob extends Job
{
    private $recipients;

    public function __construct(array $recipients)
    {
        $this->recipients = $recipients;
    }

    public function handle()
    {
        Log::info('报表:'.$this->recipients['report']->id.'生成成功啦，在这里更新下文件路径');

        // 更新报表任务状态
        $this->recipients['report']->update([
            'file_path' => $this->recipients['path'],
            'status'    => Report::STATUS_COMPLETED,
        ]);
    }
}

日志

[2021-04-04 20:29:20] production.INFO: 开始(电话中心)订单明细统计报表任务
[2021-04-04 20:29:20] production.INFO: uploads/media/reports/202104/04/2021-03-25-2021-03-31 23:59:59-订单明细统计报表(电话中心)202920.xls
[2021-04-04 20:29:20] production.INFO: report {**不重要的信息已略去**}}

[2021-04-04 20:52:51] production.INFO: 报错啦:Maatwebsite\Excel\Jobs\AppendQueryToSheet has been attempted too many times or run too long. The job may have previously timed out.

大佬们给把把脉，看问题处在哪里。先谢谢啦