Массовое обновление sqlalchemy в MySQL работает очень медленно

Я использую SQLAlchemy 1.0.0 и хочу сделать несколько UPDATE ONLY (обновлять, если первичный ключ соответствует, иначе ничего не делать) запросы в пакетном режиме.

Я провел небольшой эксперимент и обнаружил, что массовое обновление выглядит намного медленнее, чем массовая вставка или массовая upsert.

Не могли бы вы помочь мне указать, почему он работает так медленно, или есть ли альтернативный способ / идея сделать BULK UPDATE (not BULK UPSERT) with SQLAlchemy?

Ниже представлена ​​таблица в 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;

И тестовый код:

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)

Результат при num_of_rows = 100:

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

Результат при num_of_rows = 1000:

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

Сетевая задержка до сервера базы данных составляет около 500 мс.

Похоже, что при массовом обновлении он отправляет и выполняет каждый запрос один за другим, а не в пакетном режиме?

Заранее спасибо.


python mysql sqlalchemy bulkinsert bulkupdate
person twds    schedule 27.10.2015    source источник
comment
Не существует массового обновления. Попробуйте заключить все обновления в одну транзакцию и посмотрите, как это работает.   -  person Shadow    schedule 27.10.2015
comment
@Shadow он по-прежнему очень медленный (стоит почти столько же), похоже, он просто отправляет каждый запрос на обновление в базу данных один за другим, в то время как сетевая задержка каждой операции отправки составляет 500 мс.   -  person twds    schedule 27.10.2015
comment
Затем сгенерируйте строку sql самостоятельно со всеми операторами обновления, разделенными; и отправьте его единым пакетом в свою базу данных.   -  person Shadow    schedule 27.10.2015
comment
@Shadow спасибо, теперь работает намного быстрее. Но так ли же SQLAlchemy реализует массовую вставку / массовое обновление? Раньше я думал, что он отправляет шаблон SQL в базу данных, а затем отправляет пакет данных на сервер, поэтому я подумал, что он также должен работать для массового обновления.   -  person twds    schedule 27.10.2015
comment
Повторяю: массового обновления не существует. Есть массовая вставка, но, как отметил @pi в своем ответе, sqlalchemy даже не использует этот синтаксис. Я не совсем уверен, что массовая вставка, а затем обновление с использованием объединений будет намного быстрее, чем отправка операторов обновления в пакете с использованием одной транзакции.   -  person Shadow    schedule 27.10.2015

Ответы (1)


arrow_upward
4
arrow_downward

Вы можете ускорить операции массового обновления с помощью уловки, даже если сервер базы данных (как в вашем случае) имеет очень плохую задержку. Вместо непосредственного обновления таблицы вы используете stage-table для очень быстрой вставки новых данных, а затем выполняете одно обновление-соединение с таблицей назначения. Это также имеет то преимущество, что вы значительно сокращаете количество операторов, которые нужно отправлять в базу данных.

Как это работает с ОБНОВЛЕНИЯМИ?

Допустим, у вас есть таблица entries, и у вас постоянно поступают новые данные, но вы хотите обновлять только те, которые уже были сохранены. Вы создаете копию своей целевой таблицы entries_stage только с соответствующими полями:

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

Затем вы вставляете свои данные с помощью массовой вставки. Это можно ускорить еще больше, если вы используете синтаксис множественной вставки значений MySQL, который изначально не поддерживается SQLAlchemy, но может быть построен без особого труда.

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

В конце вы обновляете значения целевой таблицы значениями из промежуточной таблицы следующим образом:

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

Тогда все готово.

А как насчет вставок?

Конечно, это также работает для ускорения вставки. Поскольку у вас уже есть данные в stage-table, все, что вам нужно сделать, это выполнить оператор INSERT INTO ... SELECT с данными, которых еще нет в destination-table.

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

Приятно то, что вам не нужно делать INSERT IGNORE, REPLACE или ON DUPLICATE KEY UPDATE, которые увеличивают ваш первичный ключ, даже если они ничего не будут делать.

person pi.    schedule 27.10.2015