XOBM

WEB建站知识

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

1. 使用PreparedStatement batch operation

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

  • 没有使用批处理方法
  • 在语句执行之前应关闭事务自动提交,语句执行完之后再提交
?
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
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将每列数据隔开)

?
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
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。

 

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