Django Query Database performance optimization

This is a creation in Article, where the information may have evolved or changed.

Now there is a data sheet that records the user's information UserInfo , which records the names of 10 users, say, age, work, etc.

Models file

    from django.db import models        class Job(models.Model):        title=models.CharField(max_length=32)        class UserInfo(models.Model):        username=models.CharField(max_length=32)        nickname=models.CharField(max_length=32)        job=models.ForeignKey(to="Job",to_field="id",null=True)

The Data table records:

Another data table records the job table of the user's work and associates the user's work fields.

To find out the user name of each user, what is called and work

    def index(request):        user_list=models.UserInfo.objects.all()            print(user_list.query)      # 打印查询时使用的语句        print(type(user_list))      # 打印查询结果的数据类型            for user in user_list:                print("%s-->%s-->%s" %(user.username,user.nickname,user.job.title))            return render(request,'index.html')

Printing information:

SELECT "app01_userinfo"."id", "app01_userinfo"."username", "app01_userinfo"."nickname", "app01_userinfo"."job_id" FROM "app01_userinfo"<class 'django.db.models.query.QuerySet'>user1-->user1-->pythonuser2-->user2-->linuxuser3-->user3-->golanguser4-->user4-->pythonuser5-->user5-->linuxuser6-->user6-->golanguser7-->user7-->pythonuser8-->user8-->linuxuser9-->user9-->golanguser10-->user10-->linux

On the server side of these operations, these query statements performance is very low, traverse out the names of these 10 users, say, work and other information to perform 11 queries in two databases.

First, all user records are identified from the UserInfo table, and a query operation is required.

Query the Job data table, each cycle of user information list, you need to query from the job table once the user's work information.

A total of 10 user records are recorded in the data table, so it is also necessary to cycle 10 times to complete the job information for all users from the job table. So there are 11 database query operations that need to be performed.

Is there any good way to improve the efficiency of database queries???

    def index(request):        user_list=models.UserInfo.objects.values("username","nickname","job")            print(user_list.query)      # 打印查询时使用的语句        print(type(user_list))      # 打印查询结果的数据类型        print("user_list:",user_list)            for user in user_list:                print(user["username"], user["nickname"], user["job"])            return render(request,'index.html')

To run the program, print the information in the server backend:

SELECT "app01_userinfo"."username", "app01_userinfo"."nickname", "app01_userinfo"."job_id" FROM "app01_userinfo"<class 'django.db.models.query.QuerySet'>user_list: <QuerySet [{'username': 'user1', 'nickname': 'user1', 'job': 1}, {'username': 'user2', 'nickname': 'user2', 'job': 2}, {'username': 'user3', 'nickname': 'user3', 'job': 3}, {'username': 'user4', 'nickname': 'user4', 'job': 1}, {'username': 'user5', 'nickname': 'user5', 'job': 2}, {'username': 'user6', 'nickname': 'user6', 'job': 3}, {'username': 'user7', 'nickname': 'user7', 'job': 1}, {'username': 'user8', 'nickname': 'user8', 'job': 2}, {'username': 'user9', 'nickname': 'user9', 'job': 3}, {'username': 'user10', 'nickname': 'user10', 'job': 2}]>user1 user1 1user2 user2 2user3 user3 3user4 user4 1user5 user5 2user6 user6 3user7 user7 1user8 user8 2user9 user9 3user10 user10 2

As you can see, the result of the query is still a user_list QuerySet , but inside this object collection is a dictionary.

And this time the query only performed two database query operations.

In this way, only two queries are needed to obtain the desired data, optimizing the query efficiency of the database.

Select_related Active Table query for Django Database optimization operations

In the above example, the object collection can only query the current data table, can not query other data tables??

Of course not, this method can also be used here select_related .

At the time of the first query, add one after all () select_related to make the active table query.

When creating these two data tables, the job is in the UserInfo data table as one ForeignKey exists, so add select_related not only to the UserInfo database records, but also query the job data table records.

    def index(request):        user_list=models.UserInfo.objects.all().select_related("job")            print(user_list.query)      # 打印查询时使用的语句        print(type(user_list))      # 打印查询结果的数据类型        print("user_list:",user_list)            for user in user_list:                print("%s-->%s-->%s" %(user.username,user.nickname,user.job.title))            return render(request,'index.html')

Server-side Print results

  Select "App01_userinfo". " ID "," app01_userinfo "." Username "," app01_userinfo "." Nickname "," App01_userinfo "." job_id "," App01_job "." ID "," app01_job "." Title ' from ' App01_userinfo ' left OUTER JOIN ' app01_job ' on (' App01_userinfo '. job_id "=" app01_job "." ID ") <class ' Django.db.models.query.QuerySet ' >user_list: <queryset [<userinfo:userinfo object>, < Userinfo:userinfo object>, <userinfo:userinfo object>, <userinfo:userinfo Object>, <UserInfo: UserInfo object>, <userinfo:userinfo object>, <userinfo:userinfo object>, <userinfo:userinfo object Object>, <userinfo:userinfo, <userinfo:userinfo object>]>user1-->user1-->pythonuser2-- >user2-->linuxuser3-->user3-->golanguser4-->user4-->pythonuser5-->user5-->linuxuser6-- >user6-->golanguser7-->user7-->pythonuser8-->user8-->linuxuser9-->user9--> Golanguser10-->user10-->linux  

Look at the printed query statement, which is "FROM "app01_userinfo" LEFT OUTER JOIN "app01_job" ON ("app01_userinfo"."job_id" = "app01_job"."id")" used to do the query of the table, only need to query all the data once.

Similarly, if you want to continue with the table, for example, in the job table with a foreign key field desc, you just need to add DESC in the query statement.

    user_list=models.UserInfo.objects.all().select_related("job__desc")

In this way, three tables are linked together for query, but be sure to add the fields ForeignKey .

If you use models.UserInfo.objects.all() a similar statement to query, do not make a cross-table query, query only the current table of data, or the performance of the query statement will be much lower.

If you want to check the data in other tables, add it select_related(ForeignKey字段名) ;

If you want to take ForeignKey data from multiple fields, you can use theselect_related(ForeignKey字段1,ForeignKey字段2,...)

The performance of the query operation is also reduced, which select_related is used to do the active table query.

perfetch_related Non-active table query of Django database optimization operation

perfetch_relatedMethod is a tradeoff between non-active table queries and not many query statements

Modify View Function Index

    def index(request):            user_list=models.UserInfo.objects.all().prefetch_related("job")            print(user_list.query)      # 打印查询时使用的语句        print(type(user_list))      # 打印查询结果的数据类型        print("user_list:",user_list)            for user in user_list:                print("%s-->%s-->%s" %(user.username,user.nickname,user.job.title))            return render(request,'index.html')

Back-End printing results

SELECT "app01_userinfo"."id", "app01_userinfo"."username", "app01_userinfo"."nickname", "app01_userinfo"."job_id" FROM "app01_userinfo"<class 'django.db.models.query.QuerySet'>user_list: <QuerySet [<UserInfo: UserInfo object>, <UserInfo: UserInfo object>, <UserInfo: UserInfo object>, <UserInfo: UserInfo object>, <UserInfo: UserInfo object>, <UserInfo: UserInfo object>, <UserInfo: UserInfo object>, <UserInfo: UserInfo object>, <UserInfo: UserInfo object>, <UserInfo: UserInfo object>]>user1-->user1-->pythonuser2-->user2-->linuxuser3-->user3-->golanguser4-->user4-->pythonuser5-->user5-->linuxuser6-->user6-->golanguser7-->user7-->pythonuser8-->user8-->linuxuser9-->user9-->golanguser10-->user10-->linux

Use a prefetch_related method to perform two query operations without a single table

Query all the data in the user table first, all the job_id in the user table are queried, and perform the deduplication operation;

The result is a query of 3 jobs for the user, followed by a "select" statement that queries the "title" field in the "Job" datasheet

This allows you to perform only two query operations on the data table

prefetch_relateda field "job" was added to the method, and two database query operations were performed;

If you add another field, the data will be added again for the action.

The only method of the Django database optimization operation

    def index(request):        user_list=models.UserInfo.objects.all().only("username")            print(user_list.query)      # 打印查询时使用的语句        print(type(user_list))      # 打印查询结果的数据类型        print("user_list:",user_list)            for user in user_list:                print("%s-->%s" %(user.username,user.nickname))            return render(request,'index.html')

Service-Side Spool information

SELECT "app01_userinfo"."id", "app01_userinfo"."username" FROM "app01_userinfo"<class 'django.db.models.query.QuerySet'>user_list: <QuerySet [<UserInfo: UserInfo object>, <UserInfo: UserInfo object>, <UserInfo: UserInfo object>, <UserInfo: UserInfo object>, <UserInfo: UserInfo object>, <UserInfo: UserInfo object>, <UserInfo: UserInfo object>, <UserInfo: UserInfo object>, <UserInfo: UserInfo object>, <UserInfo: UserInfo object>]>user1-->user1user2-->user2user3-->user3user4-->user4user5-->user5user6-->user6user7-->user7user8-->user8user9-->user9user10-->user10

When you execute a query, the query only result is a collection of objects, but you can see from the printed query that only the user's ID field and the Username field are queried when the query is executed, and there is no query for the nickname field.

However, in the back of the loop, you can print the user's nikename information. Why, because the request operation of the query was executed again. As a result, the query operation uses the only method, which query field is added to the only method, and which query field is used later.

The plus only argument is to take only one field from the query result, and the other defer is to exclude a field from the query results

Defer method for optimizing operation of Django Database

Modify the index view function

def index(request):    user_list=models.UserInfo.objects.all().defer("username")    print(user_list.query)      # 打印查询时使用的语句    print(type(user_list))      # 打印查询结果的数据类型    print("user_list:",user_list)    for user in user_list:        print("%s" % user.nickname)    return render(request,'index.html')

Server-side printing information

SELECT "app01_userinfo"."id", "app01_userinfo"."nickname", "app01_userinfo"."job_id" FROM "app01_userinfo"<class 'django.db.models.query.QuerySet'>user_list: <QuerySet [<UserInfo: UserInfo object>, <UserInfo: UserInfo object>, <UserInfo: UserInfo object>, <UserInfo: UserInfo object>, <UserInfo: UserInfo object>, <UserInfo: UserInfo object>, <UserInfo: UserInfo object>, <UserInfo: UserInfo object>, <UserInfo: UserInfo object>, <UserInfo: UserInfo object>]>user1user2user3user4user5user6user7user8user9user10

By printing the query statement, you can know that after using the defer method, only the User ID field and the user's nickname Field Operation are queried from the database, and the username field is not queried, which can also improve the performance of the Django query database.