MySQL database bulk high-speed insert

In the recent process of processing a batch of data, the fields from the table in the library need to be processed and exported to a new table. Only the amount of data in this table is nearly 500w.

The problem with this amount of data is that it has to be handled for a long time.
First think, a sentence of the insertion, large data processing time is long, ignore.

Next think, multi-threaded INSERT, think database connection is need to synchronize, so feel very useful.
Finally, you use PreparedStatement to pre-compile SQL for Bulk Insert batch processing.

All right. The batch is now inserted into the test.
1. Use simple Batch

public static void main(String[] args) { Connection conn = getConn(lsqlurl, luser, lpassword); long startTime = System.currentTimeMillis(); try { PreparedStatement pst = conn.prepareStatement("insert into testmy (id,name,age) values (?
,?,?
)"); for(inti =0; I < -; i++) {Pst.setint (1,3); Pst.setstring (2,"XX"); Pst.setint (3,Ten); Pst.addbatch (); } pst.executebatch ();LongEndTime = System.currenttimemillis (); System.out.println ((endtime-starttime)/ ++"S"); System.out.println ("Test SQL Batch--->2000 ..."); }Catch(SQLException e) {E.printstacktrace (); }finally{if(conn!=NULL) {Try{Conn.close (); }Catch(SQLException e) {E.printstacktrace (); } } } }

You will find that the time will be around 30s.

2k rows of data are inserted for 30 seconds.
2w Row Data insertion time is 940 seconds (about 16min).

2. Change the batch you submitted voluntarily

 Public Static void Main(string[] args) {Connection conn = Getconn (Lsqlurl, Luser, Lpassword);LongStartTime = System.nanotime ();Try{Conn.setautocommit (false); PreparedStatement PST = conn.preparestatement (INSERT into Test (id,name,age) VALUES (?,?,?
)"); for(inti =0; I < -; i++) {Pst.setint (1,3); Pst.setstring (2,"XX"); Pst.setint (3,Ten); Pst.addbatch (); } pst.executebatch (); Conn.commit ();LongEndTime = System.nanotime (); System.out.println ((endtime-starttime)/1000000+"MS"); System.out.println ("Test SQL Batch--->2000 ..."); }Catch(SQLException e) {Try{Conn.rollback ();

MySQL database bulk high-speed insert