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

Question

Я использую 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 мс.

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

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

Answer 1

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

Как это работает с обновлениями?

Скажем, у вас есть стол 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;

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

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

Это также работает, чтобы ускорить вставки, конечно. Поскольку у вас уже есть данные в таблице этапов, все, что вам нужно сделать, это выдать INSERT INTO ... SELECT оператор с данными, которых еще нет в таблице назначения.

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, который увеличит ваш первичный ключ, даже если они ничего не будут делать.