INSERT文を一括処理して5万件のデータ追加を12倍高速化する

PHP

2022/10/25 (更新日：2022/10/25)

もう半年以上前になりますが、「PHPでSQLiteからMySQLへテーブル構造を変換しデータも移行する例」と題して、SQLiteからMySQLへデータベースを移行するPHPスクリプトを公開／解説しました。

半年で1,400人くらいにしか読まれていないので、たぶんあまり需要はないと思いますが、ちょっと必要に迫られてコンバート処理を高速化したので、自分の備忘録代わりに記事にしておきたいと思います。

逐次更新は処理が遅い

前回記事で掲載したデータ更新の処理部分ですが、SQLiteから1行読んで、MySQLへ1行書きこんで、という処理を繰り返しているので処理がとても遅いです。

/************************************************
 * データのコンバート処理 SQLite→MySQL
 *
 ************************************************/
function convertData($db_sqlite, $db_mysql, $table_name)
{
	//INSERTクエリーの準備
	$query = 'INSERT INTO '.$table_name.' (';
	$st = $db_sqlite->query('PRAGMA TABLE_INFO('.$table_name.')');
	$columns = $st->fetchAll();
	$types = array();
	foreach ($columns as $i => $column) {
		if ($i > 0) $query .= ',';
		$query .= $column['name'];
		$types[$column['name']] = strtoupper($column['type']);
	}
	$query .= ') VALUES (';
	$query .= substr(str_repeat(',?', $i + 1), 1);
	$query .= ')';
	//
	echo ''.$query.'';
	$st = $db_mysql->prepare($query);
	$rows = $db_sqlite->query('SELECT * FROM '.$table_name);
	foreach ($rows as $i => $row) {
		$rowInsert = array();
		foreach ($row as $key=>$value) {
			switch ($types[$key]) {
				case 'REAL':
				case 'INTEGER':
					if ($value == '') {
						$value = null;
					}
			}
			$rowInsert[] = $value;
		}
		$st->execute($rowInsert);
	}
}

うちの場合、Intel Celeron J4125という非力だけれど超省電力なCPUを積んだNUCで実行しているため尚更ですが、具体的には7,664件のINSERTに7.1秒、55,389件のINSERTに276秒（4分36秒）もかかっています。

SQLiteからMySQLのデータ移行なんてそうしょっちゅうやるものでもないし、5分くらい良いんじゃね？と思われるかも知れませんが、これは1つのテーブルの話なので、システム全体だと10分くらいかかるんですね。それでも、まぁ、保守作業中です！とHPに表示してメンテナンスに入ることもできますけれど…　もしかしたらその際にミスが発覚して何度もデータ移行をやりなおすかも知れないし、そのたびに10分かかっていたらメンテナンス完了まで結局数時間もシステムを止めることになった、なんてことにもなりかねません。

そんなわけで、こいつをもうちょっと高速にできないか、と考えてみました。

逐次INSERTと一括INSERTの違い

SQL文の書き方がほんのちょっと違うだけです。

■逐次INSERT

INSERT INTO user (id, name) VALUES (1, '山田一郎');
INSERT INTO user (id, name) VALUES (2, '山田二郎');
INSERT INTO user (id, name) VALUES (3, '山田三郎');

■一括INSERT

INSERT INTO user (id, name) VALUES (1, '山田一郎'), (2, '山田二郎'), (3, '山田三郎');

3行程度だと実感わかないかも知れませんが、これが数百、数千行となってくると目に見えて速度が変わってきます。

まぁそりゃそうなんですよね、1行ごとにSQL文発行してたら（ローカルだったとしても）その都度TCP/IP通信が発生するので、オーバーヘッドが馬鹿になりません。

数リットルの水ならコップとバケツで速度はほとんど変わりませんが、プール一杯の水を移動するとなったら入れ物の大きさを大きくしたほうが良いに決まっています。

PHPでINSERT文を使って一括更新する例

ちょっと長くなりましたが、while文とforeach文の分岐のところとか、工夫すればもう少しスマートに書ける気もします。

/************************************************
 * データのコンバート処理 SQLite→MySQL
 *
 ************************************************/
function convertData($db_sqlite, $db_mysql, $table_name)
{
	$t1 = microtime(true);
	//SQLite側のデータ件数をチェック
	$st = $db_sqlite->query('SELECT COUNT(*) AS cnt FROM '.$table_name);
	$rowCount = $st->fetch();
	$rows = null;
	$stTable = $db_sqlite->query('SELECT * FROM '.$table_name);
	$stTable->execute();
	if ($rowCount['cnt'] <= 100000) {
		//10万行以下なら全件読み込む
		$rows = $stTable->fetchAll();
	}
	//INSERTクエリーの準備
	$query = 'INSERT INTO '.$table_name.' (';
	//INSERT文作るついでにカラム数とカラムごとのデータ型も取得しておく
	$st = $db_sqlite->query('PRAGMA TABLE_INFO('.$table_name.')');
	$columns = $st->fetchAll();
	$types = array();
	foreach ($columns as $i => $column) {
		if ($i > 0) $query .= ',';
		$query .= $column['name'];
		$types[$column['name']] = strtoupper($column['type']);
	}
	$query .= ') VALUES ';
	$placeholder = '('.substr(str_repeat(',?', $i + 1), 1).')';
	if (is_null($rows)) {
		//1行ずつ処理するためプレースホルダは1行分だけ
		$query .= $placeholder;
	} else {
		//一括INSERTするためプレースホルダもその数用意する
		for($r = 0; $r < count($rows); $r++) {
			if ($r > 0) {
				$query .= ',';
			}
			$query .= $placeholder;
		}
	}
	//
	$stInsert = $db_mysql->prepare($query);
	echo 'INSERT INTO '.$table_name;

	if (is_null($rows)) { //逐次INSERT
		while ($row = $stTable->fetch()) {
			$param_no = 1; //1行ずつ処理するためプレースホルダ番号は毎回1からスタート
			setRow($row, $types, $stInsert, $param_no);
			$stInsert->execute();
		}
	} else { //一括INSERT
		echo ' [BULK] ';
		$param_no = 1; //一括処理なのでプレースホルダ番号は全行カウントする
		foreach ($rows as $row) {
			setRow($row, $types, $stInsert, $param_no);
		}
		$stInsert->execute();
	}
	$t2 = microtime(true);
	echo ' ('.$rowCount['cnt'].'件 '.($t2-$t1).'秒)<br>';
}

解説

解説と言っても、基本はソース内のコメント見ればわかると思いますが、流れとしてはこんな感じ。

テーブル内の件数によって逐次INSERTか一括INSERTか分岐する
一括INSERTの場合、カラム数×行数分のプレースホルダ（ハテナ記号）が必要なため、INSERT実行前にそれを組み立てる
逐次INSERTの場合はwhile文、一括INSERTの場合はforeach文になるため、ここも処理を分岐する
またその際、独自のsetRow関数内でデータ加工（※後述）を行う

なぜすべて一括INSERTで処理しないかというとメモリの都合です。

うちの運営サイトでも30万件くらいのデータを蓄えたテーブルもあり、30万行を一括INSERTするにはメモリが足りませんでした。

ini_set('memory_limit', '512M');

というように、PHP用に512MB確保しても30万件分の一括INSERTは不可能だったので、10万行以下のテーブルなら一括INSERT、それより多いテーブルは1行ずつINSERT文を発行することにしました。

先述したとおり、55,389件あるテーブルのコンバートに276秒かかっていましたが、これを一括INSERTにすることで22秒まで短縮。実に12倍の高速化が実現しました。

SQLiteからMySQLへのデータ移行時に必要な加工について

本題の一括処理とは外れた話題になるので興味のある方向け。

PDOの問題ですが、プレースホルダを使った更新だとNULLの設定が面倒くさいんです。

普通に考えたら、SQLiteから読み込んだ配列を無加工でMySQLへ渡すことも可能のように思えます。

しかし、executeに配列を渡す更新方法ではすべての値がPDO::PARAM_STR型（文字列型）として判定されるというPDO独自の仕様があります。

具体例を挙げるとこんな感じ。

$st = $db->prepare('INSERT INTO product (id, price) VALUES (?, ?)');
$st->execute(array('test', null));

ここではNULLを明示的に指定しているように見えますが、実際に挿入されるのは空文字です。これがPDOの仕様。

SQLiteの場合はデータ型なんてあってないようなもので、INTEGER型に文字列をセットすることも可能なため、数値項目に空文字が入っていたらNULLである、とプログラム側で判定することもできるわけですが…。

MySQLへのデータ移行となるとそうもいきません。

$row = $db_sqlite->query('SELECT * FROM product');
$st = $db_mysql->prepare('INSERT INTO product (id, price) VALUES (?, ?)');
$st->execute(array('test', null));

こんなふうにMySQLの数値項目に（NULLのつもりで）空文字を渡すことになると、ランタイムエラーになってしまいます。

これを回避するためにsetRowという独自の関数を用意して、数値項目に空文字が入っていた場合にはbindValueにPDO::PARAM_NULLを渡し、明示的にNULLだよー、と指定してあげることにしました。

function setRow($row, $types, &$st, &$param_no)
{
	foreach ($row as $key => $value) {
		$value_type = PDO::PARAM_STR;
		switch ($types[$key]) {
			case 'INTEGER':
				$value_type = PDO::PARAM_INT;
			case 'REAL':
			case 'DOUBLE':
				if ($value == '') {
					$value = null;
					$value_type = PDO::PARAM_NULL;
				}
				break;
			case 'TEXT':
				//文字化け対策
				$value = mb_convert_encoding($value, 'UTF-8', 'UTF-8');
		}
		$st->bindValue($param_no, $value, $value_type);
		$param_no++;
	}
}

NULL判定をしているのがINTEGER/REAL/DOUBLEだけなのは、うちのシステムの都合ですね。

SQLiteの仕様を利用（悪用？）し、数値項目にNULLの代用として空文字を使っていたので、数値項目に関しては空文字＝NULLと判定してPARAM_NULLをセットしていますが、文字列項目にNULLは使っていなかったため、このような書き方になっています。

また、INTEGER型はPDO::PARAM_INTをセットしているのにREAL/DOUBLEに関してはPDO::PARAM_STRにしているのはbindValueの型指定に浮動小数点数型がないからです。仮にREAL/DOUBLEにPDO::PARAM_INT（数値）を指定した場合、1.5が1になります。小数点以下が切り捨てられるんですね。…………なんでPDOでは浮動小数点数型の指定ができないの？

PHP5までは小数点以下が切り捨てられるようなことはなかったのですが、いつのまにか、少なくともPHP7.3以降ではPDO::PARAM_INTを指定した場合、小数点以下が切り捨てられます。というわけで、REAL/DOUBLE型にはPDO::PARAM_STRを指定するのが大正義。…今のところ、ね。

それから最後に、TEXT型で実行しているmb_convert_encodingについては完全にうちのシステム独自の問題で、文字化け回避のためです。

恥ずかしい話、日本語が含まれる文字列を mb_substr ではなく、substrでぶったぎってしまった過去データがまだ残っており、UTF-8として正しくない文字コードがデータベース内に取り残されていたんですね。

SQLiteの場合、そんなおかしな文字コードすら受け入れてしまうので特にエラーにはならないのですが、このデータをMySQLへもっていこうとすると当然エラーになります。

よって、mb_convert_encoding($value, 'UTF-8', 'UTF-8');という、UTF-8からUTF-8への変換、という一見無駄な処理を行うことで、UTF-8として正しくない文字を排除しています。

このあたりの話については「PHPのjson_encodeで空白が返ってきてしまう場合のエラー確認方法」でも解説していますので、興味があればどうぞ。

記事書きながら思ったんですが、こんなデータ修正はDB移行前にやっておけよって気がしたので、たぶんそうします。

まとめ

SQLiteからMySQLへ移行するすると言い続けて半年も何やってんだって話ですが、数十万PV程度とはいえ、ざっくり計算してみると6秒に1ページ開かれている計算になり、数分間止めるだけでも膨大な影響があることがわかったので、どうやって一瞬で切り替えようかアレコレ考えて夜も寝れずに昼寝しているうちにこんなに時間が経ってしまいました。

ローカル環境では移行が完了しており、SQLite独自の方言（INSERT OR IGNORE文等）や拡張仕様（WHERE句でもエイリアスが使える等）も削除し、ほとんど標準SQLのみ使うようにプログラムも修正済なので、あとはホントにデータを移行する時間だけ。

今回のテストで一括更新処理を入れることで、大幅な時間短縮もできたため、よーやくSQLiteからMySQLへのデータ移行が完了しそうです。

ちなみにSQLiteを廃止する理由が気になる方はこちらの記事「SQLiteのDatabase is lockedで悩む人に見て欲しいロックの話」をどうぞ。Database is lockedの問題はある程度解決可能なのですが、6秒に1ページ読まれる頻度になってくるとロック解除待ちの時間が非常にわずらわしいので、そのくらいの規模のHPならとっととMySQLやPostgreSQLへ移行したほうが良いと思います。

備忘録というわりに長くなりましたが、何かの間違いで（？）たまたまここへ訪れた人のお役に立てば幸いです。

INSERT文を一括処理して5万件のデータ追加を12倍高速化する

逐次更新は処理が遅い

逐次INSERTと一括INSERTの違い

PHPでINSERT文を使って一括更新する例

解説

SQLiteからMySQLへのデータ移行時に必要な加工について

まとめ

関連記事

PHPでCookieを保持しつつWebスクレイピングする例

PHPのjson_encodeで空白が返ってきてしまう場合のエラー確認方法

Windows版PHPでUTF8なCSVファイルを読むならロケールに注意

PHPで文字化けしないHTMLメールを送信するために必要な知識とサンプルコード

コメント

新しいコメントを投稿する