django ORM查询优化

前言

我们在使用django开发后端接口的时候，经常使用ORM来查询，然后来处理各种各样的数据，下面的一些方法可以提高ORM的查询效率

一、value

创建两张表

class Publish(models.Model):
    name = models.CharField(max_length=32)
    city = models.CharField(max_length=32)
    email = models.EmailField()
    def __str__(self):
        return self.name

# 书籍表
class Book(models.Model):
    title = models.CharField(max_length=32)
    good = models.IntegerField(default=0)  # 点赞
    comment = models.IntegerField(default=0)  # 评论
    publishDate = models.DateField()
    price = models.DecimalField(max_digits=5, decimal_places=2)
    publish = models.ForeignKey(to="Publish", on_delete=models.CASCADE)

使用all查询

def query(request):
AuthorDetail.objects.create(birthday=datetime.now(),telephone='14513256456',addr='北京')
    books = Book.objects.all()
    for book in books:
        print(book.title,book.publish.name)
    return HttpResponse('ok')

打印结果以及打印的sql

 使用values查询

def query(request):
    books = Book.objects.all().values('title','publish__name')
    print(books)
    return HttpResponse('ok')

打印结果

 

上面通过控制台打印可以看到，输出的sql是不一样的，使用all查询明显是多查询了一次，通过values进行了连表操作，效率就高一些

二、select_related

返回一个QuerySet将“遵循”外键关系的，在执行查询时选择其他相关对象数据。这可以提高性能，从而导致单个更复杂的查询，但意味着以后使用外键关系将不需要数据库查询

简单创建两张表

# 作者表
class Author(models.Model):
    name = models.CharField(max_length=32)
    age = models.IntegerField()
    sex = models.CharField(max_length=32, default='male')
    authorDetail = models.OneToOneField(to="AuthorDetail", on_delete=models.CASCADE)

    def __str__(self):
        return self.name

# 作者详细信息表
class AuthorDetail(models.Model):
    birthday = models.DateField()  # 出生日期
    telephone = models.CharField(max_length=32)  # 电话
    addr = models.CharField(max_length=64)  # 地址

    def __str__(self):
        return self.addr

以下示例说明了普通查找和select_related()查找之间的区别 。这是标准查询

一对一

def query(request):
    # 查询id 为1的人的手机号
    author = Author.objects.get(id=1)
    print(author.authorDetail.telephone)
    print('*' * 60)
    author = Author.objects.select_related('authorDetail').get(id=1)
    print(author.authorDetail.telephone)
    return HttpResponse('ok')

结果：

使用了select_related之后，里面的参数写的是关系字段的名称，那么就会先进行JOIN语句操作，通过减少SQL查询的次数来进行优化、提高性能，效率高一些，但是他用在外键或者一对一的关系上。 也接受无参数的调用，Django会尽可能深的递归查询所有的字段。但注意有Django递归的限制和性能的浪费

一对多

创建两张表：

# 出版社表
class Publish(models.Model):
    name = models.CharField(max_length=32)
    city = models.CharField(max_length=32)
    email = models.EmailField()

    def __str__(self):
        return self.name


# 书籍表
class Book(models.Model):
    title = models.CharField(max_length=32)
    good = models.IntegerField(default=0)  # 点赞
    comment = models.IntegerField(default=0)  # 评论
    publishDate = models.DateField()
    price = models.DecimalField(max_digits=5, decimal_places=2)
    publish = models.ForeignKey(to="Publish", on_delete=models.CASCADE)

普通查询

def query(request):
    books = Book.objects.all()
    for book in books:
        print(book.publish.name,book.title)
    return HttpResponse('ok')

结果：

 select_related查询

def query(request):
    books = Book.objects.all().select_related('publish')
    for book in books:
         print(book.publish.name,book.title)
    return HttpResponse('ok')

结果

 小结：
select_related主要针一对一和多对一关系进行优化，产生一次查询，使用SQL的JOIN语句进行优化

三、prefetch_related

这个方法和select_related非常类似，就是在访问多个表中数据的时候，减少查询的次数，这个方法是为了解决多对一和多对多的关系的查询问题

创建两张表

# 书籍表
class Book(models.Model):
    title = models.CharField(max_length=32)
    good = models.IntegerField(default=0)  # 点赞
    comment = models.IntegerField(default=0)  # 评论
    publishDate = models.DateField()
    price = models.DecimalField(max_digits=5, decimal_places=2)
    publish = models.ForeignKey(to="Publish", on_delete=models.CASCADE)
    authors = models.ManyToManyField(to='Author', through='BookToAuthor')

# 作者表
class Author(models.Model):
    name = models.CharField(max_length=32)
    age = models.IntegerField()
    sex = models.CharField(max_length=32, default='male')
    authorDetail = models.OneToOneField(to="AuthorDetail", on_delete=models.CASCADE)

使用prefetch_related 查询
查单个书本的作者：

def query(request):
    books = Book.objects.get(id=1)
    print(books.authors.all())
    print('*' * 50)
    books = Book.objects.prefetch_related('authors').get(id=1)
    print(books.authors.all())
    return HttpResponse('ok')

查所有书本的作者

def query(request):
    books = Book.objects.all()
    for book in books:
        for author in book.authors.all():
            print(author.name)
    print('*' * 50)
    books = Book.objects.prefetch_related('authors','publish').all()
    for book in books:
        for author in book.authors.all():
            print(author.name)
    return HttpResponse('ok')

 后面还可以跟过滤条件：

def query(request):
    books = Book.objects.prefetch_related('authors').filter(authors__age=12)
    for book in books:
        print(book.title)
    return HttpResponse('ok')

 所有书的出版社

def query(request):
    books = Book.objects.prefetch_related('publish')
    for book in books:
        print(book.publish.name)
    return HttpResponse('ok')

注意事项：
在预查询数据中使用filter？

def query(request):
    books = Book.objects.prefetch_related('authors')
    for book in books:
        for author in book.authors.filter(age__gte=10):
            print(author.name)
    return HttpResponse('ok')

看下结果，可以看到产生了多次查询，这肯定是有问题，如果使用了filter，会把之前的预加载给清除掉重新向数据库发送请求，可以使用Prefetch来过滤需要操作的条件：

 Prefetch：

from django.db.models import Prefetch
def query(request):
    books = Book.objects.prefetch_related(
    Prefetch('authors',queryset=Author.objects.filter(age__gte=10)))
    for book in books:
        for author in book.authors.all():
            print(author.name)
    return HttpResponse('ok')

结果

 

四、only和defer

only：只提取某几个字段

def query(request):
    author = Author.objects.all().only('name') # 只提取name字段
    print(author)
    return HttpResponse('ok')

sql

 注意：如果后面你再提取其他字段则会发起数据库请求。id无法操作，下同defer

defer：过滤字段

def query(request):
    author = Author.objects.all().defer('name','age')
    print(author)
    return HttpResponse('ok')

sql

 注意：如果你将字段过滤掉但后续又操作这个字段的话会再次发起数据库请求

 

参考博客：

https://blog.csdn.net/qq_39253370/article/details/108443148

https://blog.csdn.net/PY0312/article/details/103301693