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

Основные понятия и реализация Jetpack Compose

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

Чтобы повысить производительность, следуйте этим принципам:

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

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

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

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

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

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

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

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

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

Когда вы включаете WAL через фреймворк, Android применяет синхронный режим WAL по умолчанию, который в AOSP для Android 9 и более поздних версий равен NORMAL. Производители устройств могут изменить это значение по умолчанию, а базы данных, которые не используют WAL, по умолчанию используют FULL. Чтобы убедиться, что база данных использует NORMAL, задайте этот параметр при открытии базы данных:

Kotlin

// When opening the database
val params = SQLiteDatabase.OpenParams.Builder()
    .addOpenFlags(SQLiteDatabase.ENABLE_WRITE_AHEAD_LOGGING)
    // SYNC_MODE_NORMAL is available in API level 33 and higher.
    // On API levels 28 to 32, pass "NORMAL" instead.
    .setSynchronousMode(SQLiteDatabase.SYNC_MODE_NORMAL)
    .build()
// With SQLiteOpenHelper, call setOpenParams(params) in your constructor.

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

Java

// When opening the database
SQLiteDatabase.OpenParams params = new SQLiteDatabase.OpenParams.Builder()
    .addOpenFlags(SQLiteDatabase.ENABLE_WRITE_AHEAD_LOGGING)
    // SYNC_MODE_NORMAL is available in API level 33 and higher.
    // On API levels 28 to 32, pass "NORMAL" instead.
    .setSynchronousMode(SQLiteDatabase.SYNC_MODE_NORMAL)
    .build();
// With SQLiteOpenHelper, call setOpenParams(params) in your constructor.

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

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

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

Как улучшить эффективность запросов

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

Чтение только нужных строк

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

Kotlin

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.

Kotlin

// 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:

Kotlin

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

Как параметризовать запросы

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

Kotlin

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:

Kotlin

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).

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

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

Kotlin

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

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

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

Kotlin

// 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, если одна или несколько строк соответствуют условию:

Kotlin

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() считывает все строки из базы данных и возвращает все значения строк.

Kotlin

// 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() база данных возвращает только количество:

Kotlin

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 и позволяет ядру базы данных оптимизировать ваш запрос.

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

Kotlin

// 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-запрос с вложенными операторами:

Kotlin

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

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

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

Kotlin

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

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

Kotlin

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

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

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

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

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

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

Kotlin

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

Java

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