Laravel 块和删除

2021-12-26 00:00:00 php laravel eloquent jobs

我想从数据库中删除大量项目(1M+),我分叉了一个后台作业来处理这个问题,这样用户就不必等待它完成继续无论他/她在做什么,问题是,当项目被删除时,应用程序变得没有响应,所以我想我会一个块一个块地处理这些项目,然后休眠几秒钟,然后继续.

I have a large number of items (1M+) that i want to delete from a database, i fork a background job to take care of that, so that the user won't have to wait for it to finish to carry on whatever he/she was doing, the problem is, the app becomes unresponsive while the items are being deleted, so i thought that i would process the items chunk by chunk and sleep for a couple of seconds then carry on.

这是处理删除的代码:

// laravel job class
// ...
public function handle()
{
    $posts_archive = PostArchive::find(1); // just for the purpose of testing ;)
    Post::where('arch_id', $posts_archive->id)->chunk(1000, function ($posts) {
        //go through the collection and delete every post.
        foreach($posts as $post) {
            $post->delete();
        }
        // throttle
        sleep(2);
    });
}

预期结果:帖子被分块并处理每个块,然后空闲 2 秒,重复此操作,直到所有项目都被删除.

Expected result: the posts are chunked and each chunk is processed, then idle for 2 seconds, repeat that until all the items are deleted.

实际结果:随机删除一次,过程结束.没有错误没有指标,没有线索?

Actual result: a random number of items is deleted once, then the process ends. no errors no indicators, no clue ?

有没有更好的方法来实现这一点?

is there a better way to implement this?

推荐答案

Laravel 并没有针对您处理此问题的方式进行具体说明.如果作业中的删除查询冻结了 UI 的其余部分,听起来您的数据库服务器需要审查或优化.

There is nothing Laravel specific about the way you'd handle this. It sounds like your database server needs review or optimization if a delete query in a job is freezing the rest of the UI.

检索每个模型并单独运行删除查询绝对不是优化此操作的好方法,因为您将执行数百万个查询.如果您希望尝试限制应用程序中的每秒负载而不是优化数据库服务器来处理此查询,则可以使用带有删除限制的 while 循环:

Retrieving each model and running a delete query individually definitely isn't a good way to optimize this as you'd be executing millions of queries. You could use a while loop with a delete limit if you wish to try to limit the load per second in your application instead of optimizing your database server to handle this query:

do {
    $deleted = Post::where('arch_id', $posts_archive->id)->limit(1000)->delete();
    sleep(2);
} while ($deleted > 0);

相关文章