django数据库查询优化

Question

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

我有一个自定义模型表单来编辑目标对象，在构造函数中，我从相关的访问者模型中获得一个查询集，该模型将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"

这样做的目的是将相关的visitor_set加载到变量中，以避免重复查询，以检查每个访问者是否存在于visitor_set中。这是最好的办法吗？

另外，如果我打开db日志记录(正如在这个问题中解释的，第二个答案)，我可以看到一个重复的查询(第三个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对象是ManyToMany字段在Visitor对象上的相关部分。如果我的表单是编辑Visitor对象本身，那么Django将自动处理ManyToMany字段。但是要在Destination的模型表单上这样做，我需要为Visitor添加一个多个选择字段，并定制__init__方法来加载它的选择和初始选择。

然而，问题是如何处理queryset，以及加载多个数据的神秘的第二个sql，我从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文档中的当QuerySets被评估时 )。您可以通过立即将visitor_set转换为set (因此Django被迫立即执行查询)来避免这种情况，如下所示：

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

这还将提高测试对象是否在此集合中的性能(与list或类似的可迭代性相比)。