ОПТИМИЗАЦИЯ В РАБОТЕ С БАЗОЙ ДАННЫХ SQLITE

SQLite — компактная встраиваемая реляционная база данных. Она способна быть невероятно быстрой, возможно, наиболее быстрой базой данных. Однако и при работе с SQLite можно получить скорость ниже, чем при работе с MySQL или PostGres. Это лишь означает, что пользователь не обращается к ключевым особенностям и возможностям базы данных.

Первым делом следует принять тот факт, что при работе с SQLite (и большинством других баз данных) самая медленная операция - это работа с диском. Она много медленнее работы с оперативной памятью. Поэтому ключ к оптимизации SQLite лежит в уменьшении количества взаимодействия с диском. Для этого необходимо понимать, когда и зачем база данных обращается к информации, хранящейся на диске. Так как большинство ОС кэшируют открытые файлы с диска в оперативной памяти, при аккуратном и продуманном использовании SQLite мы можем получить скорость, сравнимую со скоростью чисто-виртуальной базы данных, т.е. базы данных, работающей исключительно с оперативной памятью.

Ключ к эффективности лежит в простоте. Наиболее простые способы оптимизации скорости SQLite занимают одну строчку или требуют простой замены исходников. В работе будут рассмотрены следующие способы, в порядке эффективности:

1. Использование команд BEGIN TRANSACTION и END TRANSACTION
2. Использование индексов
3. Установка PRAGMA cache_size
4. Команда PRAGMA synchronous=OFF
5. Дефрагментация базы данных
6. Замена библиотеки, управляющей памятью
7. Команда PRAGMA count_changes=OFF

1. Использование команд BEGIN TRANSACTION и END TRANSACTION

SQLite начинает новую транзакцию для каждого SQL-выражения при нахождении вне выполняющейся транзакции. Открытие транзакции - очень “дорогая” операция, так как она требует открытия, записи, и закрытия файла журнала для каждого выражения. Этого можно избежать, заключая SQL-выражения в блоки: BEGIN TRANSACTION ... END TRANSACTION.

В качестве синонима END TRANSACTION можно использовать команду COMMIT.

Следует отметить, что SQLite блокирует запись файла базы данных, когда открыта транзакция. Поэтому попытка доступа к базе данных из разных потоков может вызвать как серьёзное падение производительности, так и критические ошибки в работе базы данных в целом.

2. Использование индексов

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

Индексирование реализуется созданием отдельной таблицы индексов, которая устанавливает соответствие ключей, созданных из столбца(-ов), и строки из таблицы индексов. SQLite автоматически создает индекс для каждого UNIQUE столбца из выражения CREATE TABLE.

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

Либо, когда ваши запросы, в любом случае, требуют сканирования всей таблицы (например, при запросе LIKE). В таком случае толку от индексов не будет.

3. Установка PRAGMA cache_size

База данных SQLite представлена в виде Би-дерева, разбитого на страницы памяти, размером в 1 Кб. При работе с базой данных SQLite загружает эти страницы в память вместе со структурами, необходимыми для их отслеживания. Если запрос включает в себя только кэшированные страницы, он выполняется куда быстрее, т.к. нет необходимости в доступе к диску.

Параметр cache_size определяет максимальное количество страниц Би-дерева, которое SQLite может хранить в памяти одновременно. Его стандартное значение равно 2000. Оно может отличаться от значения параметра default_cache_size, хранящегося в файле базы данных и считываемого SQLite при её загрузке.

Стандартный объем дискового пространства, занимаемый одной страницей базы данных – это 1 Кб. Так что современные системы обычно могут единовременно поддерживать довольно большое число страниц в оперативной памяти. Страничный кэш не рассчитывается досрочно, так что проблем при установке большего значения, чем необходимо, не возникнет. К тому же ОС может более эффективно выполнить операцию постраничного распределения памяти, чем человек.

Так как cache_size является динамическим параметром, его значение можно увеличивать при выполнении определенных наборов запросов, а затем возвращать к более низкому значению.

4. Команда PRAGMA synchronous=OFF

Параметр synchronous контролирует то, будут ли библиотеки ожидать полной записи данных на диск перед возобновлением работы. Значение параметра может отличаться от значения default_synchronous, загружаемого из базы данных. При обычном использовании библиотеки могут простаивать долгое время в ожидании файловой системы. В таком случае установка "PRAGMA synchronous=OFF” может принести ощутимый прирост в скорости.

5. Дефрагментация базы данных

Когда информация удаляют из базы данных, а страница Би-дерева очищается, на самом деле, она не удаляется из базы данных, а всего лишь помечается как “свободная” для будущего переиспользования. Когда требуется свободная страница памяти, SQLite использует одну из помеченных страниц при их наличии, вместо того чтобы увеличивать размер базы данных. Однако из-за этого база данных фрагментируется, т.е. размер файла превышает объем, необходимый для хранения данных, а сами данные становятся неупорядоченными.

Другим побочным эффектом динамических баз данных является фрагментирование таблиц. Страницы памяти, содержащие данные отдельной таблицы, могут стать разбросанными по всему файлу базы данных, что потребует большего времени при загрузке таблицы. Такой процесс может сильно замедлить работу базы данных из-за особенностей работы файловых систем. Дефрагментация решает обе из этих проблем.

Самым простым способом удаления пустых страниц является использование команды SQLite – VACUUM. Она может быть выполнена из библиотеки SQLite или утилиты sqlite.

Другим способом может стать создание дампа базы данных и последующее пересоздание всей базы данных. Этот способ применим только “извне”.

Количество свободных страниц памяти в БД может быть определено при помощи вызова функции sqlite_count_free_pages.

6. Замена библиотеки, управляющей памятью

Распределение памяти может быть посредственно реализовано в некоторых операционных системах, например, Windows. Замена функций malloc, realloc, и free на таких системах может оказать большое влияние на производительность.

Одним из широко используемых источников с открытым кодом является Hans Boehm’s Memory Pool System используемая во freshmeat и Ravenbrook Limited.

При необходимости использования легковесной библиотеки с открытой реализацией, можно использовать библиотеку Doug Lea - dlmalloc.

В обеих есть переопределение функций malloc, realloc и free, так что они сходу могут быть использованы в качестве замены для стандартных библиотек управления памятью.

7. Команда PRAGMA count_changes=OFF

Когда параметр count_changes включен, функция подсчета вызывается для каждой команды DELETE, INSERT или UPDATE. В качестве аргумента передается количество строк, которые были изменены. Если в ходе работы отсутствует необходимость использования данного функционала, то, отключив его, можно получить небольшой прирост в скорости.

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

Список литературы:

1. Lyon J. SQLite Optimization FAQ: сайт – URL: http://codificar.com.br/blog/sqlite-optimization-faq/ (дата обращения: 05.04.2017)
2. Newman C. SQLite, 2004. – С. 68-74
3. Owens M. The Definitive Guide to SQLite, 2006. – C. 381-392
4. The SQLite Query Planner [Электронный ресурс]. – Режим доступа: https://sqlite.org/optoverview.html (дата обращения: 25.03.2017)