<p>首先,一个明显的优化。在循环中,基本上要对同一个查询执行四次:三次过滤不同的<code>userTypeId</code>,一次不过滤。您可以在一个<code>COUNT(*) ... GROUP BY userTypeId</code>查询中完成此操作。你知道吗</p>
<pre><code>...
# Here, we're building a dict {userTypeId: count}
# by counting PKs over each userTypeId
qs = Users.objects.using('cms').filter(universityId=item.id)
counts = {
x["userTypeId"]: x["cnt"]
for x in qs.values('userTypeId').annotate(cnt=Count('pk'))
}
student_count = counts.get(2, 0)
faculty_count = counts.get(1, 0)
club_count = count.get(3, 0)
total_count = sum(count.values()) # Assuming there may be other userTypeIds
...
</code></pre>
<p>但是,您仍然在进行1+n查询,其中n是数据库中的大学数。如果这个数字很低,这是可以的,但是如果它很高,你需要进一步的聚合,加入<code>Universities</code>和<code>Users</code>。我的初稿是这样的:</p>
<pre><code># Assuming University.name is unique, otherwise you'll need to use IDs
# to distinguish between different projects, instead of names.
qs = Users.objects.using('cms').values('userTypeId', 'university__name')\
.annotate(cnt=Count('pk').order_by('university__name')
for name, group in itertools.groupby(qs, lambda x: x["university__name"]):
print("University: %s" % name)
cnts = {g["userTypeId"]: g["cnt"] for g in group}
faculty, student, club = cnts.get(1, 0), cnts.get(2, 0), cnts.get(3, 0)
# NOTE: I'm assuming there are only few (if any) userTypeId values
# other than {1,2,3}.
total = sum(cnts.values())
print(" Student: %d, faculty: %d, club: %d, total: %d" % (
student, faculty, club, total))
</code></pre>
<p>我可能打错了,但希望是对的。就SQL而言,它应该发出如下查询</p>
<pre><code>SELECT uni.name, usr.userTypeId, COUNT(usr.id)
FROM some_app_universities AS uni
LEFT JOUN some_app_users AS usr ON us.universityId = uni.id
GROUP BY uni.name, usr.userTypeId
ORDER BY uni.name
</code></pre>
<p>考虑阅读<a href="https://docs.djangoproject.com/en/1.11/topics/db/aggregation/" rel="nofollow noreferrer">aggregations and annotations</a>上的文档。一定要检查Django ORM发出的原始SQL(例如,使用<a href="https://django-debug-toolbar.readthedocs.io/en/stable/" rel="nofollow noreferrer">Django Debug Toolbar</a>),并分析它在数据库中的工作情况。例如,如果您使用的是PostgreSQL,请使用<code>EXPLAIN SELECT</code>。根据您的数据集,您可能会受益于其中的一些索引(例如<code>userTypeId</code>列)。你知道吗</p>
<p>哦,顺便说一句。。。这是离题的,但在Python中,让变量和属性使用<code>lowercase_with_underscores</code>是一种定制。在Django中,模型类名通常是单数的,例如<code>User</code>和<code>University</code>。你知道吗</p>