当前位置： 移动技术网 > IT编程>脚本编程>Python > 利用Django框架中select_related和prefetch_related函数对数据库查询优化

利用Django框架中select_related和prefetch_related函数对数据库查询优化

2019年06月14日  | 移动技术网IT编程  | 我要评论

精凌岭,郭德纲岳云鹏汾河湾,江西师大论坛

实例的背景说明

假定一个个人信息系统，需要记录系统中各个人的故乡、居住地、以及到过的城市。数据库设计如下：

201541150650059.jpg (591×250)

models.py 内容如下：
 

from django.db import models
 
class province(models.model):
 name = models.charfield(max_length=10)
 def __unicode__(self):
  return self.name
 
class city(models.model):
 name = models.charfield(max_length=5)
 province = models.foreignkey(province)
 def __unicode__(self):
  return self.name
 
class person(models.model):
 firstname = models.charfield(max_length=10)
 lastname = models.charfield(max_length=10)
 visitation = models.manytomanyfield(city, related_name = "visitor")
 hometown = models.foreignkey(city, related_name = "birth")
 living  = models.foreignkey(city, related_name = "citizen")
 def __unicode__(self):
  return self.firstname + self.lastname

注1：创建的app名为“qsoptimize”

注2：为了简化起见，`qsoptimize_province` 表中只有2条数据：湖北省和广东省，`qsoptimize_city`表中只有三条数据：武汉市、十堰市和广州市

如果我们想要获得所有家乡是湖北的人，最无脑的做法是先获得湖北省，再获得湖北的所有城市，最后获得故乡是这个城市的人。就像这样：
 

>>> hb = province.objects.get(name__iexact=u"湖北省")
>>> people = []
>>> for city in hb.city_set.all():
... people.extend(city.birth.all())
...

显然这不是一个明智的选择，因为这样做会导致1+（湖北省城市数）次sql查询。反正是个反例，导致的查询和获得掉结果就不列出来了。
prefetch_related() 或许是一个好的解决方法，让我们来看看。
 

>>> hb = province.objects.prefetch_related("city_set__birth").objects.get(name__iexact=u"湖北省")
>>> people = []
>>> for city in hb.city_set.all():
... people.extend(city.birth.all())
...

因为是一个深度为2的prefetch，所以会导致3次sql查询：
 

select `qsoptimize_province`.`id`, `qsoptimize_province`.`name`
from `qsoptimize_province`
where `qsoptimize_province`.`name` like '湖北省' ;
 
select `qsoptimize_city`.`id`, `qsoptimize_city`.`name`, `qsoptimize_city`.`province_id`
from `qsoptimize_city`
where `qsoptimize_city`.`province_id` in (1);
 
select `qsoptimize_person`.`id`, `qsoptimize_person`.`firstname`, `qsoptimize_person`.`lastname`,
`qsoptimize_person`.`hometown_id`, `qsoptimize_person`.`living_id`
from `qsoptimize_person`
where `qsoptimize_person`.`hometown_id` in (1, 3);

嗯…看上去不错，但是3次查询么？倒过来查询可能会更简单？
 

>>> people = list(person.objects.select_related("hometown__province").filter(hometown__province__name__iexact=u"湖北省"))
 
select `qsoptimize_person`.`id`, `qsoptimize_person`.`firstname`, `qsoptimize_person`.`lastname`,
`qsoptimize_person`.`hometown_id`, `qsoptimize_person`.`living_id`, `qsoptimize_city`.`id`,
`qsoptimize_city`.`name`, `qsoptimize_city`.`province_id`, `qsoptimize_province`.`id`, `qsoptimize_province`.`name`
from `qsoptimize_person`
inner join `qsoptimize_city` on (`qsoptimize_person`.`hometown_id` = `qsoptimize_city`.`id`)
inner join `qsoptimize_province` on (`qsoptimize_city`.`province_id` = `qsoptimize_province`.`id`)
where `qsoptimize_province`.`name` like '湖北省';
 
+----+-----------+----------+-------------+-----------+----+--------+-------------+----+--------+
| id | firstname | lastname | hometown_id | living_id | id | name | province_id | id | name |
+----+-----------+----------+-------------+-----------+----+--------+-------------+----+--------+
| 1 | 张  | 三  |   3 |   1 | 3 | 十堰市 |   1 | 1 | 湖北省 |
| 2 | 李  | 四  |   1 |   3 | 1 | 武汉市 |   1 | 1 | 湖北省 |
| 3 | 王  | 麻子  |   3 |   2 | 3 | 十堰市 |   1 | 1 | 湖北省 |
+----+-----------+----------+-------------+-----------+----+--------+-------------+----+--------+
3 rows in set (0.00 sec)

完全没问题。不仅sql查询的数量减少了，python程序上也精简了。
select_related()的效率要高于prefetch_related()。因此，最好在能用select_related()的地方尽量使用它，也就是说，对于foreignkey字段，避免使用prefetch_related()。
联用
对于同一个queryset，你可以同时使用这两个函数。
在我们一直使用的例子上加一个model：order （订单）
 

class order(models.model):
 customer = models.foreignkey(person)
 orderinfo = models.charfield(max_length=50)
 time  = models.datetimefield(auto_now_add = true)
 def __unicode__(self):
  return self.orderinfo

如果我们拿到了一个订单的id 我们要知道这个订单的客户去过的省份。因为有manytomanyfield显然必须要用prefetch_related()。如果只用prefetch_related()会怎样呢？
 

>>> plist = order.objects.prefetch_related('customer__visitation__province').get(id=1)
>>> for city in plist.customer.visitation.all():
... print city.province.name
...

显然，关系到了4个表：order、person、city、province，根据prefetch_related()的特性就得有4次sql查询
 

select `qsoptimize_order`.`id`, `qsoptimize_order`.`customer_id`, `qsoptimize_order`.`orderinfo`, `qsoptimize_order`.`time`
from `qsoptimize_order`
where `qsoptimize_order`.`id` = 1 ;
 
select `qsoptimize_person`.`id`, `qsoptimize_person`.`firstname`, `qsoptimize_person`.`lastname`, `qsoptimize_person`.`hometown_id`, `qsoptimize_person`.`living_id`
from `qsoptimize_person`
where `qsoptimize_person`.`id` in (1);
 
select (`qsoptimize_person_visitation`.`person_id`) as `_prefetch_related_val`, `qsoptimize_city`.`id`,
`qsoptimize_city`.`name`, `qsoptimize_city`.`province_id`
from `qsoptimize_city`
inner join `qsoptimize_person_visitation` on (`qsoptimize_city`.`id` = `qsoptimize_person_visitation`.`city_id`)
where `qsoptimize_person_visitation`.`person_id` in (1);
 
select `qsoptimize_province`.`id`, `qsoptimize_province`.`name`
from `qsoptimize_province`
where `qsoptimize_province`.`id` in (1, 2);

+----+-------------+---------------+---------------------+
| id | customer_id | orderinfo  | time    |
+----+-------------+---------------+---------------------+
| 1 |   1 | info of order | 2014-08-10 17:05:48 |
+----+-------------+---------------+---------------------+
1 row in set (0.00 sec)
 
+----+-----------+----------+-------------+-----------+
| id | firstname | lastname | hometown_id | living_id |
+----+-----------+----------+-------------+-----------+
| 1 | 张  | 三  |   3 |   1 |
+----+-----------+----------+-------------+-----------+
1 row in set (0.00 sec)
 
+-----------------------+----+--------+-------------+
| _prefetch_related_val | id | name | province_id |
+-----------------------+----+--------+-------------+
|      1 | 1 | 武汉市 |   1 |
|      1 | 2 | 广州市 |   2 |
|      1 | 3 | 十堰市 |   1 |
+-----------------------+----+--------+-------------+
3 rows in set (0.00 sec)
 
+----+--------+
| id | name |
+----+--------+
| 1 | 湖北省 |
| 2 | 广东省 |
+----+--------+
2 rows in set (0.00 sec)

更好的办法是先调用一次select_related()再调用prefetch_related()，最后再select_related()后面的表
 

>>> plist = order.objects.select_related('customer').prefetch_related('customer__visitation__province').get(id=1)
>>> for city in plist.customer.visitation.all():
... print city.province.name
...

这样只会有3次sql查询，django会先做select_related，之后prefetch_related的时候会利用之前缓存的数据，从而避免了1次额外的sql查询：

select `qsoptimize_order`.`id`, `qsoptimize_order`.`customer_id`, `qsoptimize_order`.`orderinfo`, 
`qsoptimize_order`.`time`, `qsoptimize_person`.`id`, `qsoptimize_person`.`firstname`, 
`qsoptimize_person`.`lastname`, `qsoptimize_person`.`hometown_id`, `qsoptimize_person`.`living_id` 
from `qsoptimize_order` 
inner join `qsoptimize_person` on (`qsoptimize_order`.`customer_id` = `qsoptimize_person`.`id`) 
where `qsoptimize_order`.`id` = 1 ;
 
select (`qsoptimize_person_visitation`.`person_id`) as `_prefetch_related_val`, `qsoptimize_city`.`id`, 
`qsoptimize_city`.`name`, `qsoptimize_city`.`province_id` 
from `qsoptimize_city` 
inner join `qsoptimize_person_visitation` on (`qsoptimize_city`.`id` = `qsoptimize_person_visitation`.`city_id`) 
where `qsoptimize_person_visitation`.`person_id` in (1);
 
select `qsoptimize_province`.`id`, `qsoptimize_province`.`name` 
from `qsoptimize_province` 
where `qsoptimize_province`.`id` in (1, 2);
 
+----+-------------+---------------+---------------------+----+-----------+----------+-------------+-----------+
| id | customer_id | orderinfo  | time    | id | firstname | lastname | hometown_id | living_id |
+----+-------------+---------------+---------------------+----+-----------+----------+-------------+-----------+
| 1 |   1 | info of order | 2014-08-10 17:05:48 | 1 | 张  | 三  |   3 |   1 |
+----+-------------+---------------+---------------------+----+-----------+----------+-------------+-----------+
1 row in set (0.00 sec)
 
+-----------------------+----+--------+-------------+
| _prefetch_related_val | id | name | province_id |
+-----------------------+----+--------+-------------+
|      1 | 1 | 武汉市 |   1 |
|      1 | 2 | 广州市 |   2 |
|      1 | 3 | 十堰市 |   1 |
+-----------------------+----+--------+-------------+
3 rows in set (0.00 sec)
 
+----+--------+
| id | name |
+----+--------+
| 1 | 湖北省 |
| 2 | 广东省 |
+----+--------+
2 rows in set (0.00 sec)

值得注意的是，可以在调用prefetch_related之前调用select_related，并且django会按照你想的去做：先select_related，然后利用缓存到的数据prefetch_related。然而一旦prefetch_related已经调用，select_related将不起作用。

 小结

  1.     因为select_related()总是在单次sql查询中解决问题，而prefetch_related()会对每个相关表进行sql查询，因此select_related()的效率通常比后者高。
  2.     鉴于第一条，尽可能的用select_related()解决问题。只有在select_related()不能解决问题的时候再去想prefetch_related()。
  3.     你可以在一个queryset中同时使用select_related()和prefetch_related()，从而减少sql查询的次数。
  4.     只有prefetch_related()之前的select_related()是有效的，之后的将会被无视掉。

您可能感兴趣的文章:

如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网