MySQL で大量 INSERT を高速化するテクニック

数千〜数百万行のデータを INSERT する場面では、1 行ずつ愚直に実行すると時間がかかりすぎる。MySQL には大量 INSERT を効率化するための仕組みがいくつも用意されており、状況に応じて使い分けることでスループットを大幅に改善できる。

複数行 INSERT でラウンドトリップを減らす

最も基本的かつ効果的なのが、複数行をまとめて 1 つの INSERT 文で送る方法だ。1 行ずつ INSERT すると、行ごとに SQL の解析・実行・コミットが走る。複数行 INSERT ならこれを 1 回で済ませられる。

具体的には次のように書く。

-- 遅い: 1 行ずつ
INSERT INTO products (name, price) VALUES ('Apple', 100);
INSERT INTO products (name, price) VALUES ('Banana', 200);
INSERT INTO products (name, price) VALUES ('Cherry', 300);

-- 速い: まとめて
INSERT INTO products (name, price) VALUES
  ('Apple', 100),
  ('Banana', 200),
  ('Cherry', 300);

1 回の INSERT にまとめる行数は 1,000〜10,000 行程度が目安となる。max_allowed_packet の上限を超えないように注意が必要で、デフォルトでは 64MB に設定されている環境が多い。

LOAD DATA INFILE でファイルから一括読み込み

CSV や TSV ファイルからデータを投入するなら、LOAD DATA INFILE が最速の手段になる。SQL パーサーを経由せずにストレージエンジンへ直接データを渡すため、INSERT 文を組み立てるオーバーヘッドが丸ごとなくなる。

LOAD DATA INFILE '/tmp/products.csv'
INTO TABLE products
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(name, price, category);

公式ドキュメントでも LOAD DATA は INSERT と比較して 20 倍ほど高速になるケースがあると記載されている。リモートクライアントからファイルを送る場合は LOAD DATA LOCAL INFILE を使うが、セキュリティ上 local_infile 変数を ON にする必要がある点に留意しよう。

トランザクションで自動コミットを抑制する

InnoDB はデフォルトで各 INSERT を自動コミットする。コミットのたびにログへの fsync が発生するため、大量行では深刻なボトルネックになる。明示的にトランザクションで囲めば、コミットは最後の 1 回だけで済む。

START TRANSACTION;

INSERT INTO products (name, price) VALUES ('Apple', 100);
INSERT INTO products (name, price) VALUES ('Banana', 200);
-- ... 数千行
INSERT INTO products (name, price) VALUES ('Mango', 500);

COMMIT;

ただし、1 つのトランザクションに数百万行を詰め込むと undo ログが肥大化してロールバックに時間がかかるリスクがある。数万行ごとにコミットするバッチ方式がバランスのよい選択肢だ。

インデックスとユニークチェックを一時的に無効化する

空のテーブルに大量データを初期投入する場合、インデックスの更新コストが大きな割合を占める。データ投入中だけインデックス更新を止めて、あとからまとめて再構築する方法が有効だ。

-- キーの更新を無効化
ALTER TABLE products DISABLE KEYS;

-- 大量 INSERT 実行
LOAD DATA INFILE '/tmp/products.csv' INTO TABLE products ...;

-- キーを再構築
ALTER TABLE products ENABLE KEYS;

DISABLE KEYS は MyISAM テーブルで効果を発揮する。InnoDB の場合はユニークチェックと外部キーチェックを一時的にオフにすることで同様の効果が得られる。

SET unique_checks = 0;
SET foreign_key_checks = 0;

-- 大量 INSERT 実行

SET unique_checks = 1;
SET foreign_key_checks = 1;

これらの設定はセッション単位で反映される。データの整合性が保証できる前提で使うこと。投入データに重複や不正な外部キーが含まれていると、チェックを戻したあとに問題が顕在化する。

innodb_flush_log_at_trx_commit の調整

InnoDB のログ書き込み戦略を制御する innodb_flush_log_at_trx_commit は、大量 INSERT 時のパフォーマンスに直結する設定だ。

デフォルトの 1 は ACID 準拠だが最も遅い。大量データ投入時だけ一時的に 2 や 0 に変更し、終わったら 1 に戻す運用が実用的だ。値を 0 にするとクラッシュ時に最大 1 秒分のデータが失われる可能性があるため、本番環境では慎重に判断する必要がある。

-- 一時的に緩める
SET GLOBAL innodb_flush_log_at_trx_commit = 2;

-- 大量 INSERT 実行

-- 元に戻す
SET GLOBAL innodb_flush_log_at_trx_commit = 1;

テクニックの組み合わせ

ここまで紹介した手法は排他的ではなく、組み合わせることで相乗効果が得られる。

初期データ投入では全テクニックを併用し、日常的な運用バッチでは複数行 INSERT とトランザクション制御の組み合わせが安全かつ効果的な選択になる。環境に応じてベンチマークを取りながら最適な組み合わせを見つけていこう。