Блог

Главная - Вопросы по программированию - Удалить миллионы строк с помощью SQLAlchemy в MariaDB

Удалить миллионы строк с помощью SQLAlchemy в MariaDB

#mysql #sqlalchemy #mariadb

#mysql #sqlalchemy #mariadb

Вопрос:

У нас огромная база (~ от 1 до) с миллиардами строк, особенно в одной таблице. Недавно нам пришлось удалить 10 миллионов строк за одно удаление, и у нас возникли проблемы с этим. Когда выполняются такого рода удаления, запрос использует много памяти, что может привести к сбоям, также может возникнуть проблема с блокировкой. Такого рода удаления будут происходить все чаще.

С сегодняшнего дня мы удаляем строки, используя этот код:

 delete_query = myDB.__table__.delete().where(myDB.foreign_id.in_(foreign_ids_to_delete))
dbsession.execute(delete_query)

после этого есть commit() .

Я думал о добавлении ограничения на удаление по пакету, но, похоже, это не обрабатывается SQLAlchemy. Возможно, я мог бы сделать это на стороне python: получить все идентификаторы для удаления с помощью select и удалить их, например, пакетом 10000. Это может привести к проблемам с производительностью и памятью на стороне python.

Может synchronize_session быть одной из причин, по которой объем памяти настолько велик?

Комментарии:

1. Если foreign_ids_to_delete это большой список, то для создания одного только объекта statement потребуется значительное количество памяти, не говоря уже о компиляции дерева инструкций в строку SQL. Попробуйте использовать самую последнюю (стабильную) версию SQLAlchemy и расширяющийся bindparam() в in_() . С другой стороны, если вы считываете идентификаторы из базы данных, зачем вообще переходить на Python? Synchronize_session никоим образом не задействовано в коде, представленном здесь.

2. foreign_ids_to_delete не велико, вероятно, это максимум 100 значений. В Python можно было бы выполнить цикл обработки идентификаторов в пакетном режиме, чтобы выполнить удаление и ограничить объем используемой памяти.

3. Не совсем ясно, поступает ли список идентификаторов из самой базы данных или из внешнего источника. Если они поступают из БД, вы также можете выполнять цикл в БД пакетами. Под используемой памятью вы имеете в виду память сервера БД или память сервера приложений?

4. Список идентификаторов поступает из базы данных, но после нескольких шагов в приложении. Наличие всего в БД кажется маловероятным (несколько условий, которые зависят от параметров). Не совсем ясно, поступает ли это из sqlalchemy (серверной части) или из BDD (на том же сервере). Но это значительно увеличивается при удалении 10 миллионов строк.

5. Методы для больших удалений: mysql.rjweb.org/doc.php/deletebig

Ответ №1:

MariaDB Connector / Python поддерживает собственные операции массового удаления (которые были введены в MariaDB 10.2) с использованием двоичного протокола и executemany() метода.

Поскольку диалект MariaDB еще не включен в SQLAlchemy (есть открытый запрос на извлечение), вы можете удалить его с помощью простого скрипта python (демонстрационная таблица содержит записи, которые следует удалить, table delete_keys идентификаторы, которые следует удалить.

 import mariadb

conn= mariadb.connect(database="testp")
conn.autocommit= False

cursor=conn.cursor()

# read ids which needs to be deleted
cursor.execute("select id from delete_keys")
ids=cursor.fetchall()
print("Deleting %s rows" % cursor.rowcount)

cursor.executemany("DELETE FROM demo WHERE id=?", ids)
conn.commit()
print("%s rows deleted" % cursor.rowcount)

Удаление 20 тысяч строк занимает около половины секунды (сервер базы данных работает на том же компьютере):

 $ time python3.8 delkeys.py
Deleting 20000 rows
20000 rows deleted

real    0m0,535s
user    0m0,134s
sys     0m0,024s