Почему SQL запрос обрабатывается так долго?

Question

Ким

python , sqlite

Почему SQL запрос обрабатывается так долго?

Добрый день. <br/> Я изучаю Python и работу с SQLite3. <br/> Меня беспокоит медленная обработка запросов. <br/> У меня есть VPS-сервер с одним ядром на 2.5 ГГц и 2 ГБ оперативной памяти. <br/> В моей SQLite3 базе находятся две таблицы: в одной — 11 миллионов записей, в другой — 3 миллиона. <br/> Мне необходимо удалить из первой таблицы все значения, которые содержатся во второй, при этом сверка должна происходить по одному столбцу. <br/> Вот код на Python, который выполняет данную операцию: <br/> 
<pre><code class="python">print('Запуск удаления проверенных юзеров')
    step = 0
    cur.execute('''SELECT user_full_data_user_id FROM user_full_data''')
    user_parsing_ok = cur.fetchall()
    korteg = ()
    for k in user_parsing_ok:
        korteg += k
        step += 1
    cur.execute(f'''DELETE FROM parsing
                    WHERE parsing_user_id IN {korteg}''')
    con.commit()
    cur.execute('''VACUUM''')  # Сжимаем таблицу, удаляя пустые строки
    print(f'Строк проверено {step}')</code></pre> <br/> Этот код выполняется на удаленной машине уже 10 часов и еще не завершился. <br/> В чем причина такой медленной работы: в ограничениях SQLite3, в том, что сам Python медленнее, или в недочетах моего кода?

23 декабря 2024 г. 9:43

Ответы на вопрос 2

Похожие вопросы

Answer 1

Задержка в обработке SQL-запросов, в частности при работе с SQLite и большими объемами данных, может быть вызвана несколькими факторами. Давайте рассмотрим, что именно может вызывать такие проблемы в вашем коде.

1. **Количество данных и структура запроса**:
- Вы загружаете все `user_full_data_user_id` в память и формируете большой кортеж для использования в запросе `DELETE`. Если в `user_parsing_ok` очень много пользователей, то это может привести к созданию слишком большого SQL-запроса. SQLite имеет ограничения на размер запросов, и если кортеж слишком велик, это может привести к сбоям.

2. **Производительность операции DELETE**:
- Удаление большого количества записей в SQLite может быть медленным, поскольку это требует блокировки таблицы и может повлечь за собой множество внутренних операций по освобождению места. Это особенно актуально для больших таблиц.

3. **Индексирование**:
- Если столбец, по которому вы выполняете удаление (`parsing_user_id`), не индексирован, это может существенно замедлить операцию. Убедитесь, что на этом столбце есть индекс. Индексы ускоряют операции поиска и удаления, особенно в больших таблицах.

4. **Использование VACUUM**:
- Хотя вызов `VACUUM` после удаления может помочь сжать базу данных, это может занять много времени, особенно если у вас большие объемы данных. Лучше вызывать `VACUUM` по расписанию, например, после нескольких больших операций удаления, а не сразу после каждой операции.

5. **Возможные альтернативы**:
- Вместо загрузки всех ID в память, вы можете использовать SQL JOIN для удаления. Например:
```sql
DELETE FROM parsing
WHERE parsing_user_id IN (SELECT user_full_data_user_id FROM user_full_data);
```
Это позволит базе данных самой обработать удаление, не загружая все данные в память.

6. **Тестирование и отладка**:
- Поскольку ваша база данных довольно большая, попробуйте сначала запустить меньшее количество записей или поработать с более мелкими частями данных для понимания, как изменяется производительность.

Попробуйте применить указанные рекомендации и оптимизировать ваш код. Если проблема не исчезнет, возможно, стоит рассмотреть использование более мощной СУБД, если ваша работа с данными становится слишком долгой или сложной.

Answer 2

Можно использовать подзапрос вместо двух запросов и цикла, если я не ошибаюсь cur.execute('''DELETE FROM parsing
               WHERE parsing_user_id IN (SELECT user_full_data_user_id FROM user_full_data)''')