Skip navigation links
4.2.22 UPDATE ステートメントの速度 »
Section Navigation      [Toggle]

4.2.21. INSERT ステートメントの速度

行挿入の時間は、次の要因によって決定されます。(数はおよその割合を示します。

  • 接続: (3)

  • サーバーへのクエリーの送信: (2)

  • クエリーの解析: (2)

  • 行挿入: (1 × 行サイズ)

  • インデックス挿入: (1 × インデックス数)

  • クローズ: (1)

テーブルを開く初期オーバヘッドは算入されていません (これは同時実行クエリーのそれぞれで 1 回実行されます)。

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

挿入の速度を上げる方法

  • 1 つのクライアントから同時に多数の行を挿入する場合は、マルチプル VALUES リストで INSERT ステートメントを使用します。これで独立した INSERT ステートメントの使用時と比較して大幅に (場合によっては数倍) 速度が上がります。空ではないテーブルにデータを追加する場合は、さらに速度を上げるために bulk_insert_buffer_size 変数を調整します。Server System Variables を参照してください。

  • 複数のクライアントから大量のレコードを挿入する場合は、INSERT DELAYED ステートメントを使用すると速度を上げることができます。項8.2.5.2. 「INSERT DELAYED 構文」 を参照してください。

  • MyISAM テーブルでは、データファイルの途中に削除された行がない場合、SELECT の実行と同時に行を挿入できることに注意してください。項4.3.3. 「同時挿入」 を参照してください。

  • テキストファイルからテーブルをロードする場合は LOAD DATA INFILE を使用します。通常、これは INSERT ステートメントを使用する場合と比較して、20 倍速度が上がります。項8.2.6. 「LOAD DATA INFILE 構文」 を参照してください。

  • テーブルにインデックスが多数ある場合、操作を少し追加するだけで MyISAM テーブルの LOAD DATA INFILE の実行速度をさらに上げることができます。次の手順を使用します。

    1. CREATE TABLE を使用して、テーブルを作成します。

    2. FLUSH TABLES ステートメントまたは mysqladmin flush-tables コマンドを実行します。

    3. myisamchk --keys-used=0 -rq /path/to/db/tbl_name を使用します。これでテーブルからすべてのインデックスの使用が削除されます。

    4. LOAD DATA INFILE を使用して、テーブルにデータを挿入します。これはインデックスをまったく更新しないため、非常に高速になります。

    5. テーブルを読み取り専用にする場合は、myisampack を実行してテーブルを小さくします。Compressed Table Characteristics を参照してください。

    6. myisamchk -rq /path/to/db/tbl_nameを使用してインデックスを作成し直します。これは、ディスクに書き込む前にメモリーにインデックスツリーを作成してディスクシークを回避するため、LOAD DATA INFILE 中のインデックス更新が非常に高速になります。生成されたインデックスツリーは完全にバランスが取られています。

    7. FLUSH TABLES ステートメントまたは mysqladmin flush-tables コマンドを実行します。

    データを挿入する MyISAM テーブルが空の場合は、LOAD DATA INFILE は上記の最適化を自動的に実行します。自動的な最適化と上記の明示的な手順との主な相違点は、LOAD DATA INFILE ステートメントの実行時にサーバーでインデックスの再作成のために割り当てる量に比べて、myisamchk でははるかに多量の一時メモリーをインデックス作成のために割り当てることができる点です。

    myisamchk の代わりに次のステートメントを使用して、MyISAM テーブルの一意でないインデックスの利用を可能にしたり、不可能にしたりすることもできます。これらのステートメントを使用すると、FLUSH TABLE オペレーションをスキップできます。 

    ALTER TABLE tbl_name DISABLE KEYS;
ALTER TABLE tbl_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 を使用して速度の改善を図るようにしてください。

    ロックは複数の同時接続テストの合計時間も短縮するが、一部のスレッドの最大待機時間は長くなります。(ロックの際に待機するため)次のように 5 つのクライアントが同時に挿入を実行すると仮定します。

    • 接続 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 システム変数値を上げてキーキャッシュを拡張します。項4.5.3. 「サーバーパラメータのチューニング」 を参照してください。