PHP＋MySQL/MariaDBで高速バルクインサート｜CSV一括登録の実装と処理時間の検証

PHP+PDOでMySQL/MariaDBのテーブルにバルクインサートで高速にレコード挿入

大量データをPHP＋MySQL/MariaDBで高速に登録したい方へ。
このページでは、CSVファイルをバルクインサート（bulk insert）で一括登録する方法を、実際のソースコードと処理時間の検証結果とともに紹介します。
INSERT文の最適化により、従来の1行ずつ登録する方法と比較して大幅な高速化を実現。
PDO接続、ファイル読み込み、SQL構築まで、MySQL/MariaDB環境で応用可能な実務的手法を解説します。

まずはバルクインサートのSQL構文と、処理速度に影響するポイントを整理します。

挿入先テーブル構造が以下とします。
CREATE TABLE IF NOT EXISTS t_csv(id INT, name VARCHAR(255), age INT);

普通に1レコードずつ挿入

3レコードを個別に挿入する場合、以下のようにSQLを3回実行します。
そのため、サーバ側では3回の処理が行われ、ログにも3件（3イベント）が記録されます。

INSERT INTO t_csv(id, name, age) VALUES(1, 'イシダ', 24);
INSERT INTO t_csv(id, name, age) VALUES(2, 'カワサキ', 31);
INSERT INTO t_csv(id, name, age) VALUES(3, 'ソノダ', 41);

バルクインサートで複数レコードを一度に挿入

バルクインサートでは、3レコードを1つのSQL文でまとめて挿入します。
SQLの実行は1回で済み、ログにも1件（1イベント）のみが記録されます。

INSERT INTO t_csv(id, name, age) VALUES(1, 'イシダ', 24),
                                       (2, 'カワサキ', 31),
                                       (3, 'ソノダ', 41);

この差は、処理時間やログの肥大化、トランザクション制御にも影響します。
特に大量データを扱う場合は、バルクインサートの方が圧倒的に効率的です。

テスト用CSVファイルの作成

以下のソースコードでテスト用「insert.csv」ファイルを生成します。

create_csv.php ファイル

<?php
$filename="insert.csv";
if(file_exists($filename)){
  unlink($filename);
}
for($i=1;$i<=10000;$i++){
  file_put_contents(
    $filename, 
    $i.  ",ナマエ".$i.  ",".($i%60+20)."\r\n",
    FILE_APPEND);
}

実行すると以下の「insert.csv」ファイルが生成されます。

1,ナマエ1,21
2,ナマエ2,22
3,ナマエ3,23

・・・

9999,ナマエ9999,59
10000,ナマエ10000,60

１行ずつレコード挿入

以下のソースコードでテスト用「insert.csv」ファイルを読み込んで１行ずつレコード挿入します。
実行完了後にかかった時間（秒）を表示します。

normal_insert.php ファイル

<?php
$start_time = microtime(true);
//insert.csv ファイルを開く
$handle = fopen("insert.csv", "r");
if($handle === FALSE){die();}

//データベース接続
$pdo=new PDO("mysql:dbname=データベース名;host=ホスト名","ユーザー名", "パスワード");
//文字コード指定
$pdo->query("SET NAMES utf8mb4");
//必要であれば「t_csv」テーブルを空にする
$pdo->query("TRUNCATE t_csv");

//トランザクション開始
$pdo->beginTransaction();
//通常のSQLで１行ずつ挿入する
$stmt=$pdo->prepare( "INSERT INTO t_csv(id, name, age) VALUES(?, ?, ?)" );
while (($data = fgetcsv($handle, 4000, ",")) !== FALSE) {
  $stmt->execute($data);
}
//コミット
$pdo->commit();

//ファイルを閉じる
fclose($handle);
//計測時間の出力
echo sprintf('%.4f', microtime(true)-$start_time);

バルクインサートで複数行ずつレコード挿入

以下のソースコードでテスト用「insert.csv」ファイルを読み込んでバルクインサートで複数行ずつ同時にレコード挿入します。
最大同時挿入レコード数は MAX_COUNT 定数で指定しています。
この値は、PHPの設定やMariaDB(MySQL)の設定、CSVファイルの１レコード当たりの長さ（バイト数）により考慮する必要があります。
実行完了後にかかった時間（秒）を表示します。

bulk_insert.php ファイル

<?php
$start_time = microtime(true);
//最大何レコード同時にバルクインサートで挿入するかを指定する
define("MAX_COUNT", 10);
//insert.csv ファイルを開く
$handle = fopen("insert.csv", "r");
if($handle === FALSE){die();}

//データベース接続
$pdo=new PDO("mysql:dbname=データベース名;host=ホスト名","ユーザー名", "パスワード");
//文字コード指定
$pdo->query("SET NAMES utf8mb4");
//必要であれば「t_csv」テーブルを空にする
$pdo->query("TRUNCATE t_csv");

//トランザクション開始
$pdo->beginTransaction();
//バルクインサートで複数行同時に挿入する
$base_sql="INSERT INTO t_csv(id, name, age) VALUES";
$count=0;
$args=[];
$sql_values=[];
while (($data = fgetcsv($handle, 4000, ",")) !== FALSE) {
  $count++;
  $args=array_merge($args, $data);
  $arr=array_fill(0,count($data),"?");
  $sql_values[] = "(".implode(",",$arr).")";
  if($count>=MAX_COUNT){
    //MAX_COUNT行同時に挿入する
    $sql=$base_sql.implode(",", $sql_values);
    $stmt=$pdo->prepare($sql);
    $stmt->execute($args);
    $args=[];
    $sql_values=[];
    $count=0;
  }
}
if($count>0){
  //MAX_COUNT行未満の行が残っていたら残りを同時に挿入する
  $sql=$base_sql.implode(",", $sql_values);
  $stmt=$pdo->prepare($sql);
  $stmt->execute($args);
}
//コミット
$pdo->commit();

//ファイルを閉じる
fclose($handle);
//計測時間の出力
echo sprintf('%.4f', microtime(true)-$start_time);

CSVファイルのレコード挿入速度検証結果

1万レコードでテストしました。
結果にバラツキがありますが、バルクインサートの方が10倍以上速いです。
カラムにイデックスが付与された場合は全体的に遅くなると想定されるので、後で実験したいと思います。

インデックスが付与されたテーブルへCSVファイルのレコード挿入速度検証結果

CREATE INDEX idx_id ON t_csv(id); で、「id」カラムだけインデックスを付与してテストしました。
インデックスが付与されている場合でもバルクインサートの方が10倍以上速いです。
バルクインサートを使用するとトランザクション数が減り、ログ出力回数が減り、インデックス処理がまとめて行われ、ディスク書き込み回数が減り、並列処理が行われるため処理速度が向上するそうです。

注意点

バルク挿入は高速ですが、無制限にまとめれば良いわけではありません。
SQLサーバーの構造やテーブル設計の意味を踏まえると、10～20行程度が最も安定して高速と考えられます。
特に1レコードのサイズが大きい場合や、インデックスが複数存在する場合は、過剰なバルク挿入が逆に性能を劣化させることもあります。
MySQL/MariaDBでは max_allowed_packet（SQL文の長さ制限）、レコードサイズなどにも配慮し、最適な同時挿入行数を慎重に設計することをお勧めします。

PHPサンプル集一覧へ