Django数据库查询优化

Question

关于 django db 查询优化的新手问题：

我有一个自定义模型表单来编辑 Destination 对象，并且在构造函数中我从相关的 Visitor 模型中获取查询集，该模型在 Destination 中具有 ManyToMany 字段（请参阅编辑了解原因使用自定义模型表单）

    print "loading initial choices"
    visitor_choices, visitor_initial = [], []
    visitor_set = self.instance.visitor_set.all()
    print visitor_set
    for obj in Visitor.objects.all():
        visitor_choices.append((obj.pk, obj.name))
        #if visitor_set.filter(pk=obj.pk # this hits the db every time!
        if obj in visitor_set:
            visitor_initial.append(obj.pk)

    self.fields['visitors'].choices = visitor_choices
    self.fields['visitors'].initial = visitor_initial

    print "finished loading initial choices"

这个想法是将相关的访问者集加载到变量中，以避免重复查询以检查访问者集中是否存在每个访问者。这是最好的方法吗？

另外，如果我打开数据库日志记录（如问题中所述，第二个答案），我可以看到一个重复的查询（第三个 SELECT 语句）来选择目的地 id 1 的所有访问者，但这在我编写的代码中没有，它来自哪里？

loading initial choices
(0.000) SELECT "testapp_visitor"."id", "testapp_visitor"."name" FROM "testapp_visitor" INNER JOIN "testapp_visitor_destinations" ON ("testapp_visitor"."id" = "testapp_visitor_destinations"."visitor_id") WHERE "testapp_visitor_destinations"."destination_id" = 1  LIMIT 21; args=(1,
)
[<Visitor: MIMA>, <Visitor: MIMO>, <Visitor: MIMU>]
(0.000) SELECT "testapp_visitor"."id", "testapp_visitor"."name" FROM "testapp_visitor"; args=()
(0.000) SELECT "testapp_visitor"."id", "testapp_visitor"."name" FROM "testapp_visitor" INNER JOIN "testapp_visitor_destinations" ON ("testapp_visitor"."id" = "testapp_visitor_destinations"."visitor_id") WHERE "testapp_visitor_destinations"."destination_id" = 1 ; args=(1,)
finished loading initial choices

编辑

我引用的Destination对象是Visitor对象上ManyToMany字段的相关端。如果我的表单正在编辑 Visitor 对象本身，那么 Django 将自动处理 ManyToMany 字段。但要在 Destination 的模型表单上执行此操作，我需要为 Visitor 添加多项选择字段，并自定义 __init__ 方法来加载选择以及它的初始选择。

然而问题是如何处理查询集，以及神秘的第二个sql来加载manytomany值，我也可以从shell中看到：

>>> from testapp.forms import DestinationForm
>>> from testapp.models import Destination, Visitor
>>> dest = Destination.objects.get(pk=1)
(0.001) SELECT "testapp_destination"."id", "testapp_destination"."destination" FROM "testapp_destination" WHERE "testapp_destination"."id" =
 1 ; args=(1,)
>>> destinationForm = DestinationForm(instance=dest)
loading initial choices
(0.000) SELECT "testapp_visitor"."id", "testapp_visitor"."name" FROM "testapp_visitor" INNER JOIN "testapp_visitor_destinations" ON ("testap
p_visitor"."id" = "testapp_visitor_destinations"."visitor_id") WHERE "testapp_visitor_destinations"."destination_id" = 1  LIMIT 21; args=(1,
)
[<Visitor: MIMA>, <Visitor: MIMO>, <Visitor: MIMU>]
(0.000) SELECT "testapp_visitor"."id", "testapp_visitor"."name" FROM "testapp_visitor"; args=()
(0.000) SELECT "testapp_visitor"."id", "testapp_visitor"."name" FROM "testapp_visitor" INNER JOIN "testapp_visitor_destinations" ON ("testap
p_visitor"."id" = "testapp_visitor_destinations"."visitor_id") WHERE "testapp_visitor_destinations"."destination_id" = 1 ; args=(1,)
finished loading initial choices
>>>

谢谢

Answer 1

回答你所说的是你的问题：我认为查询

SELECT "testapp_visitor"."id", "testapp_visitor"."name" FROM "testapp_visitor" INNER JOIN "testapp_visitor_destinations" ON ("testapp_visitor"."id" = "testapp_visitor_destinations"."visitor_id") WHERE "testapp_visitor_destinations"."destination_id" = 1 ; args=(1,)

来自

if obj in visitor_set:

Django 重新执行 visitor_set 查询的行（参见 当 Django 文档中的 QuerySet 被评估时）。您可以通过立即将 visitor_set 转换为 set 来避免这种情况（因此 Django 被迫立即执行查询），如下所示：

visitor_set = set(self.instance.visitor_set.all())

这也将提高测试是否存在的性能一个对象位于该集合中（与 list 或类似的可迭代对象相比）。