【求优化】MySQL批量插入5W条左右数据

Question

     Connection conn = null;
PreparedStatement ps = null;
try {
conn = DBUtils.getConnection();
conn.setAutoCommit(false); long s = System.currentTimeMillis();

StringBuffer sb = new StringBuffer();
sb.append(" INSERT INTO TEST1 ");
sb.append(" ( ");
sb.append(" A , B , C , ");
sb.append(" D , E , F , ");
sb.append(" G , H , I ");
sb.append(" J , K , K ");
sb.append(" M , N , O ");
sb.append(" P , Q , R ");
sb.append(" S , T , U ");
sb.append(" V , W , X ");
sb.append(" Y , Z ");
sb.append(" ) ");
sb.append(" VALUES");
sb.append(" (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,? (SELECT NAME FROM TEST2 WHERE ID =? )  ");

ps = conn.prepareStatement(sb.toString(),ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
int count = 0 ;

for (int i=0,n=50000;i<n;i++) {

ps.setString(1, "value1");
ps.setString(2, "value2");
ps.setString(3, "value3");
ps.setString(4, "value4");
ps.setString(5, "value5");
ps.setString(6, "value6");
ps.setString(7, "value7");
                                ps.setString(8, "value8");
                                ps.setString(9, "value9");
                                ps.setString(10, "value10");
                                ps.setString(11, "value11");
                                ps.setString(12, "value12");
                                ps.setString(13, "value13");
                                ps.setString(14, "value14");
                                ps.setString(15, "value15");
                                ps.setString(16, "value16");
                                ps.setString(17, "value17");
ps.setString(18, "value18");
ps.setString(19, "value19");
ps.setString(20, "value20");
ps.setString(21, "value21");
ps.setString(22, "value22");
ps.setString(23, "value23");
ps.setString(24, "value24");
ps.setString(25, "value25"); ps.setInt(26, i);

ps.addBatch();
count ++ ;
if(count % 10000 == 0){
//1W条一提交
ps.executeBatch();
conn.commit();
count = 0;
}

}

ps.executeBatch();
conn.commit();
ps.clearBatch();

long e = System.currentTimeMillis();
System.out.println("共用时：" + (e - s)/1000.00000000 + "秒");

} catch (Exception e) {
try {
conn.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
} finally {
if (ps != null) {
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
Mysql数据库连接的URL 附加了
rewriteBatchedStatements=truejar包是5.1.17的jar引擎是InnoDB的
具体的测试时间   每多少条记录一提交
只有插入（就是只有insert into table values(?,?,?)）
5W条数据计时 ：13.591秒 5W条数据计时 ：13591ms  ---100提交
5W条数据计时 ：8.681秒 5W条数据计时 ：8681ms    ---200一提交
5W条数据计时 ：6.617秒 5W条数据计时 ：6617ms    ----500一提交
5W条数据计时 ：5.173秒 5W条数据计时 ：5173ms    ----1000一提交
5W条数据计时 ：4.766秒 5W条数据计时 ：4766ms    ----2000一提交
5W条数据计时 ：5.242秒 5W条数据计时 ：5242ms    ----2222一提交
5W条数据计时 ：4.938秒 5W条数据计时 ：4938ms    ----2500一提交
5W条数据计时 ：5.017秒 5W条数据计时 ：5017ms    ----3000一提交
5W条数据计时 ：5.208秒 5W条数据计时 ：5208ms    ----5000一提交
5W条数据计时 ：5.027秒 5W条数据计时 ：5027ms    ----10000一提交
5W条数据计时 ：5.771秒 5W条数据计时 ：5771ms    ----20000一提交插入加查询(在插入的时候带入查询后的值在插入)
5W条数据计时 ：20.955秒 5W条数据计时 ：20955ms   -100一提交
5W条数据计时 ：13.436秒 5W条数据计时 ：13436ms   -500一提交
5W条数据计时 ：12.999秒 5W条数据计时 ：12999ms   -1000一提交
5W条数据计时 ：17.855秒 5W条数据计时 ：17855ms   -2000一提交
5W条数据计时 ：11.952秒 5W条数据计时 ：11952ms   -3000一提交
5W条数据计时 ：11.818秒 5W条数据计时 ：11818ms   -5000一提交
5W条数据计时 ：10.498秒 5W条数据计时 ：10498ms   -10000一提交
5W条数据计时 ：9.668秒 5W条数据计时 ：9668ms     -20000一提交
5W条数据计时 ：15.843秒 5W条数据计时 ：15843ms   -30000一提交能不能5万条数据库在插入的时间减少些、或者能使最优？
需要其他参数和环境请告知、
先谢谢各位了、

Answer 1

只需要set global innodb_flush_log_at_trx_commit=0就行

Answer 2

打开事务
OR
用  LOAD DATA FIIFILE 导入

Answer 3

用load data infile导入速度还是比较快的。

Answer 4

set global innodb_flush_log_at_trx_commit= 0   1   2  不同的情况、0
5W条数据计时 ：10.832秒 5W条数据计时 ：10832ms
5W条数据计时 ：13.013秒 5W条数据计时 ：13013ms
1
5W条数据计时 ：11.867秒 5W条数据计时 ：11867ms
5W条数据计时 ：12.358秒 5W条数据计时 ：12358ms
2
5W条数据计时 ：10.609秒 5W条数据计时 ：10609ms
5W条数据计时 ：10.749秒 5W条数据计时 ：10749ms怎么感觉会出现反效果？
在网上查了查2会安全些、以后还要部署到集群环境、

Answer 5

情况是这样的  有个批量增加的功能选取好数据后 点击才会存入数据库的、
如果使用load data infile 的话  好像还要做io操作文件 
是不是对性能又有些增加？

Answer 6

我上面有打开事物、
conn.setAutoCommit(false); 设置手动设置事物、
之后按每多少条来提交事物、load data infile 的话、应该还要先对io做操作文件插入、性能有损耗吗？

Answer 7

感觉你没改innodb_flush_log_at_trx_commit这个参数  试试在你配置文件里更改这个参数  然后重启数据库 

Answer 8

# If set to 1, InnoDB will flush (fsync) the transaction logs to the
# disk at each commit, which offers full ACID behavior. If you are
# willing to compromise this safety, and you are running small
# transactions, you may set this to 0 or 2 to reduce disk I/O to the
# logs. Value 0 means that the log is only written to the log file and
# the log file flushed to disk approximately once per second. Value 2
# means the log is written to the log file at each commit, but the log
# file is only flushed to disk approximately once per second.
innodb_flush_log_at_trx_commit=0修改my.ini后   在服务里重启了mysql服务、
程序没改动、
执行的是插入后查询在赋值
执行了3次  10000一提交
5W条数据计时 ：10.857秒 5W条数据计时 ：10857ms
5W条数据计时 ：10.491秒 5W条数据计时 ：10491ms
5W条数据计时 ：10.72秒 5W条数据计时 ：10720ms5000一提交
5W条数据计时 ：12.238秒 5W条数据计时 ：12238ms
5W条数据计时 ：12.069秒 5W条数据计时 ：12069ms2000
5W条数据计时 ：11.678秒 5W条数据计时 ：11678ms
5W条数据计时 ：11.653秒 5W条数据计时 ：11653ms
1000
5W条数据计时 ：11.91秒 5W条数据计时 ：11910ms
5W条数据计时 ：11.496秒 5W条数据计时 ：11496ms
怎么感觉没有变化呢、
MySQL Server 5.5.15
是不是我哪里配置错了？

Answer 9

我晕  手动批量写事务commit这样对于0 1 2 这个参数基本不失去作用了

Answer 10

问题应该不是innodb_flush_log_at_trx_commit, 这个参数是控制写redo log写的时机。感觉上时间还是消耗在每个语句的提交上。