django - 使用 annotate Exists 时提高 Django 查询集性能

Question

我有一个返回大量数据的查询集，它可以按年份过滤，将返回大约 10 万行，或者显示所有将带来大约 100 万行。

此注释的目的是生成一个 xlsx 电子表格。

模型表示，RelatedModel Model之间是多条线和 AnotherModel

Model:
    id
    field1
    field2
    field3

RelatedModel:
    foreign_key_model (Model)
    foreign_key_another (AnotherModel)

Queryset，如果关系存在，它会注释，这个注释很慢，可能需要几分钟。

Model.objects.all().annotate(
    related_exists=Exists(RelatedModel.objects.filter(foreign_key_model=OuterRef('id'))),
    related_column=Case(
        When(related_exists=True, then=Value('The relation exists!')),
        When(related_exists=False, then=Value('The relation doesn't exist!')),
        default=Value('This is the default value!'),
        output_field=CharField(),
    )
).values_list(
    'related_column',
    'field1',
    'field2',
    'field3'
)

Answer 1

如果唯一需要的是更改 xlsx 中 True/False 的显示方式 - 一种选择是只有一个 related_exists BooleanField 注释和稍后自定义在创建 xlsx 文档时将如何转换 - 即在序列化程序中。数据库应存储原始/未格式化的值，应用程序准备将它们显示给用户。

其他需要考虑的事项:

加速过滤的索引。

如果过滤后有数百万条记录，在一张表中 - 也许可以考虑表分区。

但是让我们看看原始查询的原始 sql。 它会是这样的:

SELECT [model_fields],
       EXISTS([CLIENT_SELECT]) AS related_exists,
       CASE
       WHEN EXISTS([CLIENT_SELECT]) = true THEN 'The relation exists!'
       WHEN EXISTS([CLIENT_SELECT]) = true THEN 'The relation does not exist!'
       ELSE 'The relation exists!'
       END AS related_column
FROM model;

马上我们就可以看到 Exists CLIENT_SELECT 的嵌套查询有吗 3次 .即使完全相同，也可能最少执行 2 次，最多执行 3 次。数据库可能优化到比 3 倍快，但仍然没有优化到 1 倍。

一、 EXISTS返回 True 或 False，我们可以只留下一个检查它是否为 True，使 'The relation does not exist!'默认值。

    related_column=Case(
        When(related_exists=True, then=Value('The relation exists!')),
        default=Value('The relation does not exist!')

为什么 related_column再次执行相同的选择并且不采用 related_exists 的值?

因为我们 无法引用计算列 在计算另一列时 - 这是 django 知道并复制表达式的数据库级约束。

等等，那我们其实不需要 related_exists专栏，让我们离开 related_column带有 CASE 语句和 1 个存在子查询。

Django 来了——我们不能(直到 3.0)在过滤器中使用表达式而不先注释它们。

所以，在我们的情况下是这样的:为了使用 Exist在 When ，我们首先需要将其添加为注解，但它不会用作引用，而是表达式的完整副本。

好消息!

自 Django 3.0我们 可以在QuerySet过滤器中直接使用输出BooleanField的表达式，而不必先注释 . Exists是这样的 BooleaField 表达式之一。

Model.objects.all().annotate(
    related_column=Case(
        When(
            Exists(RelatedModel.objects.filter(foreign_key_model=OuterRef('id'))),
            then=Value('The relation exists!'),
        ),
        default=Value('The relation doesn't exist!'),
        output_field=CharField(),
    )
)

并且只有一个嵌套选择和一个带注释的字段。

Django 2.1、2.2

这是 commit尽管之前添加了许多先决条件，但最终确定了对 bool 表达式的允许。其中之一是 conditional 的存在表达式对象上的属性并检查此属性。

所以，虽然 不推荐 和 未测试 对于 Django 2.1、2.2 来说，这似乎是一个小技巧(在没有 conditional 检查之前，它需要更多的侵入性更改):

创建 Exists表达式实例

猴子用 conditional = True 修补它

将其用作 When 中的条件声明

related_model_exists = Exists(RelatedModel.objects.filter(foreign_key_model=OuterRef('id')))

setattr(related_model_exists, 'conditional', True)

Model.objects.all().annotate(
    related_column=Case(
        When(
            relate_model_exists,
            then=Value('The relation exists!'),
        ),
        default=Value('The relation doesn't exist!'),
        output_field=CharField(),
    )
)

相关检查

relatedmodel_set__isnull=True 支票是 不是 适合的原因有几个:

它执行 LEFT OUTER JOIN - 效率低于 EXISTS

它执行 LEFT OUTER JOIN - 它连接表，这使它仅适用于 filter() 条件(不适用于注释 - 何时)，并且仅适用于 OneToOne 或 OneToMany(一个在相关模型端)关系