mysql 数据表格切分_MySQL快速导入千万级别的大数据量sql文件

最近除了需要导入Oracle数据之外，MySQL也需要导入。mysql导入的数据量也在24个G左右。最多的一张表1300万数据。

最开始的时候，就抱着靠navicat工具进行导入，其他小一点的表，一百多万，两百多万的数据量的表导入的时间还是能够忍受的。但是直到有一个表是1300多万。晚上下班了我就开始导，挂了一晚上，第二天来上班的时候发现，12个小时，只导入了960万数据，照着这个速度，到中午也导不完，然而上班时间数据库压力比较大，不太允许上班时间进行操作，只能取消了。

这个时候必须想一个改进的办法，通过网上搜索和询问，基本确定了两个改进方案。

第一个：因为sql文件都是有建表语句的，先把里面的索引先干掉，等数据导入之后再建索引，如果先建好表，以后每一个insert语句就要维护这个索引，肯定影响效率。

第二个：sql文件里面的比如1300万数据这个表，居然是1300条insert语句，这样会频繁插入1300万次，肯定是不行的，把这些语句合并为一条语句。就是insert into XXX values(),()；这种。然后再通过navicat导入。

第一个比较简单，去掉建表语句的索引语句就可以了。主要是第二个有点麻烦了。当你一个文件就是一条语句，而达到几百兆。MySQL默认是不支持这么大的sql语句。

所以首先需要修改MySQL的配置，通过show variables like '%max_allowed_packet%'；查询当前MySQL支持的最大语句大小，我这边的4m。直接登录mysql,设置

set global max_allowed_packet = 5 * 1024 * 1024 * 1024

这个只是暂时设置，如果想永久改，可以搜一下改my.ini文件。

好的，设置成功了，1300万的文件1.6个G也已经改成批量的形式。

接下来通过navicat选中相应的数据库运行sql文件。

好的。直接报错，Integer overflow。上网搜了，没找到答案，后面考虑到是不是刚才的配置改了没生效，再次命令行进入MySQL。输入show variables like '%max_allowed_packet%'，显示的是1073741824。这个换算之后就是1G。但是前面我设置的是5G，为什么没有生效呢？

网上说的是，这个参数最大值就只能设置1G。就算设置大于1G，也只能是1G。

ok。现在面临的问题是1.6G的文件怎么导入。

想到的就是把这个文件上传到Linux，通过split命令切分这个文件。

split可以通过大小切分，也可以通过行数切分。

但是两种都试过的我，发现还是行数切分比较好。因为按大小来切，很可能一个语句前半截在第一个文件，后半截在前一个文件，两个都要修改，很麻烦。通过行数切分，先wc -l xxx.sql统计一下文件的总行数，然后确定怎么切。

行数进行切分：split -l 5500000 -d --verbose xx.sql split-size

大小进行切分：split -b 500M -d --verbose xx.sql split-size

切分完成之后，修改两个文件为批量插入的格式。

完成之后，下载文件到本地，通过navicat导入，成功了。亲测了一下，导入五百万数据在五分钟左右，比之前的效率大大提升。但是这个1300万的表建立主键索引也是挺耗时的，用了20分钟。不过总的来说相比之前这点耗时微不足道了。

后面导入了一两个G的文件之后，MySQL居然挂了，重启之后，重新设置之前的参数，运行正常。可能还是一条语句太大造成的，所以每个文件尽可能切得合适而小一点，我的最多的是快900M，几乎达到1G了。

最后再补充一个Linux替换文本的命令，我之前在本地通过sublime text打开几百兆的文件，然后替换，比如先替换所有的insert into xxx values为空，非常耗时。后面试了一下linux sed命令，很好用，效率也非常高，sublime text替换所有可能要几分钟，但是sed几秒钟就可以搞定。

替换命令：sed -e 's/insert into xxx values()/g' -i split-size00