优化Laravel查询：提升获取Top10热销产品的查询速度

一、添加数据库索引（核心优化步骤） #

查询缓慢的主要原因之一是缺少针对性索引，导致数据库频繁全表扫描。针对你的业务场景，需添加以下索引：

• cart_items表：创建联合索引覆盖关联与日期条件，可选添加覆盖索引减少回表查询：

  CREATE INDEX idx_cart_items_product_created ON cart_items (product_id, created_at);
  -- 可选：包含计算字段的覆盖索引，进一步提升聚合效率
  CREATE INDEX idx_cart_items_sales_calc ON cart_items (product_id, created_at, product_price_selling, product_remise, product_quantity);
  

• invoice_items表：同理创建联合索引，同时添加关联invoice的字段索引：

  CREATE INDEX idx_invoice_items_product_created ON invoice_items (product_id, created_at);
  CREATE INDEX idx_invoice_items_invoice_id ON invoice_items (invoice_id);
  

• invoices表：针对doesntHave('carts')的查询，添加关联cart的字段索引（假设关联字段为cart_id）：

  CREATE INDEX idx_invoices_cart_id ON invoices (cart_id);
  

二、重构查询逻辑，减少关联次数 #

原查询使用多个withCount和whereHas会触发多次关联查询，性能损耗大。通过子查询预汇总销售数据，再与产品表关联，可大幅降低查询复杂度：

use Carbon\Carbon;
use Illuminate\Support\Facades\DB;

// 标准化日期范围，避免字符串拼接引发的格式问题
$startDate = Carbon::parse($from)->startOfDay();
$endDate = Carbon::parse($to)->endOfDay();

// 预汇总cart_items的销售额
$cartSalesSub = DB::table('cart_items')
    ->select(
        'product_id',
        DB::raw('sum(product_price_selling * (1 - (product_remise / 100)) * product_quantity) as cart_total')
    )
    ->whereBetween('created_at', [$startDate, $endDate])
    ->groupBy('product_id');

// 预汇总符合条件的invoice_items销售额
$invoiceSalesSub = DB::table('invoice_items')
    ->select(
        'product_id',
        DB::raw('sum(product_price_selling * (1 - (product_remise / 100)) * product_quantity) as invoice_total')
    )
    ->whereBetween('created_at', [$startDate, $endDate])
    ->whereHas('invoice', fn($q) => $q->doesntHave('carts'))
    ->groupBy('product_id');

// 关联产品表计算总销售额，筛选并排序取Top10
$top10Products = Product::select('id', 'name')
    ->selectSub(
        DB::raw('COALESCE(cart_total, 0) + COALESCE(invoice_total, 0)'),
        'total_sales'
    )
    ->leftJoinSub($cartSalesSub, 'cart_sales', fn($join) => $join->on('products.id', '=', 'cart_sales.product_id'))
    ->leftJoinSub($invoiceSalesSub, 'invoice_sales', fn($join) => $join->on('products.id', '=', 'invoice_sales.product_id'))
    // 仅保留有有效销售数据的产品
    ->where(function ($query) use ($startDate, $endDate) {
        $query->whereExists(fn($sub) => $sub->select(DB::raw(1))
                ->from('cart_items')
                ->whereColumn('cart_items.product_id', 'products.id')
                ->whereBetween('cart_items.created_at', [$startDate, $endDate]))
            ->orWhereExists(fn($sub) => $sub->select(DB::raw(1))
                ->from('invoice_items')
                ->whereColumn('invoice_items.product_id', 'products.id')
                ->whereBetween('invoice_items.created_at', [$startDate, $endDate])
                ->whereHas('invoice', fn($q) => $q->doesntHave('carts')));
    })
    ->orderBy('total_sales', 'DESC')
    ->limit(10)
    ->get();

三、其他优化细节 #

1. 日期处理优化：使用Carbon的startOfDay()和endOfDay()替代字符串拼接，确保日期格式规范，让数据库能高效利用日期索引。
2. 缓存策略：若Top10数据无需实时更新，可将查询结果缓存（比如缓存1小时），避免重复计算：

   $top10Products = Cache::remember('top10_products_' . $from . '_' . $to, 3600, function () use ($startDate, $endDate) {
       // 上述查询逻辑
   });
   

3. 离线汇总表（超大数据量场景）：若数据量达百万级以上，可创建product_sales_summary汇总表，通过Laravel任务调度每天凌晨计算前一天的销售数据并写入该表，查询时直接读取汇总表，性能提升数量级：

   CREATE TABLE product_sales_summary (
       product_id INT,
       date DATE,
       total_sales DECIMAL(10,2),
       PRIMARY KEY (product_id, date),
       INDEX idx_date_total_sales (date, total_sales)
   );
   

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