PHPでMariaDB(MySQL)のテーブルにバルクインサートで高速にレコード挿入 ～PHPサンプルソースコード集

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

大量のレコードをテーブルに挿入する場合バルク(BULK)インサートを使うと普通に挿入するより圧倒的に速くなります。
後程、CSVファイルをバルクインサートで高速挿入するPHPソースコードと速度検証結果を示しますが、その前にバルクインサートがどのようなSQLなのか解説します。

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

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

普通に３レコード挿入する場合は以下のSQLです。

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);

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

バルクインサートで３レコード同時に挿入する場合は以下のSQLです。

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倍以上速いです。
バルクインサートを使用するとトランザクション数が減り、ログ出力回数が減り、インデックス処理がまとめて行われ、ディスク書き込み回数が減り、並列処理が行われるため処理速度が向上するそうです。

PHPサンプル集一覧へ