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
The main idea is ‘ When we look up a data item in the table , We Handy Connect the relational data items in other tables connected to this data item , Find it together , So when we operate on this data item , You can directly operate just Handy The relational data items , Instead of going to the database again ’
select_related() For one-on-one OneToOneField and For one more （ It's the foreign key ）ForeignKey
prefetch_related() Apply to Many to many ManyToManyField and One to many （ I call it The reverse foreign key , It's the one in the foreign key table related_name The value of the inside ）
class Pic(models.Model):
xx = models.CharField(max_length=30)
album = models.ForeignKey(to=Album, on_delete=models.CASCADE, related_name='pic')
// Like the one below prefetch_related('tags', 'pic') Medium ‘pic'
album = Album.objects.prefetch_related('tags', 'pic').get(pk=id)
// But if from Pic In terms of , Connect album Words , Just use select_related()
pic = Pic.objects.select_related('album').get(pk=id)
The following is a copy ：
https://www.cnblogs.com/mdzzbojo/p/9216122.html
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 ：
Copy code
from django.db import models
class Book(models.Model):
title = models.CharField(max_length=32)
publish_date = models.DateField(auto_now_add=True)
price = models.DecimalField(max_digits=5, decimal_places=2)
memo = models.TextField(null=True)
# Create foreign keys , relation publish
publisher = models.ForeignKey(to=“Publisher”, on_delete=models.CASCADE, related_name=‘books’)
# Create many to many associations author
author = models.ManyToManyField(to=“Author”)
def __str__(self):
return self.title
class Publisher(models.Model):
name = models.CharField(max_length=32)
city = models.CharField(max_length=32)
def __str__(self):
return self.name
class Author(models.Model):
name = models.CharField(max_length=32)
age = models.IntegerField()
phone = models.CharField(max_length=11)
detail = models.OneToOneField(to=“AuthorDetail”, on_delete=models.CASCADE)
def __str__(self):
return self.name
class AuthorDetail(models.Model):
addr = models.CharField(max_length=64)
email = models.EmailField()
Copy code
1.select_related()
For one-to-one fields （OneToOneField） And foreign key fields （ForeignKey） have access to select_releated To optimize QuerySet Inquire about
Example ：
1） When the method is not called , Check all books and publishing houses
book_obj = models.Book.objects.all()
for book in book_obj:
print(book.publisher)
Output log information
Copy code
(0.000) SELECT @@SQL_AUTO_IS_NULL; args=None
(0.000) SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; args=None
(0.001) SELECT app_book.id, app_book.title, app_book.publish_date, app_book.price, app_book.memo, app_book.publisher_id FROM app_book; args=()
Southern media press
Changsha TV press
Changsha TV press
tsinghua university press
People's post and Telecommunications Press
(0.001) SELECT VERSION(); args=None
Southern media press
(0.001) SELECT app_publisher.id, app_publisher.name, app_publisher.city FROM app_publisher WHERE app_publisher.id = 1; args=(1,)
(0.001) SELECT app_publisher.id, app_publisher.name, app_publisher.city FROM app_publisher WHERE app_publisher.id = 2; args=(2,)
(0.000) SELECT app_publisher.id, app_publisher.name, app_publisher.city FROM app_publisher WHERE app_publisher.id = 2; args=(2,)
(0.000) SELECT app_publisher.id, app_publisher.name, app_publisher.city FROM app_publisher WHERE app_publisher.id = 4; args=(4,)
(0.001) SELECT app_publisher.id, app_publisher.name, app_publisher.city FROM app_publisher WHERE app_publisher.id = 3; args=(3,)
(0.000) SELECT app_publisher.id, app_publisher.name, app_publisher.city FROM app_publisher WHERE app_publisher.id = 1; args=(1,)
Process finished with exit code 0
Copy code
2） When the method is called , Check all books and publishing houses
book_obj = models.Book.objects.select_related().all()
for book in book_obj:
print(book.publisher)
Output log information
Copy code
(0.001) SELECT @@SQL_AUTO_IS_NULL; args=None
(0.001) SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; args=None
(0.001) SELECT app_book.id, app_book.title, app_book.publish_date,app_book.price, app_book.memo, app_book.publisher_id, app_publisher.id,app_publisher.name, app_publisher.city FROM app_book INNER JOIN app_publisher
ON (app_book.publisher_id = app_publisher.id); args=()
Southern media press
Changsha TV press
Changsha TV press
tsinghua university press
People's post and Telecommunications Press
Southern media press
Process finished with exit code 0
Copy code
Conclusion ：
In the face of QuerySet Use select_related() Function before , There will be linear SQL Inquire about , If there is n A query object , Every object exists k Foreign key fields , Will appear n*k + 1 Time SQL Inquire about
In the face of QuerySet Use select_related() After the function ,Django The object corresponding to the corresponding foreign key will be obtained during the first query , So you don't have to query the database when you need it later , Only one query is needed
3） Related parameters
*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 keys, foreign keys, foreign keys, etc , To select a foreign key for a foreign key, you need to use two underscores “__” To connect
Example ： Get the detailed address of the publishing house of the book through the foreign key
obj = models.Book.objects.select_related(‘publisher__detail’).get(title=‘ Wulin legend ’)
print(obj.publisher.detail)
print(obj.publisher)
Output log information
Copy code
(0.000) SELECT @@SQL_AUTO_IS_NULL; args=None
(0.000) SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; args=None
(0.000) SELECT VERSION(); args=None
(0.000) SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; args=None
(0.001) SELECT app_book.id, app_book.title, app_book.publish_date, app_book.price, app_book.memo, app_book.publisher_id, app_publisher.id, app_publisher.name, app_publisher.city, app_publisher.detail_id, app_publisherdetail.id, app_publisherdetail.addr, app_publisherdetail.create_date FROM app_book INNER JOIN app_publisher ON (app_book.publisher_id = app_publisher.id) INNER JOIN app_publisherdetail ON (app_publisher.detail_id = app_publisherdetail.id) WHERE app_book.title = ‘ Wulin legend ’; args=(‘ Wulin legend ’,)
Changsha South District
Changsha TV press
Copy code
Conclusion ：
Django Used 2 Time INNER JOIN To complete the request , To obtain the publisher Table and publisherdetail Table and add to the corresponding columns of the result table , There is no need to call these two objects again SQL Inquire about ; On the contrary, if it is an unspecified foreign key, it will SQL Inquire about , And deeper , More queries
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
No parameter ,select_related() You can also add no parameters , It's a request Django As deep as possible select_related
summary ：
Copy code
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
Copy code
2.prefetch_releated()
For many to many fields （ManyToManyField） And one to many (ForeignKey) Field , You can use prefetch_related() To optimize , It and select_releated The design purpose of is similar , It's all about reducing SQL Number of queries , But the implementation is different , The latter is through JOIN Statements in SQL Solve the problem within the query . But because of JOIN The statement is too long , Not suitable for solving many to many relationships , It can lead to SQL The increase of statement running time and memory consumption
prefetch_releated The solution is to query each table separately , And then use Python Deal with their relationship
Example ：
book_obj = models.Book.objects.prefetch_related(‘publisher’).get(title=‘ Seven Heroes and Five Gallants ’)
print(book_obj.publisher.detail)
Output log information
Copy code
(0.000) SELECT @@SQL_AUTO_IS_NULL; args=None
(0.001) SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; args=None
(0.000) SELECT VERSION(); args=None
(0.000) SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; args=None
(0.000) SELECT app_book.id, app_book.title, app_book.publish_date, app_book.price, app_book.memo, app_book.publisher_id FROM app_book WHERE app_book.title = ‘ Seven Heroes and Five Gallants ’; args=(‘ Seven Heroes and Five Gallants ’,)
(0.000) SELECT app_publisher.id, app_publisher.name, app_publisher.city, app_publisher.detail_id FROM app_publisher WHERE app_publisher.id IN (1); args=(1,)
(0.001) SELECT app_publisherdetail.id, app_publisherdetail.addr, app_publisherdetail.create_date FROM app_publisherdetail WHERE app_publisherdetail.id = 1; args=(1,)
Tianhe District, Guangzhou
Process finished with exit code 0
Copy code
Conclusion ：
It can be seen that , The first query statement gets the object to be operated , The key is that the second item gets the condition of the query result, that is ID, The third line is obtained through the second line ID Query results
We can see from the second query statement , 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
*lookups Parameters ,prefetch_related() stay Django < 1.7 There is only one usage . and select_related() equally ,prefetch_related() Deep query is also supported
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
summary ：
Copy code
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
Copy code
inductive ：
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. Can be in a 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
