关于django的prefetch_related优化查询问题

痛定思痛。 2022-05-22 02:42 213阅读 0赞

prefetch\_related 介绍参见 [https://blog.csdn.net/pushiqiang/article/details/79560550][https_blog.csdn.net_pushiqiang_article_details_79560550]

在使用中发现，在对内嵌子列表再次进行filter时，prefetch\_related会失效

### models.py定义如下： ###

class Category(Model):
        """ 种类 """
        name = models.CharField(_(u'name'), max_length=100, db_index=True)
    
    class Book(Model):
        name = models.CharField(_(u'name'), max_length=100, db_index=True)
        is_published = models.BooleanField(_(u'is published'), default=False)
        category = models.ForeignKey(Category,
                                     verbose_name=_(u'Book category'),
                                     related_name='books')

### 使用prefetch-related 优化查询，实现在查询种类列表的同时预加载内嵌的book列表 ###

# https://docs.djangoproject.com/en/dev/ref/models/querysets/#prefetch-related
    # 查询种类列表的同时预加载内嵌的Book列表
    categories = Category.objects.prefetch_related(
                Prefetch(
                    'books',
                    queryset=Book.objects.all()))

在使用[django-extensions][]的shell环境下测试（使用 `python manage.py shell_plus --print-sql` 进入django的shell环境）

### 正确使用的情况 ###

在遍历种类列表categories时打印books列表，会触发两条sql语句，prefetch\_related正常起作用

for category in categories:
        print category.books.all()

In [39]: for category in categories:
        ...:     print category.books.all()
        ...:     
    SELECT "category"."id",
           "category"."name",
    FROM "category"
    Execution time: 0.000725s [Database: slave]
    
    SELECT "book"."id",
           "book"."name",
    FROM "book"
    WHERE "book"."category_id" IN (1, 2, 3)
    Execution time: 0.000689s [Database: default]
    
    [<Book: 语文>, <Book: 数学>]
    [<Book: 大数据>, <Book: 历史>]

### 失效的情况 ###

若是再次在books变量上进行filter，则prefetch-related会失效，会在遍历categories时对每个分类的books进行一次查询，即如果有100个分类，会进行100次books列表查询

for category in categories:
        print category.books.filter(is_published=True)

In [40]: for category in categories:
        ...:     print category.books.filter(is_published=True)    
        ...:     
    SELECT "book"."id", "book"."is_published", FROM "book" WHERE ("book"."category_id" = 1 AND "book"."is_published" = true) LIMIT 21 Execution time: 0.000805s [Database: default] [] SELECT "book"."id", "book"."is_published", FROM "book" WHERE ("book"."category_id" = 2 AND "book"."is_recommended" = true) LIMIT 21 Execution time: 0.001096s [Database: default] [] SELECT "book"."id", "book"."is_published", FROM "book" WHERE ("book"."category_id" = 3 AND "book"."is_recommended" = true) LIMIT 21 Execution time: 0.001096s [Database: default]

### 总结 ###

因此，在使用prefetch-related进行优化查询时，内嵌子列表的过滤必须在prefetch-related函数调用时指定过滤条件，而不能在遍历时再次对子列表进行过滤查询，最终的外键反向查询结果会从prefetch-related结果中提取

categories = Category.objects.prefetch_related(
                Prefetch(
                    'books',
                    queryset=Book.objects.filter(is_recommended=True)))

[https_blog.csdn.net_pushiqiang_article_details_79560550]: https://blog.csdn.net/pushiqiang/article/details/79560550
[django-extensions]: https://github.com/django-extensions/django-extensions