Práticas recomendadas para a performance do SQLite

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.

Ativar o registro prévio de escrita

O SQLite implementa mutações anexando-as a um registro, que é compactado ocasionalmente no banco de dados. Isso é chamado de registro prévio de escrita (WAL) (link em inglês).

Ative o WAL, exceto se você estiver usando ATTACH DATABASE (link em inglês).

Reduzir 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 (link em inglês). Se você ativar o WAL, defina o modo síncrono como NORMAL:

db.execSQL("PRAGMA synchronous = NORMAL")

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.

Definir esquemas de tabela eficientes

Para otimizar a performance e minimizar o consumo de dados, defina um esquema de tabela eficiente. O SQLite constrói planos de consulta e dados eficientes, o que acelera a extração de dados. Esta seção fornece práticas recomendadas para criar esquemas de tabelas.

Considerar INTEGER PRIMARY KEY

Para este exemplo, defina e preencha uma tabela desta forma:

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

A saída da tabela será esta:

A coluna rowid (link em inglês) é um índice que preserva a ordem da inserção. As consultas que filtram por rowid são implementadas como uma pesquisa rápida em árvore B, mas as consultas que filtram por id são uma verificação de tabela lenta.

Se você planeja realizar pesquisas por id, pode evitar o armazenamento da coluna rowid para ter menos dados no armazenamento e um banco de dados mais rápido no geral:

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

A tabela ficará assim:

Como você não precisa armazenar a coluna rowid, as consultas id são rápidas. A tabela agora está classificada com base em id, em vez de na ordem de inserção.

Acelerar consultas com índices

O SQLite usa índices (link em inglês) para acelerar consultas. Ao filtrar (WHERE), classificar (ORDER BY) ou agregar (GROUP BY) uma coluna, se a tabela tiver um índice para a coluna, a consulta será acelerada.

No exemplo anterior, a filtragem por city exige a verificação da tabela inteira:

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

Para um app com muitas consultas de cidades, é possível acelerar essas consultas com um índice:

CREATE INDEX city_index ON Customers(city);

Um índice é implementado como uma tabela extra, classificada pela coluna de índice e mapeado para rowid:

O custo de armazenamento da coluna city agora é duplo, porque ela está na tabela original e no índice. Como você está usando o índice, o custo do armazenamento extra compensa o uso de consultas mais rápidas. No entanto, não mantenha um índice que você não esteja usando para evitar pagar o custo de armazenamento sem nenhum ganho de performance da consulta.

Criar índices de várias colunas

Se as consultas combinarem várias colunas, crie índices de várias colunas (link em inglês) para acelerar a consulta. Também é possível usar um índice em uma coluna externa e deixar a pesquisa interna ser feita como uma verificação linear.

Por exemplo, considerando esta consulta:

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

É possível acelerar a consulta com um índice de várias colunas na mesma ordem especificada na consulta:

CREATE INDEX city_name_index ON Customers(city, name);

No entanto, se você tiver apenas um índice em city, a ordem externa ainda será acelerada, embora a ordenação interna exija uma leitura linear.

Isso também funciona com consultas de prefixo. Por exemplo, um índice ON Customers (city, name) também acelera a filtragem, a ordenação e o agrupamento por city, já que a tabela de um índice de várias colunas é ordenada pelos índices especificados em uma determinada ordem.

Considerar WITHOUT ROWID

Por padrão, o SQLite cria uma coluna rowid para a tabela, em que rowid é um INTEGER PRIMARY KEY AUTOINCREMENT implícito. Se você já tem uma coluna INTEGER PRIMARY KEY, ela se tornará um alias de rowid.

Para tabelas com uma chave primária diferente de INTEGER ou um composto de colunas, considere WITHOUT ROWID (link em inglês).

Armazenar dados pequenos como um BLOB e dados grandes como um arquivo

Para associar grandes quantidades de dados a uma linha, por exemplo, a miniatura de uma imagem ou a foto de um contato, armazene os dados em uma coluna BLOB ou em um arquivo e armazene o caminho do arquivo na coluna.

Os arquivos geralmente são arredondados para até 4 KB. É melhor armazenar arquivos muito pequenos, em que o erro de arredondamento é significativo, no banco de dados como um BLOB. O SQLite minimiza as chamadas do sistema de arquivos e é mais rápido que o sistema de arquivos (link em inglês) em alguns casos.

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.

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:

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

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:

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

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

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:

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

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:

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

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:

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

Use funções de agregação do SQLite (link em inglês) 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:

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

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

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

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:

// 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()) {
          ...
        }
    }
  }
}

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

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

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:

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

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

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

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

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:

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

Usar ferramentas de solução de problemas

O SQLite oferece as ferramentas de solução de problemas abaixo para medir a performance.

Usar uma solicitação interativa do SQLite

Execute o SQLite na sua máquina para realizar consultas e aprender. Diferentes versões da Plataforma Android usam revisões diferentes do SQLite. Para usar o mesmo mecanismo de um dispositivo Android, use adb shell e execute sqlite3 no dispositivo de destino.

É possível solicitar consultas de tempo ao SQLite:

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

EXPLAIN QUERY PLAN

Você pode perguntar ao SQLite como ele pretende responder a uma consulta, usando EXPLAIN QUERY PLAN (link em inglês):

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

O exemplo anterior requer uma verificação completa da tabela sem um índice para encontrar todos os clientes de Paris. Isso é chamado de complexidade linear. O SQLite precisa ler todas as linhas e manter apenas aquelas que correspondem aos clientes de Paris. Para corrigir isso, adicione um índice:

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=?

Se você estiver usando o shell interativo, peça ao SQLite para sempre explicar os planos de consulta:

sqlite> .eqp on

Para mais informações, consulte Planejamento de consultas (link em inglês).

SQLite Analyzer

O SQLite oferece a interface de linha de comando (CLI) sqlite3_analyzer para despejar outras informações que podem ser usadas para resolver problemas de performance. Para instalar, acesse a página de download do SQLite (link em inglês).

Use adb pull para fazer o download de um arquivo de banco de dados de um dispositivo de destino para sua estação de trabalho para análise:

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

SQLite Browser

Você também pode instalar a ferramenta GUI do Navegador SQLite na página Downloads do SQLite (links em inglês).

Geração de registros do Android

O Android marca o tempo das consultas SQLite e as registra para você:

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