行挿入の時間は、以下の要因によって決定されます。(数はおよその割合を示します。)
接続: (3)
サーバへのクエリの送信: (2)
クエリの解析: (2)
行挿入:(1 × 行サイズ)
エンデックス挿入:(1 × インデックス数)
クローズ: (1)
テーブルを開く初期オーバヘッドは算入されていません(これは同時実行クエリのそれぞれで 1 回実行されます)。
テーブルのサイズによって対数N
の分だけインデックス挿入の速度が低下します(B
ツリー)。
挿入の速度を上げる方法
1
つのクライアントから同時に多数の行を挿入する場合は、マルチプルVALUES
リストでINSERT
ステートメントを使用します。これで独立した
INSERT
ステートメントの使用時と比較して大幅に(場合によっては数倍)速度が上がります。空ではないテーブルにデータを追加する場合は、さらに速度を上げるためにbulk_insert_buffer_size
変数を調整します。項4.2.3. 「システム変数」を参照してください。
異なる複数のクライアントから大量のレコードを挿入する場合は、INSERT
DELAYED
ステートメントを使用すると速度を上げることができます。項12.2.4.2. 「INSERT DELAYED
構文」を参照してください。
MyISAM
テーブルでは、テーブルに削除された行がない場合、SELECT
の実行と同時に行を挿入できることに注意してください。項6.3.3. 「同時挿入」を参照してください。
テキストファイルからテーブルをロードする場合は
LOAD DATA
INFILE
を使用します。通常、これは
INSERT
ステートメントを使用する場合と比較して、20
倍速度が上がります。項12.2.5. 「LOAD DATA INFILE
構文」を参照してください。
テーブルにインデックスが多数ある場合、操作を少し追加するだけでMyISAM
テーブルのLOAD
DATA INFILE
の実行速度をさらに上げることができます。以下の手順を使用してください。
CREATE
TABLE
を使用して、テーブルを作成します。
FLUSH
TABLES
ステートメントまたはmysqladmin
flush-tablesコマンドを実行します。
myisamchk --keys-used=0 -rq
/path/to/db/tbl_name
を使用します。これでテーブルからすべてのインデックスの使用が削除されます。
LOAD DATA
INFILE
を使用して、テーブルにデータを挿入します。これはインデックスをまったく更新しないため、非常に高速になります。
テーブルを読み取り専用にする場合は、myisampackを実行してテーブルを小さくします。項13.4.3.3. 「圧縮テーブルの特徴」を参照してください。
myisamchk -rq
/path/to/db/tbl_name
を使用してインデックスを作成しなおします。これは、ディスクに書き込む前にメモリにインデックスツリーを作成してディスクシークを回避するため、LOAD
DATA
INFILE
中のインデックス更新が非常に高速になります。生成されたインデックスツリーは完全にバランスが取られています。
FLUSH
TABLES
ステートメントまたはmysqladmin
flush-tablesコマンドを実行します。
データを挿入したMyISAM
テーブルが空の場合は、LOAD
DATA
INFILE
は上記の最適化を自動的に実行します。上記手順との主な相違点は、LOAD
DATA
INFILE
ステートメントの実行中にサーバにインデックスの再作成を割り当てる場合より、myisamchkにインデックス作成用のテンポラリメモリ割り当てるほうが、より大幅に割り当てることができる点です。
myisamchkよりも以下のステートメントを使用して、MyISAM
のインデックス利用を可能にしたり、不可能にしたりできます。これらのステートメントを使用すると、FLUSH
TABLE
オペレーションをスキップできます。
ALTER TABLEtbl_name
DISABLE KEYS; ALTER TABLEtbl_name
ENABLE KEYS;
非トランザクショナルテーブル上で、複数ステートメントを使用して実行されるINSERT
速度を上げるには、テーブルをロックしてください。
LOCK TABLES a WRITE; INSERT INTO a VALUES (1,23),(2,34),(4,33); INSERT INTO a VALUES (8,26),(6,29); ... UNLOCK TABLES;
主な速度の相違点は、すべての
INSERT
ステートメントの完了後にインデックスバッファが
1
回のみディスクにフラッシュされることです。通常は、INSERT
ステートメントの数と同じだけ、インデックスバッファのフラッシュが行われます。すべての行を
1
つのINSERT
で挿入できる場合はロックの必要がありません。
トランザクショナルテーブルの場合は、LOCK
TABLES
ではなく START
TRANSACTION
および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,000 行ごと)LOCK
TABLES
にUNLOCK TABLES
を続けて実行して、他のスレッドからのテーブルへのアクセスを可能にすることができます。これでもパフォーマンの増加が得られます。
アウトラインストラテジー使用時でも、データのロードにはLOAD
DATA
INFILE
のほうがINSERT
よりも大幅に高速です。
MyISAM
テーブルおよびLOAD
DATA
INFILE
とINSERT
の両方に対するパフォーマンスの向上には、key_buffer_size
システム変数値を上げてキーキャッシュを拡張します。項6.5.2. 「サーバパラメータのチューニング」を参照してください。
MySQL Enterprise. サーバのパフォーマンスを最適化するための詳しいアドバイスについては、MySQL Network Monitoring and Advisory Serviceを購読してください。多数のアドバイザーがパフォーマンス向上をサポートします。追加情報については http://www-jp.mysql.com/products/enterprise/advisors.htmlを参照してください。