Práticas recomendadas para a performance do SQLite (visualizações)

Conceitos e implementação do Jetpack Compose

O Android oferece suporte integrado ao SQLite, um banco de dados SQL eficiente. Siga estas práticas recomendadas para otimizar a performance do seu app, garantindo que ele permaneça rápido e previsivelmente rápido à medida que seus dados aumentam. Ao usar estas práticas recomendadas, você também reduz a possibilidade de encontrar problemas de performance difíceis de reproduzir e resolver.

Para ter uma performance mais rápida, siga estes princípios:

• Ler menos linhas e colunas: otimize suas consultas para extrair apenas os dados necessários. Minimize a quantidade de dados lidos no banco de dados, porque a extração excessiva de dados pode afetar a performance.

• Enviar o trabalho para o mecanismo SQLite: realize operações de cálculos, filtragem e classificação nas consultas SQL. O uso do mecanismo de consulta do SQLite pode melhorar significativamente a performance.

• Modificar o esquema do banco de dados: projete seu esquema de banco de dados para ajudar o SQLite a construir planos de consulta e representações de dados eficientes. Faça a indexação de tabelas de forma adequada e otimize estruturas das tabelas para melhorar a performance.

Além disso, você pode usar as ferramentas de solução de problemas disponíveis para medir a performance do seu banco de dados SQLite para identificar áreas que exigem otimização.

Recomendamos usar a biblioteca Room do Jetpack.

Configurar o banco de dados para performance

Siga as etapas desta seção para configurar seu banco de dados para ter a performance ideal no SQLite.

Relaxar o modo de sincronização

Com o WAL, por padrão, cada confirmação emite um fsync para garantir que os dados cheguem ao disco. Isso melhora a durabilidade dos dados, mas torna as confirmações mais lentas.

O SQLite tem uma opção para controlar o modo síncrono. Se você ativar o WAL, defina o modo síncrono como NORMAL:

Kotlin

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

Nessa configuração, uma confirmação pode ser retornada antes que os dados sejam armazenados em um disco. Se o dispositivo for desligado por qualquer motivo, por exemplo, falta de energia ou em caso de kernel panic, os dados confirmados poderão ser perdidos. No entanto, devido à geração de registros, seu banco de dados não fica corrompido.

Se apenas o app falhar, os dados ainda chegarão ao disco. Na maioria dos apps, essa configuração produz melhorias de performance sem custo significativo.

**Observação**: se o app tiver vários bancos de dados, use a mesma configuração síncrona em todos os casos de dependência de dados entre bancos de dados diferentes.

Melhorar a performance da consulta

Siga estas práticas recomendadas para melhorar a performance da consulta no SQLite, minimizando os tempos de resposta e maximizando a eficiência do processamento.

Observação: muitos dos exemplos nesta página incluem uma cláusula LIMIT. Essa é uma boa prática recomendada, porque consultas que podem retornar muitas linhas têm implicações na performance ao retornar grandes conjuntos de dados. A exceção é para as consultas que retornam implicitamente um conjunto de dados de tamanho restrito. Por exemplo, uma pesquisa em um campo definido como UNIQUE só pode retornar zero ou uma linha.

Ler somente as linhas necessárias

Os filtros permitem restringir os resultados, especificando determinados critérios, como período, local ou nome. Os limites permitem controlar o número de resultados exibidos:

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

Ler somente as colunas necessárias

Evite selecionar colunas desnecessárias, porque isso pode diminuir a velocidade das suas consultas e desperdiçar recursos. Em vez disso, selecione apenas as colunas usadas.

No exemplo abaixo, você seleciona id, name e 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
  }
}

No entanto, você só precisa da coluna 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
  }
}

Parametrizar consultas

A string de consulta pode incluir um parâmetro conhecido apenas no momento da execução, como este:

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

No código anterior, cada consulta constrói uma string diferente e, portanto, não se beneficia do cache de instruções. Cada chamada exige que o SQLite seja compilado antes da execução. Em vez disso, você pode substituir o argumento id por um parâmetro e vincular o valor a 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;
    }
  }
}

Agora a consulta pode ser compilada uma vez e armazenada em cache. A consulta compilada é reutilizada entre diferentes invocações de getNameById(long).

Cuidado: se o argumento de entrada for algum outro objeto menos restrito do que apenas um número, a concatenação de strings poderá levar a uma vulnerabilidade de segurança de injeção de SQL. Sempre use parâmetros para variáveis ou dados não confiáveis.

Usar DISTINCT para valores exclusivos

O uso da palavra-chave DISTINCT pode melhorar a performance das consultas, reduzindo a quantidade de dados que precisam ser processados. Por exemplo, para retornar apenas os valores exclusivos de uma coluna, use 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
  }
}

Usar funções de agregação sempre que possível

Use funções de agregação para receber resultados sem dados de linha. Por exemplo, o código abaixo verifica se há pelo menos uma linha correspondente:

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

Para buscar apenas a primeira linha, use EXISTS(), para retornar 0 se uma linha correspondente não existir, e 1, se uma ou mais linhas corresponderem:

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

Use funções de agregação do SQLite no código do app:

• COUNT: conta quantas linhas há em uma coluna.
• SUM: adiciona todos os valores numéricos em uma coluna.
• MIN ou MAX: determina o valor mais baixo ou mais alto. Funciona para colunas numéricas, tipos de DATE e tipos de texto.
• AVG: encontra o valor numérico médio.
• GROUP_CONCAT: concatena strings com um separador opcional.

Usar COUNT() em vez de Cursor.getCount()

No exemplo abaixo, a função Cursor.getCount() lê todas as linhas do banco de dados e retorna todos os valores das linhas:

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
}

No entanto, ao usar COUNT(), o banco de dados retorna apenas a contagem:

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
}

Aninhar consultas em vez de código

O SQL é um elemento combinável e oferece suporte a subconsultas, junções e restrições de chave externa. É possível usar o resultado de uma consulta em outra sem passar pelo código do app. Isso reduz a necessidade de copiar dados do SQLite e permite que o mecanismo do banco de dados otimize sua consulta.

No exemplo abaixo, você pode executar uma consulta para descobrir qual cidade tem mais clientes e, em seguida, usar o resultado em outra consulta para encontrar todos os clientes dessa cidade:

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

Para obter o resultado na metade do tempo do exemplo anterior, use uma única consulta SQL com instruções aninhadas:

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

Verificar exclusividade no SQL

Se uma linha só puder ser inserida se um valor de coluna específico for exclusivo na tabela, talvez seja mais eficiente impor essa exclusividade como uma restrição de coluna.

No exemplo abaixo, uma consulta é executada para validar a linha a ser inserida e outra para realmente inserir:

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

Em vez de verificar a restrição exclusiva no Kotlin ou Java, você pode fazer essa verificação no SQL ao definir a tabela:

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

O SQLite faz o mesmo desta forma:

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

Observação: uma tabela de índice é criada para username, que usa armazenamento extra. Para mais informações sobre como consultar uma tabela de índice, consulte Acelerar consultas com índices.

Agora você pode inserir uma linha e deixar que o SQLite verifique a restrição:

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

Observação: se você definir INTEGER PRIMARY KEY, uma restrição exclusiva será aplicada a essa coluna e uma tabela de índice extra não será usada.

O SQLite oferece suporte a índices exclusivos com várias colunas:

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

O SQLite valida restrições mais rapidamente e com menos sobrecarga do que o código Kotlin ou Java. Uma prática recomendada é usar o SQLite em vez do código do app.

Agrupar várias inserções em uma única transação

Uma transação confirma várias operações, o que melhora não só a eficiência, mas também a precisão. Para melhorar a consistência dos dados e acelerar a performance, faça inserções em lote:

Kotlin

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

Observação: apenas uma transação de escrita pode ocorrer por vez. Use MoreExecutors.newSequentialExecutor(Executor) para serializar escritas.