LOAD DATA INFILE を使う (MySQL)
MySQL
Published: 2020-11-08

やったこと

データ登録するときに、外部ファイルを読み込む LOAD DATA INFILE を使ってみます。

確認環境

$ mysql --version
mysql  Ver 14.14 Distrib 5.6.25, for Linux (x86_64) using  EditLine wrapper

調査

対象テーブル

移行前テーブル

mysql> select count(id) from bulk_test
    -> ;
+-----------+
| count(id) |
+-----------+
|  16777216 |
+-----------+
1 row in set (6.41 sec)

テーブル名は、bulk_test_1 bulk_test_2 とインクリメントします。

CREATE TABLE `bulk_test_1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `text_data` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `uniq_col` int(11) DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_col` (`uniq_col`)
) ENGINE=InnoDB AUTO_INCREMENT=17366761 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
;

insert select で投入

mysql> INSERT INTO bulk_test_2 SELECT * FROM bulk_test;
Query OK, 16777216 rows affected (1 min 33.62 sec)
Records: 16777216  Duplicates: 0  Warnings: 0
mysql> SET FOREIGN_KEY_CHECKS = 0;
Query OK, 0 rows affected (0.05 sec)

mysql> SET UNIQUE_CHECKS = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET AUTOCOMMIT = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO bulk_test_1 SELECT * FROM bulk_test;
Query OK, 16777216 rows affected (1 min 49.48 sec)
Records: 16777216  Duplicates: 0  Warnings: 0

LOAD DATA LOCAL INFILE

投入する CSV 例

1,dummy text34,1,2020-11-03 07:31:48,2020-11-03 07:31:48
2,dummy text58,2,2020-11-03 07:33:16,2020-11-03 07:33:16
[vagrant@localhost ~]$ time mysql -u root -p test -e "LOAD DATA LOCAL INFILE '/tmp/bulk_test3.csv' INTO TABLE bulk_test_4 FIELDS TERMINATED BY ','"
Enter password:

real	1m40.355s
user	0m0.067s
sys	0m2.468s

LOAD DATA INFILE

投入する CSV 例

1,dummy text34,1,2020-11-03 07:31:48,2020-11-03 07:31:48
2,dummy text58,2,2020-11-03 07:33:16,2020-11-03 07:33:16
mysql> LOAD DATA INFILE '/tmp/bulk_test3.csv'
    ->   INTO TABLE bulk_test_3
    ->   FIELDS TERMINATED BY ','
    -> ;
Query OK, 16777216 rows affected (1 min 34.17 sec)
Records: 16777216  Deleted: 0  Skipped: 0  Warnings: 0

mysqldump で dump を流し込む

[vagrant@localhost ~]$ time mysqldump -uroot -p test bulk_test > /tmp/20201108.sql
Enter password:

real	0m47.587s
user	0m13.386s
sys	0m2.482s

投入前に別名に rename しておきます。

mysql> rename table bulk_test to bulk_test_org
    -> ;
Query OK, 0 rows affected (0.02 sec)
[vagrant@localhost ~]$ time mysql -u root -p test < /tmp/20201108.sql
Enter password:

real	2m39.268s
user	0m14.920s
sys	0m2.572s

まとめ

自分の PC で検証した限りだと、INSERT INTO ... SELECT と LOAD DATA INFILE は、そんなに速度差はないみたいでした。

CSV 出力の時間が追加されるので、合計では INSERT INTO ... SELECT の方が短時間で出来るかもしれません。

参考