#やること
100万件データを取得して、指定のテーブルに追加します。存在しているレコードを項目を更新する、存在しないレコードを新規追加します。
#前提
更新したいテーブルのサイズは11.6GB、レコード数は5000万件です。
SELECT ... FOR INSERT INTO ON DUPLICATE KEY UPDATE
とlimit offsetで1000件ずつ必要な情報を取得してテーブルにINSERT INTO ON DUPLICATE KEY UPDATEを検証しましたけど、結局遅いです。LOAD DATA INFILEで早そうで検証しました。
##まずレコードを全部取得してCSVファイルに書き込む
SELECT name, id
INTO OUTFILE '/var/lib/mysql-files/result.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
FROM test
注意：INTO OUTFILEのファイル名が存在したら、エラーになります。
##CSVファイルをテーブルにLOADする
LOAD DATA INFILEはON DUPLICATE KEY UPDATEがサポートされないですが、LOAD DATA INFILE REPLACEがサポートされます。
###LOAD DATA INFILE REPLACE
LOAD DATA INFILE '/var/lib/mysql-files/result.csv' REPLACE INTO TABLE result_table
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'
(name, id, tel, mail);
↓
Query OK, 2365032 rows affected (6 min 0.14 sec)
Records: 1182516 Deleted: 1182516 Skipped: 0 Warnings: 0
遅い。
原因は：replaceは、キーが重複した場合、その古い行は新しい行が挿入される前に削除されるので、INSERT ON DUPLICATE KEY UPDATE より遅い。
###LOAD DATA INFILE with ON DUPLICATE KEY UPDATE
- temporaryテーブル作成
CREATE TEMPORARY TABLE temporary_table
(
`seq` int(11) unsigned NOT NULL AUTO_INCREMENT,
`id` int(11) unsigned NOT NULL,
`name` varchar(255) NOT NULL,
...
PRIMARY KEY (`seq`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
余計なindexなど全部いりません。
注意
CREATE TEMPORARY TABLE temporary_table `LIKE` test
も作成できますが、もしテーブルtestがロックされたら、LIKEで作成できません。
- CSVファイルをTEMPORARYテーブルにLOAD
LOAD DATA INFILE '/var/lib/mysql-files/result.csv'
INTO TABLE temporary_table
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(id, name ...);
Query OK, 1182516 rows affected (5.42 sec)
Records: 1182516 Deleted: 0 Skipped: 0 Warnings: 0
- TEMPORARYテーブルのデータを目的テーブルに追加方法①
INSERT INTO result_table (`id`, `name`...)
SELECT * FROM temporary_table
ON DUPLICATE KEY UPDATE name = VALUES(name);
Query OK, 2364762 rows affected (43.54 sec)
Records: 1182516 Duplicates: 1182381 Warnings: 0
- TEMPORARYテーブルのデータを目的テーブルに追加方法②
TEMPORARYテーブルのseqを活用して1000件ずつselectして目的テーブルに追加
INSERT INTO result_table (`id`, `name`...)
SELECT * FROM temporary_table
WHERE seq > 'offset_start'
AND seq <= 'offset_end'
ON DUPLICATE KEY UPDATE name = VALUES(name);
totoal time: 35.23 sec
上記でテーブルロックも解消できるし、速度は一番早いです。
##まとめ
MySQLで巨大データを追加してON DUPLICATE KEY UPDATEの場合、INSERT INTO ON DUPLICATE KEY UPDATE よりLOAD DATA INFILEを活用したほうが早いです。
| 実装方法 | 実装時間 |
|---|---|
| SELECT ... FOR INSERT INTO ON DUPLICATE KEY UPDATE | 1分55秒 |
| limit offsetでINSERT INTO ON DUPLICATE KEY UPDATE | 10分 |
| LOAD DATA INFILE REPLACE | 6分14秒 |
| LOAD DATA INFILE with ON DUPLICATE KEY UPDATE | 35.23秒 |