Laravel百万级交易明细查询优化：热销商品Server-Side报表提速

我要制作一份热销商品报表，transaction_liquid_details表存有100万+数据，需基于该数据实现服务端分页的Datatable功能。我用Laravel查询构造器编写的查询可正常运行，但耗时过长，恳请协助优化以提升速度：

$start = ($request->start) ? $request->start : '2022-04-01';
$end = ($request->end) ? $request->end : Carbon::now()->toDateString();

$bestSellingTransactions = DB::table('transaction_liquid_details')
                           ->select('transaction_liquid_details.item_id', 'items.barcode', 
                             'items.name as item_name', 'item_units.name as item_unit_name',
                             'categories.name as category_name', 
                              DB::raw('SUM(transaction_liquid_details.quantity * transaction_liquid_details.parfume) as total_sold'),
                              DB::raw('SUM(transaction_liquid_details.total) as total_price'))
                           ->join('items', 'items.id', '=', 'transaction_liquid_details.item_id')
                           ->leftJoin('item_units', 'item_units.id', 'items.item_unit_id')
                           ->leftJoin('categories', 'categories.id', 'items.category_id')
                           ->leftJoin('transactions', 'transactions.id', 'transaction_liquid_details.transaction_id')
                           ->groupBy('transaction_liquid_details.item_id', 'items.barcode', 'items.name', 'item_units.name', 'categories.name')
                           ->orderBy('total_sold', 'DESC')
                           ->whereBetween('transactions.created_at', [$start, $end . " 23:59:59"])
                           ->where('transactions.status', $this->FINISHED_TRANSACTION_STATUS)
                           ->get();

表结构说明 #

• items表：核心字段包括id（主键）、barcode（商品条码）、name（商品名称）、item_unit_id（关联单位表ID）、category_id（关联分类表ID），存储商品基础信息
• transaction_liquid_details表：核心字段包括id（主键）、transaction_id（关联交易表ID）、item_id（关联商品表ID）、quantity（数量）、parfume（系数）、total（单条明细总价），存储交易明细数据

1. 先过滤再关联，缩小数据范围 #

优先筛选出符合时间和状态的交易ID，再关联交易明细，减少后续关联计算的数据量：

// 先获取有效交易ID集合
$validTransactionIds = DB::table('transactions')
    ->whereBetween('created_at', [$start, $end . " 23:59:59"])
    ->where('status', $this->FINISHED_TRANSACTION_STATUS)
    ->pluck('id');

// 基于有效交易ID关联查询
$bestSellingTransactions = DB::table('transaction_liquid_details')
    ->whereIn('transaction_id', $validTransactionIds)
    ->select('transaction_liquid_details.item_id', 'items.barcode', 
        'items.name as item_name', 'item_units.name as item_unit_name',
        'categories.name as category_name', 
        DB::raw('SUM(transaction_liquid_details.quantity * transaction_liquid_details.parfume) as total_sold'),
        DB::raw('SUM(transaction_liquid_details.total) as total_price'))
    ->join('items', 'items.id', '=', 'transaction_liquid_details.item_id')
    ->leftJoin('item_units', 'item_units.id', 'items.item_unit_id')
    ->leftJoin('categories', 'categories.id', 'items.category_id')
    ->groupBy('transaction_liquid_details.item_id', 'items.barcode', 'items.name', 'item_units.name', 'categories.name')
    ->orderByDesc('total_sold')
    ->get();

2. 添加针对性索引 #

给以下字段创建复合或单字段索引，大幅提升查询、关联、分组效率：

• transactions表：创建复合索引(status, created_at, id)，覆盖查询条件和返回字段
• transaction_liquid_details表：创建复合索引(transaction_id, item_id)，支持交易关联和商品分组
• items表：创建复合索引(id, barcode, name, item_unit_id, category_id)，覆盖关联和查询所需的所有字段

3. 适配Server-Side Datatable的分页逻辑 #

不需要一次性获取全量数据，用Laravel的paginate()配合Datatable的分页参数，只返回当前页数据：

$perPage = $request->input('length', 10);
$page = $request->input('start', 0) / $perPage + 1;

$bestSellingTransactions = DB::table('transaction_liquid_details')
    ->whereIn('transaction_id', $validTransactionIds)
    ->select('transaction_liquid_details.item_id', 'items.barcode', 
        'items.name as item_name', 'item_units.name as item_unit_name',
        'categories.name as category_name', 
        DB::raw('SUM(transaction_liquid_details.quantity * transaction_liquid_details.parfume) as total_sold'),
        DB::raw('SUM(transaction_liquid_details.total) as total_price'))
    ->join('items', 'items.id', '=', 'transaction_liquid_details.item_id')
    ->leftJoin('item_units', 'item_units.id', 'items.item_unit_id')
    ->leftJoin('categories', 'categories.id', 'items.category_id')
    ->groupBy('transaction_liquid_details.item_id', 'items.barcode', 'items.name', 'item_units.name', 'categories.name')
    ->orderByDesc('total_sold')
    ->paginate($perPage, ['*'], 'page', $page);

4. 简化分组字段 #

由于item_id是items表的主键，barcode、name等字段都唯一依赖item_id，可仅按item_id分组，其他字段用聚合函数取值，减少分组计算量：

->select('transaction_liquid_details.item_id', 
    DB::raw('MAX(items.barcode) as barcode'),
    DB::raw('MAX(items.name) as item_name'),
    DB::raw('MAX(item_units.name) as item_unit_name'),
    DB::raw('MAX(categories.name) as category_name'),
    DB::raw('SUM(transaction_liquid_details.quantity * transaction_liquid_details.parfume) as total_sold'),
    DB::raw('SUM(transaction_liquid_details.total) as total_price'))
->groupBy('transaction_liquid_details.item_id')

5. 规范时间处理 #

用Carbon统一处理时间范围，避免手动字符串拼接，确保时间范围准确性：

$start = $request->start ? Carbon::parse($request->start)->startOfDay() : Carbon::parse('2022-04-01')->startOfDay();
$end = $request->end ? Carbon::parse($request->end)->endOfDay() : Carbon::now()->endOfDay();

// 后续查询直接使用Carbon对象
->whereBetween('transactions.created_at', [$start, $end])

内容的提问来源于stack exchange，提问作者Aditya Muhamad Putra P.