哪些查詢慢谓着？ 怎么查看慢在哪里猪腕？ 可以參考上一遍文章

使用Django Debug Toolbar 調(diào)試 django rest framework 接口

image.png

分析  其中的sql 查詢發(fā)現(xiàn) 饺律，這個(gè)是 查詢一個(gè)表的數(shù)據(jù)后，把每條數(shù)據(jù) 都 單獨(dú)的去關(guān)聯(lián)表 查詢

select_related() prefetch_related() 官網(wǎng)文檔

相關(guān)文檔參考 ：
django優(yōu)化查詢語(yǔ)句之 深入select_related與prefetch_related函數(shù)

select_related 和 prefetch_related 實(shí)現(xiàn)查詢優(yōu)化

Django ORM中的select_related和prefetch_related有什么區(qū)別？

個(gè)人 項(xiàng)目不同 铸磅，建議可以根據(jù)  Django Debug Toolbar 的分析缀遍，來(lái)確定使用  select_related和prefetch_related

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

例：使用 select_related 優(yōu)化 django rest framework 接口

表單關(guān)系

image.png

model 頁(yè)面

'''
可以看到 設(shè)備型號(hào) 通過(guò)  ForeignKey 關(guān)聯(lián) 廠商  設(shè)備類型 表
'''
class Manufacturers(models.Model):
    '''
    基本信息 廠商
    '''
    id = models.AutoField('id', primary_key=True, help_text="id")
    name = models.CharField("廠商名稱", max_length=50, help_text="廠商名稱(驗(yàn)證重復(fù))", unique=True)
    note = models.TextField("備注", default="", help_text="備注", null=True, blank=True)
    add_time = models.DateTimeField("添加時(shí)間", auto_now_add=True)

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

    def __str__(self):
        return self.id

class DeviceTypes(models.Model):
    '''
    基本信息 設(shè)備類型
    '''
    id = models.AutoField('id', primary_key=True, help_text="id")
    name = models.CharField("設(shè)備類型", max_length=50, help_text="設(shè)備類型名稱(驗(yàn)證重復(fù))", unique=True)
    note = models.TextField("備注", default="", help_text="備注", null=True, blank=True)
    add_time = models.DateTimeField("添加時(shí)間", auto_now_add=True)

    class Meta:
        verbose_name = "設(shè)備類型"
        verbose_name_plural = verbose_name

    def __str__(self):
        return self.id

class DeviceModels(models.Model):
    '''
    基本信息 設(shè)備型號(hào)
    '''
    id = models.AutoField('id', primary_key=True, help_text="id")
    name = models.CharField("設(shè)備型號(hào)名稱", max_length=100, help_text="設(shè)備型號(hào)名稱(驗(yàn)證重復(fù))", 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="設(shè)備類型id")
    price = models.IntegerField("價(jià)格", default="", help_text="價(jià)格", null=True, blank=True)
    life_cycle = models.IntegerField("生命周期", default="", help_text="生命周期", null=True, blank=True)
    templateids = models.CharField("監(jiān)控模板", max_length=255, help_text="監(jiān)控模板", null=True, blank=True)
    note = models.TextField("備注", default="", help_text="備注", null=True, blank=True)
    add_time = models.DateTimeField("添加時(shí)間", auto_now_add=True)

    class Meta:
        verbose_name = "設(shè)備型號(hào)"
        verbose_name_plural = verbose_name

Serializer 頁(yè)面

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

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

class DeviceTypesSerializer(serializers.ModelSerializer):
    '''
    設(shè)備類型 Serializer
    '''
    def validate(self, attrs):
        return attrs

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

class DeviceModelsSerializer(serializers.ModelSerializer):
    '''
    設(shè)備型號(hào) 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 頁(yè)面

class DeviceModelsViewset(viewsets.ModelViewSet):
    '''
    list:
        獲取所有設(shè)備型號(hào)信息
    read:
        根據(jù)id獲取單個(gè)設(shè)備型號(hào)信息
    create:
        創(chuàng)建新的設(shè)備型號(hào)信息
    update:
        根據(jù)id更新單個(gè)設(shè)備類型信息的全部字段
    partial_update:
        根據(jù)id更新單個(gè)設(shè)備類型信息的字段
    delete:
        根據(jù)id刪除單個(gè)設(shè)備類型信息
    '''
    queryset = DeviceModels.objects.all().order_by('-id')   # 關(guān)注這行 慕匠，后面主要在這里修改
    serializer_class = DeviceModelsSerializer
    pagination_class = GenericsPageNumberPagination

queryset = DeviceModels.objects.all().order_by('-id')   # 關(guān)注這行 ，后面主要在這里修改
# 分頁(yè)查詢后域醇，一直在重復(fù)查詢  basic_devicetypes   basic_manufacturers  總共花費(fèi) 47ms

image.png

    queryset = DeviceModels.objects.select_related('manufacturers', 'devicetype').order_by('-id')  
    # 用 join 的方法 只查詢了一次 台谊，總共花費(fèi) 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

 點(diǎn)擊 Expl 可以看下查詢的 詳細(xì)分析

image.png

queryset = DeviceModels.objects.prefetch_related('manufacturers', 'devicetype').order_by('-id')
# 分頁(yè)查詢后，再查詢  basic_devicetypes   basic_manufacturers 組合  總共花費(fèi) 11ms

image.png