2019/08/19にリリースされたDBeaver 6.1.5から、MySQL用のドライバはMySQL8用になった影響で、レガシーなMySQL4には接続できなくなりました。 接続するには手動で旧ドライ…
フリーのデータベースとして有名なMySQLは利用者も多いと思います。
テーブルにレコードをINSERTする時、100件程度なら問題になることはありませんが、
一万、十万、百万件にもなってくるとINSERTの仕方によって処理時間が何十倍にも変わってきます。
目次
SQLのINSERT構文を使用する
通常のSQLでデータをINSERTする場合は以下の記述ですが、これは最も遅いです。
ちょっとした数十件程度をINSERTする時に使用する場合に使用します。
また、SQLクライアントソフトが生成するINSERT文は基本のこの形が多いです。
標準のINSERT文
INSERT INTO db_name.tbl_name (col_name1, col_name2, ...) VALUES (value1, value2, ...);
バルクインサート
VALUES句をカンマ区切りで複数記述することで、1トランザクション内でまとめてINSERTされます。
構文自体に件数制限はありませんが、数千～数万件になると実行可能なSQL文のバイト数を超えてエラーになる可能性が出てきます。
その場合、バルクインサート文を複数に分けるか他の方法を検討しましょう。
また、バルクインサート文を複数に分ける場合には、BEGIN TRANSACTION～END TRANSACTIONで1トランザクションにまとめてコミットするようにしましょう。
バルクインサート文
INSERT INTO db_name.tbl_name (col_name1, col_name2, ...) VALUES (value1, value2, ...) ,(value1, value2, ...) .(value1, value2, ...) ...;
SELECT結果をINSERT
VALUES句の代わりにSELECT文を記述することが出来ます。
この場合、SELECTで得られた結果を1回のINSERT文で入れることが出来るため、バルクインサートよりも高速です。
INSERT内容がSELECT結果から得られる場合はこちらを使用しましょう。
SELECT結果インサート文
INSERT INTO db_name.tbl_name (col_name1, col_name2, ...) SELECT * FROM db_name.tbl_name2;
CSVファイルから直接取込
ここまでは基本のSQL主体の方法で、MySQLに限らず大方のDBで共通です。
ここからは他のDBでも似たような発想・機能がありますが、より大量のデータを高速に取り込む為のMySQL独自のものを紹介します。
LOAD DATA INFILE 構文
CSVファイルのパスを直接指定してテーブルへ取り込む構文が用意されています。
数十万～数百万件のデータを移行する場合は通常のSQL文ではなく、DBごとに拡張された構文を使用しましょう。
MySQLリファレンスによると、LOAD DATA INFILE構文は以下の通り、とても高速です。
これは INSERT ステートメントを使用する場合より、20 倍速くなります。
基本的な構文はファイルのフルパスとINSERT対象のテーブルを指定します。
LOAD DATA INFILE文
LOAD DATA INFILE 'file_name' INTO TABLE db2.my_table;
LOAD DATA INFILE 構文はMySQLが実行されているサーバー内に置かれたファイルパスを指定する必要があります。
自身のPC上でMySQLが動いている場合は問題ありませんが、サーバーが別の場合は後述のLOAD DATA LOCAL INFILE 構文を使用しましょう。
LOAD DATA INFILE 構文は高速でデータ解釈や重複キーエラー時、その時点で停止されます。
LOAD DATA LOCAL INFILEはデータ転送を途中で止めることは出来ないため、すべて実行した後に警告が表示されます。
この構文を使っても時間が掛かるような膨大なデータの場合、LOAD DATA INFILEで処理した方が安全でしょう。
LOAD DATA LOCAL INFILE 構文
MySQLサーバーの実行環境とは別の端末から、端末内のファイルを取り込む場合の構文です。
LOCALオプションを付ける以外は、ファイルのフルパスがサーバーではなく端末ローカルのフルパスへ変わります。
LOAD DATA LOCAL INFILE文
LOAD DATA LOCAL INFILE 'file_name' INTO TABLE db2.my_table;
Advertisement
オプションについて
LOAD DATA INFILE構文は様々なオプションが用意されています。
（ファイルの区切り文字・データの囲み文字・改行コード・対象カラムの指定等）
詳細は公式リファレンスを参照下さい。
取込ファイルの文字コードの指定は構文内ではなく、「SET character_set_database=[文字コード(sjis)];」でファイルの文字コードを指定します。
パフォーマンスに関する詳細
MySQL公式リファレンスで「INSERTステートメントによる速度」について記述があります。
Google等で検索すると簡単に色々な情報が出てきますが、解決への近道は情報元がしっかりしている公式リファレンスをよく読むことなので、一読することをお勧めします。
