mysql快速导入
数据迁移、性能测试时经常要用到导入导出，MySQL也有多种导入、导出数据的方式，本文将测试比较哪种方式效率更高。
本次测试基于MySQL 5.7.33，虚拟机CPU4核心、8G内存。
参考
MySQL官网：mysqldump使用说明
MySQL官网：mysqlimport使用说明
MySQL官网：优化INSERT语句
准备
本文使用的表均无索引（正式操作时一般先禁用索引，数据导入完毕在启用索引），各操作均在mysql服务器执行。测试表数据如下：
mysql> USE `information_schema`;
Database changed
mysql> SELECT TABLE_NAME,TABLE_ROWS,ENGINE,CONCAT(ROUND(DATA_LENGTH/1024/1024),'MB') FROM TABLES WHERE table_schema='test';
+-------------+------------+--------+-------------------------------------------+
| TABLE_NAME | TABLE_ROWS | ENGINE | CONCAT(ROUND(DATA_LENGTH/1024/1024),'MB') |
+-------------+------------+--------+-------------------------------------------+
| innodb_test | 9729199 | InnoDB | 962MB |
| myisam_test | 10000000 | MyISAM | 797MB |
+-------------+------------+--------+-------------------------------------------+
2 rows in set (0.00 sec)
测试mysqldump
mysqlpump客户端通常用来执行逻辑备份，它能导出一组SQL语句，可以执行这些语句来重新生成原始的数据库对象定义和表数据，以便备份或转移到另一个SQL服务器。
- 导出1000W数据
# myisam引擎耗时35秒
mysqldump -uroot -p123456 test myisam_test > /tmp/myisam_test.sql
# innodb引擎耗时38秒
mysqldump -uroot -p123456 test innodb_test > /tmp/innodb_test.sql
- 导入1000W数据
# myisam引擎耗时1分13秒
mysql -uroot -p123456 test < /tmp/myisam_test.sql
# innodb引擎耗时16分9秒
mysql -uroot -p123456 test < /tmp/innodb_test.sql
测试mysqlimport
mysqlimport客户端是LOAD DATA SQL语句的命令行接口。LOAD DATA语句能以非常高的速度将行从文本文件读取到表中。
从文本文件加载表格时，请使用 LOAD DATA。这通常比使用INSERT语句快20倍 。（这段话是官网的描述）
通过实验结果来看，20倍应该是和单条INSERT语句比较~
- 导出1000W数据
# myisam引擎耗时18秒
SELECT * INTO OUTFILE '/var/lib/mysql-files/myisam_test.txt' FIELDS TERMINATED BY ',' FROM `myisam_test`
# innodb引擎耗时34秒
SELECT * INTO OUTFILE '/var/lib/mysql-files/innodb_test.txt' FIELDS TERMINATED BY ',' FROM `innodb_test`
- 导入1000W数据
# myisam引擎耗时24秒
mysqlimport --user=root --password=123456 --local test /var/lib/mysql-files/myisam_test.txt --fields-terminated-by=','
# innodb引擎耗时12分28秒
mysqlimport --user=root --password=123456 --local test /var/lib/mysql-files/innodb_test.txt --fields-terminated-by=',';
结果
- MyISAM引擎下导入mysqlimport比mysqldump快很多
- InnoDB引擎下导入mysqlimport和mysqldump差别不明显
- 导入数据时尽量在mysql服务器操作，本地使用GUI操作受网络传输影响较大
- 跨服务器迁移数据时，导出的文件先压缩在传输，一般压缩后空间能缩小10倍
| 导入多次的平均耗时 | ||
|---|---|---|
| 存储引擎 | mysqldump | mysqlimport |
| MyISAM | 1分13秒 | 24秒 |
| InnoDB | 16分9秒 | 12分28秒 |
