本文将通过简单的示例介绍为什么要使用prefetch_related。
准备工作
创建模型
from django.db import modelsclass Product(models.Model):product_name = models.CharField(max_length=255)class Component(models.Model):product = models.ForeignKey(Product, on_delete=models.CASCADE, related_name='components')code = models.CharField(max_length=255)class Meta:ordering = ['product']
创建数据
>>> from api.models import Product
>>> p1 = Product.objects.create(product_name="产品1")
>>> p2 = Product.objects.create(product_name="产品2")
>>> from api.models import Component
>>> pc1 = Component.objects.create(product=p1, code="LS")
>>> pc2 = Component.objects.create(product=p1, code="LM")
>>> pc3 = Component.objects.create(product=p2, code="XYB")
>>> pc4 = Component.objects.create(product=p2, code="LXC")
在settings.py文件中添加配置输出运行时执行的sql
LOGGING = {'version': 1,'disable_existing_loggers': False,'handlers': {'console': {'class': 'logging.StreamHandler',},},'loggers': {'django.db.backends': {'handlers': ['console'],'level': 'DEBUG',},},
}
不使用prefetch_related
from api.models import Product
ps = Product.objects.all()
# SELECT "api_product"."id", "api_product"."product_name" FROM "api_product"; args=(); alias=defaultfor p in ps:print(p.components.all())# SELECT "api_component"."id", "api_component"."product_id", "api_component"."code" FROM "api_component" WHERE "api_component"."product_id" = 1 ORDER BY "api_component"."product_id" ASC LIMIT 21; args=(1,);# SELECT "api_component"."id", "api_component"."product_id", "api_component"."code" FROM "api_component" WHERE "api_component"."product_id" = 2 ORDER BY "api_component"."product_id" ASC LIMIT 21; args=(2,);
不使用prefetch_related我们获取所有"产品"执行了一次sql查询，然而当我们遍历想要获取所有产品的组件时，对每个产品都执行了一次sql查询。
我们的示例中只产品的数量只有两个，当产品的数量非常多，成百上千时，对每个产品你都要到数据库执行一次查询，这将导致严重的性能问题，也就是所谓的n+1问题。
使用prefetch_related
from api.models import Product
ps = Product.objects.prefetch_related("components")
# SELECT "api_product"."id", "api_product"."product_name" FROM "api_product"; args=(); alias=default# SELECT "api_component"."id", "api_component"."product_id", "api_component"."code" FROM "api_component" WHERE "api_component"."product_id" IN (1, 2) ORDER BY "api_component"."product_id" ASC; args=(1, 2); alias=default
for p in ps:print(p.components.all())
可以清晰的看到总共执行了两次sql查询，在获取全部"产品"的时候通过一次额外的查询将所有产品的组件信息缓存起来，当我们需要的时候是从缓存中获取，不会执行任何的数据库查询
select_related
select_related只能用于一对一关系和外键关系，它是利用Join只进行一次SQL查询，但是它的局限也很明显，不能用于多对多，多对一等
from api.models import Component
c = Component.objects.select_related("product").get(pk=1)# SELECT "api_component"."id", "api_component"."product_id", "api_component"."code", "api_product"."id", "api_product"."product_name" FROM "api_component" INNER JOIN "api_product" ON ("api_component"."product_id" = "api_product"."id") WHERE "api_component"."id" = 1 LIMIT 21; args=(1,); alias=default
