はじめに
CSVで取り込んだ配列のエンコードを変えた上で、連想配列化したいと思いました。
最終的に文字コードをUTF-8にします。
件数が少ないうちは問題なかったのですが、実データ（10万レコード・200カラム）を流し込んだ際に処理スピードがとんでもないことになったのでチューニングしました。
やったこと
- INSERT処理を単行から複数行（マルチプルインサート・バルクインサート）へ変更
- CSV変換処理のチューニング
バルクインサートとマルチプルインサートは別物でした。
今回やってることは広義にはマルチプルインサートですが、件数的に一回では処理ができていないのでマルチプルとバルクの合わせ技的な感じですかね。
バルクインサート
1回のコミットで複数のインサートを実行。コミットの回数が少ない分処理が早い。
BEGIN;
INSERT INTO tbl_name (a, b, c) VALUES (1, 2, 3);
INSERT INTO tbl_name (a, b, c) VALUES (4, 5, 6);
INSERT INTO tbl_name (a, b, c) VALUES (7, 8, 9);
COMMIT;
>マルチプルインサート
1つのクエリで複数行分のレコードをインサート。コミットももちろん1回だけ。
>```
BEGIN;
INSERT INTO tbl_name (a, b, c) VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9);
COMMIT;
配列
/* SJIS-WIN */
$csv_array = [
0 => [
0 => '1',
1 => 'test user1',
2 => '090-9999-9999',
3 => '28',
// more columns...
],
1 => [
0 => '2',
1 => 'test user2',
2 => '090-9999-9999',
3 => '42',
// more columns...
],
// more rows...
];
/* key array */
$keys = [
0 => 'id',
1 => 'name',
2 => 'phone',
3 => 'age',
// more keys...
];
/* 最終的にほしい配列 */
$rows = [
0 => [
'id' => '1',
'name' => 'test user1',
'phone' => '090-9999-9999',
'age' => '28',
// more columns...
],
1 => [
'id' => '2',
'name' => 'test user2',
'phone' => '090-9999-9999',
'age' => '42',
// more columns...
],
// more rows...
];
バッド・プラクティス
$rows = [];
foreach ($csv_array as $line => $csv_line) {
foreach ($csv_line as $i => $buf) {
$key = $keys[$i];
$rows[$line][$key] = mb_convert_encoding($buf, 'UTF-8', 'ASCII, JIS, UTF-8, EUC-JP, SJIS-win'); // ※1
}
}
try () {
// トランザクション開始
\DB::connection('mysql_sample')->beginTransaction();
$table = \DB::connection('mysql_sample')->table('sample');
foreach ($rows as $row) {
// 1レコード追加
$table->insert($row);
}
// 問題なければコミット
\DB::connection('mysql_sample')->commit();
} catch (\Exception $e) {
// 問題が発生すればロールバック
\DB::connection('mysql_sample')->rollback();
echo $e->getTraceAsString();
}
変更後
/*
配列をまとめてエンコーディングする
PHP5.6・7.0・7.1だとバグがある点に注意する ※2
変換結果は元の配列にセットされる
*/
mb_convert_variables('UTF-8', 'SJIS-WIN', $csv_array);
// キー配列を指定して一括置換
$rows = array_combine($keys, $csv_array);
try () {
// トランザクション開始
\DB::connection('mysql_sample')->beginTransaction();
$table = \DB::connection('mysql_sample')->table('sample');
$bulk_counter = 0; // カラム数カウンタ
$bulk_rows = []; // 一括INSERT用配列
foreach ($rows as $row) {
$counter = count($row);
$bulk_rows[] = $row;
/*
プリペアドステートメントは65536パラメータをオーバーするとエラーとなる ※3
そのため、カラム数が規定値を上回った時点でINSERTを実行している
*/
if ($counter + $bulk_counter > 65500) {
$table->insert($bulk_rows);
$bulk_rows = [];
$bulc_counter = 0;
} else {
$bulk_counter += $counter;
}
}
if ($bulk_rows !== [null]) {
$table->insert($bulk_rows);
}
\DB::connection('mysql_sample')->commit();
} catch (\Exception $e) {
\DB::connection('mysql_sample')->rollback();
echo $e->getTraceAsString();
}
注意事項
※1：mb_convert_encodingにおける文字コードの明示指定について
こちらを参照。
第3引数の $from_encoding には変換前の文字エンコーディング名を指定しますが、ここを"auto"と指定しておくと、環境によっては
Warning: mb_convert_encoding(): Unable to detect character encoding
のようなエラーが発生し、文字エンコーディングの変換が失敗する場合があります。
ですので、"auto" は極力使わず、文字エンコーディングを指定することをおすすめします。
※2：mb_convert_variablesのエラーについて
こちらを参照。
PHP 5.6.30 or PHP 7.0.16 or PHP 7.1.1 現在、発生するバグの模様で、修正のプルリクエストが投げられている模様。
※3：プリペアドステートメントの一度に発行できる上限値
こちらに気になる記載あり。
think the number of placeholders is limited to 65536 per query (at least in older mysql versions).
（私はプレースホルダの数が（少なくとも古いmysqlバージョンでは）クエリごとに65536に制限されていると思います。）
その他
- MySQL文にも送信できる上限値がある点（こちら）に気をつける
10万レコード・200カラムをINSERTした結果
| 処理 | 時間 |
|---|---|
| バッドプラクティス | 30分超
|
| 変更後 | 5分23秒 |
所感
ずっと大量のデータをINSERTするのに時間がかかっていると思っていたら、自分の書いたコードに大部分を使っていたことが判明。
自分のコーディングを反省するとともに、改めて標準で実装されている関数の素晴らしさを感じることができました。