es java bulk 导入数据一直超时_天翼云MySQL数据库大数据量导入性能调优

今天本是晴天，到了下午天一下子暗起来了，不久便下起了小雨，没办法外出，就在家鼓捣起了MySQL数据库服务。

前一段时间做内训，演示数据库复制服务，通过数据库客户端工具Navicat Premium导入样例数据库时，只导入了数据库结构，数据却一条都没导入，当时就觉得纳闷，好在不影响演示，打算有空了查一下。今天恰好有点时间，从下午四点多，一直鼓捣到晚上，差不多搞明白了原因，同时也尝试了通过mysql数据库服务的参数调优来提高大数据量的导入性能。

01 实验环境

1、本地win10，需要安装并配置好mysql，怎么安装百度查；

2、安装客户端工具Navicat Premium，怎么安装百度查；

3、天翼云上开通mysql数据库服务，我选择最低配1c2g，SAS盘，并配置了2M的EIP；

4、下载mysql示例数据库，大概有近400万条数据，具体下载地址忘了，如果需要可以@我。

02 Navicat 导入数据失败

先从日志里查找原因，提示是语法错误，不断的去尝试，发现在Navicat工具的mysql命令行窗口，仅仅输入source命令居然也报错，百思不得其解，各种百度，可能的原因是source命令是mysql客户端的工具集，Navicat可能不支持，要导入还是要直接在命令行窗口执行。

03 本地批量数据导入

1、以系统管理员的权限打开cmd窗口；

2、运行 net start mysql，显示“服务已经启动成功”，如下图：

3、进入到employees样例数据库文件夹，执行导入数据库命令： 

mysql -h 125.124.74.17 -P 8635 -uroot -pTest@1234 

“<”符号表示导入数据库文件，执行结果如下：

图中导入数据成功，耗时4分42秒，通过Navicat工具能够直观的看到导入的数据库、表、视图及数据等，如下图：

04  参数调优

通过不断地尝试，针对大数据量的批量导入，在天翼云服务端可以调优的参数有2个，分别是innodb_flush_log_at_trx_commit和bulk_insert_buffer_size，我们可以在天翼云mysql服务的参数组界面修改参数，如下图：

innodb_flush_log_at_trx_commit

该参数控制重做日志写入磁盘的过程，系统默认为1，表示事务提交时，会将重做日志缓冲写入磁盘，并且立即刷新.也即是说一旦事务完成，数据立即写盘，以确保数据不会丢失。但对于批量导入大量数据的情况，会导致频繁写盘，影响导入性能。我们这里将该参数设置为0，表示每间隔1秒再写盘。这里我们把参数从1改为0,如图：

bulk_insert_buffer_size

用来缓存批量插入数据的时候临时缓存写入数据，大数据的sql文件能达到几百M乃至上G，默认8M，确实有点小，这里我们改到120M。

修改参数后保险期间需重启数据库实例。

05 性能优化效果

先是修改了innodb_flush_log_at_trx_commit参数，导入数据耗时00:04:14，快了28秒；

然后再修改bulk_insert_buffer_size参数，耗时00:03:42，此次调优提升了32秒；

相比没有调优之前，整体快了60秒，相当于性能提升了了27%！

06 总结

通常用户在云端开了RDS服务后，需要将本地私有云的数据批量导入到云端，对数据量达到千万级的情况，需要综合来考虑，比如数据可以分批导入，为提升导入性能，可以调整RDS的相关参数，以加快数据的导入速度，使客户的业务中断时间降低到最小。当然除了手动导入，也可以通过购买数据库复制服务来实现在线的数据导入，好处是能够避免人工导入带来的疏忽，并能实现业务的平滑迁移。

最后附上导入的数据库的相关表的数据条数：

SELECT count(*) from salaries2844047SELECT count(*) from employees300024SELECT count(*) from titles443308SELECT count(*) from dept_emp331603