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

Концепции и реализация Jetpack Compose

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

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

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

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

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

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

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

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

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

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

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

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

Котлин

// When opening the database
val paramsBuilder: SQLiteDatabase.OpenParams.Builder = SQLiteDatabase.OpenParams.Builder()
paramsBuilder.journalMode = SQLiteDatabase.SYNC_MODE_NORMAL

// Or: after having opened the database
db.execSQL("PRAGMA synchronous = NORMAL");

Java

// When opening the database
SQLiteDatabase.OpenParams.Builder paramsBuilder = new SQLiteDatabase.OpenParams.Builder();
paramsBuilder.setJournalMode(SQLiteDatabase.SYNC_MODE_NORMAL);

// Or: after having opened the database
db.execSQL("PRAGMA synchronous = NORMAL");

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

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

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

Повышение производительности запросов

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

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

Читайте только необходимые строки.

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

Котлин

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

Java

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

Читайте только необходимые столбцы.

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

В следующем примере вы выбираете 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)
        // Further processing
    }
}

Java

// 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);
    // Further processing
  }
}

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

Котлин

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

Java

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

Параметризация запросов

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

Котлин

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
        }
    }
}

Java

@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
        }
    }
}

Java

@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-инъекциями. Всегда используйте параметры для переменных или ненадежных данных.

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

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

Котлин

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

Java

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

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

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

Котлин

// 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
        // Handle found
    } else {
        // No customers from Paris
        // Handle not found
}

Java

// 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
    // Handle found
  } else {
    // No customers from Paris
    // Handle not found
  }
}

Чтобы получить только первую строку, можно использовать 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
        // Handle found
    } else {
        // No customers from Paris
        // Handle not found
    }
}

Java

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
    // Handle found
  } else {
    // No customers from Paris
    // Handle not found
  }
}

Используйте агрегатные функции 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()
    // Use count
}

Java

// 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();
  // Use count
}

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

Котлин

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

Java

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

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

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()) {
                // Process inner cursor data
            }
        }
    }
}

Java

// 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()) {
          // Process inner cursor data
        }
    }
  }
}

Чтобы получить результат вдвое быстрее, чем в предыдущем примере, используйте один 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()) {
        // Process cursor data
    }
}

Java

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()) {
    // Process cursor data
  }
}

Проверка уникальности в 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
    )
)

Java

// 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);

Примечание: для username создается индексная таблица, которая использует дополнительное хранилище. Дополнительную информацию о запросах к индексной таблице см. в разделе «Ускорение запросов с помощью индексов» .

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

Котлин

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

Java

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);
}

Примечание: Если вы определяете INTEGER PRIMARY KEY , то к этому столбцу применяется ограничение уникальности, и дополнительная индексная таблица не используется.

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, "customerValue")
        )
    }
} finally {
    db.endTransaction()
}

Java

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

Примечание: одновременно может выполняться только одна транзакция записи. Используйте MoreExecutors.newSequentialExecutor(Executor) для сериализации операций записи.

{% verbatim %} {% endverbatim %}