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

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

Для достижения более высокой производительности следуйте следующим принципам производительности:

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

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

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

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

Мы рекомендуем использовать библиотеку Jetpack Room .

Настройте базу данных для производительности

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

Включить ведение журнала упреждающей записи

SQLite реализует мутации, добавляя их в журнал, который иногда сжимает в базу данных. Это называется ведением журнала упреждающей записи (WAL) .

Включите WAL , если вы не используете ATTACH DATABASE .

Ослабьте режим синхронизации

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

SQLite имеет возможность управлять синхронным режимом . Если вы включите WAL, установите для синхронного режима значение NORMAL :

Котлин 

db.execSQL("PRAGMA synchronous = NORMAL")

Ява

db.execSQL("PRAGMA synchronous = NORMAL");

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

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

Определите эффективные схемы таблиц

Чтобы оптимизировать производительность и минимизировать потребление данных, определите эффективную схему таблицы. SQLite создает эффективные планы запросов и данные, что приводит к более быстрому получению данных. В этом разделе представлены рекомендации по созданию схем таблиц.

Рассмотрим INTEGER PRIMARY KEY

В этом примере определите и заполните таблицу следующим образом:

CREATE TABLE Customers(
  id INTEGER,
  name TEXT,
  city TEXT
);
INSERT INTO Customers Values(456, 'John Lennon', 'Liverpool, England');
INSERT INTO Customers Values(123, 'Michael Jackson', 'Gary, IN');
INSERT INTO Customers Values(789, 'Dolly Parton', 'Sevier County, TN');

Вывод таблицы следующий:

рябчик идентификатор имя город
1 456 Джон Леннон Ливерпуль, Англия
2 123 Майкл Джексон Гэри, Индиана
3 789 Долли Партон Округ Севьер, Теннесси

rowid столбца — это индекс, который сохраняет порядок вставки. Запросы с фильтрацией по rowid реализуются как быстрый поиск по B-дереву, а запросы с фильтрацией по id представляют собой медленное сканирование таблицы.

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

CREATE TABLE Customers(
  id INTEGER PRIMARY KEY,
  name TEXT,
  city TEXT
);

Теперь ваша таблица выглядит следующим образом:

идентификатор имя город
123 Майкл Джексон Гэри, Индиана
456 Джон Леннон Ливерпуль, Англия
789 Долли Партон Округ Севьер, Теннесси

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

Ускорение запросов с помощью индексов

SQLite использует индексы для ускорения запросов. При фильтрации ( WHERE ), сортировке ( ORDER BY ) или агрегировании ( GROUP BY ) столбца, если в таблице есть индекс для столбца, запрос ускоряется.

В предыдущем примере фильтрация по city требует сканирования всей таблицы:

SELECT id, name
WHERE city = 'London, England';

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

CREATE INDEX city_index ON Customers(city);

Индекс реализован как дополнительная таблица, отсортированная по столбцу индекса и сопоставленная с rowid :

город рябчик
Гэри, Индиана 2
Ливерпуль, Англия 1
Округ Севьер, Теннесси 3

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

Создание многостолбцовых индексов

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

Например, учитывая следующий запрос:

SELECT id, name
WHERE city = 'London, England'
ORDER BY city, name

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

CREATE INDEX city_name_index ON Customers(city, name);

Однако если у вас есть индекс только по city , внешний порядок все равно будет ускоряться, тогда как внутренний порядок требует линейного сканирования.

Это также работает с префиксными запросами. Например, индекс ON Customers (city, name) также ускоряет фильтрацию, упорядочивание и группировку по city , поскольку индексная таблица для многостолбцового индекса упорядочивается по заданным индексам в заданном порядке.

Рассмотрим WITHOUT ROWID

По умолчанию SQLite создает столбец rowid для вашей таблицы, где rowid — это неявное INTEGER PRIMARY KEY AUTOINCREMENT . Если у вас уже есть столбец с типом INTEGER PRIMARY KEY , тогда этот столбец становится псевдонимом rowid .

Для таблиц, имеющих первичный ключ, отличный от INTEGER , или составных столбцов, рассмотрите WITHOUT ROWID .

Храните небольшие данные в виде BLOB , а большие данные в виде файла.

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

Размер файлов обычно округляется до 4 КБ. Для очень маленьких файлов, в которых ошибка округления значительна, эффективнее хранить их в базе данных как BLOB . SQLite сводит к минимуму вызовы файловой системы и в некоторых случаях работает быстрее, чем базовая файловая система .

Улучшение производительности запросов

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

Читайте только те строки, которые вам нужны

Фильтры позволяют сузить результаты, указав определенные критерии, такие как диапазон дат, местоположение или имя. Ограничения позволяют вам контролировать количество результатов, которые вы видите:

Котлин 

db.rawQuery("""
    SELECT name
    FROM Customers
    LIMIT 10;
    """.trimIndent(),
    null
).use { cursor ->
    while (cursor.moveToNext()) {
        ...
    }
}

Ява 

try (Cursor cursor = db.rawQuery("""
    SELECT name
    FROM Customers
    LIMIT 10;
    """, null)) {
  while (cursor.moveToNext()) {
    ...
  }
}

Читайте только те столбцы, которые вам нужны

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

В следующем примере вы выбираете id , name и phone :

Котлин 

// This is not the most efficient way of doing this.
// See the following example for a better approach.

db.rawQuery(
    """
    SELECT id, name, phone
    FROM customers;
    """.trimIndent(),
    null
).use { cursor ->
    while (cursor.moveToNext()) {
        val name = cursor.getString(1)
        // ...
    }
}

Ява

// This is not the most efficient way of doing this.
// See the following example for a better approach.

try (Cursor cursor = db.rawQuery("""
    SELECT id, name, phone
    FROM customers;
    """, null)) {
  while (cursor.moveToNext()) {
    String name = cursor.getString(1);
    ...
  }
}

Однако вам нужен только столбец name :

Котлин 

db.rawQuery("""
    SELECT name
    FROM Customers;
    """.trimIndent(),
    null
).use { cursor ->
    while (cursor.moveToNext()) {
        val name = cursor.getString(0)
        ...
    }
}

Ява 

try (Cursor cursor = db.rawQuery("""
    SELECT name
    FROM Customers;
    """, null)) {
  while (cursor.moveToNext()) {
    String name = cursor.getString(0);
    ...
  }
}

Параметризация запросов с помощью карт SQL, а не с помощью конкатенации строк.

Строка запроса может включать параметр, известный только во время выполнения, например следующий:

Котлин 

fun getNameById(id: Long): String? 
    db.rawQuery(
        "SELECT name FROM customers WHERE id=$id", null
    ).use { cursor ->
        return if (cursor.moveToFirst()) {
            cursor.getString(0)
        } else {
            null
        }
    }
}

Ява

@Nullable
public String getNameById(long id) {
  try (Cursor cursor = db.rawQuery(
      "SELECT name FROM customers WHERE id=" + id, null)) {
    if (cursor.moveToFirst()) {
      return cursor.getString(0);
    } else {
      return null;
    }
  }
}

В приведенном выше коде каждый запрос создает другую строку и, следовательно, не использует кэш операторов. Каждый вызов требует, чтобы SQLite скомпилировал его, прежде чем он сможет выполниться. Вместо этого вы можете заменить аргумент id параметром и связать значение с помощью selectionArgs :

Котлин 

fun getNameById(id: Long): String? {
    db.rawQuery(
        """
          SELECT name
          FROM customers
          WHERE id=?
        """.trimIndent(), arrayOf(id.toString())
    ).use { cursor ->
        return if (cursor.moveToFirst()) {
            cursor.getString(0)
        } else {
            null
        }
    }
}

Ява

@Nullable
public String getNameById(long id) {
  try (Cursor cursor = db.rawQuery("""
          SELECT name
          FROM customers
          WHERE id=?
      """, new String[] {String.valueOf(id)})) {
    if (cursor.moveToFirst()) {
      return cursor.getString(0);
    } else {
      return null;
    }
  }
}

Теперь запрос можно скомпилировать один раз и кэшировать. Скомпилированный запрос повторно используется при различных вызовах getNameById(long) .

Итерация в SQL, а не в коде

Используйте один запрос, который возвращает все целевые результаты, вместо программного цикла, повторяющего запросы SQL для возврата отдельных результатов. Программный цикл примерно в 1000 раз медленнее, чем одиночный SQL-запрос.

Используйте DISTINCT для уникальных значений.

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

Котлин 

db.rawQuery("""
    SELECT DISTINCT name
    FROM Customers;
    """.trimIndent(),
    null
).use { cursor ->
    while (cursor.moveToNext()) {
        // Only iterate over distinct names in Kotlin
        ...
    }
}

Ява 

try (Cursor cursor = db.rawQuery("""
    SELECT DISTINCT name
    FROM Customers;
    """, null)) {
  while (cursor.moveToNext()) {
    // Only iterate over distinct names in Java
    ...
  }
}

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

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

Котлин 

// This is not the most efficient way of doing this.
// See the following example for a better approach.

db.rawQuery("""
    SELECT id, name
    FROM Customers
    WHERE city = 'Paris';
    """.trimIndent(),
    null
).use { cursor ->
    if (cursor.moveToFirst()) {
        // At least one customer from Paris
        ...
    } else {
        // No customers from Paris
        ...
}

Ява

// This is not the most efficient way of doing this.
// See the following example for a better approach.

try (Cursor cursor = db.rawQuery("""
    SELECT id, name
    FROM Customers
    WHERE city = 'Paris';
    """, null)) {
  if (cursor.moveToFirst()) {
    // At least one customer from Paris
    ...
  } else {
    // No customers from Paris
    ...
  }
}

Чтобы получить только первую строку, вы можете использовать EXISTS() чтобы вернуть 0 , если соответствующая строка не существует, и 1 , если совпадает одна или несколько строк:

Котлин 

db.rawQuery("""
    SELECT EXISTS (
        SELECT null
        FROM Customers
        WHERE city = 'Paris';
    );
    """.trimIndent(),
    null
).use { cursor ->
    if (cursor.moveToFirst() && cursor.getInt(0) == 1) {
        // At least one customer from Paris
        ...
    } else {
        // No customers from Paris
        ...
    }
}

Ява

try (Cursor cursor = db.rawQuery("""
    SELECT EXISTS (
      SELECT null
      FROM Customers
      WHERE city = 'Paris'
    );
    """, null)) {
  if (cursor.moveToFirst() && cursor.getInt(0) == 1) {
    // At least one customer from Paris
    ...
  } else {
    // No customers from Paris
    ...
  }
}

Используйте агрегатные функции SQLite в коде вашего приложения:

  • COUNT : подсчитывает количество строк в столбце.
  • SUM : добавляет все числовые значения в столбец.
  • MIN или MAX : определяет минимальное или максимальное значение. Работает для числовых столбцов, типов DATE и текстовых типов.
  • AVG : находит среднее числовое значение.
  • GROUP_CONCAT : объединяет строки с необязательным разделителем.

Используйте COUNT() вместо Cursor.getCount()

В следующем примере функция Cursor.getCount() считывает все строки из базы данных и возвращает все значения строк:

Котлин 

// This is not the most efficient way of doing this.
// See the following example for a better approach.

db.rawQuery("""
    SELECT id
    FROM Customers;
    """.trimIndent(),
    null
).use { cursor ->
    val count = cursor.getCount()
}

Ява

// This is not the most efficient way of doing this.
// See the following example for a better approach.

try (Cursor cursor = db.rawQuery("""
    SELECT id
    FROM Customers;
    """, null)) {
  int count = cursor.getCount();
  ...
}

Однако при использовании COUNT() база данных возвращает только количество:

Котлин 

db.rawQuery("""
    SELECT COUNT(*)
    FROM Customers;
    """.trimIndent(),
    null
).use { cursor ->
    cursor.moveToFirst()
    val count = cursor.getInt(0)
}

Ява 

try (Cursor cursor = db.rawQuery("""
    SELECT COUNT(*)
    FROM Customers;
    """, null)) {
  cursor.moveToFirst();
  int count = cursor.getInt(0);
  ...
}

Вложение запросов вместо кода

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

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

Котлин 

// This is not the most efficient way of doing this.
// See the following example for a better approach.

db.rawQuery("""
    SELECT city
    FROM Customers
    GROUP BY city
    ORDER BY COUNT(*) DESC
    LIMIT 1;
    """.trimIndent(),
    null
).use { cursor ->
    if (cursor.moveToFirst()) {
        val topCity = cursor.getString(0)
        db.rawQuery("""
            SELECT name, city
            FROM Customers
            WHERE city = ?;
        """.trimIndent(),
        arrayOf(topCity)).use { innerCursor ->
            while (innerCursor.moveToNext()) {
                ...
            }
        }
    }
}

Ява

// This is not the most efficient way of doing this.
// See the following example for a better approach.

try (Cursor cursor = db.rawQuery("""
    SELECT city
    FROM Customers
    GROUP BY city
    ORDER BY COUNT(*) DESC
    LIMIT 1;
    """, null)) {
  if (cursor.moveToFirst()) {
    String topCity = cursor.getString(0);
    try (Cursor innerCursor = db.rawQuery("""
        SELECT name, city
        FROM Customers
        WHERE city = ?;
        """, new String[] {topCity})) {
        while (innerCursor.moveToNext()) {
          ...
        }
    }
  }
}

Чтобы получить результат вдвое быстрее, чем в предыдущем примере, используйте один SQL-запрос с вложенными операторами:

Котлин 

db.rawQuery("""
    SELECT name, city
    FROM Customers
    WHERE city IN (
        SELECT city
        FROM Customers
        GROUP BY city
        ORDER BY COUNT (*) DESC
        LIMIT 1;
    );
    """.trimIndent(),
    null
).use { cursor ->
    if (cursor.moveToNext()) {
        ...
    }
}

Ява 

try (Cursor cursor = db.rawQuery("""
    SELECT name, city
    FROM Customers
    WHERE city IN (
      SELECT city
      FROM Customers
      GROUP BY city
      ORDER BY COUNT(*) DESC
      LIMIT 1
    );
    """, null)) {
  while(cursor.moveToNext()) {
    ...
  }
}

Проверка уникальности в SQL

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

В следующем примере один запрос выполняется для проверки вставляемой строки, а другой — для фактической вставки:

Котлин 

// This is not the most efficient way of doing this.
// See the following example for a better approach.

db.rawQuery(
    """
    SELECT EXISTS (
        SELECT null
        FROM customers
        WHERE username = ?
    );
    """.trimIndent(),
    arrayOf(customer.username)
).use { cursor ->
    if (cursor.moveToFirst() && cursor.getInt(0) == 1) {
        throw AddCustomerException(customer)
    }
}
db.execSQL(
    "INSERT INTO customers VALUES (?, ?, ?)",
    arrayOf(
        customer.id.toString(),
        customer.name,
        customer.username
    )
)

Ява

// This is not the most efficient way of doing this.
// See the following example for a better approach.

try (Cursor cursor = db.rawQuery("""
    SELECT EXISTS (
      SELECT null
      FROM customers
      WHERE username = ?
    );
    """, new String[] { customer.username })) {
  if (cursor.moveToFirst() && cursor.getInt(0) == 1) {
    throw new AddCustomerException(customer);
  }
}
db.execSQL(
    "INSERT INTO customers VALUES (?, ?, ?)",
    new String[] {
      String.valueOf(customer.id),
      customer.name,
      customer.username,
    });

Вместо проверки ограничения уникальности в Kotlin или Java вы можете проверить его в SQL при определении таблицы:

CREATE TABLE Customers(
  id INTEGER PRIMARY KEY,
  name TEXT,
  username TEXT UNIQUE
);

SQLite делает то же самое:

CREATE TABLE Customers(...);
CREATE UNIQUE INDEX CustomersUsername ON Customers(username);

Теперь вы можете вставить строку и позволить SQLite проверить ограничение:

Котлин 

try {
    db.execSql(
        "INSERT INTO Customers VALUES (?, ?, ?)",
        arrayOf(customer.id.toString(), customer.name, customer.username)
    )
} catch(e: SQLiteConstraintException) {
    throw AddCustomerException(customer, e)
}

Ява 

try {
  db.execSQL(
      "INSERT INTO Customers VALUES (?, ?, ?)",
      new String[] {
        String.valueOf(customer.id),
        customer.name,
        customer.username,
      });
} catch (SQLiteConstraintException e) {
  throw new AddCustomerException(customer, e);
}

SQLite поддерживает уникальные индексы с несколькими столбцами:

CREATE TABLE table(...);
CREATE UNIQUE INDEX unique_table ON table(column1, column2, ...);

SQLite проверяет ограничения быстрее и с меньшими издержками, чем код Kotlin или Java. Лучше всего использовать SQLite, а не код приложения.

Пакетная обработка нескольких вставок в одной транзакции

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

Котлин 

db.beginTransaction()
try {
    customers.forEach { customer ->
        db.execSql(
            "INSERT INTO Customers VALUES (?, ?, ...)",
            arrayOf(customer.id.toString(), customer.name, ...)
        )
    }
} finally {
    db.endTransaction()
}

Ява 

db.beginTransaction();
try {
  for (customer : Customers) {
    db.execSQL(
        "INSERT INTO Customers VALUES (?, ?, ...)",
        new String[] {
          String.valueOf(customer.id),
          customer.name,
          ...
        });
  }
} finally {
  db.endTransaction()
}

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

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

Используйте интерактивную подсказку SQLite

Запустите SQLite на своем компьютере, чтобы выполнять запросы и учиться. В разных версиях платформы Android используются разные версии SQLite. Чтобы использовать тот же движок, что и на устройстве под управлением Android, используйте adb shell и запустите sqlite3 на целевом устройстве.

Вы можете попросить SQLite синхронизировать запросы: 

sqlite> .timer on
sqlite> SELECT ...
Run Time: real ... user ... sys ...

EXPLAIN QUERY PLAN

Вы можете попросить SQLite объяснить, как он собирается отвечать на запрос, используя EXPLAIN QUERY PLAN :

sqlite> EXPLAIN QUERY PLAN
SELECT id, name
FROM Customers
WHERE city = 'Paris';
QUERY PLAN
`--SCAN Customers

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

sqlite> CREATE INDEX Idx1 ON Customers(city);
sqlite> EXPLAIN QUERY PLAN
SELECT id, name
FROM Customers
WHERE city = 'Paris';
QUERY PLAN
`--SEARCH test USING INDEX Idx1 (city=?

Если вы используете интерактивную оболочку, вы можете попросить SQLite всегда объяснять планы запросов:

sqlite> .eqp on

Дополнительные сведения см. в разделе Планирование запросов .

SQLite-анализатор

SQLite предлагает интерфейс командной строки (CLI) sqlite3_analyzer для вывода дополнительной информации, которую можно использовать для устранения неполадок с производительностью. Для установки посетите страницу загрузки SQLite .

Вы можете использовать adb pull для загрузки файла базы данных с целевого устройства на рабочую станцию ​​для анализа: 

adb pull /data/data/<app_package_name>/databases/<db_name>.db

SQLite-браузер

Вы также можете установить инструмент с графическим интерфейсом SQLite Browser на странице загрузок SQLite.

Ведение журнала Android

Android синхронизирует запросы SQLite и записывает их для вас:

# Enable query time logging
$ adb shell setprop log.tag.SQLiteTime VERBOSE
# Disable query time logging
$ adb shell setprop log.tag.SQLiteTime ERROR
```### Perfetto tracing

### Perfetto tracing {:#perfetto-tracing}

When [configuring Perfetto](https://perfetto.dev/docs/concepts/config), you may
add the following to include tracks for individual queries:

```protobuf
data_sources {
  config {
    name: "linux.ftrace"
    ftrace_config {
      atrace_categories: "database"
    }
  }
}
{% дословно %} {% дословно %} {% дословно %} {% дословно %}