python - 如何改进 Django N+1 数据导出问题？

Question

我正在尝试将数据导出为 CSV，为此需要查询数千个模型实例。没有任何优化，我的代码可以导出约 700 个实例及其相关状态。再多一点，服务器就会因502 - Bad Gateway错误而超时。

添加prefetch_related('statuses')改进的性能，但在选择超过 1000 个实例时仍然会崩溃。

状态 200 和 300 可能会丢失或存在多次。否则我可以放弃这条线obj.statuses.filter(status=status_number).first()并简单地按顺序附加它们，但我认为这里有必要。

如何提高此代码段的性能？还是我使用prefetch_related()不正确？

# models.py
class MyExportModel(models.Model):
    id = models.PrimaryKey()
    name = models.CharField(max_length=255)


class StatusModel(models.Model):
    STATUS_CHOICES = [
        (100, 'created'),
        (200, 'paid'),
        (300, 'cancelled'),
        (400, 'billed'),
        (500, 'completed')
    ]

    export_model = models.ForeignKey('MyExportModel', related_name='statuses', on_delete=models.CASCADE)
    number = models.IntegerField(choices=STATUS_CHOICES)
    created = models.DateTimeField(auto_now_add=True)


# export_as_csv.py
def export_as_csv(writer, queryset):
    # Write headings into CSV
    writer.writerow(['id', 'name'] + [f'{number} - {name}' for (number, name) in StatusModel.STATUS_CHOICES])

    # Iterate over all selected models and their related statuses
    for obj in queryset.prefetch_related('statuses'):
        fields = [obj.id, obj.name]

        # Append date for statuses if known (problematic section)
        for (status_number, _) in StatusModel.STATUS_CHOICES:
            status = obj.statuses.filter(status=status_number).first()
            fields.append(status.created if status is not None else '')

        # Write row for instance
        writer.writerow(fields)

标签： pythonsqldjangoperformanceorm