【求优化】MySQL批量插入5W条左右数据
- Java code
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=true
jar包是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万条数据库在插入的时间减少些、或者能使最优？
需要其他参数和环境请告知、
先谢谢各位了、
[解决办法]
只需要set global innodb_flush_log_at_trx_commit=0就行
[解决办法]
打开事务
OR
用 LOAD DATA FIIFILE 导入
[解决办法]
用load data infile导入速度还是比较快的。
[解决办法]
[解决办法]
我晕 手动批量写事务commit这样对于0 1 2 这个参数基本不失去作用了
[解决办法]
问题应该不是innodb_flush_log_at_trx_commit, 这个参数是控制写redo log写的时机。
感觉上时间还是消耗在每个语句的提交上。