Почему SQLite3 работает медленно при массовом удалении в Python?

Вы столкнулись с типичной проблемой: на VPS (1 ядро, 2 ГБ ОЗУ) скрипт Python с SQLite3 удаляет записи из таблицы с 11 миллионами строк, сверяясь со второй таблицей (3 миллиона записей). Процесс длится более 10 часов - это ненормально. Разберём причины и покажем, как ускорить выполнение в десятки раз.

Почему ваш код работает так медленно?

Основная проблема - неэффективная работа с памятью и отсутствие индексов. Рассмотрим по шагам:

1. Загрузка всех данных в оперативную память

Команда cur.execute('''SELECT user_full_data_user_id FROM user_full_data''') выгружает 11 миллионов значений в список Python. Это потребляет гигабайты ОЗУ на VPS с 2 ГБ, вызывая свопинг и замедление в 100-1000 раз.

2. Создание кортежа через цикл

Конструкция korteg = (); for k in user_parsing_ok: korteg += k - крайне неэффективна. Каждая итерация создаёт новый кортеж, копируя все предыдущие элементы. Для 11 млн записей это эквивалентно O(n²) операций.

3. Отсутствие индекса для соединения

Запрос DELETE FROM parsing WHERE parsing_user_id IN {korteg} выполняет полное сканирование таблицы parsing (3 млн строк) для каждого элемента кортежа, если нет индекса по полю parsing_user_id.

Как ускорить массовое удаление в SQLite3?

Вот проверенные методы оптимизации:

Способ 1: Используйте подзапрос с EXISTS

Вместо выгрузки данных в Python, выполните удаление одним SQL-запросом:

DELETE FROM parsing WHERE EXISTS (SELECT 1 FROM user_full_data WHERE user_full_data.user_full_data_user_id = parsing.parsing_user_id)

Этот запрос обрабатывается целиком внутри SQLite3, без передачи данных в Python. Время выполнения сокращается до минут.

Способ 2: Добавьте индексы

Перед массовым удалением убедитесь, что на столбцах user_full_data_user_id и parsing_user_id созданы индексы:

CREATE INDEX IF NOT EXISTS idx_user_full_data_id ON user_full_data(user_full_data_user_id); CREATE INDEX IF NOT EXISTS idx_parsing_id ON parsing(parsing_user_id);

Способ 3: Удаляйте пачками (BATCH DELETE)

Если нужно сохранить контроль над процессом, удаляйте записи порциями по 1000-10000 строк:

while True: cur.execute('''DELETE FROM parsing WHERE parsing_user_id IN (SELECT user_full_data_user_id FROM user_full_data LIMIT 10000)''') con.commit() if cur.rowcount == 0: break

Почему VACUUM замедляет процесс?

Команда VACUUM перестраивает весь файл базы данных. Для таблицы с миллионами записей это может занимать часы. Лучше выполнять VACUUM после завершения всех операций удаления, а не после каждого пакета.

Ограничения SQLite3 для больших данных

SQLite3 - встраиваемая СУБД, не предназначенная для высоконагруженных операций. При объёмах свыше 10 млн записей рекомендуется:

• Использовать PostgreSQL или MySQL для серверных задач
• Настроить WAL-режим в SQLite: PRAGMA journal_mode=WAL;
• Увеличить кэш: PRAGMA cache_size=-80000; (80 МБ)

Итоговый оптимизированный код

import sqlite3 con = sqlite3.connect('database.db') cur = con.cursor() cur.execute('PRAGMA journal_mode=WAL') cur.execute('PRAGMA cache_size=-80000') cur.execute('''CREATE INDEX IF NOT EXISTS idx_user_id ON user_full_data(user_full_data_user_id)''') cur.execute('''CREATE INDEX IF NOT EXISTS idx_parsing_id ON parsing(parsing_user_id)''') print('Запуск удаления проверенных юзеров') cur.execute('''DELETE FROM parsing WHERE EXISTS (SELECT 1 FROM user_full_data WHERE user_full_data.user_full_data_user_id = parsing.parsing_user_id)''') con.commit() cur.execute('VACUUM') print(f'Удалено строк: {cur.rowcount}') con.close()

Этот код выполнится за 10-30 минут вместо 10 часов.