MySQLに大量のデータを入れるときに最適な方法は？
データベースへ大量データを入れる時、誤った投入方法を選択してしまうと、ものすごい時間がかかってしまいます。
大量データの投入には、LOAD(ロード)、IMPORT(インポート)、プログラムからINSERT(インサート)、どのような方法が適しているのでしょうか？
それぞれの方法によってどれくらい時間に差が出るのか検証してみました。
Contents
いきなりですが、結果から
今回検証した結果は次の通りです。
| LOAD | IMPORT | INSERT 一括コミット |
INSERT 都度コミット |
|---|---|---|---|
| 30秒 | 58秒 | 144秒 | 2,656秒 |
大量データの投入にはLOADを利用しよう
大量データの投入にはLOADを利用するようにしましょう。
実際に大量データを投入する場合は、
- プログラムでLOAD用の入力ファイルを作成
- LOADでデータ投入
の流れになります。
公式サイトにもLOADめちゃ早いよ！と書かれています。
テキストファイルからテーブルをロードする場合は LOAD DATA INFILE を使用します。通常、これは INSERT ステートメントを使用する場合より、20 倍速くなります。
出典：https://dev.mysql.com/doc/refman/5.6/ja/insert-speed.html
プログラムで投入する場合は、コミット処理を明示しよう
また、最も遅かったのが自動コミットありのPHP処理です。
PDO等のモジュールはデフォルトでコミット処理がINSERTごとに実行されてしまいます。コミット処理は非常に重い処理になるので、データ件数が多いと膨大な時間が消費されることになります。
プログラムで投入する場合は、必ず自動コミットをOFFに設定し、複数レコードをまとめてコミットするようにトランザクションを制御しましょう。
検証内容
実際に行った検証内容についてもまとめておきます。
検証パターン
次の3パターンのデータ投入方法で速度比較してみます。
- パターン1. LOAD(ロード)を利用
- パターン2. Import(インポート)を利用
- パターン3. PHPからINSERT(一括コミット
- パターン4. PHPからINSERT(都度コミット
検証用テーブル
ec-cube2系の商品テーブル（dtb_products）を使ってみました。
投入データ
100万件のデータを入れることを想定します。
投入方法
EC-CUBE2の初期データのアイスクリームの「product_id」を、インクリメントしてデータを増幅させています。
LOAD用投入データ
データサンプル
1,'アイスクリーム',NULL,1,NULL,NULL,'アイス,バニラ,チョコ,抹茶',NULL,NULL,NULL,NULL,'暑い夏にどうぞ。','ice130.jpg','冷たいものはいかがですか?','ice260.jpg','ice500.jpg',NULL,'<b>おいしいよ<b>',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,2,'2016-09-13 09:22:23','2016-09-13 09:22:23',2
LOADコマンド
LOAD DATA INFILE "/tmp/eccube.csv" INTO TABLE dtb_products FIELDS TERMINATED BY "," LINES TERMINATED BY "\n";
Import用投入データ
LOADしたデータをダンプして利用しています。
PHPからINSERT(一括コミット
PHPのPDOを利用してデータを投入しています。
こちらは、トランザクションの制御を明示的に行い、100万件を一括でコミットするようにしています。データが多い場合は適当な件数でコミットしたほうがいいと思います。
$dbh = new PDO($dsn, $user, $password);
$dbh->query('SET NAMES sjis');
$dbh->beginTransaction();
for ( $i=0;$i<1000000;$i++ ){
$sql = 'INSERT INTO `dtb_products` VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)';
$stmt = $dbh->prepare($sql);
$flag = $stmt->execute(array($i,'アイスクリーム',NULL,1,NULL,NULL,'アイス,バニラ,チョコ,抹茶',NULL,NULL,NULL,NULL,'暑い夏にどうぞ。','ice130.jpg','冷たいものはいかがですか?','ice260.jpg','ice500.jpg',NULL,'<b>おいしいよ<b>',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,2,'2016-09-13 09:22:23','2016-09-13 09:22:23',2));
}
$dbh->commit();
PHPからINSERT(都度コミット
PHPのPDOを利用してデータを投入しています。明示的に宣言していたトランザクション制御をコメントアウトしています。INSERTのたびにコミットが発行されます。
$dbh = new PDO($dsn, $user, $password);
$dbh->query('SET NAMES sjis');
# $dbh->beginTransaction();
for ( $i=0;$i<1000000;$i++ ){
$sql = 'INSERT INTO `dtb_products` VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)';
$stmt = $dbh->prepare($sql);
$flag = $stmt->execute(array($i,'アイスクリーム',NULL,1,NULL,NULL,'アイス,バニラ,チョコ,抹茶',NULL,NULL,NULL,NULL,'暑い夏にどうぞ。','ice130.jpg','冷たいものはいかがですか?','ice260.jpg','ice500.jpg',NULL,'<b>おいしいよ<b>',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,2,'2016-09-13 09:22:23','2016-09-13 09:22:23',2));
}
# $dbh->commit();
結果
それぞれのパターンを3回づつ実行した結果は次の通りです。
| 試行回数 | LOAD | IMPORT | INSERT 一括コミット |
INSERT 都度コミット |
|---|---|---|---|---|
| 1回目 | 27秒 | 61秒 | 121秒 | 2,681秒 |
| 2回目 | 31秒 | 59秒 | 123秒 | 2,567秒 |
| 3回目 | 32秒 | 55秒 | 121秒 | 2,720秒 |
| 平均 | 30秒 | 58秒 | 122秒 | 2,656秒 |
参考
検証用テーブル
dtb_productsは下記のようなテーブルです。
mysql> desc dtb_products; +-------------------+-------------+------+-----+---------------------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------+-------------+------+-----+---------------------+-------+ | product_id | int(11) | NO | PRI | NULL | | | name | text | NO | | NULL | | | maker_id | int(11) | YES | | NULL | | | status | smallint(6) | NO | | 2 | | | comment1 | text | YES | | NULL | | | comment2 | text | YES | | NULL | | | comment3 | mediumtext | YES | | NULL | | | comment4 | text | YES | | NULL | | | comment5 | text | YES | | NULL | | | comment6 | text | YES | | NULL | | | note | text | YES | | NULL | | | main_list_comment | text | YES | | NULL | | | main_list_image | text | YES | | NULL | | | main_comment | mediumtext | YES | | NULL | | | main_image | text | YES | | NULL | | | main_large_image | text | YES | | NULL | | | sub_title1 | text | YES | | NULL | | | sub_comment1 | mediumtext | YES | | NULL | | | sub_image1 | text | YES | | NULL | | | sub_large_image1 | text | YES | | NULL | | | sub_title2 | text | YES | | NULL | | | sub_comment2 | mediumtext | YES | | NULL | | | sub_image2 | text | YES | | NULL | | | sub_large_image2 | text | YES | | NULL | | | sub_title3 | text | YES | | NULL | | | sub_comment3 | mediumtext | YES | | NULL | | | sub_image3 | text | YES | | NULL | | | sub_large_image3 | text | YES | | NULL | | | sub_title4 | text | YES | | NULL | | | sub_comment4 | mediumtext | YES | | NULL | | | sub_image4 | text | YES | | NULL | | | sub_large_image4 | text | YES | | NULL | | | sub_title5 | text | YES | | NULL | | | sub_comment5 | mediumtext | YES | | NULL | | | sub_image5 | text | YES | | NULL | | | sub_large_image5 | text | YES | | NULL | | | sub_title6 | text | YES | | NULL | | | sub_comment6 | mediumtext | YES | | NULL | | | sub_image6 | text | YES | | NULL | | | sub_large_image6 | text | YES | | NULL | | | del_flg | smallint(6) | NO | | 0 | | | creator_id | int(11) | NO | | NULL | | | create_date | timestamp | NO | | CURRENT_TIMESTAMP | | | update_date | timestamp | NO | | 0000-00-00 00:00:00 | | | deliv_date_id | int(11) | YES | | NULL | | +-------------------+-------------+------+-----+---------------------+-------+ 45 rows in set (0.00 sec)
この記事へのコメントはこちら