Deep select_related and prefetch_related functions

Read the blog http://blog.jobbole.com/74881/notes

When the database has foreign keys, using select_related () and prefetch_related () can reduce the number of database requests and improve performance.

Here is the database design diagram

models.py as follows:

From django.db Import models Class province (models. Model):    name = models. Charfield (max_length=10)    def __unicode__ (self):        return to Self.name class city (models. Model):    name = models. Charfield (max_length=5)    province = models. ForeignKey (province)    def __unicode__ (self):        return Self.name class person (models. Model):    firstname  = models. Charfield (max_length=10)    LastName   = models. Charfield (max_length=10)    visitation = models. Manytomanyfield (city, related_name = "Visitor")    Hometown   = models. ForeignKey (city, related_name = "Birth")    living     = models. ForeignKey (city, related_name = "Citizen")    def __unicode__ (self):        return self.firstname + self.lastname

The app is named "Qsoptimize"

There are only 2 data in the ' qsoptimize_province ' table: Hubei Province and Guangdong province, ' qsoptimize_city ' table has only three data: Wuhan, Shiyan and Guangzhou

Select_related ()

For a pair of fields (Onetoonefield) and foreign key fields (ForeignKey), you can use select_related () to optimize the queryset.

After using the select_related () function with Queryset, Django acquires the corresponding foreign key corresponding to the object, thus eliminating the need to query the database later.

An example:

>>> citys = City.objects.all () >>> for C in Citys: ...   Print C.province

This results in a linear SQL query with SQL query statements as follows:

SELECT ' qsoptimize_city '. ' id ', ' qsoptimize_city '. ' Name ', ' qsoptimize_city '. ' province_id ' from ' qsoptimize_city ' SELECT ' qsoptimize_province '. ' id ', ' qsoptimize_province '. ' Name ' from ' qsoptimize_province ' WHERE ' qsoptimize_ Province '. ' id ' = 1; SELECT ' qsoptimize_province '. ' id ', ' qsoptimize_province '. ' Name ' from ' qsoptimize_province ' WHERE ' qsoptimize_ Province '. ' id ' = 2; SELECT ' qsoptimize_province '. ' id ', ' qsoptimize_province '. ' Name ' from ' qsoptimize_province ' WHERE ' qsoptimize_ Province '. ' id ' = 1;

After using the select_related () function

>>> citys = City.objects.select_related (). All () >>> for C in Citys:   ... Print C.province

There is only one SQL query, which greatly reduces the number of SQL queries

SELECT ' qsoptimize_city '. ' id ', ' qsoptimize_city '. ' Name ', ' qsoptimize_city '. ' province_id ', ' qsoptimize_province '. ' Id ', ' qsoptimize_province '. ' Name ' from ' qsoptimize_city ' INNER joins ' Qsoptimize_province ' on (' qsoptimize_city '. ') province_id ' = ' qsoptimize_province '. ' id ');

Django uses the inner join to get information about the province.

How to use

Select_related () accepts variable long parameters, each parameter is a field name that needs to get the foreign key (the parent table's contents), and the foreign Key's foreign key field name, foreign key foreign key .... To select a foreign key, you need to use two underscores "__" to connect.

For example, to obtain Zhang San's current province

>>> zhangs = Person.objects.select_related (' living__province '). Get (Firstname=u "Zhang", lastname=u "three") >> > zhangs.living.province

Triggered sql:

SELECT ' Qsoptimize_person '. ' id ', ' qsoptimize_person '. ' FirstName ', ' Qsoptimize_person '. ' LastName ', ' Qsoptimize_ Person '. ' hometown_id ', ' Qsoptimize_person '. ' living_id ', ' qsoptimize_city '. ' id ', ' qsoptimize_city '. ' Name ', ' Qsoptimize_city '. ' province_id ', ' qsoptimize_province '. ' id ', ' qsoptimize_province '. ' Name ' from ' Qsoptimize_person ' INNER join ' qsoptimize_city ' on (' Qsoptimize_person '. ' living_id ' = ' qsoptimize_city '. ' id ') INNER join ' Qsoptimize_ Province ' on (' qsoptimize_city '. ' province_id ' = ' qsoptimize_province '. ' id ') WHERE (' Qsoptimize_person '. ' LastName ' = ' Three ' and  ' Qsoptimize_person '. ' FirstName ' = ' Zhang ');

Django uses two inner joins to complete the request, but unspecified foreign keys are not added to the results, such as Zhang San's hometown.

django1.7 used to specify two foreign keys at a time

Zhangs = Person.objects.select_related (' hometown__province ', ' living__province '). Get (Firstname=u "Zhang", lastname=u "three" )

1.7 After use

Zhangs = Person.objects.select_related (' hometown__province '). select_related (' living__province '). Get (Firstname=u " Zhang ", lastname=u" three ")

Depth parameters

Select_related () accepts the depth parameter, and the depth parameter determines the depth of the select_related. Django recursively iterates through all the Onetoonefield and ForeignKey within the specified depth.

Zhangs = Person.objects.select_related (depth = d)

D=1 equivalent to select_related (' hometown ', ' living ')

d=2 equivalent to select_related (' hometown__province ', ' living__province ')

No parameters

Select_related () can also be non-parametric, which means that Django is required to be as deep as possible select_related. For example: Zhangs = Person.objects.select_related (). Get (Firstname=u "Zhang", lastname=u "three"). But pay attention to two points:

1. Django itself has a built-in upper limit, and for a particularly complex table relationship, Django might jump out of recursion somewhere you don't know, and that's not the way you want it to be. I don't know how the specific restrictions work.
2. Django does not know what fields you actually want to use, so it takes all the fields in, causing unnecessary waste to affect performance.

Summary

1. Select_related Primary pin-to-one and multi-pair relationship optimization.
2. Select_related uses the SQL JOIN statement to optimize and improve performance by reducing the number of SQL queries.
3. You can specify the name of the field you want to select_related with variable length parameters. You can also implement a specified recursive query by using the double underscore "__" to concatenate the field names. No specified field is cached, no depth specified is not cached, and Django will make a SQL query again if it is to be accessed.
4. You can also specify the depth of recursion through the depth parameter, and Django automatically caches all fields in the specified depth. If you want to access fields outside the specified depth, Django will make the SQL query again.
5. Also accepts parameterless calls, and Django recursively queries all of the fields as deeply as possible. But be aware of the limitations of Django recursion and the waste of performance.
6. Django >= 1.7, the select_related of chained calls is equivalent to using variable-length parameters. Django < 1.7, chained calls cause the select_related in front to fail, leaving only the last one.

Prefetch_related ()

For many-to-many fields (Manytomanyfield) and one-to-many (ForeignKey) fields, you can use prefetch_related () to optimize.

Prefetch_related () and select_related () are designed to be similar in order to reduce the number of SQL queries, but they are implemented differently. The latter resolves the problem within the SQL query through the JOIN statement. But for many-to-many relationships, it is somewhat unwise to use SQL statements to solve them, because joins get tables that are very long, resulting in increased SQL statement uptime and memory consumption. If there are n objects, the multiple-to-many fields of each object correspond to the MI bars, and the result table of the σ (n) Mi rows is generated.

The workaround for prefetch_related () is to query each table separately and then use Python to handle the relationship between them.

If we want to get Zhang San all the cities we've been to.

>>> zhangs = Person.objects.prefetch_related (' Visitation '). Get (Firstname=u "Zhang", lastname=u "three") >>> For City in Zhangs.visitation.all (): ...   Print City ...

　　

Deep select_related and prefetch_related functions