SQLite: эффективный способ сбросить множество строк

SQlite, Android, настоящая история. У меня есть таблица, которую я использую в качестве кеша:

CREATE TABLE cache(key TEXT, ts TIMESTAMP, size INTEGER, data BLOB); CREATE UNIQUE INDEX by_key ON cache(key); CREATE INDEX by_ts ON cache(ts); 

Во время жизни приложения я заполняю кеш, и в какой-то момент я хочу его очистить и удалить N записей. Обычно эта таблица будет содержать ~ 25000 blobs ~ 100-500Kb каждый, общий размер блобов в БД составляет 600-800 Мб, но теперь я тестирую ~ 2000, которые составляют около 60 Мб (следующие цифры для этого случая). Clear удаляет 90% записей кэша.

Я пробовал разные способы сделать это, здесь краткое описание:

[1] Худший и самый простой. Сначала выберите, а затем удалите один за другим, идущий курсор. Ужасно медленно.

[2] Сделайте SQLite для этого с запросом (удалите blobs с полностью N байтами в них):

 DELETE FROM blobs WHERE ROWID IN (SELECT ROWID FROM blobs WHERE (SELECT SUM(size) FROM blobs AS _ WHERE ts <= blobs.ts) <= N); 

Это быстрее, но все еще ужасно медленно: ~ 15 секунд. Похоже, он также имеет квадратичную сложность.

[3] Выберите строку вокруг места для удаления (используя средний размер blob для вычислений) и удалите с помощью простого WHERE :

 -- Find row after which to delete, let it's time stamp is T0: SELECT ts FROM cache ORDER BY ts LIMIT 1 OFFSET count; -- Delete DELETE FROM cache WHERE ts < T0; 

Это намного лучше, но занимает ~ 7 сек.

[4] Создайте новую таблицу, скопируйте ее, чтобы сохранить и отбросить ее. Обратите внимание, что я создаю индекс в новой таблице ПОСЛЕ того, как я скопировал все это:

  -- Insert only rows I want leave INSERT INTO temp(key, ts, size, data) SELECT key, ts, size, data FROM cache ORDER BY ts LIMIT count; -- Drop table and indices. DROP INDEX by_key; DROP INDEX by_ts; DROP TABLE cache; -- Rename temp table and create indices... 

Копирование занимает ~ 300 мс для 6 Мб для капли. Но DROP TABLE составляет около ~ 8 сек.

Обратите внимание, что во всех случаях я делаю VACUUM который занимает еще ~ 1 сек. Как я могу сделать это быстро? Почему DROP TABLE и удаление так медленно? Я думаю, что это может быть из-за индексов: когда я сбросил ключевой индекс перед DELETE он работал быстрее. Как быстро удалить SQLite?

Вы работаете с базой данных с «большими» данными, т. Е. С каждым блоком, использующим несколько страниц.

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

Проверяя все ваши варианты, я вижу разные формы поведения, а не только разные алгоритмы.

[1] Это не должно быть ужасно медленным, пока вы используете транзакцию. Вам нужно сразу две операции, запрос (чтобы получить размер блоба) и удалить.

[2] Это хороший подход. Как два запроса, так и удаление, все в одной команде, поэтому SQLite-движок будет оптимизирован.

[3] Это по-другому по сравнению со всеми ранее. То же, что и DELETE FROM cache WHERE ts < (SELECT ts FROM cache ORDER BY ts LIMIT 1 OFFSET count) . Запрос дешевле предыдущего, но я уверен, что количество удаленных строк намного меньше предыдущего! Дорогая часть запроса / удаления будет удалена! Оптимизация запросов важна, но при удалении все будет медленнее.

[4] Это очень плохой подход !!! Копирование всех ваших данных в новую таблицу – возможно, другую базу данных – будет ОЧЕНЬ дорогостоящим. Я получаю только одно преимущество от этого: вы можете копировать данные в новую базу данных и избегать VACUUM , поскольку новая база данных была построена с базы, и она чистая.

О VACUUM … Хуже тогда DELETEVACUUM . Вакуум не должен использоваться часто в базе данных. Я понимаю, что этот алгоритм должен «очистить» вашу базу данных, но очистка не должна быть частым действием – базы данных оптимизированы для выбора / вставки / удаления / обновления – чтобы не хранить все данные с минимальным размером.

Мой выбор будет использовать одиночную операцию DELETE ... IN (SELECT ...) соответствии с предопределенными критериями. VACUUM не будет использоваться, по крайней мере, не так часто. Одним хорошим выбором будет размер монитора db – когда этот размер превысит лимит, запустите предполагаемую дорогостоящую очистку для обрезки базы данных.

Наконец, при использовании нескольких команд никогда не забывайте использовать транзакции!

По-видимому, медленное – это не поиск удаляемых записей, а фактическое удаление.

Проверьте, установлен ли PRAGMA secure_delete по умолчанию в SQLite вашего Android. Вы должны просто отключить его, просто чтобы быть уверенным.

Вам не нужно запускать VACUUM ; SQLite автоматически восстанавливает освобожденные страницы. Вам нужен VACUUM только в том случае, если вы действительно знаете, что в будущем база данных больше не будет расти.

У вас есть два варианта улучшения производительности, особенно первый:

1) Используя транзакцию следующим образом:

 DbTransaction trans = conn.BeginTransaction(); // <------------------- try { Any code to delete the items } catch { trans.Rollback(); // <------------------- throw; // <------------------- } 

2) В противном случае, если элементы непрерывны, то

  • A) Получить идентификатор первого элемента;

  • Б) Получить общее количество элементов, подлежащих удалению

  • C) Используя команду:

    DELETE FROM blobs WHERE ID > fistId LIMIT count;

Удачи.

Intereting Posts
Запретить пользователю делать снимок экрана приложения Фрагмент воссоздается каждый раз после изменения ориентации, не может восстановить состояние Asynctask DoInBackground () не вызывается в Android Tablet Хранить AsyncTask во время вращения, но не удалять активность Установка приложения из Google Play на эмуляторе Приложение сбой безопасности с помощью SecurityException на Android M Выход инструмента Proguard retrace Устройство Genymotion не появляется на устройстве Chooser – Android Studio Может ли кто-нибудь дать пример кода для TabHost в Android? Как создать пользовательский виджет блокировки экрана (я просто хочу отобразить кнопку) Как добавить библиотеку в папку lib для Android-эмулятора Пользовательский вид Android возвращается в исходное положение при обновлении Android – найти сервер в сети Как получить доступ к значению edittext внутри фрагмента, который находится в viewpager Android – обнаружение двойного касания и триплет на вид