如何提升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 = 1i <= columns.lengthi++)

{

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。

mysql数据库单表插入速度每秒最高100000 rows没问题，在标准配置下。mysql的读写速度跟硬盘的速度，网卡的速度，写入行的数据量，数据在硬盘中的存放位置等等因素都有关系的。要统计这个最好的是进行测试，然后得出一个平均值。没有人能够光通过配置得出这些数据的。

这个是需要做一些设置的。主要设置 rewriteBatchedStatements参数。原理如下：

MySQL Jdbc驱动在默认情况下会无视executeBatch()语句，把我们期望批量执行的一组sql语句拆散，一条一条地发给MySQL数据库，直接造成较低的性能。

只有把rewriteBatchedStatements参数置为true, 驱动才会帮你批量执行SQL (jdbc:mysql://ip:port/db?rewriteBatchedStatements=true)。不过，驱动具体是怎么样批量执行的？　你是不是需要看一下内幕，才敢放心地使用这个选项？ 下文会给出答案。

另外，有人说rewriteBatchedStatements只对INSERT有效，有人说它对UPDATE/DELETE也有效。为此我做了一些实验（详见下文），结论是： 这个选项对INSERT/UPDATE/DELETE都有效，只不过对INSERT它为会预先重排一下SQL语句。

注：本文使用的mysql驱动版本是5.1.12

实验记录：未打开rewriteBatchedStatements时

未打开rewriteBatchedStatements时，根据wireshark嗅探出的mysql报文可以看出，

batchDelete(10条记录) => 发送10次delete 请求

batchUpdate(10条记录) => 发送10次update 请求

batchInsert(10条记录) => 发送10次insert 请求

也就是说，batchXXX()的确不起作用

实验记录：打开了rewriteBatchedStatements后

打开rewriteBatchedStatements后，根据wireshark嗅探出的mysql报文可以看出

batchDelete(10条记录) => 发送一次请求，内容为”delete from t where id = 1delete from t where id = 2delete from t where id = 3….”

batchUpdate(10条记录) => 发送一次请求，内容为”update t set … where id = 1update t set … where id = 2update t set … where id = 3 …”

batchInsert(10条记录) => 发送一次请求，内容为”insert into t (…) values (…) , (…), (…)”

对delete和update，驱动所做的事就是把多条sql语句累积起来再一次性发出去；而对于insert，驱动则会把多条sql语句重写成一条风格很酷的sql语句，然后再发出去。 官方文档说，这种insert写法可以提高性能（”This is considerably faster (many times faster in some cases) than using separate single-row INSERT statements”）

一个注意事项

需要注意的是，即使rewriteBatchedStatements=true, batchDelete()和batchUpdate()也不一定会走批量： 当batchSize <= 3时，驱动会宁愿一条一条地执行SQL。所以，如果你想验证rewriteBatchedStatements在你的系统里是否已经生效，记得要使用较大的batch.

更多细节看这个帖子：

blog.yemou.net/article/query/info/tytfjhfascvhzxcyt397

---