最近，有一位同事，咨询我mysql的一点问题， 具体来说， 是如何很快的将一个mysql导出的文件快速的导入到另外一个mysql数据库。我学习了很多mysql的知识， 使用的时间却并不是很多， 对于mysql导入这类问题，我更是头一次碰到。询问我的原因，我大致可以猜到，以前互相之间有过很多交流，可能觉得我学习还是很认真可靠的。

首先，我了解了一下大致的情况， （1）这个文件是从mysql导出的，文件是运维给的，具体如何生成的，他不知道，可以询问运维 （2）按照当前他写的代码来看， 每秒可以插入几百条数据 （3）按照他们的要求， 需要每秒插入三四千条数据。（4）他们需要插入的数据量达到几亿条数据， 当前有一个上千万的实验数据

根据我学习的知识，在《高性能mysql》上面有过描述， load data的速度，比插入数据库快得多， 所以，我先通过他们从运维那里获取了生成数据的代码，通过向他们寻求了大约几千条数据。运维生成数据的方式是：select ... into outfile, 根据mysql官方文档上的说明，正好可以通过load data加载回数据库，load data使用的fields和lines等参数， 可以通过运维给出的语句得到，测试一次，成功。在我的推荐下，我们先使用innodb引擎根据测试的结果显示，大约每秒1千多。这个，我是通过date; mysql -e "load data ..."; date; 来大致得到。考虑到innodb中存储带索引的数据，插入速度会随着数据量的增加而变慢，那么我们使用几千条数据测试的结果，应该超过1千多。我们又在目标电脑上进行测试，得到的结果基本一致，速度会略快，具体原因当时没有细查。向远程mysql导入数据，需要进行一定的配置，具体配置的说明，在这里省略。因为目标电脑有额外的用处，所以，我们决定现在本机电脑上进行测试。

 

进行了初步的准备工作之后，我决定获取更多的数据，这次我打算下拉10万条数据。可是我的那位同事，通过对csv文件进行剪切，得到的结果，不能在我的mysql上进行很好的加载（load data)。于是，我们打算从有上千万条数据的测试mysql服务器上进行拉取。可是SELECT ... INTO OUTFILE只会将文件下拉到本地，我们没有本地的权限，除非找运维。为此，我们换了一种思路，使用select语句，模拟select ... into outfile语句，具体做法如下：

(1) 将数据select到本地，这里需要注意一件事，order by后面应该跟一个索引，这样可以避免排序操作。我进行过测试，如果不显示制定order by index, 下拉所用的时间会明显增加。

mysql -h 192.168.9.10 -u root -p -e "SELECT * FROM edu.olog order by idd LIMIT 100000;" > /home/sun/data.txt

(2) 使用sed命令移除输出中的第一行

sed -i '1d' /home/sun/data.txt

(3) 对远程数据库表格调用show create table指令得到表格的创建指令。在本机mysql的一个数据库中调用上述的创建表格的指令，这里可能需要做修改引擎一类的操作, 例如使用MYISAM作为表格的引擎，这样对于我们的加载任务来说，load data的速度明显更快。然后，对生成的文件调用load data记载到本机数据库， 不过没有FIELDS, lines一类的后缀。

这样，我们就可以对本机表格调用select ... into outfile生成对应的csv文件。

我们对十万条数据进行了测试，测试的结果显示，加载速度大约每秒1千多，速度比之前几千条时略慢。

 

我们已经知道了目标，也知道了当前的状态，那么下一步就是优化。首先，我查找了一些资料，这里以mysql8.0为例。首先查看了load data的文档，具体网页为：https://dev.mysql.com/doc/refman/8.0/en/load-data.html。考虑到load data类似于insert，我又在优化的相关模块中进行查找，大的目录为：https://dev.mysql.com/doc/refman/8.0/en/optimization.html（优化），紧接着查找的网页是：https://dev.mysql.com/doc/refman/8.0/en/insert-optimization.html（插入优化）， 根据这个网页的提示，最后查看的网页是：https://dev.mysql.com/doc/refman/8.0/en/optimizing-innodb-bulk-data-loading.html（优化innodb表格的加载大量数据）。根据这里面的提示，我对我自己构建的测试数据进行了测试，使用的优化方法如下：

（1）临时修改自动提交的方式：

SET autocommit=0;
... SQL import statements ...
COMMIT;

 (2)临时取消unique索引的检查：

SET unique_checks=0;
... SQL import statements ...
SET unique_checks=1;

这里简单介绍一下，我的测试使用表格和数据，我的create table如下：

CREATE TABLE `tick` (
  `id` int(11) NOT NULL,
  `val` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

行数为1775232，插入时间大约在十几秒。在测试过程中，因为设计频繁的清空表格，清空表格需要花好几秒钟的时间，建议做类似操作的可以考虑，先drop table，然后再create table，这样速度会明显更快。按照在我的电脑上的测试结果，两者基本没有什么区别，大约都在18秒左右。使用的mysql语句为：

date; mysql -u root -pmysql -e "load data infile './tick.txt' into table test.tick;";date;

mysql -u root -pmysql -e "set autocommit=0;set unique_checks=0;load data infile '/home/sun/tick.txt' into table test.tick;set unique_checks=1;set autocommit=1;";date;

为了确保我的设置生效，我检查了status输出：

mysql -u root -pmysql -e "set autocommit=0;set unique_checks=0; show variables like '%commit';set unique_checks=1;set autocommit=1;";date;

date; mysql -u root -pm