大量データの INSERT / UPDATE パフォーマンスチューニング
akinori概要
数万〜数百万件規模のデータを RDB へ投入・更新するバッチ処理では、素朴な「1 行ずつループして INSERT / UPDATE」がそのまま数十分〜数時間のボトルネックになります。本記事では、大量データの INSERT / UPDATE を速くするための実践的なチューニング手法を、効果の大きい順にハンズオン形式でまとめます。対象は MySQL (InnoDB) と PostgreSQL ですが、考え方は他の RDB にも応用できます。
環境
- MySQL 8.0 (InnoDB) / PostgreSQL 15
- アプリケーション側は PHP (PDO) / Python を想定
- 検証用テーブルは 100 万件規模を投入
CREATE TABLE sales (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
order_code VARCHAR(32) NOT NULL,
customer_id BIGINT NOT NULL,
amount DECIMAL(12,2) NOT NULL,
status TINYINT NOT NULL DEFAULT 0,
created_at DATETIME NOT NULL,
updated_at DATETIME NOT NULL,
UNIQUE KEY uq_order_code (order_code),
KEY idx_customer (customer_id)
) ENGINE=InnoDB;
発生した問題
あるデータ移行で、約 100 万件を 1 件ずつ INSERT する処理を書いたところ、完了まで 40 分以上かかりました。処理内容は単純で、CPU もディスク I/O も飽和していないのに遅い、という典型的な状況です。
// アンチパターン: 1件ずつ autocommit で INSERT
foreach ($rows as $row) {
$stmt = $pdo->prepare(
"INSERT INTO sales (order_code, customer_id, amount, status, created_at, updated_at)
VALUES (?, ?, ?, ?, ?, ?)"
);
$stmt->execute([
$row['order_code'], $row['customer_id'], $row['amount'],
0, $row['created_at'], $row['updated_at'],
]);
}
原因
遅さの正体は SQL そのものではなく、1 行ごとに発生するオーバーヘッドの積み重ねでした。
- 1 行ごとのコミット（autocommit） — InnoDB はコミットのたびにトランザクションログを
fsyncします。100 万回のディスク同期が支配的なコストになります。 - 1 行ごとのネットワーク往復 — クライアント ⇄ サーバ間のラウンドトリップが 100 万回発生します。
- prepare をループ内で毎回実行 — 構文解析・実行計画生成のコストが毎回かかります。
- 全インデックス・制約をリアルタイム維持 — 投入のたびにインデックス更新とユニーク制約チェックが走ります。
つまり「正しいけど遅い」典型で、ボトルネックは 1 件あたりの固定費 × 件数でした。
対応方法
1. 複数行 INSERT（バルクインサート）
function bulkInsert(PDO $pdo, array $rows, int $chunk = 1000): void
{
foreach (array_chunk($rows, $chunk) as $batch) {
$placeholders = implode(',', array_fill(0, count($batch), '(?,?,?,?,?,?)'));
$sql = "INSERT INTO sales
(order_code, customer_id, amount, status, created_at, updated_at)
VALUES $placeholders";
$params = [];
foreach ($batch as $row) {
array_push($params,
$row['order_code'], $row['customer_id'], $row['amount'],
0, $row['created_at'], $row['updated_at']);
}
$pdo->prepare($sql)->execute($params);
}
}
チャンクサイズは 500〜2000 程度が無難です。大きすぎると MySQL の max_allowed_packet や PostgreSQL のパラメータ上限（65535）に当たるので注意します。
2. トランザクションでまとめてコミット
$pdo->beginTransaction();
try {
foreach (array_chunk($rows, 1000) as $batch) {
// ... バルクインサート ...
}
$pdo->commit();
} catch (\Throwable $e) {
$pdo->rollBack();
throw $e;
}
1 トランザクションに全件を詰め込むと undo ログやロック保持が肥大化するため、数千〜数万件ごとに区切ってコミットするのが実務的な落としどころです。
3. UPSERT で INSERT / UPDATE を 1 文に
MySQL:
INSERT INTO sales (order_code, customer_id, amount, status, created_at, updated_at)
VALUES (?, ?, ?, ?, ?, ?)
ON DUPLICATE KEY UPDATE
amount = VALUES(amount), status = VALUES(status), updated_at = VALUES(updated_at);
PostgreSQL:
INSERT INTO sales (order_code, customer_id, amount, status, created_at, updated_at)
VALUES (?, ?, ?, ?, ?, ?)
ON CONFLICT (order_code) DO UPDATE SET
amount = EXCLUDED.amount, status = EXCLUDED.status, updated_at = EXCLUDED.updated_at;
4. 大量 UPDATE は CASE 式 or 一時テーブル JOIN
-- CASE 式でまとめる
UPDATE sales
SET status = CASE id WHEN 101 THEN 1 WHEN 102 THEN 2 WHEN 103 THEN 1 END,
updated_at = NOW()
WHERE id IN (101, 102, 103);
-- 件数が多い場合は一時テーブル JOIN CREATE TEMPORARY TABLE tmp_status (id BIGINT PRIMARY KEY, status TINYINT); -- tmp_status にバルクインサート後 UPDATE sales s JOIN tmp_status t ON s.id = t.id SET s.status = t.status, s.updated_at = NOW();
5. 超大量投入は専用ローダを使う
-- PostgreSQL: CSV を直接高速ロード COPY sales (order_code, customer_id, amount, status, created_at, updated_at) FROM '/path/to/sales.csv' WITH (FORMAT csv, HEADER true);
MySQL なら LOAD DATA INFILE。INSERT 文より桁違いに速くなります。
6. インデックスの一時無効化（初期ロード限定）
ALTER TABLE sales DROP INDEX idx_customer; -- ... データ投入 ... ALTER TABLE sales ADD INDEX idx_customer (customer_id);
運用中・参照中のテーブルでは制約チェックが効かなくなり危険なので、初期ロードや隔離された移行時のみに限定します。
実装例
import psycopg2
from psycopg2.extras import execute_values
def upsert_sales(conn, rows, chunk=1000):
sql = """
INSERT INTO sales
(order_code, customer_id, amount, status, created_at, updated_at)
VALUES %s
ON CONFLICT (order_code) DO UPDATE SET
amount = EXCLUDED.amount, status = EXCLUDED.status, updated_at = EXCLUDED.updated_at
"""
with conn.cursor() as cur:
for i in range(0, len(rows), chunk):
execute_values(cur, sql, rows[i:i + chunk], page_size=chunk)
conn.commit()
確認方法
-- MySQL EXPLAIN ANALYZE UPDATE sales s JOIN tmp_status t ON s.id = t.id SET s.status = t.status; -- PostgreSQL EXPLAIN (ANALYZE, BUFFERS) INSERT INTO sales (...) VALUES (...) ON CONFLICT ...;
アプリ側では処理時間とスループット（件/秒）をログに出し、before / after を比較します。実際に 100 万件移行は「バルク化＋トランザクション区切り＋UPSERT 化」で 40 分 → 数分オーダーまで短縮できました（件数・ハードウェア・スキーマで変動するため、数値は目安です）。
注意点
- チャンクサイズとコミット間隔はトレードオフ。実測して決める。
- UPSERT のユニークキー設計が前提。衝突判定キーが曖昧だと誤更新・重複が発生する。
- インデックス無効化は運用中テーブルで使わない。
- 外部キー制約が多いテーブルは更新順序に注意する。
- 本番適用前に必ずステージングで件数相当の負荷をかけて計測する。
まとめ
大量 INSERT / UPDATE の遅さは、SQL の書き方よりも「1 件あたりの固定費 × 件数」で決まることが多いです。(1) バルク化、(2) トランザクション区切り、(3) UPSERT、をまず入れるだけで多くのバッチは実用速度に乗ります。最後は必ず計測して、推測ではなく数値で効果を確認してください。
＼ 最新情報をチェック ／
