Django查询极慢 [英] Django Query extremely slow

问题描述

我有一个Django应用程序的问题。模型上的查询 Scope 极端缓慢，经过一些调试，我仍然没有找到问题所在。

i got a problem with an Django application. Queries on the model Scope are extremly slow and after some debugging i still got no clue where the problem is.

当我查询db scope = Scope.objects.get（pk ='Esoterik I'）在django它需要5到10秒。数据库具有少于10个条目和主键上的索引。所以它是太慢了。当对数据库执行等价查询时，如 SELECT * FROM scope WHERE title ='Esoterik I'; 一切正常，只需要大约50ms。

When i query the db like scope = Scope.objects.get(pk='Esoterik I') in django it takes 5 to 10 seconds. The database has less than 10 entries and an index on the primary key. so it is way too slow. When executing the an equivalent query on the db like SELECT * FROM scope WHERE title='Esoterik I'; everything is ok, it takes only about 50ms.

如果我使用一组结果 scope_list = Scope.objects.filter（members = some_user）进行查询，然后做例如打印（scope_list）或迭代列表的元素。查询本身只需要几个ms，但打印或迭代的元素再次需要5到10秒，但该集只有两个条目。

Same problem happens if i do a query with a set of results like scope_list = Scope.objects.filter(members=some_user) and then doing e.g. a print(scope_list) or iterating over the elements of the list. The query itself only takes a few ms but the print or iterating of the elements takes again like 5 to 10 seconds but the set has only two entries.

数据库后端是Postgresql 。

Database Backend is Postgresql. The Problem occurs the same on the local development server and apache.

这里是模型的代码：

class Scope(models.Model):
    title = models.CharField(primary_key=True, max_length=30)

    ## the semester the scope is linked with
    assoc_semester  =   models.ForeignKey(Semester, null=True) 

    ## the grade of the scope. can be Null if the scope is not a class
    assoc_grade     =   models.ForeignKey(Grade, null=True)

    ## the timetable of the scope. can be null if the scope is not direct associated with a class
    assoc_timetable =   models.ForeignKey(Timetable, null=True)

    ## the associated subject of the scope
    assoc_subject   =   models.ForeignKey(Subject)

    ## the calendar of the scope
    assoc_calendar  =   models.ForeignKey(Calendar)

    ## the usergroup of the scope
    assoc_usergroup =   models.ForeignKey(Group)

    members = models.ManyToManyField(User)

    unread_count = None

更新
这里是python探查器的输出。看来query.py被调用160万次是有点太多了。

update here the output of the python profiler. it seems query.py getting called 1.6 million times is a little too much.

推荐答案

您应该尝试首先隔离问题。运行manage.py shell并运行以下命令：

You should try and first isolate the problem. Run manage.py shell and run the following:

scope = Scope.objects.get(pk='Esoterik I')
print scope

现在django查询不会执行，直到他们非常忙。也就是说，如果你在第一行之后遇到缓慢，问题是在查询的创建中的某处，这将暗示对象管理器的问题。下一步是通过django尝试并执行原始SQL，并确保问题是真的与经理，而不是django中的bug一般。

Now django queries are not executed until they very much have to. That is to say, if you're experiencing slowness after the first line, the problem is somewhere in the creation of the query which would suggest problems with the object manager. The next step would be to try and execute raw SQL through django, and make sure the problem is really with the manager and not a bug in django in general.

如果你第二行遇到缓慢，问题是与查询的实际执行，或与数据的display \printing。您可以强制执行查询而不打印它（检查文档）以找出它是哪一个。

If you're experiencing slowness with the second line, the problem is eitherwith the actual execution of the query, or with the display\printing of the data. You can force-execute the query without printing it (check the documentation) to find out which one it is.

这就是我的理解，但我认为解决这个问题的最好方法是将过程分解为不同的部分，找出哪个部分是导致慢度的部分

That's as far as I understand but I think the best way to solve this is to break the process down to different parts and finding out which part is the one causing the slowness

这篇关于Django查询极慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！