django优化查询语句之 深入select_related与prefetch_related函数

在数据库有外键的时候,使用select_related()和prefetch_related()可以很好的减少数据库请求的次数,从而提高性能.

下面是数据库设计图

 

models.py如下:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

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

app名为"QSOptimize"

`qsoptimize_province` 表中只有2条数据：湖北省和广东省，`qsoptimize_city`表中只有三条数据：武汉市、十堰市和广州市

select_related()

对于一对一字段(OneToOneField)和外键字段(ForeignKey),可以使用select_related()来对QuerySet进行优化.

在对QuerySet使用select_related()函数后，Django会获取相应外键对应的对象，从而在之后需要的时候不必再查询数据库了。

一个例子:

1 2 3

>>> citys = City.objects.all() >>> for c in citys: ...   print c.province

这样会导致线性的SQL查询,SQL查询语句如下:

1 2 3 4 5 6 7 8 9 10 11 12 13 14

SELECT `QSOptimize_city`.`id`, `QSOptimize_city`.`name`, `QSOptimize_city`.`province_id` FROM `QSOptimize_city`    SELECT `QSOptimize_province`.`id`, `QSOptimize_province`.`name` FROM `QSOptimize_province` WHERE `QSOptimize_province`.`id` = 1 ;    SELECT `QSOptimize_province`.`id`, `QSOptimize_province`.`name` FROM `QSOptimize_province` WHERE `QSOptimize_province`.`id` = 2 ;    SELECT `QSOptimize_province`.`id`, `QSOptimize_province`.`name` FROM `QSOptimize_province` WHERE `QSOptimize_province`.`id` = 1 ;

使用select_related()函数后

1 2 3

>>> citys = City.objects.select_related().all() >>> for c in citys: ...   print c.province

就只有一次SQL查询,大大减少SQL查询次数

1 2 3 4

SELECT `QSOptimize_city`.`id`, `QSOptimize_city`.`name`, `QSOptimize_city`.`province_id`, `QSOptimize_province`.`id`, `QSOptimize_province`.`name` FROM`QSOptimize_city` INNER JOIN `QSOptimize_province` ON (`QSOptimize_city`.`province_id` = `QSOptimize_province`.`id`) ;

django使用了INNER JOIN来获得省份的信息.

使用方法

select_related() 接受可变长参数，每个参数是需要获取的外键（父表的内容）的字段名，以及外键的外键的字段名、外键的外键的外键…。若要选择外键的外键需要使用两个下划线“__”来连接。

例如获得张三的现居省份

1 2	>>> zhangs = Person.objects.select_related('living__province').get(firstname=u"张",lastname=u"三") >>> zhangs.living.province

触发的SQL:

1 2 3 4 5 6 7 8

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`.`living_id` = `QSOptimize_city`.`id`) INNER JOIN `QSOptimize_province` ON (`QSOptimize_city`.`province_id` = `QSOptimize_province`.`id`) WHERE (`QSOptimize_person`.`lastname` = '三'  AND `QSOptimize_person`.`firstname` = '张' );

django使用了两次INNER JOIN来完成请求,但是未指定的外键则不会被添加到结果中,例如张三的故乡.

django1.7以前同时指定两个外键使用

1	zhangs = Person.objects.select_related('hometown__province','living__province').get(firstname=u"张",lastname=u"三")

1.7后使用

1	zhangs = Person.objects.select_related('hometown__province').select_related('living__province').get(firstname=u"张",lastname=u"三")

depth参数

select_related() 接受depth参数，depth参数可以确定select_related的深度。Django会递归遍历指定深度内的所有的OneToOneField和ForeignKey.

1	zhangs = Person.objects.select_related(depth = d)

d=1  相当于 select_related(‘hometown’,'living’)

d=2  相当于 select_related(‘hometown__province’,'living__province’)

无参数

select_related() 也可以不加参数，这样表示要求Django尽可能深的select_related。例如：zhangs = Person.objects.select_related().get(firstname=u”张”,lastname=u”三”)。但要注意两 点：

1. Django本身内置一个上限，对于特别复杂的表关系，Django可能在你不知道的某处跳出递归，从而与你想的做法不一样。具体限制是怎么工作的我表示不清楚。
2. Django并不知道你实际要用的字段有哪些，所以会把所有的字段都抓进来，从而会造成不必要的浪费而影响性能。

小结

1. select_related主要针一对一和多对一关系进行优化。
2. select_related使用SQL的JOIN语句进行优化，通过减少SQL查询的次数来进行优化、提高性能。
3. 可以通过可变长参数指定需要select_related的字段名。也可以通过使用双下划线“__”连接字段名来实现指定的递归查询。没有指定的字段不会缓存，没有指定的深度不会缓存，如果要访问的话Django会再次进行SQL查询。
4. 也可以通过depth参数指定递归的深度，Django会自动缓存指定深度内所有的字段。如果要访问指定深度外的字段，Django会再次进行SQL查询。
5. 也接受无参数的调用，Django会尽可能深的递归查询所有的字段。但注意有Django递归的限制和性能的浪费。
6. Django >= 1.7，链式调用的select_related相当于使用可变长参数。Django < 1.7，链式调用会导致前边的select_related失效，只保留最后一个。

prefetch_related()

对于多对多字段（ManyToManyField）和一对多(ForeignKey)字段，可以使用prefetch_related()来进行优化.

prefetch_related()和select_related()的设计目的很相似，都是为了减少SQL查询的数量，但是实现的方式不一样。后者 是通过JOIN语句，在SQL查询内解决问题。但是对于多对多关系，使用SQL语句解决就显得有些不太明智，因为JOIN得到的表将会很长，会导致SQL 语句运行时间的增加和内存占用的增加。若有n个对象，每个对象的多对多字段对应Mi条，就会生成Σ(n)Mi 行的结果表。

prefetch_related()的解决方法是，分别查询每个表，然后用Python处理他们之间的关系。

如果我们要获得张三所有去过的城市.

1 2 3 4

>>> zhangs = Person.objects.prefetch_related('visitation').get(firstname=u"张",lastname=u"三") >>> for city in zhangs.visitation.all() : ...   print city ...

触发如下SQL语句

1 2 3 4 5 6 7 8 9 10

SELECT `QSOptimize_person`.`id`, `QSOptimize_person`.`firstname`, `QSOptimize_person`.`lastname`, `QSOptimize_person`.`hometown_id`, `QSOptimize_person`.`living_id` FROM `QSOptimize_person` WHERE (`QSOptimize_person`.`lastname` = '三'  AND `QSOptimize_person`.`firstname` = '张');    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);

第一条SQL查询仅仅是获取张三的Person对象，第二条比较关键，它选取关系表`QSOptimize_person_visitation`中`person_id`为张三的行，然后和`city`表内联（INNER JOIN 也叫等值连接）得到结果表.

或者我们要获得湖北的所有城市名

1 2 3

>>> hb = Province.objects.prefetch_related('city_set').get(name__iexact=u"湖北省") >>> for city in hb.city_set.all(): ...   city.name

SQL如下:

1 2 3 4 5 6 7

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);

例如要获得所有姓张的人去过的省：

1 2 3 4

>>> zhangs = Person.objects.prefetch_related('visitation__province').filter(firstname__iexact=u'张') >>> for i in zhangs: ...   for city in i.visitation.all(): ...     print city.province

要注意的是，在使用QuerySet的时候，一旦在链式操作中改变了数据库请求，之前用prefetch_related缓存的数据将会被忽略掉。这会导 致Django重新请求数据库来获得相应的数据，从而造成性能问题。这里提到的改变数据库请求指各种filter()、exclude()等等最终会改变 SQL代码的操作。而all()并不会改变最终的数据库请求，因此是不会导致重新请求数据库的。

1 2	plist = Person.objects.prefetch_related('visitation') [p.visitation.filter(name__icontains=u"市") for p in plist]

虽然已经查询结果中包含所有所需的city的信息，但因为在循环体中对Person.visitation进行了filter操作，这显然改变了数据库请求。因此这些操作会忽略掉之前缓存到的数据，重新进行SQL查询。

在Django小于1.7的版本

1 2	plist = Person.objects.prefetch_related('visitation') [[city for city in p.visitation.all() if u"市" in city.name] for p in plist]

在Django>1.7的版本

获取所有人访问过的城市中带有“武”字和“州”的城市：

1 2 3 4 5 6 7

wus = City.objects.filter(name__icontains = u"武") zhous = City.objects.filter(name__icontains = u"州") plist = Person.objects.prefetch_related(     Prefetch('visitation', queryset = wus, to_attr = "wu_city"),     Prefetch('visitation', queryset = zhous, to_attr = "zhou_city"),) [p.wu_city for p in plist] [p.zhou_city for p in plist]

None

可以通过传入一个None来清空之前的prefetch_related。就像这样：

1	prefetch_cleared_qset = qset.prefetch_related(None)

select_related()的效率要高于prefetch_related()。因此，最好在能用select_related()的地方尽量使用它，也就是说，对于ForeignKey字段，避免使用prefetch_related()。

查询家乡是湖北的人

普通做法1

1 2 3 4

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

pre做法

1 2 3 4

>>> 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())

select做法

1	people = list(Person.objects.select_related("hometown__province").filter(hometown__province__name__iexact=u"湖北省"))

　

例子2:

一个订单表:

1 2 3 4 5 6

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().

1 2 3

>>> 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_related()再调用prefetch_related()，最后再select_related()后面的表.

1 2 3

>>> 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

值得注意的是，可以在调用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()是有效的，之后的将会被无视掉。