Best practice per le prestazioni SQLite

Android offre il supporto integrato per SQLite, un database SQL efficiente. Segui queste best practice per ottimizzare le prestazioni della tua app e assicurarti che rimanga veloce e prevedibile man mano che i dati crescono. Utilizzando queste best practice, riduci anche la possibilità di incontrare problemi di prestazioni difficili da riprodurre e risolvere.

Per velocizzare il rendimento, segui questi principi:

  • Leggi meno righe e colonne: ottimizza le query per recuperare solo i dati necessari. Riduci al minimo la quantità di dati letti dal database, poiché un recupero eccessivo di dati può influire sulle prestazioni.

  • Esegui il push del lavoro nel motore SQLite: esegui operazioni di calcolo, filtro e ordinamento all'interno delle query SQL. L'uso del motore di query SQLite può migliorare significativamente le prestazioni.

  • Modifica lo schema del database: progetta lo schema del tuo database per aiutare SQLite a creare rappresentazioni dei dati e piani di query efficienti. Indicizzazione corretta delle tabelle e ottimizzazione delle relative strutture per migliorare le prestazioni.

Inoltre, puoi utilizzare gli strumenti di risoluzione dei problemi disponibili per misurare le prestazioni del tuo database SQLite e identificare le aree che richiedono l'ottimizzazione.

Ti consigliamo di utilizzare la libreria delle sale Jetpack.

Configura il database per le prestazioni

Segui i passaggi in questa sezione per configurare il tuo database e ottenere prestazioni ottimali in SQLite.

Abilita il logging Write-Ahead

SQLite implementa le mutazioni aggiungendole a un log, che occasionalmente compatta nel database. Questa operazione è denominata WAL (Write-Ahead Logging).

Abilita WAL a meno che non utilizzi ATTACH DATABASE.

Ridurre la modalità di sincronizzazione

Quando si utilizza WAL, per impostazione predefinita ogni commit emette un fsync per garantire che i dati raggiungano il disco. Ciò migliora la durabilità dei dati, ma rallenta i commit.

SQLite offre un'opzione per controllare la modalità sincrona. Se attivi WAL, imposta la modalità sincrona su NORMAL:

Kotlin

db.execSQL("PRAGMA synchronous = NORMAL")

Java

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

Con questa impostazione, può essere restituito un commit prima che i dati vengano archiviati su un disco. Se si verifica un arresto del dispositivo, ad esempio in caso di interruzione dell'alimentazione o in caso di kernel panic, i dati impegnati potrebbero andare persi. Tuttavia, a causa del logging, il database non è danneggiato.

Se solo la tua app si arresta in modo anomalo, i tuoi dati raggiungono ancora il disco. Per la maggior parte delle app, questa impostazione offre miglioramenti delle prestazioni senza costi materiali.

Definisci schemi di tabelle efficienti

Per ottimizzare le prestazioni e ridurre al minimo il consumo di dati, definisci uno schema di tabella efficiente. SQLite crea piani di query e dati efficienti, portando a un recupero dei dati più rapido. Questa sezione fornisce le best practice per la creazione di schemi di tabella.

Prendi in considerazione INTEGER PRIMARY KEY

Per questo esempio, definisci e compila una tabella come segue:

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

L'output della tabella è il seguente:

ID vogatore id nome città
1 456 Mario Bianchi Liverpool, Inghilterra
2 123 Michael Jackson Gary, IN
3 789 Dolly Parton Contea di Sevier, TN

La colonna rowid è un indice che conserva l'ordine di inserzione. Le query che filtrano per rowid vengono implementate come una ricerca B-Tree rapida, ma le query che filtrano per id sono una scansione lenta della tabella.

Se prevedi di eseguire ricerche entro il giorno id, puoi evitare di archiviare la colonna rowid per meno dati in termini di spazio di archiviazione e per un database complessivo più veloce:

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

La tabella ha ora il seguente aspetto:

id nome città
123 Michael Jackson Gary, IN
456 Mario Bianchi Liverpool, Inghilterra
789 Dolly Parton Contea di Sevier, TN

Poiché non è necessario archiviare la colonna rowid, le query id sono veloci. Tieni presente che la tabella è ora ordinata in base al criterio id anziché all'ordine di inserzione.

Accelera le query con gli indici

SQLite utilizza gli indici per accelerare le query. Durante l'applicazione di filtri (WHERE), ordinamento (ORDER BY) o aggregazione (GROUP BY) di una colonna, se la tabella ha un indice per la colonna, la query è accelerata.

Nell'esempio precedente, il filtro per city richiede la scansione dell'intera tabella:

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

Per un'app con molte query sulla città, puoi accelerare queste query con un indice:

CREATE INDEX city_index ON Customers(city);

Un indice viene implementato come tabella aggiuntiva, ordinata in base alla colonna dell'indice e mappato a rowid:

città ID vogatore
Gary, IN 2
Liverpool, Inghilterra 1
Contea di Sevier, TN 3

Tieni presente che il costo di archiviazione della colonna city è ora il doppio, perché è presente sia nella tabella originale sia nell'indice. Poiché utilizzi l'indice, il costo dello spazio di archiviazione aggiuntivo comporta il vantaggio di ottenere query più veloci. Tuttavia, non gestire un indice che non utilizzi per evitare di pagare il costo di archiviazione senza alcun miglioramento delle prestazioni delle query.

Crea indici a più colonne

Se le query combinano più colonne, puoi creare indici a più colonne per accelerare completamente la query. Puoi usare un indice anche su una colonna esterna e fare in modo che la ricerca interna venga eseguita come una scansione lineare.

Ad esempio, data la query seguente:

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

Puoi accelerare la query con un indice a più colonne nello stesso ordine specificato nella query:

CREATE INDEX city_name_index ON Customers(city, name);

Tuttavia, se hai un indice solo su city, l'ordinamento esterno è ancora accelerato, mentre l'ordinamento interno richiede una scansione lineare.

Questo metodo funziona anche con le richieste relative ai prefissi. Ad esempio, un indice ON Customers (city, name) accelera anche i filtri, l'ordinamento e il raggruppamento in base a city, poiché la tabella dell'indice per un indice a più colonne è ordinata in base agli indici specificati nell'ordine specificato.

Prendi in considerazione WITHOUT ROWID

Per impostazione predefinita, SQLite crea una colonna rowid per la tabella, dove rowid è un valore INTEGER PRIMARY KEY AUTOINCREMENT implicito. Se disponi già di una colonna INTEGER PRIMARY KEY, questa diventa un alias di rowid.

Per le tabelle con una chiave primaria diversa da INTEGER o una combinazione di colonne, considera WITHOUT ROWID.

Archivia i dati di piccole dimensioni come BLOB e i dati di grandi dimensioni come file

Se vuoi associare dati di grandi dimensioni a una riga, ad esempio una miniatura di un'immagine o una foto di un contatto, puoi archiviare i dati in una colonna BLOB o in un file, quindi archiviare il percorso del file nella colonna.

I file vengono generalmente arrotondati per eccesso a incrementi di 4 kB. Per i file molto piccoli, nei quali l'errore di arrotondamento è significativo, è più efficiente archiviarli nel database come BLOB. SQLite riduce al minimo le chiamate al file system e, in alcuni casi, è più veloce del file system sottostante.

Migliora le prestazioni delle query

Segui queste best practice per migliorare le prestazioni delle query in SQLite riducendo al minimo i tempi di risposta e massimizzando l'efficienza dell'elaborazione.

Leggi solo le righe necessarie

I filtri ti consentono di restringere i risultati specificando determinati criteri, come l'intervallo di date, la località o il nome. I limiti ti consentono di controllare il numero di risultati che vedi:

Kotlin

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

Java

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

Leggi solo le colonne necessarie

Evita di selezionare colonne non necessarie, che possono rallentare le query e lo spreco di risorse. Seleziona invece solo le colonne utilizzate.

Nell'esempio seguente, selezioni 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)
        // ...
    }
}

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

Tuttavia, è necessaria solo la colonna name:

Kotlin

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

Java

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

Utilizza DISTINCT per i valori univoci

L'utilizzo della parola chiave DISTINCT può migliorare il rendimento delle query riducendo la quantità di dati da elaborare. Ad esempio, se vuoi restituire solo i valori univoci di una colonna, utilizza DISTINCT:

Kotlin

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

Java

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

Se possibile, utilizza le funzioni di aggregazione

Utilizza le funzioni di aggregazione per aggregare i risultati senza dati di riga. Ad esempio, il seguente codice controlla se è presente almeno una riga corrispondente:

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

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

Per recuperare solo la prima riga, puoi utilizzare EXISTS() per restituire 0 se non esiste una riga corrispondente e 1 se una o più righe corrispondono:

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

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

Utilizza le funzioni di aggregazione SQLite nel codice dell'app:

  • COUNT: conteggia il numero di righe presenti in una colonna.
  • SUM: somma tutti i valori numerici in una colonna.
  • MIN o MAX: determina il valore più basso o più alto. Funziona per le colonne numeriche, i tipi DATE e i tipi di testo.
  • AVG: trova il valore numerico medio.
  • GROUP_CONCAT: concatena le stringhe con un separatore facoltativo.

Usa COUNT() anziché Cursor.getCount()

Nell'esempio seguente, la funzione Cursor.getCount() legge tutte le righe del database e restituisce tutti i valori delle righe:

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

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

Tuttavia, utilizzando COUNT(), il database restituisce solo il conteggio:

Kotlin

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

Java

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

Query Nest al posto del codice

SQL è componibile e supporta sottoquery, join e vincoli di chiave esterna. Puoi utilizzare il risultato di una query in un'altra query senza esaminare il codice dell'app. Questo riduce la necessità di copiare dati da SQLite e consente al motore di database di ottimizzare la query.

Nell'esempio seguente, puoi eseguire una query per trovare la città con il maggior numero di clienti e poi utilizzare il risultato in un'altra query per trovare tutti i clienti di quella città:

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

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

Per ottenere il risultato nella metà del tempo dell'esempio precedente, utilizza una singola query SQL con istruzioni nidificate:

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

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

Verifica l'univocità in SQL

Se una riga non deve essere inserita a meno che un determinato valore di colonna non sia univoco nella tabella, potrebbe essere più efficiente applicare quell'univocità come vincolo della colonna.

Nell'esempio seguente, una query viene eseguita per convalidare la riga da inserire e un'altra per inserire effettivamente:

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

Anziché controllare il vincolo univoco in Kotlin o Java, puoi controllarlo in SQL quando definisci la tabella:

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

SQLite si comporta come segue:

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

Ora puoi inserire una riga e lasciare che SQLite controlli il vincolo:

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 supporta indici univoci con più colonne:

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

SQLite convalida i vincoli più velocemente e con meno overhead rispetto al codice Kotlin o Java. Una best practice consiste nell'utilizzare SQLite anziché il codice dell'app.

Raggruppare più inserzioni in una singola transazione

Una transazione esegue il commit di più operazioni, migliorando non solo l'efficienza, ma anche la correttezza. Per migliorare la coerenza dei dati e accelerare le prestazioni, puoi eseguire gli inserimenti in batch:

Kotlin

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

Java

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

Utilizzare gli strumenti per la risoluzione dei problemi

SQLite fornisce i seguenti strumenti per la risoluzione dei problemi per aiutarti a misurare le prestazioni.

Usa il prompt interattivo di SQLite

Esegui SQLite sulla tua macchina per eseguire query e apprendere. Versioni di piattaforma Android diverse utilizzano revisioni diverse di SQLite. Per utilizzare lo stesso motore di un dispositivo Android, usa adb shell ed esegui sqlite3 sul dispositivo di destinazione.

Puoi chiedere a SQLite di eseguire le query temporali:

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

EXPLAIN QUERY PLAN

Puoi chiedere a SQLite di spiegare come intende rispondere a una query utilizzando EXPLAIN QUERY PLAN:

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

L'esempio precedente richiede un'analisi completa della tabella senza un indice per trovare tutti i clienti di Parigi. Questo processo è chiamato complessità lineare. SQLite deve leggere tutte le righe e mantenere solo le righe che corrispondono ai clienti di Parigi. Per risolvere il problema, puoi aggiungere un indice:

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 utilizzi la shell interattiva, puoi chiedere a SQLite di spiegare sempre i piani di query:

sqlite> .eqp on

Per ulteriori informazioni, consulta Pianificazione delle query.

Analizzatore SQLite

SQLite offre l'interfaccia a riga di comando (CLI) di sqlite3_analyzer per eseguire il dump di informazioni aggiuntive che possono essere utilizzate per la risoluzione dei problemi delle prestazioni. Per installarlo, visita la pagina di download SQLite.

Puoi utilizzare adb pull per scaricare un file di database da un dispositivo di destinazione alla workstation per l'analisi:

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

Browser SQLite

Puoi anche installare lo strumento GUI SQLite Browser nella pagina Download di SQLite.

Logging di Android

Android utilizza le query SQLite e le registra per te:

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