MySQL Statement: load data

当我们想导入数据时, 一般有两种选择(如果借用IDE就不止了): 一是传统的 mysqldump + source, 二就是本文谈到的 LOAD DATA 了. 本文简单介绍了 LOAD DATA, 并模拟各个量级的数据来比较这两者的实际处理速度. 为减小外部因素的影响, 本文的实操部分都是在一台新拉起的阿里云ECS服务器上进行的, 规格为 2核2G ecs.ic5.large , 且仅运行了 MySQL 5.7 .

什么是 LOAD DATA

The LOAD DATA statement reads rows from a text file into a table at a very high speed. LOAD DATA is the complement of SELECT … INTO OUTFILE.

以上是 官方文档 对 LOAD DATA 语句用途的解释, 清晰明了: LOAD DATA 可快速的从文本文件读取内容以导入到库中. SELECT ... INTO OUTFILE 与 LOAD DATA 互为补充, 前者用于导出数据至外部文件, 后者用于从外部文件导入数据至库中.

而除开定义本身, 本文更关注以下几点:

1. 何时使用 LOAD DATA
2. SELECT ... INTO OUTFILE 的限制
3. 运用 LOAD DATA 时可能遇到的数据值为 NULL 的问题

何时使用 LOAD DATA

在 insert-optimization 一节, 有云:

When loading a table from a text file, use LOAD DATA. This is usually 20 times faster than using INSERT statements. See Section 13.2.6, “LOAD DATA Statement”.

也就是说, 当被需要导入的数据量很大时建议使用 LOAD DATA, 它的性能会比常规的 INSERT 快 20 倍 以上! 下文会通过一定量的随机数据, 来简要揭示 LOAD DATA 的威力.

准备

首先, 新建一张表:

1
2
3
4
5
6
7
8

mysql> create table `book` (
    id BIGINT(20) AUTO_INCREMENT NOT NULL,
    name VARCHAR(255) NOT NULL,
    discount DECIMAL(6,2),
    on_sale_at DATETIME NULL,
    PRIMARY KEY (`id`)
);
Query OK, 0 rows affected (0.08 sec)

允许为 NULL 的列 (比如 on_sale_at) 实际上是为后续 NULL 导出及导入问题做的准备. 由于 book 表仅有可怜巴巴的 4 列, 因此, 我需要随机写入量级比较客观的数据.

以及一张用于导入的表:

1
2

mysql> create table `book_dst` like `book`;
Query OK, 0 rows affected (0.06 sec)

然后, 定义用于插入指定行数随机数据的存储过程:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

delimiter $$$
create procedure insert_rows(in c int)
begin
  declare i int;
  set i=0;
  start transaction;
  while i<c do
    insert into book (`name`, `discount`, `on_sale_at`)
      values (substring(md5(rand()), 1, 20), rand(), case rand() > 0.5 when 1 then now() else null end);
    set i=i+1;
  end while;
  commit;
end$$$
delimiter;

浅尝导出与导入

以 1000 行数据为例. 先写入随机数据:

1

call insert_rows(1000);

导出:

1
2

mysql> select * from book into outfile '/var/lib/mysql-files/book_1000.csv';
Query OK, 1000 rows affected (0.00 sec)

输出的文件内容如下:

1
2
3
4
5
6
7
8
9
10
11

[root@playground ~]# head -n10 /var/lib/mysql-files/book_1000.csv 
1       b076164fef4c03cc2012    0.06    2021-01-24 19:04:51
2       46f64b1b7743e5c8ec31    0.74    \N
3       60aa4d846a41a53a9d61    0.76    \N
4       082c0758e6caf9425276    0.48    2021-01-24 19:04:51
5       2b2077de43fac6d9eb39    0.21    2021-01-24 19:04:51
6       70b6fecb03844ccd0399    0.93    2021-01-24 19:04:51
7       8fbd3c40c4df63af2575    0.40    \N
8       82e714873989f9091a78    0.27    2021-01-24 19:04:51
9       2aeb7d4f54b29caddb1e    0.40    2021-01-24 19:04:51
10      a01a678d0e72e579967c    0.57    2021-01-24 19:04:51

导入:

1
2
3

mysql> load data infile '/var/lib/mysql-files/book_1000.csv' into table `book_dst`;
Query OK, 1000 rows affected (0.01 sec)
Records: 1000  Deleted: 0  Skipped: 0  Warnings: 0

参照上述步骤, 取得在其他数据量级时 SELECT INTO OUTFILE 和 LOAD DATA 的耗时:

与 mysqldump+source 的粗略对比

为了衡量大量数据导入时的性能差异, 笔者同时记录了相同的数据在以下数据量时, 使用 mysqldump + source sql 来导入的耗时:

对比如下:

笔者在自己的 Macbook Pro 上也做了同样的测试, 图标数据如下:

可见, 对于本例中极为简单的 book 表, LOAD DATA 比 SOURCE 快两倍, 与声称的 20 倍有很大差距. 另一方面, 由于 mysqldump 输出的 insert 语句基本上是 2w行 为一个批量, 这也大大加快了使用 SOURCE 时 insert 语句的处理速度. 关于影响 insert 速度的因素, 请参考 insert-optimization .

从导出的文件大小来看:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

[root@playground ~]# ll -h /var/lib/mysql-files/
total 5.0G
-rw-rw-rw- 1 mysql mysql  41K Jan 24 19:07 book_1000.csv
-rw-rw-rw- 1 mysql mysql 433M Jan 24 20:06 book_1000w.csv
-rw-r--r-- 1 root  root  491M Jan 24 20:08 book_1000w.sql
-rw-rw-rw- 1 mysql mysql  43M Jan 24 19:16 book_100w.csv
-rw-r--r-- 1 root  root   49M Jan 24 19:19 book_100w.sql
-rw-rw-rw- 1 mysql mysql 4.2M Jan 24 19:15 book_10w.csv
-rw-rw-rw- 1 mysql mysql 415K Jan 24 19:13 book_1w.csv
-rw-rw-rw- 1 mysql mysql  86M Jan 24 19:23 book_200w.csv
-rw-r--r-- 1 root  root   98M Jan 24 19:24 book_200w.sql
-rw-rw-rw- 1 mysql mysql 130M Jan 24 19:27 book_300w.csv
-rw-r--r-- 1 root  root  147M Jan 24 19:32 book_300w.sql
-rw-rw-rw- 1 mysql mysql 173M Jan 24 19:36 book_400w.csv
-rw-r--r-- 1 root  root  196M Jan 24 19:37 book_400w.sql
-rw-rw-rw- 1 mysql mysql 216M Jan 24 19:40 book_500w.csv
-rw-r--r-- 1 root  root  245M Jan 24 19:43 book_500w.sql
-rw-rw-rw- 1 mysql mysql 260M Jan 24 19:46 book_600w.csv
-rw-r--r-- 1 root  root  294M Jan 24 19:47 book_600w.sql
-rw-rw-rw- 1 mysql mysql 303M Jan 24 19:51 book_700w.csv
-rw-r--r-- 1 root  root  343M Jan 24 19:52 book_700w.sql
-rw-rw-rw- 1 mysql mysql 347M Jan 24 19:56 book_800w.csv
-rw-r--r-- 1 root  root  392M Jan 24 19:57 book_800w.sql
-rw-rw-rw- 1 mysql mysql 390M Jan 24 20:01 book_900w.csv
-rw-r--r-- 1 root  root  441M Jan 24 20:03 book_900w.sql

可以看到, 对于本例而言, csv 和 sql 文件大小是差不多的.

更为复杂的表

在之前的版本上, 将 book 表进行扩张:

• 增加至 20列
• 给 name 列增加唯一索引
• 给 col_1, col_2 增加索引
• 给 col_3, col_4 增加联合索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

CREATE TABLE `book` (
  `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `discount` DECIMAL(6,2) NOT NULL,
  `on_sale_at` DATETIME NULL,
  `col_1` VARCHAR(45) NULL,
  `col_2` VARCHAR(45) NULL,
  `col_3` VARCHAR(45) NULL,
  `col_4` VARCHAR(45) NULL,
  `col_5` VARCHAR(45) NULL,
  `col_6` VARCHAR(45) NULL,
  `col_7` VARCHAR(45) NULL,
  `col_8` VARCHAR(45) NULL,
  `col_9` VARCHAR(45) NULL,
  `col_10` VARCHAR(45) NULL,
  `col_11` VARCHAR(45) NULL,
  `col_12` VARCHAR(45) NULL,
  `col_13` VARCHAR(45) NULL,
  `col_14` VARCHAR(45) NULL,
  `col_15` VARCHAR(45) NULL,
  `col_16` VARCHAR(45) NULL,
  `col_17` VARCHAR(45) NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `name_UNIQUE` (`name` ASC),
  INDEX `idx_col_1` (`col_1` ASC),
  INDEX `idx_col_2` (`col_2` ASC),
  INDEX `idx_col_3_n_4` (`col_3` ASC, `col_4` ASC));

调整存储过程:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

delimiter $$$
create procedure insert_rows(in c int)
begin
  declare i int;
  set i=0;
  start transaction;
  while i<c do
    insert ignore into book (`name`, `discount`, `on_sale_at`,

                      `col_1`, `col_2`, `col_3`, `col_4`,
                      
                      `col_5`, `col_6`, `col_7`, `col_8`, `col_9`, `col_10`,

                      `col_11`, `col_12`, `col_13`, `col_14`, `col_15`, `col_16`, `col_17`)

      values (substring(md5(rand()), 1, 20), rand(), case rand() > 0.5 when 1 then now() else null end,

              substring(md5(rand()), 1, 20),
              substring(md5(rand()), 1, 20),
              substring(md5(rand()), 1, 20),
              substring(md5(rand()), 1, 20),

              rand(), null, rand(), null, rand(), null,
              
              rand(), rand(), rand(), rand(), rand(), rand(), rand());
    set i=i+1;
  end while;
  commit;
end$$$
delimiter;

由于不能保证随机生成的字符串的唯一性, 所以使用了 insert ignore into . 然后, 按以上步骤取得以下数据量级的指标:

• Actual Rows 由于 insert ignore into 的缘故, 实际行数少于对应量级
• Data length 数据部分占用磁盘空间大小
• Index length 索引部分占用磁盘空间大小
• insert_rows_100w 在上一轮数据的基础上, 本轮调用 call insert_rows(1000000) 写入数据的耗时. 虽然 insert ignore into 解决了 unique key 冲突的问题, 但也导致了每轮实际新增行数不足100w

图标对比如下:

导出文件的大小:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

[root@playground ~]# ll -h /var/lib/mysql-files/
total 8.9G
-rw-rw-rw- 1 mysql mysql 306M Jan 24 20:33 book_100w.csv
-rw-r--r-- 1 root  root  343M Jan 24 20:36 book_100w.sql
-rw-rw-rw- 1 mysql mysql  32M Jan 24 20:20 book_10w.csv
-rw-r--r-- 1 root  root   36M Jan 24 20:22 book_10w.sql
-rw-rw-rw- 1 mysql mysql 593M Jan 24 20:57 book_200w.csv
-rw-r--r-- 1 root  root  664M Jan 24 21:04 book_200w.sql
-rw-rw-rw- 1 mysql mysql 860M Jan 24 21:26 book_300w.csv
-rw-r--r-- 1 root  root  964M Jan 24 21:38 book_300w.sql
-rw-rw-rw- 1 mysql mysql 1.1G Jan 24 22:09 book_400w.csv
-rw-r--r-- 1 root  root  1.3G Jan 24 22:41 book_400w.sql
-rw-rw-rw- 1 mysql mysql 1.4G Jan 24 23:25 book_500w.csv
-rw-r--r-- 1 root  root  1.5G Jan 24 23:49 book_500w.sql

SELECT ... INTO OUTFILE 的限制

官方文档 有云:

The SELECT … INTO OUTFILE ‘file_name’ form of SELECT writes the selected rows to a file. The file is created on the server host, so you must have the FILE privilege to use this syntax.

以及:

The SELECT … INTO OUTFILE statement is intended to enable dumping a table to a text file on the server host. To create the resulting file on some other host, SELECT … INTO OUTFILE normally is unsuitable because there is no way to write a path to the file relative to the server host file system, unless the location of the file on the remote host can be accessed using a network-mapped path on the server host file system.

Alternatively, if the MySQL client software is installed on the remote host, you can use a client command such as mysql -e “SELECT …” > file_name to generate the file on that host.

即仅当有权限往 MySQL 服务器本身写入文件时, 才可以使用. 不论何种情况导致写入不了文件, 都得换个配方才能达到相同的效果. 比如:

• 若是想往其他服务器写入文件, 可以通过网络文件系统来解决, 总之就是各种 mount 或者 SAN 或者 NAS
• 若使用了云数据服务, 那么就只能以 mysql -e ... > file_name 的方式来曲线救国

运用 LOAD DATA 时可能遇到的数据值为 NULL 的问题

鉴于本文的内容已经比较长了, 这部分将放到下一篇博客来写.

结论

仅针对本文的模拟测试, 得出以下总结:

• LOAD DATA 与 SOURCE 的性能差异并不大. 对于简单表的场景, LOAD DATA 比 SOURCE 快两倍; 而对于复杂表的场景, 当数据量上去后, LOAD DATA 出现反而不如 SOURCE 的现象.
• 通过 mysqldump 导出的 sql 文件是分批量的 (本文中, 简单 book 导出后为 ~2w 一个批量; 复杂 book 表导出后为 ~2800 一个批量), 在很大程度提升了 insert 的速度, 因为这部分占据了 insert 非常可观的时间比重.
• 就单纯的减少导入时间来说, 可以通过在导入前分别执行 alter table book disable keys; 及 alter table book enable keys; 来达到, 这减少的是索引部分的占用时间, 但这种手段并不局限于哪种导入方式.
• mysqldump 和 SELECT INTO OUTFILE 输出的数据文件大小差异不大, 无须作为选择导入方式的考虑因素.

那么, 官方文档中所称的 20X 性能差异是针对什么情景的? 会不会指的是 1 row 1 insert 的未经任何优化的情况? 这无从印证.