Django2.0手册:Database access optimization

Understand QuerySet evaluation¶

To avoid performance problems, it is important to understand:

• that QuerySets are lazy.
• when they are evaluated.
• how the data is held in memory.

Understand cached attributes¶

As well as caching of the whole QuerySet, there is caching of the result of
attributes on ORM objects. In general, attributes that are not callable will be
cached. For example, assuming the example Weblog models:

>>> entry = Entry.objects.get(id=1)
>>> entry.blog   # Blog object is retrieved at this point
>>> entry.blog   # cached version, no DB access

But in general, callable attributes cause DB lookups every time:

>>> entry = Entry.objects.get(id=1)
>>> entry.authors.all()   # query performed
>>> entry.authors.all()   # query performed again

Be careful when reading template code – the template system does not allow use
of parentheses, but will call callables automatically, hiding the above
distinction.

Be careful with your own custom properties – it is up to you to implement
caching when required, for example using the
cached_property decorator.

Use the with template tag¶

To make use of the caching behavior of QuerySet, you may need to use the
with template tag.

Use iterator()¶

When you have a lot of objects, the caching behavior of the QuerySet can
cause a large amount of memory to be used. In this case,
iterator() may help.

Use RawSQL¶

A less portable but more powerful method is the
RawSQL expression, which allows some SQL
to be explicitly added to the query. If that still isn’t powerful enough:

Use raw SQL¶

Write your own custom SQL to retrieve data or populate models. Use django.db.connection.queries to find out what Django
is writing for you and start from there.

Use QuerySet.select_related() and prefetch_related()¶

Understand select_related() and
prefetch_related() thoroughly, and use
them:

• in managers and default managers where
  appropriate. Be aware when your manager is and is not used; sometimes this is
  tricky so don’t make assumptions.
• in view code or other layers, possibly making use of
  prefetch_related_objects() where needed.

Use QuerySet.values() and values_list()¶

When you just want a dict or list of values, and don’t need ORM model
objects, make appropriate usage of
values().
These can be useful for replacing model objects in template code – as long as
the dicts you supply have the same attributes as those used in the template,
you are fine.

使用 QuerySet.defer() 和 only()¶

Use defer() and
only() if there are database columns
you know that you won’t need (or won’t need in most cases) to avoid loading
them. Note that if you do use them, the ORM will have to go and get them in
a separate query, making this a pessimization if you use it inappropriately.

Also, be aware that there is some (small extra) overhead incurred inside
Django when constructing a model with deferred fields. Don’t be too aggressive
in deferring fields without profiling as the database has to read most of the
non-text, non-VARCHAR data from the disk for a single row in the results, even
if it ends up only using a few columns. The defer() and only() methods
are most useful when you can avoid loading a lot of text data or for fields
that might take a lot of processing to convert back to Python. As always,
profile first, then optimize.

使用 QuerySet.exists()¶

…if you only want the count, rather than doing len(queryset).

使用 QuerySet.exists()¶

…if you only want to find out if at least one result exists, rather than if
queryset.

但是：

请不要过度使用 count() 和 exists()¶

If you are going to need other data from the QuerySet, just evaluate it.

For example, assuming an Email model that has a body attribute and a
many-to-many relation to User, the following template code is optimal:

{% if display_inbox %}
  {% with emails=user.emails.all %}
    {% if emails %}
      <p>You have {{ emails|length }} email(s)</p>
      {% for email in emails %}
        <p>{{ email.body }}</p>
      {% endfor %}
    {% else %}
      <p>No messages today.</p>
    {% endif %}
  {% endwith %}
{% endif %}

It is optimal because:

1. Since QuerySets are lazy, this does no database queries if ‘display_inbox’
   is False.
2. Use of with means that we store user.emails.all in a variable
   for later use, allowing its cache to be re-used.
3. The line {% if emails %} causes QuerySet.__bool__() to be called,
   which causes the user.emails.all() query to be run on the database, and
   at the least the first line to be turned into an ORM object. If there aren’t
   any results, it will return False, otherwise True.
4. The use of {{ emails|length }} calls QuerySet.__len__(), filling
   out the rest of the cache without doing another query.
5. The for loop iterates over the already filled cache.

In total, this code does either one or zero database queries. The only
deliberate optimization performed is the use of the with tag. Using
QuerySet.exists() or QuerySet.count() at any point would cause
additional queries.

Use QuerySet.update() and delete()¶

Rather than retrieve a load of objects, set some values, and save them
individual, use a bulk SQL UPDATE statement, via QuerySet.update(). Similarly, do bulk deletes where possible.

Note, however, that these bulk update methods cannot call the save() or
delete() methods of individual instances, which means that any custom
behavior you have added for these methods will not be executed, including
anything driven from the normal database object signals.

Use foreign key values directly¶

If you only need a foreign key value, use the foreign key value that is already on
the object you’ve got, rather than getting the whole related object and taking
its primary key. i.e. do:

entry.blog_id

替换成：

entry.blog.id

Don’t order results if you don’t care¶

Ordering is not free; each field to order by is an operation the database must
perform. If a model has a default ordering (Meta.ordering) and you don’t need it, remove
it on a QuerySet by calling
order_by() with no parameters.

Adding an index to your database may help to improve ordering performance.