MySQL Batch data import inquiry

Recently working on a problem, how to import large amounts of data (100mb+) to the remote MySQL server.

Try 1:

Statement executes the ExecuteBatch method. Import 1000 records at a time. Time is 12s/1000 bar. More slowly.

For 1M insertions This means that it takes 4 hours and the load delay is raised to 85s/1000 or even higher due to network conditions, database load, and other factors.

Poor results.

Try 2:

With PreparedStatement, this method requires a pre-specified "format" of the insert operation.

The measured efficiency in this way is dozens of rows per second.

　　Note that after setting Rewritebatchedstatements to True, all 780,000 data is imported into the database in less than a minute. This is a proven approach.

Code:

1 ImportJava.io.BufferedReader;2 ImportJava.io.FileReader;3 Importjava.sql.Connection;4 ImportJava.sql.DriverManager;5 Importjava.sql.PreparedStatement;6 7 /**8  *9  */Ten  Public classPreparedstatementtestmain { One     Private StaticPreparedStatement PS; A      Public Static voidMain (string[] args) { -         Try{ -Class.forName ("Com.mysql.jdbc.Driver"); theConnection conn = drivermanager.getconnection ("jdbc:mysql://remote-host/test?user=xxx&password=xxx"); -String sql = "INSERT into test values (?,?,?,?,?,?,?,?,?,?,?)"; -PS =conn.preparestatement (SQL); -  +BufferedReader in =NewBufferedReader (NewFileReader ("xxxx")); - String Line; +             intCount =0; A              while(line = In.readline ())! =NULL){ atCount+=1; -String[] values = line.split ("\ T",-1); -                 //Ps.setint (1,count); -                  for(intI =1;i<values.length;i++) { - //if (i==6) { - //Ps.setint (I+1,integer.parseint (values[i)); in //}else{ - //if (values[i]==null) { to //ps.setstring (i, ""); + //}else { - ps.setstring (i, values[i]); the //                    } * //                    } $                 }Panax Notoginseng Ps.addbatch (); -System.out.println ("line" +count); the             } + Ps.executebatch (); A ps.close (); the}Catch(Exception e) { + e.printstacktrace (); -         } $     } $}

Try 3:

Use the Mysqlimport tool. After measurement, the speed is close to the speed of the experiment 2 plus rewritebatchedstatements. But only if the data has to be saved as a file.

　　

　　

Rewritebatchedstatements exactly why the speed optimization this much?

One argument: The purpose of this is to allow MySQL to package multiple MySQL INSERT statements into a packet and MySQL server communication. This can greatly reduce network overhead.

Another way of saying:

Rewriting Batches

? "Rewritebatchedstatements=true"

? Affects (Prepared) Statement.add/executebatch ()

? Core Concept-remove Latency

? Special treatment for prepared INSERT statements

--mark Matthews-sun Microsystems

PreparedStatement VS Statement

The SQL statement is precompiled by the database system (if the JDBC driver supports it), and the pre-processing statements are precompiled, and this precompiled SQL query can be reused in future use.

MySQL Batch data import inquiry