使用 select_related prefetch_related 优化 django rest framework 接口

哪些查询慢？ 怎么查看慢在哪里？ 可以参考上一遍文章

使用Django Debug Toolbar 调试 django rest framework 接口

image.png

image.png
分析  其中的sql 查询发现 ，这个是 查询一个表的数据后，把每条数据 都 单独的去关联表 查询

select_related() prefetch_related() 官网文档

相关文档参考 ：
django优化查询语句之 深入select_related与prefetch_related函数

select_related 和 prefetch_related 实现查询优化

Django ORM中的select_related和prefetch_related有什么区别？

个人 项目不同 ，建议可以根据  Django Debug Toolbar 的分析，来确定使用  select_related和prefetch_related

一些建议：
select_related    >    OneToOneField 和ForeignKey
prefetch_related    >    ManyToManyField

例：使用 select_related 优化 django rest framework 接口

表单关系

image.png

image.png

model 页面

'''
可以看到 设备型号 通过  ForeignKey 关联 厂商  设备类型 表
'''
class Manufacturers(models.Model):
    '''
    基本信息 厂商
    '''
    id = models.AutoField('id', primary_key=True, help_text="id")
    name = models.CharField("厂商名称", max_length=50, help_text="厂商名称(验证重复)", unique=True)
    note = models.TextField("备注", default="", help_text="备注", null=True, blank=True)
    add_time = models.DateTimeField("添加时间", auto_now_add=True)

    class Meta:
        verbose_name = "厂商"
        verbose_name_plural = verbose_name

    def __str__(self):
        return self.id

class DeviceTypes(models.Model):
    '''
    基本信息 设备类型
    '''
    id = models.AutoField('id', primary_key=True, help_text="id")
    name = models.CharField("设备类型", max_length=50, help_text="设备类型名称(验证重复)", unique=True)
    note = models.TextField("备注", default="", help_text="备注", null=True, blank=True)
    add_time = models.DateTimeField("添加时间", auto_now_add=True)

    class Meta:
        verbose_name = "设备类型"
        verbose_name_plural = verbose_name

    def __str__(self):
        return self.id

class DeviceModels(models.Model):
    '''
    基本信息 设备型号
    '''
    id = models.AutoField('id', primary_key=True, help_text="id")
    name = models.CharField("设备型号名称", max_length=100, help_text="设备型号名称(验证重复)", unique=True)
    manufacturers = models.ForeignKey(Manufacturers, on_delete=models.PROTECT, related_name='manufacturers_id', verbose_name="厂商id")
    devicetype = models.ForeignKey(DeviceTypes, on_delete=models.PROTECT, related_name='devicetype_id', verbose_name="设备类型id")
    price = models.IntegerField("价格", default="", help_text="价格", null=True, blank=True)
    life_cycle = models.IntegerField("生命周期", default="", help_text="生命周期", null=True, blank=True)
    templateids = models.CharField("监控模板", max_length=255, help_text="监控模板", null=True, blank=True)
    note = models.TextField("备注", default="", help_text="备注", null=True, blank=True)
    add_time = models.DateTimeField("添加时间", auto_now_add=True)

    class Meta:
        verbose_name = "设备型号"
        verbose_name_plural = verbose_name

Serializer 页面

class ManufacturersSerializer(serializers.ModelSerializer):
    '''
    厂商 Serializer
    '''
    def validate(self, attrs):
        return attrs

    class Meta:
        ordering = ['-id']
        model = Manufacturers
        fields = "__all__"

class DeviceTypesSerializer(serializers.ModelSerializer):
    '''
    设备类型 Serializer
    '''
    def validate(self, attrs):
        return attrs

    class Meta:
        ordering = ['-id']
        model = DeviceTypes
        fields = "__all__"

class DeviceModelsSerializer(serializers.ModelSerializer):
    '''
    设备型号 Serializer
    '''
    manufacturers = ManufacturersSerializer()
    devicetype = DeviceTypesSerializer()
    value = serializers.SerializerMethodField()
    def validate(self, attrs):
        return attrs

    class Meta:
        ordering = ['-id']
        model = DeviceModels
        fields = "__all__"

    def get_value(self, obj):
        return obj.manufacturers.name+' '+obj.devicetype.name+' '+obj.name

views.py 页面

class DeviceModelsViewset(viewsets.ModelViewSet):
    '''
    list:
        获取所有设备型号信息
    read:
        根据id获取单个设备型号信息
    create:
        创建新的设备型号信息
    update:
        根据id更新单个设备类型信息的全部字段
    partial_update:
        根据id更新单个设备类型信息的字段
    delete:
        根据id删除单个设备类型信息
    '''
    queryset = DeviceModels.objects.all().order_by('-id')   # 关注这行 ，后面主要在这里修改
    serializer_class = DeviceModelsSerializer
    pagination_class = GenericsPageNumberPagination

queryset = DeviceModels.objects.all().order_by('-id')   # 关注这行 ，后面主要在这里修改
# 分页查询后，一直在重复查询  basic_devicetypes   basic_manufacturers  总共花费 47ms

image.png

image.png
    queryset = DeviceModels.objects.select_related('manufacturers', 'devicetype').order_by('-id')  
    # 用 join 的方法 只查询了一次 ，总共花费 11ms
    SELECT `basic_devicemodels`.`id`,
       `basic_devicemodels`.`name`,
       `basic_devicemodels`.`manufacturers_id`,
       `basic_devicemodels`.`devicetype_id`,
       `basic_devicemodels`.`price`,
       `basic_devicemodels`.`life_cycle`,
       `basic_devicemodels`.`templateids`,
       `basic_devicemodels`.`note`,
       `basic_devicemodels`.`add_time`,
       `basic_manufacturers`.`id`,
       `basic_manufacturers`.`name`,
       `basic_manufacturers`.`note`,
       `basic_manufacturers`.`add_time`,
       `basic_devicetypes`.`id`,
       `basic_devicetypes`.`name`,
       `basic_devicetypes`.`note`,
       `basic_devicetypes`.`add_time`
  FROM `basic_devicemodels`
 INNER JOIN `basic_manufacturers`
    ON (`basic_devicemodels`.`manufacturers_id` = `basic_manufacturers`.`id`)
 INNER JOIN `basic_devicetypes`
    ON (`basic_devicemodels`.`devicetype_id` = `basic_devicetypes`.`id`)
 ORDER BY `basic_devicemodels`.`id` DESC
 LIMIT 10

image.png

image.png
 点击 Expl 可以看下查询的 详细分析

image.png

image.png
queryset = DeviceModels.objects.prefetch_related('manufacturers', 'devicetype').order_by('-id')
# 分页查询后，再查询  basic_devicetypes   basic_manufacturers 组合  总共花费 11ms

image.png

image.png