12 способов оптимизации баз данных SQLite – попробуйте прямо сейчас!

Раскрытие информации: Ваша поддержка помогает поддерживать работу сайта! Мы зарабатываем реферальную плату за некоторые услуги, которые мы рекомендуем на этой странице.


SQLite – это основанная на SQL система управления реляционными базами данных (RDBMS), реализованная в виде встраиваемой библиотеки. Он хранит базы данных в виде отдельных файлов, а не полагается на модель клиент-сервер.

SQLite обычно используется тремя способами:

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

Необходимость оптимизации

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

Но когда SQLite используется «в производстве», как и во двух других случаях, производительность имеет значение. Принятие нескольких простых методов может реально повлиять на скорость обновления базы данных и запросов.

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

Поскольку SQLite является очень популярным хранилищем данных в приложениях для Android, мы также включили некоторые конкретные советы по оптимизации производительности SQLite на Android..

Использовать транзакцию

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

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

Допустим, вы собрали кучу данных в какую-то итеративную структуру, такую ​​как список или массив. У вас может возникнуть соблазн перебирать ваши данные и вставлять их в базу данных SQLite на каждой итерации цикла..

/ ************************************************* ***
Получить имена и фамилии из файла с разделителями табуляции.
Затем вставьте их в базу данных SQLlite.
************************************************** ** /

/ * не забудьте определить их в реальной жизни…
#define DATABASE = // имя базы данных //
#define FILE_OF_NAMES = // путь к файлу //
#define CREATE_TABLE = // SQL-оператор для создания таблицы имен //
* /

sqlite3_open (БАЗ, &дБ);
sqlite3_exec (дБ, CREATE_TABLE, NULL, NULL, &sErrMsg);

pFile = fopen (FILE_OF_NAMES,"р");
while (! feof (pFile)) {

fgets (sInputBuf, BUFFER_SIZE, pFile);

sFirstName = strtok (sInputBuf, "T");
sLastName = strtok (NULL, "T");

Sprintf (sSQL, "INSERT INTO Имена VALUES (NULL, “% s”, “% s”,)", sFirstName, sLastName, s);
sqlite3_exec (дБ, sSQL, NULL, NULL, &sErrMsg);

п ++;
}
fclose (pFile);
sqlite3_close (дБ);

Это плохая идея. Это распыляет каждую вставку в одну транзакцию – каждая со своими издержками. Ничего страшного, если у вас всего пара вставок, но даже в быстродействующем C-коде это может замедлить работу до 100 вставок в секунду. Если вы используете SQLite в качестве формата файла приложения, это может означать, что пользователи испытывают задержку в несколько секунд при каждом сохранении сложного документа или проекта..

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

/ * перед циклом – начать транзакцию * /
sqlite3_exec (дБ, "НАЧАЛО СДЕЛКИ", НОЛЬ НОЛЬ, &sErrMsg);

pFile = fopen (FILE_OF_NAMES,"р");
while (! feof (pFile)) {
.
.
.
}

fclose (pFile);

/ * после цикла – завершить транзакцию * /
sqlite3_exec (дБ, "КОНЕЦ СДЕЛКИ", НОЛЬ НОЛЬ, &sErrMsg);

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

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

/ * после открытия соединения дб,
до начала транзакции * /
sqlite3_exec (дБ, "PRAGMA cache_size = 10000", НОЛЬ НОЛЬ, &sErrMsg);

Транзакции в Android

Встроенный в Android API SQLite делает использование транзакций еще проще.

// начать транзакцию
db.beginTransaction ();

// завершить транзакцию
db.endTransaction ();

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

пытаться {
db.beginTransaction ();

/ * Делать вещи в цикле. * /

db.setTransactionSuccessful (); // Это фиксирует транзакцию, если не было исключений

} catch (исключение e) {
Log.w ("Исключение:", д);
} наконец-то {
db.endTransaction ();
}

Подготовить и связать

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

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

/ * до начала транзакции * /
Sprintf (sSQL, "INSERT INTO Names VALUES (NULL, @FirstName, @LastName)");
sqlite3_prepare_v2 (дБ, sSQL, BUFFER_SIZE, &STMT, &хвост);

sqlite3_exec (дБ, "НАЧАЛО СДЕЛКИ", НОЛЬ НОЛЬ, &sErrMsg);

pFile = fopen (FILE_OF_NAMES,"р");
while (! feof (pFile)) {

fgets (sInputBuf, BUFFER_SIZE, pFile);

sFirstName = strtok (sInputBuf, "T");
sLastName = strtok (NULL, "T");

sqlite3_bind_text (stmt, 1, sFirstName, -1, SQLITE_STATIC);
sqlite3_bind_text (stmt, 2, sLastName, -1, SQLITE_STATIC);

sqlite3_step (STMT);

sqlite3_clear_bindings (STMT);
sqlite3_reset (STMT);

п ++;
}
fclose (pFile);
sqlite3_exec (дБ, "КОНЕЦ СДЕЛКИ", НОЛЬ НОЛЬ, &sErrMsg);
sqlite3_close (дБ);

Эту стратегию можно использовать и вне циклов. Если у вас есть запрос внутри функции, вы можете подготовить его один раз, а затем связывать его каждый раз, когда он используется.

Подготовленные заявления в Android

Android SQLite API предоставляет класс SQLiteStatement для этого легко.

// написать запрос, с? для значений для вставки
Строка sql = "INSERT INTO Имена VALUES (?,?)";

// скомпилируем оператор
SQLiteStatement оператор = db.compileStatement (sql);

/ ** просмотр записей ** /

/ ** получить имена из файла и присвоить firstName и lastName ** /

// связать
Statement.bindString (1, firstName);
Statement.bindString (2, lastName);

// exec
Long row_id = Statement.executeInsert ();

Не синхронизировать с диском после каждой вставки

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

sqlite3_exec (дБ, "ПРАГМА синхронная = ВЫКЛ", НОЛЬ НОЛЬ, &sErrMsg);

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

Хранить журнал отката в памяти

Если вы уже опасно живете с PRAGMA синхронным = ВЫКЛ и пытаетесь выжать все лишние миллисекунды, вы можете также сохранить журнал отката в памяти вместо того, чтобы сохранить его на диск. В сочетании с предыдущей оптимизацией это немного рискованно.

sqlite3_exec (дБ, "PRAGMA journal_mode = MEMORY", НОЛЬ НОЛЬ, &sErrMsg);

Вы также можете установить для journal_mode значение OFF, если вы пытаетесь выиграть соревнование по скорости или что-то в этом роде. (Это не рекомендуется для использования в реальной жизни.)

Предупреждение о режиме журнала для Android

Режим журнала SQLite управляется с помощью Android-метода enableWriteAheadLogging (). Итак, в документации по выполнению сырых команд SQL сказано:

Не устанавливайте journal_mode, используя "PRAGMA journal_mode" заявление, если ваше приложение использует enableWriteAheadLogging ().

Только индекс, когда вам это действительно нужно

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

Поэтому, если пользователи никогда не будут искать содержимое таблицы по определенному столбцу, не индексируйте ее. Кроме того, если пользователи могут выполнять поиск по определенному столбцу нечасто, не индексируйте его. Даже если они, вероятно, будут часто выполнять поиск, вам все равно нужно подумать о том, будет ли таблица записываться или выполняться поиск чаще. Если он будет записываться чаще, чем поиск, или если скорости записи особенно важны, не индексируйте его.

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

Индекс после массовой вставки

После того как вы создадите индекс для таблицы, каждая последующая вставка займет время для индексации нового содержимого. Если ваша таблица будет инициализирована большой вставкой объемных данных (возможно, при первом сохранении нового проекта или документа или при импорте данных для нового пользователя), вы можете ускорить эту первую большую вставку, ожидая создания индекса пока после вставки.

Другие настройки PRAGMA

Существует ряд параметров PRAGMA, которые могут помочь повысить производительность SQLite..

Размер кэша

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

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

sqlite3_exec (дБ, "PRAGMA cache_size = 100000", НОЛЬ НОЛЬ, &sErrMsg);

Хранение временных таблиц

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

sqlite3_exec (дБ, "PRAGMA temp_store = ПАМЯТЬ", НОЛЬ НОЛЬ, &sErrMsg);

Настройки Android и Pragma

Вы можете использовать метод execSQL () для выполнения необработанного SQL в вашей базе данных SQL. Это самый прямой способ изменения любых настроек PRAGMA. Однако некоторые из них (например, journal_mode, упомянутый выше) управляются другими классами или помощниками в API..

Быстрые Запросы – Фильтруйте Больше Скоро

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

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

Регистр без учета регистра для LIKE

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

СОЗДАТЬ ИНДЕКС sLastName НА ИМЕНАХ (КЛЮЧЕВОЙ КОЛЛЕКТИВНЫЙ NOCASE);

Используйте последнюю версию, если это возможно

Каждый основной выпуск SQLite содержит улучшения производительности. Некоторые выпуски резко увеличили скорость. Поэтому, если вы используете младшую версию, которой несколько лет (или, что еще хуже, все еще используете v2), самый простой путь к более быстрому выполнению – это просто обновление.

Не создавайте новую базу данных

Это большое изменение в мышлении людей из других систем RDBMS.

Рассмотрим случай использования SQLite в качестве формата файла приложения. Каждый раз, когда вы впервые сохраняете новый проект (файл) в приложении, необходим новый экземпляр базы данных..

Вы можете создать новую базу данных и выполнить ряд операторов SQL для добавления соответствующих таблиц и индексов. Это то, что вы хотели бы сделать, если бы вы создавали развертываемое приложение с (например) PostgreSQL – вы бы написали код для настройки базы данных и запустили ее при установке.

Но есть более быстрый способ.

Поскольку база данных SQLite представляет собой отдельный файл, клонировать базу данных относительно тривиально – это просто дублирование файла. Это означает, что обычно нет необходимости создавать новую базу данных, а затем выполнять все необходимые операторы SQL. Обычно вы можете просто сделать копию.

На Android вы можете использовать SQLite Asset Helper для управления базами данных как активами при выполнении этого..

Подумайте о денормализации

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

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

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

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

  • Прикладная логика становится ответственной за поддержание целостности данных (то есть правдивости или точности).
  • База данных должна быть больше для хранения того же объема информации.

Денормализация в SQLite особенно хороша

Все эти проблемы и недостатки присутствуют при работе с любой системой СУБД, а не только с SQLite. Однако в SQLite есть некоторые потенциально смягчающие факторы, которые делают денормализацию данных менее проблематичной и более полезной.

  • Как правило, приложение SQLite будет иметь менее сложную модель данных (схему), чем очень большое приложение, которому требуется сервер базы данных. Это делает сложность приложения, необходимую для поддержки денормализованных данных, менее обременительной..
  • Наборы данных, поддерживаемые базами данных SQLite, обычно меньше, чем хранящиеся в других системах баз данных. Это означает, что увеличение размера от дублированных данных менее проблематично. Кроме того, в масштабе большинства приложений SQLite (локальное хранилище файлов) стоимость дополнительного размера незначительна.
  • В отличие от больших серверов баз данных (особенно тех, которые обслуживаются организациями), маловероятно, что второе приложение попытается подключиться к файлам базы данных SQLite, созданным вашим приложением. Следовательно, вам не нужно защищаться от случайного повреждения данных и проблем с динамикой работы команды..
  • Точно так же, поскольку SQLite обычно используется как встроенная база данных, часто существует тесная связь между приложением и структурой базы данных. Это означает, что недостатки обработки целостности данных внутри приложения, как правило, ниже, чем они были бы, когда база данных и приложение слабо связаны физически, но в действительности сильно взаимозависимы..
  • Наконец, некоторые из улучшений производительности для поддержания нормализации, доступных в других системах баз данных, таких как материализованные представления, недоступны в SQLite..

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

Резюме

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

Дальнейшее чтение и ресурсы

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

  • Ресурсы SQL: наш общий ресурс SQL, который важен для всех разработчиков реляционных баз данных..
  • MySQL Введение и Ресурсы: другая очень популярная система базы данных.
  • Введение и ресурсы PostgreSQL: популярная система баз данных сама по себе, SQLite частично основана на ней.

Ultimate Руководство по веб-хостингу

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

Ultimate Руководство по веб-хостингу
Ultimate Руководство по веб-хостингу

Jeffrey Wilson Administrator
Sorry! The Author has not filled his profile.
follow me
    Like this post? Please share to your friends:
    Adblock
    detector
    map