饮冰三年-人工智能-Python-48 Django ORM 性能优化

这是饮冰三年-人工智能-Python-24 Django ORM增删改查的进阶篇

一、准备 

1：搭建好django环境，通过模型生成数据库，

from django.db import models

class Book(models.Model):
    name=models.CharField(max_length=20)
    price = models.IntegerField()
    pub_date=models.DateField()
    # 创建一对多的方法
    publish = models.ForeignKey("Publish",on_delete=models.CASCADE)
    # 创建多对多的方法
    authors=models.ManyToManyField("Author")
class Publish(models.Model):
    name = models.CharField(max_length=32)
    city = models.CharField(max_length=32)

class Author(models.Model):
    name=models.CharField(max_length=32)
    age=models.IntegerField()

2：初始化数据库

delete from app01_publish where 1=1;
update sqlite_sequence set seq=0 where name='app01_publish';
insert into app01_publish(name,city)values ('清华大学出版社','北京');
insert into app01_publish(name,city)values ('北京大学出版社','北京');
insert into app01_publish(name,city)values ('上海商务出版社','上海');
select * from app01_publish;

delete from app01_book where 1=1;
update sqlite_sequence set seq=0 where name='app01_book';
insert into app01_book (name,price,pub_date,publish_id)values ('天龙八部',200,'2000-01-01',3);
insert into app01_book (name,price,pub_date,publish_id)values ('神雕侠侣',150,'2011-01-01',3);
insert into app01_book (name,price,pub_date,publish_id)values ('Python入门',100,'2020-01-01',1);
insert into app01_book (name,price,pub_date,publish_id)values ('Python精通',90,'2022-01-01',1);
select * from app01_book;

delete from app01_author where 1=1;
update sqlite_sequence set seq=0 where name='app01_author';
insert into app01_author (name,age)values ('金庸',93);
insert into app01_author (name,age)values ('倪匡',83);
insert into app01_author (name,age)values ('武沛齐',83);
insert into app01_author (name,age)values ('袁浩',83);
select * from app01_author;

delete from app01_book_authors where 1=1;
update sqlite_sequence set seq=0 where name='app01_book_authors';
--《天龙八部》作者：金庸、倪匡
--《神雕侠侣》作者：金庸、
--《Python入门》作者：武沛齐、
--《Python精通》作者：袁浩、武沛齐
insert into app01_book_authors (book_id,author_id)values (1,1);
insert into app01_book_authors (book_id,author_id)values (1,2);
insert into app01_book_authors (book_id,author_id)values (2,1);
insert into app01_book_authors (book_id,author_id)values (3,3);
insert into app01_book_authors (book_id,author_id)values (4,3);
insert into app01_book_authors (book_id,author_id)values (4,4);
select * from app01_book_authors;

3：设置数据库日志记录

二、select_related()

对于一对一字段（OneToOneField）和外键字段（ForeignKey），可以使用select_related 来对QuerySet进行优化

场景一：获取每本书的名称和出版社

def getbook_publish():
    '''获取=书籍的出版社'''
    books = Book.objects.all()
    for book in books:
        print(book.name, book.publish.name)

[2020-07-26 17:01:39,484] (0.000) SELECT "app01_book"."id", "app01_book"."name", "app01_book"."price", "app01_book"."pub_date", "app01_book"."publish_id" FROM "app01_book"; args=()
[2020-07-26 17:01:39,485] (0.000) SELECT "app01_publish"."id", "app01_publish"."name", "app01_publish"."city" FROM "app01_publish" WHERE "app01_publish"."id" = 3 LIMIT 21; args=(3,)
天龙八部 上海商务出版社
[2020-07-26 17:01:39,486] (0.000) SELECT "app01_publish"."id", "app01_publish"."name", "app01_publish"."city" FROM "app01_publish" WHERE "app01_publish"."id" = 3 LIMIT 21; args=(3,)
神雕侠侣 上海商务出版社
[2020-07-26 17:01:39,486] (0.000) SELECT "app01_publish"."id", "app01_publish"."name", "app01_publish"."city" FROM "app01_publish" WHERE "app01_publish"."id" = 1 LIMIT 21; args=(1,)
Python入门 清华大学出版社
[2020-07-26 17:01:39,487] (0.000) SELECT "app01_publish"."id", "app01_publish"."name", "app01_publish"."city" FROM "app01_publish" WHERE "app01_publish"."id" = 1 LIMIT 21; args=(1,)
Python精通 清华大学出版社

优化后

def getbook_publish2():
    '''获取=书籍的出版社'''
    books = Book.objects.select_related("publish").all()
    for book in books:
        print(book.name, book.publish.name)


getbook_publish2()

[2020-07-26 17:35:50,650] (0.000) SELECT "app01_book"."id", "app01_book"."name", "app01_book"."price", "app01_book"."pub_date", "app01_book"."publish_id", "app01_publish"."id", "app01_publish"."name", "app01_publish"."city" FROM "app01_book" INNER JOIN "app01_publish" ON ("app01_book"."publish_id" = "app01_publish"."id"); args=()
天龙八部 上海商务出版社
神雕侠侣 上海商务出版社
Python入门 清华大学出版社
Python精通 清华大学出版社

 

二、prefetch_related()

对于多对多字段（ManyToManyField）和一对多字段，可以使用prefetch_related()来进行优化。

场景一：获取每个出版社出版的书（一对多）

def get_publish_book():
    '''获取每个出版社出版的数据名称（性能较差）'''
    publishs = Publish.objects.all()
    for publish in publishs:
        for book in publish.book_set.all():
            print(publish.name, book.name)


get_publish_book()

'''
[2020-07-27 19:31:59,218] (0.000) SELECT "app01_publish"."id", "app01_publish"."name", "app01_publish"."city" FROM "app01_publish"; args=()
[2020-07-27 19:31:59,220] (0.000) SELECT "app01_book"."id", "app01_book"."name", "app01_book"."price", "app01_book"."pub_date", "app01_book"."publish_id" FROM "app01_book" WHERE "app01_book"."publish_id" = 1; args=(1,)
清华大学出版社 Python入门
清华大学出版社 Python精通
[2020-07-27 19:31:59,221] (0.000) SELECT "app01_book"."id", "app01_book"."name", "app01_book"."price", "app01_book"."pub_date", "app01_book"."publish_id" FROM "app01_book" WHERE "app01_book"."publish_id" = 2; args=(2,)
[2020-07-27 19:31:59,222] (0.000) SELECT "app01_book"."id", "app01_book"."name", "app01_book"."price", "app01_book"."pub_date", "app01_book"."publish_id" FROM "app01_book" WHERE "app01_book"."publish_id" = 3; args=(3,)
上海商务出版社 天龙八部
上海商务出版社 神雕侠侣
'''

优化后

def get_publish_book2():
    '''获取每个出版社出版的数据名称（性能较好）'''
    publishs = Publish.objects.prefetch_related('book_set')
    for publish in publishs:
        for book in publish.book_set.all():
            print(publish.name, book.name)


get_publish_book2()

'''
[2020-07-27 19:39:39,342] (0.000) SELECT "app01_publish"."id", "app01_publish"."name", "app01_publish"."city" FROM "app01_publish"; args=()
[2020-07-27 19:39:39,343] (0.000) SELECT "app01_book"."id", "app01_book"."name", "app01_book"."price", "app01_book"."pub_date", "app01_book"."publish_id" FROM "app01_book" WHERE "app01_book"."publish_id" IN (1, 2, 3); args=(1, 2, 3)
清华大学出版社 Python入门
清华大学出版社 Python精通
上海商务出版社 天龙八部
上海商务出版社 神雕侠侣
'''

 场景二：获取每个出版社出版的书（多对多）

def get_author_book():
    '''获取每个作者出版的书（性能较差）'''
    authors = Author.objects.all()
    for author in authors:
        for book in author.book_set.all():
            print(author.name, book.name)


get_author_book()

'''
[2020-07-27 19:53:17,837] (0.000) SELECT "app01_author"."id", "app01_author"."name", "app01_author"."age" FROM "app01_author"; args=()
[2020-07-27 19:53:17,838] (0.000) SELECT "app01_book"."id", "app01_book"."name", "app01_book"."price", "app01_book"."pub_date", "app01_book"."publish_id" FROM "app01_book" INNER JOIN "app01_book_authors" ON ("app01_book"."id" = "app01_book_authors"."book_id") WHERE "app01_book_authors"."author_id" = 1; args=(1,)
金庸 天龙八部
金庸 神雕侠侣
[2020-07-27 19:53:17,839] (0.000) SELECT "app01_book"."id", "app01_book"."name", "app01_book"."price", "app01_book"."pub_date", "app01_book"."publish_id" FROM "app01_book" INNER JOIN "app01_book_authors" ON ("app01_book"."id" = "app01_book_authors"."book_id") WHERE "app01_book_authors"."author_id" = 2; args=(2,)
倪匡 天龙八部
[2020-07-27 19:53:17,840] (0.000) SELECT "app01_book"."id", "app01_book"."name", "app01_book"."price", "app01_book"."pub_date", "app01_book"."publish_id" FROM "app01_book" INNER JOIN "app01_book_authors" ON ("app01_book"."id" = "app01_book_authors"."book_id") WHERE "app01_book_authors"."author_id" = 3; args=(3,)
武沛齐 Python入门
武沛齐 Python精通
[2020-07-27 19:53:17,840] (0.000) SELECT "app01_book"."id", "app01_book"."name", "app01_book"."price", "app01_book"."pub_date", "app01_book"."publish_id" FROM "app01_book" INNER JOIN "app01_book_authors" ON ("app01_book"."id" = "app01_book_authors"."book_id") WHERE "app01_book_authors"."author_id" = 4; args=(4,)
袁浩 Python精通
'''

def get_author_book2():
    '''获取每个作者出版的书（性能较好）'''
    authors = Author.objects.prefetch_related('book_set')
    for author in authors:
        for book in author.book_set.all():
            print(author.name, book.name)


get_author_book2()

'''
[2020-07-27 19:52:09,036] (0.000) SELECT "app01_author"."id", "app01_author"."name", "app01_author"."age" FROM "app01_author"; args=()
[2020-07-27 19:52:09,038] (0.000) SELECT ("app01_book_authors"."author_id") AS "_prefetch_related_val_author_id", "app01_book"."id", "app01_book"."name", "app01_book"."price", "app01_book"."pub_date", "app01_book"."publish_id" FROM "app01_book" INNER JOIN "app01_book_authors" ON ("app01_book"."id" = "app01_book_authors"."book_id") WHERE "app01_book_authors"."author_id" IN (1, 2, 3, 4); args=(1, 2, 3, 4)
金庸 天龙八部
金庸 神雕侠侣
倪匡 天龙八部
武沛齐 Python入门
武沛齐 Python精通
袁浩 Python精通
'''

三、prefetch_related()和select_related()

场景一：获取每个作者合作的出版社

def get_author_publish():
    '''获取每个作者合作的出版社（性能较差）'''
    authors = Author.objects.all()
    for author in authors:
        for book in author.book_set.all():
            print(author.name, book.name, book.publish.name)


get_author_publish()

'''
[2020-07-27 20:10:40,919] (0.000) SELECT "app01_author"."id", "app01_author"."name", "app01_author"."age" FROM "app01_author"; args=()
[2020-07-27 20:10:40,920] (0.000) SELECT "app01_book"."id", "app01_book"."name", "app01_book"."price", "app01_book"."pub_date", "app01_book"."publish_id" 
                                  FROM "app01_book" INNER JOIN "app01_book_authors" ON ("app01_book"."id" = "app01_book_authors"."book_id")
                                  WHERE "app01_book_authors"."author_id" = 1; args=(1,)
[2020-07-27 20:10:40,921] (0.000) SELECT "app01_publish"."id", "app01_publish"."name", "app01_publish"."city" FROM "app01_publish" WHERE "app01_publish"."id" = 3 LIMIT 21; args=(3,)
金庸 天龙八部 上海商务出版社
[2020-07-27 20:10:40,922] (0.000) SELECT "app01_publish"."id", "app01_publish"."name", "app01_publish"."city" FROM "app01_publish" WHERE "app01_publish"."id" = 2 LIMIT 21; args=(2,)
金庸 神雕侠侣 北京大学出版社
[2020-07-27 20:10:40,922] (0.000) SELECT "app01_book"."id", "app01_book"."name", "app01_book"."price", "app01_book"."pub_date", "app01_book"."publish_id" 
                                  FROM "app01_book" INNER JOIN "app01_book_authors" ON ("app01_book"."id" = "app01_book_authors"."book_id") 
                                  WHERE "app01_book_authors"."author_id" = 2; args=(2,)
[2020-07-27 20:10:40,923] (0.000) SELECT "app01_publish"."id", "app01_publish"."name", "app01_publish"."city" FROM "app01_publish" WHERE "app01_publish"."id" = 3 LIMIT 21; args=(3,)
倪匡 天龙八部 上海商务出版社
[2020-07-27 20:10:40,924] (0.000) SELECT "app01_book"."id", "app01_book"."name", "app01_book"."price", "app01_book"."pub_date", "app01_book"."publish_id" 
                                  FROM "app01_book" INNER JOIN "app01_book_authors" ON ("app01_book"."id" = "app01_book_authors"."book_id") 
                                  WHERE "app01_book_authors"."author_id" = 3; args=(3,)
                                  
[2020-07-27 20:10:40,925] (0.000) SELECT "app01_publish"."id", "app01_publish"."name", "app01_publish"."city" 
                                  FROM "app01_publish" WHERE "app01_publish"."id" = 1 LIMIT 21; args=(1,)
武沛齐 Python入门 清华大学出版社
[2020-07-27 20:10:40,925] (0.000) SELECT "app01_publish"."id", "app01_publish"."name", "app01_publish"."city" 
                                  FROM "app01_publish" WHERE "app01_publish"."id" = 1 LIMIT 21; args=(1,)
武沛齐 Python精通 清华大学出版社
[2020-07-27 20:10:40,926] (0.000) SELECT "app01_book"."id", "app01_book"."name", "app01_book"."price", "app01_book"."pub_date", "app01_book"."publish_id" 
                                  FROM "app01_book" INNER JOIN "app01_book_authors" ON ("app01_book"."id" = "app01_book_authors"."book_id") 
                                  WHERE "app01_book_authors"."author_id" = 4; args=(4,)
[2020-07-27 20:10:40,927] (0.000) SELECT "app01_publish"."id", "app01_publish"."name", "app01_publish"."city" FROM "app01_publish" WHERE "app01_publish"."id" = 1 LIMIT 21; args=(1,)
袁浩 Python精通 清华大学出版社
ps:
    1:先根据作者表和书籍表的关系拿到，该作者撰写的所有出版社id，循环这些书籍对应的出版社id    
'''

优化后

def get_author_publish3():
    '''获取每个作者合作的出版社（性能一般）'''
    authors = Author.objects.prefetch_related("book_set")
    for author in authors:
        for book in author.book_set.select_related("publish"):
            print(author.name, book.name, book.publish.name)


get_author_publish3()

'''
[2020-07-27 20:27:04,048] (0.000) SELECT "app01_author"."id", "app01_author"."name", "app01_author"."age" FROM "app01_author"; args=()
[2020-07-27 20:27:04,049] (0.000) SELECT ("app01_book_authors"."author_id") AS "_prefetch_related_val_author_id", "app01_book"."id", "app01_book"."name", "app01_book"."price", "app01_book"."pub_date", "app01_book"."publish_id" FROM "app01_book" INNER JOIN "app01_book_authors" ON ("app01_book"."id" = "app01_book_authors"."book_id") WHERE "app01_book_authors"."author_id" IN (1, 2, 3, 4); args=(1, 2, 3, 4)
[2020-07-27 20:27:04,051] (0.000) SELECT "app01_book"."id", "app01_book"."name", "app01_book"."price", "app01_book"."pub_date", "app01_book"."publish_id", "app01_publish"."id", "app01_publish"."name", "app01_publish"."city" FROM "app01_book" INNER JOIN "app01_book_authors" ON ("app01_book"."id" = "app01_book_authors"."book_id") INNER JOIN "app01_publish" ON ("app01_book"."publish_id" = "app01_publish"."id") WHERE "app01_book_authors"."author_id" = 1; args=(1,)
金庸 天龙八部 上海商务出版社
金庸 神雕侠侣 北京大学出版社
[2020-07-27 20:27:04,051] (0.000) SELECT "app01_book"."id", "app01_book"."name", "app01_book"."price", "app01_book"."pub_date", "app01_book"."publish_id", "app01_publish"."id", "app01_publish"."name", "app01_publish"."city" FROM "app01_book" INNER JOIN "app01_book_authors" ON ("app01_book"."id" = "app01_book_authors"."book_id") INNER JOIN "app01_publish" ON ("app01_book"."publish_id" = "app01_publish"."id") WHERE "app01_book_authors"."author_id" = 2; args=(2,)
倪匡 天龙八部 上海商务出版社
[2020-07-27 20:27:04,052] (0.000) SELECT "app01_book"."id", "app01_book"."name", "app01_book"."price", "app01_book"."pub_date", "app01_book"."publish_id", "app01_publish"."id", "app01_publish"."name", "app01_publish"."city" FROM "app01_book" INNER JOIN "app01_book_authors" ON ("app01_book"."id" = "app01_book_authors"."book_id") INNER JOIN "app01_publish" ON ("app01_book"."publish_id" = "app01_publish"."id") WHERE "app01_book_authors"."author_id" = 3; args=(3,)
武沛齐 Python入门 清华大学出版社
武沛齐 Python精通 清华大学出版社
'''