MySQL マニュアル manual - 5.2.10 INSERT クエリの速度

日本語 MySQL マニュアル

Previous / Next / Up / Table of Contents

5.2.10. `INSERT` クエリの速度

レコード挿入の時間構成の概要は次のとおりです。

接続:（3）
サーバへのクエリの送信:（2）
クエリの解析:（2）
レコード挿入:（1 x レコードサイズ）
インデックス挿入:（1 x インデックス数）
クローズ:（1）

ここに示した数値は、時間全体を比例的に配分したものです。テーブルを開く初期オーバヘッドは算入されていません（これは同時実行クエリのそれぞれで 1 回実行されます）。

テーブルのサイズによって対数 N の分だけインデックス挿入の速度が低下します（B ツリー）。

挿入の速度を上げる方法

1 つのクライアントから同時に多数のレコードを挿入する場合はマルチプル INSERT ステートメントを使用する。これで独立した INSERT ステートメントの使用時と比較して大幅に（場合によっては数倍）速度が上がる。空ではないテーブルにデータを追加する場合は、さらに速度を上げるために bulk_insert_buffer_size 変数を調整する。 See 項4.6.8.4. 「SHOW VARIABLES」。
異なる複数のクライアントから大量のレコードを挿入する場合は、INSERT DELAYED ステートメントを使用すると速度を上げることができる。 See 項6.4.3. 「INSERT 構文」。
MyISAM テーブルでは、テーブルに削除されたレコードがない場合、SELECT の実行と同時にレコードを挿入できることに注意する。
テキストファイルからテーブルをロードする場合は LOAD DATA INFILE を使用する。通常、これは INSERT ステートメントを多数使用する場合と比較して、20 倍速度が上がる。 See 項6.4.8. 「LOAD DATA INFILE 構文」。
テーブルにインデックスが多数ある場合、操作を少し追加するだけで LOAD DATA INFILE の実行速度をさらに上げることができる。以下の手順を使用する。
1. CREATE TABLE を使用して、テーブルを作成する。mysql や Perl-DBI などを使用する。
2. FLUSH TABLES ステートメントまたはシェルコマンド mysqladmin flush-tables を実行する。
3. myisamchk --keys-used=0 -rq /path/to/db/tbl_name を使用する。これでテーブルからすべてのインデックスの使用が削除される。
4. LOAD DATA INFILE を使用し、テーブルにデータを挿入する。これはインデックスをまったく更新しないため、非常に高速になる。
5. テーブルを読み取り専用にする場合は、myisampack を実行してテーブルを小さくする。 See 項7.1.2.3. 「圧縮テーブルの特性」。
6. myisamchk -r -q /path/to/db/tbl_name を使用してインデックスを作成しなおす。これは、ディスクに書き込む前にメモリにインデックスツリーを作成して、ディスクシークを回避するため非常に高速になる。生成されたインデックスツリーは完全にバランスが取られている。
7. FLUSH TABLES ステートメントまたはシェルコマンド mysqladmin flush-tables を実行する。
空のテーブルへ挿入する場合は、LOAD DATA INFILE は上記の最適化を実行します。上記手順との主な相違点は、myisamchk にインデックス作成用のテンポラリメモリを大幅に割り当てることができる点です。
MySQL 4.0 以降は、myisamchk --keys-used=0 -rq /path/to/db/tbl_name の代わりに ALTER TABLE tbl_name DISABLE KEYS を、また myisamchk -r -q /path/to/db/tbl_name の代わりに ALTER TABLE tbl_name ENABLE KEYS を使用することもできます。このようにすると、FLUSH TABLES ステップをスキップすることもできます。
複数ステートメントを使用して実行される挿入の速度を、テーブルをロックすることによって上げることができる。
```
mysql> LOCK TABLES a WRITE;
mysql> INSERT INTO a VALUES (1,23),(2,34),(4,33);
mysql> INSERT INTO a VALUES (8,26),(6,29);
mysql> UNLOCK TABLES;
```
主な速度の相違点は、すべての INSERT ステートメントの完了後にインデックスバッファが 1 回のみディスクにフラッシュされることである。通常は、INSERT ステートメントの数と同じだけ、インデックスバッファのフラッシュが行われる。すべてのレコードを 1 つのステートメントで挿入できる場合はロックの必要がない。
トランザクションテーブルの場合は、LOCK TABLES ではなく BEGIN/COMMIT を使用して速度の改善を図る。
ロックは複数の同時接続テストの合計時間も短縮するが、一部のスレッドの最大待機時間は長くなる（ロックの際に待機するため）。次の例を参照してください。
```
スレッド 1 は 1000 レコードをインサート
スレッド 2, 3, 4 は 1 レコードをインサート
スレッド 5 は 1000 レコードをインサート
```
ロックを使用しない場合、2、3、4 は 1 と 5 の前に終了する。ロックを使用した場合は、2、3、4 は 1 と 5 の前には終了しない確率が高くなるが、合計時間は約 40% 短縮される。
MySQL では、INSERT、UPDATE、および DELETE の演算が非常に速いため、約 5 つより多い挿入や 1 レコード更新する前にロックを追加すると総合的なパフォーマンスを改善できる。1 行で非常に多数の挿入を実行する場合は、ときどき（約 1,000 レコードごと）LOCK TABLES に UNLOCK TABLES を続けて実行して、他のスレッドからのテーブルへのアクセスを可能にすることができる。これでもパフォーマンの増加が得られる。
言うまでもなく、データのロードには LOAD DATA INFILE のほうが大幅に高速である。