使用 laravel-excel 和 queue 导入 1M 数据

问：

提前感谢您的帮助。

我正在使用 laravel 版本 7 和 laravel-excel 包导入大数据 CSV 文件。我面临的问题是 laravel 望远镜显示作业失败，但我在数据库上看到当作业显示失败时，数据库事务正在进行中。当我手动在 php artisan queue：work 中服务器并调度作业数据时，导入速度很快，但是当我启动 supervisor 运行作业时，它执行得很慢。请检查下面的代码，并告诉我我是否弄错了什么。

这是我的控制器代码

public function importStore(Request $request)
    {
        try {
            $file = $request->file('file');
            $filename = date('Ymd-His') . '-' . rand(1111, 9999) . '.' . $file->getClientOriginalExtension();
            $path = $file->storeAs('importData', $filename);

            // Dispatch the job
            $job = new MemberImportJob($path);
            dispatch($job)->onConnection('redis')->onQueue('default');

            return response()->json([
                'success' => true,
                'message' => 'File is in queue, Background job is started',
            ]);
        } catch (\Exception $e) {
            return response()->json([
                'success' => false,
                'message' => $e->getMessage(),
            ]);
        }
    }

这是我的导入文件代码

<?php

namespace App\Imports;

use App\Member;
use Illuminate\Support\Facades\Log;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithBatchInserts;
use Maatwebsite\Excel\Concerns\WithChunkReading;
use Maatwebsite\Excel\Concerns\WithHeadingRow;

class MemberFileImport implements ToModel, WithHeadingRow, WithChunkReading, WithBatchInserts
{
    /**
     * @param array $row
     *
     * @return \Illuminate\Database\Eloquent\Model|null
     */
    public function model(array $row)
    {

        $check = Member::where('mobile', $row['mobile'])->first();
        if ($check) {
            Log::info($row['mobile'] . ' already exists');
            return;
        }
        $lastMember = Member::latest()->first();
        if ($lastMember) {
            $lastId = $lastMember->id;
        } else {
            $lastId = 0;
        }

        try {
            $member = Member::create([
                'client_id' => 1,
                'account_type' => $row['account_type'],
                'name' => $row['name'],
                'ctm_id' => $row['ctm_id'],
                'sponsor_code' => $row['sponsor_code'],
                'partner_code' => $row['partner_code'],
                'gender' => $row['gender'],
                'date_of_birth' => date('Y-m-d', strtotime($row['date_of_birth'])),
                'email' => $row['email'],
                'mobile' => $row['mobile'],
                'date_of_joining' => date('Y-m-d', strtotime($row['date_of_joining'])),
                'member_id' => 'CS0' . ($lastId + 1),
                'blood_group' => $row['blood_group'],
                'married_status' => $row['maritial_status'],
                'rfid' => $row['rfid'],
                'alt_numbers' => $row['alt_number'],
                'member_type' => json_encode(explode(',', $row['member_type'])),
                'profession' => $row['profession'],
                'preferred_language' => $row['language'],
                'date_of_anniversary' => date('Y-m-d', strtotime($row['date_of_anniversary'])),
                'created_by' => 1,
            ]);
            Log::info($member->id . ' imported successfully');
        } catch (\Exception $e) {
            Log::error($e->getMessage());
            return $e->getMessage();
        }
    }

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

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

这是我的JOB文件代码

<?php

namespace App\Jobs;

use App\Imports\MemberFileImport;
use Illuminate\Bus\Queueable;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Bus\Dispatchable;
use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Queue\SerializesModels;
use Maatwebsite\Excel\Facades\Excel;

class MemberImportJob implements ShouldQueue
{
    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;
    public $file;
    public $timeout = 0;
    /**
     * Create a new job instance.
     *
     * @return void
     */
    public function __construct($file)
    {
        $this->file = $file;
    }

    /**
     * Execute the job.
     *
     * @return void
     */
    public function handle()
    {
        Excel::import(new MemberFileImport, $this->file);
    }

    
}

这是我的主管配置

[program:laravel-worker-dev]
command=php /var/www/dev-vocfoundation/artisan queue:work
process_name=%(program_name)s_%(process_num)02d
numprocs=8 
priority=999 
autostart=true
autorestart=true  
startsecs=1
startretries=3
user=nginx
redirect_stderr=true
stdout_logfile=/var/www/dev-vocfoundation/storage/logs/worker.log