Рекомендации по использованию SQLite на мобильных устройствах

    В буржунете есть интересный документ по оптимизации SQLite на платформе BlackBerry. Однако, идеи, которые в нем содержатся, вообще говоря, относятся к любой мобильной платформе. Мы решили развить этот список и создать эдакий эталонный текст в рунете с набором полезных, вводных советов для программирования SQLite под мобильными устройствами. Принимаем ваши предложения в комментариях.



    Всегда используйте кодировку UTF-8

    Это оптимальный вариант по быстродействию и размеру занимаемой памяти (внутри себя SQLite все запросы переводит в UTF-8, поскольку имеет встроенный парсер только этой кодировки).

    Храните в базе как можно меньше данных

    Этот совет не от капитана Очевидности. Не используйте SQLite просто потому, что это «модно». Продумайте альтернативы: XML, например. За использование SQLite нужно платить тратой ресурсов.

    Всегда явно управляйте транзакциями

    Иначе на каждую команду SQL будет создаваться и фиксироваться отдельная, неявная транзакция. Группируйте изменения данных.

    Тщательно продумывайте создание индексов

    Индексы ускоряют выборку, но замедляют модификацию данных. Иногда полезно использовать covering индексы, когда в определение индекса включаются поля с данными:

    CREATE INDEX idx ON tab(key1, key2,  data1, data2, data3);
    

    Теперь, если выбирать (по условию на key1 и key2) данные столбцов data1, data2, data2, SQLite может взять их значения из самого индекса (нет необходимости дополнительного чтения данных из таблицы). Но такой индекс занимает больше места. Поэтому, следующий совет:

    Лучший способ проверить что-то: тестировать на конкретном устройстве

    И, разумеется, не на пустой базе. Заполните ее случайными данными, если нет подходящих. Иначе быстродействие в реальном сценарии вас неприятно удивит.

    Продумайте как хранить BLOBы

    На официальном сайте приводится тест, который показывает, что при использовании BLOBов следует:
    — поставить размер страницы побольше (8192 или 16384);
    — большие BLOBы хранить в отдельных файлах.
    Если вы решили хранить BLOBы в базе, заведите отдельную таблицу под них:

    CREATE blobs(blobid INTEGER PRIMARY KEY, data BLOB)
    

    и в таблицах с данными поставьте поле со ссылкой на запись в этой таблице. Это совет от самих разработчиков SQLite.

    Используйте временные таблицы

    Если при создании таблицы, вы указали «TEMP»

    CREATE TEMP TABLE tab(...)
    

    то созданная таблица будет уничтожена автоматически при закрытии базы данных.

    Используйте параметризованные запросы

    То есть передавайте значения в SQL запросы не в виде литералов внутри SQL, а привязывая их к параметрам. Это одновременно обезопасит ваш код от атак SQL injection и повысит быстродействие.

    Добавьте AUTOINCREMENT в определение PRIMARY KEY

    Столбец

    id INTEGER PRIMARY KEY 
    

    является синонимом поля ROWID (уникального идентификатора записи). Хитрость в том, что SQLite не гарантирует, что новые строки получат новые ключи, если ранее записи удалялись из таблицы. Если вам требуется «настоящая уникальность» идентификаторов добавьте к определению AUTOINCREMENT:

    id INTEGER PRIMARY KEY AUTOINCREMENT
    

    Теперь при любых обстоятельствах новая строка получит уникальное значение id (и rowid) (ну или вы исчерпаете 8-байтный счетчик).

    Используйте внешние ключи (FOREIGN KEY) только если это действительно нужно

    В SQLite имеется довольно развитый механизм поддержки внешних ключей. По умолчанию, он отключен (для совместимости). С одной стороны, внешние ключи гарантируют целостность. С другой — требуют ресурсов. Если вы все-таки решите их использовать, то…

    По каждому полю внешнего ключа создайте индекс

    Допустим, есть пара таблиц:

    CREATE TABLE master(mid INTEGER PRIMARY KEY ...);
    CREATE TABLE detail(fk_master REFERENCES master(mid) ON DELETE CASCADE ...);
    

    При удалении записи из master таблицы, SQLite должен проверить — не ссылается ли на нее какая-то запись в таблице detail и делает дополнительную выборку:

    SELECT rowid FROM detail WHERE fk_master = ?
    

    Как правило, разработчики забывают создать индекс по FK ключу и эта выборка работает очень долго.

    Удаление данных из базы не уменьшает размер ее файла

    Чтобы сжать файл базы данных, выполните

    VACUUM
    

    Эта операция требует времени.

    Ну и самый главный совет.

    Тщательно продумайте схему БД

    Обычно вначале данные максимально нормализуют, а затем выполняют денормализацию для оптимизации. Например, если у пользователя часто требуют название его отдела, можно это название продублировать в таблице пользователей отдельным полем. Задумайтесь, какие данные меняются в вашей БД наиболее интенсивно, возможно, их следует выделить в отдельную таблицу.

    Дополнено

    Как выполнить сразу несколько операторов SQL?

    Объедините их в одну строку через ";".

    Как быстро добавить много записей (bulk insert)?

    Перед вставкой большого числа записей выполните

     PRAGMA synchronous = OFF; 
     PRAGMA journal_mode = OFF;
     BEGIN;
    

    Вставляйте записи. Наибольшей производительности можно достигнуть, откомпилировав (prepare) оператор вставки с параметрами и многократно выполнив его с требуемыми значениями. После вставки восстановите настройки (приводятся настройки по умолчанию):

     COMMIT;
     PRAGMA synchronous = NORMAL; 
     PRAGMA journal_mode = DELETE;
    

    Создавать индексы по таблице лучше после вставки, а не до. Учтите, что при отключенном журнале база может оказаться испорченной, если приложение «вылетит» в процессе вставки.

    PS. Используй современный менеджер для администрирования базы данных SQLite.
    AdBlock has stolen the banner, but banners are not teeth — they will be back

    More
    Ads

    Comments 17

      +1
      Не используйте SQLite просто потому, что это «модно». Продумайте альтернативы: XML, например. За использование SQLite нужно платить тратой ресурсов.
      Хорошо, тогда вопрос: а когда следует использовать SQLite?

      Я во многих проектах использую базу:
      1) В играх храню информацию о всех покупках внутриигровых.
      2) В бизнес-приложениях храню кэши данных, чтобы с сети не тягать постоянно.
      Лучше так не делать? В xml под Android я вообще почти ничего храню, к примеру; только небольшие данные через SharedPreferences.
        0
        Следует или нет вопрос тонкий, но выше обозначенное вполне можно реализовать и на файлах.
        1) Использую зашифрованный JSON в файле,
        2) Завёл отдельную директорию, в которой кэширую в основном картинки. Имена файлов — хэш от URL.
        +1
        Этот совет предостерегает от использования SQLite «на все случаи жизни». Ваши сценарии как раз подходят хорошо.

        Контрпример: какой-то небольшой справочник. Его проще положить и использовать в виде XML.
          +1
          А чем проще-то?
            0
            Ну, например, справочник уже есть и он в XML формате. Стоит вопрос — переводить это все в SQLite или оставить как есть? На платформе Андроид получить данные из XML файла довольно просто.

              0
              Если он уже есть, то переводить нет смысла. А если система делается с нуля, то значительно удобнее пользоваться одной универсальной технологией, которая подходит для любого хранения данных и вообще не зависит от платформы.
          +1
          >Продумайте альтернативы: XML, например. За использование SQLite нужно платить тратой ресурсов.

          Если разделить индексный файл и блоб-файл, то в SQLite можно хранить все что угодно, так же как и в файловой системе, практически без оверхеда.
            +1
            Что значит «разделить индексный файл и блоб-файл»? Две базы SQLite в двух разных файлах, и в одной из них блобы только?
              +2
              Да. В одной делаем выборки, поиск и что угодно, а другой запрашиваем данные уже по конкретным ID, без поиска. У меня так гигабайтная база крутится с картинками. Вполне шустро.
            +1
            В принципе, лучше перед советами почитать документацию сайте SQLite, там все доступно описано, полезные вещи для себя можно приметить.
            А потом обязательно почитать, какой используется SQLite на вашей платформе, какие есть дополнительные функции, что поддерживает, а что отключено. Если что, всегда можно посмотреть план запроса.

              0
              a) не все читают английский;
              b) документации очень много;
              c) некоторые соображения трудно найти даже в документации, нужно много «шерстить»;
                0
                Я пишу с той точки зрения, что неплохо представлять, что и как работает, перед использованием в своем коде. Необязательно сразу же копать на всю глубину.
              +1
              Позвольте дополнить ваш пост ссылками:
                0
                Вот, то чего не хватает статье! Спасибо за подборку.
                И вообще чуть ли не каждый подзаголовок статьи можно закончить ", Ваш КО" ибо какие-то прописные истины, кроме м.б. раздела "Дополнено". Про разработку собственно ни слова…
                +2
                Как быстро добавить много записей (bulk insert)?

                Обернуть кучу инсертов в транзакцию.
                  0
                  А никто не подскажет, как правильно организовать работу с серверной базой? То есть я предполагаю, что на устройстве должна быть практически полная копия базы на сервере (чтобы не гонять туда-сюда информацию постоянно). С другой стороны возникает куча всяких проблем с синхронизацией тогда. Вроде как проще постоянно держать всё только на сервере и брать только когда надо и что надо.

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