バルクJSON to SQLインサートのパフォーマンス最適化

バルクインサートのパフォーマンス最適化

大量のJSONデータセット（数千〜数百万レコード）をSQLに変換する際、素朴な行ごとのINSERTは非常に遅くなります。いくつかのテクニックでインポート速度を10倍〜100倍向上させることができます。

バッチINSERTのサイジング

行ごとに1つのINSERTを実行する代わりに、マルチバリュー文に行をバッチ化します：

-- 遅い：行ごとに1つのINSERT
INSERT INTO events (id, type) VALUES (1, 'click');
INSERT INTO events (id, type) VALUES (2, 'view');
INSERT INTO events (id, type) VALUES (3, 'click');

-- 速い：バッチINSERT
INSERT INTO events (id, type) VALUES
  (1, 'click'),
  (2, 'view'),
  (3, 'click');

最適なバッチサイズはデータベースによって異なりますが、通常は文あたり500〜5000行です。

トランザクションラッピング

すべてのインサートを単一トランザクションでラップして、文ごとのコミットオーバーヘッドを回避します：

BEGIN;
INSERT INTO events (id, type) VALUES (1, 'click'), (2, 'view'), ...;
INSERT INTO events (id, type) VALUES (5001, 'click'), (5002, 'view'), ...;
COMMIT;

COPYコマンド（PostgreSQL）

最大速度を得るには、PostgreSQLのCOPYコマンドがCSV/TSVを直接読み込みます：

COPY events (id, type, timestamp) FROM STDIN WITH (FORMAT csv);
1,click,2024-06-15
2,view,2024-06-15
\.

コンバーターはJSON配列からCOPY互換の出力を生成できます。

LOAD DATA INFILE（MySQL）

MySQLのバルクロードに相当するコマンド：

LOAD DATA INFILE '/tmp/events.csv'
INTO TABLE events
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';

インデックスと制約の管理

バルクインサート中にインデックスと制約を一時的に無効にすると、速度が劇的に向上します：

-- バルクインサート前
ALTER TABLE events DISABLE TRIGGER ALL;     -- PostgreSQL
SET FOREIGN_KEY_CHECKS = 0;                 -- MySQL

-- ... バルクインサート ...

-- バルクインサート後
ALTER TABLE events ENABLE TRIGGER ALL;
SET FOREIGN_KEY_CHECKS = 1;

パフォーマンス比較

方法	行/秒の目安（PostgreSQL）
個別INSERT	100-500
バッチINSERT（1000行）	10,000-50,000
COPYコマンド	100,000-500,000