Django ORM数据库查询N + 1问题性能优化

Django ORM数据库查询N + 1问题性能优化

• 模型类

  # device 表，表示当前网络中纳管的物理设备。
  class Device(models.Model):
      name = models.CharField(max_length=100, unique=True)  # 添加设备时的设备名
      hostname = models.CharField(max_length=100, null=True)  # 从设备中获取的hostname
      ip_address = models.CharField(max_length=100, null=True)  # 设备管理IP
  
  # interface 表，表示物理设备拥有的接口。    
  class Interface(models.Model):
      device = models.ForeignKey(Device, on_delete=models.PROTECT, null=False，related_name='interfaces')) # 属于哪台设备
      name = models.CharField(max_length=100)  # 端口名
      collect_status = models.CharField(max_length=30, default='active')
      class Meta:
          unique_together = ("device", "name")  # 联合主键
  
  # interface_extension 表，和 interface 表是一对一关系，由于 interface 属性过多，用于存储一些不太常用的接口属性。      
  class InterfaceExtension(models.Model):
      interface = models.OneToOneField(
          Interface, on_delete=models.PROTECT, null=False, related_name='ex_info')
          
      endpoint_device_id = models.ForeignKey( # 绑定了的终端设备
          Device, db_column='endpoint_device_id',
          on_delete=models.PROTECT, null=True, blank=True)
          
      endpoint_interface_id = models.ForeignKey(
          Interface, db_column='endpoint_interface_id', on_delete=models.PROTECT, # 绑定了的终端设备的接口
          null=True, blank=True)
  

• 使用queryset来查询

  interfaces = Interface.objects.filter()[:5] # hit once database 
  
  for interface in interfaces:  # 这里每遍历一次，执行一次`select name from device where id = {}`
      print('interface_name: ', interface.name,
            'device_name: ', interface.device.name) # hit database again
  

  上面第一句取前 5 条 interface 记录，对应的 raw sql 就是 select * from interface limit 5; 没有任何问题。

  但下面取接口所属的设备名时，就会出现反复调用数据库情况：当遍历到一个接口，就会通过获取的 device_id 去数据库查询 device_name. 对应的 raw sql 类似于：select name from device where id = {}.

  也就是说，假如有 10 万个接口，就会执行 10 万次查询，性能的消耗可想而知。算上之前查找所有接口的一次查询，合称为 N + 1 次查询问题。

• select_related

  在调用 select_related() 方法时，Queryset 会将所属 Model 的外键关系，一起查询。相当于 raw sql 中的 join . 一次将所有数据同时查询出来。select_related() 主要的应用场景是：某个 model 中关联了外键（多对一），或者有 1 对 1 的关联关系情况。

  interfaces = Interface.objects.select_related('device').filter()[:5] # hit once database
  
  for interface in interfaces:
      print('interface_name: ', interface.name,
           'device_name: ', interface.device.name) # don't need to hit database again
  

  上面的查询 SQL 就类似于：SELECT xx FROMinterface INNER JOIN device ON interface.device_id = device.id limit5，注意这里是 inner join 是因为是非空外键。

  select_related() 还支持一个 model 中关联了多个外键的情况：如拓展接口，查询绑定的设备名称和接口名称：

  # 下面的查询类似于 SELECT XXX FROM interface_extension LEFT OUTER JOIN device ON (interface_extension.endpoint_device_id=device.id)，这里由于是可空外键，所以是 left join.
  LEFT OUTER JOIN interface ON (interface_extension.endpoint_interface_id=interface.id)
  LIMIT 5
  ex_interfaces = InterfaceExtension.objects.select_related(
      'endpoint_device_id', 'endpoint_interface_id').filter()[:5] 
  
  # or
  
  ex_interfaces = InterfaceExtension.objects.select_related(
      'endpoint_device_id').select_related('endpoint_interface_id').filter()[:5] 
  

• prefetch_related

  prefetch_related 和 select_related 一样都是为了避免大量查询关系时的数据库调用。只不过为了避免多表 join 后产生的巨大结果集以及效率问题， 所以 select_related 比较偏向于外键（多对一）和一对一的关系。

  而 prefetch_related 的实现方式则类似于之前 raw sql 的第二种，分开查询之间的关系，然后通过 python 代码，将其组合在一起。所以 prefetch_related 可以很好的支持一对多或者多对多的关系。

  interfaces = Interface.objects.prefetch_related('device').filter()[:5] # hit twice database
  
  for interface in interfaces:
      print('interface_name: ', interface.name,
           'device_name: ', interface.device.name) # don't need to hit database again
  

  执行逻辑也是：

  1. “SELECT * FROM device”
  2. “SELECT * FROM interface where device_id in (…)”
  3. 然后通过 python 代码将之间的关系组合起来。

  如果换成多对多的关系，在第二步会变为 join 后在 in，具体可以直接尝试。

  但有一点需要注意，当使用的 QuerySet 有新的逻辑查询时， prefetch_related 的结果不会生效，还是会去查询数据库：

  如在查询所有设备具有哪些接口上，增加一个条件，接口的状态是 active 的接口

  devices = Device.objects.prefetch_related('interfaces').filter()[:5] # hit twice database
  for device in devices:
      print('device_name: ', device.name,
           'interfaces:', device.interfaces.filter(collect_status='active')) # hit dababase repeatly
  

  执行逻辑变成：

  1. “SELECT * FROM device”
  2. “SELECT * FROM interface where device_id in (…)”
  3. 一直重复 device 的数量次： “SELECT * FROM interface where device_id = xx and collect_status=‘active’;”
  4. 最后通过 python 组合到一起。

  原因在于：之前的 prefetch_related 查询，并不包含判断 collect_status 的状态。所以对于 QuerySet 来说，这是一个新的查询。所以会重新执行

  可以利用 Prefetch 对象 进一步控制并解决上面的问题：

  devices = Device.objects.prefetch_related(
      Prefetch('interfaces', queryset=Interface.objects.filter(collect_status='active'))
      ).filter()[:5] # hit twice database
  for device in devices:
      print('device_name: ', device.name, 'interfaces:', device.interfaces)
  

  执行逻辑变成：

  1. “SELECT * FROM device”
  2. “SELECT * FROM interface where device_id in (…) and collect_status = ‘active’;”
  3. 最后通过 python 组合到一起。

  可以通过 Prefetch 对象的 to_attr，来改变之间关联关系的名称：

  devices = Device.objects.prefetch_related(
      Prefetch('interfaces', queryset=Interface.objects.filter(collect_status='active'), to_attr='actived_interfaces')
      ).filter()[:5] # hit twice database
  for device in devices:
      print('device_name: ', device.name, 'interfaces:', device.actived_interfaces) 
  

  可以看到通过 Prefetch，可以实现控制关联那些有关系的对象。

  最后，对于一些关联结构较为复杂的情况，可以将 prefetch_related 和 select_related 组合到一起，从而控制查询数据库的逻辑。

  比如，想要查询全部接口的信息，及其设备名称，以及拓展接口中绑定了对端设备和接口的信息。

  queryset = Interface.objects.select_related('ex_info').prefetch_related(
              'ex_info__endpoint_device_id', 'ex_info__endpoint_interface_id')
  

  执行逻辑如下：

  1. SELECT XXX FROM interface LEFT OUTER JOIN interface_extension ON (interface.id=interface_extension .interface_id)
  2. SELECT XXX FROM device where id in ()
  3. SELECT XXX FROM interface where id in ()
  4. 最后通过 python 组合到一起。

  第一步, 由于 interface 和 interface_extension 是 1 对 1 的关系，所以使用 select_related 将其关联起来。

  第二三步：虽然 interface_extension 和 endpoint_device_id 和 endpoint_interface_id 是外键关系，如果继续使用 select_related 则会进行 4 张表连续 join，所以将其换成 prefetch_related. 对于 interface_extension 外键关联的属性使用 in 查询，因为interface_extension 表的属性并不是经常使用的。