MySQLのINSERT文を高速化する

仕事で「MySQL」を使うことになったのだが、Notes/DominoのLotusScript（Visual Basic互換の言語）のForループの中から、ADODBオブジェクトを使って、ODBC接続でINSERT文をくりかえしMySQLサーバへ発行すると、処理速度が遅いのが気になった。

MySQLのドキュメントで「INSERTクエリの速度」の部分を読むと、「複数ステートメント」を利用すると高速になるという。「INSERT INTO table_a VALUES (1, 23), (2, 24), (3, 25)」のように、値の組を複数指定するINSERT文の書き方だ。

ためしに、かんたんなテーブルを定義して、VBScriptで1000件のデータを、1件ずつ1000回INSERTするのと、2件ずつ500回と、4件ずつ250回と、8件ずつ125回INSERTするのを比べると、37秒、19秒、9秒、4秒という具合に、所要時間が正確に２分の１になっていく。

たった1000件で37秒もかかるのはおかしいと思ったら、初期状態ではトランザクションが自動コミットモードになっていることに気づいた。そこで「SET AUTOCOMMIT=0」で自動コミットでなくして、「START TRANSACTION」「COMMIT」で明示的にトランザクションを開始・終了するようにした。

すると、まったく同じ処理が、2秒、1秒、0秒、0秒になった（VBScriptで1/1000秒単位で実行時間を計測する方法が分からなかったので秒単位になっている）。

MySQLのドキュメントによれば、テキストファイルからデータを読み込む「LOAD DATA」コマンドを使うともっと高速だとあったので、同じくADODBオブジェクトを使ったODBC接続で実行してみると、0秒まで短縮した。

読込み件数を10,000件に増やしても0秒と、VBScriptでは計測不能である。100,000件にしてようやく3秒と、計測可能になった。1件ずつINSERTする処理と比較すると、数百倍の処理速度ということになる。

なお、WindowsクライアントでMySQLの「LOAD DATA」コマンドを実行するときは、行末文字を指定する必要があるようだ。

LOAD DATA INFILE （ファイル名） INTO TABLE （テーブル名） FIELDS TERMINATED BY （データ区切り文字） LINES TERMINATED BY '\r\n'

このように行区切り文字として「'\r\n'」を指定しないと、「\r」が文字データの一部分として読み込まれてしまう。