Всем доброго времени суток. Хотелось бы поделиться интересным, на мой взгляд, опытом работы с SQLite.
Сам я являюсь RubyOnRails разработчиком, вследствие чего для небольших проектов стараюсь использовать стандартную конфигурацию, которая использует SQLite. Не вижу смысла тянуть в зависимость простого приложения тяжеловесные СУБД.
Однажды мое внимание привлек размер базы данных, таблицы которой несколько минут назад были очищены. Интересный факт: при удалении объекта из базы данных от него в файле базы остается свободное место. Быть может, для опытных специалистов этот факт очевиден, но объемы результатов поиска по данному вопросу не превзошли мои ожидания.
Рассмотрим на примере, с чем мы имеем дело.
Создадим базу данных, а в ней — таблицу с простой структурой: id(INTEGER, AI), phone(TEXT). Далее заполним ее произвольными значениями:
Такая база данных занимает 850 944 байта. Количество записей — 41 922.
Выполним «DELETE FROM mytable»:
Размер файла базы данных — 850 944 байта. Количество записей — 0.
После импорта тех же 41 922 записей:
Размер файла базы данных — 871 424 байта. Количество записей — 41 922.
В последнем случае рост объема связан с типами, используемыми для хранения автоинкрементного поля id. При значениях поля до 65 536 используется, по всей видимости, unsigned short. У нас же после «delete from» и нового импорта ai индекс начался с 41 923 и закончился 82 946, следовательно, для хранения значений поля id уже используется unsigned int или unsigned long — не считал, не знаю точно. А может и вообще используются не unsigned типы — не в этом суть.
Ключевой вопрос: почему после удаления данных из таблицы размер файла БД остался прежним?
Сначала я подумал, что данные вообще не удаляются, а просто помечаются удаленными и далее при записи перезаписываются новыми. Однако, это оказалось не так. Заглянув в файл после удаления данных, предыдущих значений я не обнаружил. Это означает, что при удалении данных удаление на самом деле происходит, только память не освобождается, а забивается значениями ASCII 00.
Что с этим можно сделать? Есть специальная команда — VACUUM.
Выполним снова «DELETE FROM mytable». Таблица очищена, но ее объем по-прежнему 851 КБ — все как и прежде.
Выполним команду VACUUM: размер файла уменьшился до 3 КБ.
При выполнении этой операции содержимое нашей базы было скопировано во временный файл, которым был перезаписан оригинальный файл базы. Технически перезапись оригинального файла происходит, используя метод «Write-Ahead Logging». Подробнее — тут. Это означает, что для выполнении операции VACUUM на жестком диске необходимо иметь свободного пространства — не менее удвоенного объема исходной базы.
Обратите внимание на следующую особенность — команда vacuum может менять значения ROWID (подробнее — тут) для таблиц, не имеющих primary key.
В общем, использование операции VACUUM уместно, если не включен режим auto_vacuum и нам необходимо удалить лишнее «пустое» пространство, тем самым уменьшив размер файла базы данных.
Подробнее о режиме auto_vacuum можно узнать тут. Режим auto_vacuum не фрагментирует базу данных. В этом режиме, если после добавления данных после значений самих данных остаются nul-байты, они удаляются.
Спасибо за внимание. Надеюсь, материал может быть кому-нибудь полезным.
Источники:
http://sqlite.org/lang_vacuum.html
http://sqlite.org/wal.html
http://sqlite.org/pragma.html#pragma_auto_vacuum
http://sqlite.org/lang_createtable.html#rowid
Сам я являюсь RubyOnRails разработчиком, вследствие чего для небольших проектов стараюсь использовать стандартную конфигурацию, которая использует SQLite. Не вижу смысла тянуть в зависимость простого приложения тяжеловесные СУБД.
Однажды мое внимание привлек размер базы данных, таблицы которой несколько минут назад были очищены. Интересный факт: при удалении объекта из базы данных от него в файле базы остается свободное место. Быть может, для опытных специалистов этот факт очевиден, но объемы результатов поиска по данному вопросу не превзошли мои ожидания.
Рассмотрим на примере, с чем мы имеем дело.
Создадим базу данных, а в ней — таблицу с простой структурой: id(INTEGER, AI), phone(TEXT). Далее заполним ее произвольными значениями:
Такая база данных занимает 850 944 байта. Количество записей — 41 922.
Выполним «DELETE FROM mytable»:
Размер файла базы данных — 850 944 байта. Количество записей — 0.
После импорта тех же 41 922 записей:
Размер файла базы данных — 871 424 байта. Количество записей — 41 922.
В последнем случае рост объема связан с типами, используемыми для хранения автоинкрементного поля id. При значениях поля до 65 536 используется, по всей видимости, unsigned short. У нас же после «delete from» и нового импорта ai индекс начался с 41 923 и закончился 82 946, следовательно, для хранения значений поля id уже используется unsigned int или unsigned long — не считал, не знаю точно. А может и вообще используются не unsigned типы — не в этом суть.
Ключевой вопрос: почему после удаления данных из таблицы размер файла БД остался прежним?
Сначала я подумал, что данные вообще не удаляются, а просто помечаются удаленными и далее при записи перезаписываются новыми. Однако, это оказалось не так. Заглянув в файл после удаления данных, предыдущих значений я не обнаружил. Это означает, что при удалении данных удаление на самом деле происходит, только память не освобождается, а забивается значениями ASCII 00.
Что с этим можно сделать? Есть специальная команда — VACUUM.
Выполним снова «DELETE FROM mytable». Таблица очищена, но ее объем по-прежнему 851 КБ — все как и прежде.
Выполним команду VACUUM: размер файла уменьшился до 3 КБ.
При выполнении этой операции содержимое нашей базы было скопировано во временный файл, которым был перезаписан оригинальный файл базы. Технически перезапись оригинального файла происходит, используя метод «Write-Ahead Logging». Подробнее — тут. Это означает, что для выполнении операции VACUUM на жестком диске необходимо иметь свободного пространства — не менее удвоенного объема исходной базы.
Обратите внимание на следующую особенность — команда vacuum может менять значения ROWID (подробнее — тут) для таблиц, не имеющих primary key.
В общем, использование операции VACUUM уместно, если не включен режим auto_vacuum и нам необходимо удалить лишнее «пустое» пространство, тем самым уменьшив размер файла базы данных.
Подробнее о режиме auto_vacuum можно узнать тут. Режим auto_vacuum не фрагментирует базу данных. В этом режиме, если после добавления данных после значений самих данных остаются nul-байты, они удаляются.
Спасибо за внимание. Надеюсь, материал может быть кому-нибудь полезным.
Источники:
http://sqlite.org/lang_vacuum.html
http://sqlite.org/wal.html
http://sqlite.org/pragma.html#pragma_auto_vacuum
http://sqlite.org/lang_createtable.html#rowid
