Laravel销售报表查询优化求助：3000+数据耗时10分钟

你的查询耗时10分钟核心原因在于重复子查询、缺少索引、全量数据加载以及不必要的SQL计算，以下是针对性优化方案：

1. 替换重复子查询为LEFT JOIN + 分组 #

原查询中多次对order_voucher执行子查询，每条数据都会触发6次独立查询，这是性能瓶颈的核心。改用LEFT JOIN结合分组取第一条记录的方式：

$query = OrderDetail::select(
    'order_detail.id as no',
    'order_detail.order_id as id',
    'order_booking.booking_number as booking_id',
    'order_booking.sales_code as sales_code',
    DB::raw('CONCAT(customer.first_name, " ", customer.last_name) as fullname'),
    'customer.sap_code',
    'customer.phone_number as handphone',
    'order.created_at as date_order',
    'order.invoice_number as invoice',
    'order.so_number',
    'order.ro_sap as do_number',
    // 替换order_shipping.*为具体需要的字段
    'order_shipping.address as shipping_address',
    'store_location.sap_code as kode_toko',
    'store_location.name as name_location',
    'order_detail.name as name_prod',
    'order_detail.model as model',
    'order_detail.product_price as a_pcs',
    'order_detail.qty',
    'order_detail.price',
    'metode.name as metode_pembayaran',
    'order_booking.tenor as name_tenor',
    // 用CASE分组替换子查询
    DB::raw('MAX(CASE WHEN ov.voucher_type_id <=4 THEN ov.voucher_code END) as vch_code_product'),
    DB::raw('MAX(CASE WHEN ov.voucher_type_id <=4 THEN ov.discount ELSE 0 END) as payment_voucher_product'),
    DB::raw('MAX(CASE WHEN ov.voucher_type_id =5 THEN ov.voucher_code END) as vch_code_shipment'),
    DB::raw('MAX(CASE WHEN ov.voucher_type_id =5 THEN ov.discount ELSE 0 END) as payment_voucher_shipment'),
    DB::raw('MAX(CASE WHEN ov.voucher_type_id =6 THEN ov.voucher_code END) as vch_code_bank'),
    DB::raw('MAX(CASE WHEN ov.voucher_type_id =6 THEN ov.discount ELSE 0 END) as payment_voucher_bank'),
    'order_status.name as name_status',
    'order_booking.channel as channel',
    'order_booking.device as device',
    'order_detail.rating as rating',
    'order_detail.cwh_code',
    'order.shipping_cost'
)
->join('order', 'order_detail.order_id', '=', 'order.id')
->join('order_booking', 'order_booking.id', '=', 'order.booking_id')
->join('order_shipping', 'order_shipping.order_id', '=', 'order.id')
->join('customer', 'customer.id', '=', 'order.customer_id')
->join('store_location', 'store_location.id', '=', 'order.store_location_id')
->join('order_status', 'order_status.id', '=', 'order.order_status_id')
->join('metode', 'metode.id', '=', 'order_booking.metode_id')
// 左连接order_voucher并按order_id取第一条有效记录
->leftJoin(DB::raw('(SELECT ov.* FROM order_voucher ov 
    JOIN (SELECT order_id, MIN(id) as min_id FROM order_voucher 
          WHERE voucher_type_id IN (4,5,6) GROUP BY order_id) ov_min 
    ON ov.id = ov_min.min_id) ov'), 'ov.order_id', '=', 'order.id')
->where(['order.status' => 1])
->whereBetween('order.created_at', [$start, $end])
->groupBy('order_detail.id')
->orderBy('no', 'asc');

2. 添加数据库索引 #

给关联字段、查询条件字段添加索引，大幅提升JOIN和WHERE语句的执行效率：

-- order表索引
CREATE INDEX idx_order_status_created_at ON `order`(status, created_at);
CREATE INDEX idx_order_booking_id ON `order`(booking_id);
CREATE INDEX idx_order_customer_id ON `order`(customer_id);
CREATE INDEX idx_order_store_location_id ON `order`(store_location_id);

-- order_detail表索引
CREATE INDEX idx_order_detail_order_id ON order_detail(order_id);

-- order_voucher表索引
CREATE INDEX idx_order_voucher_order_id_type ON order_voucher(order_id, voucher_type_id);

-- 关联表辅助索引
CREATE INDEX idx_order_booking_metode_id ON order_booking(metode_id);
CREATE INDEX idx_order_shipping_order_id ON order_shipping(order_id);

3. 分块处理数据，避免内存溢出 #

3000+行数据用get()会一次性加载到内存，改用chunk()分块处理，同时适合生成下载文件：

// 示例：分块生成CSV报表
$file = fopen(storage_path('app/sales_report.csv'), 'w');
// 写入表头
fputcsv($file, [
    '序号', '订单ID', '预订号', '销售码', '客户全名', 'SAP码', '手机号',
    '下单日期', '发票号', 'SO号', 'DO号', '收货地址', '门店编码', '门店名称',
    '产品名称', '型号', '单价', '数量', '折扣', '小计', '支付方式', '分期期限',
    '产品优惠券码', '产品优惠金额', '运费优惠券码', '运费优惠金额',
    '银行优惠券码', '银行优惠金额', '实付金额', '订单状态', '渠道', '设备',
    '评分', 'CWH码', '运费'
]);

$query->chunk(200, function($rows) use ($file) {
    foreach ($rows as $row) {
        // 在PHP中计算折扣、小计、实付金额
        $discount = $row->price !== null ? ($row->a_pcs - $row->price) : 0;
        $subtotal = $row->qty * $row->a_pcs - $discount;
        $payment = $subtotal - $row->payment_voucher_product - $row->payment_voucher_shipment - $row->payment_voucher_bank;

        // 组装行数据写入文件
        fputcsv($file, [
            $row->no, $row->id, $row->booking_id, $row->sales_code,
            $row->fullname, $row->sap_code, $row->handphone,
            $row->date_order->format('Y-m-d H:i:s'), $row->invoice,
            $row->so_number, $row->do_number, $row->shipping_address,
            $row->kode_toko, $row->name_location, $row->name_prod,
            $row->model, $row->a_pcs, $row->qty, $discount,
            $subtotal, $row->metode_pembayaran, $row->name_tenor,
            $row->vch_code_product, $row->payment_voucher_product,
            $row->vch_code_shipment, $row->payment_voucher_shipment,
            $row->vch_code_bank, $row->payment_voucher_bank,
            $payment, $row->name_status, $row->channel,
            $row->device, $row->rating, $row->cwh_code,
            $row->shipping_cost
        ]);
    }
});

fclose($file);

4. 复杂计算逻辑移到PHP处理 #

原SQL中的subtotal、payment等复杂计算，移到PHP中处理，减少数据库计算压力，同时更易维护。

5. 缓存高频报表数据 #

如果是固定时间段的报表（如月度报表），可以将查询结果缓存，避免重复计算：

$cacheKey = 'sales_report_' . $start->format('Y-m') . '_' . $end->format('Y-m');
$reportData = Cache::remember($cacheKey, 3600, function() use ($query) {
    return $query->get();
});

6. 分析查询执行计划 #

用Laravel查询日志或数据库EXPLAIN语句定位剩余性能瓶颈：

DB::enableQueryLog();
$query->get();
dd(DB::getQueryLog());

或者直接在数据库执行：

EXPLAIN [你的完整SQL语句];

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