如何优化PHP代码实现75000条CSV数据快速导入MySQL

原代码核心问题 #

1. 逐行执行查询+插入/更新，75000条数据产生15万次数据库IO操作，耗时极长
2. 插入逻辑错误：原INSERT语句试图从目标表查询数据插入，实际无法新增记录
3. 循环内重复计算总行数、逐行输出HTML，额外消耗资源
4. 无索引支撑，查询重复数据时全表扫描
5. 未使用事务与批量操作，每次SQL单独提交

优化后代码（最快方案：用MySQL原生LOAD DATA） #

<?php
require('config.php');

// 关闭自动提交，开启事务
mysqli_autocommit($con, false);

// 检查文件上传合法性
if (!isset($_FILES['dataCliente']) || $_FILES['dataCliente']['error'] !== UPLOAD_ERR_OK) {
    die('文件上传失败');
}

$archivotmp = $_FILES['dataCliente']['tmp_name'];
$totalRegistros = 0;

// 先在数据库执行这条语句，给codigo_inmueble加唯一索引（必须操作）
// ALTER TABLE serviciosglforjson ADD UNIQUE INDEX idx_codigo_inmueble (codigo_inmueble);

try {
    // 1. 创建临时表，结构与目标表匹配
    $createTmpTable = "
        CREATE TEMPORARY TABLE tmp_serviciosglforjson (
            codigo_inmueble VARCHAR(255) NOT NULL,
            ruta VARCHAR(255) NOT NULL,
            PRIMARY KEY (codigo_inmueble)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    ";
    mysqli_query($con, $createTmpTable);

    // 2. 用LOAD DATA快速将CSV导入临时表（分隔符为|，跳过第一行表头）
    $loadData = "
        LOAD DATA LOCAL INFILE '".mysqli_real_escape_string($con, $archivotmp)."'
        INTO TABLE tmp_serviciosglforjson
        FIELDS TERMINATED BY '|'
        OPTIONALLY ENCLOSED BY '\"'
        LINES TERMINATED BY '\n'
        IGNORE 1 LINES
        (codigo_inmueble, ruta);
    ";
    mysqli_query($con, $loadData);

    // 3. 批量同步到目标表：不存在则插入，存在则更新
    $upsert = "
        INSERT INTO serviciosglforjson (codigo_inmueble, ruta)
        SELECT codigo_inmueble, ruta FROM tmp_serviciosglforjson
        ON DUPLICATE KEY UPDATE ruta = VALUES(ruta);
    ";
    mysqli_query($con, $upsert);

    // 获取处理的总记录数
    $totalRegistros = mysqli_affected_rows($con);

    // 提交事务
    mysqli_commit($con);

    // 输出结果
    echo '<center><p style="text-align:center; color:#333;">处理完成！共处理 '.$totalRegistros.' 条记录</p></center>';
    echo '<center><a href="index.php">返回</a></center>';
} catch (Exception $e) {
    // 出错回滚事务
    mysqli_rollback($con);
    die('导入失败：'.$e->getMessage());
} finally {
    // 删除临时表
    mysqli_query($con, "DROP TEMPORARY TABLE IF EXISTS tmp_serviciosglforjson");
    mysqli_close($con);
}
?>

备选方案（无LOAD DATA权限时：批量预处理） #

如果服务器禁用了LOAD DATA LOCAL INFILE，可以用PHP批量预处理实现：

<?php
require('config.php');

mysqli_autocommit($con, false);

if (!isset($_FILES['dataCliente']) || $_FILES['dataCliente']['error'] !== UPLOAD_ERR_OK) {
    die('文件上传失败');
}

$archivotmp = $_FILES['dataCliente']['tmp_name'];
$batchSize = 1000; // 每1000条批量提交一次
$totalRegistros = 0;
$count = 0;

// 先给codigo_inmueble加唯一索引（必须操作）
// ALTER TABLE serviciosglforjson ADD UNIQUE INDEX idx_codigo_inmueble (codigo_inmueble);

// 预处理UPSERT语句
$stmt = mysqli_prepare($con, "
    INSERT INTO serviciosglforjson (codigo_inmueble, ruta)
    VALUES (?, ?)
    ON DUPLICATE KEY UPDATE ruta = VALUES(ruta);
");
mysqli_stmt_bind_param($stmt, "ss", $codigo, $ruta);

// 逐行读取文件，减少内存占用
$handle = fopen($archivotmp, 'r');
if ($handle) {
    // 跳过第一行表头
    fgets($handle);

    while (($line = fgets($handle)) !== false) {
        $line = trim($line);
        if (empty($line)) continue;

        $datos = explode("|", $line);
        $codigo = !empty($datos[0]) ? trim($datos[0]) : '';
        $ruta = !empty($datos[1]) ? trim($datos[1]) : '';

        if (empty($codigo)) continue;

        // 执行预处理语句
        mysqli_stmt_bind_param($stmt, "ss", $codigo, $ruta);
        mysqli_stmt_execute($stmt);
        $totalRegistros += mysqli_stmt_affected_rows($stmt);

        // 每积累1000条提交一次事务
        if (++$count % $batchSize === 0) {
            mysqli_commit($con);
            mysqli_autocommit($con, false);
        }
    }

    // 提交剩余的记录
    mysqli_commit($con);
    fclose($handle);

    echo '<center><p style="text-align:center; color:#333;">处理完成！共处理 '.$totalRegistros.' 条记录</p></center>';
    echo '<center><a href="index.php">返回</a></center>';
} else {
    die('无法打开上传文件');
}

mysqli_stmt_close($stmt);
mysqli_close($con);
?>

关键优化点 #

1. 用LOAD DATA替代逐行读取：MySQL原生导入速度是PHP逐行操作的100倍以上，直接绕过PHP层的IO开销
2. 批量UPSERT：通过INSERT ... ON DUPLICATE KEY UPDATE实现批量插入/更新，仅需2次核心SQL操作
3. 索引优化：给codigo_inmueble添加唯一索引，确保UPSERT时快速定位重复数据，避免全表扫描
4. 事务批量提交：关闭自动提交，批量提交事务减少数据库日志写入次数
5. 内存优化：用fgets逐行读取文件，避免一次性加载75000行到内存
6. 修复原插入逻辑：修正了原代码中无法新增记录的错误
7. 移除冗余操作：删除循环内重复计算总行数、逐行输出HTML的逻辑，减少资源消耗

内容的提问来源于stack exchange，提问作者Roger Gerber Quirino