一.环境准备
一直想测试下mysqldump与load data的性能比较，今天抽出部分时间，在测试环境进行测试。
如下所示，我有一个测试表fact_sale，有7亿多行数据，数值类型、字符类型、时间类型的字段均有。
下面我们测试将这个表的数据分别通过mysqldump与load data进行导出，然后在目标环境进行导入。
mysql> select count(*) from fact_sale;
+-----------+
| count(*) |
+-----------+
| 767830000 |
+-----------+
1 row in set (1 min 1.58 sec)
mysql> select * from fact_sale limit 10;
+----+---------------------+-----------+-----------+
| id | sale_date | prod_name | sale_nums |
+----+---------------------+-----------+-----------+
| 1 | 2011-08-16 00:00:00 | PROD4 | 28 |
| 2 | 2011-11-06 00:00:00 | PROD6 | 19 |
| 3 | 2011-04-25 00:00:00 | PROD8 | 29 |
| 4 | 2011-09-12 00:00:00 | PROD2 | 88 |
| 5 | 2011-05-15 00:00:00 | PROD5 | 76 |
| 6 | 2011-02-23 00:00:00 | PROD6 | 64 |
| 7 | 2012-09-26 00:00:00 | PROD2 | 38 |
| 8 | 2012-02-14 00:00:00 | PROD6 | 45 |
| 9 | 2010-04-22 00:00:00 | PROD8 | 57 |
| 10 | 2010-10-31 00:00:00 | PROD5 | 65 |
+----+---------------------+-----------+-----------+
10 rows in set (0.01 sec)
mysql> desc fact_sale;
+-----------+--------------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+-------------------+-----------------------------------------------+
| id | bigint | NO | PRI | NULL | auto_increment |
| sale_date | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
| prod_name | varchar(200) | NO | | NULL | |
| sale_nums | int | YES | | NULL | |
+-----------+--------------+------+-----+-------------------+-----------------------------------------------+
4 rows in set (0.01 sec)
mysql>
二.数据迁移开始
2.1 mysqldump导出
命令:
mysqldump -uroot -p -hlocalhost test fact_sale> fact_sale_20210526.sql
scp ./fact_sale_20210526.sql root@10.31.1.124:/home/backup/
测试记录:
[root@10-31-1-122 backup]# mysqldump -uroot -p -hlocalhost test fact_sale> fact_sale_20210526.sql
Enter password:
[root@10-31-1-122 backup]#
scp ./fact_sale_20210526.sql root@10.31.1.124:/home/backup/
[root@10-31-1-122 mysql-files]# cd /home/backup/
[root@10-31-1-122 backup]# ls -lrth
总用量 33G
-rw-r--r-- 1 root root 33G 5月 26 11:34 fact_sale_20210526.sql
2.2 select ... into outfile导出
命令:
select * from fact_sale into outfile "/var/lib/mysql-files/fact_sale_20210526.txt";
cd /var/lib/mysql-files/
ls -lrth
scp ./fact_sale_20210526.txt root@10.31.1.124:/home/backup/
测试记录:
mysql> select * from fact_sale into outfile "/var/lib/mysql-files/fact_sale_20210526.txt";
Query OK, 767830000 rows affected (12 min 31.91 sec)
[root@10-31-1-122 backup]# cd /var/lib/mysql-files/
[root@10-31-1-122 mysql-files]# ls -lrth
总用量 28G
-rw-rw-rw- 1 mysql mysql 28G 5月 26 11:52 fact_sale_20210526.txt
2.3 mysql 导入
考虑mysqldump没有输出时间，写了个简单的shell脚本，输出当前的时间。
代码:
[root@hp2 backup]# more 1.sh
date
echo "begin load data"
mysql -uroot -pabc123 test < fact_sale_20210526.sql
echo "end load data"
date
测试记录:
[root@hp2 backup]# sh 1.sh
2021年 05月 26日 星期三 13:58:36 CST
begin load data
mysql: [Warning] Using a password on the command line interface can be insecure.
end load data
2021年 05月 26日 星期三 16:03:12 CST
2.4 load data导入
load data之前需要先删除然后重新创建表
命令:
CREATE TABLE `fact_sale` (
`id` bigint NOT NULL AUTO_INCREMENT,
`sale_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`prod_name` varchar(200) NOT NULL,
`sale_nums` int DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
LOAD DATA INFILE '/home/backup/fact_sale_20210526.txt' INTO TABLE fact_sale;
测试记录:
mysql> CREATE TABLE `fact_sale` (
-> `id` bigint NOT NULL AUTO_INCREMENT,
-> `sale_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> `prod_name` varchar(200) NOT NULL,
-> `sale_nums` int DEFAULT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)
mysql> LOAD DATA INFILE '/home/backup/fact_sale_20210526.txt' INTO TABLE fact_sale;
Query OK, 767830000 rows affected (1 hour 34 min 50.20 sec)
Records: 767830000 Deleted: 0 Skipped: 0 Warnings: 0
2.5 总结
首先对比导出文件的大小
outfile的方式比mysqldump的导出的明显会小一些
其实这个很容易理解，outfile只包含数据，而mysqldump导出的是拼接的insert语句，所以容量会大一些。
[root@hp2 backup]# ls -lrth
总用量 60G
-rw-r--r--. 1 root root 33G 5月 26 11:50 fact_sale_20210526.sql
-rw-r--r--. 1 root root 28G 5月 26 13:52 fact_sale_20210526.txt
-rwxr--r--. 1 root root 106 5月 26 13:58 1.sh
然后对比导入的性能
mysql导入耗时2小时5分左右，而load data耗时1小时34分钟，load data的性能明显的优于mysql导入。
[root@hp2 backup]# sh 1.sh
2021年 05月 26日 星期三 13:58:36 CST
begin load data
mysql: [Warning] Using a password on the command line interface can be insecure.
end load data
2021年 05月 26日 星期三 16:03:12 CST
mysql> LOAD DATA INFILE '/home/backup/fact_sale_20210526.txt' INTO TABLE fact_sale;
Query OK, 767830000 rows affected (1 hour 34 min 50.20 sec)
Records: 767830000 Deleted: 0 Skipped: 0 Warnings: 0
总结
mysqldump对于批量迁移数据便利性要高于outfile的方式。
如果是迁移大的日志表，可以考虑outfile的方式。
