Views on batch data insertion (mysql)

Views on batch data insertion (mysql)

I went to the interview some time ago and asked how to efficiently insert 0.1 million records into the database. I have not handled similar issues before, nor have I read the relevant materials. I did not answer the results. I checked some materials today, three methods are summarized:

The test database is mysql !!!

Method 1:

Public static void insert () {// start time Long begin = new Date (). getTime (); // SQL prefix String prefix = "insert into tb_big_data (count, create_time, random) VALUES"; try {// Save the SQL suffix StringBuffer suffix = new StringBuffer (); // set the conn for non-automatic commit of transactions. setAutoCommit (false); // Statement st = conn. createStatement (); // pst is better than st in PreparedStatement pst = conn. prepareStatement (""); // outer loop, total number of transactions committed for (int I = 1; I <= 100; I ++) {// step for the first submission (int j = 1; j <= 10000; j ++) {// construct the SQL suffix. append ("(" + j * I + ", SYSDATE ()," + I * j * Math. random () + "),");} // construct the complete sqlString SQL = prefix + suffix. substring (0, suffix. length ()-1); // Add and execute sqlpst. addBatch (SQL); // execute the operation pst.exe cuteBatch (); // submit the transaction conn. commit (); // clear the last added data suffix = new StringBuffer ();} // first-class connection pst. close (); conn. close ();} catch (SQLException e) {e. printStackTrace ();} // end time Long end = new Date (). getTime (); // time consumed System. out. println ("cast:" + (end-begin)/1000 + "ms ");}

Output Time: cast: 23 MS

This method is currently the most efficient method!

Method 2:

public static void insertRelease() {Long begin = new Date().getTime();String sql = "INSERT INTO tb_big_data (count, create_time, random) VALUES (?, SYSDATE(), ?)";try {conn.setAutoCommit(false);PreparedStatement pst = conn.prepareStatement(sql);for (int i = 1; i <= 100; i++) {for (int k = 1; k <= 10000; k++) {pst.setLong(1, k * i);pst.setLong(2, k * i);pst.addBatch();}pst.executeBatch();conn.commit();}pst.close();conn.close();} catch (SQLException e) {e.printStackTrace();}Long end = new Date().getTime();System.out.println("cast : " + (end - begin) / 1000 + " ms");}

Note: there will be no comments. Similar to the above, there will be an analysis below!

Console output: cast: 111 MS

The execution time is five times that of the preceding method!

Method 3:

Public static void insertBigData (SpringBatchHandler sbh) {Long begin = new Date (). getTime (); JdbcTemplate jdbcTemplate = sbh. getJdbcTemplate (); final int count = 10000; String SQL = "INSERT INTO tb_big_data (count, create_time, random) VALUES (?, SYSDATE (),?) "; JdbcTemplate. batchUpdate (SQL, new BatchPreparedStatementSetter () {// set parameters for prepared statement. This method will be called throughout the process public void setValues (PreparedStatement pst, int I) throws SQLException {pst. setLong (1, I); pst. setInt (2, I) ;}// returns the number of updated result sets public int getBatchSize () {return count ;}}); Long end = new Date (). getTime (); System. out. println ("cast:" + (end-begin)/1000 + "ms ");}

This method uses spring batchUpdate for execution. Due to efficiency problems, there are only 10 thousand data records!

Execution time: cast: 387 MS

Summary: method 1 and method 2 are very similar. The only difference is that method 1 uses "insert into tb (...) values (...), (...)...;,

The second method is "insert into tb (...) values (...); insert into tb (...) values (...);... "way, if it wasn't for testing, I don't know the difference between the two is so big!

Of course, this is only the current test, and the specific execution time and step size also have a lot to do! If you change the step size to 100, it may take several minutes to test the method...

Methods are highly praised on the three networks. However, efficiency is also seen. It takes 6 minutes to record 10 thousand records, but the efficiency is not ideal! In addition, method 3 requires configuring the spring applicationContext environment for application!

However, method 3 is highly available in ssh/spring-mvc!

I started to study the big data issue just now. The above is only the result of a real test. It is not necessarily a fact. If you have good suggestions, please correct me. Thank you!

Learn from each other to make progress faster!

The source code will be sent later. You can download and test it directly!