优化Yii2/PHP中MySQL数据导入性能的策略与实践

$item = new Product_dub();
$item->id_1c_product = $product->id;
// ... 填充其他属性
if (!$item->save()) {
    // 处理错误
}

// 示例代码片段，已去除外部函数和错误处理，聚焦核心逻辑
// ...
foreach ($products as $product) {
    Yii::$app->db->createCommand()->insert('product_dub', [
        'id_1c_product' => $product->id,
        'category_id' => $categoryMap[$product->category_id] ?? '0', // 使用预加载的映射
        'title' => $product->title,
        'brand_id' => $brandMap[$product->brand_id] ?? 'No brand', // 使用预加载的映射
        'content1' => $product->content1,
        'content2' => $product->content2,
        'content3' => $product->content3,
        'link_order' => $product->link_order,
        'img' => $product->img ?? 'no-image.png',
        'in_stock' => $product->in_stock ? 1 : 0,
        'is_popular' => $product->is_popular ? 1 : 0,
    ])->execute();
}
// ...

// 在循环开始前执行
$categoryMap = Category_dub::find()->select(['id', 'id_1c_category'])->indexBy('id_1c_category')->column();
$brandMap = Brands_dub::find()->select(['id', 'id_1c_brand'])->indexBy('id_1c_brand')->column();

// 在循环中使用这些映射
// ...
'category_id' => $categoryMap[$product->category_id] ?? '0',
'brand_id' => $brandMap[$product->brand_id] ?? 'No brand',
// ...

public function importProductFile($file, $return = true)
{    
    $products = json_decode($file, true); // 解码为关联数组更方便处理
    $dubTableName = Product::tableName() . "_dub";
    $start = microtime(true); // 使用microtime获取更精确的时间

    if ($this->db->createDuplicateTable(Product::tableName(), $dubTableName)) {
        $categoryMap = Category_dub::find()->select(['id', 'id_1c_category'])->indexBy('id_1c_category')->column();
        $brandMap = Brands_dub::find()->select(['id', 'id_1c_brand'])->indexBy('id_1c_brand')->column();

        $rows = [];
        $columns = [
            'id_1c_product', 'category_id', 'title', 'brand_id',
            'content1', 'content2', 'content3', 'link_order', 'img',
            'in_stock', 'is_popular'
        ];

        foreach ($products as $product) {
            $rows[] = [
                $product['id'],
                $categoryMap[$product['category_id']] ?? '0',
                $product['title'],
                $brandMap[$product['brand_id']] ?? 'No brand',
                $product['content1'],
                $product['content2'],
                $product['content3'],
                $product['link_order'],
                $product['img'] ?? 'no-image.png',
                $product['in_stock'] ? 1 : 0,
                $product['is_popular'] ? 1 : 0,
            ];

            // 每隔一定数量的记录执行一次批量插入，避免单次SQL过大
            if (count($rows) >= 1000) { // 例如，每1000条记录批量插入一次
                Yii::$app->db->createCommand()->batchInsert($dubTableName, $columns, $rows)->execute();
                $rows = []; // 清空已插入的行
            }
        }

        // 插入剩余的记录
        if (!empty($rows)) {
            Yii::$app->db->createCommand()->batchInsert($dubTableName, $columns, $rows)->execute();
        }
    }

    $finish = microtime(true);
    $res = round($finish - $start, 2) . " sec.";

    if ($return) {
        echo $res;
        // Answer::success(); // 根据实际需求调整
    }
}

// 事务示例
$transaction = Yii::$app->db->beginTransaction();
try {
    // ... 批量插入逻辑 ...
    $transaction->commit();
} catch (\Exception $e) {
    $transaction->rollBack();
    throw $e; // 抛出异常或记录错误
}