SQLite — размер базы после DELETE FROM

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

    Сам я являюсь RubyOnRails разработчиком, вследствие чего для небольших проектов стараюсь использовать стандартную конфигурацию, которая использует SQLite. Не вижу смысла тянуть в зависимость простого приложения тяжеловесные СУБД.

    Однажды мое внимание привлек размер базы данных, таблицы которой несколько минут назад были очищены. Интересный факт: при удалении объекта из базы данных от него в файле базы остается свободное место. Быть может, для опытных специалистов этот факт очевиден, но объемы результатов поиска по данному вопросу не превзошли мои ожидания.

    Рассмотрим на примере, с чем мы имеем дело.

    Создадим базу данных, а в ней — таблицу с простой структурой: id(INTEGER, AI), phone(TEXT). Далее заполним ее произвольными значениями:

    image

    Такая база данных занимает 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
    Share post

    Comments 13

      +50
      Отличная статья, которая заменяет целое одно предложение из документации (!): при удалении данных из БД SQLite место не освобождается. Для освобождения места используйте команду VACUUM.
        0
        Не спорю. Суть можно этим предложением высказать.
        0
        А что это за такой красивый менеджер на скриншоте?
        Пользовался парой утилит для SQLite — всё очень убогое по сравнению с тулзами, заточенными под другие БД
          +4
          Скорее всего Navicat.
            +3
            Это точно Navicat, а вот рекомендация Wizard999 ниже в разы лучше. Без проблем работает как с UTF-8 (у Навиката тут нормуль все), так и с ANSI (вот тут Навикат проигрывает), есть бесплатная редакция, умеет использовать несколько SQLite-библиотек (переключением между ними + embedded версия), да и в целом на уровне. Меня на SQLite Export Personal долго пытались посадить — упирался, он визуально неприятен, зато внутри просто конфетка. Navicat тоже хорош, но мелочи в функционале портят впечатление.
            +6
            Рекомендую SQLite Expert: www.sqliteexpert.com/
            +2
            А если вы телефоны заполняли случайными значениями, зачем вы их на скриншоте замазали?
              0
              Изначально это было в проекте для смс-рассылки, номера реальные.
              +2
              Кстати, в линукс системах программы часто используют Sqlite, как хранилище своих данных, и можно ускорить программы, оптимизируя их файлы sqlite.
              Скрипт, который выполнит нужное и порадует насколько всё уменьшилось.

              #!/bin/bash
              find ~/ -size +20k -type f -print0 | \
              while read -d '' FILE; do
              abs_file_name=$(readlink -f "$FILE")
              headfile=`head -c 15 «file_name»`;
              if [ "$headfile" = «SQLite format 3» ]; then
              file_size_do=`du -b "$abs_file_name"|cut -f1`;
              sqlite3 "$abs_file_name" «VACUUM; REINDEX;» > /dev/null 2>&1
              file_size_posle=`du -b "$abs_file_name"|cut -f1`;
              echo "$abs_file_name";
              echo «Размер ДО $file_size_do»;
              echo «Размер ПОСЛЕ $file_size_posle»;
              echo -n «Процент „
              echo “scale=2; ($file_size_posle/$file_size_do)*100»|bc -l
              fi
              done
              sleep 2
              exit 0

              Тотальная чистка и реиндексация Sqlite — vasilisc.com/speedup_ubuntu#clear_reindex_sqlite
                0
                file_size_posle — это пять
                +1
                А еще есть бесплатная sqlite studio
                  0
                  Добавлю ещё свои 5 копеек: SQLite Manager — неплохое кроссплатформенное решение в виде дополнения для Firefox. Вполне удобен в работе.
                  0
                  Дак вот кто эти бродвеи с телефонами делает!

                  Only users with full accounts can post comments. Log in, please.