mysql - descargar - Rendimiento de inserción por lotes JDBC

mysql jdbc driver

Question

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);

Answer 1

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 declaración enviada al servidor de mysql.

Answer 2

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.

Answer 3

Puede intentar usar el objeto DDBulkLoad.

// Get a DDBulkLoad object
DDBulkLoad bulkLoad = DDBulkLoadFactory.getInstance(connection);
bulkLoad.setTableName(“mytable”);
bulkLoad.load(“data.csv”);

Answer 4

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.

Answer 5

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");

Answer 6

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<vals.length; i++) {
            pstmt.setString(1, vals[i].toString());
            pstmt.addBatch();
            if(count%maxInsertBatch == 0){
                 pstmt.executeBatch();
            }
            count++;
        }

        // Execute the batch
        pstmt.executeBatch();
        System.out.append("inserted "+count);