Оптимизация SQLite для Android

У нас около 7-8 таблиц в нашем Android-приложении, каждый из которых имеет около 8 столбцов в среднем. Операции чтения и записи выполняются в базе данных, и я экспериментирую и пытаюсь найти способы повышения производительности уровня DataAccess. Итак, я пробовал следующее:

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

Я упомянул о своих предположениях в paranthesis; пожалуйста, исправьте меня, если я ошибаюсь.

Вопросы:

Могу ли я добавить что-нибудь еще в этот список? Я где-то читал, что избежать использования db-журнала может повысить производительность обновлений? Это миф или факт? Как это можно сделать, если их рекомендовать?
Разрешены ли вложенные транзакции в SQLite3? Как они влияют на производительность? Дело в том, что у меня есть функция, которая запускает обновление в цикле, поэтому я включил цикл в блок транзакций. Иногда эта функция вызывается из другого цикла внутри некоторой другой функции. Вызывающая функция также охватывает цикл внутри блока транзакций. Как такое вложение транзакций влияет на производительность?
В предложениях where для моих запросов используется несколько столбцов для создания предиката. Эти столбцы могут не обязательно иметь первичный ключ или уникальные столбцы. Должен ли я создавать индексы на этих столбцах? Это хорошая идея создать несколько индексов для такой таблицы?

Ответ 1

Укажите, какие именно запросы нужно оптимизировать. Возьмите копию типичной базы данных и используйте REPL для запросов времени. Используйте это, чтобы сравнить любые выгоды при оптимизации.
Используйте ANALYZE, чтобы позволить SQLite планировщику запросов работать более эффективно.
Для SELECT и UPDATE s индексы могут увеличиваться, но только если созданные вами индексы могут фактически использоваться запросами, которые вам нужно ускорить. Используйте EXPLAIN QUERY PLAN в своих запросах, чтобы узнать, какой индекс будет использоваться, или если запрос требует полного сканирования таблицы. Для больших таблиц полное сканирование таблицы плохое, и вам, вероятно, нужен индекс. Только один индекс будет использоваться для любого заданного запроса. Если у вас есть несколько предикатов, то индекс, который будет использоваться, - это тот, который, как ожидается, максимально уменьшит результирующий набор (на основе ANALYZE). У вас могут быть индексы, которые содержат несколько столбцов (для поддержки запросов с несколькими предикатами). Если у вас есть индексы с несколькими столбцами, они могут использоваться только в том случае, если предикаты соответствуют индексу слева направо без пробелов (но неиспользуемые столбцы в конце являются точными). Если вы используете предикат заказа (<, <=, > и т.д.), То это должно быть в последнем использованном столбце индекса. Использование обоих предикатов WHERE и ORDER BY требует индекса, а SQLite может использовать только один, так что это может быть точка, в которой работает производительность. Чем больше индексов у вас есть, тем медленнее будет ваш INSERT, поэтому вам придется разработать лучший компромисс для вашей ситуации.
Если у вас есть более сложные запросы, которые не могут использовать какие-либо индексы, которые вы могли бы создать, вы можете де-нормализовать свою схему, структурируя свои данные таким образом, чтобы запросы были проще и их можно было ответить используя индексы.
Если вы делаете большое количество INSERT s, попробуйте отбросить индексы и воссоздать их в конце. Вам нужно будет сравнить это.
SQLite поддерживает вложенные транзакции с использованием точек сохранения, но я не уверен, что вы получите что-то там, что по производительности.
Вы можете получить большую скорость, поставив под угрозу целостность данных. Если вы можете самостоятельно восстановить повреждение базы данных, это может сработать для вас. Возможно, вы можете сделать это только тогда, когда выполняете интенсивные операции, которые вы можете восстановить вручную.

Я не уверен, сколько из этого вы можете получить от приложения для Android. Существует более подробное руководство для оптимизации SQLite в документации SQLite.

Ответ 2

Здесь немного кода для получения EXPLAIN QUERY PLAN результатов в Android-кодах Android из работающего приложения для Android. Я начинаю с SQLiteOpenHelper dbHelper и SQLiteQueryBuilder qb.

String sql = qb.buildQuery(projection,selection,selectionArgs,groupBy,having,sortOrder,limit);
android.util.Log.d("EXPLAIN",sql + "; " + java.util.Arrays.toString(selectionArgs));
Cursor c = dbHelper.getReadableDatabase().rawQuery("EXPLAIN QUERY PLAN " + sql,selectionArgs);
if(c.moveToFirst()) {
    do {
        StringBuilder sb = new StringBuilder();
        for(int i = 0; i < c.getColumnCount(); i++) {
            sb.append(c.getColumnName(i)).append(":").append(c.getString(i)).append(", ");
        }
        android.util.Log.d("EXPLAIN",sb.toString());
    } while(c.moveToNext());
}
c.close();

Я сбросил это в мой ContentProvider.query(), и теперь я вижу, как все запросы выполняются. (В моем случае похоже, что проблема состоит в том, что слишком много запросов, а не плохое использование индексирования, но, возможно, это поможет кому-то еще...)

Ответ 3

Я бы добавил:

Использование rawQuery() вместо построения с использованием ContentValues будет закреплено в определенных случаях. конечно, немного утомительно писать сырые запросы.
Если у вас много данных типа string/text, подумайте о создании виртуальных таблиц с использованием полнотекстового поиска (FTS3), который может запускать более быстрый запрос. вы можете искать в google для точных улучшений скорости.

Ответ 4

Небольшая точка для добавления к Robie в противном случае всеобъемлющего ответа: VFS в SQLite (который в основном связан с блокировкой) может быть заменен для альтернатив. Вы можете найти одну из альтернатив, например unix-excl или unix-none, но прислушайтесь к предупреждениям на Страница SQLite VFS!

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