在 PHP 中快速向 MySQL 插入大量数据（例如 100 万条数据），如果每次执行单独的 INSERT 语句，性能会非常差。

为了解决这个问题，我们可以通过以下几种方式来优化插入操作：

1. 批量插入（Batch Insert）

批量插入是最常见的优化方法，能够大大提高插入效率。与其执行 100 万个单独的 INSERT 语句，不如将多条数据组合成一个 INSERT 语句一次性插入。

示例：批量插入（每次插入 1000 条）

<?php
// MySQL 连接设置
$mysqli = new mysqli("localhost", "username", "password", "database");

// 检查连接
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

// 准备数据（这里我们生成100万条数据）
$data = [];
for ($i = 1; $i <= 1000000; $i++) {
    $data[] = "('Name $i', 'email$i@example.com')";
}

// 批量插入，每次插入1000条数据
$batchSize = 1000;
$totalRecords = count($data);
for ($i = 0; $i < $totalRecords; $i += $batchSize) {
    $batchData = array_slice($data, $i, $batchSize);
    $sql = "INSERT INTO users (name, email) VALUES " . implode(", ", $batchData);
    
    if ($mysqli->query($sql) === TRUE) {
        echo "Batch $i to " . ($i + $batchSize - 1) . " inserted successfully\n";
    } else {
        echo "Error: " . $sql . "\n" . $mysqli->error;
    }
}

$mysqli->close();
?>

解析：

  • 生成了 100 万条数据，每 1000 条数据构建一次 INSERT 语句进行插入。

  • 通过 implode(", ", $batchData) 将多条 INSERT 值连接成一个大 INSERT 语句，这样可以减少数据库的执行次数，从而提高性能。

2. 禁用 SQL 事务提交（关闭自动提交）

如果使用事务管理，可以通过关闭自动提交（autocommit），减少每个操作的提交次数，提高插入效率。

示例：禁用自动提交

<?php
// MySQL 连接设置
$mysqli = new mysqli("localhost", "username", "password", "database");

// 检查连接
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

// 禁用自动提交
$mysqli->autocommit(FALSE);

$data = [];
for ($i = 1; $i <= 1000000; $i++) {
    $data[] = "('Name $i', 'email$i@example.com')";
}

$batchSize = 1000;
$totalRecords = count($data);
for ($i = 0; $i < $totalRecords; $i += $batchSize) {
    $batchData = array_slice($data, $i, $batchSize);
    $sql = "INSERT INTO users (name, email) VALUES " . implode(", ", $batchData);
    
    if ($mysqli->query($sql) === TRUE) {
        echo "Batch $i to " . ($i + $batchSize - 1) . " inserted successfully\n";
    } else {
        echo "Error: " . $sql . "\n" . $mysqli->error;
    }
}

// 提交事务
$mysqli->commit();

// 关闭连接
$mysqli->close();
?>

解析：

  • 通过禁用自动提交并使用事务（$mysqli->autocommit(FALSE)），在插入大量数据时避免每次插入后都进行一次提交操作。

  • 在所有数据插入完成后，调用 $mysqli->commit() 提交事务，这可以显著提高插入速度。

3. 使用 LOAD DATA INFILE

如果你可以先将数据导出到一个 CSV 文件中，使用 LOAD DATA INFILE 是最快的插入方式之一。通过这个方法，MySQL 可以直接从文件中读取数据并插入到表中，效率非常高。

示例：使用 LOAD DATA INFILE

<?php
// MySQL 连接设置
$mysqli = new mysqli("localhost", "username", "password", "database");

// 检查连接
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

// 生成 CSV 文件
$filename = '/path/to/data.csv';
$file = fopen($filename, 'w');

// 写入列标题
fputcsv($file, ['name', 'email']);

for ($i = 1; $i <= 1000000; $i++) {
    fputcsv($file, ["Name $i", "email$i@example.com"]);
}

fclose($file);

// 使用 LOAD DATA INFILE 插入数据
$sql = "LOAD DATA INFILE '$filename' INTO TABLE users FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' (name, email)";
if ($mysqli->query($sql) === TRUE) {
    echo "Data inserted successfully!";
} else {
    echo "Error: " . $mysqli->error;
}

$mysqli->close();
?>

解析：

  • 先将数据生成到 CSV 文件（fputcsv），然后使用 LOAD DATA INFILE 语句从文件中批量加载数据。

  • 这种方法是非常高效的，适用于导入大量数据到数据库中。

4. 优化 MySQL 配置

除了在 PHP 中优化插入过程外，还可以通过调整 MySQL 的配置来提高插入速度。以下是一些有助于提高插入速度的配置项：

  • innodb_flush_log_at_trx_commit：设置为 2 或 0，可以减少每个事务的日志写入次数。

  • innodb_buffer_pool_size：增大此值可以提高数据写入性能，尤其是在大量插入时。

  • bulk_insert_buffer_size：增大此值可以提高 LOAD DATA INFILE 等批量插入的性能。

  • max_allowed_packet：增大此值可以允许更大的 INSERT 语句。

总结

快速插入 100 万条数据的关键在于：

  1. 批量插入：将多条数据合并成一个 INSERT 语句，减少数据库交互次数。

  2. 禁用事务提交：关闭自动提交，使用事务提交一次性提交大量数据。

  3. 使用 LOAD DATA INFILE：这是最快的插入方法，适用于大批量数据的导入。

  4. 优化 MySQL 配置：确保 MySQL 的配置适合高效处理大量数据插入。

通过这些技术，你可以显著提高大规模数据插入的效率。