mysql批量导入数据遇到的问题

来源:互联网 时间:2017-07-29

最近项目中有一个需求,需要把大概150W条数据导入mysql数据库中,一共六张表,数据最多的表大概有100W条数据。
因为是一次性导入,不会经常使用,因此计划写成一个小程序,使用原生JDBC,打成jar包,放到服务器上运行。

第一次写完,用maven打jar包,打出的jar包不包含mysql驱动等第三方jar,并且java -jar 时没有程序入口,需要在pom.xml文件中加入如下plugin:

<plugins> <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-assembly-plugin</artifactId> <version>2.5.5</version> <configuration> <archive> <manifest><!--指定jar包主类--> <mainClass>com.jd.TransferData</mainClass> </manifest> </archive> <descriptorRefs><!--打jar时打入依赖--> <descriptorRef>jar-with-dependencies</descriptorRef> </descriptorRefs> </configuration> <executions> <execution> <id>make-assembly</id> <phase>package</phase> <goals> <goal>single</goal> </goals> </execution> </executions> </plugin></plugins>

程序很短,如下:

public class TransferData{ public static void main(String[] args) throws Exception { Properties p = readProperties(); Connection connection = getConnection(p); insertTable(connection,"table1"); /*insertTable(connection,"table2"); insertTable(connection,"table3"); insertTable(connection,"table4"); insertTable(connection,"table5"); insertTable(connection,"table6");*/ connection.close(); } public static void insertTable(Connection connection, String tableName) throws Exception{ InputStream inputStream = TransferDataCopy.class.getResourceAsStream("/"+tableName+".txt"); BufferedReader reader = new BufferedReader(new InputStreamReader(inputStream)); String line; while ((line = reader.readLine()) != null){ StringBuilder sb = new StringBuilder(100).append("insert into ").append(tableName).append(" values (").append(line).append(")"); PreparedStatement preparedStatement = connection.prepareStatement(sb.toString()); preparedStatement.executeUpdate(); connection.commit(); } reader.close(); } public static Properties readProperties() throws Exception { Properties properties = new Properties(); properties.load(TransferDataCopy.class.getResourceAsStream("/jdbc.properties")); //properties.list(System.out); return properties; } public static Connection getConnection(Properties p) throws Exception{ Class.forName(p.getProperty("jdbc.driverClass")); Connection connection = DriverManager.getConnection(p.getProperty("jdbc.jdbcUrl"),p.getProperty("jdbc.username") ,p.getProperty("jdbc.password")); connection.setAutoCommit(false); return connection; }}

首先从properties文件中读取jdbc配置,根据配置创建一个connection连接,然后将每个表的数据文件读到内存,然后一条一条插入。最大的文件大概140M,我预估把它一次性全部读入内存也没有什么压力。一百多万条数据也不算很多,没有选择批量插入,我觉得再慢几个小时也就完事了吧。

不过放到服务器上,使用预发环境数据库,运行了2分钟左右,程序莫名其妙被kill掉了,试了好几次都是这样,问了下运维,运维说刚才docker容器内存被打满了,程序被操作系统杀了:

很意外,不知道怎么内存就被干满了,到底发生了什么导致jvm这么贪婪的申请内存。后来听运维的建议,限制一下jvm内存:

java -Xms400m -Xmx400m -jar transferdata-1.0-SNAPSHOT-jar-with-dependencies.jar 

果然不会被kill了,执行了一个多小时后发现数据只导入了二十多万,之后越来越慢,一秒只能插入10来条。数据库表并没有建索引。

这么慢不知道要导到猴年马月,网上查阅资料,百万级的插入对于mysql应该是小case,我程序肯定是有问题。于是把程序在我本地运行了一下。

用jconsole连接了一下程序进程,观察了一下堆内存使用情况:

发现随着时间的推移,堆大小在不断的增长,不过在我本地并没有被操作系统杀掉(可能是win和linux操作系统不同的原因,也有可能是docker的原因)。很好奇堆里面到底堆积了些什么玩意儿。

用jmap dump出内存快照:

jmap -dump:format=b,file= 文件名 [pid]

用jvisualvm分析一下dump文件:

发现很多关于jdbc的对象,JDBC42PreparedStatement、JDBC42ResultSet等,实例数加起来有几百万个…

应该是我使用jdbc出问题了,请教了下部门的架构师,终于知道问题在哪了:

1.我创建了太多PreparedStatement,每次while循环创建一个,而且一个都不一样,之后也没有close过,connection从始至终都是一个,因此随着程序执行时间越来越长,while循环执行次数越来越多,在内存中缓存的PreparedStatement越来越多,因此内存占用越来越大。不仅数据库客户端(也就是此程序)会缓存大量PreparedStatement,数据库server也有自己的缓存机制,对于如此多的PerparedStatement,server压力也会很大。另外,根据jconsole的堆内存变化情况,heap抖动次数非常频繁,且幅度较大,说明jvm频繁发生gc,在一定程度上gc线程抢占了我的程序的时间片,这也是程序插入越来越慢的一个原因。
2.我没有利用上PreparedStatement的特性,PreparedStatement是一个预编译语句,它把?占位符放到参数的位置,每次查询时只需把参数set到指定位置即可,这样一个PreparedStatement可以使用多次;

An object that represents a precompiled SQL statement.A SQL statement is precompiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times.

3.使用jdbc批量插入接口,理论上比每次插入一条效率高很多。

public static void insertTable(Properties p, String tableName) throws Exception{ Connection connection = getConnection(p); InputStream inputStream = TransferData.class.getResourceAsStream("/"+tableName+".txt"); BufferedReader reader = new BufferedReader(new InputStreamReader(inputStream)); PreparedStatement preparedStatement = connection.prepareStatement(table_quote.get(tableName)); String line; int linecount = 1; String[] params = null; try { while ((line = reader.readLine()) != null){ params = line.replaceAll("'","").split(","); for(int i=0;i<params.length;i++) { preparedStatement.setObject(i+1,params[i].trim()); } preparedStatement.addBatch(); if (linecount % 1000 == 0) { preparedStatement.executeBatch(); connection.commit(); } linecount++; } preparedStatement.executeBatch(); connection.commit(); preparedStatement.close(); connection.close(); inputStream.close(); } catch (Exception e) { e.printStackTrace(); throw e; }}

改造完成的程序,运行在服务器,大概两分钟左右就把数据全部导入完毕了。

注:

把所有的java进程杀掉(只能杀当前用户启动的进程)ps -ef | grep java | grep -v grep | awk '{print $2}' | xargs kill运行java进程后即使ctrl+c退出,也不会停掉程序的运行 nohup java -Xms400m -Xmx400m -jar transferdata-1.0-SNAPSHOT-jar-with-dependencies.jar & 

相关阅读:
Top