圆月山庄资源网

利用Django框架中select_related和prefetch_related函数对数据库查询优化
脚本专栏 2024/11/8 佚名
3 2
圆月山庄资源网 Design By www.vgjia.com

实例的背景说明

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

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

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()是有效的，之后的将会被无视掉。
标签：
Python,Django,数据库
圆月山庄资源网 Design By www.vgjia.com
广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！ 如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com
圆月山庄资源网 Design By www.vgjia.com
www.vgjia.com 圆月山庄资源网
139,976互联网资源
144,792高清电影
21,817无损音乐
631,128技术资源

更新日志

友情链接

杰晶网络 DDR爱好者之家 桃源资源网 杰网资源 富贵资源网 南强小屋 铁雪资源网 幽灵资源网 万梅资源网 狼山资源网 白云岛资源网 昆仑资源网 相思资源网 明霞山资源网 内蒙古资源网 黑松山资源网 茶园资源网 饿虎岗资源网 大旗谷资源网 常春岛资源网 岱庙资源网 兴国资源网 快活林资源网 蝙蝠岛资源网 帝王谷资源网 白云城资源网 伏龙阁资源网 清风细雨楼 天枫庄资源网 圆月山庄资源网 无争山庄资源网 神水资源网 移花宫资源网 神剑山庄资源网 无为清净楼资源网 金钱帮资源网 丐帮资源网 华山资源网 极乐门资源网 小李飞刀资源网 凤求凰客栈 风云阁资源网 金狮镖局 鸳鸯亭资源网 千金楼资源网 更多链接