MySQL优化案例篇1-mysqldump与load data比较篇

一.环境准备

一直想测试下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的方式。

参考:

1. https://blog.csdn.net/appleyuchi/article/details/106439710