Django 数据库查询优化

对于网站和Web APP来说最影响网站性能的就是数据库查询了，因为反复从数据库读写数据很耗时间和计算资源，而查询返回的数据集非常大时还会占据很多内存。这里从django orm的角度来探索数据库查询的优化。

QuerySet与查询

什么是QuerySet

QuerySet是Django提供的强大的数据库接口(API)。正是因为通过它，我们可以使用filter, exclude, get等方法进行数据库查询，而不需要使用原始的SQL语言与数据库进行交互。从数据库中查询出来的结果一般是一个集合，这个集合叫就做 queryset。

QuerySet是惰性的

当我们使用如filter语句获得queryset，Django的数据接口QuerySet并没有对数据库进行查询，只有在做进一步运算时(如打印查询结果、判断是否存在、计算结果长度)才会执行真正的数据库查询,这个过程就是queryset的执行(evaluation)。这样做的目的是减少对数据库无效的操作。

QeurySet自带缓存

当queryset被执行后，其查询结果会载入内存并保存在queryset内置的cache中。再次使用就不需要重新去查询了

判断查询结果是否存在

if与exists()都可以判断查询结果是否存在，但两者使用却又很大的不相同。if会触发整个queryset的缓存，而exists()只会返回True或False检视查询结果是否存在而不会缓存查询结果。选用哪个办法来判断需要根据实际使用需求来看。

统计查询结果数量

len()与count()方法均能统计查询结果数量，这里也不说哪个更好。count()是从数据库层面直接获取查询结果数量而不需要返回整个queryset数据集一般来说会更快。len()会导致queryset的执行，需要先将整个数据集载入内存方可计算，但如果queryset数据集已经缓存在内存当中了len()则会更快

按需获取数据

当查询到的queryset非常大时，会占用大量的内存，使用values和values_list按需提取数据

注意: values和values_list返回的是字典形式字符串数据，而不是对象集合

使用update更新数据

相比于使用save()方法，update()不需要先缓存整个queryset

使用explain方法分析耗时，优化查询

Blog.objects.filter(title='My Blog').explain(verbose=True)

# output
Seq Scan on public.blog  (cost=0.00..35.50 rows=10 width=12) (actual time=0.004..0.004 rows=10 loops=1)
  Output: id, title
  Filter: (blog.title = 'My Blog'::bpchar)
Planning time: 0.064 ms
Execution time: 0.058 ms

aggregate和annotate

Django的aggregate和annotate方法主要用于组合查询，当我们需要对查询集(queryset)的某些字段进行计算，或先分组在计算或排序就需要用到aggregate和annotate方法了

准备工作

一个模型Student和Hobbit是多对多关系:

class Student(models.Model):

    name = models.CharField(max_length=20)
    age = models.IntegerField()
    hobbies = models.ManyToManyField(Hobby)
    
class Hobby(models.Model):
    name = models.CharField(max_length=20)

aggregate

aggregate方法支持的聚合操作有MIN、MAX、AVG、SUM、COUNT，所以先提前import进来：

from django.db.models import Max, Min, Avg, Sum, Count

一些例子:

# 计算学生平均年龄, 返回字典。age和avg间是双下划线哦
Student.objects.all().aggregate(Avg('age'))
{ 'age__avg': 12 }

# 学生平均年龄，返回字典。all()不是必须的。
Student.objects.aggregate(Avg('age'))
{ 'age__avg: 12' }

# 计算学生总年龄, 返回字典。
Student.objects.aggregate(Sum('age'))
{ 'age__sum': 144 }

# 学生平均年龄, 设置字典的key
Student.objects.aggregate(average_age = Avg('age'))
{ 'average_age': 12 }

# 学生最大年龄，返回字典
Student.objects.aggregate(Max('age'))
{ 'age__max': 12 }

# 同时获取学生年龄均值, 最大值和最小值, 返回字典
Student.objects.aggregate(Avg('age‘), Max('age‘), Min('age‘))
{ 'age__avg': 12, 'age__max': 18, 'age__min': 6, }

# 根据Hobby反查学生最大年龄。查询字段student和age间有双下划线哦。
Hobby.objects.aggregate(Max('student__age'))
{ 'student__age__max': 12 }

annotate

annotate的中文意思是注释，但似乎有点词不达意，更好的理解应该是分组(group by)。如果需要对数据集先进行分组然后再进行某些聚合操作或排序时，就需要用annotate来实现。与aggregate不同的是annotate返回的是一个查询集，该查询集相当于是在原来的基础上多加了一个统计字段

一些例子:

# 按学生分组，统计每个学生的爱好数量
Student.objects.annotate(Count('hobbies'))

# 按学生分组，统计每个学生爱好数量，并自定义字段名
Student.objects.annotate(hobby_count_by_student=Count('hobbies'))

# 按爱好分组，再统计每组学生数量。
Hobby.objects.annotate(Count('student'))

# 按爱好分组，再统计每组学生最大年龄。
Hobby.objects.annotate(Max('student__age'))

annotate与filter联用

有时我们需要对数据集先筛选再分组或先分组再筛选，就可以通过annotate与filter联用来实现

一些例子:

# 先按爱好分组，再统计每组学生数量, 然后筛选出学生数量大于1的爱好。
Hobby.objects.annotate(student_num=Count('student')).filter(student_num__gt=1)

# 先按爱好分组，筛选出以'd'开头的爱好，再统计每组学生数量。
Hobby.objects.filter(name__startswith="d").annotate(student_num=Count('student‘))

annotate与order_by联用

一些例子:

# 先按爱好分组，再统计每组学生数量, 然后按每组学生数量大小对爱好排序。
Hobby.objects.annotate(student_num=Count('student‘)).order_by('student_num')

# 统计最受学生欢迎的5个爱好。
Hobby.objects.annotate(student_num=Count('student‘)).order_by('-student_num')[:5]

annotate与values联用

在前面的例子中分组都是按照对象分组的，如按学生对象分组，同样的也可以通过values按如学生姓名name来分组，如果两个学生具有相同的名字他们的爱好将叠加

# 按学生名字分组，统计每个学生的爱好数量。
Student.objects.values('name').annotate(Count('hobbies'))

你还可以使用values方法从annotate返回的数据集里提取你所需要的字段，如下所示:
# 按学生名字分组，统计每个学生的爱好数量。
Student.objects.annotate(hobby_count=Count('hobbies')).values('name', 'hobby_count')

select_related和prefetch_related

准备工作

文章(Article)与类别(Category)是一对多关系，文章(Article)与标签(Tag)是多对多关系

class Article(models.Model):
    """文章模型"""
    title = models.CharField('标题', max_length=200, db_index=True)
    category = models.ForeignKey('Category', verbose_name='分类', on_delete=models.CASCADE, blank=False, null=False)
    tags = models.ManyToManyField('Tag', verbose_name='标签集合', blank=True)

糟糕的用法

articles = Article.objects.all()
for article in articles:
  print(article.title)
  print(article.category.name)
  for tag in article.tags.all():
    print(tag.name)

当使用Article.objects.all()查询得到的只是Article表的数据，并没有包含Category表和Tag表的数据。因此每一次打印article.category.name和tag.name都会重新去查询一遍Category表和Tag表，造成了很大不必要的浪费

select_related

select_related会根据外键关系(仅限一对一、一对多)，使用inner join来一次性获取主体对象和相关对象的信息，这样在打印article.category.name时就不用去重新查询数据库了

修改article查询语句:

articles = Article.objects.all().select_related('category')

selected_related常用使用案例:

# 获取id=13的文章对象同时，获取其相关category信息
Article.objects.select_related('category').get(id=13)

# 获取id=13的文章对象同时，获取其相关作者名字信息
Article.objects.select_related('author__name').get(id=13)

# 获取id=13的文章对象同时，获取其相关category和相关作者名字信息。下面方法等同
Article.objects.select_related('category', 'author__name').get(id=13)
Article.objects.select_related('category').select_related('author__name').get(id=13)

# 使用select_related()可返回所有相关主键信息,all()非必需
Article.objects.all().select_related()

# 获取Article信息同时获取blog信息,filter方法和selected_related方法顺序不重要
Article.objects.filter(pub_date__gt=timezone.now()).select_related('blog')
Article.objects.select_related('blog').filter(pub_date__gt=timezone.now())

prefetch_related

在多对多关系中不能再使用selectd_related，因为多对多JOIN操作后表会变得非常的大。而prefetch_related就是用来处理这个问题的,prefect_related可用于多对多关系字段，也可用于反向外键关系(related_name)

再次修改article的查询语句:

articles = Article.objects.all().select_related('category').prefecth_related('tags')

prefetch_related常用使用案例:

# 文章列表及每篇文章的tags对象名字信息
Article.objects.all().prefetch_related('tags__name')

# 获取id=13的文章对象同时，获取其相关tags信息
Article.objects.prefetch_related('tags').get(id=13)

用Prefetch方法可以给prefetch_related方法额外添加额外条件和属性
# 获取文章列表及每篇文章相关的名字以P开头的tags对象信息
Article.objects.all().prefetch_related(
    Prefetch('tags', queryset=Tag.objects.filter(name__startswith="P"))
)

# 文章列表及每篇文章的名字以P开头的tags对象信息, 放在article_p_tag列表
Article.objects.all().prefetch_related(
    Prefetch('tags', queryset=Tag.objects.filter(name__startswith="P")), to_attr='article_p_tag'
)

F()函数

F()会生成数据库级别的SQL表达式，直接使用它引用模型字段的值并执行数据库操作，而不用把它导出到Python的内存当中

对比两个例子:

例1
article = Article.objects.get(title='文章2')
article.thumb_count += 1
article.save()

例2 使用F()函数
Article.objects.filter(title='文章1').update(thumb_count=F('thumb_count')+1)

很明显使用F()函数的执行效率会更高，只需要一条sql完全的数据库操作，而例1则需要先查询，缓存，然后再更新

F()函数避免竞争

在Python线程中例1的方法是存在竞态条件的，如第一个线程完成取值、更新值、保存新值，而第二个线程操作还是使用就的值来进行操作，使用F()函数的话，因为是数据库层面的原子操作，第二个线程再来取值那也是取到更新后的值了

F()函数用于查询表达式

例如:

同一数据不同字段比较
article = Article.objects.filter(thumb_count__gt=F('view_count'))

两个操作数都是常数和F()函数的加、减、乘、除、取模、幂计算等算术操作
article = Article.objects.filter(view_count__gt=F('thumb_count') * 2)

配合annotate使用
article = Article.objects.annotate(all_count=F('view_count') + F('thumb_count'))

优化tips

1. 利用 queryset lazy 的特性 去优化代码，尽可能的减少连接数据库的次数
2. 如果查出的 queryset 只用一次，可以使用 iterator() 去来防止占用太多的内存
3. 尽可能把一些数据库层级的工作放到数据库，例如使用 filter/exclude, F, annotate, aggregate
4. 一次性拿出所有你要的数据，不去取那些你不需要的数据
5. 意思就是要巧用select_related()、prefetch_related()和values_list()、values ()例如如果只需要id字段的话，用values_list(‘id’, flat=True)也能节约很多资源。或者使用defer()和only()方法：不加载某个字段(用到这个方法就要反思表设计的问题了)只加载某些字段.
6. 如果不用select_related的话，去取外键的属性就会连数据再去查找.
7. bulk(批量)地去操作数据，比如bulk_create
8. 查找一条数据时，尽量用有索引的字段去查询，O(1)或O(log n)和O(n)差别还是很大的
9. 用count()代替len(queryset), 用exists()代替if queryset:

[参考]

QuerySet特性及高级使用技巧
aggregate和annotate方法使用详解与示例
select_related和prefetch_related的用法与区别