Laravel导入3.3万行Excel生成30万条ProductSale数据执行耗时过长问题

我正在上传包含用户及其产品状态（取值0、1）数据的Excel文件，要实现以下逻辑：

1. 先将产品数据携带user_id、product_id、target_month、status字段存入Productsale表
2. 之后拉取所有用户，从productsale表中获取产品及其状态进行统计，将统计结果存入Saleproduct表

我的Excel文件共有33000行数据，由于每个用户对应8个产品，最终会在productsale表生成30万条数据，当前代码执行耗时过长。
Excel截图如下：

原有代码：

try {
    $path = $request->file('file')->store('upload', ['disk' => 'upload']);
    $value = (new FastExcel())->import($path, function ($line) {
        $user = User::where('code', $line['RVS Code'])->first();
        $store = Store::where('code', $line['Customer Code'])->first();
        $a = array_keys($line);
        $total_number = count($a);
        $n = 4;
        $productsale= 0;
        for ($i=3; $i<$total_number; $i++) {
            $str_arr = preg_split('/(ml )/', $a[$i]);
            $product = Product::where('name', $str_arr[1] ?? null)->where('type', $str_arr[0] . 'ml')->first();
            if (!empty($product)) {
                $product = ProductSale::updateOrCreate([
                    'user_id' => $user->id,
                    'store_id' => $store->id,
                    'month' => $line['Target Month'],
                    'product_id' => $product->id,
                ],[
                    'status' => $line[$str_arr[0] . 'ml ' . $str_arr[1]],
                ]);
            }
        }
    });
    //sales
    $datas = User::all();
    foreach($datas as $user){
        $targets = Target::where('user_id',$user->id)->get();
        foreach($targets as $target){
            $sales = Sales::where('user_id', $user->id)->where('month',$target->month)->first();
            $products = Product::all();
            foreach ($products as $product) {
                $totalSale = ProductSale::where('user_id',$user->id)->where('month',$target->month)->where('product_id',$product->id)->sum('status');
                $sale_product = SalesProduct::updateOrCreate([
                    'product_id' => $product->id,
                    'sales_id' => $sales->id,
                ],[
                    'sale' => $totalSale,
                ]);
            }
        }
    }
    return response()->json(true, 200);
}

核心耗时原因 #

• 循环内重复查询字典表（User/Store/Product），产生大量N+1查询
• 单条循环执行updateOrCreate，每写入一条数据至少触发2次数据库IO，30万条数据对应百万级IO请求
• 统计逻辑使用多层嵌套循环+单条sum查询，查询次数随用户、产品数量线性增长
• 原有代码updateOrCreate参数错误，把status放在了唯一判断条件中，会导致重复写入

优化后代码 #

try {
    $path = $request->file('file')->store('upload', ['disk' => 'upload']);

    // 预加载所有字典数据，避免循环内重复查库
    $userMap = User::pluck('id', 'code')->toArray();
    $storeMap = Store::pluck('id', 'code')->toArray();
    $productMap = Product::all()->mapWithKeys(function ($item) {
        return [$item->type . ' ' . $item->name => $item->id];
    })->toArray();
    $allProductIds = Product::pluck('id')->toArray();

    $productSaleData = [];
    $productSaleUniqueKeys = ['user_id', 'store_id', 'month', 'product_id'];

    // 解析Excel整理待写入数据
    (new FastExcel())->import($path, function ($line) use ($userMap, $storeMap, $productMap, &$productSaleData) {
        // 跳过不存在的用户、门店
        if (!isset($userMap[$line['RVS Code']], $storeMap[$line['Customer Code']])) {
            return;
        }
        $userId = $userMap[$line['RVS Code']];
        $storeId = $storeMap[$line['Customer Code']];
        $month = $line['Target Month'];

        foreach ($line as $colName => $status) {
            // 过滤非产品列
            if (!str_contains($colName, 'ml ') || !isset($productMap[$colName])) {
                continue;
            }
            $productSaleData[] = [
                'user_id' => $userId,
                'store_id' => $storeId,
                'month' => $month,
                'product_id' => $productMap[$colName],
                'status' => $status
            ];
        }
    });

    // 分块批量写入ProductSale，单次写入1000条避免数据量过大
    foreach (array_chunk($productSaleData, 1000) as $chunk) {
        ProductSale::upsert($chunk, $productSaleUniqueKeys, ['status']);
    }

    // 一次性分组聚合所有统计数据，无需嵌套循环查询
    $statData = ProductSale::query()
        ->selectRaw('user_id, month, product_id, sum(status) as total_sale')
        ->groupBy('user_id', 'month', 'product_id')
        ->get()
        ->mapWithKeys(function ($item) {
            return ["{$item->user_id}|{$item->month}|{$item->product_id}" => $item->total_sale];
        })->toArray();

    // 预加载Sales映射关系
    $salesMap = Sales::all()->mapWithKeys(function ($item) {
        return ["{$item->user_id}|{$item->month}" => $item->id];
    })->toArray();

    $salesProductData = [];
    $salesProductUniqueKeys = ['product_id', 'sales_id'];

    // 整理统计待写入数据
    foreach ($salesMap as $key => $salesId) {
        list($userId, $month) = explode('|', $key);
        foreach ($allProductIds as $productId) {
            $statKey = "{$userId}|{$month}|{$productId}";
            $salesProductData[] = [
                'product_id' => $productId,
                'sales_id' => $salesId,
                'sale' => $statData[$statKey] ?? 0
            ];
        }
    }

    // 批量写入统计结果
    foreach (array_chunk($salesProductData, 1000) as $chunk) {
        SalesProduct::upsert($chunk, $salesProductUniqueKeys, ['sale']);
    }

    return response()->json(true, 200);
}

补充说明 #

如果后续数据量进一步增大，建议把导入逻辑放到队列异步执行，避免前端请求超时。本次优化后30万条数据的整体耗时可从原来的数分钟降至10秒以内。

内容的提问来源于stack exchange，提问作者Faisal Faiz Michankhel