如何在Django树形评论结构中优化SQL查询？

我开发的帖子系统带有树形评论结构，评论支持点赞功能。目前已优化了帖子主评论的SQL查询，避免了额外查询，但评论的回复仍会触发3-4次新的数据库请求。我已使用prefetch_related进行优化，请问如何进一步减少查询次数？

模型代码 #

Post模型 #

class Post(models.Model):
    author = models.ForeignKey(
        User,
        on_delete=models.CASCADE
    )
    title = models.CharField(
        max_length=200,
        null=True,
        blank=True
    )
    header_image = models.ImageField(
        null=True,
        blank=True,
        upload_to="posts/headers",
        help_text='Post will start from this image'
    )
    body = models.CharField(
        max_length=500
    )

    post_date = models.DateTimeField(
        auto_now_add=True
    )
    likes = models.ManyToManyField(
        User,
        through='UserPostRel',
        related_name='likes',
        help_text='Likes connected to the post',
    )

    def total_likes(self):
        return self.likes.count()

Comment模型 #

class Comment(models.Model):
    user = models.ForeignKey(User, related_name='comment_author', on_delete=models.CASCADE)
    post = models.ForeignKey(Post, related_name='comments', on_delete=models.CASCADE)
    body = models.TextField(max_length=255)
    comment_to_reply = models.ForeignKey("self", on_delete=models.CASCADE, null=True, blank=True, related_name='replies')
    likes = models.ManyToManyField(User, through='CommentLikeRelation', related_name='comment_likes')
    created_at = models.DateTimeField(auto_now_add=True)

    def replies_count(self):
        return self.replies.count()

    def total_likes(self):
        return self.likes.count()

    def is_a_leaf(self):
        return self.replies.exists()

    def is_a_reply(self):
        return self.comment_to_reply is not None

class CommentLikeRelation(models.Model):
    user = models.ForeignKey(User, on_delete=models.CASCADE)
    comment = models.ForeignKey(Comment, on_delete=models.CASCADE)

当前视图处理代码 #

def get(self, request, *args, **kwargs):
    current_user = request.user
    user_profile = User.objects.get(slug=kwargs.get('slug'))
    is_author = True if current_user == user_profile else False

    comment_form = CommentForm()
    posts = Post.objects.filter(author=user_profile.id)
        .select_related('author')
        .prefetch_related('likes')
        .prefetch_related(
            'comments',
            'comments__user',
            'comments__likes',
            'comments__comment_to_reply',
            'comments__replies',
            'comments__replies__user',
            'comments__replies__likes',
        )
        .order_by('-post_date')

    return render(request, self.template_name,
                  context={
                      'is_author': is_author,
                      'current_user': current_user,
                      'profile': user_profile,
                      'form': comment_form,
                      'posts': posts,
                  })

优化方案 #

1. 用Prefetch对象精准预取嵌套回复 #

当前的预取仅覆盖一级回复，若回复还有子层级会触发额外查询。通过嵌套Prefetch对象，可一次性预取所有需要的关联数据，还能自定义查询集规则：

from django.db.models import Prefetch

# 定义回复的预取配置，包含关联的用户、点赞
reply_prefetch = Prefetch(
    'replies',
    queryset=Comment.objects.select_related('user')
                             .prefetch_related('likes'),
    to_attr='prefetched_replies'
)

# 定义主评论的预取配置，嵌套回复的预取规则
comment_prefetch = Prefetch(
    'comments',
    queryset=Comment.objects.select_related('user', 'comment_to_reply')
                             .prefetch_related('likes', reply_prefetch),
    to_attr='prefetched_comments'
)

# 最终查询
posts = Post.objects.filter(author=user_profile.id)
    .select_related('author')
    .prefetch_related('likes', comment_prefetch)
    .order_by('-post_date')

注意使用to_attr指定预取结果的别名，避免与默认反向关联名称冲突，确保模板中调用的是预取数据而非触发新查询。

2. 用聚合提前计算模型方法中的统计值 #

你的replies_count()、total_likes()等模型方法，每次调用都会触发新查询。可以在查询时用聚合函数提前计算这些值：

from django.db.models import Count

# 在评论查询集中添加聚合字段
comment_queryset = Comment.objects.select_related('user', 'comment_to_reply')
    .prefetch_related('likes')
    .annotate(
        replies_count=Count('replies', distinct=True),
        total_likes=Count('likes', distinct=True),
        has_replies=Count('replies', distinct=True) > 0
    )

然后修改模型方法，优先使用预计算的字段：

class Comment(models.Model):
    # ... 其他字段 ...

    def replies_count(self):
        return self.replies_count if hasattr(self, 'replies_count') else self.replies.count()

    def total_likes(self):
        return self.total_likes if hasattr(self, 'total_likes') else self.likes.count()

    def is_a_leaf(self):
        return self.has_replies if hasattr(self, 'has_replies') else self.replies.exists()

3. 检查模板循环逻辑 #

确保模板中遍历评论回复时，使用to_attr指定的别名（比如comment.prefetched_replies），而非默认的comment.replies——后者会绕过预取数据，重新触发数据库查询。

4. 深度嵌套评论用递归CTE一次性查询 #

如果评论层级很深（超过3级），预取所有层级可能导致数据冗余，这时可以用递归CTE一次性获取所有评论的层级关系，再在内存中构建树形结构：

from django.db.models import F, Value
from django.db.models.expressions import RawSQL

# 递归CTE查询所有评论及层级路径
comments_cte = Comment.objects.filter(post__author=user_profile.id)
    .with_cte(
        'comment_tree',
        Comment.objects.annotate(
            depth=Value(1),
            path=F('id')
        ).union(
            Comment.objects.filter(comment_to_reply__in=RawSQL('SELECT id FROM comment_tree', ()))
            .annotate(
                depth=F('comment_to_reply__depth') + 1,
                path=F('comment_to_reply__path') || Value(',') || F('id')
            ),
            all=True
        )
    )

之后可以将CTE结果与帖子关联，或在Python内存中整理成树形结构，仅需一次查询即可获取所有评论数据。

内容的提问来源于stack exchange，提问作者jmur