Django-优化数据库查询

##一次检索你需要的所有东西

在不同的位置多次访问数据库,一次获取一个数据集,通常来说不如在一次查询中获取它们更高效。如果你在一个循环中执行查询,这尤其重要。有可能你会做很多次数据库查询,但只需要一次就够了。

我们需要充分了解并使用select_related()prefetch_related()

###select_related()
Returns a QuerySet that will “follow” foreign-key relationships, selecting additional related-object data when it executes its query. This is a performance booster which results in a single more complex query but means later use of foreign-key relationships won’t require database queries.

The following examples illustrate the difference between plain lookups and select_related() lookups. Here’s standard lookup:

# Hits the database.
e = Entry.objects.get(id=5)

# Hits the database again to get the related Blog object.
b = e.blog

And here’s select_related lookup:

# Hits the database.
e = Entry.objects.select_related('blog').get(id=5)

# Doesn't hit the database, because e.blog has been prepopulated
# in the previous query.
b = e.blog

You can use select_related() with any queryset of objects:

from django.utils import timezone

# Find all the blogs with entries scheduled to be published in the future.
blogs = set()

for e in Entry.objects.filter(pub_date__gt=timezone.now()).select_related('blog'):
    # Without select_related(), this would make a database query for each
    # loop iteration in order to fetch the related blog for each entry.
    blogs.add(e.blog)

The order of filter() and select_related() chaining isn’t important. These querysets are equivalent:

Entry.objects.filter(pub_date__gt=timezone.now()).select_related('blog')
Entry.objects.select_related('blog').filter(pub_date__gt=timezone.now())

You can follow foreign keys in a similar way to querying them. If you have the following models:

from django.db import models

class City(models.Model):
    # ...
    pass

class Person(models.Model):
    # ...
    hometown = models.ForeignKey(City)

class Book(models.Model):
    # ...
    author = models.ForeignKey(Person)

… then a call to Book.objects.select_related('author__hometown').get(id=4) will cache the related Person and the related City:

b = Book.objects.select_related('author__hometown').get(id=4)
p = b.author         # Doesn't hit the database.
c = p.hometown       # Doesn't hit the database.

b = Book.objects.get(id=4) # No select_related() in this example.
p = b.author         # Hits the database.
c = p.hometown       # Hits the database.

###prefetch_related()
待补充

##不要查询你不需要的东西

###使用QuerySet.values()values_list()
当你仅仅想要一个带有值的字典或者列表,并不需要使用ORM模型对象时,可以适当使用values()

###使用QuerySet.count()
如果你想要获取大小,不要使用 len(queryset)

###使用QuerySet.exists()
如果你想要知道是否存在至少一个结果,不要使用if queryset

###使用QuerySet.update()delete()
通过QuerySet.update()使用批量的SQL UPDATE语句,而不是获取大量对象,设置一些值再单独保存。与此相似,在可能的地方使用批量deletes

但是要注意,这些批量的更新方法不会在单独的实例上面调用save()或者delete()方法,意思是任何你向这些方法添加的自定义行为都不会被执行,包括由普通数据库对象的信号驱动的任何方法。

###直接使用外键的值
如果你仅仅需要外键当中的一个值,要使用对象上你已经取得的外键的值,而不是获取整个关联对象再得到它的主键。例如,执行:

entry.blog_id

而不是:

entry.blog.id