Rendimiento de inserción por lotes JDBC

Necesito insertar un par de cientos de millones de registros en mysql db. Estoy por lotes insertándolo 1 millón a la vez. Por favor vea mi código abajo. Parece ser lento. ¿Hay alguna forma de optimizarlo?

try { // Disable auto-commit connection.setAutoCommit(false); // Create a prepared statement String sql = "INSERT INTO mytable (xxx), VALUES(?)"; PreparedStatement pstmt = connection.prepareStatement(sql); Object[] vals=set.toArray(); for (int i=0; i<vals.length; i++) { pstmt.setString(1, vals[i].toString()); pstmt.addBatch(); } // Execute the batch int [] updateCounts = pstmt.executeBatch(); System.out.append("inserted "+updateCounts.length); 

Tuve un problema de rendimiento similar con mysql y lo resolví estableciendo las propiedades useServerPrepStmts y rewriteBatchedStatements en la url de conexión.

 Connection c = DriverManager.getConnection("jdbc:mysql://host:3306/db?useServerPrepStmts=false&rewriteBatchedStatements=true", "username", "password"); 

Me gustaría ampliar la respuesta de Bertil, ya que he estado experimentando con los parámetros de URL de conexión.

rewriteBatchedStatements=true es el parámetro importante. useServerPrepStmts ya es falso de manera predeterminada, e incluso cambiarlo a verdadero no hace mucha diferencia en términos de rendimiento de inserción de lotes.

Ahora creo que es el momento de escribir cómo rewriteBatchedStatements=true mejora el rendimiento de forma tan espectacular. Lo hace rewriting of prepared statements for INSERT into multi-value inserts when executeBatch() ( fuente ). Eso significa que en lugar de enviar las siguientes n sentencias INSERT al servidor mysql cada vez que executeBatch() :

 INSERT INTO X VALUES (A1,B1,C1) INSERT INTO X VALUES (A2,B2,C2) ... INSERT INTO X VALUES (An,Bn,Cn) 

Enviaría una sola instrucción INSERT:

 INSERT INTO X VALUES (A1,B1,C1),(A2,B2,C2),...,(An,Bn,Cn) 

Puede observarlo al alternar en el registro de mysql (por SET global general_log = 1 ) que registraría en un archivo cada statement enviada al servidor de mysql.

Puede insertar varias filas con una instrucción de inserción, hacer unos pocos miles a la vez puede acelerar mucho las cosas, es decir, en lugar de hacerlo, por ejemplo, 3 inserciones del formulario INSERT INTO tbl_name (a,b,c) VALUES(1,2,3); , lo hace INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(1,2,3),(1,2,3); (Podría ser que JDBC .addBatch () haga una optimización similar ahora -aunque el mysql addBatch solía estar totalmente des-optimizado y solo emitir consultas individuales de todos modos- no sé si todavía es el caso con los controladores recientes)

Si realmente necesita velocidad, cargue sus datos de un archivo separado por comas con LOAD DATA INFILE , obtenemos aproximadamente 7-8 veces la aceleración haciéndolo frente a hacer decenas de millones de insertos.

Si:

  1. Es una tabla nueva, o la cantidad a insertar es mayor que los datos ya insertados
  2. Hay índices en la mesa
  3. No necesita otro acceso a la tabla durante la inserción

Luego, ALTER TABLE tbl_name DISABLE KEYS puede mejorar en gran medida la velocidad de sus insertos. Cuando hayas terminado, ejecuta ALTER TABLE tbl_name ENABLE KEYS para comenzar a construir los índices, lo que puede llevar un tiempo, pero no tanto como hacerlo para cada inserción.

Puede intentar usar el objeto DDBulkLoad.

 // Get a DDBulkLoad object DDBulkLoad bulkLoad = DDBulkLoadFactory.getInstance(connection); bulkLoad.setTableName(“mytable”); bulkLoad.load(“data.csv”); 
 try { // Disable auto-commit connection.setAutoCommit(false); int maxInsertBatch = 10000; // Create a prepared statement String sql = "INSERT INTO mytable (xxx), VALUES(?)"; PreparedStatement pstmt = connection.prepareStatement(sql); Object[] vals=set.toArray(); int count = 1; for (int i=0; i