INSERT INTO SELECT FROM構文とは？
任意の条件でSELECTした結果を別のテーブルにINSERTする構文です。
複数レコードを一括で挿入するため、1レコードずつ処理するより高速に動作し
SQLで完結するためアプリケーションサーバのメモリにも優しいです。
各種サマリー作成の他、単純なテーブルのコピーにも便利です。
INSERT INTO SELECT FROMのロック
高速に動作し、日常的に書くことの多い構文ですが、ロックが設定されるため注意が必要です。
以下、MySQLのデフォルトであるREPEATABLE READのトランザクション分離レベルを利用した場合です。
INSERT側テーブルはロックなし
公式ドキュメントにauto incrementが指定されている場合、
更新用の特殊な排他ロックが設定される記載がありますが
読み込み、書き込みともにスムーズに流れます。
SELECT側テーブルは共有ネクストキーロック
共有ロックのため読み込みは流れますが、書き込みは待ち状態になります。
ロック対象はSELECTの探査と共に増加し、INSERTの完了まで解除されないため
書き込み頻度の高いテーブルから大量のデータを移動する用途では
高確率でタイムアウトが発生します。
ロックを回避するためには？
INSERT INTO B SELECT * FROM A;
単純なテーブルコピーを行う場合の回避策の検討です。
方法1. トランザクション分離レベルの変更
# トランザクション分離レベルの変更
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
INSERT INTO B SELECT * FROM A;
トランザクション分離レベルを変更してSELECT側のロックを回避します。
ファントムリードなどが発生する可能性がありますが、
修正が容易で対応工数は少なくすみます。
方法2. TEMPテーブルにスナップショットを退避
# トランザクション分離レベルの変更
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
# 中間テーブルを作成
CREATE TEMPORARY TABLE C AS SELECT * FROM A;
# 中間テーブルからコピー
INSERT INTO B SELECT * FROM C;
SELECT側のロックを回避した状態で、
セッション内でしか読み書きされない中間テーブルを作成しています。
中間テーブルのロックを気にする必要がなく、
途中からトランザクションを開始することも可能です。
データの整形やINSERT前の検証が必要な場合に便利な折衷案ですが
データコピーが2回走るため、実行時間は2倍に伸びます。
方法3. LOAD DATA IN (LOCAL) FILEの利用
# ファイル作成
SELECT * FROM A INTO OUTFILE '/tmp/a.tsv';
# ファイル読み込み
LOAD DATA LOCAL INFILE '/tmp/a.tsv' INTO TABLE B;
同じく複数レコードを一括挿入する目的の構文です。
INSERT文より高速に動作し、SELECTのロックはFOR UPDATEなどで任意に指定可能です。
ローカルにファイルが作成されるため、
多重実行時の保存場所やセキュリティ、ディスク容量など考えることが多くなります。
エスケープや区切り文字にも気をつかう必要があり、
必然的に対応工数は肥大化しがちです。
まとめ
書き込み頻度が少ないマスタデータなどでは
そもそも共有ロックの影響を気にする必要はありません。
LOAD DATA IN FILEは優れた代替案ですが、
精度が求められない、内部向けデータ集計などのオペレーションでは、
トランザクション分離レベルの変更は便利な選択肢です。
一長一短あるので、用途に応じて使い分けができると幸せになれます。
採用PR
弊社で一緒に働く仲間を募集しています。
全てのオタクを幸せにしたい方、是非ご覧ください！