PHP Yii2框架下MySQL导入JSON数据速度缓慢优化问询

优化方案 #

1. 替换单条插入为批量插入 #

你当前代码是在循环中每次执行一次单条插入，每执行一次都要完成一次和MySQL的请求交互，网络传输、SQL解析的开销会随数据量线性增长。你可以把N条数据拼成一个批量插入语句，比如一次攒1000条再执行，性能可以提升10~30倍。
Yii框架本身支持批量插入的方法，示例代码如下：

// 定义批量插入的阈值，可根据实际场景调整，建议500~2000
$batchSize = 1000;
$insertData = [];
foreach ($products as $product) {
    $insertData[] = [
        '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,
    ];
    // 达到阈值就执行一次批量插入，然后清空数组
    if (count($insertData) == $batchSize) {
        Yii::$app->db->createCommand()->batchInsert('product_dub', array_keys($insertData[0]), $insertData)->execute();
        $insertData = [];
    }
}
// 插入剩下的不足阈值的数据
if (!empty($insertData)) {
    Yii::$app->db->createCommand()->batchInsert('product_dub', array_keys($insertData[0]), $insertData)->execute();
}

2. 开启事务批量提交 #

MySQL默认开启自动提交，单条插入就会触发一次事务落盘，你使用的是HDD硬盘，随机IO性能很差，这是当前速度慢的核心原因之一。把所有插入操作放到同一个事务里提交，或者按批量插入的频次提交事务，能大幅降低IO开销，性能还能再提升3~10倍。
示例：

$transaction = Yii::$app->db->beginTransaction();
try {
    // 上述批量插入逻辑放在此处
    $transaction->commit();
} catch (\Exception $e) {
    $transaction->rollBack();
    Answer::failure("导入失败：".$e->getMessage());
}

3. 临时禁用索引和约束 #

导入前先把目标表的非主键索引、唯一约束临时关闭，等数据全部导入完成后再重建索引，避免每次插入都要更新索引的开销。如果批量导入不需要校验外键，也可以临时关闭外键检查：

SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
-- 导入完成后执行
SET FOREIGN_KEY_CHECKS = 1;
SET UNIQUE_CHECKS = 1;

4. 十万级以上数据用LOAD DATA INFILE #

如果是十万、百万级的大批量导入，直接用MySQL原生的LOAD DATA INFILE是性能最高的方案，比PHP拼接SQL快50~100倍。你只需要把JSON数据先转成CSV/TSV格式的文本文件，然后直接调用MySQL的LOAD命令导入即可，不需要PHP读取整个JSON文件进内存，还能避免内存溢出。

常见问题解答 #

• 十万、百万级数据导入需要数小时完全不正常，按照上述方案优化后，百万级数据导入在你当前的硬件配置下耗时不会超过5分钟，哪怕是HDD硬盘。
• 如果你的JSON文件超过PHP内存限制，不要一次性用json_decode读入整个文件，要用流式JSON解析器逐行读取处理，避免内存溢出。

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