インサートの処理方式別のパフォーマンスについて検証してみました。
検証環境は以下の通りです。
- サーバ：DELL PowerEdge860 (1U）
- CPUIntel(R) Xeon(R) CPU 3050 @ 2.13GHz 2cores
- メモリー：4G
- ディスク：SEAGATE Model: ST373455SS (SAS,15000rpm,キャッシュ16M）
- ※ローカルディスク、NO RAIDで利用
- OS ：CentOS5.2 x86_64 （Red Hat Enterprise Linux 互換OS)
- カーネル：2.6.18-92.el5 #1 SMP Tue Jun 10 18:51:06 EDT 2008
- FileSystem : ext3 (LVM )
MySQLのインサート方式は用途に応じて、以下が選択できます。
- 通常の１件づつのインサート
- マルチプルインサート（１回の発行で複数行セットをインサート）
- ※MySQL独自構文です
- ※当たり前ですがアプリケーションの仕様上、登録行セットをまとめることが可能な場合のみ使用できます
- 大量データの一括ロード時はバルクロード
バルクロード では、可変長データ（CSV等）、固定長データからのデータロードができます。コードはこんな感じ。
CODE:
-
LOAD DATA INFILE “import_data.csv” INTO TABLE tableA FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”’;
マルチプルインサートは、１発行で複数行セットを挿入するできます。コードはこんな感じ。
CODE:
-
INSERT INTO emp (empid,name,country) VALUES(1,’taro’,’jpn’),(2,’jiro’,’usa’),(3,’saburo’,’ita’),(4,’shiro’,’chu’);
※１インサート文のサイズは、初期化パラメータ max_allowed_packet以内となることが条件
通常はもちろんこうです
CODE:
-
INSERT INTO emp (empid,name,country) VALUES(1,’taro’,’jpn’);
-
INSERT INTO emp (empid,name,country) VALUES(2,’jiro’,’usa’);
処理方式別のインサート時間比較
データ長約200bytes、100万件（約200Mbytes）のデータを投入した場合の比較 （単位：秒）
| インサート処理方式 | １インサートの行セット数 | MySQL 5.0.79 | MySQL 5.1.30 | ||
|---|---|---|---|---|---|
| InnoDB | MyISAM | InnoDB | MyISAM | ||
| バルクロード | 31.99 | 31.24 | 37.79 | 31.51 | |
| マルチプルインサート | 1,000行 | 78.86 | 2,104.75 | 95.39 | 2,048.20 |
| 500行 | 102.88 | 2,560.96 | 132.01 | 2,392.40 | |
| 100行 | 300.38 | 3,554.03 | 370.88 | 3,393.88 | |
| 通常のインサート※ | 1行 | 25,459.00 | 27,887.00 | 28,284.00 | 29,797.00 |
※遅すぎたので1000件分を計測し×1000で算出
【参考】1,000万件ロード時
| インサート処理方式 | MySQL 5.0.79 ／ MySQL 5.1.30 | |
|---|---|---|
| InnoDB | MyISAM | |
| バルクロード | 805.78 ／ 864.08 | 366.28 ／ 371.06 |
インサート性能をまとめると
- 通常のインサート（一件づつ）ならInnoDBとMyISAMはあまり変わらない
- InnoDBのインサートは5.0系のほうが少し速い
- マルチプルインサートは通常のインサートより非常に速い
- 100件づつなら84倍、1000件づつなら322倍
- MyISAMのマルチプルインサートは通常のインサートより速いがInnoDBほど効果がない
- バルクロードは通常のインサートの800倍くらい速い
ということになります。
