naoberry.com Open in urlscan Pro
133.130.99.45  Public Scan

Form analysis
2 forms found in the DOM

POST https://naoberry.com/tech/wp-comments-post.php

<form action="https://naoberry.com/tech/wp-comments-post.php" method="post" id="commentform" class="comment-form">
  <p class="comment-notes">メールアドレスは公開されませんのでご安心ください。<br>また、<span class="required">*</span> が付いている欄は必須項目となりますので、必ずご記入をお願いします。</p>
  <p class="comment-form-comment"><label for="comment">コメント</label> <textarea id="comment" name="comment" cols="45" rows="8" maxlength="65525" required="required"></textarea></p>
  <p class="form-allowed-tags">内容に問題なければ、下記の「コメント送信」ボタンを押してください。</p>
  <p class="comment-form-author"><label for="author">名前 <span class="required">*</span></label> <input id="author" name="author" type="text" value="" size="30" maxlength="245" required="required"></p>
  <p class="comment-form-email"><label for="email">メール <span class="required">*</span></label> <input id="email" name="email" type="text" value="" size="30" maxlength="100" required="required"></p>
  <p class="comment-form-url"><label for="url">サイト</label> <input id="url" name="url" type="text" value="" size="30" maxlength="200"></p>
  <p class="comment-form-cookies-consent"><input id="wp-comment-cookies-consent" name="wp-comment-cookies-consent" type="checkbox" value="yes"> <label for="wp-comment-cookies-consent">次回のコメントで使用するためブラウザーに自分の名前、メールアドレス、サイトを保存する。</label></p>
  <p class="form-submit"><input name="submit" type="submit" id="submit" class="submit" value="コメント送信"> <input type="hidden" name="comment_post_ID" value="627" id="comment_post_ID">
    <input type="hidden" name="comment_parent" id="comment_parent" value="0">
  </p>
  <p style="display: none;"><input type="hidden" id="akismet_comment_nonce" name="akismet_comment_nonce" value="35233ba8ed"></p>
  <p style="display: none;"><input type="hidden" id="ak_js" name="ak_js" value="37"></p>
</form>

GET https://naoberry.com/tech/

<form role="search" method="get" id="searchform" action="https://naoberry.com/tech/">
  <div>
    <input type="text" value="" name="s" id="s" placeholder="キーワード検索">
    <button type="submit" id="searchsubmit"><i class="fa fa-search"></i></button>
  </div>
</form>

Text Content

NAVI
 * BOOK
 * TECH
 * JMeter


なおべりーのブログ


LINUXやレバメモなどを書いてます

ホーム >
linux >
ベンチマーク >


MYSQLに大量のデータを入れるときに最適な方法は？

2016/09/13   2016/09/14

データベースへ大量データを入れる時、誤った投入方法を選択してしまうと、ものすごい時間がかかってしまいます。

大量データの投入には、LOAD(ロード)、IMPORT(インポート)、プログラムからINSERT(インサート)、どのような方法が適しているのでしょうか？

それぞれの方法によってどれくらい時間に差が出るのか検証してみました。

Contents

 * 1 いきなりですが、結果から
   * 1.1 大量データの投入にはLOADを利用しよう
   * 1.2 プログラムで投入する場合は、コミット処理を明示しよう
 * 2 検証内容
   * 2.1 検証パターン
   * 2.2 検証用テーブル
   * 2.3 投入データ
   * 2.4 投入方法
     * 2.4.1 LOAD用投入データ
     * 2.4.2 Import用投入データ
     * 2.4.3 PHPからINSERT(一括コミット
     * 2.4.4 PHPからINSERT(都度コミット
 * 3 結果
 * 4 参考
   * 4.1 検証用テーブル


いきなりですが、結果から

今回検証した結果は次の通りです。

LOAD IMPORT INSERT
一括コミット INSERT
都度コミット 30秒 58秒 144秒 2,656秒


大量データの投入にはLOADを利用しよう

大量データの投入にはLOADを利用するようにしましょう。

実際に大量データを投入する場合は、

 1. プログラムでLOAD用の入力ファイルを作成
 2. 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用投入データ

データサンプル




Shell

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
1
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コマンド




Shell

LOAD DATA INFILE "/tmp/eccube.csv" INTO TABLE dtb_products FIELDS TERMINATED BY
"," LINES TERMINATED BY "\n";
1
LOAD DATA INFILE "/tmp/eccube.csv"  INTO TABLE dtb_products FIELDS TERMINATED BY
","  LINES TERMINATED BY "\n";



IMPORT用投入データ

LOADしたデータをダンプして利用しています。

PHPからINSERT(一括コミット

PHPのPDOを利用してデータを投入しています。
こちらは、トランザクションの制御を明示的に行い、100万件を一括でコミットするようにしています。データが多い場合は適当な件数でコミットしたほうがいいと思います。




PHP

$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();
1
2
3
4
5
6
7
8
9
10
   $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のたびにコミットが発行されます。




PHP

$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();
1
2
3
4
5
6
7
8
9
10
   $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は下記のようなテーブルです。




Shell

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)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
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)



ベンチマーク
Sponsored Link

スポンサードリンク


 * 
 * 
 * 
 * 
 * 


関連記事-こちらもどうぞ

 * * 
   * hdparmコマンドでディスクの読み出し性能を簡易測定する
 * * 
   * ddコマンドでディスクの書き込み性能を簡易測定する
 * * 
   * dbenchでDISK IO性能(書き込み)を測定する




この記事へのコメントはこちら コメントをキャンセル

メールアドレスは公開されませんのでご安心ください。
また、* が付いている欄は必須項目となりますので、必ずご記入をお願いします。

コメント

内容に問題なければ、下記の「コメント送信」ボタンを押してください。

名前 *

メール *

サイト

次回のコメントで使用するためブラウザーに自分の名前、メールアドレス、サイトを保存する。







PREV：文字コードを指定してファイルを読む方法 NEXT：PostgreSQLのExplain analyzeを読みやすくする方法
 * 
 * 
 * 
 * 




 * JMETERの使い方
   
   

 * カテゴリー
   
   * AWS
   * linux
   * linux小ネタ
   * wordpress
   * ツール
   * プログラム
   * ベンチマーク
   * レバメモ
   * 言語

 * 最近の投稿
   
   * SimpleMindProを購入してハマったこと
   * ズボラPDCA
   * 【2019年改定後】AWS 認定ソリューションアーキテクト プロフェッショナル(SAP)合格のために勉強したこと
   * pythonの使い方をざっとまとめてみた
   * MySQLのスロークエリについてのまとめ
   * AWS 認定ソリューションアーキテクト – アソシエイト（SAA)合格のために勉強したこと
   * Linuxのaliasコマンドを活用してみる
   * 二段階認証でLinuxサーバへログインする方法
   * New Relicでアプリケーションを丸裸にしよう
   * PostgreSQLのExplain analyzeを読みやすくする方法
   * MySQLに大量のデータを入れるときに最適な方法は？
   * 文字コードを指定してファイルを読む方法
   * ddコマンドでディスクの書き込み性能を簡易測定する
   * hdparmコマンドでディスクの読み出し性能を簡易測定する
   * bashの特殊変数「SECONDS」で処理の実行時間を計測する
   * 覚えておくと便利なシェルスクリプトサンプル
   * 未経験でも可能！在宅から働ける仕事 5選
   * Cygwin上でバッチ処理を作成するうえで、利用するであろうコマンド
   * Cygwinを使って、Windows環境でシェルとjavaを実行する方法
   * crontabコマンドで処理を定期実行させる方法
   * XY座標のデータをアニメーションにする方法
   * FTPのアクティブモードとパッシブモードの違いは、データ転送の接続方向
   * PHPの単体テストを自動化したかったので、phpunitを試してみた。
   * topコマンドで、サーバシステムと各プロセスのモニタリングを行う
   * rsyncコマンドでデータの同期を行う
   * NIFTY CloudのREST APIを使うために必要な知識
   * Fluentd、ElasticsearchとKibanaが使えそうな一つのシチュエーション
   * エクセル（Excel)お役立ち技
   * Apacheのcombinedログの解析
   * WordPressを高速化する方法
   * [便利サイト] slowcop～サイトの構成を把握
   * [便利サイト] ALEXA～WEBの視聴率調査
   * 最近のメーラーの使い方はGTD風？
   * 自分だけの検証環境を作る方法
   * kSarを使って、apacheのcombinedログをグラフ化する方法
   * ksarを使って、sarの結果をグラフ化する方法
   * sarコマンドでLinuxサーバのシステムモニタリングを行う方法
   * dbenchでDISK IO性能(書き込み)を測定する
   * UnixBenchでLinuxサーバのCPU性能を比較する



NEW エントリー

2021.04.06 Tue
SimpleMindProを購入してハマったこと

2020.03.20 Fri
ズボラPDCA

2020.01.04 Sat
【2019年改定後】AWS 認定ソリューションアーキテクト プロフェッショナル(SAP)合格のために勉強したこと

2019.08.15 Thu
pythonの使い方をざっとまとめてみた

2018.05.31 Thu
MySQLのスロークエリについてのまとめ

2018.04.21 Sat
AWS 認定ソリューションアーキテクト – アソシエイト（SAA)合格のために勉強したこと

2017.09.28 Thu
Linuxのaliasコマンドを活用してみる

2016.11.10 Thu
二段階認証でLinuxサーバへログインする方法

2016.11.08 Tue
New Relicでアプリケーションを丸裸にしよう

2016.09.29 Thu
PostgreSQLのExplain analyzeを読みやすくする方法



©2022 なおべりーのブログ

WordPress Theme Gush2