Django聚合数据仪表盘加载缓慢且缓存数据不显示问题

问题背景 #

生产环境中Django数据聚合仪表盘页面加载极慢（最长超30秒），采用以下优化方案后，日志确认缓存已存入正确数据，但页面始终空白：

• 用Django缓存框架缓存order_dashboard视图完整上下文
• 对Assignment模型使用only('status')限制查询字段
• 基于django-background-tasks每25秒后台更新缓存，将聚合逻辑移至后台

核心问题分析 #

1. QuerySet序列化丢失注解字段：缓存存储的是list(assignments)，但assignments是带注解的QuerySet，序列化后仅保留only('status')指定的字段，所有通过annotate添加的聚合字段（如total_packaged、operation_progress等）全部丢失，导致模板无数据可渲染。
2. 同步调用后台任务的时序问题：视图中调用后台任务后立即读取缓存，此时任务可能尚未执行完毕，缓存仍为空，上下文缺失。
3. 模板关联对象触发N+1查询：模板直接访问assignment.order，但缓存的Assignment实例未预取关联对象，即使修复缓存问题，也会回到加载缓慢的状态。

修复方案 #

1. 缓存字典而非模型实例 #

将QuerySet转换为包含所需字段的字典列表，确保注解字段被完整保留：
修改tasks.py中构建上下文的代码：

# 替换原context中的assignments部分
assignment_dicts = []
for assn in assignments:
    assignment_dicts.append({
        'order_id': assn.order.id,
        'order_str': str(assn.order),
        'total_actual_cutting': assn.total_actual_cutting,
        'operation_progress': assn.operation_progress,
        'total_cleaned': assn.total_cleaned,
        'total_ironed': assn.total_ironed,
        'total_packaged': assn.total_packaged
    })

context = {
    'assignments': assignment_dicts,
    'total_paid_amount': total_paid_amount,
    'remaining_amount': remaining_amount,
    'active_orders_count': active_orders_count,
    'completed_count': completed_count,
    'total_defect_count': total_defect_count,
    'total_actual_cutting': assignments.aggregate(total=Sum('total_actual_cutting'))['total'] or 0,
}

2. 修正视图缓存读取逻辑 #

当缓存为空时，同步执行聚合逻辑确保立即获取上下文，同时启动后台任务维持后续缓存更新：
修改views.py：

def order_dashboard(request):
    cache_key = "order_dashboard"
    context = cache.get(cache_key)

    if not context:
        # 同步执行聚合逻辑，确保立即拿到数据
        start_time = time.time()

        assignments = Assignment.objects.annotate(
            total_packaged=Coalesce(Sum('packaging__quantity', distinct=True), Value(0)),
            total_ironed=Coalesce(Sum('iron__quantity', distinct=True), Value(0)),
            total_cleaned=Coalesce(Sum('cleaning__quantity', distinct=True), Value(0)),
            total_completed_operations=Coalesce(Sum('operationlog__operationitem__quantity'), Value(0)),
            total_actual_cutting=Coalesce(Sum('cutting__actual_quantity', distinct=True), Value(0)),
            total_planned_operations=Coalesce(
                Sum(F('cutting__order_item__quantity') *
                    F('cutting__order_item__product__technological_map__operation__details_quantity_per_product')),
                Value(0)
            ),
            operation_progress=Case(
                When(total_planned_operations=0, then=Value(0.0)),
                default=(F('total_completed_operations') * 100.0) / F('total_planned_operations'),
                output_field=FloatField()
            ),
            total_defect=Coalesce(Sum('defect__quantity', distinct=True), Value(0)),
        ).select_related('order')  # 预取order关联对象，避免N+1查询

        assignment_statuses = assignments.values('status').annotate(count=Count('id'))
        completed_count = sum(status['count'] for status in assignment_statuses if status['status'] == 'completed')

        total_paid_amount = Receipt.objects.aggregate(total=Sum('paid_amount'))['total'] or 0
        remaining_amount = CustomerDebt.objects.filter(debt_amount__gt=0).aggregate(total=Sum('debt_amount'))['total'] or 0
        active_orders_count = Order.objects.filter(status__in=['new', 'in_progress']).count()
        total_defect_count = assignments.aggregate(total=Sum('total_defect'))['total'] or 0

        # 转换为字典列表
        assignment_dicts = []
        for assn in assignments:
            assignment_dicts.append({
                'order_id': assn.order.id,
                'order_str': str(assn.order),
                'total_actual_cutting': assn.total_actual_cutting,
                'operation_progress': assn.operation_progress,
                'total_cleaned': assn.total_cleaned,
                'total_ironed': assn.total_ironed,
                'total_packaged': assn.total_packaged
            })

        context = {
            'assignments': assignment_dicts,
            'total_paid_amount': total_paid_amount,
            'remaining_amount': remaining_amount,
            'active_orders_count': active_orders_count,
            'completed_count': completed_count,
            'total_defect_count': total_defect_count,
            'total_actual_cutting': assignments.aggregate(total=Sum('total_actual_cutting'))['total'] or 0,
        }

        # 存入缓存
        cache.set(cache_key, context, 20)
        # 启动后台任务，后续自动更新缓存
        update_order_dashboard_cache(repeat=25, repeat_until=None)

        total_time = time.time() - start_time
        print(f"首次同步生成缓存耗时: {total_time:.2f} seconds")
        print('缓存为空，已同步生成并启动后台更新')

    return render(request, 'OrderDashboard.html', context)

3. 调整模板适配字典数据 #

修改OrderDashboard.html，使用字典中的字段：

{% for assignment in assignments %}
<tr>
    <td class="py-2 px-4 border-b border-gray-200 text-sm text-gray-700">
    <a href="{% url 'hrm:detail_order' assignment.order_id %}" class="text-blue-600 hover:underline">
        {{ assignment.order_str }}
    </a>
</td>
    <td class="py-2 px-4 border-b border-gray-200 text-sm text-gray-700">{{ assignment.total_actual_cutting }}</td>
    <td class="py-2 px-4 border-b border-gray-200 text-sm text-gray-700">
         <div class="relative pt-1">
          <div class="flex mb-2 items-center justify-between">
           <div>
            <span class="text-xs font-semibold inline-block py-1 px-2 uppercase rounded-full text-purple-600 bg-purple-200">
             {{ assignment.operation_progress|default_if_none:"0"|floatformat:0}}%
            </span>
           </div>
          </div>
          <div class="overflow-hidden h-2 mb-4 text-xs flex rounded bg-purple-200">
           <div class="shadow-none flex flex-col text-center whitespace-nowrap text-white justify-center bg-purple-500" style="width: {{ assignment.operation_progress|floatformat:0 }}%">
           </div>
          </div>
         </div>
        </td>
     <td class="py-2 px-4 border-b border-gray-200 text-sm text-gray-700">{{ assignment.total_cleaned }}</td>
     <td class="py-2 px-4 border-b border-gray-200 text-sm text-gray-700">{{ assignment.total_ironed }}</td>
     <td class="py-2 px-4 border-b border-gray-200 text-sm text-gray-700">{{ assignment.total_packaged}}</td>
</tr>
{% endfor %}

4. 优化后台任务的QuerySet #

修改tasks.py中的update_order_dashboard_cache函数，同样转换为字典列表，并添加select_related('order')避免N+1查询：

@background(schedule=25) 
def update_order_dashboard_cache():
    cache_key = "order_dashboard"
    cache_timeout = 20

    start_time = time.time()

    assignments = Assignment.objects.annotate(
        total_packaged=Coalesce(Sum('packaging__quantity', distinct=True), Value(0)),
        total_ironed=Coalesce(Sum('iron__quantity', distinct=True), Value(0)),
        total_cleaned=Coalesce(Sum('cleaning__quantity', distinct=True), Value(0)),
        total_completed_operations=Coalesce(Sum('operationlog__operationitem__quantity'), Value(0)),
        total_actual_cutting=Coalesce(Sum('cutting__actual_quantity', distinct=True), Value(0)),
        total_planned_operations=Coalesce(
            Sum(F('cutting__order_item__quantity') *
                F('cutting__order_item__product__technological_map__operation__details_quantity_per_product')),
            Value(0)
        ),
        operation_progress=Case(
            When(total_planned_operations=0, then=Value(0.0)),
            default=(F('total_completed_operations') * 100.0) / F('total_planned_operations'),
            output_field=FloatField()
        ),
        total_defect=Coalesce(Sum('defect__quantity', distinct=True), Value(0)),
    ).select_related('order')  # 预取order关联对象

    assignment_statuses = assignments.values('status').annotate(count=Count('id'))
    completed_count = sum(status['count'] for status in assignment_statuses if status['status'] == 'completed')

    total_paid_amount = Receipt.objects.aggregate(total=Sum('paid_amount'))['total'] or 0
    remaining_amount = CustomerDebt.objects.filter(debt_amount__gt=0).aggregate(total=Sum('debt_amount'))['total'] or 0
    active_orders_count = Order.objects.filter(status__in=['new', 'in_progress']).count()
    total_defect_count = assignments.aggregate(total=Sum('total_defect'))['total'] or 0

    # 转换为字典列表
    assignment_dicts = []
    for assn in assignments:
        assignment_dicts.append({
            'order_id': assn.order.id,
            'order_str': str(assn.order),
            'total_actual_cutting': assn.total_actual_cutting,
            'operation_progress': assn.operation_progress,
            'total_cleaned': assn.total_cleaned,
            'total_ironed': assn.total_ironed,
            'total_packaged': assn.total_packaged
        })

    context = {
        'assignments': assignment_dicts,
        'total_paid_amount': total_paid_amount,
        'remaining_amount': remaining_amount,
        'active_orders_count': active_orders_count,
        'completed_count': completed_count,
        'total_defect_count': total_defect_count,
        'total_actual_cutting': assignments.aggregate(total=Sum('total_actual_cutting'))['total'] or 0,
    }

    cache.set(cache_key, context, cache_timeout)
    total_time = time.time() - start_time
    print(f"缓存更新耗时: {total_time:.2f} seconds")
    print(f"缓存键: {cache_key} - 数据已更新")

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