如何使 Django QuerySet 批量删除()更高效

发布于 2024-10-15 12:16:32 字数 456 浏览 7 评论 0原文

设置：
Django 1.1.2，MySQL 5.1

问题：

Blob.objects.filter(foo = foo) \
            .filter(status = Blob.PLEASE_DELETE) \
            .delete()

此代码片段导致 ORM 首先生成一个 SELECT * from xxx_blob where ... 查询，然后执行 DELETE from xxx_blob where id in (BLAH) ; 其中 BLAH 是一个长得离谱的 id 列表。由于我删除了大量的 blob，这让我和数据库都非常不高兴。

这是有原因的吗？我不明白为什么 ORM 无法将上面的代码片段转换为单个 DELETE 查询。有没有一种方法可以在不诉诸原始 SQL 的情况下对其进行优化？

原文

Setup:
Django 1.1.2, MySQL 5.1

Problem:

Blob.objects.filter(foo = foo) \
            .filter(status = Blob.PLEASE_DELETE) \
            .delete()

This snippet results in the ORM first generating a SELECT * from xxx_blob where ... query, then doing a DELETE from xxx_blob where id in (BLAH); where BLAH is a ridiculously long list of id's. Since I'm deleting a large amount of blobs, this makes both me and the DB very unhappy.

Is there a reason for this? I don't see why the ORM can't convert the above snippet into a single DELETE query. Is there a way to optimize this without resorting to raw SQL?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

︶￣淡然 2024-10-22 12:16:32

对于那些仍在寻找在 django 中批量删除的有效方法的人，这里有一个可能的解决方案：

delete() 可能如此缓慢的原因有两个： 1) Django 必须确保正确的级联删除功能，从而寻找模型的外键引用； 2) Django 必须处理模型的保存前和保存后信号。

如果您知道您的模型没有级联删除或需要处理的信号，则可以通过使用私有 API _raw_delete 来加速此过程，如下所示：

queryset._raw_delete(queryset.db)

更多详细信息，请参见此处。请注意，Django 已经尝试很好地处理这些事件，尽管在许多情况下使用原始删除会更有效。

For those who are still looking for an efficient way to bulk delete in django, here's a possible solution:

The reason delete() may be so slow is twofold: 1) Django has to ensure cascade deleting functions properly, thus looking for foreign key references to your models; 2) Django has to handle pre and post-save signals for your models.

If you know your models don't have cascade deleting or signals to be handled, you can accelerate this process by resorting to the private API _raw_delete as follows:

queryset._raw_delete(queryset.db)

More details in here. Please note that Django already tries to make a good handling of these events, though using the raw delete is, in many situations, much more efficient.

回复收藏 0 原文