mysql批量插入数据优化

hi.baidu.com/yiewgckawnlpuwe/item/80adbb9d5c263eb282d29571

?

mysql批量插入数据优化

?

100W条数据：比上次的代码性能提高了3倍。

j=2,即每2千条提交一次时间为45687ms，

j=10,即每1万条提交一次时间为40890ms

j=20,即每2万条提交一次时间为72000ms

j=100，每10万条提交一次时间为：64375ms

差不多每1万条插入一次数据，性能最高，40秒左右。

package ying;

import java.sql.*;
/*
* @author kuohao
* @Date 09-11-26
*
*/
public class sqltest {
public static void main(String[] args) {
?? java.util.Date now_start = new java.util.Date();
?? long start_time = now_start.getTime();
?? int st = 1000000;
?? String db = "com.mysql.jdbc.Driver";
?? String host = "jdbc:mysql://localhost:3306/nxjdf";
?? String user = "root";
?? String passwd = "12345";
?? Connection con = null;
?? try {
??? Class.forName(db).newInstance();
?? } catch (Exception e) {
??? System.out.println("加载驱动失败:" + db);
?? }
?? try {
??? con = DriverManager.getConnection(host, user, passwd);
??? con.setAutoCommit(false);???
??? StringBuilder sql = new StringBuilder("");
??? long start = System.currentTimeMillis();
??? int j = 0;
??? for (int i = 0; i < 1000; i++) {
???? sql = new StringBuilder("");
???? sql
?????? .append("insert into person (id,name,sex,phone,orgId) values");
???? for (int k = 0; k < 1000; k++) {
????? if (k == 999) {
?????? sql.append("('").append(i * 1000 + k + 1).append("',")
???????? .append("'这条记录是第=").append(i + 1)
???????? .append("',' ").append("',' ").append("','1')");
????? } else {
?????? sql.append("('").append(i * 1000 + k + 1).append("',")
???????? .append("'这条记录是第=").append(i + 1)
???????? .append("',' ").append("',' ")
???????? .append("','1'),");
????? }
???? }????
???? PreparedStatement p = con.prepareStatement(sql.toString());
???? p.executeUpdate();
???? p.addBatch();// 将一组参数添加到此 PreparedStatement 对象的批处理命令中。????
???? j++;
???? if (j == 2) {
????? con.commit(); // 每2K条提交一次
????? j = 0;
????? System.out.println("index=" + i);
???? }
???? if (i % 200 == 0) {
????? p.clearBatch();// 每20w条清理一下批处理
???? }
???? con.commit();
???? p.close();
??? }
??? s.close();
??? con.close();
??? long end = System.currentTimeMillis();
??? System.out.println("manyInsert()执行时间为：" + (end - start) + "ms");

?? } catch (Exception e) {
??? e.printStackTrace();
?? }

}
}
总结：批量插入：insert into tableName (x,xx,xxx) values(x,xx,xxx),(xx,xxx,xxxx)…,如一次插入1000条，然后提交批处理，批量插入的引入使性能大大提高了。