Question

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

Answer 1

需要将大量数据(大概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。