mysql 导入导出大数据sql文件

最新推荐文章于 2024-07-01 11:42:01 发布

weixin_34072857

最新推荐文章于 2024-07-01 11:42:01 发布

阅读量351

点赞数

文章标签：大数据数据库 python

原文链接：https://my.oschina.net/OpenSourceBO/blog/412178

版权

2019独角兽企业重金招聘Python工程师标准>>>

##1. 导出Sql文件

在导出时合理使用几个参数，可以大大加快导入的速度。

-e 使用包括几个VALUES列表的多行INSERT语法; --max_allowed_packet=XXX 客户端/服务器之间通信的缓存区的最大大小; --net_buffer_length=XXX TCP/IP和套接字通信缓冲区大小,创建长度达net_buffer_length的行

注意：max_allowed_packet和net_buffer_length不能比目标数据库的配置数值大，否则可能出错。例子：

mysql>mysqldump -uroot -p exmple  -e --max_allowed_packet=1048576 --net_buffer_length=16384 > exmple.sql

##2. 导入文件

###2.1 使用max_allowed_packet属性

客户端/服务器之间通信的缓存区的最大大小,适量调大可以加快mysql导入数据的速度。

查看当前大小：

show VARIABLES like '%max_allowed_packet%';

修改方式：

可以编辑my.cnf来修改（windows下my.ini）,在[mysqld]段或者mysql的server配置段进行修改。(在[mysqld]下加上max_allowed_packet=16M，保存重启mysql后) 如果找不到my.cnf可以通过mysql --help | grep my.cnf去寻找my.cnf文件。
进入mysql server，在mysql 命令行中运行set global max_allowed_packet = 2*1024*1024*10 然后关闭掉这此mysql server链接，再进入。show VARIABLES like '%max_allowed_packet%';查看下max_allowed_packet是否编辑成功

###2.2 使用innodb_flush_log_at_trx_commit属性

配置有0,1,2三种配置

如果innodb_flush_log_at_trx_commit的值为0, log buffer每秒就会被刷写日志文件到磁盘，提交事务的时候不做任何操作。（执行是由mysql的master thread线程来执行的。主线程中每秒会将重做日志缓冲写入磁盘的重做日志文件(REDO LOG)中。不论事务是否已经提交。）默认的日志文件是ib_logfile0,ib_logfile1
当设为默认值1的时候，每次提交事务的时候，都会将log buffer刷写到日志。
如果设为2,每次提交事务都会写日志，但并不会执行刷的操作。每秒定时会刷到日志文件。要注意的是，并不能保证100%每秒一定都会刷到磁盘，这要取决于进程的调度。每次事务提交的时候将数据写入事务日志，而这里的写入仅是调用了文件系统的写入操作，而文件系统是有缓存的，所以这个写入并不能保证数据已经写入到物理磁盘
默认值1是为了保证完整的ACID。当然，你可以将这个配置项设为1以外的值来换取更高的性能，但是在系统崩溃的时候，你将会丢失1秒的数据。设为0的话，mysqld进程崩溃的时候，就会丢失最后1秒的事务。设为2,只有在操作系统崩溃或者断电的时候才会丢失最后1秒的数据。InnoDB在做恢复的时候会忽略这个值。

故在导入数据时可以暂时设置为0，以获得更大的性能，加快导入。

###2.3 加快插入数据方法

上面的参数设置好后，下面就是真正的插入操作了。

####2.3.1 如果是使用insert则，将自动提交，检查唯一性以及外键检查等等关闭，并且一次插入多条记录。命令如下：

SET autocommit=0;
SET UNIQUE_CHECKS=0;
SET FOREIGN_KEY_CHECKS=0;
insert into tablename values (...),(...),(...)
SET UNIQUE_CHECKS=1;
SET FOREIGN_KEY_CHECKS=1;
COMMIT;

需要注意的是，如果一次插入数据量很大，可能发生请求超时或网络包过大，需要调节相应mysql参数，以及拆分成多次插入提交。

即使insert采用了上述那么多策略但是速度依旧没有load [local] data infile快，本人亲测，以及mysql官网也是推荐在大数据量导入时使用load data infile。

2.3.2 终极武器X因素

当导入千万记录以上的表时，并且表上索引较多。即使使用local data infile 等待时间也是很长的。最终我在一篇博客中找到了更好的答案。写这个博客时，我发现我已经弄丢了那篇博客的地址，我就在这里讲讲大概思路吧，大致过程是使用内存表，因为在内存中构建数据比在磁盘上快了好几个量级。就是在内存上建立同样结构的表，并且把数据导入进去（使用load data infile），之后将构建好的数据复制到真实的表中。这个过程相比直接导入，优势在于构建相应的索引是在内存上执行的，速度很可观。当然表很大的情况，你无法一次就在内存中构建好，需要分批次构建，我使用的时候每次在内存上构建200W条数据，当时使用的是32G内存的服务器，各位看官可以自行尝试着去做。

参考的文章：

转载于:https://my.oschina.net/OpenSourceBO/blog/412178