When the database has foreign keys , Use  select_related() and prefetch_related() Can reduce the number of database requests , To improve performance . In this paper, a simple example is given to explain the function of these two functions . although QuerySet It's explained in detail in the document , However, this paper attempts to analyze it from the following aspects QuerySet The trigger SQL Statement to analyze how it works , To learn more about Django The specific way of operation .

1. Background of the example

Suppose a personal information system , You need to record the hometown of each person in the system 、 To live 、 And the cities I've been to . The database design is as follows :

Models.py The contents are as follows :

from django.db import models
class Province(models.Model):
name = models.CharField(max_length=10)
def __unicode__(self):
return 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

notes 1: Created app be known as “QSOptimize”

notes 2: To simplify ,`qsoptimize_province` Only in the table 2 Data : Hubei Province and Guangdong Province ,`qsoptimize_city` There are only three pieces of data in the table : Wuhan City 、 Shiyan City and Guangzhou city

2. select_related()

For one-to-one fields (OneToOneField) And foreign key fields (ForeignKey), have access to select_related Come on QuerySet To optimize

Function and method

In the face of QuerySet Use select_related() After the function ,Django Will get the object corresponding to the corresponding foreign key , So when you need it later, you don't have to query the database . Take the above example to illustrate , If we need to print all the cities in the database and their provinces , The most direct way is to :

>>> citys = City.objects.all()
>>> for c in citys:
... print c.province
...

This leads to a linear SQL Inquire about , If the number of objects n Too much , There are... In each object k If you have a foreign key field , It will lead to n*k+1 Time SQL Inquire about . In this case , Because there is 3 individual city Objects lead to 4 Time SQL Inquire about :

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 ;

If we use select_related() function :

>>> citys = City.objects.select_related().all()
>>> for c in citys:
... print c.province
...

Only once SQL Inquire about , Obviously, it's greatly reduced SQL Number of queries :

SELECT `QSOptimize_city`.`id`, `QSOptimize_city`.`name`,
`QSOptimize_city`.`province_id`, `QSOptimize_province`.`id`, `QSOptimize_province`.`name`
FROM`QSOptimize_city`
INNER JOIN `QSOptimize_province` ON (`QSOptimize_city`.`province_id` = `QSOptimize_province`.`id`) ;

Here we can see ,Django Used INNER JOIN To get information about provinces . By the way, this one SQL The result of the query is as follows :

+----+-----------+-------------+----+-----------+
| id | name      | province_id | id | name      |
+----+-----------+-------------+----+-----------+
|  1 | Wuhan City     |           1 |  1 | Hubei province     |
|  2 | guangzhou     |           2 |  2 | Guangdong province,     |
|  3 | Shiyan City     |           1 |  1 | Hubei province     |
+----+-----------+-------------+----+-----------+
3 rows in set (0.00 sec)

Usage method

Function supports the following three uses :
*fields  Parameters

select_related() Accept variable length parameters , Each parameter is a foreign key to get ( The contents of the parent table ) Field name , And the field name of the foreign key 、 Foreign key foreign key foreign key …. To select a foreign key for a foreign key, you need to use two underscores “__” To connect .

For example, we want to get Zhang San's current province , You can do this in the following way :

>>> zhangs = Person.objects.select_related('living__province').get(firstname=u" Zhang ",lastname=u" 3、 ... and ")
>>> zhangs.living.province

The trigger SQL Enquiries are as follows :

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` = ' 3、 ... and ' AND `QSOptimize_person`.`firstname` = ' Zhang ' );

You can see ,Django Used 2 Time INNER JOIN To complete the request , To obtain the city Table and province Table and add to the corresponding columns of the result table , So this is calling zhangs.living You don't have to do it again SQL Inquire about .

+----+-----------+----------+-------------+-----------+----+-----------+-------------+----+-----------+
| id | firstname | lastname | hometown_id | living_id | id | name | province_id | id | name |
+----+-----------+----------+-------------+-----------+----+-----------+-------------+----+-----------+
| 1 | Zhang | 3、 ... and | 3 | 1 | 1 | Wuhan City | 1 | 1 | Hubei province |
+----+-----------+----------+-------------+-----------+----+-----------+-------------+----+-----------+
1 row in set (0.00 sec)

However , Unspecified foreign keys are not added to the result . At this time, if it is necessary to obtain Zhang San's hometown, it will be carried out SQL The query :

>>> zhangs.hometown.province
SELECT `QSOptimize_city`.`id`, `QSOptimize_city`.`name`,
`QSOptimize_city`.`province_id`
FROM `QSOptimize_city`
WHERE `QSOptimize_city`.`id` = 3 ; SELECT `QSOptimize_province`.`id`, `QSOptimize_province`.`name`
FROM `QSOptimize_province`
WHERE `QSOptimize_province`.`id` = 1

meanwhile , If you don't specify a foreign key , Two queries will be made . If it's deeper , More queries .

It is worth mentioning that , from Django 1.7 Start ,select_related() The way functions work has changed . In this case , If you want to get Zhang San's hometown and the province where he lives at the same time , stay 1.7 You used to Can only To do so :

>>> zhangs = Person.objects.select_related('hometown__province','living__province').get(firstname=u" Zhang ",lastname=u" 3、 ... and ")
>>> zhangs.hometown.province
>>> zhangs.living.province

however 1.7 And above , You can be like and queryset Chain operations are performed just like other functions in :

>>> zhangs = Person.objects.select_related('hometown__province').select_related('living__province').get(firstname=u" Zhang ",lastname=u" 3、 ... and ")
>>> zhangs.hometown.province
>>> zhangs.living.province

If you are in the 1.7 The following version does , You only get the result of the last operation , In this case, there is only the current place of residence but no hometown . When you print your home province, it will cause two SQL Inquire about .

depth  Parameters

select_related() Accept depth Parameters ,depth The parameters can be determined select_related The depth of the .Django Recursively traverses all of the OneToOneField and ForeignKey. Take this example to illustrate :

>>> zhangs = Person.objects.select_related(depth = d)

d=1   amount to  select_related(‘hometown’,'living’)

d=2   amount to  select_related(‘hometown__province’,'living__province’)

No parameter

select_related() You can also add no parameters , It's a request Django As deep as possible select_related. for example :zhangs = Person.objects.select_related().get(firstname=u” Zhang ”,lastname=u” 3、 ... and ”). But there are two things to note :

1.Django It has a built-in upper limit , For particularly complex table relationships ,Django It's possible to jump out of recursion somewhere you don't know , So it's different from what you think . I don't know how the restrictions work .
    2.Django I don't know what fields you're actually using , So I'll grab all the fields , This will cause unnecessary waste and affect performance .

Summary

1.select_related It mainly focuses on one-to-one and many to one relationship optimization .
2.select_related Use SQL Of JOIN Statement optimization , By reducing SQL Number of queries to optimize 、 Improve performance .
3. You can specify the need through variable length parameters select_related Field name . You can also use double underscores “__” Connect field names to implement specified recursive queries . Fields not specified will not be cached , Will not cache without specified depth , If you want to visit Django It will be done again SQL Inquire about .
4. It can also be done through depth Parameter to specify the depth of the recursion ,Django Will automatically cache all fields within the specified depth . If you want to access fields beyond the specified depth ,Django It will be done again SQL Inquire about .
5. Also accept calls without parameters ,Django Will recursively query all fields as deep as possible . But notice that Django The limitation of recursion and the waste of performance .
6.Django >= 1.7, Chain called select_related Equivalent to using variable length parameters .Django < 1.7, Chained calls lead to select_related invalid , Keep only the last one .

3. prefetch_related()

For many to many fields (ManyToManyField) And one to many fields , have access to prefetch_related() To optimize . Maybe you'll say , None of them is called OneToManyField It's something special . actually
,ForeignKey It's a many to one field , And be ForeignKey The associated fields are one to many fields .

Function and method

prefetch_related() and select_related() The purpose of the design is very similar , It's all about reducing SQL Number of queries , But it's not the same way . The latter is through JOIN sentence , stay SQL Solve the problem within the query . But for many to many relationships , Use SQL Sentence solving is a bit unwise , because JOIN The resulting watch will be very long , It can lead to SQL Statement run time and memory usage increase . If you have any n Objects , Many to many fields of each object correspond to Mi strip , Will generate Σ(n)Mi

The result table of line .prefetch_related() The solution is , Query each table separately , And then use Python Deal with their relationship . Continue with the example above , If we want to get all the cities that Zhang San has been to , Use prefetch_related() That's how it should be done :

>>> zhangs = Person.objects.prefetch_related('visitation').get(firstname=u" Zhang ",lastname=u" 3、 ... and ")
>>> for city in zhangs.visitation.all() :
... print city
...

The above code triggers SQL Enquiries are as follows :

SELECT `QSOptimize_person`.`id`, `QSOptimize_person`.`firstname`,
`QSOptimize_person`.`lastname`, `QSOptimize_person`.`hometown_id`, `QSOptimize_person`.`living_id`
FROM `QSOptimize_person`
WHERE (`QSOptimize_person`.`lastname` = ' 3、 ... and ' AND `QSOptimize_person`.`firstname` = ' Zhang '); SELECT (`QSOptimize_person_visitation`.`person_id`) AS `_prefetch_related_val`, `QSOptimize_city`.`id`,
`QSOptimize_city`.`name`, `QSOptimize_city`.`province_id`
FROM `QSOptimize_city`
INNER JOIN `QSOptimize_person_visitation` ON (`QSOptimize_city`.`id` = `QSOptimize_person_visitation`.`city_id`)
WHERE `QSOptimize_person_visitation`.`person_id` IN (1);

Article 1 with a SQL The query is just to get Zhang San's Person object , The second is the key , It selects the relation table `QSOptimize_person_visitation` in `person_id` For Zhang San's line , And then `city` Table inline (INNER JOIN It's also called equivalent connection ) Get the result table .

+----+-----------+----------+-------------+-----------+
| id | firstname | lastname | hometown_id | living_id |
+----+-----------+----------+-------------+-----------+
| 1 | Zhang | 3、 ... and | 3 | 1 |
+----+-----------+----------+-------------+-----------+
1 row in set (0.00 sec) +-----------------------+----+-----------+-------------+
| _prefetch_related_val | id | name | province_id |
+-----------------------+----+-----------+-------------+
| 1 | 1 | Wuhan City | 1 |
| 1 | 2 | guangzhou | 2 |
| 1 | 3 | Shiyan City | 1 |
+-----------------------+----+-----------+-------------+
3 rows in set (0.00 sec)

Obviously, Zhang San 、 Guangzhou 、 I've been to Shiyan .

Or, , We want to get all the city names of Hubei , It can be like this :

>>> hb = Province.objects.prefetch_related('city_set').get(name__iexact=u" Hubei province ")
>>> for city in hb.city_set.all():
... city.name
...

The trigger SQL Inquire about :

SELECT `QSOptimize_province`.`id`, `QSOptimize_province`.`name`
FROM `QSOptimize_province`
WHERE `QSOptimize_province`.`name` LIKE ' Hubei province ' ; SELECT `QSOptimize_city`.`id`, `QSOptimize_city`.`name`, `QSOptimize_city`.`province_id`
FROM `QSOptimize_city`
WHERE `QSOptimize_city`.`province_id` IN (1);

The resulting table :

+----+-----------+
| id | name |
+----+-----------+
| 1 | Hubei province |
+----+-----------+
1 row in set (0.00 sec) +----+-----------+-------------+
| id | name | province_id |
+----+-----------+-------------+
| 1 | Wuhan City | 1 |
| 3 | Shiyan City | 1 |
+----+-----------+-------------+
2 rows in set (0.00 sec)

We can see ,prefetch It uses IN Statement implementation . such , stay QuerySet When there are too many objects in , Depending on the characteristics of the database, it may cause performance problems .

Usage method

*lookups Parameters

prefetch_related() stay Django < 1.7 There is only one usage . and select_related() equally ,prefetch_related() Deep query is also supported , For example, to get all the provinces that people surnamed Zhang have been to :

>>> zhangs = Person.objects.prefetch_related('visitation__province').filter(firstname__iexact=u' Zhang ')
>>> for i in zhangs:
... for city in i.visitation.all():
... print city.province
...

The trigger SQL:

SELECT `QSOptimize_person`.`id`, `QSOptimize_person`.`firstname`,
`QSOptimize_person`.`lastname`, `QSOptimize_person`.`hometown_id`, `QSOptimize_person`.`living_id`
FROM `QSOptimize_person`
WHERE `QSOptimize_person`.`firstname` LIKE ' Zhang ' ; SELECT (`QSOptimize_person_visitation`.`person_id`) AS `_prefetch_related_val`, `QSOptimize_city`.`id`,
`QSOptimize_city`.`name`, `QSOptimize_city`.`province_id` FROM `QSOptimize_city`
INNER JOIN `QSOptimize_person_visitation` ON (`QSOptimize_city`.`id` = `QSOptimize_person_visitation`.`city_id`)
WHERE `QSOptimize_person_visitation`.`person_id` IN (1, 4); SELECT `QSOptimize_province`.`id`, `QSOptimize_province`.`name`
FROM `QSOptimize_province`
WHERE `QSOptimize_province`.`id` IN (1, 2);

Results obtained :

+----+-----------+----------+-------------+-----------+
| id | firstname | lastname | hometown_id | living_id |
+----+-----------+----------+-------------+-----------+
| 1 | Zhang | 3、 ... and | 3 | 1 |
| 4 | Zhang | 6、 ... and | 2 | 2 |
+----+-----------+----------+-------------+-----------+
2 rows in set (0.00 sec) +-----------------------+----+-----------+-------------+
| _prefetch_related_val | id | name | province_id |
+-----------------------+----+-----------+-------------+
| 1 | 1 | Wuhan City | 1 |
| 1 | 2 | guangzhou | 2 |
| 4 | 2 | guangzhou | 2 |
| 1 | 3 | Shiyan City | 1 |
+-----------------------+----+-----------+-------------+
4 rows in set (0.00 sec) +----+-----------+
| id | name |
+----+-----------+
| 1 | Hubei province |
| 2 | Guangdong province, |
+----+-----------+
2 rows in set (0.00 sec)

It is worth mentioning that , The chain prefetch_related These queries will be added , It's like 1.7 Medium select_related like that .

It should be noted that , In the use of QuerySet When , Once the database request is changed in the chain operation , I used to use prefetch_related The cached data will be ignored . This can lead to Django Re request the database to get the corresponding data , This causes performance problems . The database change requests mentioned here refer to all kinds of filter()、exclude() And so on, will eventually change SQL The operation of the code . and all() It doesn't change the final database request , Therefore, it will not result in a re request to the database .

for instance , To get all the cities that people have visited with “ City ” The city of the word , This leads to a lot of SQL Inquire about :

plist =Person.objects.prefetch_related('visitation')
[p.visitation.filter(name__icontains=u" City ")for p in plist]

Because the database has 4 people , Led to 2+4 Time SQL Inquire about :

SELECT `QSOptimize_person`.`id`, `QSOptimize_person`.`firstname`, `QSOptimize_person`.`lastname`,
`QSOptimize_person`.`hometown_id`, `QSOptimize_person`.`living_id`
FROM `QSOptimize_person`; SELECT (`QSOptimize_person_visitation`.`person_id`) AS `_prefetch_related_val`, `QSOptimize_city`.`id`,
`QSOptimize_city`.`name`, `QSOptimize_city`.`province_id`
FROM `QSOptimize_city`
INNER JOIN `QSOptimize_person_visitation` ON (`QSOptimize_city`.`id` = `QSOptimize_person_visitation`.`city_id`)
WHERE `QSOptimize_person_visitation`.`person_id` IN (1, 2, 3, 4); SELECT `QSOptimize_city`.`id`, `QSOptimize_city`.`name`, `QSOptimize_city`.`province_id`
FROM `QSOptimize_city`
INNER JOIN `QSOptimize_person_visitation` ON (`QSOptimize_city`.`id` = `QSOptimize_person_visitation`.`city_id`)
WHERE(`QSOptimize_person_visitation`.`person_id` = 1 AND `QSOptimize_city`.`name` LIKE '% City %' ); SELECT `QSOptimize_city`.`id`, `QSOptimize_city`.`name`, `QSOptimize_city`.`province_id`
FROM `QSOptimize_city`
INNER JOIN `QSOptimize_person_visitation` ON (`QSOptimize_city`.`id` = `QSOptimize_person_visitation`.`city_id`)
WHERE (`QSOptimize_person_visitation`.`person_id` = 2 AND `QSOptimize_city`.`name` LIKE '% City %' ); SELECT `QSOptimize_city`.`id`, `QSOptimize_city`.`name`, `QSOptimize_city`.`province_id`
FROM `QSOptimize_city`
INNER JOIN `QSOptimize_person_visitation` ON (`QSOptimize_city`.`id` = `QSOptimize_person_visitation`.`city_id`)
WHERE (`QSOptimize_person_visitation`.`person_id` = 3 AND `QSOptimize_city`.`name` LIKE '% City %' ); SELECT `QSOptimize_city`.`id`, `QSOptimize_city`.`name`, `QSOptimize_city`.`province_id`
FROM `QSOptimize_city`
INNER JOIN `QSOptimize_person_visitation` ON (`QSOptimize_city`.`id` = `QSOptimize_person_visitation`.`city_id`)
WHERE (`QSOptimize_person_visitation`.`person_id` = 4 AND `QSOptimize_city`.`name` LIKE '% City %' );

as everyone knows ,QuerySet yes lazy Of , Access the database only when you want to use it . Run to the second line Python Code ,for The cycle will plist regard as iterator, This triggers a database query . The first two SQL The query is prefetch_related As a result of .

Although the query results contain all the required city Information about , But because in the circulatory system Person.visitation the filter operation , This obviously changes the database request . So these operations ignore the previously cached data , Re engage SQL Inquire about .

But if there is such a demand, what should we do ? stay Django >= 1.7, You can go through the next section of Prefetch Object to implement , If your environment is Django < 1.7, Can be in Python Complete this part of the operation in .

plist = Person.objects.prefetch_related('visitation')
[[city for city in p.visitation.all() if u" City " in city.name] for p in plist]

Prefetch object

stay Django >= 1.7, It can be used Prefetch Object to control prefetch_related Behavior of functions .

1. One Prefetch Object can only specify one prefetch operation .
2.Prefetch Object to the field and prefetch_related The parameters in are the same , It's all done with double underlined field names .
3. Can pass queryset Parameters are manually specified prefetch The use of QuerySet.
4. Can pass to_attr Parameter assignment prefetch Property name to .
5.Prefetch Object and string form lookups Parameters can be mixed .

4. Best practices

1.prefetch_related It mainly optimizes one to many and many to many relationships .
2.prefetch_related Get the contents of each table separately , And then use Python Deal with the relationship between them to optimize .
3. You can specify the need through variable length parameters select_related Field name . Specify the way and characteristics associated with select_related It's the same .
4. stay Django >= 1.7 Can pass Prefetch Object to implement complex queries , But the lower version Django It seems that it can only be realized by itself .
5. As prefetch_related Parameters of ,Prefetch Objects and strings can be mixed .
6.prefetch_related The chained call of will put the corresponding prefetch Add in , Not replacement , There seems to be no difference based on different versions .
7. You can pass in None To clear the previous prefetch_related.

Choose which function

If we want to get all the people whose hometown is Hubei , The most brainless way is to get Hubei Province first , Get all the cities in Hubei , The people who finally got their hometown were the people in this city . Just like this. :

>>> hb = Province.objects.get(name__iexact=u" Hubei province ")
>>> people = []
>>> for city in hb.city_set.all():
... people.extend(city.birth.all())
...
Obviously this is not a wise choice , Because doing so will lead to 1+( The number of cities in Hubei Province ) Time SQL Inquire about . It's a counterexample , The resulting queries and results are not listed .

prefetch_related() Maybe a good solution , Let's see .

>>> hb = Province.objects.prefetch_related("city_set__birth").objects.get(name__iexact=u" Hubei province ")
>>> people = []
>>> for city in hb.city_set.all():
... people.extend(city.birth.all())
...

Because it's a depth of 2 Of prefetch, So it leads to 3 Time SQL Inquire about :

SELECT `QSOptimize_province`.`id`, `QSOptimize_province`.`name`
FROM `QSOptimize_province`
WHERE `QSOptimize_province`.`name` LIKE ' Hubei province ' ; SELECT `QSOptimize_city`.`id`, `QSOptimize_city`.`name`, `QSOptimize_city`.`province_id`
FROM `QSOptimize_city`
WHERE `QSOptimize_city`.`province_id` IN (1); SELECT `QSOptimize_person`.`id`, `QSOptimize_person`.`firstname`, `QSOptimize_person`.`lastname`,
`QSOptimize_person`.`hometown_id`, `QSOptimize_person`.`living_id`
FROM `QSOptimize_person`
WHERE `QSOptimize_person`.`hometown_id` IN (1, 3);

Um. … It looks good , however 3 It's a query ? It's probably easier to look up it the other way round ?

>>> people = list(Person.objects.select_related("hometown__province").filter(hometown__province__name__iexact=u" Hubei province "))
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`.`hometown_id` = `QSOptimize_city`.`id`)
INNER JOIN `QSOptimize_province` ON (`QSOptimize_city`.`province_id` = `QSOptimize_province`.`id`)
WHERE `QSOptimize_province`.`name` LIKE ' Hubei province ';
+----+-----------+----------+-------------+-----------+----+--------+-------------+----+--------+
| id | firstname | lastname | hometown_id | living_id | id | name | province_id | id | name |
+----+-----------+----------+-------------+-----------+----+--------+-------------+----+--------+
| 1 | Zhang | 3、 ... and | 3 | 1 | 3 | Shiyan City | 1 | 1 | Hubei province |
| 2 | Li | Four | 1 | 3 | 1 | Wuhan City | 1 | 1 | Hubei province |
| 3 | king | Pockmarks | 3 | 2 | 3 | Shiyan City | 1 | 1 | Hubei province |
+----+-----------+----------+-------------+-----------+----+--------+-------------+----+--------+
3 rows in set (0.00 sec)
No problem at all . Not only SQL The number of queries has decreased ,python The procedure has also been streamlined .
select_related() It's more efficient than prefetch_related(). therefore , It's better to be able to use select_related() Use it wherever you can , in other words , about ForeignKey Field , Avoid using prefetch_related().

For the same QuerySet, You can use both functions at the same time . Add a... To the example we've been using model:Order ( Order )

class Order(models.Model):
customer = models.ForeignKey(Person)
orderinfo = models.CharField(max_length=50)
time = models.DateTimeField(auto_now_add = True)
def __unicode__(self):
return self.orderinfo

If we get an order id We need to know the provinces where the customers of this order have been . Because there is ManyToManyField Obviously we have to use prefetch_related(). If only prefetch_related() What will happen? ?

>>> plist = Order.objects.prefetch_related('customer__visitation__province').get(id=1)
>>> for city in plist.customer.visitation.all():
... print city.province.name
...

obviously , It's about 4 Tables :Order、Person、City、Province, according to prefetch_related() You have to have 4 Time SQL Inquire about

SELECT `QSOptimize_order`.`id`, `QSOptimize_order`.`customer_id`, `QSOptimize_order`.`orderinfo`, `QSOptimize_order`.`time`
FROM `QSOptimize_order`
WHERE `QSOptimize_order`.`id` = 1 ; SELECT `QSOptimize_person`.`id`, `QSOptimize_person`.`firstname`, `QSOptimize_person`.`lastname`, `QSOptimize_person`.`hometown_id`, `QSOptimize_person`.`living_id`
FROM `QSOptimize_person`
WHERE `QSOptimize_person`.`id` IN (1); SELECT (`QSOptimize_person_visitation`.`person_id`) AS `_prefetch_related_val`, `QSOptimize_city`.`id`,
`QSOptimize_city`.`name`, `QSOptimize_city`.`province_id`
FROM `QSOptimize_city`
INNER JOIN `QSOptimize_person_visitation` ON (`QSOptimize_city`.`id` = `QSOptimize_person_visitation`.`city_id`)
WHERE `QSOptimize_person_visitation`.`person_id` IN (1); SELECT `QSOptimize_province`.`id`, `QSOptimize_province`.`name`
FROM `QSOptimize_province`
WHERE `QSOptimize_province`.`id` IN (1, 2);
+----+-------------+---------------+---------------------+
| id | customer_id | orderinfo | time |
+----+-------------+---------------+---------------------+
| 1 | 1 | Info of Order | 2014-08-10 17:05:48 |
+----+-------------+---------------+---------------------+
1 row in set (0.00 sec) +----+-----------+----------+-------------+-----------+
| id | firstname | lastname | hometown_id | living_id |
+----+-----------+----------+-------------+-----------+
| 1 | Zhang | 3、 ... and | 3 | 1 |
+----+-----------+----------+-------------+-----------+
1 row in set (0.00 sec) +-----------------------+----+--------+-------------+
| _prefetch_related_val | id | name | province_id |
+-----------------------+----+--------+-------------+
| 1 | 1 | Wuhan City | 1 |
| 1 | 2 | guangzhou | 2 |
| 1 | 3 | Shiyan City | 1 |
+-----------------------+----+--------+-------------+
3 rows in set (0.00 sec) +----+--------+
| id | name |
+----+--------+
| 1 | Hubei province |
| 2 | Guangdong province, |
+----+--------+
2 rows in set (0.00 sec)

A better way is to call... First select_related() Call again prefetch_related(), Finally, select_related() The watch at the back

>>> plist = Order.objects.select_related('customer').prefetch_related('customer__visitation__province').get(id=1)
>>> for city in plist.customer.visitation.all():
... print city.province.name
...

There will only be 3 Time SQL Inquire about ,Django Will do it first select_related, after prefetch_related It will take advantage of the previously cached data , Thus avoiding 1 Three extra SQL Inquire about :

SELECT `QSOptimize_order`.`id`, `QSOptimize_order`.`customer_id`, `QSOptimize_order`.`orderinfo`,
`QSOptimize_order`.`time`, `QSOptimize_person`.`id`, `QSOptimize_person`.`firstname`,
`QSOptimize_person`.`lastname`, `QSOptimize_person`.`hometown_id`, `QSOptimize_person`.`living_id`
FROM `QSOptimize_order`
INNER JOIN `QSOptimize_person` ON (`QSOptimize_order`.`customer_id` = `QSOptimize_person`.`id`)
WHERE `QSOptimize_order`.`id` = 1 ; SELECT (`QSOptimize_person_visitation`.`person_id`) AS `_prefetch_related_val`, `QSOptimize_city`.`id`,
`QSOptimize_city`.`name`, `QSOptimize_city`.`province_id`
FROM `QSOptimize_city`
INNER JOIN `QSOptimize_person_visitation` ON (`QSOptimize_city`.`id` = `QSOptimize_person_visitation`.`city_id`)
WHERE `QSOptimize_person_visitation`.`person_id` IN (1); SELECT `QSOptimize_province`.`id`, `QSOptimize_province`.`name`
FROM `QSOptimize_province`
WHERE `QSOptimize_province`.`id` IN (1, 2);
+----+-------------+---------------+---------------------+----+-----------+----------+-------------+-----------+
| id | customer_id | orderinfo | time | id | firstname | lastname | hometown_id | living_id |
+----+-------------+---------------+---------------------+----+-----------+----------+-------------+-----------+
| 1 | 1 | Info of Order | 2014-08-10 17:05:48 | 1 | Zhang | 3、 ... and | 3 | 1 |
+----+-------------+---------------+---------------------+----+-----------+----------+-------------+-----------+
1 row in set (0.00 sec) +-----------------------+----+--------+-------------+
| _prefetch_related_val | id | name | province_id |
+-----------------------+----+--------+-------------+
| 1 | 1 | Wuhan City | 1 |
| 1 | 2 | guangzhou | 2 |
| 1 | 3 | Shiyan City | 1 |
+-----------------------+----+--------+-------------+
3 rows in set (0.00 sec) +----+--------+
| id | name |
+----+--------+
| 1 | Hubei province |
| 2 | Guangdong province, |
+----+--------+
2 rows in set (0.00 sec)

It is worth noting that , You can call prefetch_related Previous call select_related, also Django Will do what you want : First select_related, Then use the cached data prefetch_related. But once prefetch_related Has called ,select_related Will not work .

Summary

  1. because select_related() It's always in a single shot SQL Solve the problem in the query , and prefetch_related() Each related table will be SQL Inquire about , therefore select_related() Is usually more efficient than the latter .
  2. In view of the first , Use... As much as possible select_related() solve the problem . Only in select_related() Think about it when you can't solve the problem prefetch_related().
  3. You can do it in one QuerySet Use... At the same time select_related() and prefetch_related(), Thereby reducing SQL Number of queries .
  4. Only prefetch_related() Previous select_related() It works , The rest will be ignored .
 original text :https://blog.csdn.net/secretx/article/details/43964607

Django Of select_related and prefetch_related Function optimization query more related articles

  1. The content of this post is worth learning -- Details of the example Django Of select_related and prefetch_related Function pair QuerySet Query optimization

    It feels like DJANGO Good use ,ORM We must learn well , No management is built in , Or a third party ORM. At the end of the day SQL.....:( this , Practice slowly .. Details of the example Django Of select_related and p ...

  2. Django Of select_related and prefetch_related Function pair QuerySet Query optimization ( Two )

    3. prefetch_related() For many to many fields (ManyToManyField) And one to many fields , have access to prefetch_related() To optimize . Maybe you'll say , None of them is called OneToMan ...

  3. Django Of select_related and prefetch_related Function pair QuerySet Query optimization ( One )

    When the database has foreign keys , Use  select_related() and prefetch_related() Can reduce the number of database requests , To improve performance . In this paper, a simple example is given to explain the function of these two functions . although Q ...

  4. Reprint : Details of the example Django Of select_related and prefetch_related Function pair QuerySet Query optimization ( One )

    When the database has foreign keys , Use  select_related() and prefetch_related() Can reduce the number of database requests , To improve performance . In this paper, a simple example is given to explain the function of these two functions . although Q ...

  5. Examples explain Django Of select_related and prefetch_related Function pair QuerySet Query optimization ( Two )

    This is the second in this series , The content is prefetch_related() Purpose of function . The way to achieve it . And usage . The first article in this series is here The third one is here 3. prefetch_related() For many to many fields (Ma ...

  6. Detailed explanation Django Of select_related and prefetch_related Function pair QuerySet Query optimization

    When the database has foreign keys , Use  select_related() and prefetch_related() Can reduce the number of database requests , To improve performance . In this paper, a simple example is given to explain the function of these two functions . 1. ...

  7. Django Of select_related and prefetch_related Function pair QuerySet Query optimization

    introduction If there are foreign keys in the database , Use select_related() and prefetch_related() Greatly reduce the number of requests to the database to improve performance 1. Example preparation Model : from django.d ...

  8. turn Examples explain DJANGO Of SELECT_RELATED and PREFETCH_RELATED Function pair QUERYSET Query optimization ( Two )

    https://blog.csdn.net/cugbabybear/article/details/38342793 This is the second in this series , The content is prefetch_related() Purpose of function . Realization ...

  9. Django Of select_related and prefetch_related Function pair QuerySet Query optimization ( 3、 ... and )

    4. Some examples If we want to get all the people whose hometown is Hubei , The most brainless way is to get Hubei Province first , Get all the cities in Hubei , The people who finally got their hometown were the people in this city . Just like this. : 1 2 3 4 5 >>> hb = Pr ...

Random recommendation

  1. lvs DR In mode vip The port for cannot telnet

    Error introduction : stay lvs Add a new one to the configuration file vip  , And add a virtual_server, Add two realserver, Configuration files are copied , It turned out to be ok , The effect was vip The port of telnet No. too ...

  2. Macbo for Mac (40)- Use AFN Send microblogs with pictures

    DJComposeViewController.m /** Micro-blog */ - (void)sendStatusRequest { AFHTTPSessionManager *RequestManage ...

  3. C++ The encoding of characters and numbers (Encode) And decoding (Decode)

    In daily application , We often use structs or classes to store a piece of information , It's very convenient , But it's not good for data transmission . For example, in network programming , We need to convert the data in the structure into a byte stream for transmission , We can use memcpy Force structured data into ...

  4. Compatible with mainstream browsers js Native function encapsulation

    1. Get and modify the scrolling distance of the vertical scroll bar // Get the scroll bar scroll distance function getScrollTop(){ var fromTop=document.documentElement.scrollTop ...

  5. Please don't be in JDK7 And above Json-lib 了

    [Json-lib Introduce ] Json-lib It was before Java A common one Json library , The final version is 2.4, Respectively provided JDK 1.3 and 1.5 Support for , The last update time is 2010 year 12 month 14 Japan . ...

  6. Data structure and algorithm PHP Sorting algorithm ( Bubble sort )

    One . The basic idea Bubble sorting algorithm is to repeatedly visit the sequence to be sorted , Compare two adjacent elements at a time , If they're in the reverse order of the sort requirements , Just swap them , Until there are no more numbers to exchange , The sorting is complete .   Two . The algorithm process 1) Compare two adjacent ...

  7. Oracle Network configuration and management

    [ Learning goals ] Oracle The listener is a server-side program , Used to listen for all requests from clients , And give it numbers Database services . Therefore, the management and maintenance of the monitor is very important .         The main content of this chapter is to describe Oracle Monitor ...

  8. PAT Rational Sum

    Rational Sum (20) The time limit  1000 ms  Memory limit  65536 KB  Code length limit  100 KB  Judgement procedure  Standard ( come from   Little ) Title Description Given N ration ...

  9. Java: The number - String conversion (String turn Double)

    String ss = "3.141592653"; double value = Double.valueOf(ss.toString());

  10. Android Permission information

    Access registration properties android.permission.ACCESS_CHECKIN_PROPERTIES , Read or write registration check-in Database property table permissions Get the wrong position android.permiss ...