postgresql 一百万条记录插入

cbjzeqam  于 12个月前  发布在  PostgreSQL
关注(0)|答案(3)|浏览(158)

如何将一百万条记录插入数据库,使系统不会挂起,然后扭曲页面没有响应的消息

$rangeNumber = collect(range($validatedDate['from'], $validatedDate['to']));
$collectData = [];

foreach ($rangeNumber as $number){
$collectData [] = [
    'receive_uuid' => $receiveUUID,
    'region_id' => $regionId,
    'office_id' => $officeId,
    'number' => $prefix.str_pad($number, 7, '0', STR_PAD_LEFT),
    'description' => '',
    'created_at' => Carbon::now(),
   ];
}

$dataCollection = collect($collectData)->chunk(10000);

foreach ($dataCollection as $key => $data){
    DB::table('blanks')->insert($data->toArray());
}

session()->flash('MsgSuccess', 'Success');
return $this->redirect(route('blanks'), true);
jljoyd4f

jljoyd4f1#

你可以使用Laravel作业,在后台处理所有这些,这样你就不必等待记录被插入。简单的例子:

php artisan make:job ProcessRecords

ProcessRecords类应该看起来像这样:

class ProcessRecords implements ShouldQueue
{
    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;

    //Get passed data from controller
    public function __construct($rangeNumbers){
        $this->rangeNumbers = $rangeNumbers;
    }

    public function handle(): void{
        $collectData = [];
        
        foreach ($this->rangeNumbers as $number){
            $collectData [] = [
                'receive_uuid' => $receiveUUID,
                'region_id' => $regionId,
                'office_id' => $officeId,
                'number' => $prefix.str_pad($number, 7, '0', STR_PAD_LEFT),
                'description' => '',
                'created_at' => Carbon::now(),
            ];
        }

        $dataCollection = collect($collectData)->chunk(10000);

        foreach ($dataCollection as $key => $data){
            DB::table('blanks')->insert($data->toArray());
        }
    }
}

然后,您可以从您的控制器调度此作业:

$rangeNumbers = collect(range($validatedDate['from'], $validatedDate['to']));
ProcessRecords::dispatch($rangeNumbers);
session()->flash('MsgSuccess', 'Success');
return $this->redirect(route('blanks'), true);
3xiyfsfu

3xiyfsfu2#

很可能,这不是一项按程序完成的工作。理想情况下,您希望使用Jobs(https://laravel.com/docs/10.x/jobs #creating-jobs)在后台运行这些类型的作业。
这将在像Horizon(https://laravel.com/docs/10.x/horizon)这样的队列上运行,并在后台处理插入,消耗更少的内存,而不是让用户等待这些插入处理。
我们可以创建一个:

php artisan make:job InsertBlanks

要生成可以在后台执行此处理的模板,请执行以下操作:

class InsertBlanks implements ShouldQueue
{
    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;
 
    /**
     * Create a new job instance.
     */
    public function __construct(
        public Collection $data,
    ) {}
 
    /**
     * Execute the job.
     */
    public function handle(): void
    {
        foreach ($this->data as $key => $data){
            DB::table('blanks')->insert($data->toArray());
        }
    }
}

我建议每个块一个作业,允许它们并行处理,并在处理过程中节省内存。理想情况下,为了进一步抽象,我们将创建一个父作业,它执行代码片段中所示的数据转换,并为每个生成的块生成子作业。
你可以在这里了解更多关于分派作业的信息(https://laravel.com/docs/10.x/jobs #dispatching-jobs)

u91tlkcl

u91tlkcl3#

在队列作业旁边
我的建议是PHP generator,Laravel已经有LazyCollection了

LazyCollection::make(function () use(......) {
    foreach (range($validatedDate['from'], $validatedDate['to']) as $number) {
        yield [
            'receive_uuid' => $receiveUUID,
            'region_id' => $regionId,
            'office_id' => $officeId,
            'number' => $prefix . str_pad($number, 7, '0', STR_PAD_LEFT),
            'description' => '',
            'created_at' => Carbon::now(),
        ];
    }
})
    ->chunk(1000)
    ->each(function ($data) {
        DB::table('blanks')->insert($data);
    });

**提示:**我还建议您使用Laravel Benchmark

还是老办法

$time_start = microtime(true);

    // your code

    $time_end = microtime(true);
    $time = $time_end - $time_start;
    $echo $time

相关问题