La actualización masiva de Sqlalchemy en MySQL funciona muy lentamente

Estoy usando SQLAlchemy 1.0.0 , y quiero hacer algunas consultas de UPDATE ONLY (actualizar si no coinciden con la clave primaria), por lotes.

Hice algunos experimentos y descubrí que la actualización masiva parece mucho más lenta que la inserción masiva o la inserción masiva.

¿Podría ayudarme, por favor, a señalar por qué funciona tan lento o hay alguna forma / idea alternativa para hacer la BULK UPDATE (not BULK UPSERT) with SQLAlchemy ?

A continuación se muestra la tabla en MYSQL:

 CREATE TABLE `test` ( `id` int(11) unsigned NOT NULL, `value` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; 

Y el código de prueba:

 from sqlalchemy import create_engine, text import time driver = 'mysql' host = 'host' user = 'user' password = 'password' database = 'database' url = "{}://{}:{}@{}/{}?charset=utf8".format(driver, user, password, host, database) engine = create_engine(url) engine.connect() engine.execute('TRUNCATE TABLE test') num_of_rows = 1000 rows = [] for i in xrange(0, num_of_rows): rows.append({'id': i, 'value': i}) print '--------- test insert --------------' sql = ''' INSERT INTO test (id, value) VALUES (:id, :value) ''' start = time.time() engine.execute(text(sql), rows) end = time.time() print 'Cost {} seconds'.format(end - start) print '--------- test upsert --------------' for r in rows: r['value'] = r['id'] + 1 sql = ''' INSERT INTO test (id, value) VALUES (:id, :value) ON DUPLICATE KEY UPDATE value = VALUES(value) ''' start = time.time() engine.execute(text(sql), rows) end = time.time() print 'Cost {} seconds'.format(end - start) print '--------- test update --------------' for r in rows: r['value'] = r['id'] * 10 sql = ''' UPDATE test SET value = :value WHERE id = :id ''' start = time.time() engine.execute(text(sql), rows) end = time.time() print 'Cost {} seconds'.format(end - start) 

La salida cuando num_of_rows = 100:

 --------- test insert -------------- Cost 0.568960905075 seconds --------- test upsert -------------- Cost 0.569655895233 seconds --------- test update -------------- Cost 20.0891299248 seconds 

La salida cuando num_of_rows = 1000:

 --------- test insert -------------- Cost 0.807548999786 seconds --------- test upsert -------------- Cost 0.584554195404 seconds --------- test update -------------- Cost 206.199367046 seconds 

La latencia de la red al servidor de la base de datos es de alrededor de 500 ms.

Parece que en actualización masiva envía y ejecuta cada consulta una por una, ¿no en lote?

Gracias por adelantado.

Puede acelerar las operaciones de actualización masiva con un truco, incluso si el servidor de base de datos (como en su caso) tiene una latencia muy mala. En lugar de actualizar su tabla directamente, usa una tabla de etapas para insertar sus nuevos datos muy rápidamente, luego haga una actualización de la combinación a la tabla de destino . Esto también tiene la ventaja de que reduce considerablemente la cantidad de declaraciones que tiene que enviar a la base de datos.

¿Cómo funciona esto con las ACTUALIZACIONES?

Digamos que tiene entries una tabla y que llegan nuevos datos todo el tiempo, pero que solo desea actualizar aquellos que ya se han almacenado. Crea una copia de su tabla de destino entries_stage con solo los campos relevantes en ella:

 entries = Table('entries', metadata, Column('id', Integer, autoincrement=True, primary_key=True), Column('value', Unicode(64), nullable=False), ) entries_stage = Table('entries_stage', metadata, Column('id', Integer, autoincrement=False, unique=True), Column('value', Unicode(64), nullable=False), ) 

Luego inserte sus datos con un inserto masivo. Esto se puede acelerar aún más si utiliza la syntax de inserción de valor múltiple de MySQL, que no es compatible de forma nativa con SQLAlchemy, pero puede construirse sin mucha dificultad.

 INSERT INTO enries_stage (`id`, `value`) VALUES (1, 'string1'), (2, 'string2'), (3, 'string3'), ...; 

Al final, actualiza los valores de la tabla de destino con los valores de la tabla de etapas como este:

  UPDATE entries e JOIN entries_stage es ON e.id = es.id SET e.value = es.value; 

Entonces has terminado

¿Qué pasa con las inserciones?

Esto también funciona para acelerar las inserciones, por supuesto. Como ya tiene los datos en la tabla de etapas , todo lo que necesita hacer es emitir una INSERT INTO ... SELECT , con los datos que aún no están en la tabla de destino .

 INSERT INTO entries (id, value) SELECT FROM entries_stage es LEFT JOIN entries e ON e.id = es.id HAVING e.id IS NULL; 

Lo bueno de esto es que no tiene que hacer INSERT IGNORE , REPLACE o ON DUPLICATE KEY UPDATE , que incrementará su clave principal, incluso si no hacen nada .