Django——select_related和prefetch_related的使用与性能测试

目录

MessageModel.objects.select_related('sender').query.__str__()
Out[6]: 'SELECT "tb_message.all_fields", "tb_users.all_fields", FROM "tb_message" INNER JOIN "tb_users" ON ("tb_message"."sender_id" = "tb_users"."id")'

MessageModel.objects.all().query.__str__()
Out[7]: 'SELECT "tb_message.all_fields" FROM "tb_message"'

测试

@timer
def gao_ji_search():
    data = []
    datas = MessageModel.objects.select_related('sender')  # ForeignKey字段
    for i in datas:
        data.append(i.sender.username)
    return any(data)

@timer
def di_ji_search():
    data = []
    datas = MessageModel.objects.all()
    for i in datas:
        data.append(i.sender.username)
    return any(data)

MessageModel.objects.count()
Out[47]: 9

gao_ji_search()
<gao_ji_search>运行用时0.011433839797973633s
Out[4]: True
  
di_ji_search()
<di_ji_search>运行用时0.11970925331115723s
Out[3]: True

结论：select_related对于o2o和m2o字段，可以牺牲内存优化对数据库的操作次数，较少时间的消耗

使用prefetch_related方法和不使用

[i.cargo_price_orders.all().values('code').query.__str__() for i in CargoModel.objects.all()[1:2]]
Out[25]: ['SELECT "tb_price_order"."code" FROM "tb_price_order" INNER JOIN "tb_price_order_cargo_ref" ON ("tb_price_order"."id" = "tb_price_order_cargo_ref"."priceordermodel_id") WHERE "tb_price_order_cargo_ref"."cargomodel_id" = 11']

[i.cargo_price_orders.all().values('code').query.__str__() for i in CargoModel.objects.prefetch_related('cargo_price_orders')[1:2]]
Out[39]: ['SELECT "tb_price_order"."code" FROM "tb_price_order" INNER JOIN "tb_price_order_cargo_ref" ON ("tb_price_order"."id" = "tb_price_order_cargo_ref"."priceordermodel_id") WHERE "tb_price_order_cargo_ref"."cargomodel_id" = 11']

没啥区别，于是看了一下查询结果带有哪些参数，找到了一个self._prefetch_related_lookups，记录预存储的m2m字段，一个self._prefetch_done，布尔值,调用了prefetch_related方法的结果里面值都是对应的有结果：

_prefetch_related_lookups = ('cargo_price_orders',)
_prefetch_done = True

所以可以推测，预存储的数据，之后再使用，是不需要在进行sql查询的

测试

@timer
def gao_ji_search():
    data = []
    # ManyToManyField字段反向查找的字段
    datas = CargoModel.objects.prefetch_related('cargo_price_orders')  
    for i in datas:
        for j in i.cargo_price_orders.all():
            data.append(j.code)
    return any(data)

@timer
def di_ji_search():
    data = []
    datas = CargoModel.objects.all()
    for i in datas:
        for j in i.cargo_price_orders.all():
            data.append(j.code)
    return any(data)

CargoModel.objects.count()
Out[6]: 21185
  
di_ji_search()
<di_ji_search>运行用时31.286818027496338s
Out[3]: True
  
gao_ji_search()
<gao_ji_search>运行用时3.2482240200042725s
Out[4]: True

结论：prefetch_related对于m2m字段，可以牺牲内存优化对数据库的操作次数，较少时间的消耗

posted @ 2021-05-24 10:09  pywjh  阅读(144)  评论(0)  编辑  收藏  举报
回到顶部