如何在Django社交媒體應用中高效實現用戶動態流查詢？

我是編程與Django新手，正在開發首個社交媒體應用，希望找到高效檢索用戶動態流的方案，減輕數據庫負載，同時學習構建高效API視圖的最佳實踐。現提供當前的User模型、Follow模型及用戶動態流API視圖實現：

User模型 #

class User(AbstractUser):
    # Additional fields for user profiles
    profile_picture = models.ImageField(upload_to='profiles/', null=True, blank=True)  # Profile picture for the user
    bio = models.TextField(max_length=300, blank=True)  # Short bio or description for the user
    contact_information = models.CharField(max_length=100, blank=True)  # Contact information for the user
    profile_privacy = models.CharField(max_length=10, choices=[('public', 'Public'), ('private', 'Private')], default='public')  # Privacy setting for user profile
    num_followers = models.PositiveIntegerField(default=0)  # counter to keep track of users num of followers
    num_following = models.PositiveIntegerField(default=0)  # counter to keep track of num of users a user is following
    num_posts = models.PositiveIntegerField(default=0)  # counter to keep track of num of posts made by the user

Follow模型 #

class Follow(models.Model):
    FOLLOW_STATUS_CHOICES = [
        ('pending', 'Pending'),
        ('accepted', 'Accepted'),
    ]

    follower = models.ForeignKey(User, on_delete=models.CASCADE, related_name='following')  # ForeignKey User that is following another User
    following = models.ForeignKey(User, on_delete=models.CASCADE, related_name='follower')  # ForeignKey User that is being followed by another User
    follow_status = models.CharField(max_length=10, choices=FOLLOW_STATUS_CHOICES, default='pending')

    class Meta:
        unique_together = ('follower', 'following')  # Ensure unique follower-following pairs
        indexes = [
            models.Index(fields=['follower', 'following', 'follow_status']),  # Combined index
        ]

用戶動態流API視圖 #

# API view to get posts from the users that the current user follows
@api_view(['GET'])
@permission_classes([IsAuthenticated])
def user_feed(request):
    # Obtains all the users the requesting user is following
    following_users = User.objects.filter(follower__follower=request.user, follower__follow_status='accepted')

    # Set a default page size of 20 returned datasets per page
    default_page_size = 20
    # Utility function to get current page number and page size from the request's query parameters and calculate the pagination slicing indeces
    start_index, end_index, validation_response = get_pagination_indeces(request, default_page_size)
    if validation_response:
        return validation_response

    # fetch the posts from the users in following_users
    feed_posts = Post.objects.filter(user__in=following_users)[start_index:end_index]

    # The context is used to pass the request to the PostSerializer to perform custom logic
    serializer = PostSerializer(feed_posts, many=True, context={'request': request})

    return Response(serializer.data, status=status.HTTP_200_OK)

當前實現會先查詢當前用戶關注的所有用戶，再獲取這些用戶的Post，但擔心關注人數較多時查詢效率低下，請問如何優化該實現？

1. 合併查詢，消除多餘中間查詢 #

當前代碼分兩次查詢數據庫（先撈關列表，再撈動態），且user__in在數據量大時性能劣化。直接通過Follow模型聯表過濾Post，將兩次查詢合併為一次：

feed_posts = Post.objects.filter(
    user__follower__follower=request.user,
    user__follower__follow_status='accepted'
).order_by('-created_at')  # 按發布時間倒序，符合動態流邏輯

這樣會生成一條高效的聯表SQL，避免多餘數據傳輸。

2. 替換自實現分頁，使用Django官方分頁器 #

當前用Python層切片[start_index:end_index]會先撈出所有符合條件的Post再裁剪，數據量大時極耗內存。改用DRF官方分頁器，在數據庫層執行LIMIT/OFFSET（或鍵集分頁）：

from rest_framework.pagination import PageNumberPagination

class FeedPagination(PageNumberPagination):
    page_size = 20
    page_size_query_param = 'page_size'
    max_page_size = 100  # 限制最大頁面大小，防止惡意請求

@api_view(['GET'])
@permission_classes([IsAuthenticated])
def user_feed(request):
    queryset = Post.objects.filter(
        user__follower__follower=request.user,
        user__follower__follow_status='accepted'
    ).order_by('-created_at')

    paginator = FeedPagination()
    result_page = paginator.paginate_queryset(queryset, request)
    serializer = PostSerializer(result_page, many=True, context={'request': request})
    
    return paginator.get_paginated_response(serializer.data)

3. 優化索引設計 #

調整Follow模型的索引字段順序，優先放置查詢時的過濾字段：

class Meta:
    indexes = [
        models.Index(fields=['follower', 'follow_status', 'following']),
    ]

同時給Post模型添加針對動態查詢的複合索引：

class Post(models.Model):
    # 你的Post字段
    user = models.ForeignKey(User, on_delete=models.CASCADE)
    created_at = models.DateTimeField(auto_now_add=True)
    
    class Meta:
        indexes = [
            models.Index(fields=['user', '-created_at']),
        ]

這些索引能大幅加速聯表和排序查詢的速度。

4. 解決N+1查詢問題 #

如果PostSerializer中會序列化用戶信息等關聯對象，一定要用select_related提前加載關聯數據：

queryset = Post.objects.filter(
    user__follower__follower=request.user,
    user__follower__follow_status='accepted'
).select_related('user').order_by('-created_at')

避免序列化時每個Post都單獨查一次用戶數據的性能問題。

5. 大規模場景：預計算動態流+緩存 #

如果後續用戶量和動態量暴增，可以採用預計算方案：

• 當用戶發布新Post時，將Post推送到所有關注者的Redis有序集合（按時間戳排序）
• 查詢動態流時直接從Redis讀取，無需每次查詢數據庫
• 對於大V用戶，可採用延時加載或分批次推送，避免瞬間壓力過大

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