如何解决Django中相似查询过多导致视图响应缓慢的问题

我的Django视图响应时间超80秒，通过django-debug-toolbar的SQL标签看到总共有422条查询，其中210条是相似查询。这个视图要计算本年度35周内，每种货物类型（commodity）和卡车类型（truck_type）的周费率（收入/里程）。以下是视图代码，求查询优化方案：

def rates_weekly(request):
    tenant = request.tenant
    loads = Load.objects.all().exclude(load_status='Cancelled').values('billable_amount_after_accessorial', 'total_miles')
    from fleetdata.utils import start_week_nr

    def calculate_rate(year, week_num, commodity, truck_type):
        start_of_week = start_week_nr(year, week_num)
        end_of_week = start_of_week + datetime.timedelta(days=7)
        relevant_loads = loads.filter(drop_date__gte=start_of_week, drop_date__lt=end_of_week, truck_type=truck_type, commodity=commodity)
        revenue = relevant_loads.aggregate(Sum("billable_amount_after_accessorial"))['billable_amount_after_accessorial__sum']
        miles = relevant_loads.aggregate(Sum("total_miles"))['total_miles__sum']
        if revenue and miles is not None:
            rate = revenue / miles
        else:
            rate = 0
        return rate

    rates = {}
    for week in range(1, CURRENT_WEEK_CUSTOM+6):
        rate_ct_reefer = calculate_rate(CURRENT_YEAR, week, 'Reefer', 'CT')
        rate_ct_dryvan = calculate_rate(CURRENT_YEAR, week, 'DryVan', 'CT')
        rate_ct_flatbed = calculate_rate(CURRENT_YEAR, week, 'Flat Bed', 'CT')
        rate_oo_reefer = calculate_rate(CURRENT_YEAR, week, 'Reefer', 'OO')
        rate_oo_dryvan = calculate_rate(CURRENT_YEAR, week, 'DryVan', 'OO')
        rate_oo_flatbed = calculate_rate(CURRENT_YEAR, week, 'Flat Bed', 'OO')

        rates[str(CURRENT_YEAR) + '-' + f"{week:02d}"] = {}
        rates[str(CURRENT_YEAR) + '-' + f"{week:02d}"]['rate_ct_reefer'] = rate_ct_reefer
        rates[str(CURRENT_YEAR) + '-' + f"{week:02d}"]['rate_ct_dryvan']= rate_ct_dryvan
        rates[str(CURRENT_YEAR) + '-' + f"{week:02d}"]['rate_ct_flatbed']= rate_ct_flatbed
        rates[str(CURRENT_YEAR) + '-' + f"{week:02d}"]['rate_oo_reefer'] = rate_oo_reefer
        rates[str(CURRENT_YEAR) + '-' + f"{week:02d}"]['rate_oo_dryvan']= rate_oo_dryvan
        rates[str(CURRENT_YEAR) + '-' + f"{week:02d}"]['rate_oo_flatbed']= rate_oo_flatbed


    list_weeks = list(rates.keys())
    list_rates = list(rates.values())
    list_ct_reefer_rates = [ x['rate_ct_reefer'] for x in list_rates ]
    list_ct_dryvan_rates = [ x['rate_ct_dryvan'] for x in list_rates ]
    list_ct_flatbed_rates = [ x['rate_ct_flatbed'] for x in list_rates ]
    list_oo_reefer_rates = [ x['rate_oo_reefer'] for x in list_rates ]
    list_oo_dryvan_rates = [ x['rate_oo_dryvan'] for x in list_rates ]
    list_oo_flatbed_rates = [ x['rate_oo_flatbed'] for x in list_rates ]

    list_weeks_dt = [ datetime.datetime.strptime(date + '-1', '%Y-%W-%w') for date in list_weeks ]
    dict_reefer_dryvan_flatbed = { 
        'weeks': list_weeks_dt, 
        'rates_ct_reefer': list_ct_reefer_rates, 'rates_ct_dryvan': list_ct_dryvan_rates, 'rates_ct_flatbed': list_ct_flatbed_rates,
        'rates_oo_reefer': list_oo_reefer_rates, 'rates_oo_dryvan': list_oo_dryvan_rates, 'rates_oo_flatbed': list_oo_flatbed_rates
    }

    fig_ct = px.line(dict_reefer_dryvan_flatbed, x='weeks', y=['rates_ct_reefer', 'rates_ct_dryvan', 'rates_ct_flatbed'])
    fig_ct.update_layout(
        xaxis_tickformat = '%Y-%W',
        xaxis = dict(tickmode = 'linear', dtick = 604800000)
        )
    fig_ct = fig_ct.to_html()

    fig_oo = px.line(dict_reefer_dryvan_flatbed, x='weeks', y=['rates_oo_reefer', 'rates_oo_dryvan', 'rates_oo_flatbed'])
    fig_oo.update_layout(
        xaxis_tickformat = '%Y-%W',
        xaxis = dict(tickmode = 'linear', dtick = 604800000)
        )
    fig_oo = fig_oo.to_html()

context = {
    'tenant': tenant,
    'CURRENT_YEAR': CURRENT_YEAR,
    'CURRENT_WEEK_CUSTOM': CURRENT_WEEK_CUSTOM,
    'rates': rates,
    'fig_ct': fig_ct,
    'fig_oo': fig_oo,
}
return render(request, template_name='loads/rates-weekly.html', context=context)

优化方案 #

核心思路：批量查询替代循环单查 #

当前代码的问题是循环中反复执行相似的单条查询，每个周、每种组合都发起两次聚合查询（收入和里程），直接导致查询量爆炸。优化方向是用一次数据库查询完成所有分组统计，再在内存中处理计算。

1. 重写核心查询逻辑 #

替换原来的循环查询，用Django的annotate和values分组，一次性获取所有周、货物类型、卡车类型的聚合数据：

from django.db.models import Sum, F
from datetime import datetime
from django.db.models.functions import ExtractWeek

def rates_weekly(request):
    tenant = request.tenant
    from fleetdata.utils import start_week_nr

    # 定义统计的周范围
    start_week = 1
    end_week = CURRENT_WEEK_CUSTOM + 5

    # 一次性获取所有分组统计数据
    loads_stats = Load.objects.filter(
        load_status__ne='Cancelled',
        drop_date__year=CURRENT_YEAR,
        drop_date__week__range=(start_week, end_week)
    ).values(
        'truck_type', 'commodity', week=F('drop_date__week')
    ).annotate(
        total_revenue=Sum('billable_amount_after_accessorial'),
        total_miles=Sum('total_miles')
    ).order_by('week', 'truck_type', 'commodity')

    # 将统计结果转换为字典，方便快速匹配
    stats_dict = {}
    # 预定义所有组合的默认值
    key_map = {
        ('CT', 'Reefer'): 'rate_ct_reefer',
        ('CT', 'DryVan'): 'rate_ct_dryvan',
        ('CT', 'Flat Bed'): 'rate_ct_flatbed',
        ('OO', 'Reefer'): 'rate_oo_reefer',
        ('OO', 'DryVan'): 'rate_oo_dryvan',
        ('OO', 'Flat Bed'): 'rate_oo_flatbed',
    }

    # 初始化所有周的默认费率为0
    for week in range(start_week, end_week + 1):
        week_key = f"{CURRENT_YEAR}-{week:02d}"
        stats_dict[week_key] = {key: 0 for key in key_map.values()}

    # 填充有数据的组合
    for stat in loads_stats:
        week_key = f"{CURRENT_YEAR}-{stat['week']:02d}"
        map_key = (stat['truck_type'], stat['commodity'])
        if map_key not in key_map:
            continue
        
        revenue = stat['total_revenue'] or 0
        miles = stat['total_miles'] or 0
        rate = revenue / miles if miles != 0 else 0
        stats_dict[week_key][key_map[map_key]] = rate

    # 后续图表生成逻辑保持不变
    list_weeks = sorted(stats_dict.keys())
    list_rates = [stats_dict[week] for week in list_weeks]
    list_ct_reefer_rates = [x['rate_ct_reefer'] for x in list_rates]
    list_ct_dryvan_rates = [x['rate_ct_dryvan'] for x in list_rates]
    list_ct_flatbed_rates = [x['rate_ct_flatbed'] for x in list_rates]
    list_oo_reefer_rates = [x['rate_oo_reefer'] for x in list_rates]
    list_oo_dryvan_rates = [x['rate_oo_dryvan'] for x in list_rates]
    list_oo_flatbed_rates = [x['rate_oo_flatbed'] for x in list_rates]

    list_weeks_dt = [datetime.strptime(date + '-1', '%Y-%W-%w') for date in list_weeks]
    dict_reefer_dryvan_flatbed = { 
        'weeks': list_weeks_dt, 
        'rates_ct_reefer': list_ct_reefer_rates, 'rates_ct_dryvan': list_ct_dryvan_rates, 'rates_ct_flatbed': list_ct_flatbed_rates,
        'rates_oo_reefer': list_oo_reefer_rates, 'rates_oo_dryvan': list_oo_dryvan_rates, 'rates_oo_flatbed': list_oo_flatbed_rates
    }

    fig_ct = px.line(dict_reefer_dryvan_flatbed, x='weeks', y=['rates_ct_reefer', 'rates_ct_dryvan', 'rates_ct_flatbed'])
    fig_ct.update_layout(
        xaxis_tickformat = '%Y-%W',
        xaxis = dict(tickmode = 'linear', dtick = 604800000)
        )
    fig_ct = fig_ct.to_html()

    fig_oo = px.line(dict_reefer_dryvan_flatbed, x='weeks', y=['rates_oo_reefer', 'rates_oo_dryvan', 'rates_oo_flatbed'])
    fig_oo.update_layout(
        xaxis_tickformat = '%Y-%W',
        xaxis = dict(tickmode = 'linear', dtick = 604800000)
        )
    fig_oo = fig_oo.to_html()

    context = {
        'tenant': tenant,
        'CURRENT_YEAR': CURRENT_YEAR,
        'CURRENT_WEEK_CUSTOM': CURRENT_WEEK_CUSTOM,
        'rates': stats_dict,
        'fig_ct': fig_ct,
        'fig_oo': fig_oo,
    }
    return render(request, template_name='loads/rates-weekly.html', context=context)

2. 添加数据库复合索引 #

为了加速过滤和分组查询，给Load模型添加复合索引：

class Load(models.Model):
    # 原有字段...
    load_status = models.CharField(...)
    drop_date = models.DateTimeField(...)
    truck_type = models.CharField(...)
    commodity = models.CharField(...)

    class Meta:
        indexes = [
            models.Index(fields=['drop_date', 'load_status', 'truck_type', 'commodity']),
        ]

该索引覆盖了查询中的过滤条件和分组字段，能大幅降低数据库查询时间。

3. 额外细节优化 #

• 将from fleetdata.utils import start_week_nr移到文件顶部，避免在函数内部重复导入。
• 处理空值时增加除数不为0的判断，避免运行时错误。
• 若drop_date是带时区字段，需确保项目时区设置正确，避免周数计算偏差。

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