> ## Documentation Index > Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt > Use this file to discover all available pages before exploring further. # MySQL での一括挿入 > MySQL に大量のデータを効率よく挿入する方法: 複数行 INSERT、LOAD DATA INFILE、INSERT...SELECT、キーの無効化、およびパフォーマンスのためのトランザクションのバッチ処理。 > [card] [VillageSQL is a drop-in replacement for MySQL with extensions.](https://villagesql.com/docs/en/8.4/stable/install-server.md) > > All examples on this page work on VillageSQL. Install Now → 行を 1 行ずつ挿入することは、大きなインポートを遅くする最も手っ取り早い方法です。MySQL は単一行の `INSERT` をそれぞれ完全なトランザクションのコミット（解析、計画、実行、fsync）として処理します。規模が大きくなると、この行ごとのオーバーヘッドが支配的になります。これを避ける方法を説明します。 ## 複数行 INSERT 最も単純な最適化は、複数の行を 1 つのステートメントにまとめることです。 ```sql theme={null} -- Slow: one round trip and one transaction per row INSERT INTO products (name, price) VALUES ('Widget A', 9.99); INSERT INTO products (name, price) VALUES ('Widget B', 14.99); INSERT INTO products (name, price) VALUES ('Widget C', 19.99); -- Fast: one round trip, one transaction for all three INSERT INTO products (name, price) VALUES ('Widget A', 9.99), ('Widget B', 14.99), ('Widget C', 19.99); ``` バッチサイズは重要です。1 ステートメントあたり数百から数千行が通常は最適です。バッチが大きすぎるとメモリの負荷が増え、トランザクションの時間が長くなります。ワークロードに合った最適な値はベンチマークで見つけてください。1 バッチあたり 500〜2000 行がよくある出発点です。 ## トランザクションのバッチ処理 多数の INSERT を 1 つのトランザクションで囲むと、ステートメントごとのコミットのオーバーヘッドを避けられます。 ```sql theme={null} START TRANSACTION; INSERT INTO products (name, price) VALUES ('Widget A', 9.99); INSERT INTO products (name, price) VALUES ('Widget B', 14.99); -- ... hundreds more ... INSERT INTO products (name, price) VALUES ('Widget Z', 99.99); COMMIT; ``` 明示的なトランザクションがない場合、各 INSERT は自動コミットされます。自動コミットとは、各行が個別にディスクにフラッシュされるということです。500 件の挿入を 1 つのトランザクションで囲めば、フラッシュは 1 回で済みます。 これを複数行 INSERT と組み合わせてください。1 ステートメントあたり 500〜1000 行のグループにまとめ、各バッチをトランザクションで囲みます。 ## LOAD DATA INFILE 大きな CSV のインポートでは、`LOAD DATA INFILE` が MySQL ネイティブの選択肢として最速です。行ごとの SQL 解析を省略するため、バッチ化した INSERT よりも大幅に高速です。 ```sql theme={null} LOAD DATA INFILE '/var/lib/mysql-files/products.csv' INTO TABLE products FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES -- skip header row (name, price, category); ``` ファイルは MySQL サーバープロセスから読み取り可能でなければなりません。デフォルトでは、ファイルパスは `secure_file_priv` で指定されたディレクトリに制限されます。 ```sql theme={null} SHOW VARIABLES LIKE 'secure_file_priv'; ``` サーバーではなくクライアントマシンからファイルをロードするには、次のようにします。 ```sql theme={null} LOAD DATA LOCAL INFILE '/path/on/client/products.csv' INTO TABLE products FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'; ``` `LOCAL INFILE` には、サーバー側で `local_infile = ON` が設定されていること、およびクライアント側で `--local-infile` フラグが有効になっていることが必要です。 ## INSERT ... SELECT テーブル間でデータをコピーする場合（ETL パターン、ステージングテーブル）、`INSERT...SELECT` は、行をアプリケーションに読み込んでから挿入し直すよりも高速です。 ```sql theme={null} -- Load staging table, validate, then move to production INSERT INTO products (name, price, category) SELECT name, price, category FROM products_staging WHERE price > 0 AND name IS NOT NULL; ``` ## 一括ロード中のインデックスの無効化 MyISAM テーブルでは、一括挿入の間に非ユニークインデックスの更新を無効にすると、大幅に高速化します。 ```sql theme={null} ALTER TABLE products DISABLE KEYS; -- ... bulk INSERT ... ALTER TABLE products ENABLE KEYS; -- rebuilds indexes in one pass ``` InnoDB では同じ効果はありません。代わりに、InnoDB はセカンダリインデックスの更新に「チェンジバッファ」を自動的に使います。InnoDB の一括ロードのパフォーマンスを最大化するには、次のようにします。 ```sql theme={null} -- Temporarily increase the buffer pool if possible SET GLOBAL innodb_buffer_pool_size = 4 * 1024 * 1024 * 1024; -- 4GB example -- Disable foreign key checks SET foreign_key_checks = 0; -- ... bulk load ... SET foreign_key_checks = 1; -- Disable unique checks for known-clean data SET unique_checks = 0; -- ... bulk load ... SET unique_checks = 1; ``` ロードが終わったら、直ちにこれらを再度有効にしてください。実際に重複を含むデータに対して `unique_checks` を無効にすると、インデックスが破損します。 ## パフォーマンスの比較 | 方法 | 相対速度 | 最適な用途 | | :- | :- | :- | | 単一行 INSERT（自動コミット） | 最も遅い | 少量、対話的な操作 | | 単一行 INSERT（トランザクションでバッチ化） | 2〜5 倍速い | 中程度の量 | | 複数行 INSERT | 5〜20 倍速い | ほとんどの一括ロード | | LOAD DATA INFILE | 最速（単一行の 10〜100 倍速いことが多い） | 大きな CSV のインポート | | INSERT...SELECT | 速い（サーバー側でのコピー） | テーブル間のコピー | 正確な数値は、行のサイズ、インデックス、ハードウェア、および構成に大きく依存します。個別のケースについてはベンチマークを取ってください。 ## よくある質問 #### INSERT の各バッチはどのくらいの大きさにすべきですか? 500〜1000 行から始めて、ベンチマークを取ってください。バッチが大きいほどコミットごとのオーバーヘッドは減りますが、メモリ使用量とトランザクションの時間（ロックを保持する時間）が増えます。バッチが失敗するとバッチ全体がロールバックされるため、バッチが小さいほどロールバックの作業は少なくなります。ほとんどのアプリケーションでは、最適なバッチサイズは 500〜5000 行の間です。 #### LOAD DATA INFILE を ON DUPLICATE KEY UPDATE と組み合わせて使えますか? `LOAD DATA` は重複の処理として `REPLACE` または `IGNORE` をサポートしますが、`ON DUPLICATE KEY UPDATE` はサポートしません。一括ロード中に upsert のセマンティクスが必要な場合は、ステージングテーブルにロードしてから、ステージングテーブルから対象テーブルへ `INSERT...SELECT...ON DUPLICATE KEY UPDATE` を使います。 #### 一括挿入はトリガーを迂回しますか? いいえ。トリガーは、`LOAD DATA INFILE` と `INSERT...SELECT` でロードされた行を含め、影響を受けるすべての行に対して発火します。トリガーが重い処理を行う場合、一括ロードはそれに比例して遅くなります。純粋なデータ移行では、トリガーを一時的に無効にすることを検討してもよいでしょう。 ## トラブルシューティング | 問題 | 解決策 | | :- | :- | | `ERROR 1290: The MySQL server is running with the --secure-file-priv option` | ファイルを `SHOW VARIABLES LIKE 'secure_file_priv'` で示されるディレクトリに移動するか、`LOAD DATA LOCAL INFILE` を使います | | `ERROR 3948: Loading local data is disabled` | サーバー側で `SET GLOBAL local_infile = 1`、クライアント側で `--local-infile=1` を指定して有効にします | | バッチ化しても一括挿入がまだ遅い | `autocommit` が ON になっていないか確認します。各ステートメントが個別にコミットされます。明示的な `BEGIN...COMMIT` ブロックで囲みます | | `unique_checks` を無効にした後、クリーンなはずのデータで重複キーエラーが出る | `unique_checks = 0` はロード中に一意性を検証しませんが、重複があるとインデックスを破損させます。必ず事前に検証します | | `LOAD DATA` が行を誤って解析する | `FIELDS TERMINATED BY`、`ENCLOSED BY`、および `LINES TERMINATED BY` が実際のファイル形式と一致しているか確認します。ロード後に `SHOW WARNINGS` を使います | これらのロード手法は、拡張機能を備えた MySQL のドロップイン代替である [VillageSQL](https://villagesql.com/install) でもそのまま動作します。 ## 関連項目 * [MySQL のトランザクション](https://villagesql.com/docs/ja/guides/transactions.md) — 安全性と速度のために一括挿入をトランザクションでバッチ化する * [MySQL のスキーママイグレーション](https://villagesql.com/docs/ja/guides/schema-migrations.md) — 一括挿入はデータ移行作業の一部になることが多い * [MySQL の UPSERT](https://villagesql.com/docs/ja/guides/upsert.md) — ロード中の重複キーの処理 * [MySQL で複数行を挿入する](https://villagesql.com/docs/ja/tutorial/insert-multiple-rows.md) — 複数行ステートメントそのもの * [MySQL INSERT SELECT](https://villagesql.com/docs/ja/tutorial/insert-select.md) — クエリが返す行のコピー This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.