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

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

SRE实战 互联网时代守护先锋,助力企业售后服务体系运筹帷幄!一键直达领取阿里云限量特价优惠。

根据我学习的知识,在《高性能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 -pmysql -e "set autocommit=0;set unique_checks=0; show variables like 'unique_checks';set unique_checks=1;set autocommit=1;";date;

输出结果如下:

autocommit为OFF, unique_checks为OFF。

其实我还设置过很多配置,例如插入缓冲区大小一类的,其中比较有效的是innodb_flush_log_at_trx_commit设置为2,对于我们这个问题,这个设置是可以考虑的。

我一直有一个疑问,就是说,load data是否为多线程运行的,按照我以往使用mysql的经验,和《高性能mysql》对load data的论断(远比insert快),如果多线程加载,不至于速度如此之慢。后来,我尝试使用mysqlimport,经过查看mysqlimport上面指定的--use-threads为多线程读取文件,以及在使用mysqlimport加载时,CPU利用率最多只有百分之两百多一点,这个让我觉得很有可能是单线程执行的。通过在运行load data的同时,调用show full processlist,可以清楚的看到,load data是单线程运行的。结果如下:

*************************** 1. row ***************************
     Id: 4
   User: event_scheduler
   Host: localhost
     db: NULL
Command: Daemon
   Time: 2835
  State: Waiting on empty queue
   Info: NULL
*************************** 2. row ***************************
     Id: 17
   User: root
   Host: localhost
     db: test
Command: Query
   Time: 0
  State: starting
   Info: show full processlist
*************************** 3. row ***************************
     Id: 39
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 13
  State: executing
   Info: load data infile '/home/sun/tick.txt' into table test.tick
3 rows in set (0.00 sec)

由上可以看出,load data是单线程执行的。

我知道,使用多线程执行,会是一个很好的办法,只是,我还是有想法提高单线程的效率,我想到了set profiling. 通过在一个session中设置set profiling = 1; 可以查看一个语句详细的时间消耗。我使用了如下的mysql语句:

set profiling=1;
load data infile '/home/sun/tick.txt' into table test.tick;
show profiles;
show profile for query 1;

可以得到如下的结果:

+----------------------+-----------+
| Status               | Duration  |
+----------------------+-----------+
| starting             |  0.000102 |
| checking permissions |  0.000016 |
| Opening tables       |  0.001377 |
| System lock          |  0.000024 |
| executing            | 17.476604 |
| query end            |  0.233398 |
| closing tables       |  0.000045 |
| freeing items        |  0.000039 |
| cleaning up          |  0.000047 |
+----------------------+-----------+
可见执行时间占据了最长的时间,而执行时间应该主要是CPU密集型的(这个我没有足够的把握),从CPU的使用来看,这个论断应该是比较合理的。对于一些比较复杂的问题,我不建议这样分析,当然,这里也可以考虑不这样分析,可以借鉴《高性能mysql》中的做法,参考如下的脚本:

#!/bin/sh

INTERVAL=5
PREFIX=$INTERVAL-sec-status
RUNFILE=/home/sun/benchmarks/running
mysql -e 'SHOW GLOBAL VARIABLES' >> mysql-variables
while test -e $RUNFILE; do
    file=$(date +%F_%I)
    sleep=$(date +%s.%N | awk "{print $INTERVAL - (\$1 % $INTERVAL)}")
    sleep $sleep
    ts="$(date +"TS %s.%N %F %T")"
    loadavg="$(uptime)"
    echo "$ts $loadavg" >> $PREFIX-${file}-status
    mysql -e 'SHOW GLOBAL STATUS' >> $PREFIX-${file}-status &
    echo "$ts $loadavg" >> $PREFIX-${file}-innodbstatus
    mysql -e 'SHOW ENGINE INNODB STATUS\G' >> $PREFIX-${file}-innodbstatus &
    echo "$ts $loadavg" >> $PREFIX-${file}-processlist
    mysql -e 'SHOW FULL PROCESSLIST\G' >> $PREFIX-${file}-processlist &
    echo $ts
done
echo Exiting because $RUNFILE does not exist

调整中间的时间,打印出详细的信息,进行分析。我这里就没有做这件事。

下面考虑了多线程处理,查看select ... into outfile的文件,即tick.txt可知,文件中的每行对应于表中的一条数据,想把文件切分,可以使用wc -l得出行数,然后使用head与tail得到两个文件,然后对这两个文件再次使用head和tail,得到测试用的四个文件。进行这个测试,我写了很简单的两个shell脚本:

#!/bin/bash
date;mysql -u root -pmysql -e "load data infile '/home/sun/$1' into table test.tick;";date;
exit 0;

#!/bin/bash
./mysql-load.sh data1 &
./mysql-load.sh data2 &
./mysql-load.sh data3 &
./mysql-load.sh data4 &
exit 0;

通过测试,可以发现加载这些文件所用的时间由18秒降低到10秒。我还测试了加载两个文件,所用时间由18秒降低到12.5秒,可见多线程加载可以明显提高加载速度。因为我的电脑是4线程的,所以我决定使用真正的测试数据进行测试。

按照我们之间出现的一个小插曲,发现使用MYISAM的插入速度会明显快于InnoDB,使用MYISAM插入速度可以达到每秒八千左右。我的同事认为这个可以满足他的需求,所以就采用了MYISAM。那时,我的同事发现他的插入速度明显我之前测试的要快,希望我能找出原因,基于没有mysql没有进行什么特殊的配置,他的电脑配置和我的电脑配置基本一致,我觉得应该是表格的问题,可能性最大的就是引擎,后来发现他无意间使用了MYISAM作为存储引擎。关于两个电脑配置的差别,可以通过show variables将配置变量导入到文件中进行对比得到,这个属于后话。既然我的同事觉得那样可行,我就没有进一步测试多线程的效果,只是这个经历可以记录下来,留以后借鉴。

扫码关注我们
微信号:SRE实战
拒绝背锅 运筹帷幄