Django paginator分页前循环耗时过长，如何优化解决该性能问题？

核心问题出在两个点：一是每次分页前都要遍历全量Organization数据做对象映射，二是ORM查询存在严重的N+1问题，循环内每一条数据都会触发多次额外的数据库查询。可以按照以下步骤优化：

1. 分页逻辑前置，仅处理当前页数据 #

Django的Paginator支持直接传入QuerySet对象，不需要提前加载全量数据：

• QuerySet是懒加载的，Paginator计算总页数时只会执行COUNT统计查询，不会拉取全量表数据
• 分页获取指定页数据时，会自动给QuerySet加上LIMIT和OFFSET，仅查询当前页需要的10条数据
• 拿到当前页的10条Organization对象后再做映射，每次仅循环10次，不会遍历全量数据

2. 优化ORM查询，解决N+1问题 #

原代码中访问跨表关联字段（如o.organizationmainapp.application）、循环内调用get_total_sales都会触发额外的数据库查询，可做如下优化：

• 用select_related/prefetch_related提前加载所有需要的关联表数据，一次性查完所有关联字段
• 把get_total_sales的单条查询改成批量查询，或者直接用annotate在数据库层完成聚合统计，避免循环内查库
• 同一次循环内get_total_sales被调用了2次，可缓存结果避免重复查询

修改后代码示例 #

视图代码 #

class CustomersView(AdminStaffRequiredMixin, TemplateView):
    template_name = 'customers/tables.html'

    def get(self, request, activeCustumers, *args, **kwargs):
        controller = CustomerViewController()
        date1 = request.GET.get('date1', 1)
        date2 = request.GET.get('date2', 1)
        # 先拿到过滤后的QuerySet，不做加载
        org_queryset = controller.get_filtered_orgs(activeCustumers)
        page = request.GET.get('page', 1)
        # 直接给Paginator传QuerySet
        paginator = Paginator(org_queryset, 10)
        try:
            current_page_orgs = paginator.page(page)
        except PageNotAnInteger:
            current_page_orgs = paginator.page(1)
        except EmptyPage:
            current_page_orgs = paginator.page(paginator.num_pages)
        # 仅映射当前页的10条数据
        customers_data = controller.map_orgs_to_customer(current_page_orgs, date1, date2)
        # 把分页信息附在返回结果上，保证模板分页逻辑兼容
        customers_data.paginator = current_page_orgs.paginator
        customers_data.number = current_page_orgs.number
        customers_data.has_previous = current_page_orgs.has_previous
        customers_data.has_next = current_page_orgs.has_next
        customers_data.previous_page_number = current_page_orgs.previous_page_number
        customers_data.next_page_number = current_page_orgs.next_page_number

        context = {'object_list': customers_data, 'num': paginator.count}
        return render(request, self.template_name, context)

控制器代码 #

class CustomerViewController(object):
    # 新增方法：仅获取过滤后的Org QuerySet
    def get_filtered_orgs(self, get_active_custumers):
        base_query = Organization.objects.select_related(
            "organizationmainapp",
            "organizationmainapp__application",
            "organizationmainapp__application__applicationselectedplan",
            "organizationmainapp__application__applicationselectedplan__price_plan",
            "organizationmainapp__application__appinfoforstore",
            "organizationmainapp__application__user"
        )
        if get_active_custumers == 1:
            return base_query.filter(
                organizationmainapp__application__appinfoforstore__status=2,
                deleted=False,
                status=True, 
                to_deleted=False
            )
        return base_query.all()
    
    # 新增方法：仅映射传入的Org列表
    def map_orgs_to_customer(self, org_list, date1, date2):
        data = []
        dates = self.set_dates(date1, date2)
        # 提前批量查询所有org的销售数据，避免循环内查库（这里根据你自己的get_total_sales逻辑修改）
        org_ids = [o.id for o in org_list]
        sales_data_map = self.batch_get_total_sales(org_ids, dates['start_date'], dates['end_date'])
        for o in org_list:
            customer_view_data = Customer()
            customer_view_data.Organization_id = o.id
            customer_view_data.Organization_name = o.name
            try:
                customer_view_data.monthly_price_plan = o.organizationmainapp.application.applicationselectedplan.price_plan.monthly_price
            except Exception as e:
                print(e)
            try:
                price_plan = o.organizationmainapp.application.applicationselectedplan.price_plan
                customer_view_data.commission = price_plan.transaction_percent_price if price_plan.transaction_percent_price is not None else price_plan.transaction_fixed_price
            except Exception as e:
                print(e)
            try:
                customer_view_data.App_name = o.organizationmainapp.application.appinfoforstore.store_name
            except Exception as e:
                print(e)
            try:
                customer_view_data.plan = o.organizationmainapp.application.applicationselectedplan.plan_name_stamp
            except Exception as e:
                print(e)
            # 直接拿提前查好的销售数据
            sales_data = sales_data_map.get(o.id, {})
            customer_view_data.Total_last_Month_sales = sales_data.get('total', 0)
            customer_view_data.Total_last_Month_sales_without_shipping_cost = sales_data.get('total_without_shipping_cost', 0)
            try:
                customer_view_data.Main_mail = o.organizationmainapp.application.user.email
            except Exception as e:
                print(e)
            data.append(customer_view_data)
        return data

额外优化建议 #

• 如果数据量超过10万，建议使用游标分页代替传统的offset分页，避免offset过大时数据库扫描性能下降
• 统计类的指标优先用Django ORM的annotate、aggregate在数据库层完成，性能远高于Python层循环统计
• 对于不常变化的客户列表数据，可以加缓存，缓存分页结果，进一步降低查询耗时

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