やること
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秒 |