如何提升MySQL批量插入的效率

需要将大量数据(大概5W条)插入MySQL数据库，用普通的SQL Statement执行,时间大概是几分钟。于是想到用PreparedStatement，但是改了之后发现效率并没有很大的提升。不成，想到了load data local infile...命令，以前一直认为这条命令仅限MySQL终端上使用而不是标准的SQL语句，今天看了几篇文章之后有了很大的收获。

1. 使用PreparedStatement batch operation

以前使用PreparedStatement性能没有很大提升的原因在于：

• 没有使用批处理方法
• 在语句执行之前应关闭事务自动提交，语句执行完之后再提交

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43

public void batchLoad(Connection connection) {      try {          connection.setAutoCommit( false );                    BufferedReader reader = new BufferedReader( new FileReader( "tfacts_result" ));          String sqlString = "insert into test(node1, node2, weight) values(?, ?, ?)" ;                    PreparedStatement pstmt = connection.prepareStatement(sqlString);          String line = null ;                    while ( true )          {              line = reader.readLine();              if (line == null )              {                  break ;              }                            String[] columns = line.split( "\t" );                            for ( int i = 1 ; i <= columns.length; i++)              {                  pstmt.setString(i, columns[i- 1 ]);                }              pstmt.addBatch();          }                    pstmt.executeBatch();                    connection.commit();                     pstmt.close();                    reader.close();                } catch (FileNotFoundException e) {          e.printStackTrace();      } catch (SQLException e){          e.printStackTrace();      } catch (IOException e){          e.printStackTrace();      }

2.使用load data local infile into tabel XXX（注意在文件中用\t将每列数据隔开）

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

public void loadData(Connection connection) {      long starTime = System.currentTimeMillis();        String sqlString = "load data local infile ? into table test" ;      PreparedStatement pstmt;      try {          pstmt = connection.prepareStatement(sqlString);                    pstmt.setString( 1 , "tfacts_result" );                    pstmt.executeUpdate();                    pstmt.close();      } catch (SQLException e) {          e.printStackTrace();      }            long endTime = System.currentTimeMillis();            System.out.println( "program runs " + (endTime - starTime) + "ms" );   }

测试了5W条数据，PreparedStatement耗时10s，而load data infile耗时3s。

REF:

http://www.iteye.com/topic/320942

http://www.blogjava.net/jicheng687/archive/2010/12/23/341418.html

PS：使用load data local infile 命令的时候貌似最后一列后面还需要加“\t”，不然会会把换行符导入到表中最后一列的数据中,影响到其他SQL语句对这一列数据的引用。