Django ORM性能调优：高效访问嵌套外键字段

from django.db import models

class A(models.Model):
  field1 = models.CharField(max_length=100)
  field2 = models.IntegerField()

class B(models.Model):
  field3 = models.CharField(max_length=100)
  field_a = models.ForeignKey(A, on_delete=models.CASCADE)

class C(models.Model):
  field4 = models.CharField(max_length=100)
  field5 = models.IntegerField()
  field_b = models.ForeignKey(B, on_delete=models.CASCADE)

  @property
  def nested_field_a(self):
    """
    直接访问嵌套外键，可能导致N+1查询
    """
    return self.field_b.field_a

# 假设我们已经创建了一些A, B, C的实例
# from your_app.models import A, B, C
# a1 = A.objects.create(field1='A1_data', field2=1)
# b1 = B.objects.create(field3='B1_data', field_a=a1)
# c1 = C.objects.create(field4='C1_data', field5=10, field_b=b1)

# 使用 select_related 预加载 'field_b' 和 'field_b__field_a'
queryset = C.objects.select_related('field_b__field_a')
obj = queryset.first()

# 此时访问 obj.field_b.field_a 不会触发额外的SQL查询
print(f"Field A's field1: {obj.field_b.field_a.field1}")
print(f"Field A's field2: {obj.nested_field_a.field2}") # 即使是属性，只要数据已加载，也不会触发额外查询

from django.db.models import F

# 使用 annotate 提取嵌套外键的特定字段
queryset = C.objects.annotate(
    nested_a_field1=F('field_b__field_a__field1'),
    nested_a_field2=F('field_b__field_a__field2')
)
obj = queryset.first()

# 此时可以通过 obj.nested_a_field1 直接访问A模型的field1，且不会触发额外查询
print(f"Field A's field1 (via annotate): {obj.nested_a_field1}")
print(f"Field A's field2 (via annotate): {obj.nested_a_field2}")

from django.db.models import Manager, Model, F

class CManager(Manager):
    def get_queryset(self):
        # 默认在所有查询中都预加载或标注特定字段
        return (
            super().get_queryset()
            .select_related('field_b__field_a') # 示例：默认预加载
            .annotate(
                nested_a_field1=F('field_b__field_a__field1'),
                nested_a_field2=F('field_b__field_a__field2')
            )
        )

class C(Model):
    field4 = models.CharField(max_length=100)
    field5 = models.IntegerField()
    field_b = models.ForeignKey(B, on_delete=models.CASCADE)

    objects = Manager() # 默认管理器
    with_optimized_fields = CManager() # 自定义管理器

# 使用自定义管理器
obj = C.with_optimized_fields.first()
print(f"Field A's field1 (via custom manager): {obj.nested_a_field1}")

from django.db.models import F, Model, QuerySet

class CQuerySet(QuerySet):
    def with_a_fields(self):
        """标注A模型中的特定字段"""
        return self.annotate(
            a_field_1=F('field_b__field_a__field1'),
            a_field_2=F('field_b__field_a__field2')
        )

    def with_b_fields(self):
        """标注B模型中的特定字段（假设B也有需要直接提取的字段）"""
        return self.annotate(
            b_field_3=F('field_b__field3')
        )

    def prefetch_all_related(self):
        """预加载所有相关对象"""
        return self.select_related('field_b__field_a')

class C(Model):
    field4 = models.CharField(max_length=100)
    field5 = models.IntegerField()
    field_b = models.ForeignKey(B, on_delete=models.CASCADE)

    objects = CQuerySet.as_manager() # 将自定义QuerySet绑定为默认管理器

# 使用扩展的QuerySet进行链式调用
queryset = (
    C.objects
    .filter(field5__gt=5) # 示例过滤条件
    .with_a_fields()      # 标注A的字段
    .with_b_fields()      # 标注B的字段
    .prefetch_all_related() # 预加载所有关联对象
)
obj = queryset.first()

print(f"Field A's field1 (via QuerySet method): {obj.a_field_1}")
print(f"Field B's field3 (via QuerySet method): {obj.b_field_3}")
print(f"Field A's field2 (via QuerySet method and select_related): {obj.field_b.field_a.field2}")